TroubleShooting

SQL Performance, Improving

🠊 This page does not directly cover application development or writing SQL. It helps you improve performance of already existing SQL.

Overview

The methods covered here can help slow running SQL run optimally.

Update CBO Statistics
Create AWR
Create a SQL Profile
View, Disable, Drop a Profile

Get Explain Plan
Troubleshooting Profile
References
SQL_ID, Get Before Running SQL
SQL_ID Flipped to diff\suboptimal Exec Plan or PHV

CBO Statistics

Ensure the Cost Based Optimizer (CBO) stats are up-to-date.
If SQL related performance issue still exists go to next steps.

Review the SQL

Create an AWR during the period of time you are experiencing the issue.
In the AWR go to Findings and Recommendations.
Ctrl-F to find then paste: Recommendations

Summary of Findings
-------------------
   Description         Active Sessions      Recommendations
                       Percent of Activity
   ------------------  -------------------  ---------------
1  Top SQL Statements  nn.nn | nn.nn        n
...

Are there any SQL findings that are causing performance issues?
If yes, get the SQL_ID of the identified SQL.

   Recommendation 1: SQL Tuning
   Estimated benefit is n.nn active sessions, nn.nn% of total activity.
   --------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the SELECT statement with SQL_ID
      "99abcdefghijk".
      Related Object
      <Full SQL Statement Here>

With the SQL_ID, find the associated SQL command.
Ctrl-F to find then paste: 99abcdefghijk
Review the SQL to see if there are any obvious issues or oversights.
1. Ensure there are no syntax errors in the SQL.
2. If large dataset, ensure there are no UNION statements.
3. Ensure bind variables are being used.
If your SQL is still running slow create a profile for it.

Create a SQL Profile

SQL profiles help the optimizer use the best plan. This can have a significant improvement in performance of related SQL.

SQL Profile Fun Facts!

The SQL Tuning Advisor compares optimizer stats with values obtained by executing fragments of the statement on a data sample. When significant variances are found, SQL Tuning Advisor sets corrective actions together in a SQL profile, and then recommends its acceptance.

The corrected statistics in a SQL profile can improve optimizer cardinality estimates, which in turn leads the optimizer to select better plans.
SQL profiles fix incorrect estimates while giving the optimizer the flexibility to pick the best plan in different situations.
No changes to application source code are necessary. SQL profiles are transparent to the user.
If RAC, use gV$SQL correspondingly.

Prerequisite

Download SQLTXPLAIN (SQLT) tool via: 215187.1.
Example: DL zip file to C:\app
Unzip install file.

oracle> cd C:\app
oracle> unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip

Sub dir created: C:\app\sqlt

Purge Old SQLTXPLAIN/SQLTXADMIN User\Schema

cd C:\app\sqlt\install
sqlplus / as sysdba
SQL> START sqdrop.sql

Install SQLT into Database Creates SQLTXPLAIN schema\user.

cd C:\app\sqlt\install
sqlplus / as sysdba
SQL> START sqcreate.sql

Optional Connect Identifier (ie: @PROD): Enter
Password for user SQLTXPLAIN: ********
Do you want to see the free space of each tablespace...: Enter
Default tablespace [UNKNOWN]: USERS
Temporary tablespace [UNKNOWN]: TEMP
Main application user of SQLT: SCOTT (initial schema experiencing issues)
- If DNA database use: OSIBANK
Oracle Pack license [T]: Enter (or option matching your license)
Process runs...
...
SQLT users must be granted SQLT_USER_ROLE before using this tool.
SQCREATE completed. Installation completed successfully.

* You can check the status of process here: tail -f /u01/orasw/sqlt/install/nnn_sqcobj.log

* SQLT_USER_ROLE Users: SELECT * FROM dba_role_privs WHERE granted_role='SQLT_USER_ROLE';

Process

Get the SQL_ID

Option A

From SQL Developer run: Tools 🠊 Real Time SQL Monitor
Short by Sql Id if it is not already visible.

Option B
From the AWR or gv$active_session_history get the SQL_ID for the slow running SQL.

Example used: c53k9q5pxkdam

Check the Profile Status

  COL PLAN_HASH_VALUE FORMAT 9999999999
  COL SQL_ID          FORMAT a16
  COL SQL_PROFILE     FORMAT a30
  SELECT SQL_ID, PLAN_HASH_VALUE,SQL_PROFILE 
  FROM V$SQL WHERE SQL_ID='c53k9q5pxkdam';
  no rows selected
  Shows no profile being used.

Run SQL Profile Creation Script

 cd C:\app\sqlt\utl
 sqlplus / as sysdba
 SQL> @coe_xfr_sql_profile.sql
     Parameter 1:
     SQL_ID (required)
     Enter value for 1: c53k9q5pxkdam

     PLAN_HASH_VALUE AVG_ET_SECS
     --------------- -----------
           532210296      59.9

     Parameter 2:
     PLAN_HASH_VALUE (required)
     Enter value for 2: 532210296

     Process runs... 

     Execute coe_xfr_sql_profile_c53k9q5pxkdam_532210296.sql
     on TARGET system in order to create a custom SQL Profile
     with plan 1833286388 linked to adjusted sql_text.

Create SQL Profile from Above Created Script

 SQL> @coe_xfr_sql_profile.sql <SQL_ID> <Hash>
 @coe_xfr_sql_profile_c53k9q5pxkdam_532210296.sql

 Process runs...
 ... manual custom SQL Profile has been created
 COE_XFR_SQL_PROFILE_c53k9q5pxkdam_532210296 completed

