Archive for the ‘Oracle’ Category.

Exadata Zone Maps

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.

The AVOID_FULL hint

I saw this very odd statement on an SAP system last week.

         SELECT /*+ AVOID_FULL ("/bic/xxx") */ * FROM "/BIC/XXX" WHERE "/BIC/XXX"=:A0

I had never seen that hint before so I thought I’d do a little investigation. First I did a quick check on a test case to see if it worked.

SYS@DEMO1> select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=234657;
 
  COUNT(*)
----------
        32
 
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  adpsagc1zb5fj, child number 0
-------------------------------------
select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=234657
 
Plan hash value: 1638045392
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |           |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| SKEW_COL1 |    32 |   160 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("COL1"=234657)
 
 
19 rows selected.

So in my first test case it clearly caused the optimizer to avoid a full scan on my table. So I then started wondering how long has this hint been around and so I looked in v$sql_hint, but to my surprise it wasn’t there.

SYS@DEMO1> select name, version, inverse from v$sql_hint where upper(name) like '%AVOID%';
 
no rows selected
 
SYS@DEMO1> select name, version, inverse from v$sql_hint where upper(name) like '%FULL%';
 
NAME                                               VERSION    INVERSE
-------------------------------------------------- ---------- ----------------------------------------------------------------
FULL                                               8.1.0
NATIVE_FULL_OUTER_JOIN                             10.2.0.3   NO_NATIVE_FULL_OUTER_JOIN
NO_NATIVE_FULL_OUTER_JOIN                          10.2.0.3   NATIVE_FULL_OUTER_JOIN
FULL_OUTER_JOIN_TO_OUTER                           11.2.0.3   NO_FULL_OUTER_JOIN_TO_OUTER
NO_FULL_OUTER_JOIN_TO_OUTER                        11.2.0.3   FULL_OUTER_JOIN_TO_OUTER

The AVOID_FULL hint is not present in v$sql_hint. But wait, maybe it’s one of those top secret hidden hints like PARALLEL, which is a valid hint but doesn’t show up in v$sql_hint.

 
SYS@DEMO1> select name, version, inverse from v$sql_hint where name like '%PARALLEL%';
 
NAME                                               VERSION    INVERSE
-------------------------------------------------- ---------- ----------------------------------------------------------------
SYS_PARALLEL_TXN                                   8.1.6
NOPARALLEL                                         8.1.0      SHARED
NO_PARALLEL                                        10.1.0.3   SHARED
PARALLEL_INDEX                                     8.1.0      NO_PARALLEL_INDEX
NO_PARALLEL_INDEX                                  8.1.0      PARALLEL_INDEX
 
SYS@DEMO1> -- hmmm there is no PARALLEL hint listed 
SYS@DEMO1> -- but it clearly works
SYS@DEMO1> --
SYS@DEMO1> select count(*) from kso.skew2;
 
  COUNT(*)
----------
  32000004
 
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  56v09mkbstyaa, child number 0
-------------------------------------
select count(*) from kso.skew2
 
Plan hash value: 4220890033
 
