Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 25, 2002 - 12:51 am UTC

Last updated: July 01, 2013 - 5:11 pm UTC

Version: 817

Viewed 10K+ times! This question is

You Asked

Hello,
Wish you a Merry christmas and Happy holidays.

1)When tuning an sql statement in plsql when should Hints be applied ? How do we know what Hint to apply and when see point 2 please .We use CBO.We are doing fresh development.
2) We have a Developer who by default starts with writing hints in a select statement. Is this approach correct. I think it is not because we are still in development and the database is empty only schema defined. so even if we analyze the tables I dont think so it will be guranteed that Oracle will use that particular hint.
Even if it does in development what is the gurantee it will also use in Production. I think this may be his publicity Stunt
3)Unless we use TKproff and explain plan , how can we make a decision .Still is it always necessary to spoon feed oracle with hints ? what must be the criteria to specify Hints ?How do we know that Oracle did use our supplied Hints ?

Thanx



and Tom said...

1) Never. They are the path of last resort.

In Oracle Applications development (11i apps - HR, CRM, etc) Hints are strictly forbidden. We find the underlying cause and fix it.

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6601251003901

for some really important information that will make the use of hints obsolete (as gartner would say "95% probability")


2) NO, not at all.  

Tell me -- can this developer read a query plan and say "yup, that is the best plan for all sizes of data" (hey, wait -- thats what the RBO did!!).  No, they cannot.  See
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6749454952894 <code>

This is "joe or josephine" developer -- thinks the CBO is broken because it full scans a table. Well, that example just speaks for itself.

If you are using the CBO, you MUST analyze your tables.

Also -- in development -- you MUST develop again real, representative data -- or you will fail (your rollout will be very very painful -- 99.999% probability)

3) NO, NO, NO. do not use hints. That will be your last choice -- never the first choice and never standard operating procedure.

The problem is -- we may well use your hints and your hints are just *wrong*, slower, precluding us from taking a better path.

Hints -- only useful if you are in RBO and you want to make use of an access path.

FIRST_ROWS is a hint I sometimes use in pagination queries (getting rows M thru N) but that is about it.

Hints -- something I use to test a theory, but not in real code.




Rating

  (96 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Now Its clear , I need no more Hints for when/ how to use Hints

A reader, December 25, 2002 - 11:30 am UTC

Thanx for your crystal clear and simple explanation.
U R Great.

Tom, why not use views?

Sam, December 25, 2002 - 6:25 pm UTC

View customized for the part of business logic
For example tables are:
ACCOUNTS
SALES
ITEMS


Views are:
Account_Sales
Account_Sale_Details

Now you can let developers use views and add hints based
on most benifitting business case.
If need more add more views.

So developers can work without interruption. DBAs are happy.
Code need not be recompiled either. Of course on the fly
performance change can be achieved.

what do you think?

thanks

Tom Kyte
December 26, 2002 - 11:20 am UTC

I think you should not be using hints....

Hints used in some cases

A reader, December 25, 2002 - 11:21 pm UTC

Hi Tom

We have to use the hints in these cases -

1. When joining a global temporary table with normal tables. Temporary tables cannot be analyzed, so we sometimes need to use hints.

2. I need your advice on this -
We have a staging database where the data is inserted, processed, pushed to production and then finally purged. This is an automatic activity as we keep receiving files - some with less data some with large amount of data. We have no clue to how much data we receive.

So in these cases we sometimes use hints as we do not analyze the tables. The queries are also not very complex. Max number of tables in a join is 3.

Is it a good idea to switch on monitoring and gather statistics every time we load a file?

Thanks


Tom Kyte
December 26, 2002 - 11:26 am UTC

1) dbms_stats.set_table_stats does that dandy....


2) analyze the tables then??

Otherwise you are telling me that the plans that work for 5 rows work great for 500,000 rows -- which is quite simply "not true"....


You don't even need monitoring in this case, just analyze as part of the load process - you KNOW when the stats are invalid.




Informative Explanation

Yogeeraj, December 26, 2002 - 12:53 am UTC

hello,

Maybe your next book for next year (after the performance tuning one) should be on "Writing efficient SQL". Something specialized with lots of case studies and guidelines.

This would definitely benefit the entire community here!

Happy New Year 2003

Best Regards
Yogeeraj

How about data warehouse application?

steve, December 26, 2002 - 11:59 am UTC

Hi Tom,

For data warehouse application, especially in creating
a report, it involved joins among five or more huge tables
(Huge table, I mean, contains ten to hundred million rows of records ), and it take hours to run.
1) In this case, should we still depend on CBO? or we have to use the Hints?

2) We have some queries which they runs more than an hour without hint, and they runs ten minutes with the hints. So each time, when I write a query if it runs longer than we expected I was told by myself or my boss to try the hints. The question is how can I tell there still is room for tuning based on tables' size, rows return, LIO and PIO.

3)I'd like to konw what is your experience to judge a query is almost optimized based on the data from TKPROF or autotrace. So I can reply to my boss, the query is no room to tune

Thank you!




Tom Kyte
December 26, 2002 - 1:34 pm UTC


1) you should ESPECIALLY rely on the CBO in that case.

2) you should try to investigate the underlying cause where -- bad stats? missing stats? need histograms? optimizer_index_cost_adj set wrong? what caused the optimizer which works on mathematical principled to come to the wrong conclusion?

3) when it meets the performance criteria laid out before the query itself was designed. otherwise you are suffering from CTD -- compulsive tuning disorder -- with no end in sight.

Worthy of note...

Connor, December 28, 2002 - 5:45 am UTC

Just to note for anyone reading that

"Hints -- only useful if you are in RBO and you want to make use of an access path."

will immediately invoke the cost optimizer (in which case it may be useful to have stats on your tables anyway)

Woot!

Chuck Jolley, January 16, 2003 - 10:05 am UTC

Well, I read this and started experimenting.
We now do:
dbms_utility.analyze_schema(ar_schema, 'COMPUTE', null, null, 'FOR TABLE FOR ALL COLUMNS FOR ALL INDEXES');
for our two main active schemas every sunday morning while the office is closed and only a few web serfers are hitting the database.
It takes about 3 hours to run.
What an improvement!
ALL the SQL that needed hints while we were RULE based, and ALL the SQL that defaulted to bad hash joins when we went to CHOOSE, run correctly now without hints.
Too Cool.

Thanks,
Chuck

hints in pl/sql

Scott, May 14, 2003 - 3:40 am UTC

G'day Tom

Speaking of when to use hints and last resorts...
I have a distributed query over 2 tables / 2 databases and I used DRIVING_SITE - one hint you recommend not to completely dismiss...
In SQL*Plus, this changed my 30 second query to 1 second.
But as soon as I put the query in my package, or test it in a declare/begin/end anonymous block (using implicit and explicit cursor), the optimiser doesn't seem to use the hint (takes 30 seconds).

Any reason why the hint would not be used in PL/SQL?

declare
v number;
begin
SELECT /*+DRIVING_SITE(s)*/
count(*)
into v
FROM ths_sales s --(remote table)
,trd_sales_rep_customers c
WHERE s.fin_yr = 2003
AND s.fin_mth = 1
AND s.state_code = 'WA'
AND c.sales_rep_nbr = 3
AND s.acc_nbr = c.acc_nbr;
dbms_output.put_line(v);
end;

Tom Kyte
May 14, 2003 - 7:11 am UTC

depending on the version, you may well need a space after the /*+

use tkprof and see if the hint is turned into

select /*+RIVING_SITE(s)*/


if so, you need a space. tkprof -- it never lies.

never mind

Scott, May 14, 2003 - 3:57 am UTC

G'day

Never mind. After a few hours investigating, I did a tkprof and remembered what Penny Cookson taught me - In PL/SQL, leave a space between /*+ and the hint...

I just wanna kick myself.

Should I use a hint here

A reader, December 29, 2003 - 2:06 pm UTC

I have a table T with 1.2 Million rows. It has a column COMPACT_NAME with 366,560 distinct values. There is a nonuniqe index on this column.

In trying to tune the most I/O-intensive queries I find many of them are accessing this table by this column with a like clause.

Example:
Select last_name from t where compact_name like 'SLRGI%';

Gives the following Trace info

LAST_NAME
-----------------------------------
SLRG INC
S L R G INC


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1798 Card=6544 Bytes
=189776)

1 0 TABLE ACCESS (FULL) OF 'T_SOS_503' (Cost=1798 Card=6544 By
tes=189776)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18537 consistent gets
18528 physical reads
0 redo size
346 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

However
Select last_name from t where compact_name like 'SLRGIN%';

Gives the following Trace info
S3_LAST_NAME
-----------------------------------
SLRG INC
S L R G INC


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1662 Card=2621 Bytes
=76009)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_SOS_503' (Cost=1662 Ca
rd=2621 Bytes=76009)

2 1 INDEX (RANGE SCAN) OF 'S3_COMPACT_NAME_IDX' (NON-UNIQUE)
(Cost=12 Card=2621)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
5 physical reads
0 redo size
346 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

I have:

analyze table t
compute statistics for table for all indexes;

and

exec dbms_stats.gather_table_stats( user, 'T', cascade => TRUE);

I still get the same results on the queries.

Any suggestions?

Tom Kyte
December 29, 2003 - 2:41 pm UTC

whoa there.. you are saying:

Select last_name from t where compact_name like 'SLRGIN%';

Gives the following Trace info
S3_LAST_NAME
-----------------------------------
SLRG INC
S L R G INC

something smells fishy here.

that would be a BUG would it not -- if that second row came out? are we seeing "reality" here?

More info

A reader, December 29, 2003 - 3:15 pm UTC

Sorry different field in select, this might help, it shows same issue.

select last_name,compact_name
from
t
where
compact_name like 'SLRG%'
/

LAST_NAME COMPACT_NAME
----------------------------------- --------------------
SLRG INC SLRGINC
S L R G INC SLRGINC


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1798 Card=23564 Byte
s=730484)

1 0 TABLE ACCESS (FULL) OF 'T_SOS_503' (Cost=1798 Card=23564 B
ytes=730484)





Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18537 consistent gets
18523 physical reads
0 redo size
413 bytes sent via SQL*Net to client
495 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

Tom Kyte
December 29, 2003 - 6:12 pm UTC

try this:

analzye table t compute statistics for columns compact_name size 225;

and lets see what that does for us. Consider (a 1,000,000 row table)

big_table@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'BIG_TABLE', cascade=>true );

PL/SQL procedure successfully completed.

big_table@ORA9IR2>
big_table@ORA9IR2> set autotrace traceonly explain
big_table@ORA9IR2> select count(*) from big_table where object_name like 'ABC%';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'BIG_TABLE_IDX' (NON-UNIQUE) (Cost=3 Card=57 Bytes=1368)



big_table@ORA9IR2> select count(*) from big_table where object_name like 'jav%';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=24)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'BIG_TABLE_IDX' (NON-UNIQUE) (Cost=3 Card=57 Bytes=1368)



big_table@ORA9IR2> analyze table big_table compute statistics for columns object_name size 225;

Table analyzed.

big_table@ORA9IR2> select count(*) from big_table where object_name like 'ABC%';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=23)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'BIG_TABLE_IDX' (NON-UNIQUE) (Cost=3 Card=64 Bytes=1472)



big_table@ORA9IR2> select count(*) from big_table where object_name like 'jav%';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=413 Card=1 Bytes=23)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'BIG_TABLE_IDX' (NON-UNIQUE) (Cost=413 Card=84444 Bytes=1942212)



big_table@ORA9IR2> set autotrace off
big_table@ORA9IR2> select count(*) from big_table where object_name like 'ABC%';

COUNT(*)
----------
0

1 row selected.

big_table@ORA9IR2> select count(*) from big_table where object_name like 'jav%';

COUNT(*)
----------
89964

1 row selected.


The cardinalities are computed a little better with the histograms here (right now you have size 1, insufficient). It is "guessing" wrong.



The hints

Zoran Matic, December 30, 2003 - 7:45 am UTC

Hi Tom,

I agree with you about hints.
Just to say a few more things:

1. Oracle did not get rid of hints becouse CBO is not perfect
2. Sometimes if you know that some critical SQL will be performed only in one way and using huge tables (we have Terabyte OLTP tables), it is easier to fix it with the hints (complete hints of course) then to rely on the procedure to set/gather table statistics by DBA's or automatically. Huge tables are usually partitioned.
Oracle CBO way in this cases is to invest in gathering proper statistics or setting that will take a lot of time of some very good DBA/Developer. What you should do?
3. Oracle recommended to not use hints in Oracle Apps 11i because much more developers does not know to write hints then to write the complete hint, even developers from Oracle. Also this is the generic application for 1000's of companies and many SQL's need to execute differently dependant on the data skew with different column histograms and sizes. In this case hints are bad.
May way of thinking, maybe wrong.
4. Hints are advanced thing that somebody should understand before try to use it at all. As we said one time everything is good if you are using that in the proper case.
5. You do not want to gather statistics for some huge tables because you do not want to waste hardware resources.
Also estimating stats are not 100% secure for some 100% important SQL's. For that you have set statistics option but in this case you are playing partly blind game, relying on your knowledge of future data (not easy in some cases). It looks easier to use hints in some particular cases.

At the end as you said in 99% SQL's you probably do not need hints, but Oracle left it to cover some CBO imperfections, DBA laziness, developer doubts, ...
At the end hints are funny because you can understand which kind of crazy things CBO is using.

Does Oracle 10g have hints? I am sure YES.
Why? Is it for that 1% (maybe less but never mind) cases you need it?

I am going back to play chess, I have got headache.

Regards,
Zoran


Tom Kyte
December 30, 2003 - 10:28 am UTC

2) that assumes you have 100% static sql - else you need to invest in the gathering of proper stats for all of your ad-hoc.

3) i think developers can figure out how to write proper hints. the problem isn't that hints are hard to code (unfortunately, they are far far far too easy to code). It is that you are doing the optimizers job, you won't be able to take advantage of new things in the future and you are hand tuning each and every single sql statment in the system. the more correct approach is to make it so the optimizer has sufficient inputs to produce good outputs. then you don't have to hand tune each and every query.

4) they are not really that advanced at all.

5) disagree about 1,000% on that one.

Hints

Zoran Martic, December 30, 2003 - 10:57 am UTC

I am just saying that hints are sometimes very useful.

3) I did not mention this for every SQL in your application. I said a few times 1% cases (maybe less). Important SQL's that should never execute slow if possible.

What do you mean by new functionality in the future?

If I have some particular SQL that should run that way for many years which new functionality I should use?
I am not that sure that it is a big deal to change a few critical SQL's from time to time. We are doing code changes in any case from time to time. You should just be aware of hints.

4) if you say that I agree, this was just my opinion

5) again in some cases like huge growing OLTP table partitioned per days or hours.
Are you saying that gathering statistics here is not wasting resources, reducing scalability?
If you do not need to do this why should your collecting statistics will be better then to put a few hints into a few SQL's (not all).

Are you using hints at all?
Are you 100% for not using hints at all?
What about bind peeking imperfection?
Do we have hints in 10g? Why?
Why there is not dessuport for hints as Oracle was saying it will be the case in 9i?
It looks to me that there are more and more hints, to not say optimizer hidden parameters.

Regards,
Zoran

Tom Kyte
December 30, 2003 - 11:50 am UTC

3) if i used a hint to tell the optmizer how to process a query in 8i -- and along comes 9i with a new index skip scan access path -- I will not be able to take advantage of it as the hint is saying "do it this way" -- removing other possible access paths from consideration.

you lose the ability to take advantage of better plans over time.

5) I put hints into two catagories (documented this in my new book effective Oracle by design)

good hints give the optimizer more information -- not directions on what to do. good hints include

o first rows
o all rows
o driving site
o cardinality
o optimizer dynamic sampling

for example -- they give the optimizer a clue as to what to do. hints that specify access paths -- i use them to prove the optimizer got it wrong and then try to figure out "why" it got it wrong.

I use hints -- the "good ones". I am loathe to use the others.

More info on question on when to use hints

A reader, December 30, 2003 - 12:20 pm UTC

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL> create table t1 as select * from t_sos_501;

Table created.
SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------
 S1_OPERATION                                       VARCHAR2(1)
 S1_STATUS                                          VARCHAR2(1)
 S1_UCC1_NUMBER                                     VARCHAR2(9)
 S1_UCC2_NUMBER                                     VARCHAR2(9)
 S1_FILE_DATE                                       VARCHAR2(10)
 S1_FILE_TIME                                       VARCHAR2(6)
 S1_LAPSE_DATE                                      VARCHAR2(10)
 S1_ROLL                                            VARCHAR2(4)
 S1_FRAME                                           VARCHAR2(5)
 S1_NUMBER_OF_PAGES                                 VARCHAR2(5)
 S1_IMAGE_ID                                        VARCHAR2(15)
 S1_ELECTRONIC_FILING                               VARCHAR2(1)

SQL> 
SQL> create table t3 as select * from t_sos_503;

Table created.

SQL> desc t3
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 S3_OPERATION                                       VARCHAR2(1)
 S3_STATUS                                          VARCHAR2(1)
 S3_UCC1_NUMBER                                     VARCHAR2(9)
 S3_UCC2_NUMBER                                     VARCHAR2(9)
 S3_SEQUENCE                                        VARCHAR2(3)
 S3_TYPE                                            VARCHAR2(1)
 S3_LAST_NAME                                       VARCHAR2(35)
 S3_NAME2                                           VARCHAR2(35)
 S3_NAME3                                           VARCHAR2(35)
 S3_NAME4                                           VARCHAR2(35)
 S3_NAME5                                           VARCHAR2(35)
 S3_FIRST_NAME                                      VARCHAR2(35)
 S3_MIDDLE_NAME                                     VARCHAR2(35)
 S3_ADDRESS1                                        VARCHAR2(35)
 S3_ADDRESS2                                        VARCHAR2(35)
 S3_ADDRESS3                                        VARCHAR2(35)
 S3_ADDRESS4                                        VARCHAR2(35)
 S3_CITY                                            VARCHAR2(30)
 S3_STATE                                           VARCHAR2(2)
 S3_ZIP                                             VARCHAR2(9)
 S3_SSN                                             VARCHAR2(9)
 S3_COMPACT_NAME                                    VARCHAR2(20)
 S3_PREFIX                                          VARCHAR2(15)
 S3_SUFFIX                                          VARCHAR2(15)

SQL> 
SQL> create index t1_ucc1_number_idx on t1(s1_ucc1_number);

Index created.

SQL> 
SQL> create index t3_ucc1_number_idx on t3(s3_ucc1_number);

Index created.

SQL> 
SQL> create index t3_compact_name_idx on t3(s3_compact_name);

Index created.

SQL> 
SQL> analyze table t1 compute statistics for table for all indexed columns;

Table analyzed.

SQL> 
SQL> analyze table t3 compute statistics for table for all indexed columns;

Table analyzed.

HERE IS THE CARDINALITY INFO FOR T1 THAT I THINK YOU WOULD BE INTERESTED IN.

SQL> select count(*) from t1;

  COUNT(*)
----------
    979511
SQL> select count(*)
  2  from
  3  (select distinct s1_ucc1_number from t1);


  COUNT(*)
----------
    548614
  1  select max(cnt)
  2  from
  3  (
  4  select s1_ucc1_number,count(*) cnt
  5  from t1
  6  group by s1_ucc1_number
  7* )
SQL> /

  MAX(CNT)
----------
        82

HERE IS THE CARDINALITY INFO FOR T3 THAT I THINK YOU WOULD BE INTERESTED IN.

SQL> select count(*)
  2  from
  3  (select distinct s3_ucc1_number from t3);

  COUNT(*)
----------
    548625

  1  select count(*)
  2  from
  3* (select distinct s3_compact_name from t3)
SQL> /

  COUNT(*)
----------
    366560

  1  select max(cnt)
  2   from
  3   (
  4   select s3_ucc1_number,count(*) cnt
  5   from t3
  6    group by s3_ucc1_number
  7* )
SQL> /

  MAX(CNT)
----------
       298

 1  select count(*)
 2  from
 3  (
 4   select s3_compact_name,count(*) cnt
 5   from t3
 6    group by s3_compact_name
 7  having count(*)>500
 8* )
SQL> /

  COUNT(*)
----------
        15
  1  select count(*)
  2  from
  3  (
  4   select s3_compact_name,count(*) cnt
  5   from t3
  6    group by s3_compact_name
  7  having count(*)<10
  8* )
SQL> /

  COUNT(*)
----------
    345439

NOW WITHOUT USING HISTOGRAMS

SQL> set autotrace on
SQL> get q4
  1  SELECT /*+ RULE */
  2   S3_LAST_NAME,
  3   S3_COMPACT_NAME DE_COMPACT_NAME
  4  FROM KS.T3, KS.T1 t1
  5  WHERE
  6  s3_compact_name like :a||'%'
  7  AND S3_UCC1_NUMBER = t1.S1_UCC1_NUMBER
  8* AND t1.S1_STATUS IS NULL
