Posts in category Indexes

Challenges and Chances of the 11g Query Optimizer

Dec12
2011 Leave a Comment Written by Christian Antognini

Challenges and Chances of the 11g Query Optimizer is the name of a presentation I gave at several events (e.g. Trivadis Performance Days, Oracle OpenWorld, DOAG Konferenz, UKOUG Conference) throughout 2011. Its abstract is the following:

With every new release, the query optimizer is enhanced. Oracle Database 11g Release 1 and Release 2 are no exception to the rule. Specifically, they introduce key improvements in the following areas: indexing, optimization techniques, object statistics and plan stability. The aim of this presentation is to review the new features from a practical point of view as well as to point out challenges related to them. In other words, to let you know what you can expect from the query optimizer when you upgrade to Oracle Database 11g.

The aim of this short post is to point out that I made available the current version of the slides and all the scripts that go with them here.

The structure of the presentation (incl. a reference to the available scripts) is the following:

  • Observations
    • Number of Query Optimizer Parameters by Release
    • Number of Query Optimizer Bugs Fixed by Patchset
  • Indexing
    • Invisible Indexes (ex_invisible_index.sql)
    • Index Support for Linguistic LIKE (ex_linguistic_like.sql)
    • INDEX REBUILD and Statistics History (ex_index_rebuild.sql)
  • Optimization Techniques
    • Full Outer Join (ex_full_outer_join.sql)
    • Join-Filter Pruning (ex_join_filter_pruning.sql)
    • Table Expansion (ex_table_expansion.sql)
    • Join Factorization (ex_join_factorization.sql)
    • OR Expansion (ex_or_expansion.sql)
    • Join Elimination (ex_join_elimination.sql)
    • Subquery Unnesting (ex_subquery_unnesting.sql)
  • System and Object Statistics (DBMS_STATS)
    • Workload System Statistics
    • Object Statistics – Default Preferences
    • Object Statistics – Auto Sample Size
    • Object Statistics – Pending Statistics (ex_pending_object_statistics.sql)
    • Object Statistics – Incremental Statistics (ex_incremental_stats.sql)
    • Object Statistics – Extended Statistics on Expressions (ex_extended_statistics1.sql)
    • Object Statistics – Extended Statistics on Column Groups (ex_extended_statistics2.sql)
    • Object Statistics – Seeding Column Groups
    • Object Statistics – Comparing Statistics (ex_comparing_statistics.sql)
    • Object Statistics – Locks not Exported
    • JOB_QUEUE_PROCESSES
  • Plan Stability
    • CURSOR_SHARING
    • SQL Plan Baselines (ex_execution_plan_stability.sql, ex_execution_plan_stability_10g.sql, ex_execution_plan_stability_11g.sql)
    • Stored Outlines
    • Adaptive Cursor Sharing (ex_bind_peeking.sql, ex_bind_peeking_bind_aware.sql)
    • Cardinality Feedback (ex_cardinality_feedback.sql)
Posted in 11gR1, 11gR2, Bug, Object Statistics, Query Optimizer, Speaking, System Statistics

IS NULL Conditions and B-tree Indexes

Feb17
2011 Leave a Comment Written by Christian Antognini

At page 383 of my book I wrote the following sentence (BTW, the same information is also provided by Table 9-3 at page 381):

With B-tree indexes, IS NULL conditions can be applied only through composite B-tree indexes when several SQL conditions are applied and at least one of them is not based on IS NULL or an inequality.

The text continues by showing the following examples (notice that in both cases the IS NULL predicate is applied through an access predicate):

SELECT /*+ index(t) */ * FROM t WHERE n1 = 6 AND n2 IS NULL

Plan hash value: 780655320

----------------------------------------------
| Id  | Operation                   | Name   |
----------------------------------------------
|   0 | SELECT STATEMENT            |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |
|*  2 |   INDEX RANGE SCAN          | I_N123 |
----------------------------------------------

   2 - access("N1"=6 AND "N2" IS NULL)

SELECT /*+ index(t) */ * FROM t WHERE n1 IS NULL AND n2 = 8

