Home » RDBMS Server » Performance Tuning » Tuning the query - Not using the index ( 9.2.0.7.0)
Tuning the query - Not using the index [message #329128] Tue, 24 June 2008 03:47 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

SQL> explain plan set statement_id = 'RBN' for
  2  select * from fem_checking where as_of_date = '30-May-2008';

Explained.

SQL>  select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
| Id  | Operation            |  Name         | Rows  | Bytes | Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |   379K|   204M|  6287 |       |       |
|*  1 |  TABLE ACCESS FULL   | FEM_CHECKING  |   379K|   204M|  6287 |    19 |    19 |
--------------------------------------------------------------------------------------

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


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
   1 - filter("FEM_CHECKING"."AS_OF_DATE"=TO_DATE('2008-05-30 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))

Note: cpu costing is off

15 rows selected.

SQL> explain plan set statement_id = 'RBN' for
  2  select /*+ index(FEM_CHECKING, FEM_CD_AOD) */ * from fem_checking where as_of_date = '30-May-2008' ;

Explained.

SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name         | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |   379K|   204M| 35587 |       |       |
|   1 |  TABLE ACCESS BY LOCAL INDEX ROWID| FEM_CHECKING  |   379K|   204M| 35587 |    19 |    19 |
|*  2 |   INDEX RANGE SCAN                | FEM_CD_AOD    |   379K|       |  1010 |    19 |    19 |
---------------------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------

   2 - access("FEM_CHECKING"."AS_OF_DATE"=TO_DATE('2008-05-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

Note: cpu costing is off

15 rows selected.

SQL>


In the above case, There is a index on as_of_date and also I've Gathered Statistics.

By default the query is not picking the index and the query execution is very slow. The specific partition '19' has 1139072 records.

But when I have given the hint to use the index it is executing faster.

Why could be the reasn it is going to FULL tablescan without the hint.

RBN.
Re: Tuning the query - Not using the index [message #329130 is a reply to message #329128] Tue, 24 June 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Activate a 10053 trace then you will know why Oracle is taking this plan.

Note that '30-May-2008' is NOT a date but a string, use TO_DATE.

Regards
Michel
Re: Tuning the query - Not using the index [message #329164 is a reply to message #329130] Tue, 24 June 2008 06:22 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Following is my 10053 trace.

I understand that, the cost is more to use the index.
But, while using the hint query execution is faster.

SINGLE TABLE ACCESS PATH
Column: AS_OF_DATE  Col#: 6      Part#: 19     Table: FEM_CHECKING   Alias: FEM_CHECKING
    NDV: 2         NULLS: 0         DENS: 5.0000e-01 LO:  2454622  HI: 2454636
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: AS_OF_DATE  Col#: 6      Table: FEM_CHECKING   Alias: FEM_CHECKING
    NDV: 2         NULLS: 0         DENS: 5.0000e-01 LO:  2454622  HI: 2454636
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: FEM_CHECKING     ORIG CDN: 753894  ROUNDED CDN: 376947  CMPTD CDN: 376947
  Access path: tsc  Resc:  4165  Resp:  4165
  Access path: index (equal)
      Index: FEM_CD_AOD
  TABLE: FEM_CHECKING
      RSC_CPU: 0   RSC_IO: 34170
  IX_SEL:  0.0000e+00  TB_SEL:  5.0000e-01
  Skip scan: ss-sel 0  andv 4696
    ss cost 4696
    index io scan cost 2744
  Access path: index (scan)
      Index: FEM_CHECKING_RM
  TABLE: FEM_CHECKING
      RSC_CPU: 0   RSC_IO: 42143
  IX_SEL:  5.0000e-01  TB_SEL:  5.0000e-01
  Skip scan: ss-sel 0  andv 376947
    ss cost 376947
    index io scan cost 2480
  Access path: index (scan)
      Index: FEM_CHECKING_T2
  TABLE: FEM_CHECKING
      RSC_CPU: 0   RSC_IO: 74742
  IX_SEL:  5.0000e-01  TB_SEL:  5.0000e-01
  BEST_CST: 4165.00  PATH: 2  Degree:  1
***************************************
GENERAL PLANS
***********************
Join order[1]:  FEM_CHECKING[FEM_CHECKING]#0
Best so far: TABLE#: 0  CST:       4165  CDN:     376947  BYTES:  213352002
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 4165  CDN: 376947  RSC: 4165  RSP: 4165  BYTES: 213352002
  IO-RSC: 4165  IO-RSP: 4165  CPU-RSC: 0  CPU-RSP: 0
QUERY
select  * from fem_checking where as_of_date = '18-Jun-2008'
*** 2008-06-24 14:58:43.324
QUERY
alter session set events '10046 trace name context off'


RBN
Re: Tuning the query - Not using the index [message #329187 is a reply to message #329164] Tue, 24 June 2008 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either don't use histogram on the column or use a histogram with more than 2 values.
Currently Oracle thinks there are only 2 values with 50% of rows for each and so it estimates than a full scan is better.

Regards
Michel
Re: Tuning the query - Not using the index [message #329201 is a reply to message #329128] Tue, 24 June 2008 08:40 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I did not understand
Quote:
Either don't use histogram on the column or use a histogram with more than 2 values.


Is it method_opt during gather stats? could you please elaborate .

As of now I have set optimzer_index_cost_adj=15, and its working fine.

Thanks,
RBN
Re: Tuning the query - Not using the index [message #329207 is a reply to message #329201] Tue, 24 June 2008 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it method_opt during gather stats?

Yes.

Regards
Michel
Re: Tuning the query - Not using the index [message #329278 is a reply to message #329207] Tue, 24 June 2008 22:16 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What do you mean the indexed one is faster? Faster to return the first row? Faster to return every row? Faster for the second and subsequent runs after the index has be loaded into the buffer-cache?

Oracle chooses the FTS because it thinks that BOTH the full partition scan AND the index range scan will process the same number of rows: 379000. FTS can read rows faster than an index range scan, so Oracle rightly figures that the index would only be faster if it retieved A LOT fewer rows.

You have told us how many rows there are in the partition.
- How many of those are for 2008-05-30?
- Are you timing the entire query through to the return of the last row?

Ross Leishman
Re: Tuning the query - Not using the index [message #329298 is a reply to message #329128] Tue, 24 June 2008 23:51 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Ross,

There are total 1139072 in the partition and there are 385387 rows pertaining to 2008-05-30.

The comparison on the entire selection for
"as_of_date = 2008-05-30" (Fetching all rows).

Regards,
RBN
Re: Tuning the query - Not using the index [message #329310 is a reply to message #329298] Wed, 25 June 2008 00:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If indexed access is faster, then there is something drastically wrong with your database, or you are just measuring the time until the first page of rows appears.

Try comparing:
select * 
from fem_checking 
where as_of_date = '30-May-2008'
and rownum > 1;

both with and without the index.

This will access every row from disk but will not display the results (the display should be equivalent for the two options).

Since you are picking up about a third of the table, I would expect the full scan to be several times faster. Right now, most of the table and index will be in the buffer-cache, so it will not be a "real" difference. For a worst-case, purge your buffer cache and any hardware disk cache.

Ross Leishman
Re: Tuning the query - Not using the index [message #329311 is a reply to message #329298] Wed, 25 June 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There are total 1139072 in the partition and there are 385387 rows pertaining to 2008-05-30.

You know this but this is NOT what Oracle knows as your statistics are not correct.
Oracle knows:
Column: AS_OF_DATE  Col#: 6      Part#: 19     Table: FEM_CHECKING   Alias: FEM_CHECKING
    NDV: 2         NULLS: 0         DENS: 5.0000e-01 LO:  2454622  HI: 2454636

Number of distinct values : 2
Selectivity of each one 50% (5.0000e-01)
So for it, AS_OF_DATE=value returns half the partition.
Also:
TABLE: FEM_CHECKING     ORIG CDN: 753894  

Cardinality: 753894 not 1139072.

You have to keep your statistics up to date if you want correct execution plan.

Regards
Michel

[Updated on: Wed, 25 June 2008 00:55]

Report message to a moderator

Re: Tuning the query - Not using the index [message #329322 is a reply to message #329311] Wed, 25 June 2008 01:32 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

I'm gathering stats using the following..

DBMS_STATS.GATHER_TABLE_STATS(ownname=>'BOIDW',tabname=>'FEM_CHECKING',
			      partname=>'MAY_2008',granularity=>'PARTITION',
			      cascade=>TRUE, degree=>16);


Following is the output says the stats are correct(which matches with my total number of records)..
SQL> select distinct as_of_date from fem_checking partition(MAY_2008);

AS_OF_DAT
---------
21-MAY-08
30-MAY-08
07-MAY-08

SQL> select num_rows,blocks,sample_size,global_stats 
  2  from dba_tab_partitions where table_name = 'FEM_CHECKING' and partition_name = 'MAY_2008';

 NUM_ROWS    BLOCKS SAMPLE_SIZE GLO
--------- --------- ----------- ---
  1139072    103143     1139072 YES

SQL> 


Then in explain plan
1. What could be the reason NDV showing 2(actually it is 3)?
2. "ORIG CDN: 753894" what is this mean? Is it the total records in the partition or cardinality of column as_of_date?

Thanks,
RBN
Re: Tuning the query - Not using the index [message #329325 is a reply to message #329322] Wed, 25 June 2008 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use:
method_opt => 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS as_of_date SIZE 254'

Remove degree, you don't need it for a 1M rows partition, let it to the default as well as for granularity.

Regards
Michel
Re: Tuning the query - Not using the index [message #329701 is a reply to message #329128] Thu, 26 June 2008 05:08 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

In the following trace which parameter/value drives to use FTS.

SINGLE TABLE ACCESS PATH
Column: AS_OF_DATE  Col#: 6      Part#: 18     Table: FEM_CHECKING   Alias: FEM_CHECKING
    NDV: 3         NULLS: 0         DENS: 4.3895e-07
    FREQUENCY HISTOGRAM: #BKT: 1139072 #VAL: 3
Column: AS_OF_DATE  Col#: 6      Table: FEM_CHECKING   Alias: FEM_CHECKING
    NDV: 3         NULLS: 0         DENS: 4.3895e-07
    FREQUENCY HISTOGRAM: #BKT: 1139072 #VAL: 3
  TABLE: FEM_CHECKING     ORIG CDN: 1139072  ROUNDED CDN: 385386  CMPTD CDN: 385386
  Access path: tsc  Resc:  6287  Resp:  6287
  Access path: index (equal)
      Index: FEM_CD_AOD
  TABLE: FEM_CHECKING
      RSC_CPU: 0   RSC_IO: 36121
  IX_SEL:  0.0000e+00  TB_SEL:  3.3833e-01
  Skip scan: ss-sel 0  andv 4775
    ss cost 4775
    index io scan cost 2804
  Access path: index (scan)
      Index: FEM_CHECKING_RM
  TABLE: FEM_CHECKING
      RSC_CPU: 0   RSC_IO: 42794
  IX_SEL:  3.3833e-01  TB_SEL:  3.3833e-01
  Skip scan: ss-sel 0  andv 385386
    ss cost 385386
    index io scan cost 2534
  Access path: index (scan)
      Index: FEM_CHECKING_T2
  TABLE: FEM_CHECKING
      RSC_CPU: 0   RSC_IO: 74888
  IX_SEL:  3.3833e-01  TB_SEL:  3.3833e-01
  BEST_CST: 6287.00  PATH: 2  Degree:  1
***************************************
GENERAL PLANS
***********************
Join order[1]:  FEM_CHECKING[FEM_CHECKING]#0
Best so far: TABLE#: 0  CST:       6287  CDN:     385386  BYTES:  218128476
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 6287  CDN: 385386  RSC: 6287  RSP: 6287  BYTES: 218128476
  IO-RSC: 6287  IO-RSP: 6287  CPU-RSC: 0  CPU-RSP: 0
QUERY
select * from fem_checking where as_of_date = '30-May-2008'
*** 2008-06-26 15:40:03.411
QUERY
alter session set events '10053 trace name context off'


Regards,
RBN
Previous Topic: High Frequency of Redo Log Files
Next Topic: Unable to Understand Explain Plan
Goto Forum:
  


Current Time: Sun Dec 04 22:35:52 CST 2016

Total time taken to generate the page: 0.09919 seconds