----------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       | 89256 (100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |            |          |
|   2 |   TABLE ACCESS STORAGE FULL| SKEW2 |    32M| 89256   (1)| 00:11:38 |
----------------------------------------------------------------------------
 
 
14 rows selected.
 
SYS@DEMO1> select /*+ parallel 2 */ count(*) from kso.skew2;
 
  COUNT(*)
----------
  32000004
 
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9rgx66dnd21zj, child number 1
-------------------------------------
select /*+ parallel 2 */ count(*) from kso.skew2
 
Plan hash value: 2117817910
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |  3095 (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         |          |    32M|  3095   (1)| 00:00:25 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SKEW2    |    32M|  3095   (1)| 00:00:25 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - storage(:Z>=:Z AND :Z<=:Z)
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 32 because of degree limit
 
 
27 rows selected.

So the PARALLEL hint is not listed but it clearly is a valid hint (even though the SHARED hint is documented as the inverse of the NOPARALLEL hint). So maybe this AVOID_FULL hint is one of those corner cases. So I did some more testing and found a special case where the hint didn’t work as I expected. When I set parallel_degree_policy to LIMITED and decorated my table with a degree setting of DEFAULT I got this behavior.

SYS@DEMO1> select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=1;
 
  COUNT(*)
----------
   3199971
 
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  591tybw6c8vth, child number 0
-------------------------------------
select /*+ avoid_full(a) */ count(*) from kso.skew a where col1=1
 
Plan hash value: 578366071
 
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |       |  1548 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |          |     1 |     5 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000 |     1 |     5 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |          |     1 |     5 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |          |  3023K|    14M|  1548   (1)| 00:00:13 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SKEW     |  3023K|    14M|  1548   (1)| 00:00:13 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - storage(:Z>=:Z AND :Z<=:Z AND "COL1"=1)
       filter("COL1"=1)
 
 
24 rows selected.

The optimizer picked a full scan, despite the hint to the contrary. My next thought was to try Wolfganging the statement (generating 10053 trace) to see if maybe the hint syntax was slightly off and so it was silently ignored. (I wish there was a setting to throw an error when an invalid hint is specified in a statement by the way, but as far as I know there isn’t such a switch). Anyway, here’s the trace bit.

SYS@DEMO1> @wolfgang
 
VALUE
----------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/demo/DEMO1/trace/DEMO1_ora_16420.trc
 
 
Session altered.
 
SYS@DEMO1> select /*+ avoid_full(a) 2 */ count(*) from kso.skew2 a where col1 is not null;
 
  COUNT(*)
----------
  32000003
 
SYS@DEMO1> !vi /u01/app/oracle/diag/rdbms/demo/DEMO1/trace/DEMO1_ora_16420.trc
 
. . .
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "KSO"."SKEW2" "A" WHERE "A"."COL1" IS NOT NULL
. . .
Dumping Hints
=============
====================== END SQL Statement Dump ======================

Not too helpful, the trace didn’t say whether the hint was valid or not. In fact, it didn’t even mention it at all. Strange. So then I thought I’d go back to the production system and see what the plan looked like there. Sure enough, the hint wasn’t working on the production system either – the plan was a full scan on the table.

So the bottom line is there is this new (to me) hint that has been around for some time (I don’t know how long because it’s not documented) that seems to work sometimes but not all the time (but since it’s not documented I don’t know the syntax – so I may just be messing it up). Anyway, due to this erratic behavior you should definitely use it with care. :)

Happy April Fools Day!

Note: I actually saw this statement in a production system.

Hotsos 2014 Presentation

Just a quick note that I’ve added a zip file with my presentation at Hotsos yesterday which includes the scripts that I demoed. You can find it on the Whitepapers/Presentations page. Or you can just click this link:

SQL Gone Bad – But Plan Not Changed!

Does parallel_degree_limit work with parallel_degree_policy=manual?

The Oracle 11g parameter parallel_degree_limit is designed to put a cap on the maximum DOP for a statement.

The Oracle Database Reference 11g Release 2 (11.2) says this:

A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

But that’s not entirely correct because it turns out you can enable auto DOP via a hint. The PARALLEL hint without a valid object on which to act will enable auto DOP for the statement. Here is an example:

SYS@DEMO1> @parms
Enter value for parameter: parallel_degree
Enter value for isset: 
Enter value for show_hidden: 
 
NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
-------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
parallel_degree_limit                              CPU                                                                    FALSE    TRUE       TRUE
parallel_degree_policy                             MANUAL                                                                 FALSE    TRUE       TRUE
 
Elapsed: 00:00:00.00
SYS@DEMO1> select /*+ parallel */ count(*) from eo00.SALES_1M2;
 
  COUNT(*)
----------
   1000000
 
Elapsed: 00:00:00.02
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  aszs6rg3fttrt, child number 2
-------------------------------------
select /*+ parallel */ count(*) from eo00.SALES_1M2
 
Plan hash value: 3155295854
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |       |    25 (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         |           |   982K|    25   (4)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SALES_1M2 |   982K|    25   (4)| 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 7
 
 
28 rows selected.
 
Elapsed: 00:00:00.02

So as you can see, the hint enabled auto DOP, even though the parallel_degree_policy is set to manual. So let’s see if the parallel_degree_limit will kick in for such a case.

SYS@DEMO1> alter session set parallel_degree_limit=4;
 
Session altered.
 
Elapsed: 00:00:00.00
SYS@DEMO1> select /*+ parallel */ count(*) from eo00.SALES_1M2;
 
  COUNT(*)
----------
   1000000
 
Elapsed: 00:00:00.02
SYS@DEMO1> @x
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  aszs6rg3fttrt, child number 1
-------------------------------------
select /*+ parallel */ count(*) from eo00.SALES_1M2
 
Plan hash value: 3155295854
 
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |       |    44 (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         |           |   982K|    44   (3)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SALES_1M2 |   982K|    44   (3)| 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 4 because of degree limit
 
 
28 rows selected.
 
Elapsed: 00:00:00.02

Yes it does. So what does it mean? Well for one thing the documentation (and some presentations I have seen recently) are slightly wrong. But the real moral of the story is that just because you have parallel_degree_policy set to manual, doesn’t mean you are not using auto DOP.

Does this mean that the other features enabled by auto DOP (parallel statement queuing and in-memory parallel) will kick in on these kinds of statements as well? I’ll leave that as an exercise for the reader.

12c Adaptive Optimization – Part 3

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:

    12c Adaptive Optimization – Part 1.
    12c Adaptive Optimization – Part 2 (Hints).

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 Expansion

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

    connect_pdb.sql
    whoami_pdb.sql

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. :)