How to get the SQL_ID of a given SQL
This article refers to teacher Gai Guoqiang's blog post for testing.
Author: eygle | English [Please indicate the source and author information when reprinting] | [OCM training of Enmo College to pass on the success of DBA] Link: http://www.eygle.com/archives/2017/08/dbms_sqltune_util0_sqltext_to_sqlid.html
-
In the Oracle database, how do I get the SQL_ID of a given SQL? This is an issue that has been widely discussed.
Now, in Oracle 11g, Oracle gives a system package that can easily calculate the SQL_ID of a given SQL through dbms_sqltune_util0.
SQL > desc dbms_sqltune_util0
FUNCTION EXTRACT_BIND RETURNS SQL_BIND
Argument Name Type In/Out Default?
BIND_DATA RAW IN
BIND_POS BINARY_INTEGER IN
FUNCTION EXTRACT_BINDS RETURNS SQL_BIND_SET
Argument Name Type In/Out Default?
BIND_DATA RAW IN
FUNCTION GET_BINDS_COUNT RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
BIND_DATA RAW IN
FUNCTION IS_BIND_MASKED RETURNS NUMBER
Argument Name Type In/Out Default?
BIND_POS BINARY_INTEGER IN
MASKED_BINDS_FLAG RAW IN DEFAULT
FUNCTION SQLTEXT_TO_SIGNATURE RETURNS NUMBER
Argument Name Type In/Out Default?
SQL_TEXT CLOB IN
FORCE_MATCH BINARY_INTEGER IN DEFAULT
FUNCTION SQLTEXT_TO_SQLID RETURNS VARCHAR2
Argument Name Type In/Out Default?
SQL_TEXT CLOB IN
FUNCTION VALIDATE_SQLID RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
SQL_ID VARCHAR2 IN
The function sqltext_to_sqlid is used to implement this function, and the following test uses a simple SQL query.
Notice that Oracle adds an invisible character of chr (0) at the end of the SQL, which we need to complete:
SYS@ R7 > SELECT DBMS_SQLTUNE_UTIL0.SQLTEXT_TO_SQLID ('SELECT SYSDATE FROM DUAL' | | CHR (0)) SQL_ID FROM DUAL
SQL_ID
- -
C749bc43qqfz3
Next, take a look at the execution of this query. The automatically generated SQL_ID in the database is exactly the same as the one generated through the function transformation:
SYS@ R7 > SELECT SYSDATE FROM DUAL
SYSDATE
-
21-AUG-17
SYS@ R7 > SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT='SELECT SYSDATE FROM DUAL'
SQL_ID
-
C749bc43qqfz3
View the execution plan
SYS@ R7 > select * from table (dbms_xplan.display_cursor ('c749bc43qqfz3'))
PLAN_TABLE_OUTPUT
- -
SQL_ID c749bc43qqfz3, child number 0
-
SELECT SYSDATE FROM DUAL
Plan hash value: 1388734953
-
| | Id | Operation | Name | Rows | Cost (% CPU) | Time |
-
| | 0 | SELECT STATEMENT | 2 (100) | |
| | 1 | FAST DUAL | | 1 | 2 (0) | 00:00:01 |
-
13 rows selected.
