Re: OPTIMIZER_DYNAMIC_SAMPLING Level 11
- From: Stefan Koehler <contact@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, mtnpreiss@xxxxxx
- Date: Thu, 11 Dec 2014 22:33:23 +0100 (CET)
Hi Martin,
the ADS stuff is quite interesting. I used a slightly modified SQL and the same
data example from my blog post for some more researching and found
several unexpected behaviors with 12.1.0.2.
alter session set optimizer_dynamic_sampling=11;
SQL 1: select /* T1 */ * from SYS.DYNTEST where COUNTRY = 'DE' and WERKS =
'1200';
SQL 2: select /* T2 */ * from SYS.DYNTEST where COUNTRY = 'DE' and WERKS =
'1200';
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)|
E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)|
| 1000 |00:00:00.01 | 90 |
|* 1 | TABLE ACCESS FULL| DYNTEST | 1 | 1000 | 13000 | 7 (0)|
00:00:01 | 1000 |00:00:00.01 | 90 |
-----------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
1. It seems like dbms_sqldiag.dump_trace is not aware of ADS. Recreating a CBO
trace from the cache by using dbms_sqldiag.dump_trace does not work
properly.
2. In no case a SQL plan directive was created with
optimizer_dynamic_sampling=11. It is the same as you have observed, but a lower
level that i have
tested created one.
3. The CBO trace (with SQL1/SQL2 example) includes some information about that
persistent cache implementation.
SQL 1:
--------
** Performing dynamic sampling initial checks. **
** Not using old style dynamic sampling since ADS is enabled.
…
SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0,
objid = 91085, objtyp = 1, vecsize = 3, colvec = [1, 2, ], fid =
11339009480354491783
SPD: Modified felem, fid=11339009480354491783, ftype = 1, freason = 1,
dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
SQL 2:
--------
** Performing dynamic sampling initial checks. **
** Not using old style dynamic sampling since ADS is enabled.
…
SPD: Generating finding id: type = 1, reason = 1, objcnt = 1, obItr = 0,
objid = 91085, objtyp = 1, vecsize = 3, colvec = [1, 2, ], fid =
11339009480354491783
SPD: Modified felem, fid=11339009480354491783, ftype = 1, freason = 1,
dtype = 0, dstate = 0, dflag = 0, ver = YES, keep = NO
4. I expected that the second SQL can use the previously stored dynamic
sampling result (as it just differs in the comment section), but the SQL trace
shows dynamic sampling (recursive SQL DS_SVC) at any time.
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */
SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "DYNTEST") */ 1
AS C1 FROM "SYS"."DYNTEST" "DYNTEST" WHERE ("DYNTEST"."WERKS"='1200')
AND ("DYNTEST"."COUNTRY"='DE')) innerQuery
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */
SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") */ 1 AS C1 FROM (SELECT
"DYNTEST"."COUNTRY" "COUNTRY","DYNTEST"."WERKS" "WERKS","DYNTEST"."TEXT"
"TEXT" FROM "SYS"."DYNTEST" "DYNTEST" WHERE "DYNTEST"."WERKS"='1200' AND
"DYNTEST"."COUNTRY"='DE') "VW_DIS_1") innerQuery
… and in the end i discovered that "oradebug doc component" does not work
anymore with Oracle 12.1.0.2 on Solaris x86. It crashes with error
"ORA-07445: exception encountered: core dump [strlen()+20] [SIGSEGV]
[ADDR:0xA00000000] [PC:0xFFFF80FFBF313AF4] [Address not mapped to object] []".
Afterwards i just started Oracle 12.1.0.1 on Linux and have seen that there is
a tracing component SQL_DS (SQL Dynamic Sampling Services (qksds)) in
RDBMS library. Maybe somebody else on this list have already investigated this
ADS behavior in detail.
Best Regards
Stefan Koehler
Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
> Martin Preiss <mtnpreiss@xxxxxx> hat am 11. Dezember 2014 um 20:32
> geschrieben:
>
>
> Hi Stefan,
>
> thank you again: my test was very simple and I did not use
> DBMS_SPD.FLUSH_SQL_PLAN_DIRECTIVE() to persist directives - but I did
> similar tests before and saw the expected behaviour, so I think adding
> the test is not that important.
>
> But the statement from Maria Colgan's white paper answers my main
> question - as you, Mauro, and Chinar already said: the persisting is
> only done in the cache (and maybe in a corresponding directive). But I
> fear that this kind of transient information could make performance
> analysis quite challenging when multiple effects - and some of them
> almost invisible - have an impact on the optimizer's calculations.
>
> Regards
>
> Martin
--
//www.freelists.org/webpage/oracle-l
Other related posts:
