My Query Performs Horrible, Fix It!

Uncategorized 2 Minutes

Many years ago, I used to work as an operational DBA, and I remember colleagues asking the following:

My query runs very slow. The other day it all ran fine, but now it’s hanging. What’s going on? Fix it!

They might even start the occasional rant about relational doesn’t scale and all the other blah blah blah.

Sounds familiar? What if you could solve the problem by just running:

EXEC DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE('<sql_id');

The query runs at warp speed again, and the users are happy.

In Oracle Database 19.22.0, that’s possible.

What’s Going On

The procedure uses SQL Plan Management: the best solution to ensure plan stability.

It will:

  • Search all your SQL tuning sets, including automatic SQL tuning sets, AWR, and cursor cache to find other plans for that SQL.
  • Test each of the plans to find the best one.
  • Create a SQL plan baseline with the best plan.

During the next execution, the database will use the best plan.

Problem solved!

SQL Plan Management

I’m a huge fan of SQL Plan Management. It is one of the most underrated features in Oracle Database.

What is your biggest fear when you upgrade? Often, the answer is changing plans. SQL Plan Management effectively solves that problem, and we recommend it as part of our performance stability prescription.

Afterwords

In fact, I’ve never been a performance expert. When I was tasked with a performance issue, I knew it would take a lot of time.

Over time, I found a better way of solving these issues. I would turn my chair and look directly into the wrinkled eyes of Frank, my trusted grey-haired colleague.

Using my cutest little puppy face and soft voice, young me would ask for advice and smoothly hand over the task to him.

But not everyone has a Frank in your office, so DBMS_SPM.ADD_VERIFIED_SQL_PLAN_BASELINE is very useful.

Experience and grey hair rock!

Further Reading

Published

