Technical Briefs and Blog Entries for Oracle Optimizer (Doc ID 1337116.1)

 Technical Briefs and Blog Entries for Oracle Optimizer (Doc ID 1337116.1)

Optimizer Technical Briefs & Blogs

 

Article TitleURL
What to expect from the Optimizer in Oracle Database 12chttp://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf
SQL Plan Management in Oracle Database 12chttp://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf
Understanding Optimizer Statistics with Oracle Database 12c - Part 1http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-statistics-concepts-12c-1963871.pdf
Best Practices for Gathering Optimizer Statistics with Oracle Database 12c - Part 2http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf
Optimizer Technical Papershttps://blogs.oracle.com/optimizer/search.html?contentType=Blog-Post&default=Optimizer Oracle Technical papers*
SQL Plan Managementhttps://blogs.oracle.com/optimizer/post/using-sql-plan-management-to-control-sql-execution-plans
Understanding Optimizer Statistics with Oracle Database 11g - Part 1http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-stats-concepts-110711-1354477.pdf
Best Practices for Gathering Optimizer Statistics with Oracle Database 11g - Part 2http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf
What is new in the optimizer for Oracle Database 19chttps://blogs.oracle.com/optimizer/post/whats-new-in-the-oracle-optimizer-for-oracle-database-19c
Adaptive Features in 18chttps://blogs.oracle.com/optimizer/post/optimizer-adaptive-features-and-upgrading-to-oracle-database-12c-release-2-or-oracle-database-18c
Adaptive Features in 12chttps://blogs.oracle.com/optimizer/post/what-adaptive-feature-settings-should-you-use-in-oracle-database-12c
Explain the Explain Planhttps://blogs.oracle.com/optimizer/post/how-do-i-display-and-read-the-execution-plans-for-a-sql-statement

 

Blogs

There are numerous Blogs written on various topics by members of the Optimizer development team under the heading Oracle Optimizer at : Optimizer and Database_In_Memory .

Please also refer to the Oracle DB/EM Troubleshooting Blog at: http://blogs.oracle.com/db/
Specific Optimizer related entries in these blogs are categorized and linked below for your convenience:

Blogs: Cursor Related Features

 

Article TitleURL
Explain adaptive cursor sharing behavior with cursor_sharing = similar and force.https://blogs.oracle.com/optimizer/post/how-does-adaptive-cursor-sharing-behavior-differ
Update on Adaptive Cursor Sharinghttps://blogs.oracle.com/optimizer/post/update-on-adaptive-cursor-sharing

How do adaptive cursor sharing and SQL Plan Management interact?

https://blogs.oracle.com/optimizer/post/how-do-adaptive-cursor-sharing-and-sql-plan-management-interact
Why are there more cursors  for my query containing bind variables?https://blogs.oracle.com/optimizer/post/why-are-there-many-cursors-for-queries-containing-bind-variables
How do I force a query to use bind-aware cursor sharing?https://blogs.oracle.com/optimizer/post/how-do-i-force-a-query-to-use-bind-aware-cursor-sharing
My cursor wasn’t shared because of STB_OBJECT_MISMATCH. What does that mean?https://blogs.oracle.com/optimizer/post/my-cursor-wasnt-shared-because-of-stb_object_mismatch-what-does-that-mean

 

Blogs: Optimizer Transformations

 

Article TitleURL
Optimizer Transformations: Join Factorizationhttps://blogs.oracle.com/optimizer/post/optimizer-transformations-join-factorization
Optimizer Transformation: Join Predicate Pushdownhttps://blogs.oracle.com/optimizer/post/optimizer-transformation-join-predicate-pushdown
Optimizer Transformations: OR Expansionhttps://blogs.oracle.com/optimizer/post/optimizer-transformations-or-expansion
Optimizer Transformations: Table Expansionhttps://blogs.oracle.com/optimizer/post/optimizer-transformations-table-expansion
Optimizer Transformations: View Merging part 1https://blogs.oracle.com/optimizer/post/optimizer-transformations-view-merging-part-1
Optimizer Transformations: View Merging part 2https://blogs.oracle.com/optimizer/post/optimizer-transformations-view-merging-part-2
Optimizer Transformations: Star Transformationhttps://blogs.oracle.com/optimizer/post/optimizer-transformations-star-transformation
Optimizer Transformations: Subquery Unnesting part 1https://blogs.oracle.com/optimizer/post/optimizer-transformations-subquery-unnesting-part-1
Optimizer Transformations: Subquery Unnesting part 2https://blogs.oracle.com/optimizer/post/optimizer-transformations-subquery-unesting-part-2

 

