Hi We are moved to version 19c from 11.2 recently. And just after moving to 19c we are seeing the stats gather processes are running quite longer as compared to previous 11.2, say it was ~30 minutes vs 2.5hrs now. I am not able to get all the sql from history as many of them are recursive sqls but few of them I found looks like below. And these are for big partition tables which are gathered with INCREMENTAL true.
But not able to see these exact sql before in 11.2 executions in AWR so it means these are new (may be with new hints), so struggling to compare what was the exact recursive sql which was in 11.2 now converted to new one as below and running longer. Has anyone run into this situation? Is there any different treatment for these preferences in 19c as compared to 11.2?
We had below table level preference set as in the 11.2 version database and is kept like that for 19C too, no changes done:-
| PREFERENCE_NAME | PREFERENCE_VALUE |
| CASCADE | TRUE |
| DEGREE | 12 |
| GRANULARITY | ALL |
| INCREMENTAL | TRUE |
| METHOD_OPT | FOR ALL COLUMNS SIZE REPEAT |
select /*+ opt_param('_optimizer_use_auto_indexes' 'on') parallel_index(t, "TAB1_IX1",12) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('optimizer_inmemory_aware' 'false') no_substrb_pad no_expand index_ffs(t,"TAB1_IX1") */ count(*) as nrw,approx_count_distinct(sys_op_lbid(1280571,'L',t.rowid)) as nlb,null as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from "USER1"."TAB1" t where
"C1" is not null or "C2" is not null or "C3" is not null or "C4" is not null
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
SQL ID : f1wcv5471mhkm
SQL Execution ID : 16777216
Execution Started : 08/11/2020 14:51:22
First Refresh Time : 08/11/2020 14:51:26
Last Refresh Time : 08/11/2020 15:28:20
Duration : 2219s
PLSQL Entry Ids (Object/Subprogram) : 1324638,1
PLSQL Current Ids (Object/Subprogram) : 7998,516
Global Stats
=======================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Buffer | Read | Read | Offload | Offload | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Elig Bytes | Returned Bytes | Offload |
=======================================================================================================================================
| 26737 | 13365 | 13371 | 0.03 | 0.00 | 0.30 | 328M | 3M | 2TB | 2TB | 2TB | 13.34% |
=======================================================================================================================================
Parallel Execution Details (DOP=12 , Servers Allocated=12)
================================================================================================================================================================================================================
| Name | Type | Server# | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Buffer | Read | Read | Offload | Offload | Cell | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Elig Bytes | Returned Bytes | Offload | (sample #) |
================================================================================================================================================================================================================
| PX Coordinator | QC | | 11 | 7.67 | 2.67 | 0.03 | | 0.30 | 30706 | 4035 | 33MB | . | . | NaN% | cell single block physical read (3) |
| p000 | Set 1 | 1 | 2228 | 1103 | 1124 | | | | 27M | 287K | 189GB | 189GB | 163GB | 13.59% | cell smart index scan (1126) |
| p001 | Set 1 | 2 | 2226 | 1119 | 1106 | | | | 27M | 286K | 188GB | 188GB | 163GB | 13.31% | cell single block physical read (1) |
| | | | | | | | | | | | | | | | cell smart index scan (1123) |
| p002 | Set 1 | 3 | 2228 | 1114 | 1113 | | | | 27M | 289K | 189GB | 189GB | 164GB | 13.25% | cell single block physical read (1) |
| | | | | | | | | | | | | | | | cell smart index scan (1121) |
| p003 | Set 1 | 4 | 2227 | 1094 | 1133 | | | | 27M | 284K | 187GB | 187GB | 162GB | 13.29% | cell smart index scan (1064) |
| p004 | Set 1 | 5 | 2228 | 1119 | 1108 | | | | 27M | 290K | 190GB | 190GB | 164GB | 13.30% | cell smart index scan (1126) |
| p005 | Set 1 | 6 | 2226 | 1109 | 1117 | | | | 27M | 285K | 188GB | 188GB | 163GB | 13.24% | cell smart index scan (1146) |
| p006 | Set 1 | 7 | 2228 | 1113 | 1115 | | | | 28M | 290K | 190GB | 190GB | 165GB | 13.18% | cell smart index scan (1102) |
| p007 | Set 1 | 8 | 2226 | 1116 | 1110 | | 0.00 | 0.00 | 27M | 289K | 189GB | 189GB | 164GB | 13.28% | cell single block physical read (1) |
| | | | | | | | | | | | | | | | cell smart index scan (1084) |
| p008 | Set 1 | 9 | 2230 | 1127 | 1103 | | | | 27M | 284K | 187GB | 187GB | 162GB | 13.46% | cell single block physical read (1) |
| | | | | | | | | | | | | | | | cell smart index scan (1111) |
| p009 | Set 1 | 10 | 2226 | 1107 | 1119 | | | 0.00 | 27M | 286K | 188GB | 188GB | 163GB | 13.42% | cell smart index scan (1112) |
| p00a | Set 1 | 11 | 2228 | 1134 | 1094 | | | | 27M | 285K | 187GB | 187GB | 162GB | 13.42% | cell single block physical read (1) |
| | | | | | | | | | | | | | | | cell smart index scan (1154) |
| p00b | Set 1 | 12 | 2228 | 1102 | 1126 | | | 0.00 | 27M | 287K | 189GB | 189GB | 164GB | 13.36% | cell smart index scan (1122) |
================================================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3008371370)
==================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) |
==================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | . | | |
| 1 | SORT AGGREGATE APPROX | | 1 | | | | 1 | | | | . | | |
| 2 | PX COORDINATOR | | | | 5 | +1 | 13 | 0 | 4035 | 33MB | . | 0.02 | Cpu (2) |
| | | | | | | | | | | | | | cell single block physical read (3) |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | | 12 | | | | . | | |
| -> 4 | SORT AGGREGATE APPROX | | 1 | | 2217 | +6 | 12 | 0 | | | . | 37.40 | Cpu (9927) |
| -> 5 | PX BLOCK ITERATOR | | 92G | 129M | 2217 | +6 | 12 | 49G | | | . | 0.00 | Cpu (1) |
| -> 6 | INDEX STORAGE FAST FULL SCAN | TAB1_IX1 | 92G | 129M | 2217 | +6 | 449K | 49G | 3M | 2TB | 34MB | 62.58 | Cpu (3217) |
| | | | | | | | | | | | | | cell single block physical read (5) |
| | | | | | | | | | | | | | cell smart index scan (13391) |
==================================================================================================================================================================================================