Plan hash value: 780655320

----------------------------------------------
| Id  | Operation                   | Name   |
----------------------------------------------
|   0 | SELECT STATEMENT            |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |
|*  2 |   INDEX RANGE SCAN          | I_N123 |
----------------------------------------------

   2 - access("N1" IS NULL AND "N2"=8)
       filter("N2"=8)

When I wrote that sentence I didn’t think about one case that, according to it, specifically the part “is not based on IS NULL or an inequality”, is not covered. In fact, as the following examples show, it is also possible to apply an IS NULL predicate when the other one is an IS NOT NULL. It is especially interesting to notice that the access predicate doesn’t reference at all the NOT NULL column!

SELECT /*+ index(t) */ * FROM t WHERE n1 IS NULL AND n2 IS NOT NULL

Plan hash value: 780655320

----------------------------------------------
| Id  | Operation                   | Name   |
----------------------------------------------
|   0 | SELECT STATEMENT            |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |
|*  2 |   INDEX RANGE SCAN          | I_N123 |
----------------------------------------------

   2 - access("N1" IS NULL)
       filter("N2" IS NOT NULL)

SELECT /*+ index(t) */ * FROM t WHERE n1 IS NOT NULL AND n2 IS NULL

Plan hash value: 3029444779

----------------------------------------------
| Id  | Operation                   | Name   |
----------------------------------------------
|   0 | SELECT STATEMENT            |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T      |
|*  2 |   INDEX SKIP SCAN           | I_N123 |
----------------------------------------------

   2 - access("N2" IS NULL)
       filter(("N2" IS NULL AND "N1" IS NOT NULL))
Posted in 10gR1, 10gR2, 11gR1, 11gR2, 9iR2, Query Optimizer, TOP

Parallel Full Table Scans Do Not Always Perform Direct Reads

Sep12
2010 5 Comments Written by Christian Antognini

Even though in general parallel full table scans performs direct reads, some exceptions exist. The aim of this post is to show such an exception.

For test purposes I build in my own schema a copy of the SH.SALES table (the one distributed by Oracle with the demo schemas…). On that table I build an index on the TIME_ID column and, at the same time, I trace the execution. The statements I use are the following.

execute dbms_monitor.session_trace_enable
CREATE INDEX sales_time_id ON sales (time_id) PARALLEL 2 ONLINE;
execute dbms_monitor.session_trace_disable

Since the index is built in parallel (DOP=2), 5 processes are used to run the statement: the query coordinator, 2 slaves for reading the table, and 2 slaves for building the index. Based on the data provided by extended SQL trace let’s have a look to some information about the execution.

  • Execution plan (without runtime statistics and query optimizer estimations): both the build of the index and the full table scan are performed in parallel.
Operation
----------------------------------------
PX COORDINATOR
  PX SEND QC (ORDER) :TQ10001
    INDEX BUILD NON UNIQUE SALES_TIME_ID
      SORT CREATE INDEX
        PX RECEIVE
          PX SEND RANGE :TQ10000
            PX BLOCK ITERATOR
              TABLE ACCESS FULL SALES
  • Resource usage profile of the query coordinator: no real work is performed, it’s just coordination work…
                                               Total            Number of     Duration per
Component                               Duration [s]       %       Events       Events [s]
----------------------------------- ---------------- ------- ------------ ----------------
PX Deq: Execute Reply                          1.928  79.044           44            0.044
recursive statements                           0.427  17.487          n/a              n/a
CPU                                            0.024   0.984          n/a              n/a
os thread startup                              0.017   0.690            1            0.017
log file sync                                  0.014   0.569            2            0.007
PX Deq: Parse Reply                            0.014   0.568            4            0.003
enq: CR - block range reuse ckpt               0.005   0.202            2            0.002
PX Deq: Join ACK                               0.004   0.145            5            0.001
SQL*Net message from client                    0.002   0.098            1            0.002
PX qref latch                                  0.002   0.075            1            0.002
PX Deq: Table Q qref                           0.001   0.058            1            0.001
enq: RO - fast object reuse                    0.001   0.028            1            0.001
PX Deq: Signal ACK EXT                         0.001   0.025            6            0.000
PX Deq: Slave Session Stats                    0.000   0.014            3            0.000
reliable message                               0.000   0.006            2            0.000
latch: call allocation                         0.000   0.004            1            0.000
db file sequential read                        0.000   0.004            6            0.000
rdbms ipc reply                                0.000   0.002            2            0.000
SQL*Net message to client                      0.000   0.000            1            0.000
----------------------------------- ---------------- -------
Total                                          2.439 100.000
  • Resource usage profile of one of the slaves building the index: direct writes are used to store the index.
                                               Total            Number of     Duration per