SQL> /

S3_LAST_NAME                        DE_COMPACT_NAME
----------------------------------- --------------------
SLRG INC                            SLRGINC
S L R G INC                         SLRGINC


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T3'
   4    3         INDEX (RANGE SCAN) OF 'T3_COMPACT_NAME_IDX' (NON-UNI
          QUE)

   5    2       INDEX (RANGE SCAN) OF 'T1_UCC1_NUMBER_IDX' (NON-UNIQUE
          )





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> 
SQL> get q5
  1  SELECT
  2   S3_LAST_NAME,
  3   S3_COMPACT_NAME DE_COMPACT_NAME
  4  FROM KS.T3, KS.T1 t1
  5  WHERE
  6  s3_compact_name like :a||'%'
  7  AND S3_UCC1_NUMBER = t1.S1_UCC1_NUMBER
  8* AND t1.S1_STATUS IS NULL
SQL> /

S3_LAST_NAME                        DE_COMPACT_NAME
----------------------------------- --------------------
SLRG INC                            SLRGINC
S L R G INC                         SLRGINC


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1128 Card=50481 Byte
          s=2675493)

   1    0   HASH JOIN (Cost=1128 Card=50481 Bytes=2675493)
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=1010 Card=48976 Bytes=
          538736)

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=10 Card=6026
          9 Bytes=2531298)

   4    3       INDEX (RANGE SCAN) OF 'T3_COMPACT_NAME_IDX' (NON-UNIQU
          E) (Cost=2 Card=2)





Statistics
----------------------------------------------------------
         42  recursive calls
          0  db block gets
      10356  consistent gets
      10544  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed


NOW WITH USING HISTOGRAMS

SQL> analyze table t3 compute statistics for table for columns s3_compact_name size 225;

Table analyzed.

  1  SELECT
  2   S3_LAST_NAME,
  3   S3_COMPACT_NAME DE_COMPACT_NAME
  4  FROM KS.T3, KS.T1 t1
  5  WHERE
  6  s3_compact_name like :a||'%'
  7  AND S3_UCC1_NUMBER = t1.S1_UCC1_NUMBER
  8* AND t1.S1_STATUS IS NULL
SQL> /

S3_LAST_NAME                        DE_COMPACT_NAME
----------------------------------- --------------------
SLRG INC                            SLRGINC
S L R G INC                         SLRGINC


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1128 Card=50481 Byte
          s=2675493)

   1    0   HASH JOIN (Cost=1128 Card=50481 Bytes=2675493)
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=1010 Card=48976 Bytes=
          538736)

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=10 Card=6026
          9 Bytes=2531298)

   4    3       INDEX (RANGE SCAN) OF 'T3_COMPACT_NAME_IDX' (NON-UNIQU
          E) (Cost=2 Card=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10352  consistent gets
      10446  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>analyze table t3 compute statistics for table for columns s3_compact_name size 100;

SQL> get q5
  1  SELECT
  2   S3_LAST_NAME,
  3   S3_COMPACT_NAME DE_COMPACT_NAME
  4  FROM KS.T3, KS.T1 t1
  5  WHERE
  6  s3_compact_name like :a||'%'
  7  AND S3_UCC1_NUMBER = t1.S1_UCC1_NUMBER
  8* AND t1.S1_STATUS IS NULL
SQL> /

S3_LAST_NAME                        DE_COMPACT_NAME
----------------------------------- --------------------
SLRG INC                            SLRGINC
S L R G INC                         SLRGINC


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1128 Card=50481 Byte
          s=2675493)

   1    0   HASH JOIN (Cost=1128 Card=50481 Bytes=2675493)
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=1010 Card=48976 Bytes=
          538736)

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=10 Card=6026
          9 Bytes=2531298)

   4    3       INDEX (RANGE SCAN) OF 'T3_COMPACT_NAME_IDX' (NON-UNIQU
          E) (Cost=2 Card=2)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10352  consistent gets
      10446  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> get q4
  1  SELECT /*+ RULE */
  2   S3_LAST_NAME,
  3   S3_COMPACT_NAME DE_COMPACT_NAME
  4  FROM KS.T3, KS.T1 t1
  5  WHERE
  6  s3_compact_name like :a||'%'
  7  AND S3_UCC1_NUMBER = t1.S1_UCC1_NUMBER
  8* AND t1.S1_STATUS IS NULL
SQL> /

S3_LAST_NAME                        DE_COMPACT_NAME
----------------------------------- --------------------
SLRG INC                            SLRGINC
S L R G INC                         SLRGINC


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T3'
   4    3         INDEX (RANGE SCAN) OF 'T3_COMPACT_NAME_IDX' (NON-UNI
          QUE)

   5    2       INDEX (RANGE SCAN) OF 'T1_UCC1_NUMBER_IDX' (NON-UNIQUE
          )





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> 

I can't seem to get anywhere close to the RULE hint using CBO. Do you have any suggestions, or do I just have developers use hints for these queries?

Thanks Tom 

Tom Kyte
December 30, 2003 - 12:31 pm UTC

uh huh. ok, so we've been talking and talking and talking here -- and this is the first time you show us a bind variable. okey dokey. nothing like looking at something that means nothing in the context of the real question....

try this "first_rows"

more info

A reader, December 30, 2003 - 12:43 pm UTC

I can demonstrate the same thing without the bind variable, just trying to eliminate issues. The top I/O-intensive queries are showing me some with bind variables and some without when accessing this T3 table using S3_COMPACT_NAME and like;

  1  SELECT /*+ FIRST_ROWS */
  2   S3_LAST_NAME,
  3   S3_COMPACT_NAME DE_COMPACT_NAME
  4  FROM KS.T3, KS.T1 t1
  5  WHERE
  6  s3_compact_name like :a||'%'
  7  AND S3_UCC1_NUMBER = t1.S1_UCC1_NUMBER
  8* AND t1.S1_STATUS IS NULL
SQL> /

S3_LAST_NAME                        DE_COMPACT_NAME
----------------------------------- --------------------
SLRG INC                            SLRGINC
S L R G INC                         SLRGINC


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=98962 Card
          =50481 Bytes=2675493)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=2 Card=1 Bytes
          =42)

   2    1     NESTED LOOPS (Cost=98962 Card=50481 Bytes=2675493)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=1010 Card=48976 Byte
          s=538736)

   4    2       INDEX (RANGE SCAN) OF 'T3_UCC1_NUMBER_IDX' (NON-UNIQUE
          ) (Cost=1 Card=2)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     837740  consistent gets
      54689  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>  

Tom Kyte
December 30, 2003 - 12:51 pm UTC

does the bind variable have a minimum length -- that is could :a be null, 1 character, 2 characters -- etc?

any inputs on that?

More

A reader, December 30, 2003 - 12:58 pm UTC

SQL> variable a varchar2(100)
SQL> begin :a := 'SLRG'; end;
  2  /

PL/SQL procedure successfully completed.

  1  SELECT
  2   S3_LAST_NAME,
  3   S3_COMPACT_NAME DE_COMPACT_NAME
  4  FROM KS.T3, KS.T1 t1
  5  WHERE
  6  s3_compact_name like :a||'%'
  7  AND S3_UCC1_NUMBER = t1.S1_UCC1_NUMBER
  8* AND t1.S1_STATUS IS NULL
SQL> /

S3_LAST_NAME                        DE_COMPACT_NAME
----------------------------------- --------------------
SLRG INC                            SLRGINC
S L R G INC                         SLRGINC


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1128 Card=50481 Byte
          s=2675493)

   1    0   HASH JOIN (Cost=1128 Card=50481 Bytes=2675493)
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=1010 Card=48976 Bytes=
          538736)

   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=10 Card=6026
          9 Bytes=2531298)

   4    3       INDEX (RANGE SCAN) OF 'T3_COMPACT_NAME_IDX' (NON-UNIQU
          E) (Cost=2 Card=2)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10352  consistent gets
      10551  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

  1  SELECT /*+ FIRST_ROWS */
  2   S3_LAST_NAME,
  3   S3_COMPACT_NAME DE_COMPACT_NAME
  4  FROM KS.T3, KS.T1 t1
  5  WHERE
  6  s3_compact_name like :a||'%'
  7  AND S3_UCC1_NUMBER = t1.S1_UCC1_NUMBER
  8* AND t1.S1_STATUS IS NULL
SQL> //

S3_LAST_NAME                        DE_COMPACT_NAME
----------------------------------- --------------------
SLRG INC                            SLRGINC
S L R G INC                         SLRGINC


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=98962 Card
          =50481 Bytes=2675493)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=2 Card=1 Bytes
          =42)

   2    1     NESTED LOOPS (Cost=98962 Card=50481 Bytes=2675493)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=1010 Card=48976 Byte
          s=538736)

   4    2       INDEX (RANGE SCAN) OF 'T3_UCC1_NUMBER_IDX' (NON-UNIQUE
          ) (Cost=1 Card=2)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     837740  consistent gets
      54783  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

  1  SELECT /*+ RULE */
  2   S3_LAST_NAME,
  3   S3_COMPACT_NAME DE_COMPACT_NAME
  4  FROM KS.T3, KS.T1 t1
  5  WHERE
  6  s3_compact_name like :a||'%'
  7  AND S3_UCC1_NUMBER = t1.S1_UCC1_NUMBER
  8* AND t1.S1_STATUS IS NULL
SQL> /

S3_LAST_NAME                        DE_COMPACT_NAME
----------------------------------- --------------------
SLRG INC                            SLRGINC
S L R G INC                         SLRGINC


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T3'
   4    3         INDEX (RANGE SCAN) OF 'T3_COMPACT_NAME_IDX' (NON-UNI
          QUE)

   5    2       INDEX (RANGE SCAN) OF 'T1_UCC1_NUMBER_IDX' (NON-UNIQUE
          )





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
         12  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed
 

Tom Kyte
December 30, 2003 - 2:04 pm UTC

does the bind variable have a minimum length -- that is could :a be null, 1
character, 2 characters -- etc?

any inputs on that?


More info

A reader, December 30, 2003 - 3:05 pm UTC

does the bind variable have a minimum length -- that is could :a be null, 1
character, 2 characters -- etc?

any inputs on that?


The bind variable has a minimum length of 1, it could theoretically be 1 character or 2 characters, it is based on user input and after talking to the developers it would almost always ( they felt 90% plus) of the time it would have at least 4 characters. By having them put RULE hint on the querys the users are much happier with the performance of the system. I realize that if they enter 1 character the query probably will run slower with the RULE hint.

I would like very much to use the CBO but in the end user satisfaction with the system is more important than my wants. I am also concerned about the potential for RBO to not be available in future release (10g?).

If you have any suggestions on how I can get the same overall performance using CBO, I would love to hear them.

Tom Kyte
December 30, 2003 - 3:22 pm UTC

if you add to the predicate

and substr( s3_compact_name,1,1) = substr(:a,1,1)

what happens to the plan (using first_rows)

You're the best

A reader, December 31, 2003 - 9:45 am UTC

SQL> variable a varchar2(10);
SQL> begin :a := 'SLRG'; end;
  2  /

PL/SQL procedure successfully completed.

SQL>  SELECT 
  2     S3_LAST_NAME,
  3     S3_COMPACT_NAME DE_COMPACT_NAME
  4     FROM KS.T3, KS.T1 t1
  5    WHERE
  6    s3_compact_name like :a||'%'
  7    AND S3_UCC1_NUMBER = t1.S1_UCC1_NUMBER
  8    AND t1.S1_STATUS IS NULL
  9  and substr( s3_compact_name,1,1) = substr(:a,1,1);

S3_LAST_NAME                        DE_COMPACT_NAME
----------------------------------- --------------------
SLRG INC                            SLRGINC
S L R G INC                         SLRGINC


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=943 Card=615 Bytes=2
          9520)

   1    0   MERGE JOIN (Cost=943 Card=615 Bytes=29520)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=826 Card=489
          76 Bytes=538736)

   3    2       INDEX (FULL SCAN) OF 'T1_UCC1_NUMBER_IDX' (NON-UNIQUE)
           (Cost=26 Card=979511)

   4    1     SORT (JOIN) (Cost=117 Card=603 Bytes=22311)
   5    4       TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=112 Card=6
          03 Bytes=22311)

   6    5         INDEX (RANGE SCAN) OF 'T3_COMPACT_NAME_IDX' (NON-UNI
          QUE) (Cost=41 Card=25)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      99382  consistent gets
      12398  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> ed
Wrote file afiedt.buf

  1  SELECT /*+ FIRST_ROWS */
  2     S3_LAST_NAME,
  3     S3_COMPACT_NAME DE_COMPACT_NAME
  4     FROM KS.T3, KS.T1 t1
  5    WHERE
  6    s3_compact_name like :a||'%'
  7    AND S3_UCC1_NUMBER = t1.S1_UCC1_NUMBER
  8    AND t1.S1_STATUS IS NULL
  9* and substr( s3_compact_name,1,1) = substr(:a,1,1)
SQL> /

S3_LAST_NAME                        DE_COMPACT_NAME
----------------------------------- --------------------
SLRG INC                            SLRGINC
S L R G INC                         SLRGINC


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1318 Card=
          615 Bytes=29520)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes
          =11)

   2    1     NESTED LOOPS (Cost=1318 Card=615 Bytes=29520)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T3' (Cost=112 Card=6
          03 Bytes=22311)

   4    3         INDEX (RANGE SCAN) OF 'T3_COMPACT_NAME_IDX' (NON-UNI
          QUE) (Cost=41 Card=108)

   5    2       INDEX (RANGE SCAN) OF 'T1_UCC1_NUMBER_IDX' (NON-UNIQUE
          ) (Cost=1 Card=20)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          4  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

Thank you so much Tom.

Can you explain why the FIRST_ROWS hint changes the plan? And what guidance I should give the developers for when to use FIRST_ROWS hint.

And again, you are the best resource for learning anything that I have ever discovered.

Happy New Year 

Tom Kyte
December 31, 2003 - 10:27 am UTC

well, first rows goes for a plan that tries to get the very very first row back to the application ASAP. by weighting the predicate heavier on s3_compact_name, it made that index more appealing for getting the first rows first.

for most 'search' applications -- things that paginate on the web or something -- first rows is very much appropriate.

Here -- the "like :a || '%'" didn't give the optimizer much to go on. it looked at that and said -- well, :a could be null, could be 50 characters -- lets just guess (thats why the cardinality is so far off here, its thinking 615 rows, not 2)

first rows is one of the "good hints" in my book -- one of the "safe" hints. I like to use this substr technique with LIKE whenever possible since most of the time the bind variable does have a minimum length (typically people must enter at least A charcter, if not more). It also prevents people from screwing your search up by putting a '%' in the search field (eg: you don't want them returning EVERY record but if they put % in it will -- but the substr trick subverts that since that is equality and not "like" anymore -- been there, done that -- rather then verify the inputs do not include % and _, we just use the substr trick and say you must enter at least N characters. Else, the system comes to a screeching halt as the end user that enters %%% retrieves EVERY record)

Hints

Zoran Martic, January 05, 2004 - 11:19 am UTC

Tom,

Thanks for your response about hints.
I agree with you what you said.
Your idea is to not use hints or use less constraining hints as much as possible.

I am not using hints in 99% cases but will use in <=1% the complete hints to be sure that Oracle CBO will not "cheat" me. These SQL's are the most important in many cases.
I just have a little bit different attitude when you have some critical SQL's that span fast-growing OLTP tables (partitioned in most cases).
I am trying to optimize my time spent in optimizing some SQL.

In your book in chapter 6, on page 310, you said in the NOTE: do not worry if you cannot replicate my findings exactly. A different setting for WORKAREA_SIZE_POLICY, PGA_AGGREGATE_TARGET, SORT_AREA_SIZE, DB_CACHE_SIZE, DB_FILE_MULTIBLOCK_READ_COUNT, different system loads, and so on may affect the cost estimates of a query.

To not mention system statistics that are very relevant for CBO. Of course Oracle is recommended collecting them and tweaking them during the different workload.
With event 10053 you can see which parameters are in the game for CBO (even some undocumented like _cpu_to_io = 0 that is telling CBO that CPU cost is trivial to IO, or _optimizer_cost_model that is set to CPU in 10g (default IO) for Oracle Apps 11i, to not mention other parameters).
You described in the book the most important of course.

What I am trying to tell is that CBO is not 100% reliable, or from Oracle standpoint it is 100% reliable but you just need to setup your CBO environment correctly (no mathematics here or formula). If there is not mathematics that means CBO is not 100% reliable :)
Of course that CBO is becoming more and more reliable.

1) In some special cases when you know that your SQL on huge tables will execute by some specific path as fast as you want: why putting the hint is wrong then playing the interesting game with all these parameters/stats?

1a) These parameters will be changed from time to time and in this case will maybe impose the CBO environment change that will lead some SQL's to the wrong execution path.
That means sometimes we need to mask hints with using many alter session commands.
Is this true?

2) Are Oracle outlines necessary at all (they are similar as hints to me just it is smarter programatical way of using hints)?
Are you using stored outlines at all?
Do you think that Oracle outlines are good at all?

3) How are you collecting statistics for these huge fast-growing partitioned OLTP tables (usually partitioned by date range)?

4) What is faster for you: put the exact complete hint to get satisfied performances or to tweak parameters/collect statistics (for example some complex SQL does not want to execute the way you want and you need to do something - change parameters/stats or putting hints)?

5) You said in your previous response that you are using only "good hints". Does this mean that even in 0.01% cases you are not using others?

I am using hints very rare, only when necessary or when I am going to spend more time in doing this the different way by tweaking stats/parameters. Also System stats can harm other SQL's that are working in the fastest way.

At the end as you said the great argument, you should beleive in Oracle CBO more because it is smarter then you and will make your SQL's running the fastest way.
Also you should use what is provided to you much more and not invent water, but sometimes you need to direct Oracle CBO like a child to listen what you have to say.
In some small number of cases I will rely on old-fashion do what I am saying to you hints.
Maybe in 10g this will change and I will forget hints forever. Oracle CBO is becoming better and better, just hints are sometimes my work and stats/parameters are customer DBA dependant task.

Your good response can change all my wrong thinking of course.

Thanks for any response in advance.

Regards,
Zoran

Tom Kyte
January 05, 2004 - 1:56 pm UTC

1) i strongly discourage hints because they seem "addictive". once someone uses them for one thing, they seem to go off and use them everywhere. I come down on the side of "don't do that".

2) they can be a make or break difference in 9i and before to "fix" a real "problem" query in a 3rd party app.

3) partition by partition -- not any different then a fast growing single partitioned table really.

4) i collect the right statistics.

5) I cannot recollect any of my code in use that uses what I deem "bad hints". Good hints, yes -- first_rows, use it lots.


<quote>
I am using hints very rare, only when necessary or when I am going to spend more
time in doing this the different way by tweaking stats/parameters.
</quote>

sounds prudent.

<quote>
Also System
stats can harm other SQL's that are working in the fastest way.
</quote>

so can gathering statistics
adding a cpu
increasing ram
having less concurrent users
having more concurrent users

it can also be true that system stats can speed up considerably queries that never performed well, it is something to think about.

Hints

Zoran Martic, January 06, 2004 - 4:06 am UTC

Tom,

Thanks a lot for always good response.

>> 1) i strongly discourage hints because they seem "addictive".
There are too many ways to somebody to overuse some Oracle feature. You talked a lot about overusing partitioning even when there is not any need for them.
I am fighting against partitioning used in some stupid ways for many years. The same apply for OPS/RAC.
Idea is to use it when is good to use it.

