Re: OPTIMIZER_DYNAMIC_SAMPLING Level 11

From: Stefan Koehler <contact_at_soocs.de>
Date: Thu, 11 Dec 2014 22:33:23 +0100 (CET)
Message-ID: <1203071903.292919.1418333603106.open-xchange_at_app01.ox.hosteurope.de>

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: _at_OracleSK

> Martin Preiss <mtnpreiss_at_gmx.de> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 11 2014 - 22:33:23 CET