++++Oracle sql tuning docs+++++

---Database Performance Tuning Guide 12c---

https://docs.oracle.com/database/121/TGDBA/index.htm

 

---Database SQL Tuning Guide 12.1---

https://docs.oracle.com/database/121/TGSQL/tgsql_intro.htm#TGSQL112

 

---SQL Tuning Guide 12c Release 1 (12.1) E49106-14 July 2017---

https://docs.oracle.com/database/121/TGSQL/E49106-14.pdf

 

---SQL Tuning Guide 12c Release 2 (12.2) E85762-04 January 2019---

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/sql-tuning-guide.pdf

 

--SQL Tuning Guide 18c E84296-04 January 2019----

https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-tuning-guide.pdf

 

---Database Performance Tuning Guide 18c E83719-04 January 2019---

https://docs.oracle.com/en/database/oracle/oracle-database/18/tgdba/database-performance-tuning-guide.pdf

 

---The Optimizer In Oracle Database 19c (white paper)---

https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

 

---Understanding Optimizer Statistics With Oracle Database 19c (white paper)---

https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-stats-concepts-19c-5324209.pdf

 

---Best Practices for Gathering Optimizer Statistics with Oracle Database 19c (white paper)---

https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-19c-5324205.pdf

 

---SQL Plan Management in Oracle Database 19c (white paper)---

https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-19c-5324207.pdf

 

---SQL Tuning Guide 19c E96095-03 January 2019----

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-tuning-guide.pdf

 

---Database Performance Tuning Guide 19c----

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/database-performance-tuning-guide.pdf

 

--Database Performance Tuning Guide---

https://docs.oracle.com/cd/E11882_01/server.112/e41573/perf_overview.htm#PFGRF025

 

--2 Day + Performance Tuning Guide--

https://docs.oracle.com/cd/B28359_01/server.111/b28275.pdf

 

----Database 2 Day + Real Application Clusters Guide---

https://docs.oracle.com/cd/B28359_01/rac.111/b28252/racmon2.htm#TDPRC008

 

https://docs.oracle.com/cd/B19306_01/server.102/b14211/instance_tune.htm#g58652

 

---RAC WAIT Events----

https://docs.oracle.com/cd/B19306_01/rac.102/b14197/monitor.htm#RACAD980

 

++++++ASH and AWR++++++

https://www.red-gate.com/simple-talk/wp-content/uploads/oracle/2012/08/Performance-Tuning-ASH-AWR-Data-Webinar.pdf

 

++++Oracle sequence caching & performance+++++

Oracle sequence caching & performance

 

++++Analysis of Performance Issues+++++

http://www.dba-oracle.com/real_application_clusters_rac_grid/performance_analysis.htm

 

http://dbaparadise.com/

 

String Aggregation Techniques:

https://oracle-base.com/articles/misc/string-aggregation-techniques#specific_function

 

DB time link:

http://www.oracle.com/technetwork/oem/db-mgmt/s317294-db-perf-tuning-with-db-time-181631.pdf

 

Harnessing the Power of Optimizer Hints:

https://sqlmaria.com/2017/09/19/harnessing-the-power-of-optimizer-hints/

 

Index Rebuild online: What happens in the background?

http://loredata.com.br/2017/08/10/o-que-acontece-quando-voce-faz-rebuild-online-do-indice/

 

http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13dba-1871177.html

 

http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42dba-1566567.html

 

http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62dba-1741123.html

 

+++++++++++++++++Execution plans+++++++++++++++++

Execution plans : blocking and non-blocking operations

https://hourim.wordpress.com/2017/10/13/execution-plans-blocking-and-non-blocking-operations/

 

How To Change Execution Plan Of Query Without Modifying SQL Code:

http://www.dbainsights.com/2017/12/how-to-change-execution-plan-of-query_17.html?spref=fb&m=1

 

Interpreting Explain Plan:

https://www.akadia.com/services/ora_interpreting_explain_plan.html

 

Comparing Plans:

https://jonathanlewis-wordpress-com.cdn.ampproject.org/c/s/jonathanlewis.wordpress.com/2018/03/12/comparing-plans/amp/?lipi=urn%3Ali%3Apage%3Ad_flagship3_feed%3BgXzfAVzaS0iqcsUPWhyHmQ%3D%3D&licu=urn%3Ali%3Acontrol%3Ad_flagship3_feed-object

++++++++++++++++++++++++++++++++++++++++++++++++++

 

Buffer Sorts:

https://jonathanlewis.wordpress.com/2006/12/17/buffer-sorts/

 

Instance Tuning Using Performance Views:

https://docs.oracle.com/cd/B19306_01/server.102/b14211/instance_tune.htm#g58652

 

Automatic partitioning in 12cr2:

https://blogs.oracle.com/oraclemagazine/automatic-and-easy

 

