Hi Friends,
I am facing a wierd problem.
We are running stats collection by using below command:
BEGIN dbms_stats.gather_table_stats(ownname => 'SA', tabname => 'MTM_SITE_PART76_X_NEED3', degree => 10, cascade => TRUE,ESTIMATE_PERCENT => 5 , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1') ; END;
It is calling below sql , which you can see is doing index full scan on K15_76_2000_3 . Problem is it is running for hours without finishing.
SQL_ID c9j2kcmpd6jtt, child number 0
-------------------------------------
select /*+ parallel(t,10) parallel_index(t,10) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)
no_monitoring no_substrb_pad */count(*), count(distinct
"X_SITE_PART2X_NEED"), sum(sys_op_opnsize("X_SITE_PART2X_NEED")),
substrb(dump(min("X_SITE_PART2X_NEED"),16,0,32),1,120),
substrb(dump(max("X_SITE_PART2X_NEED"),16,0,32),1,120), count(distinct
"X_NEED2X_SITE_PART"), sum(sys_op_opnsize("X_NEED2X_SITE_PART")),
substrb(dump(min("X_NEED2X_SITE_PART"),16,0,32),1,120),
substrb(dump(max("X_NEED2X_SITE_PART"),16,0,32),1,120) from
"SA"."MTM_SITE_PART76_X_NEED3" sample ( 5.0000000000) t
Plan hash value: 2842455981
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 52251 (100)| |
| 1 | SORT GROUP BY | | 1 | 14 | | |
|* 2 | INDEX FULL SCAN| K15_76_2000_3 | 44M| 595M| 52251 (1)| 00:10:28 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ORA_HASH(ROWID,0,1065600607,'SYS_SAMPLE',0)<214748365)
Below is the output from
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : SA (20065:9591)
SQL ID : c9j2kcmpd6jtt
SQL Execution ID : 16777216
Execution Started : 08/30/2016 05:05:46
First Refresh Time : 08/30/2016 05:05:52
Last Refresh Time : 08/30/2016 13:40:10
Duration : 30865s
Module/Action : SQL*Plus/-
Service : p2clf1d1
Program : sqlplus@ylpi043 (TNS V1-V3)
Global Stats
=================================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
=================================================================================================
| 31136 | 1246 | 29506 | 0.00 | 384 | 9M | 4M | 31GB | 4464 | 932MB |
=================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2842455981)
================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) |
================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | | | | |
| -> 1 | SORT GROUP BY | | 1 | | 30861 | +6 | 1 | 0 | | | 4464 | 932MB | 42M | 978M | 0.99 | Cpu (302) |
| -> 2 | INDEX FULL SCAN | K15_76_2000_3 | 45M | 52251 | 30866 | +1 | 1 | 38M | 4M | 31GB | | | | | 99.00 | gc cr grant 2-way (254) |
| | | | | | | | | | | | | | | | | gc cr grant congested (2) |
| | | | | | | | | | | | | | | | | gc cr request (3) |
| | | | | | | | | | | | | | | | | gc current grant 2-way (136) |
| | | | | | | | | | | | | | | | | gc current grant congested (1) |
| | | | | | | | | | | | | | | | | Cpu (656) |
| | | | | | | | | | | | | | | | | db file sequential read (28969) |
================================================================================================================================================================================================
My question is:
1. Why it is picking up "INDEX FULL SCAN" instead of "INDEX FAST FULL SCAN" . It is happening in all the DBs that we have. Problem is it is running in production for past 8 hours?
2. How to change the plan to "INDEX FAST FULL SCAN" without resorting to outline/baseline/profile? Are we hitting any bug or limitation which is resulting in oracle taking "INDEX FAST FULL SCAN" for this index.
Details:
1. DB version: 11.2.0.4.0
2. index size is 39GB and table size is 35 GB. Table has only 2 columns and index is on both columns( there is a long history behind but i will skip that for now).
3. This 'INDEX FULL SCAN' problem is happening for about 80 indexes. there are about 1200 other indexes for which 'INDEX FAST FULL SCAN' is being used.
One example of such a 'good query'
SQL_ID ddbmtduxu4v7v, child number 0
-------------------------------------
select /*+ parallel_index(t, "TABLE_X_NBA_OFFER_4IX",4) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)
no_monitoring no_substrb_pad no_expand
index_ffs(t,"TABLE_X_NBA_OFFER_4IX") */ count(*) as nrw,count(distinct
sys_op_lbid(3298701,'L',t.rowid)) as nlb,count(distinct
"X_NBA_OFFR2X_CMP_SPLT") as ndk,sys_op_countchg(substrb(t.rowid,1,15),1)
as clf from "SA"."TABLE_X_NBA_OFFER" t where "X_NBA_OFFR2X_CMP_SPLT"
is not null
Plan hash value: 3936289613
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 144 (100)| | | | |
| 1 | SORT GROUP BY | | 1 | 19 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 19 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT GROUP BY | | 1 | 19 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 1 | 19 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 1 | 19 | | | Q1,00 | P->P | HASH |
| 7 | SORT GROUP BY | | 1 | 19 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 156K| 2899K| 144 (1)| 00:00:02 | Q1,00 | PCWC | |
|* 9 | INDEX FAST FULL SCAN| TABLE_X_NBA_OFFER_4IX | 156K| 2899K| 144 (1)| 00:00:02 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access(:Z>=:Z AND :Z<=:Z)
filter("X_NBA_OFFR2X_CMP_SPLT" IS NOT NULL)
Regds,
Sachin