2) agree fully. If there is not hints in the SQL you are free to tune that SQL up to the limit with changing the environment for SQL

I can say that there are more things not to use "bad hints" as you numbered to us.
Just to say that sometimes because of Oracle 8.1.6, 8.1.7, 9i R1, 9i R2, 10g you need to play a lot with parameters/stats to achieve sometimes the same thing you can easily with fixing the hints for some critical SQL (<1% of SQL's, for >99% of SQL no need for hints).

Have you ever looked into setting the database parameters for Oracle Applications 11i. And this is Oracle product where Oracle knows everything about database internals. Imagine now some third party product, how easy is to setup the proper environment for all SQL's. Oracle invented first hints and later on stored outlines to help with fixing execution plans for non-perfect CBO.
They are all specific to the database version (you have even different parameters between 8.1.7.4 and other 8.1.7.x)
You know how differently you are collecting statistics between database versions.
It is becoming better and better with new database versions.

Do we have "bad hints" in Oracle Apps 11i?

**********************************************************
But at the end I agree it is better not to use hints or to use just some "good hints" (global hints) as you said.
**********************************************************

Thanks for your very good input.

Regards,
Zoran

_optimizer_mode_force and sql inside pl/sql

Kevin Meade, February 12, 2004 - 12:27 pm UTC

Tom, a good thread, but I got scared recently when I read the following that "not to put words in the author's mouth; but" seems to suggest hints are almost necessary when using sql inside pl/sql. I've been doing oracle work for about 20 years so this blew me away because I didn't know about it. The original author is Garry Robinson, presented also is the original material published in Quest Pipeline newsletter (hope I'm not breaking some copyright). Material from Quest is (like as from you) usually quite reliable so you can imagine my concern.

--------
Compliments of Garry Robinson, (OracleAdvice.com.

Oracle's default behavior dictates that SQL statements embedded in a PL/SQL block are always executed with the ALL_ROWS optimization method. For OLTP applications that rely on packaged stored procedures, this can be bad news for response times. OLTP queries need to return results back to the user as quickly as possible which is why OLTP instances generally have parameter optimizer_mode set to FIRST_ROWS.

There are two ways that you can override this default behavior and force the SQL inside PL/SQL blocks to use the FIRST_ROWS optimization method. The first way is to use a hint when declaring the cursor. For example

cursor C1 is
select /*+ FIRST_ROWS */ empno, name
from emp;

This is fine when you are still developing or testing an application but in most cases it won't be easy or straightforward to change the code in a production database; especially if the application has not been developed in-house. A further disadvantage is that there is no instance parameter that can "turn off" the hint if you decide to call the same procedure from a batch job.
For this reason, it is useful to know about the undocumented parameter _optimizer_mode_force.
In Oracle 8i and 9i, the default setting for this parameter is TRUE, which forces Oracle to ignore the optimizer_mode setting and behave in the following way when executing user recursive SQL (SQL inside a PL/SQL block):

INIT.ORA OPTIMIZER_MODE Mode used inside PLSQL block

RULE RULE
CHOOSE ALL_ROWS
ALL_ROWS ALL_ROWS
FIRST_ROWS ALL_ROWS

Setting _optimizer_mode_force to FALSE allows Oracle to behave in the way you might expect when executing SQL in stored procedures.
--------
I have included above via cut/paste the original material on Quest so I can't give you anything else. My questions are: 1) is this true, 2) is it true for just some forms of sql in pl/sql (i.e. just cursors maybe), 3) what do you say about it?

thanks, Kevin Meade



Tom Kyte
February 12, 2004 - 12:43 pm UTC

I've never used hints other then what I consider "good" hints in plsql. good hints give the optimizer more information (first_rows, all_rows, rewrite, no_rewrite, append -- are good. index, use_nl, hash, etc -- are bad hints IMO)


You would know pretty much IMMEDIATELY if a query needs first_rows (repsonse time would sort of "not be good")

It is rare to even have to hint first/all rows -- especially in 2004 with many apps being web based and not really "interactive" in the fashion of a client/server application (and this caveat above with OLTP only applied to ref cursors being returned as well really!)

Wow, you deliver almost as fast as the chinese takeout around here

Kevin Meade, February 12, 2004 - 1:52 pm UTC

But in your case I want quality not quantity. Not that your response was bad, but it leaves me wanting.

I am with you on "good vs. bad hints", and I am with you on "should be obviously slow if first_rows is needed", but what concerns me is the fact that there is a blatant difference in run environment behaviors between pl/sql and other sql execution mechanisms that has never been made clear to me. All along I was assuming that pl/sql would do the same thing as sql*plus with regards to sql execution (pass it to the database and let it do its thing), but now I don't know. This assumption made it easy to test execution plans. Just use simple "set autotrace on" or other "explain plan" stuff for quick and dirty results. But neither gives plans for sql inside pl/sql. Hence the need to run sql standalone. So in reality they are apples/oranges.

Reading between the lines of your initial response and adding some more reading I have done since reading your initial response, I believe that:

1) Yes, pl/sql does not do the same optimizer environment mapping as sql*plus and so, if I want to ensure an identical plan in pl/sql as I see in a sql*plus test, I must either use first_rows hint, or set _optimizer_mode_force appropriately (I assume =true?).

2) _optimizer_mode_force applies to recursive sql only, but all sql executed from plsql is considered recursive sql and is thus subject to this phenomenon: select into..., simple cursor, ref cursor, for loop cursor, insert/update/delete.

Are these statements accurate? Shoud I set =true or =false?

I don't want to over react. I only want to understand something I did not realize was going on and correct my own testing and tuning methodologies that I have used for many years but which now I may have to consider invalid.

Here is my dilemma: if a junior developer asks me, can you show me how to see the plans for the sql in my package, what do I tell him? Considered a senior oracle guy wherever I go, I am expected to be able to handle this kind of stuff and I don't feel confident anymore just showing him how to hack his code and do autotrace on.

So,

Would you say the two statements above are accurate? Is my only resort to use tkprof? Would you suggest I use the hidden parameter (as much as you and I both don't like this)? Do you have any other advice to add?

humbly yours, (you want eggroll with that?) Kevin Meade

Tom Kyte
February 12, 2004 - 7:19 pm UTC

thing about it this way.

a plsql procedure returns when? after all of its code has been executed. with the EXCEPTION of ref cursors -- it should be optimized for throughput (who cares if the first row takes 5 seconds to get -- if I get the last row in 6 seconds when the alternative is to get the first row in 1 second but the last row in 100 seconds. the end user is waiting for the procedure to FINISH). It makes sense for plsql to default to all_rows type of processing.

I've always found explain plans to be "of great academic interest" but when the rubber meets the road -- there is nothing but nothing that will supplant tkprof. explain plans -- cool, sort of neat, nice. tkprof and reality -- the only thing going.

1) not any more. plsql picks up your optimization goal of rule choose first rows all rows. It used NOT TO, but does not.

if you use choose, choose is akin to "all rows". If you run this script in 8.0 and before:

alter session set optimizer_mode=first_rows;

drop table t;
create table t ( x int );
analyze table t compute statistics;

@trace
select * from t sqlplus;

create or replace procedure p
as
begin
for x in (select * from t plsql)
loop
null;
end loop;
end;
/

exec p


you would find in the tkprof that the "from t sqlplus" was first_rows optimized whereas the "from t plsql" was CHOOSE (in effect 'throughput').

In 8i and beyond, you would find it used first_rows in both.


2) you should leave _ parameters ALONE until and if support says "ok, go ahead"



In short -- PLSQL sql always behaved more or less like sqlplus sql since the default optimizer goal of CHOOSE was used! Only if you set first rows in your session would you discover a difference and only in the older releases.


so, for now -- plsql and "sqlplus sql" are in effect one in the same for all intents and purposes.

I don't really see even that much of a change from the 8.0 to 8.1 behavior in general really -- it would come into play so so infrequently and now works the way you appear to want it to work.


Quick and Dirty...

Brent Pathakis, February 12, 2004 - 2:10 pm UTC

"Here is my dilemma: if a junior developer asks me, can you show me how to see
the plans for the sql in my package, what do I tell him? Considered a senior
oracle guy wherever I go, I am expected to be able to handle this kind of stuff
and I don't feel confident anymore just showing him how to hack his code and do
autotrace on."

Quick and dirty are the key words here ... I would only use set autotrace on for 'quick and dirty' tests. Things in sqlplus (like arraysize, and other's i'm sure.) can affect the results / plans.

For more accurate testing I would have the developer - alter session set sql_trace=true and use tkprof.

Tom Kyte
February 12, 2004 - 7:28 pm UTC

I'd tell him "plans are of great academic interest, the proof is in run time performance.  You nor I can look at a plan as say "tis the best plan ever" so -- use sql_trace and tkprof and see reality"

Honest, that is what I do say.

set autotrace is quick and dirty.

do you know that explain plan and hence autotrace "lie" frequently?  they "guess", they do not have all of the needed information.

tkprof is the ONLY way to really find the REAL plan that would/will be used.



consider:

ops$tkyte@ORA9IR2> create table t ( x varchar2(25) primary key);
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from plan_table;
 
0 rows deleted.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x number
ops$tkyte@ORA9IR2> set autotrace on explain
ops$tkyte@ORA9IR2> alter session set sql_trace=true;
 
Session altered.
 
ops$tkyte@ORA9IR2> select * from t where x = :x;
 
no rows selected
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   INDEX (UNIQUE SCAN) OF 'SYS_C004718' (UNIQUE)
 


<b>looks reasonable eh?  but wait, tkprof says.....</b>


select *
from
 t where x = :x
                                                                                             
                                                                                             
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          3          0           0
                                                                                             
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 101
                                                                                             
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL T (cr=3 r=0 w=0 time=45 us)


<b>so, which one is correct -- answer is tkprof is ALWAYS right, explain plan might be right.</b>

here what happened was the explain plan ASSUMED all binds are varchar's (always, regardless of what you "variable" them as).  Well, char_column_that_is_indexed = :bind_variable_of_char_type can use an index -- so that is the plan it showed.

However the reality is you have char_column = :number_bind and that is the same as  to_number( char_column ) = :number_bind which of course obviates the index access path.


quick and dirty is just that (add bind variable peeking to the mix and it gets even more "gnarly")

If you want the honest to goodness plan, you would:


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2     l_my_input  number;
  3     cursor c is select * from t where x = l_my_input;
  4  begin
  5     open c;
  6     close c;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
and then use tkprof to see:

SELECT *
from
 t where x = :b1
                                                                                             
                                                                                             
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0
                                                                                             
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 101     (recursive depth: 1)
                                                                                             
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL T (cr=0 r=0 w=0 time=0 us)


what it is.
 

Thanks Brent, point well taken

Kevin Meade, February 12, 2004 - 2:53 pm UTC

Thanks Brent, point well taken. I have been bitten by arraysize a couple of times (was not aware of other factors but that don't mean there ain't any as you point out).

So share a little with me. Do you set _optimizer_mode_force in your systems? If so, to what and why? If not, why not?

I believe the affects of _optimizer_mode_force and the behavior of pl/sql in this regard is not well known (maybe its just me), but I asked the other heavies in my workplace and none of them had any idea about it or that pl/sql would vary in this regard. Indeed they all seemed a little agitated that the usual "quick and dirty" may be a little too dirty than we all hade expected. It is a particular problem for us as we make heavy use of pl/sql in our environments.

As for tkprof, well, its great if you can get easy access to it. I know what some people will say (I have read the posts here about this issue and possible ways to work tkprof, and I was hoping to avoid a discussion of this topic), but the fact is not all oracle experiences are the same (a good thing really). Though many oracle people have used tkprof consistently in their careers, many others have worked for outfits that divide IM lines on a hardware basis. In practicality this means that you don't get access to the host systems and you certainly can't run programs on them whenever you like. I work in one such environment. Indeed, every company I have worked for has had similar dividing lines and rule (most were/are bigger companies). This makes it difficult at best to run tkprof and get its results.

I am trying to gauge how important this issue is and if I need to now insist on the ability to use tkprof on a regular basis for both myself and other developers here. We have several hundered oracle people of many levels of experience. Giving access to many host systems to this many people becomes a protocol problem of sorts (gonna need a meeting of the architects, then the support areas, then the app areas, then the infrastructure area, then the security area (hmm.. maybe I should start with security first, they are most likely so say no in this day and age), then ... this is gonna take a year to get approved).

Ah but I don't want to complain. Its good to see others offer help, makes me feel more like a community member. Thanks, Kevin Meade

Tom Kyte
February 12, 2004 - 7:28 pm UTC

don't touch it, don't set it. leave it BE

RE: Quick & Dirty

Brent Pathakis, February 12, 2004 - 3:08 pm UTC

Kevin,

I dont use the _optimizer_mode ... settings in my system... I just leave it set choose, analyze the table, and let it default to all_rows, unless / until I need something different.

You're right about tkprof... it can be tricky to get access to. An alternative I've seen to it is sql_analyze in OEM. But ... I don't think I trusted it's answers as much as tkprof.

Tom Kyte
February 12, 2004 - 7:29 pm UTC

nothing but tkprof is good enough. if your policies do not permit it on your test/dev system - your policies in effect prevent you from doing your job.

OK

Kevin Meade, February 12, 2004 - 4:15 pm UTC

thanks, maybe my setups are not that far off from everyone elses in this regard and I don't really need to worry.

Kevin

cursor open/close with no fetch

James Blanding, February 13, 2004 - 2:05 pm UTC

"If you want the honest to goodness plan, you would:


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 l_my_input number;
3 cursor c is select * from t where x = l_my_input;
4 begin
5 open c;
6 close c;
7 end;
8 /

PL/SQL procedure successfully completed.

and then use tkprof to see:
[...]"

I see that basically goes through the motions of submitting the SQL and having it parsed, but never actually goes out to get any data. So I guess the execute step alone is pretty light-weight for plain old SELECT statement... This will help a lot for getting an accurate plan for a query that would normally take tens of minutes to run.....

Very nice indeed. Just throw in dbms_session.set_sql_trace (true); before the open/close and the same for 'false' afterwards. Wish I'd though of that a couple months ago! :)

Tom Kyte
February 13, 2004 - 2:54 pm UTC

oh, and to get bind variable peeking done right -- you would initialize l_my_input as well :)



odd

Justin, March 04, 2004 - 2:53 pm UTC

Tom,

I have a table t038_fi:

wic_owner8@DEV> select count(*) from t038_fi;

COUNT(FI_NR)
=================
5582265

I have a unique index on that column:
wic_owner8@DEV> select * from user_ind_columns where column_name ='FI_NR';

INDEX_NAME TABLE_NAME
============================== ==============================
COLUMN_NAME
==========================================================================
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
================= ================= ================= ====
X038IE01 T038_FI
FI_NR
1 10 10 ASC


If I issue the following select, it takes about 30 seconds to complete.
**********************************************************
wic_owner8@DEV> select t038_id from t038_fi where fi_nr = 7786789472;

T038_ID
=================
16103190

Elapsed: 00:00:31.06

Execution Plan
==========================================================
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8591 Card=1 Bytes=15)
1 0 TABLE ACCESS (FULL) OF 'T038_FI' (Cost=8591 Card=1 Bytes=15)




Statistics
==========================================================
0 recursive calls
0 db block gets
89332 consistent gets
52707 physical reads
0 redo size
381 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
**********************************************************

If I issue the following select, it comes back in 2 seconds using the index. Why isn't the CBO using the index if I try to return other columns?


wic_owner8@DEV> select fi_nr from t038_fi where fi_nr = 7786789472;

FI_NR
==========
7786789472

Elapsed: 00:00:02.04

Execution Plan
==========================================================
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1561 Card=1 Bytes=10)
1 0 INDEX (FAST FULL SCAN) OF 'X038IE01' (UNIQUE) (Cost=1561 Card=1 Bytes=10)




Statistics
==========================================================
0 recursive calls
0 db block gets
16270 consistent gets
0 physical reads
0 redo size
384 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed



I have these parameters changed:
wic_owner8@DEV> show parameter index

NAME TYPE VALUE
==================================== =========== =====
optimizer_index_caching integer 90
optimizer_index_cost_adj integer 35

Switching them back and forth from the original values (0,100 respectively) do not cause any differences for either query.

Can you help me understand why the CBO is working the way it is?

Thanks very much for your help.



Tom Kyte
March 04, 2004 - 3:02 pm UTC

what does

select num_rows from user_tables where table_name = upper('t038_fi')

return? I'll guess "not very much". It is saying "hey, table is just a block, why bother with an index if I have to a) index range scan and b) table access"

that is also why it is index full scanning...


did someone analyze empty tables?

Should have mentioned that

Justin, March 05, 2004 - 7:52 am UTC

Yes. I have analyzed the table.


wic_owner8@DEV> select num_rows from user_tables where table_name = upper('t038_fi')
2 ;

NUM_ROWS
=================
5582265

Thanks very much for your help.

Tom Kyte
March 05, 2004 - 8:49 am UTC

oh, I see it now.

You are basically issueing:


select ... from t where apples = oranges;


You are comparing a string (fi_nr) to a number and that is the same as saying:

select ... from t where TO_CHAR(fi_nr) = 1234;

you are apply a function to fi_nr, making it impossible for us to use the index properly.  consider:

ops$tkyte@ORA9IR2> create table t ( fi_nr       varchar2(10) NOT NULL , t038_id number );
 
Table created.
 
ops$tkyte@ORA9IR2> create unique index x038ie01 on t(fi_nr);
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 5000000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select t038_id from t where fi_nr = 123456576;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=50000 Bytes=1000000)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=11 Card=50000 Bytes=1000000)
 
 
 
ops$tkyte@ORA9IR2> select fi_nr from t where fi_nr = 123456576;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=50000 Bytes=350000)
   1    0   INDEX (FAST FULL SCAN) OF 'X038IE01' (UNIQUE) (Cost=4 Card=50000 Bytes=350000)
 
 
 
ops$tkyte@ORA9IR2> select t038_id from t where fi_nr = '123456576';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=20)
   2    1     INDEX (UNIQUE SCAN) OF 'X038IE01' (UNIQUE) (Cost=1 Card=100)
 
 
 
ops$tkyte@ORA9IR2> select fi_nr from t where fi_nr = '123456576';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=7)
   1    0   INDEX (UNIQUE SCAN) OF 'X038IE01' (UNIQUE) (Cost=1 Card=1 Bytes=7)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off



put your literal in quotes and see what you see (and ask yourself why a field named "nr" for NumbeR is using a string datatype........)
 

! :)

Justin, March 05, 2004 - 10:07 am UTC

You rule.

Duh.. I totally missed that, and you're absolutely right.
I'll change it right away and look for other situations in the model where there is similar misuse of datatypes.

Thanks a million Tom!

use hints in deletes

A reader, March 23, 2004 - 10:23 am UTC

Hi

I have this delete statement

DELETE om_cmf_pkg
WHERE package_instance_id in ( SELECT /*+ INDEX(A OM_ORDER_IDX01) USE_NL(A C) USE_HASH(D) */
c.package_instance_id
FROM swb.om_order a,
om_cmf_pkg c,
om_cmf_pkg_components d
WHERE a.order_id = c.order_id
AND a.order_reference LIKE 'OEADSL%'
AND d.action = 'E'
AND c.package_instance_id = d.package_instance_id )

Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| DELETE STATEMENT | | 67K| 2M| 127046 | | |
| DELETE |OM_CMF_PK | | | | | |
| MERGE JOIN | | 67K| 2M| 127046 | | |
| TABLE ACCESS BY INDEX R|OM_CMF_PK | 15M| 344M| 55996 | | |
| INDEX FULL SCAN |PK_OM_CMF | 15M| | 14628 | | |
| SORT JOIN | | 67K| 861K| 521 | | |
| VIEW |VW_NSO_1 | 67K| 861K| 70529 | | |
| SORT UNIQUE | | 67K| 3M| 70529 | | |
| HASH JOIN | | 67K| 3M| 68895 | | |
| TABLE ACCESS FULL |OM_CMF_PK | 1M| 7M| 14078 | | |
| NESTED LOOPS | | 740K| 28M| 53756 | | |
| TABLE ACCESS BY IN|OM_ORDER | 16K| 465K| 4418 | | |
| INDEX RANGE SCAN |OM_ORDER_ | 16K| | 128 | | |
| TABLE ACCESS BY IN|OM_CMF_PK | 15M| 172M| 3 | | |
| INDEX RANGE SCAN |RL_CMF_PK | 15M| | 2 | | |
--------------------------------------------------------------------------------


