Vendor SQL Performance, Improving

Overview

The methods here can help vendor SQL run more optimally.

Identify Long Running SQL

1. Use this script and\or create an AWR.
2. Make sure to get the SQL_ID of the problem SQL.
   • sql.LongRunning.sql
   • sql.sql_id.sql

Both scripts updated to show PHV too.

Example Output

In this example perhaps 7sqr390wdcq9x can be made to run faster.

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.

COL TABLE_NAME        FORMAT a35
COL LAST_STATS_UPDATE FORMAT a35
SELECT TABLE_NAME, MAX(STATS_UPDATE_TIME) AS LAST_STATS_UPDATE
FROM DBA_TAB_STATS_HISTORY
WHERE OWNER = 'SCOTT'
GROUP BY TABLE_NAME
ORDER BY LAST_STATS_UPDATE DESC;

Change SCOTT to your schema name.

Create an AWR

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

For completeness:

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.

While vendor SQL cannot be changed, Oracle University teaches these as the holy grail of SQL coding.

If the AWR recommendations do not have an impact, create a SQL Profile for it (below).

SQL Profiles

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

SQL Profile Fun Facts!

SQLTXPLAIN (SQLT) Tool Installation

• 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

• Create Directory for COE Files
  mkdir C:\app\sqlt\utl\coe
• 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 Output

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

Create SQL Profile

Use sql.LongRunning.sql or AWR to get the problem SQL SQL_ID.

If PHV and SQL_ID does not exist:

• Get SQL_ID from SQL here.

Get PHV & 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';

Note if a SQL_PROFILE already exists for the matching SQL_ID.

Example Output

 SQL_ID         PLAN_HASH_VALUE SQL_PROFILE
 dbagj34vdd6ws  3472386639

Get PHV, Option 2

SELECT 
    ss.PLAN_HASH_VALUE,
    COUNT(*) AS EXECUTIONS,
    SUM(ss.ELAPSED_TIME_DELTA) AS TOTAL_ELAPSED_TIME,
    AVG(ss.ELAPSED_TIME_DELTA) AS AVG_ELAPSED_TIME,
    SUM(ss.BUFFER_GETS_DELTA) AS TOTAL_BUFFER_GETS,
    AVG(ss.BUFFER_GETS_DELTA) AS AVG_BUFFER_GETS
FROM 
    DBA_HIST_SQLSTAT ss
WHERE 
    ss.SQL_ID = 'aywu6x2xg2amk'
GROUP BY 
    ss.PLAN_HASH_VALUE
ORDER BY 
    EXECUTIONS DESC;

Example Output

 PLAN_HASH_VALUE EXECUTIONS TOTAL_ELAPSED_TIME AVG_ELAPSED_TIME TOTAL_BUFFER_GETS AVG_BUFFER_GETS
 --------------- ---------- ------------------ ---------------- ----------------- ---------------
      871306787          1         1365109871       1365109871         819289261       819289261
     4220755589          1                836              836                 0               0

If more than one, use the one with the best performance.

