Kerry Osborne's Oracle Blog

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.

My favorite new Oracle Database 12c feature is the SQL Translation Framework. The feature grew out of SQL Developer’s ability to translate SQL from non-Oracle RDBMS’s. For example, there is a pre-built Sybase ASE translation package that is designed to translate the Sybase dialect of SQL into Oracle SQL dialect. So that’s what the feature is designed for. But the developers decided to move it to the database and to allow us to write our own translations which opens up a whole world of possibilities.

The first thought that occurred to me when I saw this feature listed in the 12c New Features doc, was that I might be able to use it to fix badly written SQL behind the scenes. I’ve written and talked quite a bit about using hint based mechanisms (Outlines, SQL Profiles, Baselines, and SQL Patches) to alter execution plans without having to change the code. Those technique work great most of the time, but there are cases where hints alone can’t fix the problem. In some cases it is necessary to change the SQL statement text to get the desired results. And the SQL Translation Framework gives us the tool kit we need to do just that. And by the way, although I do have a tendency to use Oracle features for purposes for which they were not originally intended, in this case, I think the developers knew full well that the features could be used to address performance issues by re-writing SQL. As proof, here is a snippet from the 12c Release 1 Migration guide.

In addition to translating non-Oracle SQL statements, the SQL Translation Framework can also be used to substitute an Oracle SQL statement with another Oracle statement to address a semantic or a performance issue. In this way, you can address an application issue without patching the client application.

So let’s dive in. There are two main components to the framework. The first is a pl/sql package to programmatically translate code (also called the Translator in the docs). The second is a set of maps for individual SQL statements (this is called a SQL Translation Profile). There are a couple of requirements to use this feature.

1. You must create a SQL Translation Profile (using dbms_sql_translator.create_profile)
2. You must assign a session to use the Translation Profile (generally with an alter session command)
3. You must set the 10601 system event

While the Translator Profile is required, it does not have to be assigned a translator. In other words, you can map individual statements without writing a PL/SQL package. Of course if you have a system that has a lot of problems caused by the same coding pattern, you could potentially use the framework to rewrite those statements on the fly.

Here’s a quick example for a simple case of mapping individual statements.

SYS@LAB1211> exec dbms_sql_translator.create_profile('FOO');

PL/SQL procedure successfully completed.

SYS@LAB1211> select object_name, object_type from dba_objects where object_name like 'FOO';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
FOO                            SQL TRANSLATION PROFILE

SYS@LAB1211> exec dbms_sql_translator.register_sql_translation('FOO','select count(*) from hr.countries','select count(*) from hr.jobs');

PL/SQL procedure successfully completed.

SYS@LAB1211> exec dbms_sql_translator.register_sql_translation('FOO','select count(*) from countries','select count(*) from jobs');

PL/SQL procedure successfully completed.

SYS@LAB1211> exec dbms_sql_translator.register_sql_translation('FOO','select 1 from hr.countries','select count(*) from hr.countries');

PL/SQL procedure successfully completed.

SYS@LAB1211> grant all on sql translation profile foo to hr;

Grant succeeded.

SYS@LAB1211> alter session set sql_translation_profile = FOO;

Session altered.

SYS@LAB1211> alter session set events = '10601 trace name context forever, level 32';

Session altered.

SYS@LAB1211> set echo on
SYS@LAB1211> select count(*) from hr.countries;

  COUNT(*)
----------
        19

SYS@LAB1211> select /*+ fix_wrong_results */ count(*) from hr.countries;

  COUNT(*)
----------
        25

SYS@LAB1211> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  aaajpnhn25nza, child number 0
-------------------------------------
select /*+ fix_wrong_results */ count(*) from hr.countries

Plan hash value: 1399856367

----------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |       |     1 (100)|          |
|   1 |  SORT AGGREGATE  |                 |     1 |            |          |
|   2 |   INDEX FULL SCAN| COUNTRY_C_ID_PK |    25 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------


14 rows selected.

SYS@LAB1211> select count(*) from hr.countries;

  COUNT(*)
----------
        19

SYS@LAB1211> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  c95vwg4jwqqfd, child number 0
-------------------------------------
select count(*) from hr.jobs

Plan hash value: 3870222678

-----------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Cost (%CPU)|
-----------------------------------------------------------
|   0 | SELECT STATEMENT |           |       |     1 (100)|
|   1 |  SORT AGGREGATE  |           |     1 |            |
|   2 |   INDEX FULL SCAN| JOB_ID_PK |    19 |     0   (0)|
-----------------------------------------------------------


14 rows selected.

Continue reading ‘SQL Translation Framework’ »

Updated 7/7/13: Well I’m a doofus! This is not a generic problem. It is a bug but only happens when using a specific new feature I was playing with on my 12.1 database (SQL Translation Framework). No need to worry about this unless using that feature. (thanks to Stefen for pointing this out) So you probably don’t need to read this at all. The comments might be worth looking at though. 🙂

=========================================================

Shoot! SQL_ID calculation is different between 11.2 and 12.1. This is a bummer because we’ve gotten used to being able to go back and forth between versions to verify plans after upgrading to 11g for example. It was also convenient to be able to track changes in performance statistics before and after an upgrade. Fortunately there is a work around. The old_hash_value column has been carried through to 12c. See here:

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 5 18:51:43 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options


INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
LAB1123          26-JUN-2013 19:02 05-JUL-2013 18:51    8.99     776979

SYS@LAB1123> select sql_id, hash_value, old_hash_value, plan_hash_value, sql_text from v$sql where sql_text = 'select 1 from dual';

SQL_ID        HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE SQL_TEXT
------------- ---------- -------------- --------------- ----------------------------------------
520mkxqpf15q8 2866845384      271604965      1388734953 select 1 from dual

========================================================

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 18:33:12 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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
LAB1211          02-JUL-2013 10:21 05-JUL-2013 18:33    3.34     288715

SYS@LAB1211> select sql_id, hash_value, old_hash_value, plan_hash_value, sql_text from v$sql where sql_text = 'select 1 from dual';

SQL_ID        HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE SQL_TEXT
------------- ---------- -------------- --------------- ----------------------------------------
3zcn52u5tvfqh 2342370000      271604965      1388734953 select 1 from dual

So as you can see, the sql_id and hash_value have changed between versions but the old_hash_value remains consistent. It also appears that the plan_hash_value calculation is unchanged, at least for simple plans. Anyway, a little reworking of some scripts should allow us to do the same sorts of things we’ve done in the past, albeit with a little more effort. Maybe Tanel will do us all a favor and write a function to calculate the old sql_id in 12c. That would make it a little easier. 🙂