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
Index Selectivity And Queries With Multiple Equality Conditions
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
Unselective Indexes: Selectivity
Unselective Indexes And Skewed Data Distribution

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

    Reply

    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

      Reply

Your comments and suggestions are welcome!