Thread: [cx-oracle-users] Strange problem with query execution...
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Thierry F. <thi...@on...> - 2012-04-05 07:43:23
|
Hi,
I'm using cx_Oracle (actually in release 5.1.1, but with the same
symptoms in 5.1 and 5.0.4) through SQLAlchemy to access an Oracle 10g
database (release 10.2.0.4).
I'm building a web application querying a quite big table (nearly 3.5
millions records), which is indexed correctly.
After activating SQLAlchemy "echo SQL" feature, I noticed that :
- an SQLAlchemy request takes between 10 and 15 seconds
- the same request executed from an SQL worksheet (in Tora) is nearly
immediate.
So I made a few tests using cx_Oracle without SQLAlchemy and the
results are that :
- if I execute the same query with parameters (via
"cursor.execute("select...", param1=value1,...)", which is the way
SQLAlchemy works), it still takes between 10 and 15 seconds;
- but if I execute the same request with "hard-coded" parameters, the
result is immediate.
Any idea (perhaps on the DB side) ?
Thanks for any help,
Thierry
--
Chef de projets internet/intranet
Office National des Forêts
Direction des Systèmes d'Information
2, Avenue de Saint Mandé
75570 PARIS Cedex 12
Tél. : 01 40 19 59 64
Fax. : 01 40 19 59 85
Mél. : thi...@on...
WWW : http://www.onf.fr
|
|
From: Andreas M. <and...@we...> - 2012-04-05 07:51:14
|
Hi Thierry,
as far as I know Oracle DB's query planner can take
into account how selective a value in a where clause
is. That can cause the described phaenomena:
Slow execution with bind parameters, fast with literal
values in sql statement.
Look at the query plan for both cases.
(Buzzword: Query Plan Stability)
Best regards
Andreas Mock
> -----Original Message-----
> From: Thierry Florac [mailto:thi...@on...]
> Sent: Thursday, April 05, 2012 9:08 AM
> To: cx-...@li...
> Subject: [cx-oracle-users] Strange problem with query execution...
>
>
> Hi,
>
> I'm using cx_Oracle (actually in release 5.1.1, but with the same
> symptoms in 5.1 and 5.0.4) through SQLAlchemy to access an Oracle 10g
> database (release 10.2.0.4).
>
> I'm building a web application querying a quite big table (nearly 3.5
> millions records), which is indexed correctly.
>
> After activating SQLAlchemy "echo SQL" feature, I noticed that :
> - an SQLAlchemy request takes between 10 and 15 seconds
> - the same request executed from an SQL worksheet (in Tora) is nearly
> immediate.
>
> So I made a few tests using cx_Oracle without SQLAlchemy and the
> results are that :
> - if I execute the same query with parameters (via
> "cursor.execute("select...", param1=value1,...)", which is the way
> SQLAlchemy works), it still takes between 10 and 15 seconds;
> - but if I execute the same request with "hard-coded" parameters, the
> result is immediate.
>
> Any idea (perhaps on the DB side) ?
>
> Thanks for any help,
> Thierry
> --
> Chef de projets internet/intranet
> Office National des Forêts
> Direction des Systèmes d'Information
> 2, Avenue de Saint Mandé
> 75570 PARIS Cedex 12
>
> Tél. : 01 40 19 59 64
> Fax. : 01 40 19 59 85
> Mél. : thi...@on...
> WWW : http://www.onf.fr
>
> ------------------------------------------------------------------------------
> Better than sec? Nothing is better than sec when it comes to
> monitoring Big Data applications. Try Boundary one-second
> resolution app monitoring today. Free.
> http://p.sf.net/sfu/Boundary-dev2dev
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
|
|
From: Lassi T. <la...@ce...> - 2012-04-05 09:11:01
|
Hi,
> I'm using cx_Oracle (actually in release 5.1.1, but with the same
> symptoms in 5.1 and 5.0.4) through SQLAlchemy to access an Oracle 10g
> database (release 10.2.0.4).
>
> I'm building a web application querying a quite big table (nearly 3.5
> millions records), which is indexed correctly.
>
> After activating SQLAlchemy "echo SQL" feature, I noticed that :
> - an SQLAlchemy request takes between 10 and 15 seconds
> - the same request executed from an SQL worksheet (in Tora) is nearly
> immediate.
>
> So I made a few tests using cx_Oracle without SQLAlchemy and the
> results are that :
> - if I execute the same query with parameters (via
> "cursor.execute("select...", param1=value1,...)", which is the way
> SQLAlchemy works), it still takes between 10 and 15 seconds;
> - but if I execute the same request with "hard-coded" parameters, the
> result is immediate.
>
> Any idea (perhaps on the DB side) ?
Yes, oracle peeks at bind variables as a part of query planning. My first
guess would be that your query plans are bad, which could be for any number
of reasons. Assuming you use standard cost-based plans, first check if you
are running with up-to-date table statistics, which implies having your
indices monitored. For example if your table stats say the table has one
row but it has million rows now, bind value peek can totally mislead the
query planner to make a bad choice.
In sqlplus you can get the query plans with 'explain plan for <query>',
then run '@?/rdbms/admin/utlxpls.sql'. This latter isn't in instant client
but you can grab it from the full oracle client and it should still work in
the instant client sqlplus.
There's a way to get that same info via APIs like cx-oracle, but I don't
remember the incantations off the top of my head. If you can attach OEM to
your database where you execute these queries, you can see the plans live,
which may be a better way to evaluate your application.
Below you can find some useful SQL to dump and manage table stats. Feel
free to search keywords on these, there's plenty of documentation.
Be *very* careful about running these in your production schema. If you
have not had table monitoring on, and activate it, all your query plans
can change dramatically. It *is* possible even if not likely the result
is disastrously bad query plans. Though note that some DBAs enable all
databases to run all of this automatically every day or some such scheme,
so it may be that none of this is related to your problem.
Hope this helps.
Lassi
1) Dump the current table / index stats with:
====
set lines 1000
set pages 1000
select
table_name, inserts, updates, deletes,
timestamp, truncated, drop_segments
from user_tab_modifications
order by table_name;
select
index_name, num_rows, distinct_keys,
leaf_blocks, clustering_factor, blevel,
avg_leaf_blocks_per_key
from user_indexes
order by index_name;
select
table_name, column_name, num_distinct,
num_nulls, num_buckets, trunc(density,4)
from user_tab_col_statistics
where table_name in (select table_name from user_tables)
order by table_name, column_name;
====
2) Enable monitoring with something like this:
====
set serveroutput on size 100000
DECLARE
BEGIN
FOR t IN (SELECT table_name FROM user_tables
WHERE table_name NOT LIKE 'X%'
AND monitoring != 'YES')
LOOP
dbms_output.put_line ('Enabling monitoring for table ' || t.table_name);
execute immediate 'alter table ' || t.table_name || ' monitoring';
END LOOP;
FOR i IN (SELECT index_name FROM user_indexes
WHERE index_name NOT LIKE 'SYS%'
AND index_name NOT LIKE 'X%'
AND index_type NOT LIKE 'IOT%'
AND last_analyzed is null)
LOOP
dbms_output.put_line ('Enabling monitoring for index ' || i.index_name);
execute immediate 'alter index ' || i.index_name || ' monitoring usage';
END LOOP;
END;
/
====
3) Force table stats to update with something like this:
====
set serveroutput on size 100000
BEGIN
dbms_stats.gather_schema_stats
(ownname => user,
options => 'GATHER AUTO',
degree => 2,
cascade => true,
no_invalidate => false,
force => true);
END;
/
====
|
|
From: Thierry F. <thi...@on...> - 2012-04-05 09:49:19
|
Le Thu, 05 Apr 2012 11:10:53 +0200,
Lassi Tuura <la...@ce...> a écrit:
> > I'm using cx_Oracle (actually in release 5.1.1, but with the same
> > symptoms in 5.1 and 5.0.4) through SQLAlchemy to access an Oracle
> > 10g database (release 10.2.0.4).
> >
> > I'm building a web application querying a quite big table (nearly
> > 3.5 millions records), which is indexed correctly.
> >
> > After activating SQLAlchemy "echo SQL" feature, I noticed that :
> > - an SQLAlchemy request takes between 10 and 15 seconds
> > - the same request executed from an SQL worksheet (in Tora) is
> > nearly immediate.
> >
> > So I made a few tests using cx_Oracle without SQLAlchemy and the
> > results are that :
> > - if I execute the same query with parameters (via
> > "cursor.execute("select...", param1=value1,...)", which is the way
> > SQLAlchemy works), it still takes between 10 and 15 seconds;
> > - but if I execute the same request with "hard-coded" parameters,
> > the result is immediate.
> >
> > Any idea (perhaps on the DB side) ?
>
> Yes, oracle peeks at bind variables as a part of query planning. My
> first guess would be that your query plans are bad, which could be
> for any number of reasons. Assuming you use standard cost-based
> plans, first check if you are running with up-to-date table
> statistics, which implies having your indices monitored. For example
> if your table stats say the table has one row but it has million rows
> now, bind value peek can totally mislead the query planner to make a
> bad choice.
Yes, tables and indexes statistics are up to date, rebuild once a day.
Query plan *shouldn't* be so bad, as hard-coded criterias give good
query plans and good response time...
> In sqlplus you can get the query plans with 'explain plan for
> <query>', then run '@?/rdbms/admin/utlxpls.sql'. This latter isn't in
> instant client but you can grab it from the full oracle client and it
> should still work in the instant client sqlplus.
Already done, activating tracing and many things to see SGA status and
query execution plans.
Result is as explayed : Oracle makes a full scan while using queries
with parameters :-(
What is strange is that it didn't do that just a few weeks ago and
that, according to my DBA, no configuration change occurred !
> There's a way to get that same info via APIs like cx-oracle, but I
> don't remember the incantations off the top of my head. If you can
> attach OEM to your database where you execute these queries, you can
> see the plans live, which may be a better way to evaluate your
> application.
>
> Below you can find some useful SQL to dump and manage table stats.
> Feel free to search keywords on these, there's plenty of
> documentation.
>
> Be *very* careful about running these in your production schema. If
> you have not had table monitoring on, and activate it, all your query
> plans can change dramatically. It *is* possible even if not likely
> the result is disastrously bad query plans. Though note that some
> DBAs enable all databases to run all of this automatically every day
> or some such scheme, so it may be that none of this is related to
> your problem.
Thanks for this, I'll try to have a look at them and forward them to my
DBA...
Best regards,
Thierry
--
Chef de projets internet/intranet
Office National des Forêts
Direction des Systèmes d'Information
2, Avenue de Saint Mandé
75570 PARIS Cedex 12
Tél. : 01 40 19 59 64
Fax. : 01 40 19 59 85
Mél. : thi...@on...
WWW : http://www.onf.fr
|
|
From: Doug H. <djh...@te...> - 2012-04-05 11:54:38
|
On 2012-04-05 01:08, Thierry Florac wrote:
> Hi,
>
> I'm using cx_Oracle (actually in release 5.1.1, but with the same
> symptoms in 5.1 and 5.0.4) through SQLAlchemy to access an Oracle 10g
> database (release 10.2.0.4).
>
> I'm building a web application querying a quite big table (nearly 3.5
> millions records), which is indexed correctly.
>
> After activating SQLAlchemy "echo SQL" feature, I noticed that :
> - an SQLAlchemy request takes between 10 and 15 seconds
> - the same request executed from an SQL worksheet (in Tora) is nearly
> immediate.
>
> So I made a few tests using cx_Oracle without SQLAlchemy and the
> results are that :
> - if I execute the same query with parameters (via
> "cursor.execute("select...", param1=value1,...)", which is the way
> SQLAlchemy works), it still takes between 10 and 15 seconds;
> - but if I execute the same request with "hard-coded" parameters, the
> result is immediate.
>
> Any idea (perhaps on the DB side) ?
>
> Thanks for any help,
> Thierry
Look for implicit type conversions in the where clause. If columns are
converted to match the parameter type, then an index on the column will
not be used. You may need to include explicit type conversion around the
parameters to prevent implicit conversion of indexed columns. This is a
subtle problem that can be very hard to detect, but cause horrible query
performance.
For example, a clause like "numeric_column = :param1" may need to be
coded as "numeric_column = to_number(:param1)".
--
Doug Henderson, Calgary, Alberta, Canada
|
|
From: Thierry F. <thi...@on...> - 2012-04-05 12:01:37
|
Le Thu, 05 Apr 2012 05:39:40 -0600, Doug Henderson <djh...@te...> a écrit: > Look for implicit type conversions in the where clause. If columns are > converted to match the parameter type, then an index on the column > will not be used. You may need to include explicit type conversion > around the parameters to prevent implicit conversion of indexed > columns. This is a subtle problem that can be very hard to detect, > but cause horrible query performance. > > For example, a clause like "numeric_column = :param1" may need to be > coded as "numeric_column = to_number(:param1)". Thanks for the hint ! But I checked all database columns and query parameters and I only use strings; that's a point where SQLAlchemy prevents you from using bad query parameters types... Regards, Thierry -- Chef de projets internet/intranet Office National des Forêts Direction des Systèmes d'Information 2, Avenue de Saint Mandé 75570 PARIS Cedex 12 Tél. : 01 40 19 59 64 Fax. : 01 40 19 59 85 Mél. : thi...@on... WWW : http://www.onf.fr |
|
From: Christian K. <ckl...@no...> - 2012-05-31 04:25:20
|
Hi Thierry, did you find a solution for that problem. It seems run in the same issues? Thanks in advance Christian Thierry Florac schrieb: > Le Thu, 05 Apr 2012 05:39:40 -0600, > Doug Henderson<djh...@te...> a écrit: > >> Look for implicit type conversions in the where clause. If columns are >> converted to match the parameter type, then an index on the column >> will not be used. You may need to include explicit type conversion >> around the parameters to prevent implicit conversion of indexed >> columns. This is a subtle problem that can be very hard to detect, >> but cause horrible query performance. >> >> For example, a clause like "numeric_column = :param1" may need to be >> coded as "numeric_column = to_number(:param1)". > > Thanks for the hint ! > But I checked all database columns and query parameters and I only use > strings; that's a point where SQLAlchemy prevents you from using bad > query parameters types... > > Regards, > Thierry |
|
From: Thierry F. <thi...@on...> - 2012-05-31 08:02:09
|
Hi Christian, No, I didn't found any solution until now. I asked SQLAlchemy mailing list and afterwards cx_Oracle ML, which redirected me to several Oracle links. I made several checks (only using cx_Oracle) and the conclusion is that : - if I execute the query generated by SQLAlchemy, using query parameters, the index is NOT used - but if I execute the same query using hard-coded parameters, the index is used and the execution time is correct :-/ Of course, I don't want to go back and update my application without using SQLAlchemy, so I leaved the code untouched... The problem seems to be linked to what is called "Oracle execution plan stability", as described here: http://docs.oracle.com/cd/B19306_01/server.102/b14211/outlines.htm I tried to make several tests using "stored outlines" as described in this article (with the help of our "poor DBA" :-/ !!), but without any success until now... Regards, Thierry Le Thu, 31 May 2012 06:24:53 +0200, Christian Klinger <ckl...@no...> a écrit: > Hi Thierry, > > did you find a solution for that problem. It seems run in the same > issues? > > Thanks in advance > Christian > > Thierry Florac schrieb: > > Le Thu, 05 Apr 2012 05:39:40 -0600, > > Doug Henderson<djh...@te...> a écrit: > > > >> Look for implicit type conversions in the where clause. If columns > >> are converted to match the parameter type, then an index on the > >> column will not be used. You may need to include explicit type > >> conversion around the parameters to prevent implicit conversion of > >> indexed columns. This is a subtle problem that can be very hard to > >> detect, but cause horrible query performance. > >> > >> For example, a clause like "numeric_column = :param1" may need to > >> be coded as "numeric_column = to_number(:param1)". > > > > Thanks for the hint ! > > But I checked all database columns and query parameters and I only > > use strings; that's a point where SQLAlchemy prevents you from > > using bad query parameters types... > > > > Regards, > > Thierry |
|
From: Mark H. <mh...@pi...> - 2012-05-31 18:04:26
|
On 5/31/12 12:27 AM, Thierry Florac wrote: > > Hi Christian, > > No, I didn't found any solution until now. > I asked SQLAlchemy mailing list and afterwards cx_Oracle ML, which > redirected me to several Oracle links. > I made several checks (only using cx_Oracle) and the conclusion is > that : > - if I execute the query generated by SQLAlchemy, using query > parameters, the index is NOT used > - but if I execute the same query using hard-coded parameters, the > index is used and the execution time is correct :-/ short version: The solution for us was to simply regather statistics using DBMS_STATS.GATHER_TABLE_STATS. long version: I had a similar problem... I'm no expert, but here's the basic idea: - execution plans get reused - it's possible to have a "bad" execution plan which gets reused For example, suppose you have a column FOO with a yes/no value and a million rows, all of which have FOO = 1. It's likely that if you have WHERE FOO = 1, the planner will know that a full table scan will be more efficient than an index scan. But, suppose the data changes, so that only 100 rows have FOO = 1. Then obviously an index scan will be more efficient. But if the query has parameters, (FOO = :1), then [handwaving, coz I wasn't quite following the explanation], the execution planner doesn't get reinvoked since it's not obvious to oracle that the row percentages has changed. Regathering statistics invalidated the plans based on old statistics and made our query faster. HTH! |
|
From: Thierry F. <thi...@on...> - 2012-06-01 07:56:32
|
Le Thu, 31 May 2012 10:52:04 -0700, Mark Harrison <mh...@pi...> a écrit: > On 5/31/12 12:27 AM, Thierry Florac wrote: > > > > Hi Christian, > > > > No, I didn't found any solution until now. > > I asked SQLAlchemy mailing list and afterwards cx_Oracle ML, which > > redirected me to several Oracle links. > > I made several checks (only using cx_Oracle) and the conclusion is > > that : > > - if I execute the query generated by SQLAlchemy, using query > > parameters, the index is NOT used > > - but if I execute the same query using hard-coded parameters, the > > index is used and the execution time is correct :-/ > > > short version: > > The solution for us was to simply regather statistics using > DBMS_STATS.GATHER_TABLE_STATS. > > long version: > > I had a similar problem... I'm no expert, but here's the basic idea: > > - execution plans get reused > - it's possible to have a "bad" execution plan which gets reused > > For example, suppose you have a column FOO with a yes/no value and a > million rows, all of which have FOO = 1. It's likely that if you > have WHERE FOO = 1, the planner will know that a full table scan will > be more efficient than an index scan. > > But, suppose the data changes, so that only 100 rows have FOO = 1. > Then obviously an index scan will be more efficient. > > But if the query has parameters, (FOO = :1), then [handwaving, coz I > wasn't quite following the explanation], the execution planner doesn't > get reinvoked since it's not obvious to oracle that the row > percentages has changed. > > Regathering statistics invalidated the plans based on old statistics > and made our query faster. I'll forward your message to our DBA as soon as she come back from holidays, to try to use this package. I'll reply to the ML to give our feedback. Best regards, Thierry -- Chef de projets internet/intranet Office National des Forêts Direction des Systèmes d'Information 2, Avenue de Saint Mandé 75570 PARIS Cedex 12 Tél. : 01 40 19 59 64 Fax. : 01 40 19 59 85 Mél. : thi...@on... WWW : http://www.onf.fr |
|
From: Christian K. <ckl...@no...> - 2012-10-01 07:32:20
|
Hi Thierry, i ask again any news on this one? Christian Thierry Florac schrieb: > Le Thu, 31 May 2012 10:52:04 -0700, > Mark Harrison<mh...@pi...> a écrit: > >> On 5/31/12 12:27 AM, Thierry Florac wrote: >>> Hi Christian, >>> >>> No, I didn't found any solution until now. >>> I asked SQLAlchemy mailing list and afterwards cx_Oracle ML, which >>> redirected me to several Oracle links. >>> I made several checks (only using cx_Oracle) and the conclusion is >>> that : >>> - if I execute the query generated by SQLAlchemy, using query >>> parameters, the index is NOT used >>> - but if I execute the same query using hard-coded parameters, the >>> index is used and the execution time is correct :-/ >> >> short version: >> >> The solution for us was to simply regather statistics using >> DBMS_STATS.GATHER_TABLE_STATS. >> >> long version: >> >> I had a similar problem... I'm no expert, but here's the basic idea: >> >> - execution plans get reused >> - it's possible to have a "bad" execution plan which gets reused >> >> For example, suppose you have a column FOO with a yes/no value and a >> million rows, all of which have FOO = 1. It's likely that if you >> have WHERE FOO = 1, the planner will know that a full table scan will >> be more efficient than an index scan. >> >> But, suppose the data changes, so that only 100 rows have FOO = 1. >> Then obviously an index scan will be more efficient. >> >> But if the query has parameters, (FOO = :1), then [handwaving, coz I >> wasn't quite following the explanation], the execution planner doesn't >> get reinvoked since it's not obvious to oracle that the row >> percentages has changed. >> >> Regathering statistics invalidated the plans based on old statistics >> and made our query faster. > > > I'll forward your message to our DBA as soon as she come back from > holidays, to try to use this package. > I'll reply to the ML to give our feedback. > > Best regards, > Thierry |
|
From: Thierry F. <thi...@on...> - 2012-10-01 09:25:35
|
Hi Christian, Our database has been tuned by our DBA and results are now "quite good" on many aspects, even if probably not as good as with hard coded queries. The only problem is that I didn't managed until now to know what was modified! My stupid DBA just says me that it's a "DBA secret" !!!!! Regards, Thierry Le Mon, 01 Oct 2012 09:32:01 +0200, Christian Klinger <ckl...@no...> a écrit: > Hi Thierry, > > i ask again any news on this one? > > Christian > > Thierry Florac schrieb: > > Le Thu, 31 May 2012 10:52:04 -0700, > > Mark Harrison<mh...@pi...> a écrit: > > > >> On 5/31/12 12:27 AM, Thierry Florac wrote: > >>> Hi Christian, > >>> > >>> No, I didn't found any solution until now. > >>> I asked SQLAlchemy mailing list and afterwards cx_Oracle ML, which > >>> redirected me to several Oracle links. > >>> I made several checks (only using cx_Oracle) and the conclusion is > >>> that : > >>> - if I execute the query generated by SQLAlchemy, using query > >>> parameters, the index is NOT used > >>> - but if I execute the same query using hard-coded parameters, > >>> the index is used and the execution time is correct :-/ > >> > >> short version: > >> > >> The solution for us was to simply regather statistics using > >> DBMS_STATS.GATHER_TABLE_STATS. > >> > >> long version: > >> > >> I had a similar problem... I'm no expert, but here's the basic > >> idea: > >> > >> - execution plans get reused > >> - it's possible to have a "bad" execution plan which gets reused > >> > >> For example, suppose you have a column FOO with a yes/no value and > >> a million rows, all of which have FOO = 1. It's likely that if you > >> have WHERE FOO = 1, the planner will know that a full table scan > >> will be more efficient than an index scan. > >> > >> But, suppose the data changes, so that only 100 rows have FOO = 1. > >> Then obviously an index scan will be more efficient. > >> > >> But if the query has parameters, (FOO = :1), then [handwaving, coz > >> I wasn't quite following the explanation], the execution planner > >> doesn't get reinvoked since it's not obvious to oracle that the row > >> percentages has changed. > >> > >> Regathering statistics invalidated the plans based on old > >> statistics and made our query faster. > > > > > > I'll forward your message to our DBA as soon as she come back from > > holidays, to try to use this package. > > I'll reply to the ML to give our feedback. > > > > Best regards, > > Thierry |
|
From: Mark H. <mh...@pi...> - 2012-10-01 18:18:34
|
On 10/1/12 1:50 AM, Thierry Florac wrote: > > Hi Christian, > > Our database has been tuned by our DBA and results are now "quite > good" on many aspects, even if probably not as good as with hard coded > queries. > > The only problem is that I didn't managed until now to know what was > modified! My stupid DBA just says me that it's a "DBA secret" !!!!! Heh, it probably means he just regathered stats which caused a new execution plan to be generated, and that execution plan is working better for your data. Plug for my DBAs... they're great and have gone to a lot of effort to teach us all we want and need to know about writing good applications. If I ever say anything on this list which makes sense it's probably due to them. If anybody is looking for an outsource company that does 24x7 monitored support, check them out! http://www.dbspecialists.com/ |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X