Skip to Main Content
  • Questions
  • SPM> SQL in AWR to tie SQL_ID <> SQL_HANDLE <> PLAN_HASH_VALUE

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vipul.

Asked: December 16, 2020 - 4:15 am UTC

Last updated: December 17, 2020 - 5:21 am UTC

Version: 12.2.0.1

Viewed 100+ times

You Asked

Is there a way to tie SQL_ID with SQL_HANDLE in SQL Plan Baseline (SPM) along with it’s plan hash value in AWR history. It appears that we can tie this in cursor cache but not from the history. I’m working with a customer who is dumping this data periodically from cursor cache in a custom table to get this information. Essentially, we are looking to get the following:

SQL_ID / SQL_HANDLE / LAST_USE_DATE / PLAN_HASH_VALUE / SQL_PLAN_BASELINE / EXECUTIONS / ELAPSED_TIME

The last 2 columns can be computed if the others can tied successfully.

Any help is greatly appreciated ...

and we said...

Is there a way to tie SQL_ID with SQL_HANDLE


yes and no :-) Because it not necessarily a one-to-one mapping.

When we load a baseline from an incoming parse on a SQL, we normalize the sql text first, so multiple "similar" SQL statements might map to the same handle.

But on V$SQL, you can take the EXACT_MATCHING_SIGNATURE column - that is your entry into DBA_SQL_PLAN_BASELINES on the SIGNATURE column

SQL> desc DBA_SQL_PLAN_BASELINES
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 SIGNATURE                           NOT NULL NUMBER
 SQL_HANDLE                          NOT NULL VARCHAR2(30)
 SQL_TEXT                            NOT NULL CLOB
 PLAN_NAME                           NOT NULL VARCHAR2(128)
 CREATOR                                      VARCHAR2(128)
 ORIGIN                                       VARCHAR2(29)
 PARSING_SCHEMA_NAME                          VARCHAR2(128)
 DESCRIPTION                                  VARCHAR2(500)
 VERSION                                      VARCHAR2(64)
 CREATED                             NOT NULL TIMESTAMP(6)
 LAST_MODIFIED                                TIMESTAMP(6)
 LAST_EXECUTED                                TIMESTAMP(6)
 LAST_VERIFIED                                TIMESTAMP(6)
 ENABLED                                      VARCHAR2(3)
 ACCEPTED                                     VARCHAR2(3)
 FIXED                                        VARCHAR2(3)
 REPRODUCED                                   VARCHAR2(3)
 AUTOPURGE                                    VARCHAR2(3)
 ADAPTIVE                                     VARCHAR2(3)
 OPTIMIZER_COST                               NUMBER
 MODULE                                       VARCHAR2(64)
 ACTION                                       VARCHAR2(64)
 EXECUTIONS                                   NUMBER
 ELAPSED_TIME                                 NUMBER
 CPU_TIME                                     NUMBER
 BUFFER_GETS                                  NUMBER
 DISK_READS                                   NUMBER
 DIRECT_WRITES                                NUMBER
 ROWS_PROCESSED                               NUMBER
 FETCHES                                      NUMBER
 END_OF_FETCH_COUNT                           NUMBER

