Thank you Andy. I don't remember exactly but mostly it was a new session
only. But I am 100% sure the query I tested was with some hard coded
comments in the select parts , such that it will cause a hard parse. and
The query was still opting for the old params in its path. Btw, do you mean
the existing session has to be killed/disconnected to take the new setup in
effect even if these parameters are mentioned as dynamic in nature. and it
wont take that up even if the queries will be hard parsed in the same
connected session? I see one after logon trigger but that doesn't set any
such underscore params. I will update if the plan has some Note
section which I may have missed.
On Fri, Oct 22, 2021 at 3:55 PM Andy Sayer <andysayer@xxxxxxxxx> wrote:
Are you using new sessions to test the new parameter? Do you have any
alter session code being run as part of those sessions (perhaps in a logon
trigger)?
Are there any relevant notes in your execution plans to explain any
behaviour?
On Fri, 22 Oct 2021 at 10:16, Pap <oracle.developer35@xxxxxxxxx> wrote:
We tried to set those params back to defaults using the alter system and
they are now showing modified/default values in X$ views. But surprisingly
we even tried running a few new queries manually such that they will be
hard parsed surely and take the new default parameter effect, but we are
seeing the same non default setting as it was showing before in the outline
and the query is not using skip scan. By forcing a
/*+optimizer_feature_enable(11.2.0.4)*/ hint or even
OPT_PARAM('_optimizer_skip_scan_enabled' 'true') that makes it work.
So even the view(gv$parameter) and doc both were showing these are
dynamic (i.e. issys_modifiable as Immediate), why is it not taking effect
then ? and do we really need a bounce to take those into effect? And
another question we have is if we set the OFE back to 11.2.0.4(which is
dynamic only) in database level, will these underscore parameters(say
_optimizer_skip_scan_enabled,_gby_hash_aggregation_enabled,_unnest_subquery
) will override 11.2.0.4? or 11.2.0.4 OFE at database level will supersede
these underscore parameters settings and we would be back to before
without a restart?
On Thu, Oct 21, 2021 at 2:16 PM Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank you so much for the guidance here. Understood how the is_default,
default_value may not be fully reliable. But yes, in our case as the sql
outline itself noted the hint OPT_PARAM('_unnest_subquery' 'false'), so i
believe it's truly been set as false(which is again may be set/copied by
someone wrongly).
On Wed, Oct 20, 2021 at 11:47 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
The fix_control has the description: "correct ndv for non-popular
values in join cardinality comp" and has been in existence (and enabled /
true / on / 1) since at least 12.1.0.2.
Guideline - it doesn't matter what you manage to report by querying
v$parameter / gv$parameter, if you see an underscore parameter in either of
them then it's NOT the default value - it's more likely that the report
code has an error or the internal structure is wrong. In this case I've
just run up an instance of 19.11.0.0 and you seem to have been unlucky with
the internals.
For _unnest_subquery on my instance startup, the system value is TRUE,
the session value is TRUE, is_default reports TRUE - but default_value
reports FALSE which is clearly incorrect. So you seem to have been unlucky
in finding a defect in the internal structure that has confused the issue.
In fact I've just checked and there seem to be about 50 true/false
parameters in my instance where the values for is_default, system value and
default value are not self-consistent.
The three parameters do exist in 11.2.0.4, but they don't appear in
v$parameter because they are underscore parameters and their setting of
is_default is TRUE.
You need to find out why someone has been messing about with hidden
parameters, but if a query against gv$parameter in your 11.2.0.4 system
reports NO underscore parameters then a query against gv$parameter in your
upgraded (19.9.0.0) system should also show NO underscore parameters.
Regards
Jonathan Lewis
On Wed, 20 Oct 2021 at 10:20, Pap <oracle.developer35@xxxxxxxxx> wrote:
Along with this we are seeing _fix_control is also set 14033181:0.,
_ignore_desc_in_index set as TRUE , _parallel_syspls_obey_force set as
FALSE i.e. non default ones. I want to understand what that fix_control
does. Btw are these also related to peoplesoft DB setup only and thus can
be safely reverted?
On Wed, Oct 20, 2021 at 2:38 PM Pap <oracle.developer35@xxxxxxxxx>
wrote:
Thank you Jonathan and Mohamed.
Yes , this database is a non peoplesoft one. So do you mean that
these underscore parameters were not existing in 11.2 at all and thus it
means 'skip_scan' and 'gby_hash_aggregation' and 'unnest_subquery' were
all
default TRUE and were not in our control to change them. In 19c they
introduced and added more control. Is this understanding correct?
Then I think, we can definitely revert
the _optimizer_skip_scan_enabled and _gby_hash_aggregation_enabled to
true
i.e. the default. Regarding , '_unnest_subquery' its default seems false
in
this oracle 19c version. So was it like that on 11.2 and if
it's recommended to keep it as default here i.e. false only?
On Wed, Oct 20, 2021 at 2:16 PM Jonathan Lewis <
jlewisoracle@xxxxxxxxx> wrote:
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>
