Difference Between ASMM & AMM
Automatic Shared Memory Management
Automatic Memory Management
Friday, January 29, 2021
About SQL Hints
About SQL Hints
* A hint is an instruction to the optimizer to follow an ‘application developer desired’ execution plan
* Make decisions for the optimizer because you have more in-depth knowledge about data distribution patterns
* In a test or development environments, hints are useful for testing the performance of a specific access path- test use of a specific index when there are several indexes defined on the same table
* Changes in the database or host environment can make hints obsolete and even have negative consequences
* Recommended to use tools like SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to address performance problems not solved by the optimizer before considering hints
How do we gather optimizer statistics?
How do we gather optimizer statistics?
* By default, Oracle Database uses automatic optimizer statistics collection
* Database automatically runs DBMS_STATS to collect optimizer statistics for all schema objects for which statistics are missing or stale
* You can also update and manage optimizer statistics by manually executing DBMS_STATS
* Also, when optimizer statistics are missing, stale, or insufficient, the database automatically gathers dynamic statistics during a parse (depends on level of dynamic sampling)
What is An Optimizer ?
What is An Optimizer ?
* Generates the optimal execution plan
* Determines the most efficient method for a SQL statement to access requested data
* The optimizer choose the plan with the lowest cost among all considered candidate plans
* The optimizer uses available statistics to calculate cost
* Cost computation accounts for factors of query execution like I/O , CPU, Network, Memory
Reasons Of Changing Execution Plans ?
Reasons Of Changing Execution Plans ?
* Stale or missing statistics
* Have gathered fresh statistics
* Optimizer engine has changed after a database upgrade
* Index has been added or dropped or Table structure has changed
* Database optimizer related init.ora parameters have been changed
* Parsed representation of the SQL statement is not in the Library Cache – statements are aged out
* Reparsing the same SQL statement now leads to generation of a new plan
Difference Between Execution plan & Explain plan ?
Difference Between Execution plan & Explain plan ?
Execution Plan?
* The execution plan for a SQL statement is a set of instructions.
* Tell the database how to access the data and join it together.
Explain Plan?
* An explain plan predicts how Oracle will process your query
* An execution plan describes the steps it actually took