How can i change the sort merge join to hash join? I tried to put hints in DELETE /* ... */ but no effect, I used an alias for the delete table and put the alias inside subquery hint but no efffect neither... Is there any way to do this?

Tom Kyte
March 23, 2004 - 10:37 am UTC



i'd lose the HINTS all together. see what that does (and one would need to understand your pga settings -- sort area/hash area/pga aggregate target -- and versions...)

list of good and bad hints

A reader, March 24, 2004 - 9:00 am UTC

Hi Tom I'm trying to create a list of good hints (hints should be used when you need them)
and bad hints (hints you shouldn't use, except in exceptional situations and with good skill)

can you point in any of your book if you have a list like that.
.) thanks

Tom Kyte
March 24, 2004 - 9:38 am UTC

I've got a slide :)

this is my good list, if not on good list, it would be on the other kind of list

ALL_ROWS
FIRST_ROWS(n) or FIRST_ROWS
CHOOSE
(NO)REWRITE
DRIVING_SITE
(NO)PARALLEL
(NO)APPEND
CURSOR_SHARING_EXACT
DYNAMIC_SAMPLING
CARDINALITY

om

A reader, March 24, 2004 - 11:08 am UTC

Thanks T

FIRST_ROWS Use or not ?

Hector Gabriel Ulloa Ligarius, May 05, 2004 - 6:09 pm UTC

Hi Tom...

I see a document with the next article :

"FIRST_ROWS : Do not use with group functions"
"ALL_ROWS : Do not use if your table is not analyzed"

Is correct??

Regards

Hector Ulloa Ligarius

Tom Kyte
May 05, 2004 - 8:17 pm UTC

well, I'd disagree with first_rows, but would agree with all_rows (since the advice would be -- hey, don't use the cbo without stats)


group functions do not always mean "must get last row before first row", they too can use indexes to return incrementally.


FIRST_ROWS tricks

Sergey, May 06, 2004 - 6:05 am UTC

We have a very large old-style (I would say legacy) app, that runs against DBs in CHOOSE mode. They are scared to try CBO and therefore do not collect statistics as a rule. Practically it means that almost all SQL runs in RBO mode.
However I discovered that some SELECTs run much, much faster when I use FIRST_ROWS. Then I realized that FIRST_ROWS in this context just instructs Oracle to use CBO instead of RBO. The point is that CBO sometimes produces much better plans than RBO even w/o statistics.

Off topic: I'm currently reading Tom's 'Effective Oracle..'
It's a must for any Oracle developer...

BW

Firsts use or not?

Hector Gabriel Ulloa Ligarius, May 06, 2004 - 9:29 am UTC

Thanks Tom and Sergey...

Two questions :

1.- Where I buy the book "Tom's 'Effective Oracle'"?
2.- I can use hints FIRST_ROWS , with anyone functions..

Analytical Functions
Group Funcc...functions???

The Explain tell me : "You use FIRST_ROWS , with Cost=Example 1" , but that does not mean , that he is most optimal, or he is most optimal?

Thank you ...

PD : You work you enaltece and makes you be an incredible person, many congratulations

Tom Kyte
May 06, 2004 - 9:41 am UTC

1) amazon.com has it, bookstores should have it.  you can get the isbn from amazon and take that to a bookstore to order it.

2) it would be the best possible plan the optimizer could come up with to get you the FIRST ROW as fast as possible.  first rows says "optimize for initial response time".  all rows says "optimize for total throughput (get me the last row as fast as possible)"

So, something like:

select * from t1, t2 where t1.id = t2.id order by t1.id;

Assuming that there is an index on t1(id) and t2(id) would have the following sorts of plans:

ops$tkyte@ORA9IR2> select /*+ first_rows */ *
  2    from t1, t2
  3   where t1.object_id = t2.object_id
  4   order by t1.object_id;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=62092 Card=30634 Bytes=5881728)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=96)
   2    1     NESTED LOOPS (Cost=62092 Card=30634 Bytes=5881728)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=826 Card=30633 Bytes=2940768)
   4    3         INDEX (FULL SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=26 Card=30633)
   5    2       INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1 Card=1)
 
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select /*+ all_rows */ *
  2    from t1, t2
  3   where t1.object_id = t2.object_id
  4   order by t1.object_id;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1010 Card=30634 Bytes=5881728)
   1    0   MERGE JOIN (Cost=1010 Card=30634 Bytes=5881728)
   2    1     SORT (JOIN) (Cost=505 Card=30633 Bytes=2940768)
   3    2       TABLE ACCESS (FULL) OF 'T1' (Cost=43 Card=30633 Bytes=2940768)
   4    1     SORT (JOIN) (Cost=505 Card=30634 Bytes=2940864)
   5    4       TABLE ACCESS (FULL) OF 'T2' (Cost=43 Card=30634 Bytes=2940864)
 
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace off


the first rows plan would be painful to get the last row, but would start returning data right away.

the all rows plan would take a bit of time to get the first row back -- but every row after that would be light speed. 

FIRST_ROWS use or not?

Hector Gabriel Ulloa Ligarius, May 06, 2004 - 10:17 am UTC

Thank you Tom..

Like always effective

Regards

Hector Ulloa Ligarius

why not ORDERED in good list hint

A reader, June 24, 2004 - 10:09 pm UTC

Hi Tom, could you please explain why you didn't put in your good hint likst the ORDERED hint please.



Tom Kyte
June 25, 2004 - 7:42 am UTC

ordered doesn't give the optimizer information.

ordered is doing the optimizers job.


Hints that give the optimizer a clue (first_rows, all_rows for example) -- they are what I term "good" hints. Hints that tell the optimizer how to do its job (index, ordered, use_nl for example) are ones I try to avoid, not promote (except as a method to prove that something is wrong)

My ultimate goal is to have "bad" hints be the last resort, not the first thing to use.

A reader, June 25, 2004 - 10:01 am UTC

Thanks Tom

I see your point of view in this way:
hints that gives information cbo don't need, and
hint that don't need maintenance, because data load can change

Here are Don good hints, I see his point of view is valid too, because use ordered to reduce parse time, but this are hints that must be monitored periodically, because what once was the best in 1 year can not be the best.
But I see both valids

</code> http://dba.ipbhost.com/index.php?s=9ee85284bf930198cf3cfb4733f26e04&showtopic=218 <code>


A reader, June 25, 2004 - 10:05 am UTC

Sorry I fix
"hints that gives information cbo don't need, and "
to
"hints that gives information cbo don't HAVE, and "

A case requireing bad hints?

Kevin, August 25, 2004 - 12:36 pm UTC

******************************************************************************
Tom,

Your valuable insights into the workings of the CBO have helped to
"de-vilify" it at my workplace, cast the blame for poor SQL performance
exactly where it's belonged (on poor database and/or query design), and
nearly rendered the use of "bad hints" into obscurity.

However, a recent tuning effort of mine has got me wondering if the
CBO is as bullet-proof as I thought.

Perhaps you could take a quick look:
******************************************************************************

DROP TABLE BASE
/
DROP TABLE TYP
/
DROP TABLE STYP
/
CREATE TABLE BASE AS
SELECT ROWNUM ID,
MOD(ROWNUM,20)+1 TYPE_ID,
'NAME_OF_ROW_' || ROWNUM NAME,
CASE WHEN ROWNUM < 10 THEN NULL ELSE MOD(ROWNUM,9)+1 END PARENT_ID,
CASE WHEN ROWNUM < 10 THEN 'Y' ELSE 'N' END IS_A_PARENT
FROM ALL_OBJECTS
WHERE ROWNUM < 100000
/
CREATE INDEX BASE_N1 ON BASE(IS_A_PARENT)
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => ?????
,TABNAME => 'BASE'
,METHOD_OPT => 'FOR COLUMNS SIZE 6 IS_A_PARENT'
,CASCADE => TRUE);
END;
/
CREATE TABLE TYP AS
SELECT ROWNUM TYPE_ID,
'SERVICE_TYPE_'||(MOD(ROWNUM,5)+1) ST_CODE
FROM ALL_OBJECTS
WHERE ROWNUM < 20
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => ?????
,TABNAME => 'TYP');
END;
/
CREATE TABLE STYP AS
SELECT ROWNUM ST_ID,
'SERVICE_TYPE_'||(MOD(ROWNUM,5)+1) ST_CODE,
MOD(ROWNUM,4) ST_VALUE
FROM ALL_OBJECTS
WHERE ROWNUM < 100
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => ?????
,TABNAME => 'STYP');
END;
/

******************************************************************************
Once those tables are created, the following query is used to
look up a base name, the base's parent's name, the base type,
the base ST_CODE, and the minimum ST_VALUE of the base's ST_CODE.
******************************************************************************

SELECT B.ID, B.NAME, P.NAME PARENT_NAME, T.ST_CODE, S.ST_MIN_VALUE
FROM BASE B,
BASE P,
TYP T,
(SELECT ST_CODE, MIN(ST_VALUE) ST_MIN_VALUE
FROM STYP
GROUP BY ST_CODE) S
WHERE B.PARENT_ID = P.ID
AND P.IS_A_PARENT = 'Y'
AND B.TYPE_ID = T.TYPE_ID
AND T.ST_CODE = S.ST_CODE
/

******************************************************************************
This is where the CBO chokes. It would clearly make sense to
do the S - subquery group-by first, reducing that query from 100 rows
to 5 rows, THEN do the join. However, the CBO is joining first,
getting a HUGE result set, then doing a massive group-by sort operation
on that result set.
******************************************************************************

OPERATION OBJECT NAME ROWS BYTES COST
SELECT STATEMENT OPTIMIZER MODE=CHOOSE 18 615
SORT GROUP BY 18 1 K 615
HASH JOIN 33 K 3 M 58
TABLE ACCESS FULL STYP 99 1 K 2
HASH JOIN 1 K 143 K 55
TABLE ACCESS FULL TYP 19 342 2
HASH JOIN 9 K 597 K 52
TABLE ACCESS BY INDEX ROWID BASE 9 306 2
INDEX RANGE SCAN BASE_N1 9 1
TABLE ACCESS FULL BASE 99 K 3 M 49



******************************************************************************
Compare to the plan of the same query, with a
"bad" NO_MERGE hint, which sorts first, then joins...
******************************************************************************


SELECT /*+ NO_MERGE(S) */ B.ID, B.NAME, P.NAME PARENT_NAME, T.ST_CODE, S.ST_MIN_VALUE
FROM BASE B,
BASE P,
TYP T,
(SELECT ST_CODE, MIN(ST_VALUE) ST_MIN_VALUE
FROM kak_STYP
GROUP BY ST_CODE) S
WHERE B.PARENT_ID = P.ID
AND P.IS_A_PARENT = 'Y'
AND B.TYPE_ID = T.TYPE_ID
AND T.ST_CODE = S.ST_CODE
/

OPERATION OBJECT NAME ROWS BYTES COST
SELECT STATEMENT OPTIMIZER MODE=CHOOSE 1 K 60
HASH JOIN 1 K 212 K 60
TABLE ACCESS BY INDEX ROWID BASE 9 306 2
INDEX RANGE SCAN BASE_N1 9 1
HASH JOIN 19 K 1 M 57
HASH JOIN 19 1 K 7
VIEW 5 205 4
SORT GROUP BY 5 90 4
TABLE ACCESS FULL STYP 99 1 K 2
TABLE ACCESS FULL TYP 19 342 2
TABLE ACCESS FULL BASE 99 K 3 M 49


******************************************************************************
As always, the proof's in the pudding. Here are the statistics
of each run:
******************************************************************************

-- W/out NO_MERGE hint:
-- EXECUTION TIME: 00:42

DB BLOCK GETS 46
CONSISTENT GETS 510
PHYSICAL READS 1638
REDO SIZE 0
BYTES SENT VIA SQL*NET TO CLIENT 6356534
BYTES RECEIVED VIA SQL*NET FROM CLIENT 133682
SQL*NET ROUNDTRIPS TO/FROM CLIENT 3803
SORTS (MEMORY) 2
SORTS (DISK) 1


-- With NO_MERGE hint:
-- EXECUTION TIME: 00:13

DB BLOCK GETS 36
CONSISTENT GETS 4289
PHYSICAL READS 0
REDO SIZE 0
BYTES SENT VIA SQL*NET TO CLIENT 6356534
BYTES RECEIVED VIA SQL*NET FROM CLIENT 133700
SQL*NET ROUNDTRIPS TO/FROM CLIENT 3803
SORTS (MEMORY) 3
SORTS (DISK) 0

******************************************************************************
Your thoughts? These are, of course, sample tables. The actual query
uses a "base" table of about 10,000,000 rows, and the runtime without the
hint is almost 2 hours, compared to that with the hint, which is about
10 minutes.

Barring any physical changes to the data-structures, can you formulate this
query in such a way that it performs well, without using a "bad hint"?

Thanks!
******************************************************************************


Tom Kyte
August 25, 2004 - 1:29 pm UTC

before I even look -- i'll say it straight out -- the CBO is a piece of software created by human beings that works with limited inputs. It is not infalliable, but neither is the RBO or anything that I've ever worked with.


That aside.

the cbo did the "right thing" here given what it knows (it optimizes for low IO, 510 < 4,289) -- system statistics would help it better understand some things (and might be something to look into).

It was in the performance of physical IO that the first query got "pinged"

Version info

Kevin, August 25, 2004 - 12:39 pm UTC

Left this off:

Version 8.1.7

Setting optimizer to choose

sonali, August 26, 2004 - 9:47 am UTC

Can set the optimizer mode to choose for perticular schema. We have client we number of schema's on perticular server ( i don't know if they are all on same database, but I would assume that they are)..They have this optimizer set to RULE and none of our indexes are getting used. Performance is really bad. BUt they say the other applications which uses other schemas work fine with RULE setting.
Is their any way I can just set the optimizer to CHOOSE with say some logon trigger or something for our schema. This will help a lot.
Thanks

Tom Kyte
August 26, 2004 - 10:17 am UTC

yes, you can use a logon trigger for that.

Cardinality a GOOD hint?

Kerry, December 07, 2004 - 12:52 pm UTC

I was surprised to see cardinality listed in the good hint list. I am tempted to use it to address a problem which I can't solve in any other way short of using DBMS_STATS.SET_COLUMN_STATS to directly manipulate the distcnt of a column, and I'm wondering which is less distasteful.

I would have guessed that the cardinality hint falls on the bad list, because it would tend to need tuning as the data changes over time.

Here's the scenario: We have a table called NODE which has the columns CLIENT_ID, HIERARCHY_ID, NODE_TYPE, NAME, and XML_DATA. Both CLIENT_ID and HIERARCHY_ID are based on a sequence. Since most clients have only one hierarchy, the number of distinct values (distcnt) gathered in column stats for both CLIENT_ID and HIERARCHY_ID is around 150.

The values in CLIENT_ID and HIERARCHY_ID correlate almost 1:1.

In plans involving the NODE table (which always will specify both CLIENT_ID and HIERARCHY_ID), the cardinality is always off by a factor of around 150, because of what I have seen termed the "Predicate Independence Assumption". The CBO seems to assume that the distcnt can be applied as a separate ratio for the CLIENT_ID bind variable and the HIERARCHY_ID bind variable.

In some queries this miscalculation of the cardinality throws the plan way off into an absurdly non-optimal solution, taking literally hours where a better plan takes seconds. (We are talking decision support type queries with 20-30 joins.)

Is it safer to adjust the statistics with DBMS_STATS.SET_COLUMN_STATS to set distcnt of HIERARCHY_ID to 2, or should I resort to using hints here? Note that in all cases CLIENT_ID and HIERARCHY_ID will be specified when querying this table, so changing the stats for just one of the columns does seem to address the problem.

Thanks for your guidance.

Tom Kyte
December 07, 2004 - 8:34 pm UTC

I use the cardinality hint mostly with the

where x in ( select from PLSQL_FUNCTION )

because the plsql function would always have a default cardinality. Also, can be useful sometimes with global temporary tables -- you insert N rows into a GTT (you know what N is -- sql%rowcount) and you use cardinality in "steps" -- meaning you'll have a cardinality 10, 100, 500, 5000, 10000 -- but not 502 (502 would use 500) -- so you have upto 5 plans here -- for differing sets of GTT's.


What you describe above is tackled in 10g with sql profiles (sort of the ability to analyze a query and store statistics for the query).

if you can fix your issue with dbms_stats, that seems to be much less intrusive than hinting all queries (and you can see if 10g with sql profiles might solve it completely.)

Thanks for the Sql Profile Tip

Kerry, December 08, 2004 - 12:25 pm UTC

Thanks for the tip on sql profiles... We are new to 10g, and our DBA quit a week after the upgrade. I'm just an architect trying to make sense of all this new stuff.

Sql Profiles promises to fix the exact problem we have. The section titled "Advanced Tuning: SQL Profiles" in </code> http://www.oracle.com/technology/pub/articles/10gdba/week18_10gdba.html, <code>it addresses exactly the predicate dependence situation we have.

Next... Does it really work? :-)


Tom Kyte
December 09, 2004 - 12:41 pm UTC



yes.

Actually,...

kerry, December 10, 2004 - 5:33 pm UTC

I've opened a TAR (4224154.995) on dbms_sqltune. For the query in question, if I ask it to cogitate for more than about 100 seconds, it always comes up with the error:

- ORA-03001: unimplemented feature

A shorter time limit doesn't produce a good enough plan to solve our problems.

I also tried STORED OUTLINES but ran into a limitation that they won't work with ANSI SQL SYNTAX. (Also in a TAR: 4226225.995, but apparently already a known issue.) I may be facing recoding some legacy/ported code with ANSI SQL and really hairy outer joins that involve multiple tables (possible in ANSI SQL but not directly in Oracle syntax).

STORED OUTLINES could solve our problem by first seeding the statistics with values that drive the query to a better plan, then capture the plan.

When I tried the same approach with sql profiles (and a short time limit) it suggested a plan that was actually quite a bit slower than the initial plan. Is there any way to get dbms_sqltune to just store the existing plan, not the suggested plan?

Tom Kyte
December 10, 2004 - 8:16 pm UTC

dbms_sqltune does not store plans.

do you have *an example* to look at?

clarification on post by "A case requireing bad hints" by Kevin

amit poddar, December 11, 2004 - 2:19 pm UTC

-- W/out NO_MERGE hint:
-- EXECUTION TIME: 00:42

DB BLOCK GETS 46
CONSISTENT GETS 510
PHYSICAL READS 1638
REDO SIZE 0
BYTES SENT VIA SQL*NET TO CLIENT 6356534
BYTES RECEIVED VIA SQL*NET FROM CLIENT 133682
SQL*NET ROUNDTRIPS TO/FROM CLIENT 3803
SORTS (MEMORY) 2
SORTS (DISK) 1


-- With NO_MERGE hint:
-- EXECUTION TIME: 00:13

DB BLOCK GETS 36
CONSISTENT GETS 4289
PHYSICAL READS 0
REDO SIZE 0
BYTES SENT VIA SQL*NET TO CLIENT 6356534
BYTES RECEIVED VIA SQL*NET FROM CLIENT 133700
SQL*NET ROUNDTRIPS TO/FROM CLIENT 3803
SORTS (MEMORY) 3
SORTS (DISK) 0

In the first case physical reads seems to be because of the sorting. You said oracle only looks at lio, but dosent' oracle include the cost of sorting in the query cost.




amit poddar, December 11, 2004 - 2:22 pm UTC

My question boils down to the following

Is the view merging cost based or just heuristics based.

I mean does oracle cost both merged and unmerged plans before making the decision on the plan. or does it just cost the query after merging.



Tom Kyte
December 11, 2004 - 7:00 pm UTC

use the 10053 trace (search this site for that, or see chapter 6 of Effective Oracle by Design) to see which plans were considered and rejected.

did you actually trace this with 9ir2 tkprof to see where it is spending its time.

Does dbms_sqltune store plans?

kerry, December 13, 2004 - 11:54 am UTC

I was under the impression that DBMS_SQLTUNE.ACCEPT_SQL_PROFILE would provide something like plan stability. My terminology my be incorrect when I say "store a plan", but isn't the effect similar?

