Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.