Blogs: SQL Plan Management

 

Article TitleURL
SQL Plan Management (Part 1 of 4) Creating SQL plan baselineshttp://blogs.oracle.com/optimizer/post/sql-plan-management-part-1-of-4-creating-sql-plan-baselines
SQL Plan Management (Part 2 of 4) SPM Aware Optimizerhttp://blogs.oracle.com/optimizer/post/sql-plan-management-part-2-of-4-spm-aware-optimizer
SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselineshttp://blogs.oracle.com/optimizer/post/sql-plan-management-part-3-of-4-evolving-sql-plan-baselines
SQL Plan Management (Part 4 of 4): User Interfaces and Other Featureshttp://blogs.oracle.com/optimizer/post/sql-plan-management-part-4-of-4-user-interfaces-and-other-features
Does the use of SQL Plan Management and the DBMS_SPM database package require a tuning or diagnostic pack license?https://blogs.oracle.com/optimizer/post/does-sql-plan-management-require-pack-license
What is the difference between SQL Profiles and SQL Plan Baselines?https://blogs.oracle.com/optimizer/post/what-is-the-difference-between-sql-profiles-and-sql-plan-baselines
How do I migrate stored outlines to SQL Plan Management?https://blogs.oracle.com/optimizer/post/how-do-i-migrate-stored-outlines-to-sql-plan-management
How does SQL Plan Management match SQL statements to SQL plan baselines?https://blogs.oracle.com/optimizer/post/how-does-sql-plan-management-match-sql-statements-to-sql-plan-baselines

How do adaptive cursor sharing and SQL Plan Management interact?

https://blogs.oracle.com/optimizer/post/how-do-adaptive-cursor-sharing-and-sql-plan-management-interact
SQL Plan Management and Auto Capturehttps://blogs.oracle.com/optimizer/post/what-you-need-to-know-about-sql-plan-management-and-auto-capture
Tips on SQL Plan Management and Oracle Database In-Memory Part 1https://blogs.oracle.com/optimizer/post/tips-on-sql-plan-management-and-oracle-database-in-memory-part-1
Tips on SQL Plan Management and Oracle Database In-Memory Part 2https://blogs.oracle.com/optimizer/post/tips-on-sql-plan-management-and-oracle-database-in-memory-partnbsp2
Tips on SQL Plan Management and Oracle Database In-Memory Part 3https://blogs.oracle.com/optimizer/post/tips-on-sql-plan-management-and-oracle-database-in-memory-partnbsp3

 

Blogs: Statistics

 

Article TitleURL
How to Gather Optimizer Statistics Fast!https://blogs.oracle.com/optimizer/post/how-to-gather-optimizer-statistics-fast
Gathering Statistics Only When Stalehttps://blogs.oracle.com/optimizer/post/gathering-statistics-only-when-stale
Should You Gather System Statistics?https://blogs.oracle.com/optimizer/post/should-you-gather-system-statistics
How do I know if the cardinality estimates in a plan are accurate?https://blogs.oracle.com/optimizer/post/how-do-i-know-if-the-cardinality-estimates-in-a-plan-are-accurate
Concurrent Statistics Gatheringhttp://blogs.oracle.com/optimizer/post/concurrent-statistics-gathering
Incremental Statistics Maintenance – what statistics will be gathered after DML occurs on the table?https://blogs.oracle.com/optimizer/post/incremental-statistics-maintenance-what-statistics-will-be-gathered-after-dml-occurs-on-the-table
How do I restrict concurrent statistics gathering to a small set of tables from a single schema?https://blogs.oracle.com/optimizer/post/how-do-i-restrict-concurrent-statistics-gathering-to-a-small-set-of-tables-from-a-single-schema
How do I compare statistics?https://blogs.oracle.com/optimizer/post/how-do-i-compare-optimizer-statistics
How do I drop an existing histogram on a column and stop the Auto Stats gathering job from creating it in the future?http://blogs.oracle.com/optimizer/post/how-do-i-drop-an-existing-histogram-on-a-column-and-stop-the-auto-stats-gathering-job-from-creating-it-in-the-future
How do I know what extended statistics are needed for a given workload?http://blogs.oracle.com/optimizer/post/how-do-i-know-what-extended-statistics-are-needed-for-a-given-workload
Extended Statisticshttp://blogs.oracle.com/optimizer/post/extended-statistics
Maintaining statistics on large partitioned tableshttp://blogs.oracle.com/optimizer/post/maintaining-statistics-on-large-partitioned-tables