In any case, I don't think I have an easily packaged bit of sample code: The situtation I have is a query that involves around two dozen tables, many of them partitioned, and requires data loaded from more than 100 clients to exhibit the issue with predicate independence assumptions.

Any attempt I've made to isolate a simpler piece of the problem produces a query that dbms_sqltune can indeed fix.

Tom Kyte
December 13, 2004 - 1:51 pm UTC

no, it stores additional statistics that are used to optimize the query at run time.

Clarification

kerry, December 13, 2004 - 12:15 pm UTC

I see from some additional reading on dbms_sqltune that what is stored is "of auxiliary statistics specific to that statement". Does this mean that if I really want the optimizer to stick with a specific plan, I am better of with stored outlines?

Tom Kyte
December 13, 2004 - 1:58 pm UTC

if you want to program the optimizer to death, just use hints like mad ( that is what the stored outline is, a big old hint tree). probably be less obscure.

what is wrong with giving the optimizer sufficient detail and letting it decide? that was the goal of sql profiles, to analyze a query on your system, with your data, with your skew, with your correlations between column values and do the right thing.

One hint to avoid all others.

Kerry, December 17, 2004 - 5:37 pm UTC

I have read everything on your site regarding hints, bind peeking, and query optimization. Okay, not really, but lots of it.

I have seen enough questions from other readers to know that we are not alone in having an application that is really a hybrid between OLTP and data warehousing (and unfortunately rather inseperable).

Ideally, according to what I have read here, we should rewrite much of our SQL to "use bind variables where appropriate and not where it's not". This isn't feasible for an application this size (that worked fine under 9i with bind peeking turned off).

Now with 10g we have some of these queries that really do need the benefit of either literals or at least bind peeking.

I REALLY need cursor level control over bind peeking. One more hint would allow me to avoid all of the others. It would look something like this:

select /*+ bind_peek(:c) */ * from SOME_GNARLY_VIEW
where some_column = :some_variable
other_column = :another_variable
client_id = :c
yada yada yada

My bind_peek hint would tell Oracle TWO THINGS: bind peeking is good for the particular bind variable C, and the plan should be cached individually for every value of C that is encountered. The signature of the query should be built from the text as well as the values of any peeked variables. Maybe you'd want separate control over these two behaviors: a hint to specify which variables are peeked, and another hint to tell which ones affect the cache signature.

In any case, for us, client_id is a partition key on almost every table. With the suggested hint, Oracle could use the partition level histograms appropriately without us resorting to dynamic SQL which is VERY CUMBERSOME in PL/SQL, you MUST admit. My other beefs with dynamic SQL: opens us up to runtime errors because of deferred parsing, and opens up lots of security holes.

Tom Kyte
December 18, 2004 - 8:45 am UTC

dynamic sql cumbersome in plsql?

I admit nothing there, it is easier in plsql than in any other language I've programmed in.

opens exactly zero security holes with binds. if you glue in literals, sure -- sql injection.

Yes cumbersome, yes security issues...

Kerry, December 20, 2004 - 1:30 am UTC

The very heart of this discussion is using literals instead of binds so that the optimizer may make the right decisions, so of course there is a security issue.

As for cumbersome, PL/SQL has a rich variety of ways to use SQL. My faavorite is "for REC in (select ...)". I can't use that, or many forms of cursors, or a simple "select ... into" if I am forced to use dynamic SQL for the sake of inserting literals for the optimizer's benefit.

Tom Kyte
December 20, 2004 - 8:16 am UTC

but you see -- the entire goal of binding is "don't parse"

but you are wanting the optimizer to parse the bound statement as if it had literals (sort of "anti-cursor sharing in reverse") -- that runs 100% opposite of what binds are all about.




Sometimes there is a need for bind variables and reparsing each time

Mihail Daskalov, December 22, 2004 - 11:50 am UTC

Hi Tom,
thanks for the nice answers you give in this thread and the rest of the site.

There are some situations where we really need reparsing of SQL statements as if it was using literals, but we've written a lot of PL/SQL packages which do:

open the_cursor_variable for
select * from somewhere
where x in (select the_id from table(p_varray_type )) /* or pipelined function */ ;

We've found that cardinality hint gives very good results as in:

open the_cursor_variable for
select * from somewhere
where x in (select /*+ cardinality(i 30) */ i.id from table(p_varray_type ) i) /* or pipelined function */ ;

But we don't want to migrate to dynamic sql as it would be a lot of work to rewrite all the procedures to include the cardinality hint with the real number of values passed via the p_varray_type parameter.

I wish it could be possible to include some hint e.g.

open the_cursor_variable for
select /*+ regenerate_the_plan_with_new_bind_peeking */ * from somewhere
where x in (select the_id from table(p_varray_type )) /* or pipelined function */ ;

And that the bind peeking feature worked with table(p_varray_type) to get the correct number of rows passed really.
Does it currently work that way - or table(p_varray_type) is always supposed to have some 8172 rows?

Can you recommend some really nice and relatively easy way to implement such functionality.

Sorry about asking a new question below but I think it's related to the rest in the thread.

1) What will happen to two sessions executing the same SQL statement but with different values for the session parameters (sort_area_size, db_block_multiread_count, optimizer_index_cost_adj, etc...).

2) Is there any way to force oracle not to reuse plans for the same SQL statement if parameters on which the plan depends are not the same?

3) And finally is there any way to remove a single sql statement from the library cache (not just "alter system flush shared pool")

Mary christmas and happy holidays,
Mihail Daskalov


Tom Kyte
December 22, 2004 - 1:54 pm UTC

I've suggested before that you do use dynamic sql (it would be no more rework than it would be to go back and rehint everything) and use "steps" for the cardinality.

eg: we'll have a plan for 0, 50, 500, 5000 and 50000. We'll take the real cardinality of "123" and call that "50", or 763 and call that 500. We'll dynamicaly open a query with a cardinality of 0, 50, 500, 5000, 50000.

To regenerate the plan with bind peeking each time is called "hard parsing".

Also, the "table" think is always "i have no clue how big it is" so peeking would be useless here -- it would have no idea how big.


1) two plans are generated, you'll have child cursors for each.

2) nope -- not really.

3) no.

Is there some other form of dynamic SQL I don't know?

Kerry, December 28, 2004 - 12:24 pm UTC

You say it would be no more difficult to rewrite every query in his PL/SQL code as dynamic SQL than to hint every query that needs it. I just don't get this. Is there some form of dynamic SQL with which I am not familiar?

I know of these three forms: EXECUTE IMMEDIATE, DBMS_SQL, and OPEN CURSOR for '...'.

If I need to change to dynamic SQL some PL/SQL that looks like this:

DECLARE
v_mgr_name emp.manager_name%type := 'SOME USER INPUT DATA THAT NEEDS CLEANSING';
BEGIN
for myrec in (select first_name || '-' || last_name
from emp
where first_name not like 'PLACEHOLDER'
and manager_name = v_mgr_name
) loop
...
end loop;
END;

I would lose the elegance of the "for rec in..." construct, the protection against sql insertion, the implicit declaration of myrec and the compile time checking of my SQL syntax(!!!), and end up with something like:

DECLARE
v_mgr_name emp.manager_name%type := 'SOME USER INPUT DATA THAT NEEDS CLEANSING';
type myrec_type is record (
column_data varchar2(2000)
);
myrec myrec_type;
TYPE c1_type IS REF CURSOR;
c1 c1_type;
BEGIN
LANG_UTIL.PROTECT_AGAINST_SQL_INSERTION(v_mgr_name);
open c1 for 'select first_name || ''-'' || last_name
from emp
where first_name not like ''PLACEHOLDER''
and manager_name = :B1' using v_mgr_name;
loop
EXIT WHEN c1%NOTFOUND;
FETCH c1 into myrec;
...
end loop;
END;

This is what I mean when I call dynamic SQL inelegant compared to regular SQL (specifically in PL/SQL). Is there some better, more elegant way?

I have documented these concerns with Oracle in TAR 4126112.996: NEED CURSOR LEVEL CONTROL OVER BIND PEEKING, IS THERE A HINT?

Tom Kyte
December 28, 2004 - 2:27 pm UTC

most people code not that way.
most people for whatever reason code this way

declare
cursor c is .....
l_rec c%rowtype;
begin
open c;
loop
fetch c into l_rec;
exit when c%notfound;
end loop;
close c;
end;
/


with NDS that becomes:

-- C is what I call a
-- template cursor, we never open it, checks datatypes, security, etc
-- but not syntax tis true.
declare
cursor c is select <columns> from <tables>;
l_rec c%rowtype;
l_cursor sys_refcursor;
begin
open l_cursor for .......;
loop
fetch l-cursor into l_rec;
exit when l_cursor%notfound;
end loop;
close l_cursor;
end;


there is no hint, the hint would cause a hard parse everytime, sort of what bind variables are designed "not to do".

Addendum

Kerry, December 28, 2004 - 12:28 pm UTC

I guess I really meant to change the bind variable to a literal, making it a bit more inelegant with quoting quotes and all:

DECLARE
v_mgr_name emp.manager_name%type := 'SOME USER INPUT DATA THAT NEEDS CLEANSING';
type myrec_type is record (
column_data varchar2(2000)
);
myrec myrec_type;
TYPE c1_type IS REF CURSOR;
c1 c1_type;
BEGIN
LANG_UTIL.PROTECT_AGAINST_SQL_INSERTION(v_mgr_name);
open c1 for 'select first_name || ''-'' || last_name
from emp
where first_name not like ''PLACEHOLDER''
and manager_name = '''|| v_mgr_name ||'''';
loop
EXIT WHEN c1%NOTFOUND;
FETCH c1 into myrec;
...
end loop;
END;


Tom Kyte
December 28, 2004 - 2:30 pm UTC

open c1 for q'|select first_name || '-' || last_name from emp|';

in 10g anyway.

but if you want a hard parse / execution, and use binds just:


open c1 for 'select /* ' || systimestamp || ' */
from t
where x = :x' using p_variable;



but, it'll kill your system if that is done more than "once in a long while"



Just like a hint...

Kerry, December 29, 2004 - 12:01 pm UTC

I've thought about using the timestamp mechanism like your suggestion.

open c1 for 'select /* ' || systimestamp || ' */
from t
where x = :x' using p_variable;

It almost feels like a hint, doesn't it?

