Vendor SQL Performance, Improving

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

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
...

3. Are there any SQL findings that are causing performance issues?
4. 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>

5. With the SQL_ID, find the associated SQL command.
   Ctrl-F to find then paste: 99abcdefghijk
6. 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.
7. 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!

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

• Purge Old SQLTXPLAIN/SQLTXADMIN User\Schema

• 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.

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)