Home » RDBMS Server » Performance Tuning » Wrong plan chosen by optimizer (Oracle Database 11g Release 11.2.0.1.0 - 64bit Production OEL 6)
Wrong plan chosen by optimizer [message #670026] Wed, 30 May 2018 03:47 Go to next message
Andrey_R
Messages: 335
Registered: January 2012
Location: Israel
Senior Member

Hi all,
Why isn't my index being used in spite of the hint and the fact that a good index exists, statistics are collected and all conditions seem to be ok ?


My test case:

SQL> 
SQL> 
SQL> show user
USER is "ANDREY"
SQL> 
SQL> 
SQL> create table TEST
  2  (
  3    col1 VARCHAR2(50),
  4    col2 NUMBER,
  5    uniq TIMESTAMP(6) default sysdate not null
  6  );

Table created.

SQL> 
SQL> create index IF on TEST (COL2);

Index created.

SQL> create index IND1 on TEST (COL1 DESC);

Index created.

SQL> create unique index IND1UNIQ on TEST (COL1);

Index created.

SQL> 
SQL> 
SQL> insert into test select distinct table_name , to_char(sysdate,'ss'), sysdate from dba_tables where table_name not in ( select col1 from test ) ;

4147 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> EXEC  dbms_stats.gather_table_stats ( ownname => user, tabname => 'TEST' , estimate_percent => 100 , method_opt => 'FOR ALL INDEXED COLUMNS SIZE 10', CASCADE=> TRUE);

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> 
SQL> set autot on
SQL> 
SQL> select /*+ index( t IND1UNIQ )*/  col1 from test t;

COL1                                                                            
--------------------------------------------------                              
ICOL$                                                                           
CLU$                                                                            
UGROUP$                                                                         
...
...
..
MRAC_OLAP2_AW_DIMENSIONS_T                                                      
SDO_TXN_IDX_EXP_UPD_RGN                                                         
SDO_TOPO_MAPS                                                                   
SDO_TOPO_TRANSACT_DATA                                                          
SDO_GEOR_SYSDATA_TABLE                                                          
SDO_GEOR_PLUGIN_REGISTRY                                                        
OLS_DIR_BUSINESSES                                                              

4147 rows selected.


Execution Plan
----------------------------------------------------------                      
Plan hash value: 1357081020                                                     
                                                                                
--------------------------------------------------------------------------      
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |      
--------------------------------------------------------------------------      
|   0 | SELECT STATEMENT  |      |   100 |  1900 |     2   (0)| 00:00:01 |      
|   1 |  TABLE ACCESS FULL| TEST |   100 |  1900 |     2   (0)| 00:00:01 |      
--------------------------------------------------------------------------      


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

SQL> 
SQL> 
SQL> spool off


Thanks in advance.

Andrey
Re: Wrong plan chosen by optimizer [message #670027 is a reply to message #670026] Wed, 30 May 2018 04:47 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Probably because it thinks the table is so tiny that any index would be a complete waste of time (rows = 100 where you've got 41 times that).
Stats are wrong because you set estimate_percent to 100.
Description of estimate_ercent from the docs:
Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Seems like setting that to 100 is great way to get bad stats, try the default.
Re: Wrong plan chosen by optimizer [message #670028 is a reply to message #670026] Wed, 30 May 2018 05:09 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
You'll need to declare COL1 as NOT NULL, so that Oracle knows that the index has a key for every row.
Re: Wrong plan chosen by optimizer [message #670029 is a reply to message #670028] Wed, 30 May 2018 06:22 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I should have thought of that
Re: Wrong plan chosen by optimizer [message #670030 is a reply to message #670027] Wed, 30 May 2018 07:45 Go to previous messageGo to next message
Andrey_R
Messages: 335
Registered: January 2012
Location: Israel
Senior Member

cookiemonster wrote on Wed, 30 May 2018 12:47
Probably because it thinks the table is so tiny that any index would be a complete waste of time (rows = 100 where you've got 41 times that).
Stats are wrong because you set estimate_percent to 100.
Description of estimate_ercent from the docs:
Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Seems like setting that to 100 is great way to get bad stats, try the default.
Thank you all for replying to this.

Nevertheless, I am not sure it makes sense to me, because as much as I "respect" Oracle ,
I don't think it should force its "judgement" upon me , when I "ask" it to follow a legitimate hint in a SQL statement.
From what I understood - a hint, if legitimate and possible to perform - should be followed, end of story, no "thinking" allowed.

Isn't this the whole concept of hints ?

Re: Wrong plan chosen by optimizer [message #670031 is a reply to message #670027] Wed, 30 May 2018 07:48 Go to previous messageGo to next message
Andrey_R
Messages: 335
Registered: January 2012
Location: Israel
Senior Member

cookiemonster wrote on Wed, 30 May 2018 12:47
Probably because it thinks the table is so tiny that any index would be a complete waste of time (rows = 100 where you've got 41 times that).
Stats are wrong because you set estimate_percent to 100.
Description of estimate_ercent from the docs:
Percentage of rows to estimate (NULL means compute) The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Seems like setting that to 100 is great way to get bad stats, try the default.

Why is 100 pct and estimate that gets you bad stats, if it is the most "realistic" one ?
And even in this case, statistics ( or even a total absence of them , or bad stats, or horrific horrifying wrong stats... ) should not make CBO decide if it should or shouldn't follow your hint...

Re: Wrong plan chosen by optimizer [message #670032 is a reply to message #670030] Wed, 30 May 2018 08:09 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Quote:
From what I understood - a hint, if legitimate and possible to perform - should be followed, end of story, no "thinking" allowed.
It isn't a legitimate hint. I explained why.
Re: Wrong plan chosen by optimizer [message #670033 is a reply to message #670031] Wed, 30 May 2018 08:11 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I may be misunderstanding what estimate_percent does. It's difficult to find an accurate description of what it does.
That said your stats appear wrong and I would try different values to see if it improves them.

But then, even if the stats were right oracle wouldn't use that index under any circumstances for the reason John mentioned - if the column isn't not null then there's no way oracle can be sure all the values you need are actually in the index so it has to go to the table regardless.
Re: Wrong plan chosen by optimizer [message #670034 is a reply to message #670028] Wed, 30 May 2018 08:40 Go to previous messageGo to next message
Andrey_R
Messages: 335
Registered: January 2012
Location: Israel
Senior Member

John Watson wrote on Wed, 30 May 2018 13:09
You'll need to declare COL1 as NOT NULL, so that Oracle knows that the index has a key for every row.
It can help Oracle decide, when I ask it to decide, think, wonder.

But, a hint is not a request, it's a forceful action, to my understanding.
It's not a matter of judgement, it's a matter of POSSIBLE=YES, NOT_POSSIBLE=NO


I gave it a POSSIBLE set of conditions. So if POSSIBLE=TRUE , it *HAS TO DO WHAT THE INSTRUCTIONS SAY*,
Unless I misunderstand the concept of hints.... do I ?



EDIT: ok. So I ask it to bring the data. And if the data is possibly NULL - then it cannot rely on the index to contain it all.
Now I understood. Thanks for not giving up on me Razz

[Updated on: Wed, 30 May 2018 08:41]

Report message to a moderator

Re: Wrong plan chosen by optimizer [message #670035 is a reply to message #670034] Wed, 30 May 2018 08:43 Go to previous messageGo to next message
Andrey_R
Messages: 335
Registered: January 2012
Location: Israel
Senior Member

Thanks everyone!
Re: Wrong plan chosen by optimizer [message #670036 is a reply to message #670034] Wed, 30 May 2018 08:44 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
If hints were blanket instructions that could never be disobeyed then calling them hints would be a case of woeful misnaming.
Re: Wrong plan chosen by optimizer [message #670037 is a reply to message #670036] Wed, 30 May 2018 09:32 Go to previous message
Andrey_R
Messages: 335
Registered: January 2012
Location: Israel
Senior Member

cookiemonster wrote on Wed, 30 May 2018 16:44
If hints were blanket instructions that could never be disobeyed then calling them hints would be a case of woeful misnaming.

I still tend to think this is the case.
So maybe it should have been called "forceplan" or seomthing like this and not "hint".

setting optimizer_index_cost_adj to a low precentage is sort of hinting, or directing oracle to a certain way.

Use Nested loops, start with this table, use this index - no hint here. These are exact instructions.

The only way for Oracle to "disobey" is if it is NOT POSSIBLE to perform the desired action.
Don't you agree ?
Previous Topic: Wrong plan chosen by optimizer
Next Topic: Used Left Join - Query goes for Full Table scan
Goto Forum:
  


Current Time: Mon Sep 28 08:27:39 CDT 2020