Since the upgrade from 11.2.0.4 doesn't have these non-default settings
maybe this is just a DBA using the settings from another system they had
previously upgraded; or maybe it's a case of consolidating systems into a
single CDB on the upgrade and setting the CDB parameters that ought only to
have been set in a Peoplesoft PDB.
Regards
Jonathan Lewis
On Wed, 20 Oct 2021 at 09:24, Mohamed Houri <mohamed.houri@xxxxxxxxx> wrote:
Hello
These 3 parameters are exactly the same as those suggested by PeopleSoft
(PS) to be changed. You are using PeopleSoft? isn't it?
Anyway, after some unsuccessful tests in PRE-PROD, I reset the
*_optimizer_skip_scan_enabled* parameter to its default value and left
the other two parameters as suggested by PS. But I had to intervene several
times on several queries by forcing the use of the unnest subquery via the
hint *opt_param('-_unnest_subquery' 'true')*
So I don't think it's a good idea to disable the use of *skip scan
indexes* at all
Best regards
Mohamed
Le mer. 20 oct. 2021 à 05:25, Pap <oracle.developer35@xxxxxxxxx> a écrit :
Thank you Andy.
We used hints to get rid of this plan change for this query. But we are
seeing multiple queries keep coming with suboptimal plans. So wanted to
understand if we can get what all such critical optimization parameters has
been changed during this upgrade. Is there a way to get those? The
dba_hist_parameter won't show that change because version 11.2 was not
having those parameters captured in v¶meter view.
On Wed, 20 Oct 2021, 3:16 am Andy Sayer, <andysayer@xxxxxxxxx> wrote:
If the rows don’t exist for the underscore parameters in v$parameter (or
using show parameter) then they haven’t been changed (or they don’t exist).
You would query the underlying fixed tables directly in order to ensure you
see how the hidden parameters are set too.
I think your focus should be on finding the human responsible for making
these sorts of changes and see what the reasoning was. These are big
settings to change back so you might be better off just using query level
hints (opt_param) until you have investigated the why.
Thanks,
Andy
On Tue, 19 Oct 2021 at 22:01, Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank you Andy. I queried gv$parameter and saw below. So as it says
ISDEFAULT column is TRUE for all and ISMODIFIED is SYSTEM_MOD, so that
means the parameter is actually modified manually using the alter system
command. And only two of these were having non default values i.e.
skip_scan and hash_aggregation. So mostly these two are modified manually
and should be reverted if not done by oracle recommendation. But
unnest_subquery seems to have default_value as FALSE only, which looks a
little odd though, as that seems to be frequently used in many
optimizations. And again when i tried querying a 11.2.0.4 database
gv$parameter with name as these underscore parameters, somehow i am getting
zero rows. Why.so?
NAME VALUE
DEFAULT_VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE
ISMODIFIED ISADJUSTED ISDEPRECATED
_unnest_subquery FALSE FALSE TRUE TRUE IMMEDIATE
SYSTEM_MOD FALSE FALSE
_optimizer_skip_scan_enabled FALSE TRUE TRUE TRUE
IMMEDIATE SYSTEM_MOD FALSE FALSE
_gby_hash_aggregation_enabled FALSE TRUE TRUE TRUE
IMMEDIATE SYSTEM_MOD FALSE FALSE
On Wed, Oct 20, 2021 at 1:46 AM Andy Sayer <andysayer@xxxxxxxxx> wrote:
Underscore parameters should only be set if you have a reason to set
them and you have checked what Oracle Support has to say about it.
You can see if the parameter is non-default because it appears when
you do eg `show parameter optimizer_skip`. It is likely that these have
been manually set.
That said, _optimizer_skip_scan_enabled being false will very
obviously prevent index skip scans from being used. If a skip scan is
required for the query to perform well (given the existing indexes) then
this parameter is going to prevent that good plan. You can create an index
which doesn’t include the column being skilled to allow for a regular
index
scan plan.
Thanks,
Andy
On Tue, 19 Oct 2021 at 20:53, Pap <oracle.developer35@xxxxxxxxx>
wrote:
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) |
==========================================================================================================================================================================
| 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) |
| | | |
| | | | | | | |
| direct path read (46) |
==========================================================================================================================================================================
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(@"SEL$1")
FULL(@"SEL$1" "D"@"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@SEL$1
2 - SEL$1 / D@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(@"SEL$1")
INDEX_SS(@"SEL$1" "D"@"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]
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning
Visit My - Blog <http://www.hourim.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
<https://twitter.com/MohamedHouri>
