//
archives

optimizer

This tag is associated with 11 posts

Filter and Access in Execution Plan

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:7807480400346035212

Advertisements

Combined Access and Filter Predicates

http://oracle-randolf.blogspot.sg/2015/04/combined-access-and-filter-predicates.html

Dynamic sampling and its impact on the Optimizer (Oracle Optimizer)

via Dynamic sampling and its impact on the Optimizer (Oracle Optimizer)

Explain adaptive cursor sharing behavior with cursor_sharing = similar and force.

via Explain adaptive cursor sharing behavior with cursor_sharing = similar and force. (Oracle Optimizer)

What are RowID Scans

What are RowID Scans

Choosing An Optimal Stats Gathering Strategy | Structured Data

Choosing An Optimal Stats Gathering Strategy | Structured Data.

11g Optimizer Plan Stabilty using SQL Plan Baselines « Oracle DBA – Tips and Techniques

11g Optimizer Plan Stabilty using SQL Plan Baselines « Oracle DBA – Tips and Techniques.

What is the difference between SQL Profiles and SQL Plan Baselines? (Oracle Optimizer)

What is the difference between SQL Profiles and SQL Plan Baselines? (Oracle Optimizer).

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

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

How do I know if the cardinality estimates in a plan are accurate? (Oracle Optimizer)

How do I know if the cardinality estimates in a plan are accurate? (Oracle Optimizer).

Further reading :
http://kerryosborne.oracle-guy.com/2010/02/gather_plan_statistics/
http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/