And, for our situation, we don't need a hard parse on every execution. I know, 100% without a doubt, the specific bind variable that causes bind peeking to degrade: it's the same column that we use to partition every table... client_id. Since one client may have a completely different distribution of data from the next (not even considering skewness, just PK-FK relationships--we usually don't need histograms) the plan that gets cached for one client is not at all right for another.

So, here's what I've been considering:

open c1 for 'select /* ' || p_client || ' */
from t
where x = :x
and client_id = :y' using p_variable,p_client;

This would effectively partition the plans in the shared_pool along a client boundary. We may resort to something like this.

Here's the REAL reason this needs to be a hint, rather than a HACK: A DBA can add hints in production code without a complete QA regression cycle because it SHOULD only affect performance. We don't go refactoring code without testing, and a programmer changing SQL to NDS is significant refactoring that could introduce functional bugs.

We would effectively have to change our entire application to NDS first, test it, then release it with the readiness to add the HACK where needed.

A hint would allow us to do it on an as needed basis:

select /*+ SEGMENT_SHARED_POOL_BY(p_client) */
from t
where x = p_variable
and client_id = p_client;

BEAUTIFUL.

P.S. When I read this in Oracle's documentation, I did a double-take:
Bind peeking works for a specific set of clients, not all clients. (From 14 The Query Optimizer).



Tom Kyte
December 29, 2004 - 7:28 pm UTC

enhancement requests need to be filed via support -- seems you have the start of a business case (that'll be something they need).....


(i still don't see how that hint would work -- for it would need to peek at the binds each time, eg: a parse -- it would have to parse -- soft/hard -- whatever. that hint would be a "parse every time" hint)

No need for a hard parse every time

Kerry, December 30, 2004 - 4:50 pm UTC

There's no need for a hard parse every time. Are you familiar with the way that a plan is referenced by a hash_value in v$sql_plan? I assume this is a hash of the text of the query.

After the query is initially hashed, some meta data stored with the plan could say to add selected bind variables to the hash value and look it up again. This would segment the cache according to certain bind values, as we need. I am working this issue in TAR 4126112.996.

A caution about DBMS_STATS.SET_COLUMN_STATS

Kerry, January 11, 2005 - 11:01 am UTC

If anyone else tries using DBMS_STATS.SET_COLUMN_STATS to deal with issues of correlated predicates (compensating for the predicate independence assumption), as I described on Dec 7, '04, here's a word of caution: setting distcnt (the number of distinct values for a column) to 1 may be a bad idea. We've had to adjust our strategy and use the value 2 (which is only half as effective). Otherwise certain predicates that have a literal value equated to the column (i.e. where x = 0) will be treated as outside the range of low_value and high_value and the cardinality will drop to 1.

function based index hints

Scott Duncan, January 12, 2005 - 3:55 pm UTC

With regard to using hints. I currently have a dynamically generated query from a dynamic search engine that we previously developed. The search engine uses function based indexes for quite a number of search criteria to provide case insensitive searching. On 8i the search engine works well and chooses the function based indexes appropriately, however on testing an upgrade to 9i it appears that the function based indexes are at least sometimes ( still investigating, so possibly always ) not used when I would expect so it looks as though I may need to hint the use of the function based indexes indexes. This is complicated by the fact that the query is dynamically generated.

eg. XIE1FT_T_ALID is an index on UPPER(alt_id) in the below query. 'ABC%' as below matches only 53 rows out of approx 900000 rows in the ft_t_alid table. Notably the cost of FT_T_ALID_PK index access is evaluated to 1 whereas the function based index access cost is 3 although access via the function based index is considerably quicker ?

Do you have any idea whether there are known bugs/issues with using function based indexes on 9i that mean I will need to use hints ? or am I perhaps missing something and need eg. a histogram or similar additional statistics perhaps. I have to say I tried a histogram but found no difference.

Only the hint appears to help and may indeed be my last and only resort ? Thanks for any guidance you can provide.

SELECT /*+ INDEX( sysc2 XIE1FT_T_ALID ) */
cust.ROWID row_id_cust,
CHARTOROWID(NULL) row_id_chld,
acct.ROWID row_id_acct,
reln.ROWID row_id_reln,
sysc.ROWID row_id_syst,
cust.pty_lgl_nme as prnt_pty_lgl_nme,
NULL as chld_pty_lgl_nme,
acct.acct_desc as acct_desc,
reln.inst_mnem as inst_mnem,
reln.tran_covg_cde as tran_covg_cde,
reln.slsr_nme as slsr_nme,
sysc.alt_id as alt_id
FROM cs_cust_ext cust,
ft_t_acct acct,
ft_t_cacr cacr,
cs_cac_reln reln,
ft_t_alid sysc,
ft_t_alid sysc2,
cs_fcovg_sys dist,
ft_t_idmv idmv,
ft_t_sdgp sdgp,
cs_cust_role prol
WHERE 1 = 1
AND cacr.cst_id = cust.cst_id
AND cacr.org_id = 'CSFB'
AND cacr.bk_id = 'CUST'
AND cacr.rl_typ = 'CUSTACCT'
AND cacr.end_tms IS NULL
AND acct.org_id = cacr.org_id
AND acct.bk_id = cacr.bk_id
AND acct.acct_id = cacr.acct_id
AND acct.acct_cls_dte IS NULL
AND acct.actp_acct_typ IN ('INTERNAL', 'NORMAL')
AND reln.org_id = cacr.org_id
AND reln.bk_id = cacr.bk_id
AND reln.acct_id = cacr.acct_id
AND reln.org_id = acct.org_id
AND reln.bk_id = acct.bk_id
AND reln.acct_id = acct.acct_id
AND reln.end_tms IS NULL
AND sdgp.subdmn_grp_oid = 'CMSWEB'
AND idmv.intrnl_dmn_val_id = sdgp.intrnl_dmn_val_id
AND idmv.fld_data_cl_id = 'CONTEXT'
AND sysc.tbl_typ = 'ACCT'
AND sysc.cross_ref_id = sysc2.cross_ref_id
AND sysc.id_ctxt_typ = RPAD(idmv.intrnl_dmn_val_txt, 8)
AND sysc.cross_ref_id = acct.cross_ref_id
AND sysc.end_tms IS NULL
AND sysc2.tbl_typ = 'ACCT'
AND sysc2.end_tms IS NULL
AND UPPER(sysc2.alt_id) LIKE 'ABC%'
AND EXISTS ( SELECT 1 FROM
cs_fcovg_sys dist2
WHERE dist2.src_sys_cde = sysc2.id_ctxt_typ
AND dist2.end_tms IS NULL )
AND dist.tran_covg_cde = reln.tran_covg_cde
AND dist.inst_mnem = reln.inst_mnem
AND dist.finsrl_typ = reln.finsrl_typ
AND dist.end_tms IS NULL
AND dist.src_sys_cde = sysc.id_ctxt_typ
AND prol.cst_id = cust.cst_id
AND prol.pty_role_cde IN ('CTPYAGNT', 'CTPYPRNC')
AND prol.end_tms IS NULL


Tom Kyte
January 12, 2005 - 5:49 pm UTC

sorry -- but i won't be able to comment given the level of detail -- and judging by the size of the query, please don't put the level of detail here.

do you have something significantly smaller that demonstrates the issue?

No, I'm not aware of anything specific in this area.

Further on Function based index hints

Scott Duncan, January 13, 2005 - 6:56 am UTC

Thanks for your feedback. I will try to produce a smaller expample that illustrates the problem. Meanwhile however I have just noted that if I replace 'ABC%' in the above query by eg. 'ABCD%' or any other 4 character string the query appears to use the UPPER( alt_id ) function based index but for any 3 character string it does not. Is there any relation between selectivity and string length or any other reason for this ? - thanks again

Tom Kyte
January 13, 2005 - 9:02 am UTC

definitely a relationship between selectivity and string length.

why no BINDS???

Another predicate independence/DBMS_STATS.SET_COLUMN_STATS follow-up

Kerry, January 18, 2005 - 4:13 pm UTC

So, in our ongoing saga to try to get the CBO to do the right thing without hinting every query to death, we've arrived at this partial solution to the predicate independence assumption. On one of the two highly correlated columns we'll call TAB.COL, we do this:

DBMS_STATS.DELETE_COLUMN_STATS(user, 'TAB', 'COL');
DBMS_STATS.SET_COLUMN_STATS(user,'TAB','COL', distcnt => 1, nullcnt => 0);

By first deleting the existing stats, we don't produce the foul side-effects that setting distcnt=1 alone had caused, and the cardinalities throughout the complicated, 30-some-odd table join look correct in the explain plan.

And here's the punchline: when the CBO predicts appropriate cardinalities, it suggests a pretty good plan. NO HINTS needed, at all.

Note that until we put this work-around in place, we had to constantly tweak the hints we were using because the automated gathering of statistics would change the plan and make the hints inappropriate in the context of a different plan. (i.e. TOM'S AVERSION TO USING HINTS IN PRODUCTION IS QUITE JUSTIFIED.)

This approach is our own creation: Working with Oracle support hadn't given us a solution that worked.

Now, our situation involving correlated predicates could be addressed by manipulating the statistics as described above, ONLY because the column referred to as TAB.COL in the sample above is ALWAYS used in conjunction with another column that is highly correlated (practically 1:1). This way, the CBO gets the proper selectivity when it evaluates the other column, and sees from the statistics that we manipulate that a predicate involving TAB.COL does not further restrict the row set.

In the future when I encounter the predicate independence assumption in a case where the correlated columns are not ALWAYS used in conjunction, I guess I'll hope that DBMS_SQLTUNE can handle the complexity of the query, or once again resort to heavy hinting.

Is anyone else out there experience issues with the PREDICATE INDEPENDENCE ASSUMPTION, like we have? Have you found any more creative solutions? We're still not satisfied, as you can imagine, with having to manipulate statistics...

Cardinality hint going away, selectivity already gone

Kerry, January 25, 2005 - 5:34 pm UTC

Well, the cardinality hint was nice while it lasted.

From an Oracle support TAR where I demonstrated selectivity is no longer working in 10g, the Oracle response:

"There is no SELECTIVITY hint in 10g. There is a CARDINALITY hint in 10g and 9.2"

"As the SELECTIVITY hint is not a documented item there is no bug here but you can use the undocumented CARDINALITY hint instead, but it too may be obsoleted in future. "

Tom Kyte
January 25, 2005 - 7:24 pm UTC

big difference between "might" and "will"

it is there (just used it the other day)
and it is used in some places (so a bit of safety there)

hint

reader, March 08, 2005 - 6:20 am UTC

Tom,

In one of our queries, with optimizer mode "choose", the query was taking 4 minutes. We have analyzed all the tables and the indexes were in place. But, cbo was not using the indexes of a few tables..When we used the hint "first_rows", it took only 4 seconds.

Like to know the following:

Does hint "first_rows" returns the rows without sorting when order by clause is used in the query?

Is the usage of hint here correct keeping the response time in view?

Regards

Tom Kyte
March 08, 2005 - 8:04 am UTC

if you have an order by

AND

the database returned the data not ordered by your order by

THEN

you discovered a bug



the query must return the same results regardless of any hinting you do.

More good hints...

Tamilselvan, March 08, 2005 - 1:33 pm UTC

Tom,
You said:
5) I put hints into two catagories (documented this in my new book effective Oracle by design)

good hints give the optimizer more information -- not directions on what to do.
good hints include

o first rows
o all rows
o driving site
o cardinality
o optimizer dynamic sampling

====
But in my experience, ORDERED and NO_MERGE are also very good hints in tuning the SQL statements.
I used both the hints cautiously after fully knowing the data cardinalities among the columns, tables used and their growth.

Do you see any thing wrong in using those 2 hints?

Thanks
Tamil


Tom Kyte
March 08, 2005 - 3:17 pm UTC

Yes, if you are forced to use a hint, it means SOMETHING IS WRONG.

That something is very often the statistics (things used by the optimizer to make decisions) and that should be fixed/corrected.



hint

reader, March 08, 2005 - 11:44 pm UTC

Tom,

I got the actual query from the onsite team...
I give below the query and the execution plan details with and without "first_rows" hint.

Like to know why the index got skipped when the hint clause is not used and the response time more in spite of statisitics and cbo=choose.

Also, in "no-hint" scenario the cost is very less but the response time is more whereas with "hint", the response time is very less but the cost is more. Pl. explain this phenomena...

Will the rows returned be the same in both the cases?

Thanks and Regards

======================================================


Query:

SELECT stationary_id,
cod_last_conf_txn_status,
cod_corr_branch,
cod_corr_bank,
cod_liqn_branch,
txn_id,
cod_last_conf_event,
cod_txn_status,
cod_last_proposed_event,
last_user_id,
last_event_date,
stationary_layout_id,
printing_queue_id,
cheque_number,
micr_no,
sort_code,
transaction_code,
cheque_date,
stale_date,
issue_date,
purge_date,
liquidation_date,
void_date,
stop_date,
cod_dept,
instrument_available,
version_no,
curr_auth_level,
req_auth_level,
acc_no_bene,
cod_issuing_branch,
book_date,
activation_date,
printing_due_date,
dispatch_due_date,
issue_due_date,
funding_due_date,
settlement_due_date,
cod_delivery_method,
ft_reference,
cancel_date,
last_cheque_number,
last_ft_reference
FROM (SELECT stationary_id,
cod_last_conf_txn_status,
cod_corr_branch,
cod_corr_bank,
cod_liqn_branch,
txn_id,
cod_last_conf_event,
cod_txn_status,
cod_last_proposed_event,
last_user_id,
last_event_date,
stationary_layout_id,
printing_queue_id,
cheque_number,
micr_no, sort_code,
transaction_code,
cheque_date,
stale_date,
issue_date,
purge_date,
liquidation_date,
void_date, stop_date,
cod_dept,
instrument_available,
version_no,
curr_auth_level,
req_auth_level,
acc_no_bene,
cod_issuing_branch,
book_date,
activation_date,
printing_due_date,
dispatch_due_date,
issue_due_date,
funding_due_date,
settlement_due_date,
cod_delivery_method,
ft_reference,
cancel_date,
last_cheque_number,
last_ft_reference
FROM oc_cheque_detail
WHERE txn_id IN (SELECT oc_cheque_detail.txn_id
FROM oc_cheque_detail, oc_cheque_interface
WHERE oc_cheque_detail.txn_id =oc_cheque_interface.txn_id
AND oc_cheque_detail.cod_txn_status != 'TRFPFL')
ORDER BY txn_id)
WHERE ROWNUM < 12
MINUS
SELECT
stationary_id,
cod_last_conf_txn_status,
cod_corr_branch,
cod_corr_bank,
cod_liqn_branch,
txn_id,
cod_last_conf_event,
cod_txn_status,
cod_last_proposed_event,
last_user_id,
last_event_date,
stationary_layout_id,
printing_queue_id,
cheque_number,
micr_no,
sort_code,
transaction_code,
cheque_date,
stale_date,
issue_date,
purge_date,
liquidation_date,
void_date,
stop_date,
cod_dept,
instrument_available,
version_no,
curr_auth_level,
req_auth_level,
acc_no_bene,
cod_issuing_branch,
book_date,
activation_date,
printing_due_date,
dispatch_due_date,
issue_due_date,
funding_due_date,
settlement_due_date,
cod_delivery_method,
ft_reference,
cancel_date,
last_cheque_number,
last_ft_reference
FROM (SELECT stationary_id,
cod_last_conf_txn_status,
cod_corr_branch,
cod_corr_bank,
cod_liqn_branch,
txn_id,
cod_last_conf_event,
cod_txn_status,
cod_last_proposed_event,
last_user_id,
last_event_date,
stationary_layout_id,
printing_queue_id,
cheque_number,
micr_no, sort_code,
transaction_code,
cheque_date,
stale_date,
issue_date,
purge_date,
liquidation_date,
void_date,
stop_date,
cod_dept,
instrument_available,
version_no,
curr_auth_level,
req_auth_level,
acc_no_bene,
cod_issuing_branch,
book_date,
activation_date,
printing_due_date,
dispatch_due_date,
issue_due_date,
funding_due_date,
settlement_due_date,
cod_delivery_method,
ft_reference,
cancel_date,
last_cheque_number,
last_ft_reference
FROM oc_cheque_detail
WHERE txn_id IN (SELECT oc_cheque_detail.txn_id
FROM oc_cheque_detail, oc_cheque_interface
WHERE oc_cheque_detail.txn_id =oc_cheque_interface.txn_id
AND oc_cheque_detail.cod_txn_status != 'TRFPFL')
ORDER BY txn_id)
WHERE ROWNUM < 1
ORDER BY txn_id;
==============================================

without hint:

11 rows selected.

Elapsed: 00:03:27.16

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=161286 Card=995070 B
ytes=471663180)

1 0 MINUS
2 1 SORT (UNIQUE) (Cost=47229 Card=497535 Bytes=235831590)
3 2 COUNT (STOPKEY)
4 3 VIEW (Cost=13815 Card=497535 Bytes=235831590)
5 4 MERGE JOIN (Cost=13815 Card=497535 Bytes=68659830)
6 5 VIEW OF 'VW_NSO_1' (Cost=3366 Card=497535 Bytes=
6467955)

7 6 SORT (UNIQUE) (Cost=3366 Card=497535 Bytes=134
33445)

8 7 HASH JOIN (Cost=845 Card=497536 Bytes=134334
72)

9 8 INDEX (FAST FULL SCAN) OF 'SYS_C0015178' (
UNIQUE) (Cost=99 Card=526802 Bytes=5268020)

10 8 TABLE ACCESS (FULL) OF 'OC_CHEQUE_DETAIL'
(Cost=588 Card=497536 Bytes=8458112)

11 5 SORT (JOIN) (Cost=10449 Card=526802 Bytes=658502
50)

12 11 TABLE ACCESS (FULL) OF 'OC_CHEQUE_DETAIL' (Cos
t=588 Card=526802 Bytes=65850250)

13 1 SORT (UNIQUE) (Cost=47229 Card=497535 Bytes=235831590)
14 13 COUNT (STOPKEY)
15 14 VIEW (Cost=13815 Card=497535 Bytes=235831590)
16 15 MERGE JOIN (Cost=13815 Card=497535 Bytes=68659830)
17 16 VIEW OF 'VW_NSO_2' (Cost=3366 Card=497535 Bytes=
6467955)

18 17 SORT (UNIQUE) (Cost=3366 Card=497535 Bytes=134
33445)

19 18 HASH JOIN (Cost=845 Card=497536 Bytes=134334
72)

20 19 INDEX (FAST FULL SCAN) OF 'SYS_C0015178' (
UNIQUE) (Cost=99 Card=526802 Bytes=5268020)

21 19 TABLE ACCESS (FULL) OF 'OC_CHEQUE_DETAIL'
(Cost=588 Card=497536 Bytes=8458112)

22 16 SORT (JOIN) (Cost=10449 Card=526802 Bytes=658502
50)

23 22 TABLE ACCESS (FULL) OF 'OC_CHEQUE_DETAIL' (Cos
t=588 Card=526802 Bytes=65850250)





Statistics
----------------------------------------------------------
3397 recursive calls
560 db block gets
21926 consistent gets
27113 physical reads
66340 redo size
4371 bytes sent via SQL*Net to client
1901 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
2 sorts (disk)
11 rows processed

============================================================

with hint:

11 rows selected.

Elapsed: 00:00:33.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=2150894 Ca
rd=995070 Bytes=471663180)

1 0 MINUS
2 1 SORT (UNIQUE) (Cost=1042033 Card=497535 Bytes=235831590)
3 2 COUNT (STOPKEY)
4 3 VIEW (Cost=1008619 Card=497535 Bytes=235831590)
5 4 SORT (ORDER BY STOPKEY) (Cost=1008619 Card=497535
Bytes=68659830)

6 5 NESTED LOOPS (Cost=998436 Card=497535 Bytes=6865
9830)

7 6 VIEW OF 'VW_NSO_1' (Cost=3366 Card=497535 Byte
s=6467955)

8 7 SORT (UNIQUE) (Cost=3366 Card=497535 Bytes=1
3433445)

9 8 HASH JOIN (Cost=845 Card=497536 Bytes=1343
3472)

10 9 INDEX (FAST FULL SCAN) OF 'SYS_C0015178'
(UNIQUE) (Cost=99 Card=526802 Bytes=5268020)

11 9 TABLE ACCESS (FULL) OF 'OC_CHEQUE_DETAIL
' (Cost=588 Card=497536 Bytes=8458112)

12 6 TABLE ACCESS (BY INDEX ROWID) OF 'OC_CHEQUE_DE
TAIL' (Cost=2 Card=526802 Bytes=65850250)

13 12 INDEX (UNIQUE SCAN) OF 'SYS_C0015197' (UNIQU
E) (Cost=1 Card=526802)

14 1 SORT (UNIQUE) (Cost=1042033 Card=497535 Bytes=235831590)
15 14 COUNT (STOPKEY)
16 15 VIEW (Cost=1008619 Card=497535 Bytes=235831590)
17 16 SORT (ORDER BY STOPKEY) (Cost=1008619 Card=497535
Bytes=68659830)

18 17 NESTED LOOPS (Cost=998436 Card=497535 Bytes=6865
9830)

19 18 VIEW OF 'VW_NSO_2' (Cost=3366 Card=497535 Byte
s=6467955)

20 19 SORT (UNIQUE) (Cost=3366 Card=497535 Bytes=1
3433445)

21 20 HASH JOIN (Cost=845 Card=497536 Bytes=1343
3472)

22 21 INDEX (FAST FULL SCAN) OF 'SYS_C0015178'
(UNIQUE) (Cost=99 Card=526802 Bytes=5268020)

23 21 TABLE ACCESS (FULL) OF 'OC_CHEQUE_DETAIL
' (Cost=588 Card=497536 Bytes=8458112)

24 18 TABLE ACCESS (BY INDEX ROWID) OF 'OC_CHEQUE_DE
TAIL' (Cost=2 Card=526802 Bytes=65850250)

25 24 INDEX (UNIQUE SCAN) OF 'SYS_C0015197' (UNIQU
E) (Cost=1 Card=526802)





Statistics
----------------------------------------------------------
1354 recursive calls
216 db block gets
1133796 consistent gets
19494 physical reads
26552 redo size
4371 bytes sent via SQL*Net to client
1917 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
1 sorts (disk)
11 rows processed

===========================================================


Tom Kyte
March 09, 2005 - 7:36 am UTC

when you look at the statistics, you see that the first query did more physical IO in this case (but less IO overall)

Statistics
----------------------------------------------------------
3397 recursive calls
560 db block gets
21926 consistent gets
27113 physical reads
66340 redo size
4371 bytes sent via SQL*Net to client
1901 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
2 sorts (disk)
11 rows processed

Statistics
----------------------------------------------------------
1354 recursive calls
216 db block gets
1133796 consistent gets
19494 physical reads
26552 redo size
4371 bytes sent via SQL*Net to client
1917 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
1 sorts (disk)
11 rows processed


the optimizer did the "right thing" here given the information it has. do you have system statistics collected for you system?

hint

reader, March 11, 2005 - 3:08 am UTC

Tom,

Sorry....System statistics not at hand now. If you can tell what system statistics are required, I will arrange for the same.

In the absence of system statistics, what is your guess?


Usage of hint is okay in this scenario keeping the good performance gain in view?

Regards.


Tom Kyte
March 11, 2005 - 6:15 am UTC

you gather them with dbms_stats.

guess = use first_rows for now

hint

reader, March 15, 2005 - 11:09 pm UTC

Tom,

We did schema level analysis using dbms_stats..
Is usage of hint okay in the above illustrated case? Your opinion matters a lot to us.

Regards

Tom Kyte
March 16, 2005 - 8:09 am UTC

yes.

Alex, May 26, 2005 - 3:17 pm UTC

Hello,

Tom I was wondering, If I use an access path hint, INDEX_FFS for example, and the optimizer does not use it, does that just mean I am telling it to do something that is wrong? I'm a little confused because I've seen you demonstrate many many times using hints that indexes can result in poor performance. So I'm expecting either better or worse performance using an index fast full scan and not the same unchanged plan. I know a hint is not an order, but rather a suggestion, so it's possible it will be ignored if you are way off?

Tom Kyte
May 26, 2005 - 3:31 pm UTC

the optimizer will use a hint if it can.

if you say "index_ffs" on a query like:

create table t ( x int );
create index t_idx on t(x);

select /*+ index_ffs( t t_idx ) */ *
from t
/


the optmizer will refuse to follow your hint (that is why I say a hint like that is not a directive, it is a hint. a directive would cause the query to fail if it couldn't be followed)

the optimizer will refuse because X is nullable and entirey null entries are not made in the b*tree and using the index would return the wrong answer.


So, if the hint is being ignored, it is most likely *(MOST likely)* because it cannot be followed.


/*+ ORDERED */ hint

Alexey, August 25, 2005 - 7:38 am UTC

Hi Tom,

What you think about /*+ORDERED */ hint?
Is it "good" or "bad" in your classification?

I think when we trying to join say 20 tables it's absolutely necessary to use this hint, because it's quite possible the optimizer will make a mistake.

Am I wrong?

Tom Kyte
August 25, 2005 - 8:45 am UTC

I am not a fan of the ordered hint except to show that the optimizer was wrong and patch correct a query (but file a tar).

with 20 tables or 2.

use of cardinality hint

Menon, November 15, 2005 - 4:05 pm UTC

Hi Tom
We have a situation where we only need one day's data in a table. A batch runs nightly , truncates the table, repopulates it with that day's data. Then subsequent queries in the batch use that table's data to do their job. Now in this case, since the table data changes a lot, the stats are going to be out of sync. I was wondering if cardinality hint could be used here since we know exactly how many rows this table has. Only issue I see is that all queries would have to be dynamic. Alternatively, we can add an approximate amount of cardinality (which we know) to the queries. What do you think?

Tom Kyte
November 16, 2005 - 8:24 am UTC

use dbms_stats.set_XXXX functions

you know the number of rows?
tell us...

you know the high low values for some columns of interest?
tell us....

and so on. Or just gather stats if you don't know.

(and if the cardinality is "fixed", then the basic table stats won't be "very different", you can just set them to the constants they sound like them might be)

thanx!

Menon, November 16, 2005 - 3:55 pm UTC

I guess the advantage over cardinality hint would be that you wont have to use dynamic sql to set the cardinality to different values at run time. This could be useful in dealing with temp tables when we insert some data in them and then use them in subsequent queries...

Thanx!

hints not comes to effect

rajeesh, November 25, 2005 - 12:48 am UTC

hi tom,

the above discussion is very helpful.thank you very much.

my problem is here if use the hint "USE_HASH" and ||''(to supress the second index scan),it will not comes  to effect.whats the problem with this.

  1  SELECT PNAME, PFHNAME, PPhone, DREGNO, PSEX,
  2     To_Char(PDOB, 'dd/MM/yyyy')  ,PBLDGRP,To_Char(PREGDATE, 'dd/MM/yyyy') FROM
  3    IHTPLUSER.PATREGN P   WHERE P.PNAME='werrwrwe rrwer rwerrwe' AND
  4    P.PFHNAME='erwrwr' AND P.PSEX='F' AND P.PBLDGRP='B+ve' AND P.ISACTIVE ='Y' AND
  5*     DREGNO LIKE 'GR%'  AND PADDR1 LIKE 'dfsfsfdfw%' ORDER BY P.PREGNO
SQL> /

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1 Bytes=61)
   1    0   SORT (ORDER BY) (Cost=7 Card=1 Bytes=61)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'PATREGN' (Cost=5 Card=
          1 Bytes=61)

   3    2       INDEX (RANGE SCAN) OF 'IX_DREGNO' (NON-UNIQUE) (Cost=3
           Card=27)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

 

Tom Kyte
November 25, 2005 - 10:06 am UTC

I see no example to work with.

It does look like you are using SYS to test with - stop doing that, don't do that, don't use sys for anything - use sysdba/sysoper to startup/shutdown but that is about it.


If you give a complete example, we can take a look.

"skip hint"?

A reader, March 15, 2006 - 6:15 pm UTC

we 've to maintain a "legacy" application that has been developed in 7.3.x days and makes excessive use of hints. now we are on 9iR2 and one of those queries causes serious performance problems. just removing that query's hint would obviously speed it up as needed -- but that query is issued by a client we can't modify/deploy at will ...

do we have any option to achieve our goal of *not* using that hint any longer *without* touching the client at all?


Tom Kyte
March 16, 2006 - 7:42 am UTC

You might be able to use stored outlines, you would capture the query - get it to use the right plan in some other session and replace the stored outline - and then enable the use of stored outlines.


If you are MAINTAINING an application - you better have the ability to TOUCH the code.

Should I use hints here?

A reader, September 17, 2007 - 3:20 am UTC

SQL before using Hints
INSERT INTO G_CSLPURCHASE_INSTALL (VENDOR_ID, PERIOD, ORGAN_ID, ORDER_PREFIX,
ORDER_NO, ORDER_DATE, ITEM_NO, SALES_QTY, AVA_QTY, INV_AMT, GP_ADJ,
SALES_COST) (SELECT S.VENDOR_ID, :B4 , S.ORGAN_ID, S.ORDER_PREFIX,
S.ORDER_NO, S.ORDER_DATE, S.ITEM_NO, S.SALES_QTY, S.SALES_QTY-NVL(SUM
(S2.DFI_TOTAL), 0), NVL(SUM (S2.INV_AMT), 0), NVL(SUM (S2.GP_ADJ), 0),
S.SALES_COST FROM F_CSLSALES S, D_CSLSALESTL S2 WHERE S.SALES_ID=
S2.SALES_ID (+) AND S2.GL_DATE (+) <= :B1 AND S2.VOUCHER_TYPE (+) ='I' AND
(S.SALES_QTY<>NVL(S2.DFI_TOTAL , 0) OR S.AVA_QTY!=0 OR (S.SALES_QTY=
S2.DFI_TOTAL AND S.ORDER_DATE>=:B3 )) AND S.SALES_ID>=0 AND S.ORDER_DATE
BETWEEN :B2 AND :B1 AND S.ITEM_TYPE='I' GROUP BY S.VENDOR_ID, S.ORGAN_ID,
S.ORDER_PREFIX, S.ORDER_NO, S.ORDER_DATE, S.ITEM_NO, S.SALES_QTY,
S.SALES_COST)



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 64.93 77.96 27815 8956556 4845 23328
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 64.93 77.96 27815 8956556 4845 23328

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 19 (BPSADM) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
23328 SORT GROUP BY (cr=8954402 pr=27617 pw=0 time=77306167 us)
23350 FILTER (cr=8954402 pr=27617 pw=0 time=62070142 us)
141897 NESTED LOOPS OUTER (cr=8954402 pr=27617 pw=0 time=105177096 us)
141875 TABLE ACCESS BY INDEX ROWID F_CSLSALES (cr=24121 pr=23960 pw=0 time=5836239 us)
511452 INDEX RANGE SCAN CSLSALES05 (cr=5438 pr=5438 pw=0 time=2058288 us)(object id 174134)
119112 PARTITION RANGE ITERATOR PARTITION: 1 KEY (cr=8930281 pr=3657 pw=0 time=72240965 us)
119112 TABLE ACCESS BY LOCAL INDEX ROWID D_CSLSALESTL PARTITION: 1 KEY (cr=8930281 pr=3657 pw=0 time=62423597 us)
119112 INDEX RANGE SCAN D_CSLSALESTL_IDX PARTITION: 1 KEY (cr=8811172 pr=1672 pw=0 time=39868234 us)(object id 227341)


Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT MODE: CHOOSE
23328 SORT (GROUP BY)
23350 FILTER
141897 NESTED LOOPS (OUTER)
141875 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'F_CSLSALES' (TABLE)
511452 INDEX MODE: ANALYZED (RANGE SCAN) OF 'CSLSALES05'
(INDEX)
119112 PARTITION RANGE (ITERATOR) PARTITION: START=1KEY
119112 TABLE ACCESS MODE: ANALYZED (BY LOCAL INDEX ROWID) OF
'D_CSLSALESTL' (TABLE) PARTITION: START=1KEY
119112 INDEX MODE: ANALYZED (RANGE SCAN) OF
'D_CSLSALESTL_IDX' (INDEX) PARTITION: START=1KEY


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 27618 0.21 3.90
latch: cache buffers chains 2 0.00 0.00
********************************************************************************

SQL when using Hints
********************************************************************************

INSERT INTO G_CSLPURCHASE_INSTALL (VENDOR_ID, PERIOD, ORGAN_ID, ORDER_PREFIX,
ORDER_NO, ORDER_DATE, ITEM_NO, SALES_QTY, AVA_QTY, INV_AMT, GP_ADJ,
SALES_COST) (SELECT /*+ FULL(s) FULL(s2) */ S.VENDOR_ID, :B4 , S.ORGAN_ID,
S.ORDER_PREFIX, S.ORDER_NO, S.ORDER_DATE, S.ITEM_NO, S.SALES_QTY,
S.SALES_QTY-NVL(SUM (S2.DFI_TOTAL), 0), NVL(SUM (S2.INV_AMT), 0), NVL(SUM
(S2.GP_ADJ), 0), S.SALES_COST FROM F_CSLSALES S, D_CSLSALESTL S2 WHERE
S.SALES_ID=S2.SALES_ID (+) AND S2.GL_DATE (+) <= :B1 AND S2.VOUCHER_TYPE (+)
='I' AND (S.SALES_QTY<>NVL(S2.DFI_TOTAL , 0) OR S.AVA_QTY!=0 OR
(S.SALES_QTY=S2.DFI_TOTAL AND S.ORDER_DATE>=:B3 )) AND S.SALES_ID>=0 AND
S.ORDER_DATE BETWEEN :B2 AND :B1 AND S.ITEM_TYPE='I' GROUP BY S.VENDOR_ID,
S.ORGAN_ID, S.ORDER_PREFIX, S.ORDER_NO, S.ORDER_DATE, S.ITEM_NO,
S.SALES_QTY, S.SALES_COST)



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.69 2.35 2205 25482 4856 23328
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.69 2.35 2205 25482 4856 23328

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 19 (BPSADM) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
23328 SORT GROUP BY (cr=24642 pr=2205 pw=0 time=2188409 us)
23350 FILTER (cr=24642 pr=2205 pw=0 time=1025205 us)
141897 HASH JOIN OUTER (cr=24642 pr=2205 pw=0 time=1429532 us)
141875 TABLE ACCESS FULL F_CSLSALES (cr=22528 pr=138 pw=0 time=331799 us)
119112 PARTITION RANGE ITERATOR PARTITION: 1 KEY (cr=2114 pr=2067 pw=0 time=242515 us)
119112 TABLE ACCESS FULL D_CSLSALESTL PARTITION: 1 KEY (cr=2114 pr=2067 pw=0 time=285075 us)


Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT MODE: CHOOSE
23328 SORT (GROUP BY)
23350 FILTER
141897 HASH JOIN (OUTER)
141875 TABLE ACCESS MODE: ANALYZED (FULL) OF 'F_CSLSALES'
(TABLE)
119112 PARTITION RANGE (ITERATOR) PARTITION: START=1KEY
119112 TABLE ACCESS MODE: ANALYZED (FULL) OF 'D_CSLSALESTL'
(TABLE) PARTITION: START=1KEY


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 73 0.01 0.15
db file scattered read 81 0.01 0.24
latch: cache buffers chains 1 0.00 0.00
********************************************************************************
Tom Kyte
September 18, 2007 - 2:07 pm UTC

i would guess that if you compare the explain plan (with ESTIMATED CARD= values) to the actual, you would discover they are very far off from each other.

then we ask "why" and try to fix that (so it fixes LOTS of stuff, not making you hand program query after query after query)

Official Position of Oracle

Sandro, October 02, 2007 - 5:14 am UTC

Hi Tom,
you wrote:
<<...do not use hints. That will be your last choice -- never the first choice and never standard operating procedure.
The problem is -- we may well use your hints and your hints are just *wrong*, slower, precluding us from taking a better path.
Hints -- only useful if you are in RBO and you want to make use of an access path...>>

You would be able points out me of the references to Oracle official documentations (white paper also) in 8i, 9i, 10g,....

Thanks in advance.
Tom Kyte
October 03, 2007 - 4:43 pm UTC

reference to what?

the documentation tells you what you can do.
the documentation doesn't tell you what to do.

that is what we do, all of us, together.





RBO/hints for this sql?

Ann, January 23, 2008 - 12:25 am UTC

Hi Tom,

I have a sql that seems to perform much better when using rule-based optimizer.  With rule hint,it completes in seconds  .  If using COB, it complete in 3 minutes.  I have tried all the good hints too but to no avail.  I would like to know if there is any other means for me to improve this sql without restoring to using rule hint.  Version is 10.2.0.3. Thanks

SELECT count(*)
FROM PS_COMBO_SEL_15 A, PS_PROJECT_ID_VW B WHERE A.SETID = 'GROUP' AND A.PROCESS_GROUP = 'PROJ_COMB' AND 
A.CHARTFIELD = 'PROJECT_ID' AND  B.PROJECT_ID >=A.RANGE_FROM_15 AND B.PROJECT_ID <= A.RANGE_TO_15 AND 
B.SETID = 'GROUP';  2    3    4  

  COUNT(*)
----------
     13972

Elapsed: 00:03:40.69

Execution Plan
----------------------------------------------------------
Plan hash value: 672710270

--------------------------------------------------------------------------------
--------------------------

| Id  | Operation                      | Name            | Rows  | Bytes |TempSp
c| Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
--------------------------

|   0 | SELECT STATEMENT               |                 |     1 |    65 |
 | 59706   (1)| 00:11:57 |

|   1 |  SORT AGGREGATE                |                 |     1 |    65 |
 |            |          |

|   2 |   MERGE JOIN                   |                 |   955K|    59M|
 | 59706   (1)| 00:11:57 |

|   3 |    SORT JOIN                   |                 | 20549 |   401K|
 | 59159   (1)| 00:11:50 |

|*  4 |     TABLE ACCESS BY INDEX ROWID| PS_PROJECT      | 20549 |   401K|
 | 59159   (1)| 00:11:50 |

|*  5 |      INDEX RANGE SCAN          | PSBPROJECT      | 90018 |       |
 |   453   (1)| 00:00:06 |

|*  6 |    FILTER                      |                 |       |       |
 |            |          |

|*  7 |     SORT JOIN                  |                 | 18598 |   817K|  2200
K|   286   (2)| 00:00:04 |

|*  8 |      TABLE ACCESS FULL         | PS_COMBO_SEL_15 | 18598 |   817K|
 |    69   (2)| 00:00:01 |

--------------------------------------------------------------------------------
--------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("EFF_STATUS"='A' AND "SUMMARY_PRJ"='N')
   5 - access("SET_OVERRIDE"='GROUP')
   6 - filter("PROJECT_ID"<="A"."RANGE_TO_15")
   7 - access(INTERNAL_FUNCTION("PROJECT_ID")>=INTERNAL_FUNCTION("A"."RANGE_FROM
_15"))

       filter(INTERNAL_FUNCTION("PROJECT_ID")>=INTERNAL_FUNCTION("A"."RANGE_FROM
_15"))

   8 - filter("A"."PROCESS_GROUP"='PROJ_COMB' AND "A"."SETID"='GROUP' AND
              "A"."CHARTFIELD"='PROJECT_ID')


Statistics
----------------------------------------------------------
         50  recursive calls
          0  db block gets
      58888  consistent gets
          0  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT /*+ RULE */ count(*)
FROM PS_COMBO_SEL_15 A, PS_PROJECT_ID_VW B WHERE A.SETID = 'GROUP' AND A.PROCESS_GROUP = 'PROJ_COMB' AND 
A.CHARTFIELD = 'PROJECT_ID' AND  B.PROJECT_ID >=A.RANGE_FROM_15 AND B.PROJECT_ID <= A.RANGE_TO_15 AND 
B.SETID = 'GROUP';  2    3    4  

  COUNT(*)
----------
     13972

Elapsed: 00:00:01.27

Execution Plan
----------------------------------------------------------
Plan hash value: 3739873021

--------------------------------------------------------
| Id  | Operation                    | Name            |
--------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |
|   1 |  SORT AGGREGATE              |                 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| PS_PROJECT      |
|   3 |    NESTED LOOPS              |                 |
|*  4 |     INDEX RANGE SCAN         | PSCCOMBO_SEL_15 |
|*  5 |     INDEX RANGE SCAN         | PSBPROJECT      |
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("EFF_STATUS"='A' OR "EFF_STATUS"='I') AND
              "SUMMARY_PRJ"='N' AND "EFF_STATUS"='A')
   4 - access("A"."SETID"='GROUP' AND "A"."PROCESS_GROUP"='PROJ_COMB'
              AND "A"."CHARTFIELD"='PROJECT_ID')
   5 - access("SET_OVERRIDE"='GROUP' AND
              "PROJECT_ID">="A"."RANGE_FROM_15" AND "PROJECT_ID"<="A"."RANGE_TO_
15")


Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
         29  recursive calls
          0  db block gets
      73057  consistent gets
        521  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT /*+ FIRST_ROWS */ count(*)
  2  FROM PS_COMBO_SEL_15 A, PS_PROJECT_ID_VW B WHERE A.SETID = 'GROUP' AND A.PROCESS_GROUP = 'PROJ_COMB' AND 
  3  A.CHARTFIELD = 'PROJECT_ID' AND  B.PROJECT_ID >=A.RANGE_FROM_15 AND B.PROJECT_ID <= A.RANGE_TO_15 AND 
  4  B.SETID = 'GROUP';

  COUNT(*)
----------
     13972

Elapsed: 00:03:32.47

Execution Plan
----------------------------------------------------------
Plan hash value: 672710270

--------------------------------------------------------------------------------
--------------------------

| Id  | Operation                      | Name            | Rows  | Bytes |TempSp
c| Cost (%CPU)| Time     |

--------------------------------------------------------------------------------
--------------------------

|   0 | SELECT STATEMENT               |                 |     1 |    65 |
 | 59706   (1)| 00:11:57 |

|   1 |  SORT AGGREGATE                |                 |     1 |    65 |
 |            |          |

|   2 |   MERGE JOIN                   |                 |   955K|    59M|
 | 59706   (1)| 00:11:57 |

|   3 |    SORT JOIN                   |                 | 20549 |   401K|
 | 59159   (1)| 00:11:50 |

|*  4 |     TABLE ACCESS BY INDEX ROWID| PS_PROJECT      | 20549 |   401K|
 | 59159   (1)| 00:11:50 |

|*  5 |      INDEX RANGE SCAN          | PSBPROJECT      | 90018 |       |
 |   453   (1)| 00:00:06 |

|*  6 |    FILTER                      |                 |       |       |
 |            |          |

|*  7 |     SORT JOIN                  |                 | 18598 |   817K|  2200
K|   286   (2)| 00:00:04 |

|*  8 |      TABLE ACCESS FULL         | PS_COMBO_SEL_15 | 18598 |   817K|
 |    69   (2)| 00:00:01 |

--------------------------------------------------------------------------------
--------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("EFF_STATUS"='A' AND "SUMMARY_PRJ"='N')
   5 - access("SET_OVERRIDE"='GROUP')
   6 - filter("PROJECT_ID"<="A"."RANGE_TO_15")
   7 - access(INTERNAL_FUNCTION("PROJECT_ID")>=INTERNAL_FUNCTION("A"."RANGE_FROM
_15"))

       filter(INTERNAL_FUNCTION("PROJECT_ID")>=INTERNAL_FUNCTION("A"."RANGE_FROM
_15"))

   8 - filter("A"."PROCESS_GROUP"='PROJ_COMB' AND "A"."SETID"='GROUP' AND
              "A"."CHARTFIELD"='PROJECT_ID')


Statistics
----------------------------------------------------------
         15  recursive calls
          0  db block gets
      58882  consistent gets
          0  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 

Tom Kyte
January 23, 2008 - 7:55 am UTC

everything you've shown here would indicate the CBO is better - less logical IO - less physical IO


I'd like to see a tkprof, sqlplus timing is not something to look at - show the tkprof.

A reader, January 23, 2008 - 10:48 am UTC

Some of the opitmizer plans in 10g are weird when using sort operation
Tom Kyte
January 23, 2008 - 2:17 pm UTC

eh? no clue what you might mean by that.

RBO/hints for this sql?

Ann, January 24, 2008 - 12:34 am UTC

The tkprof is as below:

SELECT /*+ RULE */ count(*)
FROM PS_COMBO_SEL_15 A, PS_PROJECT_ID_VW B WHERE A.SETID = :"SYS_B_0" AND A.PROCESS_GROUP =
:"SYS_B_1" AND
A.CHARTFIELD = :"SYS_B_2" AND B.PROJECT_ID >=A.RANGE_FROM_15 AND B.PROJECT_ID <= A.RANGE_TO_15
AND
B.SETID = :"SYS_B_3"

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.99 2.06 621 73050 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.99 2.07 621 73050 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=73050 pr=621 pw=0 time=2066400 us)
13972 TABLE ACCESS BY INDEX ROWID PS_PROJECT (cr=73050 pr=621 pw=0 time=1031622 us)
37201 NESTED LOOPS (cr=56405 pr=535 pw=0 time=5171162 us)
18600 INDEX RANGE SCAN PSCCOMBO_SEL_15 (cr=521 pr=521 pw=0 time=1035184 us)(object id 106640)
18600 INDEX RANGE SCAN PSBPROJECT (cr=55884 pr=14 pw=0 time=416993 us)(object id 98900)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 621 0.04 1.16
SQL*Net message from client 2 84.14 84.14
********************************************************************************

SELECT /*+ FIRST_ROWS */ count(*)
FROM PS_COMBO_SEL_15 A, PS_PROJECT_ID_VW B
WHERE A.SETID = :"SYS_B_0" AND A.PROCESS_GROUP = :"SYS_B_1" AND
A.CHARTFIELD = :"SYS_B_2" AND B.PROJECT_ID >=A.RANGE_FROM_15 AND B.PROJECT_ID <=
A.RANGE_TO_15 AND B.SETID = :"SYS_B_3"

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.01 0.01 0 0 0 0
Fetch 2 242.65 237.60 1850 58878 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 242.67 237.62 1850 58878 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=58878 pr=1850 pw=0 time=237605696 us)
13972 MERGE JOIN (cr=58878 pr=1850 pw=0 time=216581896 us)
18086 SORT JOIN (cr=58630 pr=1850 pw=0 time=1051093 us)
18086 TABLE ACCESS BY INDEX ROWID PS_PROJECT (cr=58630 pr=1850 pw=0 time=343745 us)
90217 INDEX RANGE SCAN PSBPROJECT (cr=451 pr=254 pw=0 time=90313 us)(object id 98900)
13972 FILTER (cr=248 pr=0 pw=0 time=233113794 us)
188188760 SORT JOIN (cr=248 pr=0 pw=0 time=188322636 us)
18600 TABLE ACCESS FULL PS_COMBO_SEL_15 (cr=248 pr=0 pw=0 time=359 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 1850 0.00 0.04
SQL*Net message from client 2 11.88 11.88

It seems to me that the execution plan for RBO is better as it processes fewer rows.

Thanks.
Tom Kyte
January 24, 2008 - 7:47 am UTC

yeah, it woefully underestimated the results of that sort join there - there is where the time is.


arg, the dreaded cursor sharing = force/similar :(


can you share your non-default init.ora settings, not all of them, just non-default settings.

Few observations and questions for both Ann and Tom ...

nameless, January 24, 2008 - 12:38 pm UTC

A:

Parsing user id: SYS

I don't think it makes a difference but still, maybe don't use SYS ... this seems to be an application accessed through public synonyms.

B:

yeah, it woefully underestimated the results of that sort join there - there is where the time is.

In the original autotrace from Ann there are these on that particular sort join:

7 - access(INTERNAL_FUNCTION("PROJECT_ID")>=INTERNAL_FUNCTION("A"."RANGE_FROM_15"))
    filter(INTERNAL_FUNCTION("PROJECT_ID")>=INTERNAL_FUNCTION("A"."RANGE_FROM_15"))

Any idea what the INTERNAL_FUNCTION is?

C:

Would be curious to know what the definition of the PS_PROJECT_ID_VW view is ... seems to be based on a table named PS_PROJECT

D:

Interesting the RBO kept these predicates from that view:

   2 - filter(("EFF_STATUS"='A' OR "EFF_STATUS"='I') AND
              "SUMMARY_PRJ"='N' AND "EFF_STATUS"='A')

where the CBO recognized the EFF_STATUS='I' is not needed:

   4 - filter("EFF_STATUS"='A' AND "SUMMARY_PRJ"='N')

E:

SELECT count(*)
  FROM PS_COMBO_SEL_15 A, PS_PROJECT_ID_VW B
 WHERE A.SETID = 'GROUP'
   AND A.PROCESS_GROUP = 'PROJ_COMB'
   AND A.CHARTFIELD = 'PROJECT_ID'
   AND B.PROJECT_ID >= A.RANGE_FROM_15
   AND B.PROJECT_ID <= A.RANGE_TO_15
   AND B.SETID = 'GROUP';

Does adding A.SETID = B.SETID make any difference?

Thx.

A reader, January 24, 2008 - 1:46 pm UTC

what if you don't use pga_aggregate_target and use manual work size policy and normal values for sort_area_size - the optimizer might automatically pick the best plan using CBO
Tom Kyte
January 24, 2008 - 1:48 pm UTC

rather than guess.... or hack away... it is best to get information. I would like to see how they have set the memory parameters

A reader, January 24, 2008 - 2:08 pm UTC

I agree

A reader, January 24, 2008 - 2:20 pm UTC

SELECT DISTINCT A.FP_ID FP_ID, A.MIRROR_FPID MIRROR_FPID, A.LINK_ID LINK_ID, A.ENTITY ENTITY, A.DERIVATIVE DERIVATIVE, A.HEDGE_ITEM HEDGE_ITEM, A.HYPO HYPO FROM
(
SELECT HLD.FP_ID FP_ID, LR.LINKED_POSITION_ID MIRROR_FPID, HLD.LINK_ID LINK_ID, HLD.LINK_ENTITY ENTITY, HLD.DERIVATIVE DERIVATIVE, HLD.HEDGE_ITEM HEDGE_ITEM, HLD.HYPO HYPO FROM HEDGE_DESIG_LIST_DAILY HLD, CR_FP_LINK_REPORT_DAILY LR WHERE HLD.LOAD_ID=20080114095839 AND HLD.LOAD_ID=LR.LOAD_ID AND HLD.FP_ID=LR.POSITION_ID AND EXISTS (SELECT 'x' FROM TRANSACTION_DTL_DAILY TDD WHERE TDD.LOAD_ID=HLD.LOAD_ID AND (TDD.PARTY_ID=HLD.FP_ID OR (TDD.PARTY_ID IS NULL AND TDD.ALT_TRADE_ID= HLD.FP_ID) ) AND TDD.TRADE_LEG='Pay')
UNION
SELECT HLD.FP_ID FP_ID, LR.POSITION_ID MIRROR_FPID, HLD.LINK_ID LINK_ID, HLD.LINK_ENTITY ENTITY, HLD.DERIVATIVE DERIVATIVE, HLD.HEDGE_ITEM HEDGE_ITEM, HLD.HYPO HYPO FROM HEDGE_DESIG_LIST_DAILY HLD, CR_FP_LINK_REPORT_DAILY LR WHERE HLD.LOAD_ID=20080114095839 AND HLD.LOAD_ID=LR.LOAD_ID AND HLD.FP_ID=LR.LINKED_POSITION_ID AND EXISTS (SELECT 'x' FROM TRANSACTION_DTL_DAILY TDD WHERE TDD.LOAD_ID=HLD.LOAD_ID AND (TDD.PARTY_ID=HLD.FP_ID OR (TDD.PARTY_ID IS NULL AND TDD.ALT_TRADE_ID= HLD.FP_ID) ) AND TDD.TRADE_LEG='Pay') AND NOT EXISTS (SELECT 'x' FROM HEDGE_DESIG_LIST_DAILY HLD2 WHERE HLD2.LOAD_ID=20080114095839 AND EXISTS (SELECT 'x' FROM CR_FP_LINK_REPORT_DAILY LR WHERE LR.LOAD_ID=HLD.LOAD_ID AND LR.POSITION_ID=HLD.FP_ID) AND EXISTS (SELECT 'x' FROM TRANSACTION_DTL_DAILY TDD WHERE TDD.LOAD_ID=HLD.LOAD_ID AND (TDD.PARTY_ID=HLD.FP_ID OR (TDD.PARTY_ID IS NULL AND TDD.ALT_TRADE_ID= HLD.FP_ID) ) AND TDD.TRADE_LEG='Pay') )
UNION
SELECT HLD.FP_ID FP_ID, '-1' MIRROR_FPID, HLD.LINK_ID LINK_ID, HLD.LINK_ENTITY ENTITY, HLD.DERIVATIVE DERIVATIVE, HLD.HEDGE_ITEM HEDGE_ITEM, HLD.HYPO HYPO FROM HEDGE_DESIG_LIST_DAILY HLD WHERE HLD.LOAD_ID=20080114095839 AND HLD.TRADE_ID=HLD.DERIVATIVE AND NOT EXISTS (SELECT 'x' FROM CR_FP_LINK_REPORT_DAILY FPL WHERE FPL.POSITION_ID=HLD.FP_ID) AND NOT EXISTS (SELECT 'x' FROM CR_FP_LINK_REPORT_DAILY FPL WHERE FPL.LINKED_POSITION_ID=HLD.FP_ID) AND HLD.FP_ID IS NOT NULL
) A


We had a similar issue, with the above SQL(which I had posted in another thread) where we were using automatic workarea_size_policy and the optimizer plan was not good and was taking 10 minutes to complete.

After changing at the session level (to use manual workarea)

alter session set workarea_size_policy=manual

alter session set sort_area_size=4000000

The above sql executed in a couple of seconds. If we had used UNION ALL instead of UNION, even automatic used to execute fine in a few seconds
Tom Kyte
January 24, 2008 - 2:42 pm UTC

that would likely mean your pga aggregate target was undersized and if set properly, you'd do even better on that machine than you do with manual.

A reader, January 24, 2008 - 2:47 pm UTC

dagstg@STGPRD1> show parameter pga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 3145728000
dagstg@STGPRD1>

3 GB is not a small value I guess, not much activity is going on in the db,even though it is prod

and for sga

A reader, January 24, 2008 - 2:49 pm UTC

dagstg@STGPRD1> show parameter target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 1000
pga_aggregate_target big integer 3145728000
sga_target big integer 3674210304

RBO/hints for this sql?

Ann, January 24, 2008 - 7:18 pm UTC

The non-default init parameters are
cursor_sharing='similar'
db_block_size=8192
db_cache_size=200M
db_files=300
fast_start_mttr_target=600
parallel_max_servers=32
parallel_min_servers=4
pga_aggregate_target=100M
processes=500
resumable_timeout=72000
session_cached_cursors=200
session_max_open_files=20
shared_pool_size=150M
workarea_size_policy='auto'
_complex_view_merging='false'
_unnest_subquery='false'

Unfortunately, I am not able to change the cursor_sharing parameter since this is a PeopleSoft ERP system and the parameter is a prerequisite set by the consultants who build this.

This is a test system so the resources are limited. However, I have tested by increasing the memory and the results are the same.

I have also tested queries by removing the undocumented
parameters and there is no difference in performances/explain plans with tkprof.

Thanks.
Tom Kyte
January 24, 2008 - 10:14 pm UTC

how much memory is on this server, these settings look pretty "small"

RBO/hints for this sql? (To nameless)

Ann, January 24, 2008 - 7:34 pm UTC

A.
I am not supposed to have the password for the schema. I can login to schema by changing the password and replacing it with original hashed value. It is a hassle but if the testing requires, I will do so.

B.
I have no idea. Both PROJECT_ID and RANGE_FROM_15 are varchar fields so it might be some sort of conversion or comparision function.

C.
The PS_PROJECT_ID_VW view is

SELECT SETID ,PROJECT_ID ,DESCR FROM PS_PROJECT_FS WHERE EFF_STATUS='A' AND SUMM
ARY_PRJ = 'N'

PS_PROJECT_FS view is

SELECT /*+ index(PS_PROJECT, PSBPROJECT) */ BUSINESS_UNIT , PROJECT_ID , LAST_ACTIVITY_ID , EFF_STATUS , IN_USE_SW , INTEGRATION_TMPL , DESCR , PROJECT_TYPE , BUSINESS_UNIT_AM , ASSET_ID , PROFILE_ID , SYSTEM_SOURCE , TEMPLATE_ID , TEMPLATE_SW , AFUDC_PROJ_SW , TARGET_PROJ_SW , SALES_TAX_PROJ_SW , DOCKET_NUMBER , PROJECT_FUNCTION , LAST_CRITERIA_ID , CURRENCY_CD , RT_TYPE , CUR_EFFDT_TYPE , PERCENT_COMPLETE , PC_ACT_OPTION , WBS_TREE_NAME , PROJECT_USER1 , PROJECT_USER2 , PROJECT_USER3 , PROJECT_USER4 , PROJECT_USER5 , PROJECT_USER_DT1 , PROJECT_USER_DT2 , PC_USER_CURRENCY , PROJECT_USERAMT1 , PROJECT_USERAMT2 , PROJECT_USERAMT3 , AN_GRP_ACTV_BUD , AN_GRP_TOT_COSTS , SET_OVERRIDE , DTTM_STAMP , PC_MSP_USR1 , PC_MSP_USR2 , PC_MSP_PROJ_ID , PC_REV_ACTIVITY , ENFORCE , enforce_type , TOLERANCE , BURDEN_PLAN , PC_SCH_PRODUCT , PC_SCH_FIELD1 , PC_SCH_FIELD2 , PC_SCH_FIELD3 , PC_SCH_FIELD4 , PC_SCH_FIELD5 , PC_SCH_FIELD6 , PC_SCH_FIELD7 , PC_SCH_FIELD8 , PC_INDENT_LEVEL , proj_grant_status , grant_flg , gm_primary_flag , pc_templa
te_id , pc_fnd_dist_sw , START_DT , END_DT , BASELINE_START_DT , BASELINE_FINISH_DT , EARLY_START_DT , EARLY_FINISH_DT , ACTUAL_START_DT , ACTUAL_FINISH_DT , LA
TE_START_DT , LATE_FINISH_DT , PC_DURATION , SUMMARY_PRJ , PC_CHC_SW , PC_CHC_TEMPLATE , PC_CALCULATE_SW , PC_PRJ_DEF_CALC_MT , PC_ACT_DEF_CALC_MT , PC_HOURS_PER_DAY , PC_CALC_METHOD , PC_SUM_METHOD , PM_AUTO_REVIEW , FORECAST_AUT_APP , FORECAST_LEVEL , FORECAST_RATE , FORECAST_SRC , PROJ_REQUEST_ID , PPK_PROJ_VERSION , PGM_SCHED_METHOD , PC_HEALTH_CALC , PC_HEALTH , PC_HEALTH_DTTM , PC_PERCENT_DTTM , HOLIDAY_LIST_ID , FMS_DTTM_STAMP , FMS_OPRID , FMS_LASTUPDDTTM , FMS_LASTUPDOPRID , LAST_CBUD_TYPE , LAST_RBUD_TYPE , SYNCID , SYNCDTTM FROM PS_PROJECT WHERE EFF_STATUS IN ('A', 'I')


E.
Adding A.SETID = B.SETID changes the execution plan for sql with first rows hint.

SELECT /*+ FIRST_ROWS */ count(*)
FROM PS_COMBO_SEL_15 A, PS_PROJECT_ID_VW B
WHERE A.SETID = :"SYS_B_0"
AND A.PROCESS_GROUP = :"SYS_B_1"
AND A.CHARTFIELD = :"SYS_B_2"
AND B.PROJECT_ID >= A.RANGE_FROM_15
AND B.PROJECT_ID <= A.RANGE_TO_15
AND B.SETID = :"SYS_B_3"
AND A.SETID=B.SETID

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.01 0 0 0 0
Fetch 2 182.15 178.57 0 58878 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 182.17 178.58 0 58878 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=58878 pr=0 pw=0 time=178574389 us)
13972 HASH JOIN (cr=58878 pr=0 pw=0 time=160899461 us)
18086 TABLE ACCESS BY INDEX ROWID PS_PROJECT (cr=58630 pr=0 pw=0 time=361811 us)
90217 INDEX RANGE SCAN PSBPROJECT (cr=451 pr=0 pw=0 time=90294 us)(object id 98900)
18600 TABLE ACCESS FULL PS_COMBO_SEL_15 (cr=248 pr=0 pw=0 time=56270 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 36.62 36.62
********************************************************************************

However, the processing time remains at 3 minute. I am concern about adding this as it might change the program logic.

merge join oddity ...

nameless, January 24, 2008 - 11:14 pm UTC

Ann:

If you have Cost-Based Oracle Fundamentals by Jonathan Lewis, turn to page 383 and you'll find a discussion about merge join with a range test ... that should explain these odd lines in your CBO row source:

13972 FILTER (cr=248 pr=0 pw=0 time=233113794 us)
188188760 SORT JOIN (cr=248 pr=0 pw=0 time=188322636 us)
18600 TABLE ACCESS FULL PS_COMBO_SEL_15 (cr=248 pr=0 pw=0 time=359 us)

which go with these predicates from your autotrace:

6 - filter("PROJECT_ID"<="A"."RANGE_TO_15")
7 - access(INTERNAL_FUNCTION("PROJECT_ID")>=INTERNAL_FUNCTION("A"."RANGE_FROM_15"))
filter(INTERNAL_FUNCTION("PROJECT_ID")>=INTERNAL_FUNCTION("A"."RANGE_FROM_15"))

As Jonathan explains the CPU penalties could be big ... and they are in your case: 233 out of the total 237 elapsed seconds are spent here. He doesn't offer a solution ... it may be that RULE to force that NESTED LOOPS may be the way to go.

Now, I'm not quite sure why the HASH JOIN managed to rack up 160 seconds out of 178 ... there is the equi-join now ... can you get an autotrace on that one too so we can see the predicate information as well?

HINT or NO HINT

Tamil, January 25, 2008 - 3:28 pm UTC

To Ann:

What are the values set for the parameters optimizer_index_caching and optimizer_index_cost_adj ?

The default values (0 and 100) are good for only DSS type systems.

Also, could you post 10053 trace file.

Thanks
Tamil

A reader, January 25, 2008 - 3:45 pm UTC

Change the value of optimizer_features_enable to 9203 :)

I am kidding. At this rate, Tom will have a lot of spare time going forward

cardinality hint

Michael Virnstein, February 19, 2008 - 11:45 am UTC


We have a top-n query like this:
select t1.a, 
       t1.b, 
       t2.x, 
       t2.z
  from (select t.*, rownum rn
          from (select *
                  from t
                 where a = :b1
                   and b = b2
                  order by x) t
         where rownum <= :bmax) t1,
       t2
 where rn >= :bmin
   and t1.y = t2.z
 order by rn

the top-n subquery (t1) returns max 15 rows, but the optimizer doesn't recognize this, because of the binds.
That's why the optimizer always uses a hash join with a table full scan on t2 when joining the top-n subquery (t1) to t2, although a nested loop using the index on t2.z would be much more appropriate in this situation.
I recognized, that using the cardinality hint is working and is giving me good results in the above situation, but i'm not sure, if there's a better approach than that:
select t1.a, 
       t1.b, 
       t2.x, 
       t2.z
  from (select /*+ CARDINALITY(15) */
               t.*, rownum rn
          from (select *
                  from t
                 where a = :b1
                   and b = b2
                  order by x) t
         where rownum <= :bmax) t1,
       t2
 where rn >= :bmin
   and t1.y = t2.z
 order by rn

Tom Kyte
February 19, 2008 - 5:14 pm UTC

what about first_rows(15)

RE: cardinality hint

Michael Virnstein, February 20, 2008 - 3:52 am UTC

> Followup February 19, 2008 - 5pm US/Eastern:
> what about first_rows(15)

No, that didn't work. 10g still uses a hash join with a full table scan. I tried a lot of possible solutions to the problem, but the only thing that really gave me the desired results, was the cardinality hint. I would use a stored plan, but the query is built dynamically and i can't store a plan for every query that's possible.

RE: cardinality hint

Michael Virnstein, February 20, 2008 - 12:17 pm UTC

I found a solution that isn't using the cardinality hint:

select t1.a, 
       t1.b, 
       t2.x, 
       t2.z
  from (Select *
          from (select t.*, rownum rn
                  from (select *
                          from t
                         where a = :b1
                           and b = b2
                         order by x) t
                 where rownum <= :bmax)
         where rn >= :bmin
           and rownum <= 15) t1,
        t2
 where t1.y = t2.z
 order by rn

no oracle knows, that the t1 only returns 15 rows.

When to use First_row Hint?

Rajesh from india, March 28, 2008 - 7:08 am UTC

Hi Tom,

Below i have illustrated a statement with its Execution plan.
==========================================================

select table_name from dba_tables where owner='SYS' AND table_name like '%$' ORDER BY 1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 NESTED LOOPS (OUTER)
4 3 NESTED LOOPS (OUTER)
5 4 NESTED LOOPS (OUTER)
6 5 NESTED LOOPS (OUTER)
7 6 NESTED LOOPS
8 7 NESTED LOOPS
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'USER$'
10 9 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE
)

11 8 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
12 11 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
13 7 TABLE ACCESS (CLUSTER) OF 'TAB$'
14 13 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQU
E)

15 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
16 15 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
17 5 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
18 4 TABLE ACCESS (CLUSTER) OF 'USER$'
19 18 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
20 3 TABLE ACCESS (CLUSTER) OF 'SEG$'
21 20 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQU
E)

22 2 TABLE ACCESS (CLUSTER) OF 'TS$'
23 22 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)

========================================================

I have run this statement with first_rows hint.Below is Execution plan for that.

select /*+ FIRST_ROWS(10) */ table_name from dba_tables where owner='SYS' AND table_name like '%$' ORDER BY 1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=45700141 C
ard=133512494 Bytes=35781348392)

1 0 SORT (ORDER BY) (Cost=45700141 Card=133512494 Bytes=357813
48392)

2 1 NESTED LOOPS (OUTER) (Cost=8 Card=133512494 Bytes=357813
48392)

3 2 NESTED LOOPS (OUTER) (Cost=8 Card=1634580 Bytes=416817
900)

4 3 NESTED LOOPS (Cost=8 Card=20012 Bytes=4842904)
5 4 NESTED LOOPS (OUTER) (Cost=8 Card=245 Bytes=56105)
6 5 NESTED LOOPS (OUTER) (Cost=5 Card=3 Bytes=609)
7 6 NESTED LOOPS (Cost=5 Card=3 Bytes=492)
8 7 NESTED LOOPS (Cost=5 Card=4 Bytes=292)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'USER$' (
Cost=2 Card=1 Bytes=30)

10 9 INDEX (UNIQUE SCAN) OF 'I_USER1' (UNIQUE
) (Cost=1 Card=82)

11 8 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (C
ost=3 Card=409 Bytes=17587)

12 11 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)
(Cost=2 Card=409)

13 7 TABLE ACCESS (CLUSTER) OF 'TAB$'
14 13 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQU
E)

15 6 TABLE ACCESS (CLUSTER) OF 'SEG$'
16 15 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON
-UNIQUE)

17 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (Cost=1
Card=8168 Bytes=212368)

18 17 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
19 4 TABLE ACCESS (CLUSTER) OF 'TS$'
20 19 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
21 3 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
22 2 TABLE ACCESS (CLUSTER) OF 'USER$'
23 22 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)