4 thoughts on “My Query Performs Horrible, Fix It!”

  1. I can’t find the procedure in my 19.22 database:

    $ sqlplus / as sysdba

    SQL*Plus: Release 19.0.0.0.0 – Production on Tue Apr 2 12:28:52 2024
    Version 19.22.0.0.0

    Copyright (c) 1982, 2023, Oracle. All rights reserved.

    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
    Version 19.22.0.0.0

    SQL> desc dbms_spm
    PROCEDURE ACCEPT_SQL_PLAN_BASELINE
    Argument Name Type In/Out Default?

    TASK_NAME VARCHAR2 IN
    OBJECT_ID NUMBER IN
    TASK_OWNER VARCHAR2 IN DEFAULT
    FORCE BOOLEAN IN DEFAULT
    FUNCTION ALTER_SQL_PLAN_BASELINE RETURNS BINARY_INTEGER
    Argument Name Type In/Out Default?

    SQL_HANDLE VARCHAR2 IN DEFAULT
    PLAN_NAME VARCHAR2 IN DEFAULT
    ATTRIBUTE_NAME VARCHAR2 IN
    ATTRIBUTE_VALUE VARCHAR2 IN
    PROCEDURE CANCEL_EVOLVE_TASK
    Argument Name Type In/Out Default?

    TASK_NAME VARCHAR2 IN
    PROCEDURE CONFIGURE
    Argument Name Type In/Out Default?

    PARAMETER_NAME VARCHAR2 IN
    PARAMETER_VALUE VARCHAR2 IN DEFAULT
    ALLOW BOOLEAN IN DEFAULT
    FUNCTION CREATE_EVOLVE_TASK RETURNS VARCHAR2
    Argument Name Type In/Out Default?

    SQL_HANDLE VARCHAR2 IN DEFAULT
    PLAN_NAME VARCHAR2 IN DEFAULT
    TIME_LIMIT NUMBER IN DEFAULT
    TASK_NAME VARCHAR2 IN DEFAULT
    DESCRIPTION VARCHAR2 IN DEFAULT
    FUNCTION CREATE_EVOLVE_TASK RETURNS VARCHAR2
    Argument Name Type In/Out Default?

    PLAN_LIST NAME_LIST IN
    TIME_LIMIT NUMBER IN DEFAULT
    TASK_NAME VARCHAR2 IN DEFAULT
    DESCRIPTION VARCHAR2 IN DEFAULT
    PROCEDURE CREATE_STGTAB_BASELINE
    Argument Name Type In/Out Default?

    TABLE_NAME VARCHAR2 IN
    TABLE_OWNER VARCHAR2 IN DEFAULT
    TABLESPACE_NAME VARCHAR2 IN DEFAULT
    PROCEDURE DROP_EVOLVE_TASK
    Argument Name Type In/Out Default?

    TASK_NAME VARCHAR2 IN
    FUNCTION DROP_MIGRATED_STORED_OUTLINE RETURNS BINARY_INTEGER
    FUNCTION DROP_SQL_PLAN_BASELINE RETURNS BINARY_INTEGER
    Argument Name Type In/Out Default?

    SQL_HANDLE VARCHAR2 IN DEFAULT
    PLAN_NAME VARCHAR2 IN DEFAULT
    FUNCTION EVOLVE_SQL_PLAN_BASELINE RETURNS CLOB
    Argument Name Type In/Out Default?

    SQL_HANDLE VARCHAR2 IN DEFAULT
    PLAN_NAME VARCHAR2 IN DEFAULT
    TIME_LIMIT NUMBER(38) IN DEFAULT
    VERIFY VARCHAR2 IN DEFAULT
    COMMIT VARCHAR2 IN DEFAULT
    ALTERNATE_PLAN_LIMIT NUMBER IN DEFAULT
    FUNCTION EVOLVE_SQL_PLAN_BASELINE RETURNS CLOB
    Argument Name Type In/Out Default?

    PLAN_LIST NAME_LIST IN
    TIME_LIMIT NUMBER(38) IN DEFAULT
    VERIFY VARCHAR2 IN DEFAULT
    COMMIT VARCHAR2 IN DEFAULT
    FUNCTION EXECUTE_EVOLVE_TASK RETURNS VARCHAR2
    Argument Name Type In/Out Default?

    TASK_NAME VARCHAR2 IN
    EXECUTION_NAME VARCHAR2 IN DEFAULT
    EXECUTION_DESC VARCHAR2 IN DEFAULT
    FUNCTION IMPLEMENT_EVOLVE_TASK RETURNS NUMBER
    Argument Name Type In/Out Default?

    TASK_NAME VARCHAR2 IN
    TASK_OWNER VARCHAR2 IN DEFAULT
    EXECUTION_NAME VARCHAR2 IN DEFAULT
    FORCE BOOLEAN IN DEFAULT
    PROCEDURE INTERRUPT_EVOLVE_TASK
    Argument Name Type In/Out Default?

    TASK_NAME VARCHAR2 IN
    FUNCTION LOAD_PLANS_FROM_AWR RETURNS BINARY_INTEGER
    Argument Name Type In/Out Default?

    BEGIN_SNAP NUMBER IN
    END_SNAP NUMBER IN
    BASIC_FILTER VARCHAR2 IN DEFAULT
    FIXED VARCHAR2 IN DEFAULT
    ENABLED VARCHAR2 IN DEFAULT
    COMMIT_ROWS NUMBER IN DEFAULT
    DBID NUMBER IN DEFAULT
    FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
    Argument Name Type In/Out Default?

    ATTRIBUTE_NAME VARCHAR2 IN
    ATTRIBUTE_VALUE VARCHAR2 IN
    FIXED VARCHAR2 IN DEFAULT
    ENABLED VARCHAR2 IN DEFAULT
    FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
    Argument Name Type In/Out Default?

    SQL_ID VARCHAR2 IN
    PLAN_HASH_VALUE NUMBER IN DEFAULT
    FIXED VARCHAR2 IN DEFAULT
    ENABLED VARCHAR2 IN DEFAULT
    FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
    Argument Name Type In/Out Default?

    SQL_ID VARCHAR2 IN
    PLAN_HASH_VALUE NUMBER IN DEFAULT
    SQL_TEXT CLOB IN
    FIXED VARCHAR2 IN DEFAULT
    ENABLED VARCHAR2 IN DEFAULT
    FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
    Argument Name Type In/Out Default?

    SQL_ID VARCHAR2 IN
    PLAN_HASH_VALUE NUMBER IN DEFAULT
    SQL_HANDLE VARCHAR2 IN
    FIXED VARCHAR2 IN DEFAULT
    ENABLED VARCHAR2 IN DEFAULT
    FUNCTION LOAD_PLANS_FROM_SQLSET RETURNS BINARY_INTEGER
    Argument Name Type In/Out Default?

    SQLSET_NAME VARCHAR2 IN
    SQLSET_OWNER VARCHAR2 IN DEFAULT
    BASIC_FILTER VARCHAR2 IN DEFAULT
    FIXED VARCHAR2 IN DEFAULT
    ENABLED VARCHAR2 IN DEFAULT
    COMMIT_ROWS NUMBER IN DEFAULT
    FUNCTION MIGRATE_STORED_OUTLINE RETURNS CLOB
    Argument Name Type In/Out Default?

    ATTRIBUTE_NAME VARCHAR2 IN
    ATTRIBUTE_VALUE CLOB IN DEFAULT
    FIXED VARCHAR2 IN DEFAULT
    FUNCTION MIGRATE_STORED_OUTLINE RETURNS CLOB
    Argument Name Type In/Out Default?

    OUTLN_LIST NAME_LIST IN
    FIXED VARCHAR2 IN DEFAULT
    FUNCTION PACK_STGTAB_BASELINE RETURNS NUMBER
    Argument Name Type In/Out Default?

    TABLE_NAME VARCHAR2 IN
    TABLE_OWNER VARCHAR2 IN DEFAULT
    SQL_HANDLE VARCHAR2 IN DEFAULT
    PLAN_NAME VARCHAR2 IN DEFAULT
    SQL_TEXT CLOB IN DEFAULT
    CREATOR VARCHAR2 IN DEFAULT
    ORIGIN VARCHAR2 IN DEFAULT
    ENABLED VARCHAR2 IN DEFAULT
    ACCEPTED VARCHAR2 IN DEFAULT
    FIXED VARCHAR2 IN DEFAULT
    MODULE VARCHAR2 IN DEFAULT
    ACTION VARCHAR2 IN DEFAULT
    FUNCTION REPORT_AUTO_EVOLVE_TASK RETURNS CLOB
    Argument Name Type In/Out Default?

    TYPE VARCHAR2 IN DEFAULT
    LEVEL VARCHAR2 IN DEFAULT
    SECTION VARCHAR2 IN DEFAULT
    OBJECT_ID NUMBER IN DEFAULT
    EXECUTION_NAME VARCHAR2 IN DEFAULT
    FUNCTION REPORT_EVOLVE_TASK RETURNS CLOB
    Argument Name Type In/Out Default?

    TASK_NAME VARCHAR2 IN
    TYPE VARCHAR2 IN DEFAULT
    LEVEL VARCHAR2 IN DEFAULT
    SECTION VARCHAR2 IN DEFAULT
    OBJECT_ID NUMBER IN DEFAULT
    TASK_OWNER VARCHAR2 IN DEFAULT
    EXECUTION_NAME VARCHAR2 IN DEFAULT
    PROCEDURE RESET_EVOLVE_TASK
    Argument Name Type In/Out Default?

    TASK_NAME VARCHAR2 IN
    PROCEDURE RESUME_EVOLVE_TASK
    Argument Name Type In/Out Default?

    TASK_NAME VARCHAR2 IN
    PROCEDURE SET_EVOLVE_TASK_PARAMETER
    Argument Name Type In/Out Default?

    TASK_NAME VARCHAR2 IN
    PARAMETER VARCHAR2 IN
    VALUE NUMBER IN
    PROCEDURE SET_EVOLVE_TASK_PARAMETER
    Argument Name Type In/Out Default?

    TASK_NAME VARCHAR2 IN
    PARAMETER VARCHAR2 IN
    VALUE VARCHAR2 IN
    FUNCTION UNPACK_STGTAB_BASELINE RETURNS NUMBER
    Argument Name Type In/Out Default?

    TABLE_NAME VARCHAR2 IN
    TABLE_OWNER VARCHAR2 IN DEFAULT
    SQL_HANDLE VARCHAR2 IN DEFAULT
    PLAN_NAME VARCHAR2 IN DEFAULT
    SQL_TEXT CLOB IN DEFAULT
    CREATOR VARCHAR2 IN DEFAULT
    ORIGIN VARCHAR2 IN DEFAULT
    ENABLED VARCHAR2 IN DEFAULT
    ACCEPTED VARCHAR2 IN DEFAULT
    FIXED VARCHAR2 IN DEFAULT
    MODULE VARCHAR2 IN DEFAULT
    ACTION VARCHAR2 IN DEFAULT

    SQL>

    Am I missing something?

    Like

    Reply

Leave a reply to Daniel Overby Hansen Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.