Just another WordPress site
Archive for the ‘12c’ Category.
Just a quick note that I posted slides for the 2 talks I did at ECO in Raleigh this week:
Keynote: Creative Problem Solving (for Oracle Systems)
In-Memory In Action (slides by Tanel Poder) 🙂
Great crowd. I really enjoyed myself.
Note: You can also find other presentations on my Whitepapers/Presentations page via the link at the top of the screen.
As shared by a well known Oracle and Big Data performance geek!
SQL> ALTER SYSTEM SET inmemory_size = 5T SCOPE=spfile; ALTER SYSTEM SET inmemory_size = 5T SCOPE=spfile * ERROR at line 1: ORA-32005: error while parsing size specification [5T] SQL> ALTER SYSTEM SET inmemory_size = 5120G SCOPE=spfile; System altered.
🙂
I started looking into In-Memory on RAC this week. Data can be distributed across RAC nodes in a couple of different ways. The default is to spread it across the available nodes in the cluster. So if you had a 2 node cluster, roughly 50% of the data in your table or partition would be loaded into the column store in each of the 2 instances.
SYS@dw1> alter table kso.skew inmemory;
Table altered.
SYS@dw1> @gen_ddl
Enter value for object_type:
Enter value for owner: KSO
Enter value for object_name: SKEW
DDL
--------------------------------------------------------------------------------
CREATE TABLE "KSO"."SKEW"
( "PK_COL" NUMBER,
"COL1" NUMBER,
"COL2" VARCHAR2(30),
"COL3" DATE,
"COL4" VARCHAR2(1),
PRIMARY KEY ("PK_COL")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 INVISIBLE COMPUTE STATISTICS
STORAGE(INITIAL 865075200 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 1480589312 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
INMEMORY PRIORITY NONE MEMCOMPRESS FOR QUERY LOW
DISTRIBUTE AUTO NO DUPLICATE <--- here's the RAC bit
CACHE
SYS@dw1> @inmem_segs
Enter value for owner:
Enter value for segment_name:
OWNER SEGMENT_NAME ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
----------------
sum
no rows selected
SYS@dw1> select count(*) from kso.skew;
COUNT(*)
----------
32000004
SYS@dw1> @inmem_segs
Enter value for owner:
Enter value for segment_name:
OWNER SEGMENT_NAME ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
KSO SKEW 1,413.0 391.4 1.7 749.4
----------------
sum 391.4
SYS@dw1> -- so about half the data is loaded in the local instance column store
SYS@dw1> -- let's see what's in the other instance's cache
SYS@dw1> l
1 SELECT v.owner, v.segment_name,
2 v.bytes/(1024*1024) orig_size_megs,
3 v.inmemory_size/(1024*1024) in_mem_size_megs,
4 (v.bytes - v.bytes_not_populated) / v.inmemory_size comp_ratio,
5 v.bytes_not_populated/(1024*1024) megs_not_populated
6 FROM v$im_segments v
7 where owner like nvl('&owner',owner)
8* and segment_name like nvl('&segment_name',segment_name)
SYS@dw1> l6
6* FROM v$im_segments v
SYS@dw1> c/v$/gv$/
6* FROM gv$im_segments v
SYS@dw1> /
Enter value for owner:
Enter value for segment_name:
OWNER SEGMENT_NAME ORIG_SIZE_MEGS IN_MEM_SIZE_MEGS COMP_RATIO MEGS_NOT_POPULATED
------------------------------ ------------------------------ -------------- ---------------- ---------- ------------------
KSO SKEW 1,413.0 569.1 1.6 526.6
KSO SKEW 1,413.0 391.4 1.7 749.4
----------------
sum 960.5
In preparation for our upcoming 12c In-Memory Webcast @CaryMillsap, @TanelPoder, and I solicited questions from members of the universe at large on the interweb. We got a question about how In-Memory works with the 12c multi-tentant option and it got me thinking so I gave it a quick try. As it turns out, it works about as you would expect. The basic idea is to turn it on for the container DB (which is where the memory is actually allocated (ala the other main shared memory regions) and then decide which PDBs to allow to use it (and if so how much of it to use) or not. First, here are the steps necessary to allocate the memory in the container DB.
Oracle’s 12.1.0.2 was released a few weeks ago (You can download it from OTN here: Oracle 12.1.0.2 Download). While technically a minor point release, it contains a couple of major features that would normally be rolled out in a more substantial version change like 12cR2 or perhaps V13. Of course the most highly anticipated feature is a new option (Oracle In-Memory Option) that provides a column oriented, in-memory store. Enkitec was in the Beta program, so we’ve been testing it out for quite a while now and we are impressed. Here’s a link to a video of a conversation between myself, Tanel Poder and Cary Millsap about the In-memory Option published prior to the general release. Note: the three of us are also scheduled to do a webcast on the topic on Sep. 17th at 9:00AM CDT. You can sign up here if you are interested: In-Memory Webcast
But back to the topic: What this new option provides is a radical departure from the way Oracle has traditionally managed data access. In the past, all data access was done using row-major format, which is a foundation of the Oracle RDBMS architecture (I’m of course leaving out some esoteric formats such as the hybrid columnar compressed (HCC) format that is available on Exadata). At any rate, this columnar format is a major change in the way data is accessed for Oracle, and while the name of the option indicates that the secret sauce is the fact that the data is accessed from memory, I’m going to argue that the “memory” part is not the most important factor. In my opinion, the column-oriented format is why it’s “The Next Big Thing”.
While accessing data from RAM is definitely faster than reading it off disk, it’s important to note that Oracle has been serving data from memory for decades via the standard buffer cache. In fact, you could describe the Oracle RDBMS as a very sophisticated disk caching mechanism. That’s certainly a vast over simplification, but it’s really not too far from reality. Many Oracle systems spend most of their time accessing data from the buffer cache. Back in the day, DBA’s even invented a metric to describe the effectiveness of the caching. The much maligned “buffer cache hit ratio” was used for that purpose and is still present in the modern day AWR reports. While tuning artificial ratios like this one has long since gone out of fashion, it’s important to note that it is not uncommon to see this ratio in the upper 90′s. (i.e. 99% of blocks being accessed from RAM is common) And in fact, we can pin tables in the buffer cache so that all rows are accessed from memory. So if that’s the case, then we should be able to compare speeds of queries executed on data in memory using both the standard row-major format and the new columnar format. Let’s give it a quick try.
Just a quick post on a new Exadata feature called Zone Maps. They’re similar to storage indexes on Exadata, but with more control (you can define the columns and how the data is refreshed for example). People have complained for years that storage indexes provided no control mechanisms, but now we have a way to exert our God given rights as DBA’s to control yet another aspect of the database. Here’s a link to the 12.1.0.2 documentation which resides in the Data Warehousing Guide: Zone Map Documentation
Zone Maps are restricted to Exadata storage by the way (well probably they work on ZFS and Pillar too). Have a look at the Oracle error messages file:
>grep -i "storage type" $ORACLE_HOME/rdbms/mesg/oraus.msg | grep -i "not supported"
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:31969, 00000, "ZONEMAP not supported for table stored in tablespace of this storage type"
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:64307, 00000, " Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type"
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:64309, 00000, " Hybrid Columnar Compression with row-level locking is not supported for tablespaces on this storage type."
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:65425, 00000, "CLUSTERING clause not supported for table stored in tablespace of this storage type"
/u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/mesg/oraus.msg:65451, 00000, "Advanced index compression is not supported for tablespaces on this storage type."
So according to the messages file, there are a handful of features that are restricted in this fashion (Zone Maps, HCC, Attribute Clustering and Advanced Index Compression).
As a bit of totally irrelevant history, zone maps were actually included in the 12.1.0.1 release, but the documentation on them was removed. So they worked, but they were undocumented.
Here’s an example on a 12.1.0.1 DB on a non-Exadata platform.
SQL*Plus: Release 12.1.0.1.0 Production on Wed Aug 13 15:41:46 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
INSTANCE_NAME STARTUP_TIME CURRENT_TIME DAYS SECONDS
---------------- ----------------- ----------------- ------- ----------
LAB1211 13-AUG-2014 09:54 13-AUG-2014 15:41 .24 20820
SYS@LAB1211> create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2);
create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2)
*
ERROR at line 1:
ORA-65425: CLUSTERING clause not supported for table stored in tablespace of this storage type
SYS@LAB1211> create table kso.junk1 (col1 number, col2 number);
Table created.
SYS@LAB1211> create materialized zonemap skew_zonemap on kso.junk1(col1);
create materialized zonemap skew_zonemap on kso.junk1(col1)
*
ERROR at line 1:
ORA-31969: ZONEMAP not supported for table stored in tablespace of this storage type
Note that both zone maps and attribute clustering were disallowed with the “not supported for table stored in tablespace of this storage type” error message.
By the way, attribute clustering is another interesting new feature of 12g that allows you to declaratively instruct Oracle to store data on disk in a sorted order. This physical ordering can have big benefit for storage indexes or zone maps (or any btree index where clustering factor is important for that matter). Oracle’s new In-Memory column store also has a min/max pruning feature (storage indexes) which means physical ordering on disk is important with that feature as well.
Anyway, here’s a link to the 12.1.0.2 documentation on attribute clustering which also resides in the Data Warehousing Guide: Attribute Clustering Documentation
And here’s another example using 12.1.0.2 on an Exadata.
SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 13 15:42:18 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
INSTANCE_NAME STARTUP_TIME CURRENT_TIME DAYS SECONDS
---------------- ----------------- ----------------- ------- ----------
INMEM 24-JUL-2014 18:35 13-AUG-2014 15:42 19.88 1717600
Elapsed: 00:00:00.00
SYS@INMEM> @test_zonemap
SYS@INMEM> create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2);
Table created.
Elapsed: 00:00:00.01
SYS@INMEM> create materialized zonemap skew_zonemap on kso.junk1(col1);
Materialized zonemap created.
Elapsed: 00:00:00.15
SYS@INMEM>
SYS@INMEM> -- so as expected, we're able to create an attribute clustered table and a zone map on Exadata
SYS@INMEM>
SYS@INMEM> -- Let's try creating a tablespace that is not on Exa storage (even though the DB is on EXA platform)
SYS@INMEM>
SYS@INMEM> create tablespace KSO_NON_EXA datafile '/home/oracle/KSO_NON_EXA.dbf' size 100M;
Tablespace created.
Elapsed: 00:00:00.38
SYS@INMEM> @tablespaces
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MGT ALLOC_TYP SPACE_MGT BLOCK_SIZE PREDICA
--------------- --------- --------- --------- ---------- --------- --------- ---------- -------
CLASS_DATA ONLINE PERMANENT LOGGING LOCAL SYSTEM AUTO 8192 STORAGE
EXAMPLE ONLINE PERMANENT NOLOGGING LOCAL SYSTEM AUTO 8192 STORAGE
KSO_NON_EXA ONLINE PERMANENT LOGGING LOCAL SYSTEM AUTO 8192 HOST <===
SYSAUX ONLINE PERMANENT LOGGING LOCAL SYSTEM AUTO 8192 STORAGE
SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL 8192 STORAGE
TEMP ONLINE TEMPORARY NOLOGGING LOCAL UNIFORM MANUAL 8192 STORAGE
UNDOTBS1 ONLINE UNDO LOGGING LOCAL SYSTEM MANUAL 8192 STORAGE
USERS ONLINE PERMANENT LOGGING LOCAL SYSTEM AUTO 8192 STORAGE
8 rows selected.
Elapsed: 00:00:00.02
SYS@INMEM>
SYS@INMEM> -- note that tablespace KSO_NON_EXA is on local disk, not Exadata storage servers, so PREDICATE_EVALUATION is set to HOST.
SYS@INMEM>
SYS@INMEM> drop table kso.junk1;
Table dropped.
Elapsed: 00:00:00.01
SYS@INMEM> create table kso.junk1 (col1 number, col2 number) clustering by linear order (col1,col2) tablespace kso_non_exa;
Table created.
Elapsed: 00:00:00.01
SYS@INMEM> select owner, table_name, tablespace_name from dba_tables where table_name like 'JUNK1';
OWNER TABLE_NAME TABLESPACE_NAME
-------------------- ------------------------------ ---------------
KSO JUNK1 KSO_NON_EXA
Elapsed: 00:00:00.01
SYS@INMEM>
SYS@INMEM> -- wow - that's a bit of a surprise, clustered table create worked on non-Exa storage
SYS@INMEM> -- maybe the check is done on some other level than the tablespace
SYS@INMEM>
SYS@INMEM> create materialized zonemap skew_zonemap on kso.junk1(col1);
create materialized zonemap skew_zonemap on kso.junk1(col1)
*
ERROR at line 1:
ORA-31969: ZONEMAP not supported for table stored in tablespace of this storage type
Elapsed: 00:00:00.00
So as you can see, attempting to create the zone map on non-Exa storage failed as expected. But I was able to create a clustered table on non-Exa storage, which is a little weird. So while the error message for attribute clustering exists in the messages file, it doesn’t appear that there is a check in the code, at least at the tablespace level. I don’t have a 12.1.0.2 install on a non-Exadata platform at the moment to test it out, but if you do, please let me know.
That’s it for now. I hope to do some more detailed posts on In-Memory, Zone Maps, Attribute Clustering in the near future. As always, your comments are welcomed.
This is the third and final post on follow up questions from the Redgate webinar I did on 12c Adaptive Optimization (the link goes to a recording of the webcast by the way).
Also, here are links to the 2 earlier posts:
So here are the last set of questions along with my responses:
Q: Is this feature on by default or you have to set a parameter to make sure of it?
A: It’s on by default but can be turned off by the methods listed in the presentation.Q: Is there any drawback of adaptive execution plan?
A: New features (especially auto-magic ones) always make people nervous, but I don’t see too many potential pitfalls with this one. The fact that it is enabled by default out of the box is also a good indicator that the developers themselves have a lot of confidence in it. There is certainly more work going on to collect statistics and buffer rows, but it seems quite minimal and only happens on the first execution. So my basic answer is no, I don’t foresee any major drawbacks.Q: For adaptive plans, usually queries are more complex, with multiple combinations of hash joins and nested loops. But adaptive plans only switches to one “sub plan”, correct? How does it account for all the various combinations?
A: A sub-plan is limited to a single join. There can obviously be many joins in a single plan and thus many sub-plans. But each sub-plan will result in either a HJ or a NLJ. At the end there will be only one final plan. See my previous post (Part 1) for an example of a more complex plan with multiple sub-plans.Q: parallel distribution methods: why not use broadcast all the time? 🙂
A: 🙂Q: Would adaptive optim switch to a better index if it finds itself sitting on a wrong index?
A: I presume the question is with regard to Adaptive Plans kicking in on the first execution, if so, the answer is No. At this point only join methods and px distribution methods can be changed. I expect this will be expanded over time though.Q: Does same plan_hash_value’s means same final plans?
A: Yes – plan hash value is computed based on the final plan with no regard to the fact that the plan was adaptive.Q: How correlated plan_hash_values with final plans? How we can find same final plans?
A: Plan hash value is computed based on final plan, so the correlation is very high. 🙂Q: Dynamic sampling would not put an excessive pressure on the CPU?
A: I guess it could, but it’s been around for some time and I haven’t been involved in any situations where the time spent on dynamic sampling was an issue. Setting it to 11 may give us some chances to see such a thing though. More often the issues arise when dynamic sampling does not come up with a good picture of the data due to the limited size of the sample.Q: Is dynamic sampling = 11 actually a good blanket setting, or do you not trust the optimizer that much? What do you use and why?
A: The optimizer_dynamic_sampling parameter still defaults to 2 in 12c. That alone makes me cautious about setting it to the new totally auto-magic value of 11. If the developers have enough confidence in a new feature to make it the default, then I will be more trusting. I prefer to stick with default values unless I have to make a change to address a specific issue. I have worked on a few systems that change the default setting, but 11 has not been one of those values (yet). I need to do more testing with it.Q: Gotta love Spinal Tap… crank it up to 11 !
A: Rock and Roll!Q: Is there any effect on cpu utilisation becoz of adaptive optimisation??
A: There is definitely some extra overhead in collecting statistics and buffering rows but it should be minimal and it should only affect the initial execution.Q: Can HJ be change to NL in 1-st execution? What is threshold for such change?
A: Yes – Adaptive Plans kick in the first execution. The threshold depends on the specific case. See the example earlier in part 2 of this series for an example of calculating the inflection point (from a 10053 trace).Q: This means that if it is abandoned once it will also be abondoned if ran again?
A: Yes, assuming no other changes occur. But there are many things that can change such as Adaptive Cursor Sharing, Cardinality Feedback, etc… and of course the data itself and/or the statistics about the data can change over time as well. Just to be clear, the choice between the the two join methods is only made during the first execution after a hard parse, so once a statement is loaded into the cache, the plan will be static until something changes that causes a new child cursor to be created.Q: At what data volumes does Adaptive Optimization become likely to be helpful.
A: Any volume that causes a NLJ to result in significantly different elapsed time than HJ.Q: Does AWR show these updated adaptive plans with minus ?
A: That’s a good question. Yes, you can use the dbms_xplan.display_awr with the ‘adaptive’ format option (see the example below).
SYS@db12c1> select * from table(dbms_xplan.display_awr('&sql_id',nvl('&plan_hash_value',null),null,'adaptive'));
Enter value for sql_id: 6qg99cfg26kwb
Enter value for plan_hash_value:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6qg99cfg26kwb
--------------------
SELECT COUNT(UNQ) UNQ, COUNT(PFX) PFX FROM (SELECT /*+ first_rows(1)
leading(cc) */ CD.TYPE# UNQ, NULL PFX FROM SYS.CCOL$ CC, SYS.CDEF$ CD
WHERE CC.OBJ# = :B2 AND CC.INTCOL# = :B1 AND CD.CON# = CC.CON# AND
CD.OBJ# = CC.OBJ# AND CD.ENABLED IS NOT NULL AND CD.INTCOLS = 1 AND
CD.TYPE# IN (2,3) AND BITAND(CD.DEFER, 2+4) = 4 AND ROWNUM < 2 UNION
ALL SELECT /*+ first_rows(1) leading(i) */ CASE WHEN I.INTCOLS = 1 AND
BITAND(I.PROPERTY,1) = 1 THEN 3 ELSE NULL END UNQ, CASE WHEN IC.POS# =
1 THEN 1 ELSE NULL END PFX FROM SYS.IND$ I, SYS.ICOL$ IC WHERE I.BO# =
:B2 AND I.BO# = IC.BO# AND IC.INTCOL# = :B1 AND I.OBJ# = IC.OBJ# AND
BITAND(I.FLAGS,1025) = 0 AND ROWNUM < 2 )
Plan hash value: 1065215175
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | VIEW | | 2 | 32 | 6 (0)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | COUNT STOPKEY | | | | | |
|- 5 | HASH JOIN | | 1 | 35 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 35 | 3 (0)| 00:00:01 |
|- 7 | STATISTICS COLLECTOR | | | | | |
| 8 | TABLE ACCESS CLUSTER | CCOL$ | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS CLUSTER | CDEF$ | 1 | 22 | 1 (0)| 00:00:01 |
|- 11 | TABLE ACCESS BY INDEX ROWID BATCHED| CDEF$ | 1 | 22 | 1 (0)| 00:00:01 |
|- 12 | INDEX RANGE SCAN | I_CDEF2 | 1 | | 1 (0)| 00:00:01 |
| 13 | COUNT STOPKEY | | | | | |
|- 14 | HASH JOIN | | 1 | 38 | 3 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 38 | 3 (0)| 00:00:01 |
|- 16 | STATISTICS COLLECTOR | | | | | |
| 17 | TABLE ACCESS CLUSTER | IND$ | 1 | 21 | 2 (0)| 00:00:01 |
| 18 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
| 19 | TABLE ACCESS CLUSTER | ICOL$ | 1 | 17 | 1 (0)| 00:00:01 |
|- 20 | TABLE ACCESS CLUSTER | ICOL$ | 1 | 17 | 1 (0)| 00:00:01 |
|- 21 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
46 rows selected.
Q: Is there any way to encourage the optimizer to collect the information but not act on it?
A: Yes, set optimizer_adaptive_reporting_only = true.Q: Does adaptive distribution for parallel processing work as expected on a Virtual server where resources can be spread over several other servers?
A: No idea (in fact I'm not even sure I understand the question). Give it a test and let us know what you find out. 🙂Q: Does Adaptive Optimization help oracle optimize somewhat complex nested views? I know nested views are not recommended but we sometimes have to live with what we inherited.
A: I don't think this particular feature is going to help nested views specifically. But who knows. The optimizer seems to get lost occasionally with deeply nested views. By the way, there is an interesting new procedure in 12c called dbms_utility.expand_sql_text which spits out the fully expanded version of a SQL statement that accesses data through views. Tom Kyte has blogged about it here: 12c - SQL Text ExpansionQ: We regularly have hash join problems tracable to temp space limits. Shifting to nested loops has proven necessary in 10 and 11. Early detection and shifting to nested loops would be important for us.
A: I'm not sure this feature is really going to help you much in that regard unless the optimizer is erroneously picking the HJ based on incorrect estimates. If you're just forcing the NLJ to avoid poor i/o performance on the temp stuff though it probably won't help. In that case you need to figure out how to sort less or use more memory (increase pga, or use manual workarea size, or use more slaves in px, etc...).Q: So if sort/merge join is used then this feature would not go to nested loop/hash join if sort/merge join is a bad plan ?
A: No it applies only to HJ and NLJ as of 12.1.0.1.Q: What happens with the rows that were read up to inflection point? Does Oracle start reading from the scratch again?
A: The rows are buffered so they don't need to be re-read.Q: Will the SQL scripts that were demonstrated for reviewing the SQL plan information be made available?
A: Most are on this blog already (use the search box to locate them) but let me know if you can't find any of the ones I used.Q: It's is a contraction for it is or it has. Its is a possessive pronoun meaning, more or less, of it or belonging to it.
A: Duly noted (and fixed in the presentation). 🙂Q: Can we *force* plan change in mid-execution?
A: No. You can enable or disable the feature, but the optimizer decides whether to switch or not.Q: How long statistics collector runs if it does not switch?
A: It should only run until the inflection point (the point at which it makes the decision), but I have not actually tested this.Q: Is there a way adaptive can be disabled for PDB and enabled for others?
A: Yes, the optimizer_adaptive_features parameter can be set separately for each PDB (see the example below).
> rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Mon Dec 9 19:53:03 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
INSTANCE_NAME STARTUP_TIME CURRENT_TIME DAYS SECONDS
---------------- ----------------- ----------------- ------- ----------
CONTAIN1 02-DEC-2013 03:22 09-DEC-2013 19:53 7.69 664225
SYS@CONTAIN1> @whoami_pdb
CON_ID CON_NAME USERNAME USER# SID SERIAL# PREV_HASH_VALUE SCHEMANAME OS_PID
---------- ---------- --------------- ---------- ---------- ---------- --------------- ------------------------------ -------
1 CDB$ROOT SYS 0 24 295 3265981639 SYS 4481
SYS@CONTAIN1> @connect_pdb
Enter value for pdb_name: plug1
Session altered.
SYS@CONTAIN1:PLUG1> @parms
Enter value for parameter: optimizer_adaptive_features
Enter value for isset:
Enter value for show_hidden:
NAME VALUE ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
optimizer_adaptive_features TRUE TRUE TRUE TRUE
SYS@CONTAIN1:PLUG1> alter system set optimizer_adaptive_features=false;
System altered.
SYS@CONTAIN1:PLUG1> @parms
Enter value for parameter: optimizer_adaptive_features
Enter value for isset:
Enter value for show_hidden:
NAME VALUE ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
optimizer_adaptive_features FALSE TRUE TRUE TRUE
SYS@CONTAIN1:PLUG1> @connect_pdb
Enter value for pdb_name: plug2
Session altered.
SYS@CONTAIN1:PLUG2> @whoami_pdb
CON_ID CON_NAME USERNAME USER# SID SERIAL# PREV_HASH_VALUE SCHEMANAME OS_PID
---------- ---------- --------------- ---------- ---------- ---------- --------------- ------------------------------ -------
4 PLUG2 SYS 0 24 295 2710464132 SYS 4481
SYS@CONTAIN1:PLUG2> @parms
Enter value for parameter: optimizer_adaptive_features
Enter value for isset:
Enter value for show_hidden:
NAME VALUE ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
optimizer_adaptive_features TRUE TRUE TRUE TRUE
SYS@CONTAIN1:PLUG2> connect / as sysdba
Connected.
INSTANCE_NAME STARTUP_TIME CURRENT_TIME DAYS SECONDS
---------------- ----------------- ----------------- ------- ----------
CONTAIN1 02-DEC-2013 03:22 09-DEC-2013 19:54 7.69 664324
SYS@CONTAIN1> @parms
Enter value for parameter: optimizer_adaptive_features
Enter value for isset:
Enter value for show_hidden:
NAME VALUE ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
optimizer_adaptive_features TRUE TRUE TRUE TRUE
So you can set the optimizer_adaptive_features parameter separately for each PDB. Note: here are links to the couple of scripts I used in this post:
There was another good question that I don't have time to look into at the moment.
Q: In the Pro*C sequence PREPARE, OPEN, FETCH, at what point(s) might Oracle switch plans? If during FETCH, how does Oracle return the next row/array?
Maybe I'll get around to that later but if anyone wants to give it a shot and post the results in the comments section that would be great. 🙂
This is the second post on follow up questions from the Redgate webinar I did on 12c Adaptive Optimization. The first post is here: 12c Adaptive Optimization – Part 1. Since there were several comments and questions about hints and how they interact with Adaptive Plans, I decided to limit this 2nd post to that topic.
Q: Regarding turning off the adaptive optimization (particularly adaptive joins), will there also be a hint to disable it for a particular SQL?
Q: can we pick and choose SQL’s not to run this collector forA: There are no specific hints to enable or disable Adaptive Plans as of 12.1.0.1. However, the OPT_PARAM hint does work with both the OPTIMIZER_ADAPTIVE_FEATURES parameter and the “_optimizer_adaptive_plans” parameter.
Here’s an example:
SYS@db12c1> -- statement that wants to generate an adaptive plan
SYS@db12c1> select product_name
2 from oe.order_items o, oe.product_information p
3 where o.unit_price=15 and o.quantity > 1
4 and o.product_id = p.product_id
5 /
PRODUCT_NAME
--------------------------------------------------
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
13 rows selected.
SYS@db12c1> @prev_sql
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME SQL_TEXT
------------- ------ ---------- ------ ---------- ----------------------------------------------------------------------
3ycnqgx5nc8nn 0 1553478007 1 .00 select product_name from oe.order_items o, oe.product_information p wh
SYS@db12c1> @dplan_adaptive
Enter value for sql_id: 3ycnqgx5nc8nn
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3ycnqgx5nc8nn, child number 0
-------------------------------------
select product_name from oe.order_items o, oe.product_information p
where o.unit_price=15 and o.quantity > 1 and o.product_id = p.product_id
Plan hash value: 1553478007
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| * 1 | HASH JOIN | | 4 | 128 | 7 (0)| 00:00:01 |
|- 2 | NESTED LOOPS | | | | | |
|- 3 | NESTED LOOPS | | 4 | 128 | 7 (0)| 00:00:01 |
|- 4 | STATISTICS COLLECTOR | | | | | |
| * 5 | TABLE ACCESS STORAGE FULL| ORDER_ITEMS | 4 | 48 | 3 (0)| 00:00:01 |
|- * 6 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 1 | | 0 (0)| |
|- 7 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 | 20 | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS STORAGE FULL | PRODUCT_INFORMATION | 1 | 20 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")
5 - storage(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
6 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Reoptimized plan:
-----------------
This cursor is marked for automatic reoptimization. The plan that is
expected to be chosen on the next execution is displayed below.
Plan hash value: 1553478007
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 416 | 8 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 13 | 416 | 8 (0)| 00:00:01 |
|* 2 | TABLE ACCESS STORAGE FULL| ORDER_ITEMS | 13 | 156 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS STORAGE FULL| PRODUCT_INFORMATION | 288 | 5760 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")
2 - storage("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1)
filter("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1)
Note
-----
- this is an adaptive plan
60 rows selected.
SYS@db12c1> -- so the previous statement used an adaptive plan picking a HJ over the NLJ
SYS@db12c1>
SYS@db12c1> -- now let's turn off adaptive plans via the OPT_PARAM hint
SYS@db12c1> -- (set _optimizer_adaptive_plans or optimizer_adaptive_features to false)
SYS@db12c1>
SYS@db12c1> select /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */ product_name
2 from oe.order_items o, oe.product_information p
3 where o.unit_price=15 and o.quantity > 1
4 and o.product_id = p.product_id
5 /
PRODUCT_NAME
--------------------------------------------------
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
13 rows selected.
SYS@db12c1> @x
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 04g4xyu3788qm, child number 0
-------------------------------------
select /*+ OPT_PARAM('_optimizer_adaptive_plans','false') */
product_name from oe.order_items o, oe.product_information p where
o.unit_price=15 and o.quantity > 1 and o.product_id = p.product_id
Plan hash value: 1255158658
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4 | 128 | 7 (0)| 00:00:01 |
|* 3 | TABLE ACCESS STORAGE FULL | ORDER_ITEMS | 4 | 48 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 | 20 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - storage(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
4 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")
26 rows selected.
SYS@db12c1> -- So the plan has reverted to the NL Join and is not marked as adaptive
So, even though there is no specific hint at this point, the OPT_PARAM hint can be used to control this behavior on a statement by statement basis.
Q: how does AP (Adaptive Plans) treat query HINTS?
Q: Does adaptive join selection potentially override query hints?A: As to whether AP can override hints, it does not appear that it can. If you specify a join method with a valid hint, a 10053 (Wolfgang) trace will show that AP’s are not used due to the hint. For example, if you use a hint to specify a nested loop join, the optimizer will not allow AP to override that directive and the 10053 trace will show this behavior.
Here’s an example:
SSYS@db12c1> select product_name
2 from oe.order_items o, oe.product_information p
3 where o.unit_price=15 and o.quantity > 1
4 and o.product_id = p.product_id
5 /
PRODUCT_NAME
--------------------------------------------------
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
13 rows selected.
SYS@db12c1> @x
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3ycnqgx5nc8nn, child number 0
-------------------------------------
select product_name from oe.order_items o, oe.product_information p
where o.unit_price=15 and o.quantity > 1 and o.product_id = p.product_id
Plan hash value: 1553478007
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
|* 1 | HASH JOIN | | 13 | 416 | 8 (0)| 00:00:01 |
|* 2 | TABLE ACCESS STORAGE FULL| ORDER_ITEMS | 13 | 156 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS STORAGE FULL| PRODUCT_INFORMATION | 288 | 5760 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")
2 - storage(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
Note
-----
- this is an adaptive plan
27 rows selected.
SYS@db12c1> -- from 10053
SYS@db12c1> !grep -i inflection adapt*trc
Searching for inflection point (join #1) between 0.00 and 12.76
AP: Computing costs for inflection point at min value 0.00
DP: Using binary search for inflection point search
DP: Costing Nested Loops Join for inflection point at card 0.00
DP: Costing Hash Join for inflection point at card 0.00
AP: Computing costs for inflection point at max value 12.76
DP: Costing Nested Loops Join for inflection point at card 12.76
DP: Costing Hash Join for inflection point at card 12.76
AP: Searching for inflection point at value 1.00
DP: Costing Nested Loops Join for inflection point at card 6.38
DP: Costing Hash Join for inflection point at card 6.38
AP: Searching for inflection point at value 6.38
DP: Costing Nested Loops Join for inflection point at card 3.19
DP: Costing Hash Join for inflection point at card 3.19
AP: Searching for inflection point at value 3.19
DP: Costing Nested Loops Join for inflection point at card 4.78
DP: Costing Hash Join for inflection point at card 4.78
AP: Searching for inflection point at value 4.78
DP: Costing Nested Loops Join for inflection point at card 5.58
DP: Costing Hash Join for inflection point at card 5.58
DP: Costing Nested Loops Join for inflection point at card 5.58
DP: Found point of inflection for NLJ vs. HJ: card = 5.58
SYS@db12c1> -- now with valid join hint
SYS@db12c1> select /*+ leading(o) use_nl(p) */ product_name
2 from oe.order_items o, oe.product_information p
3 where o.unit_price=15 and o.quantity > 1
4 and o.product_id = p.product_id
5 /
PRODUCT_NAME
--------------------------------------------------
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
Screws
13 rows selected.
SYS@db12c1> @x
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bytr421c0c2n7, child number 0
-------------------------------------
select /*+ leading(o) use_nl(p) */ product_name from oe.order_items o,
oe.product_information p where o.unit_price=15 and o.quantity > 1 and
o.product_id = p.product_id
Plan hash value: 1255158658
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 16 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 13 | 416 | 16 (0)| 00:00:01 |
|* 3 | TABLE ACCESS STORAGE FULL | ORDER_ITEMS | 13 | 156 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PRODUCT_INFORMATION_PK | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION | 1 | 20 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - storage(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))
4 - access("O"."PRODUCT_ID"="P"."PRODUCT_ID")
26 rows selected.
SYS@db12c1> !grep -i inflection /u01/app/oracle/diag/rdbms/db12c/db12c1/trace/db12c1_ora_32529.trc
SYS@db12c1> !grep AP: non-adapt*.trc
AP: Adaptive joins bypassed for table P @ SEL$1 due to join method is hinted
AP: Adaptive joins bypassed for table P @ SEL$1 due to join method is hinted
So it appears that AP will not override valid hints (as evidenced by the lines in the 10053 trace file showing that “Adaptive join bypassed … due to join method is hinted”). Keep in mind though that this is only one test case, so it’s possible that in some circumstances AP could override hints, but now you know what to look for to validate. 🙂
Here are a few more hint related questions:
Q: Does adaptive optimization reduce the need for using hints
A: Maybe. If you are hinting to avoid short comings in the optimizer where it chooses the wrong join method, you might not need to do that any more. Likewise, if you are hinting to force a particular distribution method for PX statements, you may not need to do that any more. It’s certainly a step in the right direction.Q: Can we force dynamic sampling for a statement, regardless of what Oracle thinks it should do?
A: Yes, the DYNAMIC_SAMPLING hint has been available since 9.2.Q: If we had SQL Plan Baseline set for a paticular SQL in version 11g and we were to upgrade to 12c version….would SQL Baseline be used or this adaptive plans are used ?
A: The hints in the Baseline would be used and should reproduce the 11g plan. See the example above where valid hints disable AP.Q: Is there a way to grab a previous plan (good) using the profile technique and assign it to the current statement that changed the exec plan which is bad? So far I have been doing this manually using your scripts.
A: Yes – Profiles are just a collection of hints that get applied to a statement. So they can be used to control plans even if AP is enabled.Q: How does this play with SQL Plan Management.. ?
A: Final plans can be captured and baselines created for them. This feature behaves as expected. Subsequent parses will try to reproduce final plan (using hints in the baseline if necessary).
So that’s it for the hint related questions. The final post in this series will cover the remainder of the questions.
Last week I did a webinar on 12c Adaptive Optimization. The talk was recorded. The slides are here: 12c Adaptive Optimization V2 PDF. The recording can be found here: 12c Adaptive Optimization Recording. There were a number of follow up questions and emails so I thought I’d summarize here. Since there were so many questions, (I guess I must not have done that good of a job of explaining how it works) I will break them up into 2 or 3 posts. So for this first one I will just cut and paste from a couple of email follow ups.
Here’s the first question(s):
Hi Kerry,
I followed your webinar today, and I have two questions about it.
First, what will happened if the plan changed during the fetch operation ?
Is it possible ? If then, how does it know which rows has been already fetched ?Second question is more a practical question. Indeed, in the examples you showed, it uses basic queries, but in the case you have an execution plan with more than hundreds of operation, and if during the execution an adaptive plan is decided with changes in join method, the plan can change a lot.
If we want to identify the step that will modify the plan, do we have to identify it as the step just before the statistic collector op, or will it be more complex to identify ?Thanks in advance for you answers
And here’s my Answer(s):
Hi
I’ll have to find a little time to test the prepare, open, fetch stuff to verify where the initial rows are actually retrieved, but if I had to guess it would be on the first fetch call, regardless of how many records the fetch requests. Could also be on the open though. It’s interesting to see how the optimizer comes up with the inflection point by the way (although I don’t know enough about the internal algorithm to know exactly what they are doing – but it’s clear they are guessing by splitting the difference ). But here’s a little output from a wolfgang (10053) trace file.
SYS@db12c1> !grep inflection adaptive.trc
Searching for inflection point (join #1) between 0.00 and 12.76
AP: Computing costs for inflection point at min value 0.00
DP: Using binary search for inflection point search
DP: Costing Nested Loops Join for inflection point at card 0.00
DP: Costing Hash Join for inflection point at card 0.00
AP: Computing costs for inflection point at max value 12.76
DP: Costing Nested Loops Join for inflection point at card 12.76
DP: Costing Hash Join for inflection point at card 12.76
AP: Searching for inflection point at value 1.00
DP: Costing Nested Loops Join for inflection point at card 6.38
DP: Costing Hash Join for inflection point at card 6.38
AP: Searching for inflection point at value 6.38
DP: Costing Nested Loops Join for inflection point at card 3.19
DP: Costing Hash Join for inflection point at card 3.19
AP: Searching for inflection point at value 3.19
DP: Costing Nested Loops Join for inflection point at card 4.78
DP: Costing Hash Join for inflection point at card 4.78
AP: Searching for inflection point at value 4.78
DP: Costing Nested Loops Join for inflection point at card 5.58
DP: Costing Hash Join for inflection point at card 5.58
DP: Costing Nested Loops Join for inflection point at card 5.58
DP: Found point of inflection for NLJ vs. HJ: card = 5.58
On the identification of what’s going on in more complicated plans, the general pattern appears to be like this:
Hash Join
NL Join
Statistics CollectorRegardless of whether the final plan would be to use HJ or NL. In some cases the NL is abandoned, in other cases the HJ is abandoned. (by the way, the optimization only appears to kick in on steps where the default plan would use a NL)
* Note that I was wrong in my assertion that the optimization only kicks in for NLJ steps as pointed out by Stephan in the comments section. It can kick in on HJ steps as well, although they don’t appear nearly as often. 🙂
So anyway, a NL would look like this:
- Hash Join NL Join - Statistics COllectorAnd a HJ like this:
Hash Join - NL Join - Statistics CollectorHere’s an example of a more complex plan – in this case a couple of hash joins are discarded in favor of NL.
SYS@db12c1> @dplan_adaptive
Enter value for sql_id: 95stx63r9dc34
Enter value for child_no: 1
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 95stx63r9dc34, child number 1
-------------------------------------
select /* test dp2c6pq28u5jr */ count(*), sum(blocks) FROM dba_segments
where OWNER = 'XDB' and TABLESPACE_NAME = 'SYSAUX'
Plan hash value: 1481365994
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1441 (100)| |
| 1 | SORT AGGREGATE | | 1 | 104 | | |
| 2 | VIEW | SYS_DBA_SEGS | 9 | 936 | 1441 (1)| 00:00:01 |
| 3 | UNION-ALL | | | | | |
| 4 | NESTED LOOPS | | 6 | 852 | 1356 (1)| 00:00:01 |
| 5 | NESTED LOOPS | | 6 | 810 | 1356 (1)| 00:00:01 |
| * 6 | HASH JOIN | | 67 | 6767 | 1350 (1)| 00:00:01 |
| * 7 | FILTER | | | | | |
| * 8 | HASH JOIN RIGHT OUTER | | 278 | 11954 | 89 (0)| 00:00:01 |
| 9 | TABLE ACCESS STORAGE FULL | USER$ | 71 | 1278 | 3 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 19743 | 482K| 86 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 11 | 1 (0)| 00:00:01 |
| * 12 | INDEX UNIQUE SCAN | I_TS1 | 1 | | 0 (0)| |
| 13 | TABLE ACCESS STORAGE FULL | OBJ$ | 19743 | 269K| 85 (0)| 00:00:01 |
| 14 | VIEW | SYS_OBJECTS | 4731 | 267K| 1261 (1)| 00:00:01 |
| 15 | UNION-ALL | | | | | |
| * 16 | TABLE ACCESS STORAGE FULL | TAB$ | 1533 | 33726 | 312 (0)| 00:00:01 |
| 17 | TABLE ACCESS STORAGE FULL | TABPART$ | 262 | 4192 | 5 (0)| 00:00:01 |
| 18 | TABLE ACCESS STORAGE FULL | CLU$ | 10 | 140 | 312 (0)| 00:00:01 |
| * 19 | TABLE ACCESS STORAGE FULL | IND$ | 2164 | 41116 | 312 (0)| 00:00:01 |
| 20 | TABLE ACCESS STORAGE FULL | INDPART$ | 194 | 3104 | 4 (0)| 00:00:01 |
| * 21 | TABLE ACCESS STORAGE FULL | LOB$ | 512 | 10752 | 309 (0)| 00:00:01 |
| 22 | TABLE ACCESS STORAGE FULL | TABSUBPART$ | 32 | 480 | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS STORAGE FULL | INDSUBPART$ | 1 | 52 | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS STORAGE FULL | LOBFRAG$ | 23 | 414 | 2 (0)| 00:00:01 |
| * 25 | TABLE ACCESS CLUSTER | SEG$ | 1 | 34 | 1 (0)| 00:00:01 |
| * 26 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| |
| * 27 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| |
| * 28 | FILTER | | | | | |
| * 29 | HASH JOIN RIGHT OUTER | | 3 | 405 | 85 (0)| 00:00:01 |
| 30 | TABLE ACCESS STORAGE FULL | USER$ | 71 | 1278 | 3 (0)| 00:00:01 |
| 31 | VIEW | VW_JF_SET$A8769BAB | 246 | 28782 | 82 (0)| 00:00:01 |
| 32 | UNION-ALL | | | | | |
| 33 | NESTED LOOPS | | 4 | 272 | 33 (0)| 00:00:01 |
|- * 34 | HASH JOIN | | 4 | 244 | 33 (0)| 00:00:01 |
| 35 | NESTED LOOPS | | 4 | 244 | 33 (0)| 00:00:01 |
|- 36 | STATISTICS COLLECTOR | | | | | |
| 37 | NESTED LOOPS | | 36 | 1044 | 3 (0)| 00:00:01 |
| 38 | TABLE ACCESS BY INDEX ROWID| TS$ | 1 | 11 | 1 (0)| 00:00:01 |
| * 39 | INDEX UNIQUE SCAN | I_TS1 | 1 | | 0 (0)| |
| * 40 | TABLE ACCESS STORAGE FULL | UNDO$ | 36 | 648 | 2 (0)| 00:00:01 |
| * 41 | TABLE ACCESS CLUSTER | SEG$ | 1 | 32 | 1 (0)| 00:00:01 |
| * 42 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 1 | | 0 (0)| |
|- * 43 | TABLE ACCESS STORAGE FULL | SEG$ | 1 | 32 | 1 (0)| 00:00:01 |
| * 44 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| |
|- * 45 | HASH JOIN | | 241 | 13255 | 25 (0)| 00:00:01 |
| 46 | NESTED LOOPS | | 241 | 13255 | 25 (0)| 00:00:01 |
|- 47 | STATISTICS COLLECTOR | | | | | |
| 48 | NESTED LOOPS | | 5 | 90 | 2 (0)| 00:00:01 |
| 49 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 11 | 1 (0)| 00:00:01 |
| * 50 | INDEX UNIQUE SCAN | I_TS1 | 1 | | 0 (0)| |
| 51 | INDEX FULL SCAN | I_FILE2 | 5 | 35 | 1 (0)| 00:00:01 |
| * 52 | TABLE ACCESS CLUSTER | SEG$ | 48 | 1776 | 5 (0)| 00:00:01 |
| * 53 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 1 | | 2 (0)| 00:00:01 |
|- * 54 | TABLE ACCESS STORAGE FULL | SEG$ | 48 | 1776 | 5 (0)| 00:00:01 |
|- * 55 | HASH JOIN | | 1 | 55 | 25 (0)| 00:00:01 |
| 56 | NESTED LOOPS | | 1 | 55 | 25 (0)| 00:00:01 |
|- 57 | STATISTICS COLLECTOR | | | | | |
| 58 | NESTED LOOPS | | 5 | 90 | 2 (0)| 00:00:01 |
| 59 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 11 | 1 (0)| 00:00:01 |
| * 60 | INDEX UNIQUE SCAN | I_TS1 | 1 | | 0 (0)| |
| 61 | INDEX FULL SCAN | I_FILE2 | 5 | 35 | 1 (0)| 00:00:01 |
| * 62 | TABLE ACCESS CLUSTER | SEG$ | 1 | 37 | 5 (0)| 00:00:01 |
| * 63 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 1 | | 2 (0)| 00:00:01 |
|- * 64 | TABLE ACCESS STORAGE FULL | SEG$ | 1 | 37 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("O"."OBJ#"="SO"."OBJECT_ID" AND "O"."TYPE#"="SO"."OBJECT_TYPE_ID")
7 - filter(NVL("U"."NAME",'SYS')='XDB')
8 - access("O"."OWNER#"="U"."USER#")
12 - access("TS"."NAME"='SYSAUX')
16 - filter(BITAND("T"."PROPERTY",1024)=0)
19 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR
"I"."TYPE#"=7 OR "I"."TYPE#"=8 OR "I"."TYPE#"=9))
21 - filter((BITAND("L"."PROPERTY",64)=0 OR BITAND("L"."PROPERTY",128)=128))
25 - filter("S"."TYPE#"="SO"."SEGMENT_TYPE_ID")
26 - access("S"."TS#"="TS"."TS#" AND "S"."FILE#"="SO"."HEADER_FILE" AND
"S"."BLOCK#"="SO"."HEADER_BLOCK")
filter("S"."TS#"="SO"."TS_NUMBER")
27 - access("S"."TS#"="F"."TS#" AND "S"."FILE#"="F"."RELFILE#")
28 - filter(NVL("U"."NAME",'SYS')='XDB')
29 - access("ITEM_1"="U"."USER#")
34 - access("S"."TS#"="TS"."TS#" AND "S"."TS#"="UN"."TS#" AND "S"."BLOCK#"="UN"."BLOCK#" AND
"S"."FILE#"="UN"."FILE#")
39 - access("TS"."NAME"='SYSAUX')
40 - storage("UN"."STATUS$"<>1)
filter("UN"."STATUS$"<>1)
41 - filter(("S"."TYPE#"=1 OR "S"."TYPE#"=10))
42 - access("S"."TS#"="UN"."TS#" AND "S"."FILE#"="UN"."FILE#" AND "S"."BLOCK#"="UN"."BLOCK#")
filter("S"."TS#"="TS"."TS#")
43 - filter(("S"."TYPE#"=1 OR "S"."TYPE#"=10))
44 - access("UN"."TS#"="F"."TS#" AND "UN"."FILE#"="F"."RELFILE#")
45 - access("S"."FILE#"="F"."RELFILE#" AND "S"."TS#"="F"."TS#" AND "S"."TS#"="TS"."TS#")
50 - access("TS"."NAME"='SYSAUX')
52 - filter(("S"."TYPE#"<>6 AND "S"."TYPE#"<>5 AND "S"."TYPE#"<>8 AND "S"."TYPE#"<>10 AND
"S"."TYPE#"<>11 AND "S"."TYPE#"<>1))
53 - access("S"."TS#"="TS"."TS#" AND "S"."FILE#"="F"."RELFILE#")
filter("S"."TS#"="F"."TS#")
54 - filter(("S"."TYPE#"<>6 AND "S"."TYPE#"<>5 AND "S"."TYPE#"<>8 AND "S"."TYPE#"<>10 AND
"S"."TYPE#"<>11 AND "S"."TYPE#"<>1))
55 - access("S"."FILE#"="F"."RELFILE#" AND "S"."TS#"="F"."TS#" AND "S"."TS#"="TS"."TS#")
60 - access("TS"."NAME"='SYSAUX')
62 - filter("S"."TYPE#"=11)
63 - access("S"."TS#"="TS"."TS#" AND "S"."FILE#"="F"."RELFILE#")
filter("S"."TS#"="F"."TS#")
64 - filter("S"."TYPE#"=11)
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
Hope that helps.
Kerry
The second email:
Thanks, Kerry!
So as i understand, just one execution can now create several child cursors with different final plans? (As many collectors there are in the plan?)
They will have different plan hash values?
And how other sessions will choose child for them during execution which creates many child cursors? Especially interesting, how we will analyze such plans through AWR if statistics will be splitted between several plan hash values…
And I said:
No – only one cursor is created. It can have multiple adaptations – i.e. there may be multiple places where a decision between NL and HJ are made – but in the end it decides on 1 plan ands that’s it. A new cursor will only be created if something more normal triggers a new cursor (adaptive cursor sharing, optimizer environment changes, cardinality feedback kicks in, etc…)
Kerry
That’s it for now. In Part 2 I’ll address some questions regarding interaction with hints.
I heard JP Dijcks speak at RMOUG in 2012 about a new parameter that would show up in 12c called parallel_degree_level. It’s basically a knob that you can turn to dial up (or down) the calculated DOP when setting parallel_degree_policy=auto. Early on (11.2.0.1) auto DOP seemed to vastly overestimate what the DOP should be. In a later version (11.2.0.3) it seems to often underestimate what the DOP should be. I’ve said in the past that I thought auto DOP was too hard to control and thus too scary for production systems. I’ve also said that I thought auto DOP was the wave of the future. And I think this parameter alone may make it possible to use this feature in production because it gives us the ability to dial in the level of parallelism that works for our system. So here’s a quick demo:
SYS@db12c1> @parms
Enter value for parameter: parallel_degree
Enter value for isset:
Enter value for show_hidden:
NAME VALUE ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
parallel_degree_level 100 TRUE FALSE FALSE
parallel_degree_limit 16 FALSE FALSE TRUE
parallel_degree_policy AUTO FALSE TRUE TRUE
3 rows selected.
Elapsed: 00:00:00.05
SYS@db12c1> alter session set parallel_degree_policy=auto;
Session altered.
Elapsed: 00:00:00.00
SYS@db12c1> select count(*) from kso.TT_CLUSTER_ONAME;
COUNT(*)
------------
79429632
1 row selected.
Elapsed: 00:00:01.96
SYS@db12c1> @x
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID apvrg0vpxxw8k, child number 2
-------------------------------------
select count(*) from kso.TT_CLUSTER_ONAME
Plan hash value: 2036413816
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | PX COORDINATOR | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 |
| 4 | SORT AGGREGATE | | 1 |
| 5 | PX BLOCK ITERATOR | | 79M|
|* 6 | TABLE ACCESS STORAGE FULL| TT_CLUSTER_ONAME | 79M|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 14
- parallel scans affinitized
31 rows selected.
Elapsed: 00:00:00.02
SYS@db12c1> alter session set parallel_degree_level=10;
Session altered.
Elapsed: 00:00:00.01
SYS@db12c1> select count(*) from kso.TT_CLUSTER_ONAME;
COUNT(*)
------------
79429632
1 row selected.
Elapsed: 00:00:19.95
SYS@db12c1> @x
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID apvrg0vpxxw8k, child number 4
-------------------------------------
select count(*) from kso.TT_CLUSTER_ONAME
Plan hash value: 2036413816
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 174K(100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 79M| 174K (1)| 00:00:07 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS STORAGE FULL| TT_CLUSTER_ONAME | 79M| 174K (1)| 00:00:07 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- parallel scans affinitized
31 rows selected.
Elapsed: 00:00:00.09
SYS@db12c1> alter session set parallel_degree_level=100;
Session altered.
Elapsed: 00:00:00.00
SYS@db12c1> select count(*) from kso.TT_CLUSTER_ONAME;
COUNT(*)
------------
79429632
1 row selected.
Elapsed: 00:00:04.07
SYS@db12c1> @x
Enter value for sql_id: apvrg0vpxxw8k
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID apvrg0vpxxw8k, child number 2
-------------------------------------
select count(*) from kso.TT_CLUSTER_ONAME
Plan hash value: 2036413816
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 24875 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 79M| 24875 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS STORAGE FULL| TT_CLUSTER_ONAME | 79M| 24875 (1)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 14
- parallel scans affinitized
31 rows selected.
Elapsed: 00:00:00.09
SYS@db12c1> alter session set parallel_degree_level=200;
Session altered.
SYS@db12c1> select count(*) from kso.TT_CLUSTER_ONAME;
COUNT(*)
----------
79429632
Elapsed: 00:00:00.59
SYS@db12c1> @x
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID apvrg0vpxxw8k, child number 5
-------------------------------------
select count(*) from kso.TT_CLUSTER_ONAME
Plan hash value: 2036413816
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | PX COORDINATOR | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 |
| 4 | SORT AGGREGATE | | 1 |
| 5 | PX BLOCK ITERATOR | | 79M|
|* 6 | TABLE ACCESS STORAGE FULL| TT_CLUSTER_ONAME | 79M|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 16 because of degree limit
- parallel scans affinitized
31 rows selected.
Elapsed: 00:00:00.12
SYS@db12c1> alter session set parallel_degree_limit=32;
Session altered.
Elapsed: 00:00:00.00
SYS@db12c1> select count(*) from kso.TT_CLUSTER_ONAME;
COUNT(*)
----------
79429632
Elapsed: 00:00:07.53
SYS@db12c1> @x
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID apvrg0vpxxw8k, child number 6
-------------------------------------
select count(*) from kso.TT_CLUSTER_ONAME
Plan hash value: 2036413816
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | PX COORDINATOR | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 |
| 4 | SORT AGGREGATE | | 1 |
| 5 | PX BLOCK ITERATOR | | 79M|
|* 6 | TABLE ACCESS STORAGE FULL| TT_CLUSTER_ONAME | 79M|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 28
- parallel scans affinitized
31 rows selected.
Elapsed: 00:00:00.06
So as you can see, parallel_degree_level is basically a percentage. The default is 100 and setting it to a value of 10 decreases the calculated value to roughly 10% while increasing it to 200 doubles the calculated DOP.
So just to reiterate, the auto DOP calculations have gotten progressively better over the last couple of years, but I think the simple addition of this new parameter makes it a much more palatable option.