===========================================================

(a)Could you explain the performance gain while using the First_rows hint?
(b)Could you explain in which case we can use First_rows hint?

Thanks in advance,



Tom Kyte
March 31, 2008 - 7:30 am UTC

you use first_rows to tell the optimizer to optimize for initial response time (to get the first row as fast as possible, but take your time to get the last row)

by default, Oracle uses all_rows optimization when using the cbo - that optimizes for total throughput - get me the last row as fast as possible.


I cannot explain a performance "gain" for a query that would actually appear to take much longer - it would get the FIRST ROWS fastest (because it was biased towards indexes in this case) - but it would likely take much longer to get the ENTIRE result set - using those indexes.

first_rows - a good hint ?

A reader, June 05, 2009 - 5:10 am UTC

I'm using the default optimizer_mode=ALL_ROWS and finding that there appears to be differences between how it operates under 11.1.0.6 and 11.1.0.7.

A standalone SQL runs under 0.1 seconds in 11.1.0.7, but when embedded into a PL/SQL stored procedure, it performs very poorly (9+ seconds). I then altered the session optimizer_mode to be FIRST_ROWS inside the stored procedure and the performance then becomes comparable to the standalone version.

On 11.1.0.6, the standalone SQL and embedded version performs exactly the same without having to explicitly specify the optimizer_mode.

