Home » RDBMS Server » Performance Tuning » What is happening with this sql?
What is happening with this sql? [message #121238] Fri, 27 May 2005 02:11 Go to next message
rramanat
Messages: 7
Registered: May 2005
Location: Sydney
Junior Member
All

I have a query like this:

select DISTINCT d.u##pfolio
,d.u##asset
from pmsdb.chessdtl d
,pmsdb.chessdtlout dl
,pmsdb.consollist l
where d.chessmsgtype = '533'
and d.u##msgno = dl.u##msgno
and d.u##pfolio = l.U##CONSOL_ENTRY
and l.U##TYPE = 'P'
and l.U##CONSOL = 'AS_MPS';


Query plan is :

7 SELECT STATEMENT
6 SORT [UNIQUE]
5 NESTED LOOPS
3 NESTED LOOPS
1 PMSDB.CHESSDTL TABLE ACCESS [FULL]
2 PMSDB.CONSOLLIST##PFCONS1 INDEX [UNIQUE SCAN]
4 PMSDB.CHESSDTLOUT##CHESSDTLOUTKEY1 INDEX [UNIQUE SCAN]


It takes about 30 secs.

select /*+ INDEX (l CONSOLLIST##PFCONS1) INDEX (d CHESSDTL##CHESSDTLKEY5 ) INDEX ( dl CHESSDTLOUT##CHESSDTLOUTKEY1 ) */
DISTINCT d.u##pfolio
,d.u##asset
from pmsdb.chessdtl d
,pmsdb.chessdtlout dl
,pmsdb.consollist l
where d.chessmsgtype = '533'
and d.u##msgno = dl.u##msgno
and d.u##pfolio = l.U##CONSOL_ENTRY
and l.U##TYPE = 'P'
and l.U##CONSOL = 'AS_MPS';

Query plan is:

8 SELECT STATEMENT
7 SORT [UNIQUE]
6 NESTED LOOPS
4 NESTED LOOPS
1 PMSDB.CONSOLLIST##PFCONS1 INDEX [RANGE SCAN]
3 PMSDB.CHESSDTL TABLE ACCESS [BY INDEX ROWID]
2 PMSDB.CHESSDTL##CHESSDTLKEY5 INDEX [RANGE SCAN]
5 PMSDB.CHESSDTLOUT##CHESSDTLOUTKEY1 INDEX [UNIQUE SCAN]

it takes about 1 sec to execute.

TABLES and Indexes are fully analyzed. Why the optimizer is not picking up the index for CHESSDTL table (1.5mil rows)


Cols on CHESSDTLKEY5 index is as follows:

U##PFOLIO, U##MODULE, U##ASSET, EFFDATE, PROGRESS_RECID

Any help would be well appreciated




Re: What is happening with this sql? [message #121241 is a reply to message #121238] Fri, 27 May 2005 02:16 Go to previous messageGo to next message
rramanat
Messages: 7
Registered: May 2005
Location: Sydney
Junior Member
ALso, I am on 9.2.0.4 and the mode is "CHOOSE". This query was running okay in 8i.

I have tried various adjustments like:

optimizer_index_cost_adj=50 and then 40 etc. No success.

Re: What is happening with this sql? [message #121256 is a reply to message #121241] Fri, 27 May 2005 04:04 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
What is the average selectivity of chessmsgtype field ? I mean what is
ratio (number of unique values)/(number of values) ?
And what number of rows with chessmsgtype = '533' ?

Rgds.
Re: What is happening with this sql? [message #121261 is a reply to message #121256] Fri, 27 May 2005 04:15 Go to previous messageGo to next message
rramanat
Messages: 7
Registered: May 2005
Location: Sydney
Junior Member
Here are the details:

Total rows in the table: 1618270.

Rows with 533 value = 169335 (which is more than 10%)

There are about 35 distinct values for that column.

Some other details:

Index on CHESSDTL##CHESSDTLKEY5: Leaf blocks 9926 and clustering factor = 1400752. Here clustering factor is close to no of rows and hence Optimizer thinks table scan is fine.

But when I use the hint to use the above index, the result is much quicker.

thanks
Re: What is happening with this sql? [message #121267 is a reply to message #121261] Fri, 27 May 2005 04:51 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Do you need in practice composite index for
(U##PFOLIO, U##MODULE, U##ASSET, EFFDATE, PROGRESS_RECID) ?
Can you use index for, say, (U##PFOLIO,chessmsgtype) ?

Rgds.
Re: What is happening with this sql? [message #121301 is a reply to message #121238] Fri, 27 May 2005 08:58 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Also, when you say you have statistics, how exactly did you collect them? Try something like:

dbms_stats.gather_table_stats(owner,table,cascade=>true,method_opt=>'for all columns size 250');

And run that for each table.

Also set your optimizer_mode to all_rows instead of choose.

Do the above first and regenerate your explain plan results and post them back (without hints). Oh and set your optimizer_index parameters back to default.

And I take it there is a good reason that you are including the ,pmsdb.chessdtlout dl table? It doesn't have any criteria on it other than the join to it itself (which might be ok, depends on your data/situation, or it might not be needed).

Oh and are all of your columns that won't contain nulls set as not null? A regular index won't index nulls, so the CBO can't always use an index if the field that is indexed is with null instead of not null, even if the data doesn't have nulls, because the CBO can't assume that all of the values are indexed.

On second thought, that last part probably doesn't apply to your situation, it mainly impacts when CBO tries to read an index to retrieve the selected columns.

[Updated on: Fri, 27 May 2005 09:02]

Report message to a moderator

Re: What is happening with this sql? [message #121352 is a reply to message #121301] Fri, 27 May 2005 14:17 Go to previous messageGo to next message
pscjhe
Messages: 38
Registered: April 2005
Member
Since it is 9i2, if you can publish you execution plan using
 explain plan for <your SQL>
 select * from table(dbms_xplan.display) 

it will be more revealing.

Re: What is happening with this sql? [message #121762 is a reply to message #121352] Tue, 31 May 2005 22:05 Go to previous messageGo to next message
rramanat
Messages: 7
Registered: May 2005
Location: Sydney
Junior Member
Hi


SOrry for not getting back to you immediately as I was not in for the last 2 days. I am very sorry.

I analyzed all the table with the following commands:

exec dbms_stats.gather_table_stats(ownname=> 'PMSDB', tabname=> 'CHESSDTL', cascade=>true,method_opt=>'for all columns size 250');


exec dbms_stats.gather_table_stats(ownname=> 'PMSDB', tabname=> 'chessdtlout', cascade=>true,method_opt=>'for all columns size 250');

exec dbms_stats.gather_table_stats(ownname=> 'PMSDB', tabname=> 'consollist', cascade=>true,method_opt=>'for all columns size 250');

I also changed the optimizer mode to all_rows.

This is the new query plan:

8 SELECT STATEMENT
7 SORT [UNIQUE]
6 NESTED LOOPS
4 NESTED LOOPS
1 PMSDB.CONSOLLIST##PFCONS1 INDEX [RANGE SCAN]
3 PMSDB.CHESSDTL TABLE ACCESS [BY INDEX ROWID]
2 PMSDB.CHESSDTL##CHESSDTLKEY5 INDEX [RANGE SCAN]
5 PMSDB.CHESSDTLOUT##CHESSDTLOUTKEY1 INDEX [UNIQUE SCAN]


It is doing exactlty the same plan as using hints. Fantastic.

What does this mean? I thought ALL_ROWS always prefers full scan.
If that is the case, how come it is doing index scan.

WHere do I go from here?

thanks and regards

Ram
Re: What is happening with this sql? [message #121800 is a reply to message #121762] Wed, 01 June 2005 02:20 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Reasons can be (just a suspection), for example:

1) Statistics has not been collected or has been out-dated or
has not been collected for indexes.
2) You data distribution needs histograms for CBO right working.

Rgds.
Re: What is happening with this sql? [message #121863 is a reply to message #121238] Wed, 01 June 2005 09:02 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Yep (to dmitry). More and more, as CBO gets better with each release, it seems that the number one cause of poor query performance is a lack of quality statistics.

To Ram: All_rows does not automatically mean use full table scan. It simply is a directive to the CBO (in a way like a hint), telling the CBO, "Hey, I really need you to retrieve all of the data for this query. I know it will take longer than if you just got me the first 100 rows, but I need all of the rows, so go ahead and optimize this to get me all the data. Of course, I still want you to use whatever means (plan) you feel appropriate given the information (statistics) you have on my data."
Re: What is happening with this sql? [message #121887 is a reply to message #121238] Wed, 01 June 2005 11:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In addition to dmitry and smartin's valuable advices: be very careful before you change the optimizer mode of your complete db/session. Maybe this one statement performs better, but changing the optimizer mode can have _serious_ impact on other (uptil now well performing) queries !

hth
Re: What is happening with this sql? [message #122319 is a reply to message #121887] Sun, 05 June 2005 21:41 Go to previous messageGo to next message
rramanat
Messages: 7
Registered: May 2005
Location: Sydney
Junior Member
Hi All

thanks for your help. It seem to running okay for everything except for this query (I can't tune this query as this is from a package). Vendor doesn't want to change this query as it is a package and runs okay in other places.

SELECT TRAN_NO
FROM TRANHIST T0
WHERE (
U##ASSET >= upper(:1) AND U##ASSET <= upper(:2)
AND
(
(1 = 1 AND TDATE >= :3 AND TDATE <= :4)
OR
(1 = 2 AND SDATE >= :5 AND SDATE <= :6)
OR
(1 = 2 AND EDATE >= :7 AND EDATE <= :8)
OR
(1 = 2 AND DUE_DATE >= :9 AND DUE_DATE <= :10)
)
AND
(1 = 2 OR (U##ACCOUNT >= upper(:11) AND U##ACCOUNT <= upper(:12))
)
AND
(1 = 1 OR (TRAN_NO >= :13 AND TRAN_NO <= :14)
)
AND
(1 = 2 OR (upper(REF1) >= upper(:15) AND upper(REF1) <= upper(:16))
)
AND
(1 = 2 OR (upper(BROKER) >= upper(:17) AND upper(BROKER) <= upper(:18))
)
AND
(1 = 2 OR (upper(CURRCY) >= upper(:19) AND upper(CURRCY) <= upper(:20))
)
AND
(1 = 2 OR (upper(TADV) >= upper(:21) AND upper(TADV) <= upper(:22))
)
AND
(1 = 1 OR upper(USER_ID) = upper(:23)
)
AND
(1 = 2 OR (REVERSED_BY = :24 AND REVERSES = :25)
)
AND
((1 = 1 AND SETTLED = :26) OR (1 = 1 AND (SETTLED = :27 OR (SETTLED = :28 AND SDATE > :29 AND TDATE <= :30)))
)
);

Here is the plan:

11 SELECT STATEMENT
10 FILTER
9 PMSDB.TRANHIST TABLE ACCESS [BY INDEX ROWID]
8 BITMAP CONVERSION [TO ROWIDS]
7 BITMAP AND
3 BITMAP CONVERSION [FROM ROWIDS]
2 SORT [ORDER BY]
1 PMSDB.TRANHIST##ACCOUNT INDEX [RANGE SCAN]
6 BITMAP CONVERSION [FROM ROWIDS]
5 SORT [ORDER BY]
4 PMSDB.TRANHIST##ASSET_ONLY INDEX [RANGE SCAN]

It takes about 3+ mins.


The same query, if we substitute the exact values (using 10046 trace and get the values), comes out with a diff plan like this:

SELECT TRAN_NO
FROM TRANHIST T0
WHERE (
U##ASSET >= upper('AMP0249A') AND U##ASSET <= upper('AMP0249A')
AND
(
(1 = 1 AND TDATE >= to_date('11/30/2004 00:00:00','mm/dd/yyyy hh24:mi:ss') AND TDATE <= to_date('11/30/2004 23:59:59','mm/dd/yyyy hh24:mi:ss'))
OR
(1 = 2 AND SDATE >= to_date('11/30/2004 00:00:00','mm/dd/yyyy hh24:mi:ss') AND SDATE <= to_date('11/30/2004 23:59:59','mm/dd/yyyy hh24:mi:ss') )
OR
(1 = 2 AND EDATE >= to_date('11/30/2004 00:00:00','mm/dd/yyyy hh24:mi:ss') AND EDATE <= to_date('11/30/2004 23:59:59','mm/dd/yyyy hh24:mi:ss'))
OR
(1 = 2 AND DUE_DATE >= to_date('11/30/2004 00:00:00','mm/dd/yyyy hh24:mi:ss') AND DUE_DATE <= to_date('11/30/2004 23:59:59','mm/dd/yyyy hh24:mi:ss'))
)
AND
(1 = 2 OR (U##ACCOUNT >= upper(' ') AND U##ACCOUNT <= upper('zzzzzzzzzzzzzzz'))
)
AND
(1 = 1 OR (TRAN_NO >= 0 AND TRAN_NO <= 0)
)
AND
(1 = 2 OR (upper(REF1) >= upper(' ') AND upper(REF1) <= upper('zzzzzzzzzzzzzzz'))
)
AND
(1 = 2 OR (upper(BROKER) >= upper(' ') AND upper(BROKER) <= upper('zzzzzzzzzzzzzzz'))
)
AND
(1 = 2 OR (upper(CURRCY) >= upper(' ') AND upper(CURRCY) <= upper('zzzzzzzzzzzzzzz'))
)
AND
(1 = 2 OR (upper(TADV) >= upper(' ') AND upper(TADV) <= upper('zzzzzzzzzzzzzzz'))
)
AND
(1 = 1 OR upper(USER_ID) = upper(' ')
)
AND
(1 = 2 OR (REVERSED_BY = 0 AND REVERSES = 0)
)
AND
((1 = 1 AND SETTLED = 1) OR (1 = 1 AND (SETTLED = 0 OR (SETTLED = 1 AND SDATE > to_date('6/1/2005 23:59:59','mm/dd/yyyy hh24:mi:ss')
AND TDATE <= to_date('11/30/2004 23:59:59','mm/dd/yyyy hh24:mi:ss'))))
)
);



3 SELECT STATEMENT
2 PMSDB.TRANHIST TABLE ACCESS [BY INDEX ROWID]
1 PMSDB.TRANHIST##ASSET INDEX [SKIP SCAN]

It takes 2 secs to run.

The table is analyzed with:

exec dbms_stats.gather_table_stats(ownname=> 'PMSDB', tabname=> 'tranhist', cascade=>true,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 250');

I am fully confused. Because, bind variables are the in thing with Oracle and supposed to come out with the best plan.

It is 9.2.0.4 and the index details are:

TRANHIST##ASSET index:

U##MODULE, U##ASSET, TDATE, U##TYPE, TRAN_NO

TRANHIST##ACCOUNT INDEX:

U##ACCOUNT, SDATE, TRAN_NO

TRANHIST##ASSET_ONLY index:

U##ASSET, PROGRESS_RECID.

What else can I do to get the best sql out of bind variable sql to choose the best plan.

Thanks again for all your help.
Re: What is happening with this sql? [message #122710 is a reply to message #122319] Wed, 08 June 2005 02:48 Go to previous messageGo to next message
rramanat
Messages: 7
Registered: May 2005
Location: Sydney
Junior Member
All

I tried with the parameter OPTIM_PEEK_USER_BINDS=FALSE and it didn't fix the problem as well.

I created an outline with the better response plan and it works fine. But I am still confused as to why the bind variables SQL didn't pick up that plan. Any help would be much appreciated.

regards

Ram
Re: What is happening with this sql? [message #122792 is a reply to message #121238] Wed, 08 June 2005 09:33 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
My understanding is that bind variable peeking only kicks in on the initial (hard) parse of the query. Subsequent (soft) parses will not continue to peek, but rather use the existing plan in the shared pool. So when testing this (not in production), you may need to flush the shared pool to get a fresh hard parse to investigate the peeking.

You also have to realize that bind variables are not a magical cure for all situations. They are useful in that they help oracle share sql statements so that those statements can be executed next time without the full expense of a hard parse. This is useful when you are running tons of statements that each take a small amount of time to actually execute. In those cases, the parse time to execute time is relatively high.

But if a query takes a minute to execute, then the parse time on that is a very small relative percentage. So in that particular case, bind variables may not be the best way to go.
Re: What is happening with this sql? [message #122795 is a reply to message #121238] Wed, 08 June 2005 09:39 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Oh and also if the values you are binding don't or rarely change, then that is another reason to (at least consider) not use bind variables.

And be careful with bind variable peeking and autotrace. It doesn't always know. Rely more on sql trace true and tkprof.
Re: What is happening with this sql? [message #122874 is a reply to message #122795] Wed, 08 June 2005 20:10 Go to previous message
rramanat
Messages: 7
Registered: May 2005
Location: Sydney
Junior Member
Hi

In my case, I flushed the pool before running this query. Also, the values for those bind variables will change all the time and this particular query gets executed 100 times a day.

I did not use autotrace. I used sql trace and tkprof to get the plan.

Metalink doesn't have good info on this as well.

We can't change the query also as this is a vendor supplied package.

I just want to know anyone else faced the same problem and found a "cure".

Thanks once again to SMARTIN, Dmitry, Frank and pscjhe
for responding to this query. Much appreciated.

Please give your ideas on this problem and I will try it out.

regards

Ram
Previous Topic: select large amount of data
Next Topic: Index Question
Goto Forum:
  


Current Time: Sun Jan 17 12:20:54 CST 2021