Simple query opting higher cost path
From: Pap <oracle.developer35_at_gmail.com>
Date: Wed, 20 Oct 2021 01:22:55 +0530
Message-ID: <CAEjw_fi4rGC6nE_tBVedVssZpkn=AG7mC41epYH8Gj-Z-PGw8w_at_mail.gmail.com>
Hello Listers, We have encountered slowness with one of the customer databases with a simple SELECT query. And analyzing the plan and outline of the sql it appeared that while we were on version 11.2.0.4, it was using index skip scan and was faster also the cost was lower but post upgrade to 19.9.0.0.0, there are few underscore parameters seems to be disabled along with one fix control and thus it followed a full scan path. So we want to understand if it's expected/ defaults in 19C? or it must have been altered somehow during upgrade itself and so should be reverted back to true?
SELECT D.COL1, D.CLOB2 FROM TAB1 D WHERE D.ID = :1 Global Information
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
| 72 | 18 | 53 | 0.00 | 1 | 11M | 87343 | 85GB |
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
/*+
*/
SELECT /*+optimizer_features_enable('11.2.0.4') */ D.COL1, D.CLOB2 FROM TAB1 D WHERE D.ID = :b1
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
| 0.03 | 0.01 | 0.02 | 0.00 | 2 | 54 | 13 | 104KB |
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
*/
Date: Wed, 20 Oct 2021 01:22:55 +0530
Message-ID: <CAEjw_fi4rGC6nE_tBVedVssZpkn=AG7mC41epYH8Gj-Z-PGw8w_at_mail.gmail.com>
Hello Listers, We have encountered slowness with one of the customer databases with a simple SELECT query. And analyzing the plan and outline of the sql it appeared that while we were on version 11.2.0.4, it was using index skip scan and was faster also the cost was lower but post upgrade to 19.9.0.0.0, there are few underscore parameters seems to be disabled along with one fix control and thus it followed a full scan path. So we want to understand if it's expected/ defaults in 19C? or it must have been altered somehow during upgrade itself and so should be reverted back to true?
Below underscore parameters appear to be turned off as shown in the query outline.
OPT_PARAM('_unnest_subquery' 'false')
OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
********* Plan on 19.9.0.0.0 *********************
SQL Text
SELECT D.COL1, D.CLOB2 FROM TAB1 D WHERE D.ID = :1 Global Information
Status : DONE (ALL ROWS) Instance ID : 1 SQL Execution ID : 16782261 Execution Started : 10/19/2021 12:38:18 First Refresh Time : 10/19/2021 12:38:22 Last Refresh Time : 10/19/2021 12:39:25 Duration : 67s Fetch Calls : 1
Global Stats
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
| 72 | 18 | 53 | 0.00 | 1 | 11M | 87343 | 85GB |
SQL Plan Monitoring Details (Plan Hash Value=1148202243)
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |direct path read (46) |
==========================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 64 | +4 | 1 | 1 | | | | | | 1 | TABLE ACCESS STORAGE FULL | TAB1 | 1 | 2M | 68 | +1 | 1 | 1 | 87343 | 85GB | 100.00 | Cpu (22) | | | | | | | | | | | | | |
Outline Data
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_unnest_subquery' 'false')
OPT_PARAM('_optimizer_skip_scan_enabled' 'false')
OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
OPT_PARAM('_fix_control' '14033181:0')
ALL_ROWS
OUTLINE_LEAF(_at_"SEL$1")
FULL(_at_"SEL$1" "D"_at_"SEL$1")
END_OUTLINE_DATA
*/
- Plan on 11.2.0.4 *********************
SQL Monitoring Report
SQL Text
SELECT /*+optimizer_features_enable('11.2.0.4') */ D.COL1, D.CLOB2 FROM TAB1 D WHERE D.ID = :b1
Global Information
Status : DONE (ALL ROWS) Instance ID : 1 SQL ID : 09sa822k7qsvn SQL Execution ID : 16777216 Execution Started : 10/19/2021 15:13:56 First Refresh Time : 10/19/2021 15:13:56 Last Refresh Time : 10/19/2021 15:13:56 Duration : .032143s Module/Action : SQL*Plus/- Fetch Calls : 2
Global Stats
| Elapsed | Cpu | IO | Cluster | Fetch | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
| 0.03 | 0.01 | 0.02 | 0.00 | 2 | 54 | 13 | 104KB |
SQL Plan Monitoring Details (Plan Hash Value=191420461)
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) |(# samples) |
| 0 | SELECT STATEMENT | |
| | 1 | +0 | 1 | 1 | | | |
|
| 1 | TABLE ACCESS BY INDEX ROWID | TAB1 | 1
| 6 | 1 | +0 | 1 | 1 | 1 | 8192 | |
|
| 2 | INDEX SKIP SCAN | TAB1_PK | 1
| 5 | 1 | +0 | 1 | 1 | 12 | 98304 | |
|
========================================================================================================================================================================
Query Block Name / Object Alias (identified by operation id):
1 - SEL$1 / D_at_SEL$1
2 - SEL$1 / D_at_SEL$1
Outline Data
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(_at_"SEL$1")
INDEX_SS(_at_"SEL$1" "D"_at_"SEL$1" ("TAB1"."COL3" "TAB1"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
2 - access("D"."ID"=:B1)
filter("D"."ID"=:B1)
Column Projection Information (identified by operation id):
1 - "D"."COL1"[VARCHAR2,19], "D"."CLOB2"[LOB,4000] 2 - "D".ROWID[ROWID,10]
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Oct 19 2021 - 21:52:55 CEST