Loading...

Partition-wise operations are not something new. I do not remember when they were introduced, but at that time the release number was still a single digit. Anyway, the aim of this post is not to describe the basics, but only to describe what is new in that area in 12c and 18c.

The new features can be grouped in three categories:

  • Partition-wise GROUP BY enhancements available as of version 12.2
  • Partition-wise DISTINCT enhancements available as of version 12.2
  • Partition-wise windowing functions enhancements available as of version 18.1

Before looking at the new features, here are the SQL statements I executed to create a partitioned table that I use through the examples. You can download the script here.

CREATE TABLE t (
  id NUMBER,
  d1 DATE,
  n1 NUMBER,
  n2 NUMBER,
  n3 NUMBER,
  pad VARCHAR2(4000),
  CONSTRAINT t_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (d1)
SUBPARTITION BY LIST (n1)
SUBPARTITION TEMPLATE (
  SUBPARTITION sp_1 VALUES (1),
  SUBPARTITION sp_2 VALUES (2),
  SUBPARTITION sp_3 VALUES (3),
  SUBPARTITION sp_4 VALUES (4)
)(
  PARTITION t_q1_2018 VALUES LESS THAN (to_date('2018-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
  PARTITION t_q2_2018 VALUES LESS THAN (to_date('2018-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
  PARTITION t_q3_2018 VALUES LESS THAN (to_date('2018-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')),
  PARTITION t_q4_2018 VALUES LESS THAN (to_date('2019-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
);

INSERT INTO t
SELECT rownum AS id,
       trunc(to_date('2018-01-01','YYYY-MM-DD')+rownum/27.4) AS d1,
       1+mod(rownum,4) AS n1,
       rownum AS n2,
       rownum AS n3,
       rpad('*',100,'*') AS pad
FROM dual
CONNECT BY level  user,
    tabname          => 'T'
  );
END;
/

Partition-wise GROUP BY (12.2)

The enhancements in this section are minimal. In fact, there are only two new hints: USE_PARTITION_WISE_GBY and NO_USE_PARTITION_WISE_GBY. You can use the former to enable the feature in case the query optimizer does not select it, and with the latter, you can do the opposite.

The following examples show, for the query SELECT n1, d1, sum(n2) FROM t GROUP BY n1, d1, serial/parallel execution plans with/without the partition-wise optimization. In the serial execution plans notice the placement of the HASH GROUP BY operation. In the parallel execution plans, notice the reduction of involved processes and data distribution.

serial
-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  HASH GROUP BY       |      |
|   2 |   PARTITION RANGE ALL|      |
|   3 |    PARTITION LIST ALL|      |
|   4 |     TABLE ACCESS FULL| T    |
-------------------------------------
serial+pwise
-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  PARTITION RANGE ALL |      |
|   2 |   PARTITION LIST ALL |      |
|   3 |    HASH GROUP BY     |      |
|   4 |     TABLE ACCESS FULL| T    |
-------------------------------------
parallel
--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY         |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH        | :TQ10000 |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY      |          |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T        |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------
parallel+pwise
-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |      |            |
|   1 |  PX COORDINATOR         |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|          |  Q1,00 | PCWC |            |
|   4 |     HASH GROUP BY       |          |  Q1,00 | PCWP |            |
|   5 |      TABLE ACCESS FULL  | T        |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------

Partition-wise DISTINCT (12.2)

The key enhancement of this section is the ability to execute a DISTINCT as a parallel partition-wise operation. In addition, there are two new hints: USE_PARTITION_WISE_DISTINCT and NO_USE_PARTITION_WISE_DISTINCT. You can use the former to enable the feature in case the query optimizer does not select it, and with the latter, you can do the opposite.

The following examples show, for the query SELECT DISTINCT n1, d1 FROM t, serial/parallel execution plans with/without the partition-wise optimization. In the serial execution plans notice the placement of the HASH UNIQUE operation. In the parallel execution plans, notice the reduction of involved processes and data distribution.

serial
-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  HASH UNIQUE         |      |
|   2 |   PARTITION RANGE ALL|      |
|   3 |    PARTITION LIST ALL|      |
|   4 |     TABLE ACCESS FULL| T    |
-------------------------------------
serial+pwise
-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  PARTITION RANGE ALL |      |
|   2 |   PARTITION LIST ALL |      |
|   3 |    HASH UNIQUE       |      |
|   4 |     TABLE ACCESS FULL| T    |
-------------------------------------
parallel
--------------------------------------------------------------------------
| Id  | Operation                | Name     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |        |      |            |
|   1 |  PX COORDINATOR          |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH UNIQUE           |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH        | :TQ10000 |  Q1,00 | P->P | HASH       |
|   6 |       HASH UNIQUE        |          |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   8 |         TABLE ACCESS FULL| T        |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------
parallel+pwise
-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |      |            |
|   1 |  PX COORDINATOR         |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|          |  Q1,00 | PCWC |            |
|   4 |     HASH UNIQUE         |          |  Q1,00 | PCWP |            |
|   5 |      TABLE ACCESS FULL  | T        |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------

Partition-wise Windowing Functions (18.1)

The key enhancement of this section is the ability to execute a windowing function as a parallel partition-wise operation. In addition, there are two new hints: USE_PARTITION_WISE_WIF and NO_USE_PARTITION_WISE_WIF. You can use the former to enable the feature in case the query optimizer does not select it, and with the latter, you can do the opposite.

The following examples show, for the query SELECT n1, d1, avg(n2) OVER (PARTITION BY n1, d1) AS average FROM t, serial/parallel execution plans with/without the partition-wise optimization. Notice that the serial execution plan without the optimization is missing because either I did not correctly understand how to use the NO_USE_PARTITION_WISE_WIF hint (as it too often happens, no documentation about it is provided) or it does not work (bug?) for the serial execution plan. In the parallel execution plans, notice the reduction of involved processes and data distribution.

serial+pwise
-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|   1 |  PARTITION RANGE ALL |      |
|   2 |   PARTITION LIST ALL |      |
|   3 |    WINDOW SORT       |      |
|   4 |     TABLE ACCESS FULL| T    |
-------------------------------------
parallel
-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |      |            |
|   1 |  PX COORDINATOR         |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10001 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    WINDOW SORT          |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE          |          |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10000 |  Q1,00 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T        |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------
parallel+pwise
-------------------------------------------------------------------------
| Id  | Operation               | Name     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |        |      |            |
|   1 |  PX COORDINATOR         |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|          |  Q1,00 | PCWC |            |
|   4 |     WINDOW SORT         |          |  Q1,00 | PCWP |            |
|   5 |      TABLE ACCESS FULL  | T        |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------

All in all, those are good features that can not only make some operation faster, but also reduce the number of involved processes in case the database engine uses parallel execution.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

If you run TKPROF without arguments, you get a complete list of its arguments with a short description for each of them (here the output generated by version 18.1.0):

$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use 'schema.tablename' with 'explain=' option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN.
  print=integer    List only the first 'integer' SQL statements.
  pdbtrace=user/password   Connect to ORACLE to retrieve SQL trace records.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  waits=yes|no     Record summary for any wait events found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of disk reads during parse
    prsqry  number of buffers for consistent read during parse
    prscu   number of buffers for current read during parse
    prsmis  number of misses in library cache during parse
    execnt  number of execute was called
    execpu  cpu time spent executing
    exeela  elapsed time executing
    exedsk  number of disk reads during execute
    exeqry  number of buffers for consistent read during execute
    execu   number of buffers for current read during execute
    exerow  number of rows processed during execute
    exemis  number of library cache misses during execute
    fchcnt  number of times fetch was called
    fchcpu  cpu time spent fetching
    fchela  elapsed time fetching
    fchdsk  number of disk reads during fetch
    fchqry  number of buffers for consistent read during fetch
    fchcu   number of buffers for current read during fetch
    fchrow  number of rows fetched
    userid  userid of user that parsed the cursor

If you carefully check the output, you can notice an argument that does exist only from version 12.2 onward: “pdbtrace”. If I correctly remember, since the introduction of TKPROF in Oracle7, only another time Oracle added a new argument. It was “waits” in Oracle9. Interestingly, the documentation provides no information about it.

So, the question is: what does the new argument do?

Since its name contains “pdb”, one might think that it is related to Multitenant. But, after a number of tests, it does not seem to be related to it. Simply put, “pdbtrace” can be used to process a trace file without requiring direct access to the OS where the trace file is stored. Instead, when “pdbtrace” is specified, the trace file is read through one of the dynamic performance views introduced in version 12.2 to access them (e.g. V$DIAG_TRACE_FILE and V$DIAG_TRACE_FILE_CONTENTS; have a look to this post for some basic information about them).

For example, the following command instructs TKPROF to connect a database instance with the specified user and password, to read the input file DBM1810S_ora_18264.trc (notice that no directory name is necessary; actually, it does not work if you specify it, the output file would be empty), and produce the output file DBM1810S_ora_18264.txt locally.

$ tkprof DBM1810S_ora_18264.trc DBM1810S_ora_18264.txt pdbtrace=chris/secret@pdb1

All in all, except for the name, a good addition to TKPROF.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In the past I already shared with you the scripts I use to download the Oracle Database documentation. The aim of this short post is to reference the scripts I just wrote for 18c.

Happy downloading as well as happy reading!

PS: I also refreshed the scripts for 12.1 and 12.2.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Today I faced a performance problem caused by a bad cardinality estimation involving a CDB view in a 12.1.0.2 multitenant environment. While solving the problem I did a number of observations that I try to summarize in this blog post.

First of all, when checking the execution plan of a query already running for more than two hours, I noticed that, in the execution plan, neither the referenced CDB view nor one of its underlying objects were referenced. The following query (and its execution plan) executed while connect to the CDB illustrates (I also added the 12.2.0.1 output to show you the difference it that area):

12.1.0.2
SQL> EXPLAIN PLAN FOR SELECT * FROM cdb_tables;

SQL> SELECT * FROM table(dbms_xplan.display);

Plan hash value: 1439328272

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          | 20000 |    16M|     1 (100)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 | 20000 |    16M|     1 (100)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|          | 20000 |    16M|     1 (100)| 00:00:01 |     1 |   254 |  Q1,00 | PCWC |            |
|   4 |     FIXED TABLE FULL    | X$CDBVW$ | 20000 |    16M|     1 (100)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------
12.2.0.1
SQL> EXPLAIN PLAN FOR SELECT * FROM cdb_tables;

SQL> SELECT * FROM table(dbms_xplan.display);

Plan hash value: 1043806087

-------------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            | 20000 |    28M|     1 (100)| 00:00:01 |       |       |
|   1 |  PARTITION LIST ALL|            | 20000 |    28M|     1 (100)| 00:00:01 |     1 |     3 |
|   2 |   CONTAINERS FULL  | DBA_TABLES | 20000 |    28M|     1 (100)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------

As you can see, the 12.1.0.2 execution plan doesn’t reference any object related to the CDB_TABLES view or one of its underlying tables. Instead, it uses the generic fixed table X$CDBVW$. Simply put, X$CDBVW$ is a fixed table that gives access to data stored in PDBs. To know more, I advise you to read Laurent Leturgez’s blog post entitled Oracle Database 12c CDB$VIEW function.

In the real query, the CDB view was joined to a number of V$ views. Unfortunately, the query optimizer selected the wrong join method (no surprise, it was a nested loops join instead of a hash join…) and the performance was abysmal. When I saw that the cardinality estimations were wrong, I checked whether the involved objects had statistics. But, because of its particular behavior, the fixed table X$CDBVW$ had no statistics. And, by the way, statistics on it can’t be gathered. If you try, you get the following error:

12.1.0.2
SQL> exec dbms_stats.gather_table_stats('SYS','X$CDBVW$')
BEGIN dbms_stats.gather_table_stats('SYS','X$CDBVW$'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYS"."X$CDBVW$", analyzing the table is not supported
ORA-06512: at "SYS.DBMS_STATS", line 35464
ORA-06512: at line 1
12.2.0.1
SQL> exec dbms_stats.gather_table_stats('SYS','X$CDBVW$')
BEGIN dbms_stats.gather_table_stats('SYS','X$CDBVW$'); END;

*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SYS"."X$CDBVW$", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 36496
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at line 1

As Laurent mentioned in his blog post, the query optimizer uses a default value instead. However, what I noticed is that the estimation wasn’t 10000 rows as he mentioned. In my case it was 30000 rows. The difference is probably due to the version. In fact, he wrote his blog post when only 12.1.0.1 was available, but my customer is using 12.1.0.2. So, I did a couple of test on my own test environment and found out that as of and including 12.1.0.2 the number of rows increases proportionally as the number of open PDBs increases. The following example illustrates:

12.1.0.2 / 12.2.0.1
SQL> SELECT con_id, name, open_mode FROM v$pdbs WHERE open_mode LIKE 'READ%';

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'seed' FOR SELECT * FROM cdb_tables;

SQL> ALTER PLUGGABLE DATABASE test1 OPEN;

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR SELECT * FROM cdb_tables;

SQL> ALTER PLUGGABLE DATABASE test2 OPEN;

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'test2' FOR SELECT * FROM cdb_tables;

SQL> ALTER PLUGGABLE DATABASE test3 OPEN;

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'test3' FOR SELECT * FROM cdb_tables;

SQL> SELECT con_id, name, open_mode FROM v$pdbs WHERE open_mode LIKE 'READ%';

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         4 TEST1                          READ WRITE
         5 TEST2                          READ WRITE
         6 TEST3                          READ WRITE

SQL> SELECT statement_id, cardinality FROM plan_table WHERE id = 0;

STATEMENT_ID                   CARDINALITY
------------------------------ -----------
seed                                 20000
test1                                30000
test2                                40000
test3                                50000

Finally, in the real query, because of the join condition based on the CON_ID column, the query optimizer incorrectly adjusted the number of rows returned through the fixed table X$CDBVW$. That led me doing few tests related to the selectivity estimations related to the CON_ID column. As the following example illustrates, the query optimizer uses a default selectivity of 1% for equality predicates, and 5% for range predicates.

12.1.0.2
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'all' FOR SELECT * FROM cdb_tables;

Explained.

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1439328272

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          | 50000 |    40M|     2 (100)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR         |          |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000 | 50000 |    40M|     2 (100)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|          | 50000 |    40M|     2 (100)| 00:00:01 |     1 |   254 |  Q1,00 | PCWC |            |
|   4 |     FIXED TABLE FULL    | X$CDBVW$ | 50000 |    40M|     2 (100)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'eq' FOR SELECT * FROM cdb_tables WHERE con_id = 0;

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'gt' FOR SELECT * FROM cdb_tables WHERE con_id > 0;

SQL> SELECT statement_id, cardinality FROM plan_table WHERE id = 0;

STATEMENT_ID                   CARDINALITY
------------------------------ -----------
all                                  50000
eq                                     500
gt                                    2500
12.2.0.1
SQL> EXPLAIN PLAN SET STATEMENT_ID = 'all' FOR SELECT * FROM cdb_tables;

Explained.

SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1281079049

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            | 50000 |    42M|     1 (100)| 00:00:01 |       |       |        |      |            |
|   1 |  PX COORDINATOR         |            |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10000   | 50000 |    42M|     1 (100)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX PARTITION LIST ALL|            | 50000 |    42M|     1 (100)| 00:00:01 |     1 |     3 |  Q1,00 | PCWC |            |
|   4 |     CONTAINERS FULL     | DBA_TABLES | 50000 |    42M|     1 (100)| 00:00:01 |       |       |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'eq' FOR SELECT * FROM cdb_tables WHERE con_id = 0;

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'gt' FOR SELECT * FROM cdb_tables WHERE con_id > 0;

SQL> SELECT statement_id, cardinality FROM plan_table WHERE id = 0;

STATEMENT_ID                   CARDINALITY
------------------------------ -----------
all                                  50000
eq                                     500
gt                                    2500

It goes without saying that such estimates are way off. Good estimates should consider the number of open PDBs….

In summary, if you see wrong estimates related to CDB views, don’t be surprised. In fact, the query optimizer bases its estimations on a number of default values.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

At the beginning of December, at the UKOUG Tech17 conference in Birmingham (GB), I presented a comparison of the query optimizers of MySQL 8.0.3 and PostgreSQL 10.1. One of the things I talked about is their ability to handle subqueries. I summarized my findings with the following sentence:

Simple sub-queries that are not correctly optimized were observed.

It goes without saying that such a sentence leaves a lot of questions open. After all, it is just a summary. The aim of this post is to show you which subqueries I tested, and to compare my expectations with the execution plans generated by the query optimizers. In addition, since I’m not limited in time and scope as during a 50-minute presentation, I also discuss how the Oracle Database 12.2 query optimizer handles the same queries.

To check how well a query optimizer handles subqueries, it’s in my opinion sufficient to challenge it with queries that should be obvious (at least for a human being). The type of queries where the response time ratio between a good and a bad execution plan is of several orders of magnitude. If a query optimizer isn’t able to correctly handle such queries, with more complex ones it can only be worse…

For the tests I use two very simple tables:

CREATE TABLE small (u INTEGER NOT NULL, nu INTEGER NOT NULL, n INTEGER NULL, nn INTEGER NOT NULL, p VARCHAR(128) NULL);
CREATE UNIQUE INDEX small_u ON small (u);
CREATE INDEX small_nu ON small (nu);
CREATE INDEX small_n ON small (n);
CREATE INDEX small_nn ON small (nn);
CREATE TABLE large (u INTEGER NOT NULL, nu INTEGER NOT NULL, n INTEGER NULL, nn INTEGER NOT NULL, p VARCHAR(128) NULL);
CREATE UNIQUE INDEX large_u ON large (u);
CREATE INDEX large_nu ON large (nu);
CREATE INDEX large_n ON large (n);
CREATE INDEX large_nn ON large (nn);

The table “small” contains 10 rows; its unique key contains the integer values between 1 and 10. The table “large” contains 1 million rows; its unique key contains the integer values between 1 and 1 million. Note that for both tables the columns “nu” (not unique), “n” (null), and “nn” (not null) contain the same value as the unique key column. The only exception is that the column “n” contains “null” instead of the value “7.” Basically, only the constraints applied to them are different. In addition, the column “p” contains a string of 128 characters that is only present to have tables that aren’t too small (i.e. not very representative).

I considered six types of subqueries:

  • Type A – Scalar subqueries with equality predicate
  • Type B – Scalar subqueries with inequality predicate
  • Type C – Uncorrelated subqueries with either IN or EXISTS
  • Type D – Uncorrelated subqueries with either NOT IN or NOT EXISTS
  • Type E – Correlated subqueries with either IN or EXISTS
  • Type F – Correlated subqueries with either NOT IN or NOT EXISTS

Few notes:

  • For each type I considered two sub-types; the difference between them is given by the position of the tables “small” and “large”
  • I didn’t consider subqueries outside the WHERE clause
  • “IN” could be replaced by either “=ANY” or “=SOME”
  • “NOT IN” could be replaced by “!=ALL”

Type A – Scalar subqueries with equality predicate Subtype A1 – Table “large” in the subquery

A10: SELECT * FROM small WHERE u = (SELECT nu FROM large WHERE u = 6)
A11: SELECT * FROM small WHERE n = (SELECT n FROM large WHERE u = 6)
A12: SELECT * FROM small WHERE n = (SELECT nn FROM large WHERE u = 6)
A13: SELECT * FROM small WHERE nn = (SELECT n FROM large WHERE u = 6)
A14: SELECT * FROM small WHERE nn = (SELECT nn FROM large WHERE u = 6)

The execution plan I expect for these queries carries out the following operations:

  • Access the table “large” through an index scan that returns at most one value. The operation is executed one single time.
  • Access the table “small” through either a table scan or an index scan and return the rows matching the value returned by the previous operation. The operation is executed one single time.

MySQL selects the following execution plans. All of them fulfill the expectations.

A10
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | small | NULL       | const | small_u       | small_u | 4       | const |    1 |   100.00 | NULL  |
|  2 | SUBQUERY    | large | NULL       | const | large_u       | large_u | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
A11/A12
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | small | NULL       | ref   | small_n       | small_n | 5       | const |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | large | NULL       | const | large_u       | large_u | 4       | const |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
A13/A14
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | small | NULL       | ref   | small_nn      | small_nn | 4       | const |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | large | NULL       | const | large_u       | large_u  | 4       | const |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+

Oracle Database selects the following execution plans. All of them fulfill the expectations.

A10
-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |   141 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | SMALL   |     1 |   141 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN           | SMALL_U |     1 |       |     0   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| LARGE   |     1 |    10 |     3   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | LARGE_U |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

   2 - access("U"= (SELECT "NU" FROM "LARGE" "LARGE" WHERE "U"=6))
   4 - access("U"=6)
A11
-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     1 |   141 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SMALL   |     1 |   141 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SMALL_N |     1 |       |     1   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID      | LARGE   |     1 |    10 |     3   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN               | LARGE_U |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

   2 - access("N"= (SELECT "N" FROM "LARGE" "LARGE" WHERE "U"=6))
   4 - access("U"=6)
A12
-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     1 |   141 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SMALL   |     1 |   141 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SMALL_N |     1 |       |     1   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID      | LARGE   |     1 |    10 |     3   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN               | LARGE_U |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

   2 - access("N"= (SELECT "NN" FROM "LARGE" "LARGE" WHERE "U"=6))
   4 - access("U"=6)
A13
------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     1 |   141 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SMALL    |     1 |   141 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SMALL_NN |     1 |       |     1   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID      | LARGE    |     1 |    10 |     3   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN               | LARGE_U  |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

   2 - access("NN"= (SELECT "N" FROM "LARGE" "LARGE" WHERE "U"=6))
   4 - access("U"=6)
A14
------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     1 |   141 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SMALL    |     1 |   141 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SMALL_NN |     1 |       |     1   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID      | LARGE    |     1 |    10 |     3   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN               | LARGE_U  |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

   2 - access("NN"= (SELECT "NN" FROM "LARGE" "LARGE" WHERE "U"=6))
   4 - access("U"=6)

PostgreSQL selects the following execution plans. All of them fulfill the expectations.

A10
 Seq Scan on small  (cost=8.44..9.57 rows=1 width=148)
   Filter: (u = $0)
   InitPlan 1 (returns $0)
     ->  Index Scan using large_u on large  (cost=0.42..8.44 rows=1 width=4)
           Index Cond: (u = 6)
A11/A12
 Seq Scan on small  (cost=8.44..9.57 rows=1 width=148)
   Filter: (n = $0)
   InitPlan 1 (returns $0)
     ->  Index Scan using large_u on large  (cost=0.42..8.44 rows=1 width=4)
           Index Cond: (u = 6)
A13/A14
 Seq Scan on small  (cost=8.44..9.57 rows=1 width=148)
   Filter: (nn = $0)
   InitPlan 1 (returns $0)
     ->  Index Scan using large_u on large  (cost=0.42..8.44 rows=1 width=4)
           Index Cond: (u = 6)
Subtype A2 – Table “small” in the subquery

A20: SELECT * FROM large WHERE u = (SELECT nu FROM small WHERE u = 6)
A21: SELECT * FROM large WHERE n = (SELECT n FROM small WHERE u = 6)
A22: SELECT * FROM large WHERE n = (SELECT nn FROM small WHERE u = 6)
A23: SELECT * FROM large WHERE nn = (SELECT n FROM small WHERE u = 6)
A24: SELECT * FROM large WHERE nn = (SELECT nn FROM small WHERE u = 6)

The execution plan I expect for these queries carries out the following operations:

  • Access the table “small” through either a table scan or an index scan that returns at most one value. The operation is executed one single time.
  • Access the table “large” through an index scan and return the rows matching the value returned by the previous operation. The operation is executed one single time.

MySQL selects the following execution plans. All of them fulfill the expectations.

A20
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | large | NULL       | const | large_u       | large_u | 4       | const |    1 |   100.00 | NULL  |
|  2 | SUBQUERY    | small | NULL       | const | small_u       | small_u | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
A21/A22
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | large | NULL       | ref   | large_n       | large_n | 5       | const |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | small | NULL       | const | small_u       | small_u | 4       | const |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
A23/A24
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | large | NULL       | ref   | large_nn      | large_nn | 4       | const |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | small | NULL       | const | small_u       | small_u  | 4       | const |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+

Oracle Database selects the following execution plans. All of them fulfill the expectations.

A20
-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |   149 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | LARGE   |     1 |   149 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN           | LARGE_U |     1 |       |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| SMALL   |     1 |     6 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | SMALL_U |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

   2 - access("U"= (SELECT "NU" FROM "SMALL" "SMALL" WHERE "U"=6))
   4 - access("U"=6)
A21
-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     1 |   149 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LARGE   |     1 |   149 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | LARGE_N |     1 |       |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID      | SMALL   |     1 |     6 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN               | SMALL_U |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

   2 - access("N"= (SELECT "N" FROM "SMALL" "SMALL" WHERE "U"=6))
   4 - access("U"=6)
A22
-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |     1 |   149 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LARGE   |     1 |   149 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | LARGE_N |     1 |       |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID      | SMALL   |     1 |     6 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN               | SMALL_U |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

   2 - access("N"= (SELECT "NN" FROM "SMALL" "SMALL" WHERE "U"=6))
   4 - access("U"=6)
A23
------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     1 |   149 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LARGE    |     1 |   149 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | LARGE_NN |     1 |       |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID      | SMALL    |     1 |     6 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN               | SMALL_U  |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

   2 - access("NN"= (SELECT "N" FROM "SMALL" "SMALL" WHERE "U"=6))
   4 - access("U"=6)
A24
------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     1 |   149 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| LARGE    |     1 |   149 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | LARGE_NN |     1 |       |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID      | SMALL    |     1 |     6 |     1   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN               | SMALL_U  |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

   2 - access("NN"= (SELECT "NN" FROM "SMALL" "SMALL" WHERE "U"=6))
   4 - access("U"=6)

PostgreSQL selects the following execution plans. All of them fulfill the expectations.

A20
 Index Scan using large_u on large  (cost=1.55..9.57 rows=1 width=148)
   Index Cond: (u = $0)
   InitPlan 1 (returns $0)
     ->  Seq Scan on small  (cost=0.00..1.12 rows=1 width=4)
           Filter: (u = 6)
A21/A22
 Index Scan using large_n on large  (cost=1.55..9.57 rows=1 width=148)
   Index Cond: (n = $0)
   InitPlan 1 (returns $0)
     ->  Seq Scan on small  (cost=0.00..1.12 rows=1 width=4)
           Filter: (u = 6)
A23/A24
 Index Scan using large_nn on large  (cost=1.55..9.57 rows=1 width=148)
   Index Cond: (nn = $0)
   InitPlan 1 (returns $0)
     ->  Seq Scan on small  (cost=0.00..1.12 rows=1 width=4)
           Filter: (u = 6)

Type B – Scalar subqueries with inequality predicate Subtype B1 – Table “large” in the subquery

B10: SELECT * FROM small WHERE u != (SELECT nu FROM large WHERE u = 6)
B11: SELECT * FROM small WHERE n != (SELECT n FROM large WHERE u = 6)
B12: SELECT * FROM small WHERE n != (SELECT nn FROM large WHERE u = 6)
B13: SELECT * FROM small WHERE nn != (SELECT n FROM large WHERE u = 6)
B14: SELECT * FROM small WHERE nn != (SELECT nn FROM large WHERE u = 6)

The execution plan I expect for these queries carries out the following operations:

  • Access the table “large” through an index scan that returns at most one value. The operation is executed one single time.
  • Access the table “small” through a table scan and discard the rows matching the value returned by the previous operation. The operation is executed one single time.

MySQL selects the following execution plans. All of them fulfill the expectations.

B10
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | small | NULL       | range | small_u       | small_u | 4       | NULL  |    9 |   100.00 | Using where |
|  2 | SUBQUERY    | large | NULL       | const | large_u       | large_u | 4       | const |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
B11/B12
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | small | NULL       | range | small_n       | small_n | 5       | NULL  |    8 |   100.00 | Using where |
|  2 | SUBQUERY    | large | NULL       | const | large_u       | large_u | 4       | const |    1 |   100.00 | NULL       ..
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This is a short post to point out a problem I recently discovered and for which I couldn’t find information online. Hence, in case you hit it, thanks to this short post I hope that you’ll save some time.

The documentation describes a procedure you can use to get rid of dangling triples stored in a semantic network. Simply put, you must execute a PL/SQL block like the following:

BEGIN
  sem_apis.purge_unused_values(flags => 'PARALLEL');
END;

Since one of my customers was/is concerned by the number of dangling triples that are stored in its databases, in October I experimented with the SEM_APIS.PURGE_UNUSED_VALUES procedure. Unfortunately, I quickly discovered that with a non-trivial number of triples it resulted in a never-ending operation. For one specific case I estimated ca. 300 days for one single run! It goes without saying that the issue was recognized as a bug.

Oracle Support provided a patch that I could test today. Note that I can’t share with you the patch number because, as far as I can see, it has no official number (two “references” are provided in the README: “12_1_02_SEM_NOV_30_2017” and “12.1.0.2.0_P1”). Anyway, the essential thing is that according to my tests the patch works. Now the never-ending operation takes less than 3 minutes to complete. Way better!

In case you experience a similar issue, you should open a service request. Oracle Support should be able to help you…

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

You can use the V$SQL.IS_RESOLVED_ADAPTIVE_PLAN column to know whether the execution plan associated to a child cursor is adaptive or not. Specifically, to know whether the query optimizer selected either an adaptive join method or an adaptive star transformation (notice that it is not set when the hybrid hash distribution is involved).

The column takes one of the following values:

  • NULL: the execution plan associated to the cursor is not adaptive
  • N: the execution plan is adaptive but the final execution plan is not yet determined
  • Y: the execution plan is adaptive and the final execution plan was determined

If you are interested in such an information for a SQL statement that is neither cached in the library cache nor present in AWR, as of version 12.1.0.2 you might want to check the SQL_ADAPTIVE_PLAN_RESOLVED column provided by the views V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY. In fact, according to the documentation, it provides the following information:

Indicates whether the SQL plan of the sampled database session is a resolved adaptive plan or not

Even though the documentation provides no information about the valid values for that column, given that according to my observations only the values “0” and “1” are used, I conclude that adaptive plans should be marked with the value “1”.

Unfortunately, this is not the case. In fact, also executions that are not using an adaptive plan are marked with the value “1”!

I reproduced the issue with the following test case (notice that even though the OPTIMIZER_ADAPTIVE_PLANS parameter is set to FALSE and the cursor has the IS_RESOLVED_ADAPTIVE_PLAN column set to NULL, according to the SQL_ADAPTIVE_PLAN_RESOLVED column an execution involving an adaptive plan took place):

SQL> CREATE TABLE t1 (id PRIMARY KEY, n, pad)
  2  AS
  3  SELECT rownum, CASE WHEN rownum  CREATE TABLE t2 (id PRIMARY KEY, n, pad)
  2  AS
  3  SELECT rownum, rownum, lpad('*',100,'*')
  4  FROM dual
  5  CONNECT BY level  execute dbms_stats.gather_table_stats(user,'t2')

SQL> CREATE OR REPLACE FUNCTION burn_cpu(p_wait IN INTEGER) RETURN INTEGER IS
  2    l_begin NUMBER;
  3  BEGIN
  4    l_begin := dbms_utility.get_time();
  5    LOOP
  6      EXIT WHEN l_begin+(p_wait*100)  ALTER SYSTEM FLUSH SHARED_POOL;

SQL> show parameter optimizer_adaptive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_adaptive_plans             boolean     FALSE
optimizer_adaptive_reporting_only    boolean     FALSE
optimizer_adaptive_statistics        boolean     FALSE

SQL> SELECT count(t1.pad), count(t2.pad)
  2  FROM t1, t2
  3  WHERE t1.id = t2.id
  4  AND t1.n = 666
  5  AND burn_cpu(t1.id/t1.id) = 1;

COUNT(T1.PAD) COUNT(T2.PAD)
------------- -------------
            1             1

SQL> SELECT sql_id, child_number, is_resolved_adaptive_plan, elapsed_time
  2  FROM v$sql
  3  WHERE (sql_id, child_number) = (SELECT prev_sql_id, prev_child_number
  4                                  FROM v$session
  5                                  WHERE sid = sys_context('userenv','sid'));

SQL_ID        CHILD_NUMBER IS_RESOLVED_ADAPTIVE_PLAN ELAPSED_TIME
------------- ------------ ------------------------- ------------
8ygb8f7cycp4f            0                              152687501

SQL> SELECT sql_id, sql_adaptive_plan_resolved, count(*)
  2  FROM v$active_session_history
  3  WHERE session_id = sys_context('userenv','sid')
  4  AND sample_time > systimestamp - INTERVAL '150' SECOND
  5  GROUP BY sql_id, sql_adaptive_plan_resolved;

SQL_ID        SQL_ADAPTIVE_PLAN_RESOLVED   COUNT(*)
------------- -------------------------- ----------
8ygb8f7cycp4f                          1        150

Even though I still have to open a service request, in my opinion this is a bug.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The aim of this post is to point out an issue (bug?) that I recently discovered. But, before talking about it, I need to go through a rather long introduction about the state of SQL plan directive (SPD).

As of version 12.1.0.2, an SPD has two state information. You can see both of them through the CDB/DBA_SQL_PLAN_DIRECTIVES data dictionary views:

  • The STATE column, which takes the values USABLE and SUPERSEDED, informs us on whether the query optimizer actively uses the SPD.
  • The NOTES column contains an XML fragment that, among other information, provide the INTERNAL_STATE element. This element is set to either NEW, MISSING_STATS, PERMANENT or HAS_STATS.

The internal state is the actual state of the SPD (and the only one available in version 12.1.0.1). Oracle introduced the other one to hide some implementation details that they consider irrelevant for most users. As a result, the mapping between the two states is rather simple: except in case of redundant SPDs, the internal states NEW, MISSING_STATS and PERMANENT are mapped to USABLE; otherwise, SUPERSEDED is used.

So, for the rest of this discussion, let’s consider the internal state only. That, for simplicity, is referred to as “state”.

Depending on the state of an SPD and the database configuration, the database engine triggers different operations. And, while doing so, it can also change the state itself. For example, with a version 12.1.0.2 default configuration, the lifecycle of a specific SPD can be like the following (let me stress that this is just an example, a number of different scenarios are also possible):

  • The SPD is created. Its state is set to NEW.
  • The query optimizer, while generating an execution plan, notices that a matching SPD exists. Since the SPD state is NEW, it triggers dynamic sampling and changes the SPD state to MISSING_STATS.
  • The DBMS_STATS package, while gathering object statistics, notices that the table it is working on has an associated SPD. Because of that, it creates an extension and gathers statistics for it.
  • The query optimizer, while generating an execution plan, notices that a matching SPD exist. Since the SPD state is MISSING_STATS and that an extension for it exists, it triggers dynamic sampling and changes the SPD state to HAS_STATS.
  • From now on, neither the query optimizer nor the DBMS_STATS package considers the SPD.

The essential thing I wanted to emphasize by describing such a scenario is that an SPD state is expected to change over time. And, depending on it and on the database configuration, the database engine can trigger different operations.

Now that the introduction part is over, let us focus on the issue I recently found while working for one of my customers. The particular thing about that customer (and, as a result, why I never noticed it before) is that it uses 12.1.0.2 with the patch 22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES), but without the patch 21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY). If you do not know what those patches are, have a look to this blog post.

Since the customer was using OPTIMIZER_ADAPTIVE_STATISTICS set to FALSE, I expected the SPD to be created without being actually used. But, what I discovered is that while SPD do not trigger dynamic sampling, they do trigger the creation of extensions. And, even worse (and that is the real issue), they do so without changing the SPD state that remains set to NEW!

If you are interested to see an example, here is the test case I wrote to reproduce it. Note that the issue can also be reproduced with version 12.2.0.1.

Finally, if you want to track this issue, refer to bug 27178368 (SPD STATE DOES NOT CHANGE IF ADAPTIVE STATISTICS IS FALSE).

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

The aim of this post is not to explain how the APPROX_MEDIAN function works (you find basic information in the documentation) but to show you the results of a test case I run to assess how well it works.

Here’s what I did…

I started in the Oracle Database Public Cloud an instance of version 12.2.

Then I created a table with several numerical columns (the name of each column shows how many distinct values it contains), loaded 150 million rows into it (the size of the segment is 20 GB), and gathered the object statistics.

CREATE TABLE t AS
WITH
  t1000 AS (SELECT /*+ materialize */ rownum AS n
            FROM dual
            CONNECT BY level 
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Performance feedback is one of the adaptive query optimizer features introduced in Oracle Database 12c. The aim of this short post isn’t to explain how it works, but to simply warn you about a buggy behavior in its configuration.

The parameters that control performance feedback are the following:

  • OPTIMIZER_ADAPTIVE_FEATURES (12.1 only): if set to TRUE (default), performance feedback is enabled. Otherwise, it is disabled.
  • OPTIMIZER_ADAPTIVE_STATISTICS (12.1 with patch 22652097, and 12.2): if set to TRUE, performance feedback is enabled. Otherwise, which is the default, it is disabled.
  • PARALLEL_DEGREE_POLICY (12.1 and 12.2): it has to be set to ADAPTIVE (default is MANUAL) to enable performance feedback.

So far, so good.

The problem is that all those parameters control the undocumented parameter _OPTIMIZER_PERFORMANCE_FEEDBACK without checking how the others are set. As a result, knowing the value of the documented parameters isn’t enough to know whether statistics feedback is enabled. What you have to know is the order in which they were set! Alternatively you can check the value of the undocumented parameter.

To demonstrate that behavior I wrote this script. It’s output, when executed against 12.1 and 12.2, is the following. As you can see, when PARALLEL_DEGREE_POLICY is set after OPTIMIZER_ADAPTIVE_FEATURES/ OPTIMIZER_ADAPTIVE_STATISTICS, performance feedback is incorrectly enabled.

  • 12.1
VERSION
------------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_features *before* parallel_degree_policy

optimizer_adaptive_features parallel_degree_policy _optimizer_performance_feedback
--------------------------- ---------------------- -------------------------------
FALSE                       MANUAL                 OFF
FALSE                       LIMITED                OFF
FALSE                       AUTO                   OFF
FALSE                       ADAPTIVE               ALL
TRUE                        MANUAL                 OFF
TRUE                        LIMITED                OFF
TRUE                        AUTO                   OFF
TRUE                        ADAPTIVE               ALL

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_features *after* parallel_degree_policy

parallel_degree_policy optimizer_adaptive_features _optimizer_performance_feedback
---------------------- --------------------------- -------------------------------
MANUAL                 FALSE                       OFF
LIMITED                FALSE                       OFF
AUTO                   FALSE                       OFF
ADAPTIVE               FALSE                       OFF
MANUAL                 TRUE                        OFF
LIMITED                TRUE                        OFF
AUTO                   TRUE                        OFF
ADAPTIVE               TRUE                        ALL
  • 12.2
VERSION
------------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_statistics *before* parallel_degree_policy

optimizer_adaptive_statistics parallel_degree_policy _optimizer_performance_feedback
----------------------------- ---------------------- -------------------------------
FALSE                         MANUAL                 OFF
FALSE                         LIMITED                OFF
FALSE                         AUTO                   OFF
FALSE                         ADAPTIVE               ALL
TRUE                          MANUAL                 OFF
TRUE                          LIMITED                OFF
TRUE                          AUTO                   OFF
TRUE                          ADAPTIVE               ALL

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_statistics *after* parallel_degree_policy

parallel_degree_policy optimizer_adaptive_statistics _optimizer_performance_feedback
---------------------- ----------------------------- -------------------------------
MANUAL                 FALSE                         OFF
LIMITED                FALSE                         OFF
AUTO                   FALSE                         OFF
ADAPTIVE               FALSE                         OFF
MANUAL                 TRUE                          OFF
LIMITED                TRUE                          OFF
AUTO                   TRUE                          OFF
ADAPTIVE               TRUE                          ALL
Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview