oracledba.help
Legacy

Vendor SQL Performance, Improving

🠊 This page does not directly cover application development or writing SQL. It helps you improve performance of non-modifiable (vendor) SQL.

Overview

The methods here can help slow running vendor SQL to run optimally.

Identify Long Running SQL

  1. This script might helpful determine if your SQL\job is taking too long. Otherwise create an AWR.
  2. If yes, get the SQL_ID of the problem SQL.
-- sql.LongRunning.sql
-- Purpose: Show SQL running longer than 1 minute within the last day.
-- -----------------------------------------------------------------------------
set linesize 240
set pagesize 9999
set verify off

COL username        FORMAT a15
COL module          FORMAT a20
COL sid             FORMAT 999999
COL SESSION_SERIAL# FORMAT 999999
COL action          FORMAT a25
COL CMD             FORMAT a55

-- Process
SELECT to_char(sql_exec_start, 'YYYY-MM-DD HH24:MI') as "STARTED", 
       round(elapsed_time/60000000,2) as "ELAPSE", 
       round(cpu_time/1000000/60, 2)  as "CPU", 
       sid, 
       session_serial# as "SERIAL#", 
       sql_id, 
       username, 
       substr(module,1,20) as "MODULE", 
       '...' || substr(program,-20) as "PROGRAM", 
       status, 
       substr(action,1,20) as "ACTION" 
FROM   v$sql_monitor
WHERE  elapsed_time > 60000000 
AND    status   LIKE '%DONE%' 
AND    username NOT IN('SYS','SYSTEM','COREUSER','OSIATM') 
AND    sql_exec_start >= sysdate - 1
ORDER BY sql_exec_start DESC;

-- End
prompt
prompt "*** Process Ended ***"
prompt

Example Output

STARTEDELAPSECPUSIDSERIAL#SQL_IDUSERNAMEMODULEPROGRAMSTATUSACTION
2024-05-14 12:101:261.54116118879dbpwmptks2rvpBSMITHSQL Developer...DONE (ERROR) 
2024-05-14 11:0538.621.9960186837sqr390wdcq9xAWBATCHsqr7wt.exe ...DONE (ALL ROWS)CN_TRIAL
2024-05-14 11:1210:101.52229411355gpgjuuxpmpq73POWERBIMicrosoft.Mashup.Con...ontainer.NetFX45.exeDONE (ALL ROWS) 
2024-05-14 10:199:0536.79496142887wd05prru6tdvOSIBANKruby@abcscriptsv01....cu.local (TNS V1-V3)DONE (ALL ROWS) 
2024-05-14 09:472:5517.6112945686b6dhab1d4qu6rBIDBMS_SCHEDULER...01.sccu.local (J001)DONE (ALL ROWS)MM_REGD

CBO Statistics

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

Review the SQL

  1. Create an AWR during the period of time you are experiencing the issue.
  2. 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
...
  1. Are there any SQL findings that are causing performance issues?
  2. 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>
  1. With the SQL_ID, find the associated SQL command.
    Ctrl-F to find then paste: 99abcdefghijk
  2. 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.
  3. 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
SQL> @C:\app\sqlt\install\sqdrop.sql
  • Create the new dedicated user for SQLT
CREATE USER sqlt_user IDENTIFIED BY "********";
GRANT CONNECT, RESOURCE TO sqlt_user;

GRANT SELECT ANY DICTIONARY TO sqlt_user;
GRANT EXECUTE ON SYS.DBMS_LOCK TO sqlt_user;
GRANT EXECUTE ON SYS.DBMS_SQLTUNE TO sqlt_user;
GRANT EXECUTE ON SYS.DBMS_ADVISOR TO sqlt_user;
GRANT SELECT ON SYS.V_$SESSION TO sqlt_user;
GRANT SELECT ON SYS.V_$SQL TO sqlt_user;
GRANT ADMINISTER SQL MANAGEMENT OBJECT TO sqlt_user;
GRANT EXECUTE ON DBMS_SQLDIAG TO sqlt_user;
GRANT EXECUTE ON DBMS_SQLTUNE TO sqlt_user;
GRANT SELECT ANY TABLE TO sqlt_user;
GRANT UNLIMITED TABLESPACE TO sqlt_user;
  • Install SQLT into Database Creates SQLTXPLAIN schema\user.
SYS> @C:\app\sqlt\install\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: SQLT_USER
  • 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';

Verify

SELECT
'SQLT version number: '||sqltxplain.sqlt$a.get_param('tool_version')||CHR(10)||
'SQLT version date : '||sqltxplain.sqlt$a.get_param('tool_date')||CHR(10)||
'Installation date : '||sqltxplain.sqlt$a.get_param('install_date') sqlt_version
FROM DUAL;

Example

 SQLT version number: 19.1.200226
 SSQLT version date : 2020-02-26
 SInstallation date : 2024-05-20/09:59:10

Process

Get the SQL_ID

Option A

  1. From SQL Developer run: Tools 🠊 Real Time SQL Monitor
  2. 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

  1. From the AWR or gv$active_session_history get slow running SQL.
  2. 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)