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.

Get Explain Plan
Troubleshooting Profile
References

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 Oracle's SQLTXPLAIN (SQLT) Tool.
Example: DL zip file to /u01/orasw
Unzip install file.

oracle> cd /u01/orasw
oracle> unzip sqlt_10g_11g_12c_18c_19c_5th_June_2020.zip

Sub dir created: /u01/orasw/sqlt

Install SQLT into Database
Creates SQLTXPLAIN schema\user.

cd /u01/orasw/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)
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

1. Get the SQL_ID

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

Option B

From SQLPlus or SQL Developer run the SQL.
Then run: SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ALL'));
The SQL_ID will be displayed with other info.

2. Get the PLAN_HASH_VALUE (PHV)

 SELECT DISTINCT sql_id, plan_hash_value
 FROM dba_hist_sqlstat dhs,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
    ) s
 WHERE dhs.snap_id BETWEEN s.min_snap AND s.max_snap
 AND dhs.sql_id IN ( '&SQLID');

 PLAN_HASH_VALUE
 ---------------
      979439151

3. Check the Profile 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='0w02s6dr9wb5d';

  SQL_ID        SQL_PROFILE                        PLAN_HASH_VALUE
  ------------- ---------------------------------- ---------------
  0w02s6dr9wb5d                                    979439151
  Shows no profile being used.

4. Build Profile Creation Script

 linux> locate coe_xfr_sql_profile.sql
 /u01/orasw/sqlt/utl/coe_xfr_sql_profile.sql

 linux> cd /u01/orasw/sqlt/utl
 SQL> @coe_xfr_sql_profile.sql <SQL_ID> <Hash>
 /u01/orasw/sqlt/utl/coe_xfr_sql_profile.sql 0w02s6dr9wb5d 979439151

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

5. Run Profile Creation Script

With above session still open:

 SQL> @coe_xfr_sql_profile_0w02s6dr9wb5d_979439151.sql
 ... manual custom SQL Profile has been created
 COE_XFR_SQL_PROFILE_0w02s6dr9wb5d_979439151 completed

6. Ensure the 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='0w02s6dr9wb5d';

 SQL_ID        SQL_PROFILE                        PLAN_HASH_VALUE
 ------------- ---------------------------------- ---------------
 0w02s6dr9wb5d coe_bfpbgkqs6hs3f_4122059633       979439151

Troubleshooting Profile

 -- Does New Profile Exist?
 select name from dba_sql_profiles;
 NAME
 ----------------------------
 coe_6b8ncu2mt5v88_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='6b8ncu2mt5v88';
 SQL_ID           SQL_PROFILE                    PLAN_HASH_VALUE
 ---------------- ------------------------------ ---------------
 6b8ncu2mt5v88                                        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 '6b8ncu2mt5v88';
 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='6b8ncu2mt5v88';

 SQL_ID        SQL_PROFILE                        PLAN_HASH_VALUE
 ------------- ---------------------------------- ---------------
 6b8ncu2mt5v88 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_6b8ncu2mt5v88_2331001316.sql

  vi coe_xfr_sql_profile_6b8ncu2mt5v88_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).

References

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)