Run SQL Profile Creation Script

 cd C:\app\sqlt\coe
 sqlplus SQLT_USER/********@MYDB 
 SQLT_USER> @C:\app\sqlt\utl\coe_xfr_sql_profile.sql
     Parameter 1:
     SQL_ID (required)
     Enter value for 1: c53k9q5pxkdam

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

     Process runs... 

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

Create SQL Profile Using Above Created Script

 Format: SQLT_USER> @C:\app\sqlt\coe\coe_xfr_sql_profile_<SQL_ID>_<PHV>.sql
 @C:\app\sqlt\coe\coe_xfr_sql_profile_c53k9q5pxkdam_4220755589.sql

 Process runs...
 ... manual custom SQL Profile has been created
 COE_XFR_SQL_PROFILE_c53k9q5pxkdam_4220755589 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 4220755589

  -- Flush using Above Values
  exec DBMS_SHARED_POOL.PURGE ('00000000D63B65F0, 4220755589', '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>

 -- Confirm Cursor Exists for SQL_ID
 SELECT * from table(dbms_xplan.display_cursor('c53k9q5pxkdam',null,'ADVANCED ALLSTATS LAST')); 
 Should show PLAN_TABLE_OUTPUT and more.
 PLAN_TABLE_OUTPUT...

 -- Confirm New 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      4220755589 coe_c53k9q5pxkdam_4220755589 

The above will show output AFTER your SQL runs post profile creation.

View, Disable, Drop a Profile

View

 COL created        FORMAT a15 HEAD 'Created'
 COL name           FORMAT a35 HEAD 'Name'
 COL status         FORMAT a15 HEAD 'Status'
 COL force_matching FORMAT a15 HEAD 'Force_Matching'
 SELECT to_char(created, 'YYYY-MM-DD') AS created, name, status , force_matching
 FROM dba_sql_profiles
 ORDER BY created DESC, name;
 Created     Name                   Status          Force_Matching
 ----------- ---------------------- --------------- ---------------
 2022-10-04  6v7wn7muym6nf_GLRECON  ENABLED         NO
 2022-10-04  PROFILE_fbdp2bjab9g9j  ENABLED         YES

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

When PLAN_HASH_VALUE is 0

When PLAN_HASH_VALUE is 0, it typically indicates that Oracle was unable to generate an execution plan for the SQL statement. This can happen for several reasons:

• Invalid SQL Statement: The SQL statement might be syntactically incorrect or incomplete, preventing Oracle from generating a valid execution plan.
• Dynamic Sampling Failure: If Oracle uses dynamic sampling and it fails for some reason, it might result in a PLAN_HASH_VALUE of 0.
• Permissions Issues: If the user running the query does not have the necessary permissions to access objects involved in the query, it could prevent the generation of an execution plan.
• Unsupported SQL Constructs: Certain SQL constructs might not be supported by the optimizer for plan generation, leading to a PLAN_HASH_VALUE of 0.

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;"

Scripts

• sql.LongRunning.sql
• sql.sql_id.sql

SQL Profile Cheat Sheet

 -- Identify SQL and Get SQL_ID
 SYS> @sql.LongRunning.sql

 -- Get PHV
 If PHV is 0 the SQL is invalid and cannot be improved.

 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='&vSQL_ID';

 -- Is this the Only or Best PHV?
 SELECT 
    ss.PLAN_HASH_VALUE, COUNT(*) AS EXECUTIONS,
    SUM(ss.ELAPSED_TIME_DELTA) AS TOTAL_ELAPSED_TIME, 
    AVG(ss.ELAPSED_TIME_DELTA) AS AVG_ELAPSED_TIME,
    SUM(ss.BUFFER_GETS_DELTA) AS TOTAL_BUFFER_GETS,   
    AVG(ss.BUFFER_GETS_DELTA) AS AVG_BUFFER_GETS
 FROM 
    DBA_HIST_SQLSTAT ss
 WHERE 
    ss.SQL_ID='&vSQL_ID'
 GROUP BY 
    ss.PLAN_HASH_VALUE
 ORDER BY 
    EXECUTIONS DESC;

 -- Create COE Script (as SQLT_USER)
 cd C:\app\sqlt\coe
 sqlplus SQLT_USER/********@MYDB
 SQLT_USER> @C:\app\sqlt\utl\coe_xfr_sql_profile.sql

 -- Run COE Script
 SQLT_USER> @C:\app\sqlt\coe\coe_xfr_sql_profile_nnn_nnn.sql


 -- Flush SQL Statement from Library Cache (as SYS)
 SYS>%ORACLE_HOME%\BIN\sqlplus.exe sys/********@MYDB as sysdba

      - Get ADDRESS and HASH_VALUE
      SYS> SELECT ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '&vSQL_ID';

      ADDRESS          HASH_VALUE
      ---------------- ----------------
      nnnADDRESSnnn    nnnHASH_VALUEnnn

      - Flush
      SYS> exec DBMS_SHARED_POOL.PURGE ('&vADDRESS, &vHASH_VALUE', 'C');

 -- Run Your SQL
 <RunYourSQL>

 -- Check Cursor\Profile
     - Confirm Cursor Exists for SQL_ID
     SELECT * from table(dbms_xplan.display_cursor 
     ('&vSQL_ID',null,'ADVANCED ALLSTATS LAST')); 
     PLAN_TABLE_OUTPUT...

     - Confirm New SQL Profile Exist
     SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PROFILE 
     FROM V$SQL where sql_id='&vSQL_ID'; 

 -- Recheck SQL Performance
 @sql.sql_id.sql

 Did it run any faster?

 If not, delete:
 EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(Name => '&VSQL_PROFILE>');

Adaptive Query Optimization (AQO)

Oracle 19c's Adaptive Query Optimization (AQO) can be helpful. With AQO enabled, the optimizer gathers runtime statistics and might choose a different plan even with a profile in place.

You can monitor which plan AQO selects and its performance to see if it aligns with either profile.

SELECT value FROM v$parameter WHERE name = 'optimizer_adaptive_mode';

show parameter optimizer_adaptive_mode;
show parameter optimizer_mode;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

SELECT value FROM v$parameter WHERE name = 'optimizer_mode';

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)