Here are a few scripts that can help you find the number of executions of a specific SQL_ID in an Oracle database: Scripts based on…
Leave a CommentCategory: SQL Tuning
In an Oracle Database, the GC buffer busy acquire wait event occurs when a session tries to acquire a buffer from the buffer cache and…
1 CommentThe below script can be used to run SQL tuning advisor manually. CREATE TUNING TASK DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id =>…
Leave a CommentThe “library cache pin” wait event in an Oracle database occurs when a session is waiting to acquire a shared lock on an object in…
Leave a CommentPL/SQL lock timer wait event in Oracle occurs when a session is waiting for a lock held by another session to be released. This can…
Leave a CommentThis post will cover the SQL Tuning Health Check script basics and how you can use it to collect the key information regarding a poorly performing SQL. SQLHC helps you on focusing on specific SQL and checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance.
Unlike SQLT, this tool SQLHC do not require any code to be installed in the database to provide recommendation. SQLHC uses the SQL_ID of a statement that has already been executed and is in memory to generate the report so it do not actually execute the SQL all lover again. SQLHC works 10g and above. SQLHC is also RAC aware
6 CommentsA) CREATE STAGING TABLE
First Create the staging table which will hold sql profiles to be moved over.
SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>’SQL_STG_TAB’,schema_name=>’SYS’);
(This will create a table SQL_STG_TAB, which will be able to hold the required sql profiles, the structure of this table is exactly same as that of DBA_SQL_PROFILES table)
Leave a CommentOracle 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:
10 CommentsSometime 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;
7 Comments
Tuning task for specific SQL_ID can be created by SQLTRPT very easily.
Manual way is also documented below:
Leave a Comment