Hi We are using version 12.1.0.2 of Oracle Exadata. We have a table(SFE) from which we delete/update significant(~50million out of ~70million daily) amount of data in daily basis as per the current design(and we are in process of changing the design). But we want to understand the amount of current impact to production queries due to this. We tried testing the queries in one of the test environment(with similar configuration as that of production but data volume is bit lesser as compared to production and all indexes were rebuild newly) with exact same plan. I see we are seeing similar performance until we had queries performing FTS on the table and when we encounter a query which was using an index(SFE_IX5) on a date column to fetch data from this table(SFE) there was significant difference between the db time in both databases. It is running for ~25minutes on prod as compared to ~2minuts on test environment with exact same plan. Below is the sql monitor from both the environment for same query. Want to understand from the figures ,if its really the current shape of index in production causing this and a coalesce/rebuild index will help in short term?
We are seeing the significant amount of time is getting spent on step-14,15 of the execution plan in both the cases, but in production version i do see major time is spent on "cell single block physical read" in that step. I checked during the whole execution it was not on UNDO, which means it was reading the permanent data/index blocks. That step using index SFE_IX5 which is on a date column. Comparing the table and index stats between the two environments i see the data volume in the table in production is ~1.6billion and on test environment is ~930million. The index is having blevel of 4 in production as compared to 3 in test environment. The rows per leafblock for that index is ~65 in production vs ~292 in test environment. The size of index is ~200gb in production as compared to ~25gb on test database.
I understand the data volume is not exactly same in both the database but is it still enough and giving us fair comparison result? Want to understand the experts view, if below sqlmonitor is justifying that the index access steps (14,15) is actually spending significant time on "cell single block physical read" , so is it sufficient to justify that this extra reads happening because of the current shape/fragmentation of the index and also the table blocks? Rebuild/coalesce index should help us to make things better at least in short term basis?
Test Environment Table/Index statistics:-
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE
SFE 930123300 71814213 158 9301233
TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
SFE SFE_IX5 3 3262700 174280900 955954100 9559541
Production Environment Table/Index statistics:-
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE
SFE 1604905000 114857360 157 16049050
TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
SFE SFE_IX5 4 24206500 136601600 1576363600 15763636
************** sql monitor On TEST environment******************
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL Execution ID : 33554432
Execution Started : 12/14/2018 03:59:21
First Refresh Time : 12/14/2018 03:59:25
Last Refresh Time : 12/14/2018 04:00:42
Duration : 81s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 74
Global Stats
======================================================================================
| Elapsed | Cpu | IO | Cluster | Other | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
======================================================================================
| 77 | 75 | 1.50 | 0.00 | 0.62 | 74 | 32M | 3243 | 50MB |
======================================================================================
SQL Plan Monitoring Details (Plan Hash Value=538631242)
================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 78 | +4 | 1 | 360K | | | | | |
| 1 | NESTED LOOPS | | | | 78 | +4 | 1 | 360K | | | | | |
| 2 | NESTED LOOPS | | 1 | 834K | 78 | +4 | 1 | 371K | | | | | |
| 3 | HASH JOIN | | 1 | 834K | 78 | +4 | 1 | 371K | | | 590K | | |
| 4 | TABLE ACCESS BY INDEX ROWID | SPSV | 1 | 3 | 1 | +4 | 1 | 2 | | | | | |
| 5 | INDEX RANGE SCAN | SPSV_UN1 | 1 | 2 | 1 | +4 | 1 | 2 | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 1 | 1 | +4 | 1 | 1 | | | | | |
| 7 | INDEX UNIQUE SCAN | SSC_ID | 1 | | 1 | +4 | 1 | 1 | | | | | |
| 8 | HASH JOIN | | 131K | 834K | 78 | +4 | 1 | 459K | | | 2M | | |
| 9 | INDEX STORAGE FAST FULL SCAN | SEPC_IX2 | 3398 | 6 | 1 | +4 | 1 | 3398 | 3 | 40960 | | | |
| 10 | HASH JOIN | | 131K | 834K | 78 | +4 | 1 | 459K | | | 2M | | |
| 11 | TABLE ACCESS STORAGE FULL | SSCD | 142 | 54 | 1 | +4 | 1 | 142 | | | | | |
| 12 | HASH JOIN | | 1M | 834K | 78 | +4 | 1 | 4M | | | 10M | | |
| 13 | TABLE ACCESS STORAGE FULL | STDV | 202K | 19607 | 4 | +1 | 1 | 195K | 3240 | 50MB | | 3.75 | Cpu (1) |
| | | | | | | | | | | | | | cell single block physical read (2) |
| 14 | TABLE ACCESS BY GLOBAL INDEX ROWID | SFE | 2M | 800K | 78 | +4 | 1 | 52M | | | | 43.75 | Cpu (35) |
| 15 | INDEX RANGE SCAN | SFE_IX5 | 4M | 14757 | 78 | +4 | 1 | 52M | | | | 12.50 | Cpu (10) |
| 16 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 1 | 1 | +4 | 1 | 1 | | | | | |
| 17 | INDEX UNIQUE SCAN | SSC_ID | 1 | | 1 | +4 | 1 | 1 | | | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 1 | 1 | +4 | 1 | 1 | | | | | |
| 19 | INDEX UNIQUE SCAN | SSC_ID | 1 | | 1 | +4 | 1 | 1 | | | | | |
| 20 | PARTITION RANGE ALL | | 1 | 69 | 78 | +4 | 371K | 371K | | | | 3.75 | Cpu (3) |
| 21 | INDEX RANGE SCAN | SFS_PK | 1 | 69 | 78 | +4 | 9M | 371K | | | | 35.00 | Cpu (28) |
| 22 | TABLE ACCESS BY LOCAL INDEX ROWID | SFS | 1 | 70 | 78 | +4 | 371K | 360K | | | | 1.25 | Cpu (1) |
================================================================================================================================================================================================================
************* On Production**************
SQL Monitoring Report
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL Execution ID : 33554432
Execution Started : 12/14/2018 04:00:14
First Refresh Time : 12/14/2018 04:00:20
Last Refresh Time : 12/14/2018 04:26:14
Duration : 1560s
Fetch Calls : 793
Global Stats
=========================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=========================================================================================
| 1729 | 365 | 1278 | 0.05 | 86 | 793 | 36M | 3M | 22GB |
=========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=538631242)
=====================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
=====================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1555 | +6 | 1 | 391K | | | | 0.06 | Cpu (1) |
| 1 | NESTED LOOPS | | | | 1555 | +6 | 1 | 391K | | | | | |
| 2 | NESTED LOOPS | | 1 | 735K | 1555 | +6 | 1 | 403K | | | | | |
| 3 | HASH JOIN | | 1 | 735K | 1555 | +6 | 1 | 403K | | | 557K | 0.06 | Cpu (1) |
| 4 | TABLE ACCESS BY INDEX ROWID | SPSV | 1 | 3 | 1 | +6 | 1 | 2 | | | | | |
| 5 | INDEX RANGE SCAN | SPSV_UN1 | 1 | 2 | 1 | +6 | 1 | 2 | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 1 | 1 | +6 | 1 | 1 | | | | | |
| 7 | INDEX UNIQUE SCAN | SSC_ID | 1 | | 1 | +6 | 1 | 1 | | | | | |
| 8 | HASH JOIN | | 31561 | 735K | 1555 | +6 | 1 | 504K | | | 2M | 0.06 | Cpu (1) |
| 9 | INDEX STORAGE FAST FULL SCAN | SEPC_IX2 | 3459 | 6 | 1 | +6 | 1 | 3459 | 3 | 112KB | | | |
| 10 | HASH JOIN | | 31561 | 735K | 1555 | +6 | 1 | 504K | | | 2M | 0.06 | Cpu (1) |
| 11 | TABLE ACCESS STORAGE FULL | SSCD | 147 | 54 | 1 | +6 | 1 | 147 | | | | | |
| 12 | HASH JOIN | | 82876 | 735K | 1555 | +6 | 1 | 5M | | | 13M | 0.45 | Cpu (7) |
| 13 | TABLE ACCESS STORAGE FULL | STDV | 218K | 22383 | 6 | +1 | 1 | 218K | | | | 0.06 | Cpu (1) |
| 14 | TABLE ACCESS BY GLOBAL INDEX ROWID | SFE | 82876 | 712K | 1558 | +3 | 1 | 81M | 2M | 17GB | | 71.48 | gc buffer busy acquire (18) |
| | | | | | | | | | | | | | gc cr block 2-way (8) |
| | | | | | | | | | | | | | gc cr block busy (17) |
| | | | | | | | | | | | | | gc cr disk read (1) |
| | | | | | | | | | | | | | gc cr grant 2-way (21) |
| | | | | | | | | | | | | | gc current block 2-way (4) |
| | | | | | | | | | | | | | Cpu (118) |
| | | | | | | | | | | | | | cell single block physical read (912) |
| | | | | | | | | | | | | | cell single block read request (2) |
| | | | | | | | | | | | | | read by other session (12) |
| 15 | INDEX RANGE SCAN | SFE_IX5 | 7M | 110K | 1559 | +2 | 1 | 82M | 714K | 5GB | | 25.11 | gc buffer busy acquire (1) |
| | | | | | | | | | | | | | gc cr grant 2-way (14) |
| | | | | | | | | | | | | | gc current grant busy (2) |
| | | | | | | | | | | | | | gc remaster (4) |
| | | | | | | | | | | | | | Cpu (30) |
| | | | | | | | | | | | | | gcs drm freeze in enter server mode (2) |
| | | | | | | | | | | | | | cell single block physical read (336) |
| | | | | | | | | | | | | | read by other session (2) |
| 16 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 1 | 1 | +6 | 1 | 1 | | | | | |
| 17 | INDEX UNIQUE SCAN | SSC_ID | 1 | | 1 | +6 | 1 | 1 | | | | | |
| 18 | TABLE ACCESS BY INDEX ROWID | SSC | 1 | 1 | 1 | +6 | 1 | 1 | | | | | |
| 19 | INDEX UNIQUE SCAN | SSC_ID | 1 | | 1 | +6 | 1 | 1 | | | | | |
| 20 | PARTITION RANGE ALL | | 1 | 69 | 1555 | +6 | 410K | 403K | | | | | |
| 21 | INDEX RANGE SCAN | SFS_PK | 1 | 69 | 1555 | +6 | 9M | 403K | 3412 | 27MB | | 2.18 | Cpu (33) |
| | | | | | | | | | | | | | cell single block physical read (1) |
| 22 | TABLE ACCESS BY LOCAL INDEX ROWID | SFS | 1 | 70 | 1555 | +6 | 434K | 391K | 11233 | 88MB | | 0.45 | Cpu (2) |
| | | | | | | | | | | | | | cell list of blocks physical read (1) |
| | | | | | | | | | | | | | cell single block physical read (4) |
=====================================================================================================================================================================================================================