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.
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.
- Ensure there are no syntax errors in the SQL.
- If large dataset, ensure there are no UNION statements.
- 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.
- Run SQL Developer
- Open database.
- Paste SQL into SQL Worksheet tab.
- From SQL Worksheet, select entire SQL command.
- 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)