Component                               Duration [s]       %       Events       Events [s]
----------------------------------- ---------------- ------- ------------ ----------------
direct path write                              0.982  49.671          376            0.003
CPU                                            0.781  39.508          n/a              n/a
PX Deq: Table Q Normal                         0.177   8.957          130            0.001
PX Deq: Execution Msg                          0.012   0.595            4            0.003
cursor: pin S wait on X                        0.011   0.535            1            0.011
recursive statements                           0.008   0.420          n/a              n/a
log file sync                                  0.005   0.247            3            0.002
Disk file operations I/O                       0.001   0.030            1            0.001
PX Deq: Slave Session Stats                    0.000   0.021            1            0.000
reliable message                               0.000   0.011            1            0.000
rdbms ipc reply                                0.000   0.006            2            0.000
----------------------------------- ---------------- -------
Total                                          1.977 100.000
  • Resource usage profile of one of the slaves scanning the table: instead of performing direct reads, “regular” buffered reads are performed (notice the db file scattered read event).
                                               Total            Number of     Duration per
Component                               Duration [s]       %       Events       Events [s]
----------------------------------- ---------------- ------- ------------ ----------------
PX Deq: Execution Msg                          1.571  80.295           23            0.068
CPU                                            0.311  15.889          n/a              n/a
PX Deq Credit: need buffer                     0.055   2.794          432            0.000
db file scattered read                         0.016   0.835           55            0.000
PX Deq: Table Q Get Keys                       0.002   0.109            1            0.002
Disk file operations I/O                       0.001   0.032            1            0.001
PX Deq Credit: send blkd                       0.001   0.028            1            0.001
PX Deq: Slave Session Stats                    0.000   0.010            1            0.000
db file sequential read                        0.000   0.005            4            0.000
PX qref latch                                  0.000   0.002            1            0.000
latch: cache buffers chains                    0.000   0.000            1            0.000
----------------------------------- ---------------- -------
Total                                          1.957 100.000

As pointed out by the last resource usage profile no direct reads are performed. Why? In this case it is because the ONLINE option was specified. By the way, I do not know why there is such a limitation… Anway, without this option, for one of the slaves reading the table the following resource usage profile is used (notice the direct path read event). I do not show the other resource usage profiles and the execution plan because they do not change.

                                               Total            Number of     Duration per
Component                               Duration [s]       %       Events       Events [s]
----------------------------------- ---------------- ------- ------------ ----------------
PX Deq: Execution Msg                          1.499  80.358           22            0.068
CPU                                            0.278  14.897          n/a              n/a
PX Deq Credit: need buffer                     0.071   3.790          504            0.000
direct path read                               0.012   0.630           52            0.000
PX Deq Credit: send blkd                       0.003   0.176            2            0.002
PX Deq: Table Q Get Keys                       0.001   0.047            2            0.000
PX Deq: Slave Session Stats                    0.001   0.041            1            0.001
Disk file operations I/O                       0.001   0.033            1            0.001
library cache: mutex X                         0.000   0.025            1            0.000
db file sequential read                        0.000   0.002            2            0.000
asynch descriptor resize                       0.000   0.001           18            0.000
----------------------------------- ---------------- -------
Total                                          1.866 100.000

In summary, do not expect to always see direct reads when a parallel full table scan is performed.

Posted in 10gR1, 10gR2, 11gR1, 11gR2, Parallel Processing
← Older Entries