| Tuning the query - Not using the index [message #329128] |
Tue, 24 June 2008 03:47  |
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 #329164 is a reply to message #329130] |
Tue, 24 June 2008 06:22  |
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 #329201 is a reply to message #329128] |
Tue, 24 June 2008 08:40 |
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 #329278 is a reply to message #329207] |
Tue, 24 June 2008 22:16 |
rleishman
Messages: 3728
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 #329310 is a reply to message #329298] |
Wed, 25 June 2008 00:53 |
rleishman
Messages: 3728
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 |
 |
Michel Cadot
Messages: 68665
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 |
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 #329701 is a reply to message #329128] |
Thu, 26 June 2008 05:08 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
