Skip to content
Oracle Database supports the export and import of SQL plan baselines using the Oracle Data Pump Import and Export utilities. Use the DBMS_SPM package to define a staging table, which you can use to pack and unpack SQL plan baselines.
To import a set of SQL plan baselines from one system to another:
[Post Views: 657]
Sometime DBAs need to create SQL Baseline for particular SQL_ID for plan stabilization
1) VERIFY CURRENTLY EXISTING SQL PLAN BASELINES select * from dba_sql_plan_baselines;
[Post Views: 1023]
Tuning task for specific SQL_ID can be created by SQLTRPT very easily.
Manual way is also documented below:
[Post Views: 232]
TO ACCPET A SQL PROFILE SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_96’,replace => TRUE);
If you want the profile to be registered with a custom name use,
SQL> execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_96’,name => ‘CUSTOM_PROF_Query1’);
[Post Views: 118]
Sometimes it is required to flush the BAD SQL_PLAN from shared_pool so that new (or old) better execution plan can be picked by SQL_ID
1) FIND ADDRESS AND HASH_VALUE OF SQL_ID SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%';
[Post Views: 2008]
Finding the SQL_ID for a SQL Statement is straight forward task. You can use query like below to find the SQL_ID
SQL> SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%&An_Identifiable_String%';
To Determine the SQL_ID of a SQL Statement in a PL/SQL Block below steps can be followed:
[Post Views: 409]
A normal SQL statement go through below phase before producing results.
1) PARSE PHASE
Parsing stage will tell what does the statement mean and how best to run it
[Post Views: 133]
Posted by Arindam Mukherjee Recently I encountered an interesting performance issue while working with Oracle EBS seeded components and these issues are tied to wrong use…
error: Alert: Content is protected !!