Adaptive Cursor Sharing:

http://www.dbainsights.com/2017/12/adaptive-cursor-sharing.html?spref=fb

 

Bind-Sensitive Cursors:

http://www.dbainsights.com/2017/12/bind-sensitive-cursors.html?spref=fb&m=1

 

+++++++++++++++++Shared pool+++++++++++++++++

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

http://dba-oracle.com/t_oracle_library_cache_contention_tips.htm

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

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

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

 

How Parsing in Oracle Causes Contention for Shared Pool and Library Cache Latches:

https://logicalread.com/oracle-contention-for-shared-pool-and-library-cache-latches-mc01/#.WpPQ5q6WbIU

 

14 Tuning the Shared Pool and the Large Pool

https://docs.oracle.com/database/121/TGDBA/tune_shared_pool.htm#TGDBA558

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

How to read PGA memory advisor in AWR:

http://www.onlinedbasupport.com/2011/09/20/how-to-read-pga-memory-advisor-in-awr/

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:246493100346092692

 

Monitoring Open and Cached Cursors:

http://www.orafaq.com/node/758

https://www.toadworld.com/platforms/oracle/b/weblog/archive/2017/04/12/session-cursor-caching-part-one

 

+++++++++++++++++WAIT EVENTS++++++++++++++

https://www.toadworld.com/platforms/oracle/w/wiki/786.db-file-parallel-write

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

 

enq tx - index contention oracle 11g:

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

 

direct path read wait event in oracle 11g:

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

 

db file parallel read wait event oracle 11g:

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

 

CPU in AWR Report tips:

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

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

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

 

User Calls (per second)

https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_instance_throughput_usercalls_ps.html

 

Difference between "db file scatterd read" and "db file sequential read":

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:957829973821

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

https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_db_file_sequential_read_pct.html

https://www.toadworld.com/platforms/oracle/w/wiki/788.db-file-sequential-read

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

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

 

https://fritshoogland.files.wordpress.com/2012/06/about-multiblock-reads-v2.pdf

 

direct_path_read/direct_path_read_temp:

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

https://www.perftuning.com/blog/direct-path-reads-oracle-database-11gr2/

https://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_direct_path_read_pct.html

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3931724600346905308

https://www.toadworld.com/platforms/oracle/w/wiki/792.direct-path-read

http://blog.orapub.com/20160316/difference-between-oracle-database-direct-path-reads-and-db-file-scattered-reads.html

 

+++++++++++++Latch Issues+++++++++++++++++

The Arup Nanda Blog: Cache Buffer Chains Demystified

 

Resolving Latch Contention:

https://www.toadworld.com/platforms/oracle/b/weblog/archive/2014/12/12/resolving-latch-contention

 

Library cache latch contention:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1163635055580

 

Troubleshooting: Contention – Library cache lock – Oracle Database

https://www.linkedin.com/pulse/troubleshooting-contention-library-cache-lock-oracle-bruno-murassaki/?trackingId=XJXS3OKbS1v8JCC3xDWTcw%3D%3D&lipi=urn%3Ali%3Apage%3Ad_flagship3_feed%3B%2BzGhxyz3Q%2FCBjUCaBtKRKA%3D%3D&licu=urn%3Ali%3Acontrol%3Ad_flagship3_feed-object

 

+++++Mutex+++++++++++

https://www.databasejournal.com/features/oracle/oracle-mutexes.html?utm_medium=email&utm_campaign=DBJ_NL_DD_20170717_STR…

 

+++++++++++++Display_cursor plan+++++++++++++

http://dbaparadise.com/wp-content/uploads/2016/04/UltimateSQLPerformanceTroubleshootingGuidepdf.pdf

 

select * from table(dbms_xplan.display_cursor('sql_id',0,'ADVANCED')); --> other options: PROJECTION -BYTES  RUNSTATS_LAST

 

select sh.snap_id, sh.instance_number inst, sh.begin_interval_time, s.sql_id, s.plan_hash_value, nvl(s.executions_delta,0) execs, (s.elapsed_time_delta/decode(nvl(s.executions_delta,0),0,1,s.executions_delta))/1000000 avg_etime, (s.buffer_gets_delta/decode(nvl(s.buffer_gets_delta,0),0,1,s.executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SH where s.sql_id = '&sqlid' and sh.snap_id = s.snap_id and sh.instance_number = s.instance_number order by 1, 2, 3

 

++++++++Sql Trace & Autotrace+++++++++++++

Autotrace:

https://docs.oracle.com/cd/A97630_01/server.920/a96533/autotrac.htm

 

Sql Trace:

Yet another DBA blog: TRACEFILE_IDENTIFIER - Another useful parameter

https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof

 

 

Thanks & Regards,

Mukesh Kumar Jha---