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