Category: optimizer

Adaptive Execution Plans

Adaptive Query Optimization

  • adaptive plans
    join methods
    parallel distrubution methods

  • adaptive statistics
    dynamic statistics
    automatic reoptimization
    sql plan directives

=============
Adaptive Plans in Oracle Database 12c Release 1 (12.1)
Adaptive SQL Plan Management (SPM) in Oracle Database 12c Release 1 (12.1)
Adaptive Query Optimization in Oracle Database 12c Release 1 (12.1)

=============
Parameters :
optimizer_adaptive_features
optimizer_adaptive_reporting_only

Advertisements

What are Rowid Scans ?

I came across this at one of the blog-posts (seems now removed). Reference to blog mentioned at end-of-blog post. Thought it was still worth mentioning here.


  • The rowid of a row specifies the datafile and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row, because the exact location of the row in the database is specified.

  • To access a table by rowid, Oracle first obtains the rowids of the selected rows, either from the statement’s WHERE clause or through an index scan of one or more of the table’s indexes. Oracle then locates each selected row in the table based on its rowid.

When the Optimizer Uses Rowids?

Suppose I made an index on table test_tab column col1. The table has two columns col1 and col2. Now if in my query I use to select both columns like I use SELECT * FROM TEST_TAB WHERE COL1=1; then optimizer at first step retrieve the rowid from index on column col1 and then in the second step optimizer look for row using that rowid.

Access by rowid does not need to follow every index scan. If the index contains all the columns needed for the statement, then table access by rowid might not occur.

An example will make you clear.

SQL> create table test_tab as select level col1, level col2 from dual connect by level<=1000; Table created. SQL> set autot trace
SQL> create index test_tab_I on test_tab(col1);
Index created.

Here, I select col1 , the only column by which I created index test_tab_I. So, to select only col1 no need to rowid scan.

SQL> select col1 from test_tab where col1=99;

Execution Plan
----------------------------------------------------------
Plan hash value: 933728095

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_TAB_I | 1 | 13 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("COL1"=99)

Note
-----
- dynamic sampling used for this statement

Here I select both col1 and col2. col2 is not inside test_tab_I index. So it used index rowid scan to find the col2 field.

SQL> select * from test_tab where col1=99;

Execution Plan
----------------------------------------------------------
Plan hash value: 3125486718

------------------------------------------------------------------------------------------
| Id | Operation                 | Name       | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT           |            | 1    | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB   | 1    | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN          | TEST_TAB_I | 1    |    | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=99)

Note
-----
- dynamic sampling used for this statement

Note that in both cases dynamic sampling are used. Because I have not gather statistics. If I generate statistics then note will disappear.

SQL> analyze table test_tab estimate statistics;
Table analyzed.

SQL> select * from test_tab where col1=99;

Execution Plan
----------------------------------------------------------
Plan hash value: 3125486718

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_TAB | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_TAB_I | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("COL1"=99)

reference : http://arjudba.blogspot.sg/2008/06/what-and-when-oracle-uses-rowid-scans.html

Cache Buffer Chain Latches

The “cache buffer chain” latch wait is normal, but high values are associated with high simultaneous buffer access, similar to a freelist shortage on an index or table segment header.

v$latch_children
To identify the heavily accessed buffer chain, and hence the contended for block, look at latch statistics for the cache buffers chains latches using the view V$LATCH_CHILDREN. If there is a specific cache buffers chains child latch that has many more GETS, MISSES, and SLEEPS when compared with the other child latches, then this is the contended for child latch.

ref from : Page 10-34 of Oracle Database Performance Tuning Guide 12C

Usually latch contention for these buffer caches  is due to poor disk I/O configuration. Reducing contention with these latches involves tuning the logical I/O for the associated SQL statements as well as the disk subsystem.

Another factor for latch contention with buffers chain latches could possibly be hot block contention.

Oracle Metalink Note # 163424.1 has some useful tips on tuning and identifying hot blocks within the Oracle database environment.

Latch requests come in two flavors, willing to wait and no-wait modes. In willing to wait mode, when a latch cannot be acquired, the acquiring session will go into a spin mode, attempting to acquire the latch over and over a specified number of times. After the number of spins has reached a specific threshold, the session will sleep for a specified period of time, wake up and try the latch again. Spin mode is bad, but sleep mode is worse!

Very interesting articles :

http://www.dba-oracle.com/t_high_cache_buffer_chain_waits_contention.htm

http://www.toadworld.com/platforms/oracle/w/wiki/1302.how-to-identify-a-buffer-cache-chain-latch-problem.aspx

http://blog.tanelpoder.com/2013/11/06/diagnosing-buffer-busy-waits-with-the-ash_wait_chains-sql-script-v0-2/

https://sites.google.com/site/embtdbo/wait-event-documentation

http://blog.tanelpoder.com/2009/08/27/latch-cache-buffers-chains-latch-contention-a-better-way-for-finding-the-hot-block

http://www.pythian.com/news/1135/tuning-latch-contention-cache-buffers-chain-latches/

http://www.oaktable.net/content/latch-cache-buffer-chains-small-index-primary-key-caused-concurrent-batch-scripts-select-sta#comment-6

http://jonathanlewis.wordpress.com/2008/02/09/index-rebuild-10g/

https://sites.google.com/site/embtdbo/wait-event-documentation/oracle-latch-cache-buffers-chains

http://docs.oracle.com/database/121/TGDBA/pfgrf_instance_tune.htm#TGDBA94516

http://arup.blogspot.com/2014/11/cache-buffer-chains-demystified.html