Skip to content

Category: Tuning Scripts

Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC)

This 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

[Post Views: 17]

Leave a Comment

Move SQL PROFILE to other database

A)  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)

[Post Views: 289]

Leave a Comment

Flush Bad SQL Plan from Shared Pool

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%';

ADDRESS          HASH_VALUE
---------------- ----------
000000085FD77CF0  808321886

 

[Post Views: 1165]

Leave a Comment

Find SQL_ID of a SQL Statement in a PL/SQL Block

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: 338]

1 Comment
error: Alert: Content is protected !!