Flush SQL Statement from Library Cache

  SELECT ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'c53k9q5pxkdam';

  -- Get ADDRESS and HASH_VALUE 
      ADDRESS          HASH_VALUE
      ---------------- ----------
      00000000D63B65F0 1809397075

  -- Flush using Above Values
  exec DBMS_SHARED_POOL.PURGE ('00000000D63B65F0, 1809397075', 'C');

  -- Confirm Flushed
  SELECT ADDRESS, HASH_VALUE FROM V$SQLAREA WHERE SQL_ID like 'c53k9q5pxkdam';
  no rows selected

Run Your SQL and Check

 SQL> <MyPreviouslySlowSQL>

 -- Matching Cursor Running?
 SELECT * from table(dbms_xplan.display_cursor('c53k9q5pxkdam',null,'ADVANCED ALLSTATS LAST'));
 Should show PLAN_TABLE_OUTPUT and more.
 PLAN_TABLE_OUTPUT...

 --SQL Profile Exist?
 SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PROFILE FROM V$SQL where sql_id='c53k9q5pxkdam';
 SQL_ID           CHILD_NUMBER PLAN_HASH_VALUE SQL_PROFILE
 ---------------- ------------ --------------- ------------------------------
 c53k9q5pxkdam               0      532210296 coe_c53k9q5pxkdam_1833286388

View, Disable, Drop a Profile

View

 SELECT SQL_ID, SQL_PROFILE, PLAN_HASH_VALUE from V$SQL where SQL_ID='c53k9q5pxkdam';
 SQL_ID        SQL_PROFILE                                               PLAN_HASH_VALUE
 ------------- --------------------------------------------------------- ---------------
 c53k9q5pxkdam coe_c53k9q5pxkdam_2283431628                                   1166648666

Disable

 EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(Name => '<SQL PROFILE>', 
   Attribute_Name => 'STATUS', Value => 'DISABLED');

Get SQL_PROFILE from View example.

Drop

 EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(Name => '<SQL PROFILE>');

Get SQL_PROFILE from View example.

Troubleshooting Profile

 -- Does New Profile Exist?
 select name from dba_sql_profiles;
 NAME
 ----------------------------
 coe_c53k9q5pxkdam_2331001316

 -- Is New Profile Being Used?
 COL PLAN_HASH_VALUE FORMAT 9999999999
 COL SQL_ID          FORMAT a16
 COL SQL_PROFILE     FORMAT a30
 SELECT SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE 
 FROM V$SQL WHERE SQL_ID='c53k9q5pxkdam';
 SQL_ID           SQL_PROFILE                    PLAN_HASH_VALUE
 ---------------- ------------------------------ ---------------
 c53k9q5pxkdam                                        2331001316
 Shows SQL_PROFILE is not being used.

 -- If new plan is not being used (or null) flush the HASH VALUE 
 -- then restart the process.
 SELECT ADDRESS, HASH_VALUE FROM gV$SQLAREA where SQL_ID like 'c53k9q5pxkdam';
 ADDRESS          HASH_VALUE
 ---------------- ----------
 000000079A1E9340 2331001316

 -- Flush the single SQL statement from Library Cache.
 EXEC DBMS_SHARED_POOL.PURGE ('<ADDRESS>, <HASH_VALUE>', 'C');
 EXEC DBMS_SHARED_POOL.PURGE ('000000079A1E9340, 2331001316', 'C');

 Then restart your job\SQL and check the status.
 COL PLAN_HASH_VALUE FORMAT 9999999999
 COL SQL_ID          FORMAT a16
 COL SQL_PROFILE     FORMAT a30
 SELECT SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE 
 FROM V$SQL WHERE SQL_ID='c53k9q5pxkdam';

 SQL_ID        SQL_PROFILE                        PLAN_HASH_VALUE
 ------------- ---------------------------------- ---------------
 c53k9q5pxkdam coe_bfpbgkqs6hs3f_6b8ncu2mt5v88       2331001316
 Shows SQL_PROFILE is now being used.

Plan B = Force Creation

 cd /u01/orasw/sqlt/utl
 ls -lart coe_xfr_sql_profile_*
 coe_xfr_sql_profile_c53k9q5pxkdam_2331001316.sql

  vi coe_xfr_sql_profile_c53k9q5pxkdam_2331001316.sql
  force_match => TRUE
  Then restart your job\SQL and check status.

Get Explain Plan

From the AWR or gv$active_session_history get slow running SQL.
Get the execution plan the SQL is using.
1. Run SQL Developer
2. Open database.
3. Paste SQL into SQL Worksheet tab.
4. From SQL Worksheet, select entire SQL command.
5. From SQL Worksheet, select Explain Plan button or press (F10).

Get SQL_ID Before Running SQL

Method 1

 SQLPLUS> set feedback only sql_id
 SQLPLUS> SELECT sysdate from dual;
    1 row selected.
    SQL_ID: av96vadmyk12s

Method 2

DECLARE
  p_sql_id VARCHAR2(2000);
BEGIN
  p_sql_id := DBMS_SQL_TRANSLATOR.SQL_ID('SELECT * FROM EMP');
  DBMS_OUTPUT.PUT_LINE(p_sql_id);
END;

Method 3

 sqlcl -i my_script.sql -c "set feedback only sql_id;"

References

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)
Usage Example: START H:\app\sql\oracle\sqlhc\sqlhc.sql "T" c53k9q5pxkdam
Extract created zip file. Start with this file: sqlhc_yyyymmdd_hhmm_c53k9q5pxkdam_1_health_check.html
Oracle SQL Profile Docs
Create Manually

Oracle Docs
Encouraging CBO to Pickup a Better Execution Plan Using the COE XFR SQL Profile Script (Doc ID 1955195.1)
SQLT Usage Instructions (Doc ID 1614107.1)
FAQ: SQLT (SQLTXPLAIN) Frequently Asked Questions (Doc ID 1454160.1)