How does AUTO_SAMPLE_SIZE work in Oracle Database 11g?

https://blogs.oracle.com/optimizer/post/how-does-auto-sample-size-work-in-oracle-database-11g

How does AUTO_SAMPLE_SIZE work in Oracle Database 12c?

https://blogs.oracle.com/optimizer/post/how-does-auto-sample-size-work-in-oracle-database-12c

How does the METHOD_OPT parameter work?

https://blogs.oracle.com/optimizer/post/how-does-the-method-opt-parameter-work
What should I set ESTIMATE_PERCENT to?https://blogs.oracle.com/optimizer/post/setting-estimate-percent
Fixed Objects Statistics and why they are importanthttps://blogs.oracle.com/optimizer/post/fixed-objects-statistics-and-why-they-are-important

 

Oracle Document: Testcases

 

Article TitleURL
Providing a Testcase - SQL Test Case Builderhttps://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/sql-test-case-builder.html

 

Blogs: Upgrading

 

Article TitleURL
Upgrading to Oracle Database 12c and Avoid Query Regressionhttp://blogs.oracle.com/optimizer/post/upgrade-to-oracle-database-12c-and-avoid-query-regression
Optimizer Adaptive Features and Upgrading to Oracle Database 12cR2 or Oracle Database 18chttps://blogs.oracle.com/optimizer/post/optimizer-adaptive-features-and-upgrading-to-oracle-database-12c-release-2-or-oracle-database-18c
How do I migrate stored outlines to SQL Plan Management?https://blogs.oracle.com/optimizer/entry/how_do_i_migrate_stored
Global Temporary Tables and Upgrading to Oracle Database 12c - Don't get caught outhttps://blogs.oracle.com/optimizer/post/global-temporary-tables-and-upgrading-to-oracle-database-12c-dont-get-caught-out

 

Blogs: SQL Patches

 

Article TitleURL
Using SQL Patch to add hints to a packaged applicationhttps://blogs.oracle.com/optimizer/post/using-sql-patch-to-add-hints-to-a-packaged-application
Additional Information on SQL Patcheshttps://blogs.oracle.com/optimizer/post/additional-information-on-sql-patches
Adding and Disabling Hints Using SQL Patchhttps://blogs.oracle.com/optimizer/post/adding-and-disabling-hints-using-sql-patch

 

Blogs: Queries and Plans

 

Article TitleURL
Statistics Feedback (Formerly Cardinality Feedback)http://blogs.oracle.com/optimizer/post/statistics-feedback-formerly-cardinality-feedback
Dynamic sampling and its impact on the Optimizerhttp://blogs.oracle.com/optimizer/post/dynamic-sampling-and-its-impact-on-the-optimizer
Explain the Explain Planhttp://blogs.oracle.com/optimizer/post/how-do-i-display-and-read-the-execution-plans-for-a-sql-statement
Improving Cardinality Estimates with Dynamic Statisticshttps://blogs.oracle.com/optimizer/post/cardinality-and-dynamic-statistics
How do I know if the cardinality estimates in a plan are accurate?https://blogs.oracle.com/optimizer/post/how-do-i-know-if-the-cardinality-estimates-in-a-plan-are-accurate
Why do I have SQL statement plans that change for the worse?https://blogs.oracle.com/optimizer/post/why-do-i-have-sql-statement-plans-that-change-for-the-worse

SQL Plan Management and Auto Capture

https://blogs.oracle.com/optimizer/post/what-you-need-to-know-about-sql-plan-management-and-auto-capture




Comments

Post a Comment