TUNING

Always FTS On A Table Smaller Than DBFMRC : A Myth 
Buffer Cache Wait Events
Conditions Based On Inequalities Can’t Use Indexes – How To Resolve?
Clustering Factor Demystified Part – I 
ClusteringFactor Demystified Part – II 
ClusteringFactor Demystified Part – III 
Cursor Sharing Demystified  
DB_FILE_MULTIBLOCK_READ_COUNT And Extent Size
Direct Read Enhancements in 11g
Find Out Values Of Bind Variables Across Multiple Executions Of A SQL
Find Values Of Another Session’s Parameters
Identify difference in CBO parameters across two executions of a SQL
Identify The Database Owning A CPU Intensive Process
Library Cache Lock And Pin Demonstrated
Latches, Locks, Pins And Mutexes
Multiblock Reads And Cached Blocks
Not NULL Constraint Influences Access Path
Oracle Checkpoints
Oracle 11g : Automatic DOP – Parallel Threshold
Oracle 11g :  Parallel Statement Queueing
Parent And Child Cursors In Oracle
Quick Overview Of Shared Pool Tuning
Session Cursor Caching – Part-I
Session Cursor Caching – Part-II
Simulate Buffer Busy Wait and Identify Hot Object
Single Table Index Cluster Tables
Single Table Hash Clusters Demystified
Shared Pool Architecture 
SQL Profile Demystified : Part – I
SQL Profile Demystified : Part – II
Tuning  PGA : Part – I 
Tuning  PGA : Part – II 
Tuning  PGA : Part – III 
Tuning RMAN Part – I  
Tuning RMAN Part – II 
Tuning Shared Pool : A Demonstration 
Undocumented Parameters in Oracle 11g

4 thoughts on “TUNING

  1. DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE. Using either of these methods you have three choices:

    1.Accept the plan only if it performs better than the existing SQL plan baseline
    2.Accept the plan without doing performance verification
    3.Run the performance comparison and generate a report without evolving the new plan. out evolving the new plan.

    My question is If I want to use 1 option. how can i set the value.

    thanks is advance

    1. Hi Manoj
      Pls refer to oracle documentation:
      https://docs.oracle.com/database/121/ARPLS/d_spm.htm#ARPLS68166
      This function evolves SQL plan baselines associated with one or more SQL statements. A SQL plan baseline is evolved when one or more of its non-accepted plans is changed to an accepted plan or plans. If interrogated by the user (parameter verify = ‘YES’), the execution performance of each non-accepted plan is compared against the performance of a plan chosen from the associated SQL plan baseline. If the non-accepted plan performance is found to be better than SQL plan baseline performance, the non-accepted plan is changed to an accepted plan provided such action is permitted by the user (parameter commit = ‘YES’).
      Since default value of parameters COMMIT and VERIFY is ‘Yes’, you just need issue a statement as shown below if you want to change all the better performing unaccepted plans for specified sql_handle to accepted .

      SET LONG 10000
      SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => ””)
      FROM dual;

      In case you want to do it for a specified plan, add plan_name parameter as well:
      SET LONG 10000
      SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => ‘‘,
      plan_name => ‘ ‘)
      FROM dual;

      Hope it helps.

      Your comments and suggestions are always welcome.

      regards
      Anju Garg

Your comments and suggestions are welcome!