All statistics were gather on all versions.

Leads me to 2 questions;

a) Does standalone SQL and PL/SQL use different optimization goals?
b) Is there something else I should look at to see why the upgrade from 11.1.0.6 to 11.1.0.7 would mean having to change our stored procedure to explicitly set the optimizer_mode ?
Tom Kyte
June 08, 2009 - 11:48 am UTC

no example, no guesses from me (other than the sql is not the same - you didn't use binds "normally" and you did in plsql and bind peeking came into play - until adaptive cursor sharing comes into play).


plsql by default uses all rows optimization, as does "normal" sql.


csv and delimited hints in sqls

Rajeshwaran, June 21, 2013 - 4:48 pm UTC

Tom:

if we run this query in sql developer "select /*csv*/ * from emp" we get the output in CSV format but not in sql*plus or JDBC connection from apps. Is there any idea to have oracle implement this CSV hint feature in database engine?

http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/


Tom Kyte
July 01, 2013 - 5:11 pm UTC

No, there is not, not for CSV. formatting of output data is a client thing typically. (sqlplus does have some HTML output capabilities however)

getting CSV output is fairly simple in SQL however - in a real program where you write the query once and put it into production it is not onerous to ask that you code:


select '"' || replace(c1,'"','""') || '","' || replace .....
  from t

Not getting css support

Sivaprasad, January 30, 2015 - 5:57 am UTC

http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj29840.html

On this url the page doesn't getting css support

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library