SQL> desc V$SQL
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 SQL_TEXT                                     VARCHAR2(1000)
 SQL_FULLTEXT                                 CLOB
 SQL_ID                                       VARCHAR2(13)
 SHARABLE_MEM                                 NUMBER
 PERSISTENT_MEM                               NUMBER
 RUNTIME_MEM                                  NUMBER
 SORTS                                        NUMBER
 LOADED_VERSIONS                              NUMBER
 OPEN_VERSIONS                                NUMBER
 USERS_OPENING                                NUMBER
 FETCHES                                      NUMBER
 EXECUTIONS                                   NUMBER
 PX_SERVERS_EXECUTIONS                        NUMBER
 END_OF_FETCH_COUNT                           NUMBER
 USERS_EXECUTING                              NUMBER
 LOADS                                        NUMBER
 FIRST_LOAD_TIME                              VARCHAR2(19)
 INVALIDATIONS                                NUMBER
 PARSE_CALLS                                  NUMBER
 DISK_READS                                   NUMBER
 DIRECT_WRITES                                NUMBER
 DIRECT_READS                                 NUMBER
 BUFFER_GETS                                  NUMBER
 APPLICATION_WAIT_TIME                        NUMBER
 CONCURRENCY_WAIT_TIME                        NUMBER
 CLUSTER_WAIT_TIME                            NUMBER
 USER_IO_WAIT_TIME                            NUMBER
 PLSQL_EXEC_TIME                              NUMBER
 JAVA_EXEC_TIME                               NUMBER
 ROWS_PROCESSED                               NUMBER
 COMMAND_TYPE                                 NUMBER
 OPTIMIZER_MODE                               VARCHAR2(10)
 OPTIMIZER_COST                               NUMBER
 OPTIMIZER_ENV                                RAW(2000)
 OPTIMIZER_ENV_HASH_VALUE                     NUMBER
 PARSING_USER_ID                              NUMBER
 PARSING_SCHEMA_ID                            NUMBER
 PARSING_SCHEMA_NAME                          VARCHAR2(128)
 KEPT_VERSIONS                                NUMBER
 ADDRESS                                      RAW(8)
 TYPE_CHK_HEAP                                RAW(8)
 HASH_VALUE                                   NUMBER
 OLD_HASH_VALUE                               NUMBER
 PLAN_HASH_VALUE                              NUMBER
 FULL_PLAN_HASH_VALUE                         NUMBER
 CHILD_NUMBER                                 NUMBER
 SERVICE                                      VARCHAR2(64)
 SERVICE_HASH                                 NUMBER
 MODULE                                       VARCHAR2(64)
 MODULE_HASH                                  NUMBER
 ACTION                                       VARCHAR2(64)
 ACTION_HASH                                  NUMBER
 SERIALIZABLE_ABORTS                          NUMBER
 OUTLINE_CATEGORY                             VARCHAR2(64)
 CPU_TIME                                     NUMBER
 ELAPSED_TIME                                 NUMBER
 OUTLINE_SID                                  NUMBER
 CHILD_ADDRESS                                RAW(8)
 SQLTYPE                                      NUMBER
 REMOTE                                       VARCHAR2(1)
 OBJECT_STATUS                                VARCHAR2(19)
 LITERAL_HASH_VALUE                           NUMBER
 LAST_LOAD_TIME                               VARCHAR2(19)
 IS_OBSOLETE                                  VARCHAR2(1)
 IS_BIND_SENSITIVE                            VARCHAR2(1)
 IS_BIND_AWARE                                VARCHAR2(1)
 IS_SHAREABLE                                 VARCHAR2(1)
 CHILD_LATCH                                  NUMBER
 SQL_PROFILE                                  VARCHAR2(64)
 SQL_PATCH                                    VARCHAR2(128)
 SQL_PLAN_BASELINE                            VARCHAR2(128)
 PROGRAM_ID                                   NUMBER
 PROGRAM_LINE#                                NUMBER
 EXACT_MATCHING_SIGNATURE                     NUMBER
 FORCE_MATCHING_SIGNATURE                     NUMBER
 LAST_ACTIVE_TIME                             DATE
 BIND_DATA                                    RAW(2000)
 TYPECHECK_MEM                                NUMBER
 IO_CELL_OFFLOAD_ELIGIBLE_BYTES               NUMBER
 IO_INTERCONNECT_BYTES                        NUMBER
 PHYSICAL_READ_REQUESTS                       NUMBER
 PHYSICAL_READ_BYTES                          NUMBER
 PHYSICAL_WRITE_REQUESTS                      NUMBER
 PHYSICAL_WRITE_BYTES                         NUMBER
 OPTIMIZED_PHY_READ_REQUESTS                  NUMBER
 LOCKED_TOTAL                                 NUMBER
 PINNED_TOTAL                                 NUMBER
 IO_CELL_UNCOMPRESSED_BYTES                   NUMBER
 IO_CELL_OFFLOAD_RETURNED_BYTES               NUMBER
 CON_ID                                       NUMBER
 IS_REOPTIMIZABLE                             VARCHAR2(1)
 IS_RESOLVED_ADAPTIVE_PLAN                    VARCHAR2(1)
 IM_SCANS                                     NUMBER
 IM_SCAN_BYTES_UNCOMPRESSED                   NUMBER
 IM_SCAN_BYTES_INMEMORY                       NUMBER
 DDL_NO_INVALIDATE                            VARCHAR2(1)
 IS_ROLLING_INVALID                           VARCHAR2(1)
 IS_ROLLING_REFRESH_INVALID                   VARCHAR2(1)
 RESULT_CACHE                                 VARCHAR2(1)
 SQL_QUARANTINE                               VARCHAR2(128)
 AVOIDED_EXECUTIONS                           NUMBER


More to Explore

Performance

Get all the information about database performance in the Database Performance guide.