本文參考蓋國強老師的博文進行測試。
作者：eygle |English 【轉(zhuǎn)載時請標(biāo)明出處和作者信息】|【恩墨學(xué)院 OCM培訓(xùn)傳DBA成功之道】
鏈接：http://www.eygle.com/archives/2017/08/dbms_sqltune_util0_sqltext_to_sqlid.html
-------------------------------------------------------------------------------------------------------------------
-
在 Oracle 數(shù)據(jù)庫中，如何得到給定SQL的 SQL_ID ? 這是曾經(jīng)被廣泛討論的一個問題。
-
現(xiàn)在，在Oracle 11g中，Oracle 給出了一個系統(tǒng)包，通過 dbms_sqltune_util0 可以簡便的計算出給定SQL的SQL_ID。
-
-
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
-
-
函數(shù) sqltext_to_sqlid 用于實現(xiàn)這個功能，以下測試使用了一個簡單的SQL查詢。
-
注意Oracle在SQL最后加入一個 chr(0) 的不可見字符，我們需要補齊：
-
-
SYS@ r7>SELECT DBMS_SQLTUNE_UTIL0.SQLTEXT_TO_SQLID('SELECT SYSDATE FROM DUAL'||CHR(0)) SQL_ID FROM DUAL;
-
-
SQL_ID
-
----------------------------------------------------------------------------------------------------------------------------------------------------------------
-
c749bc43qqfz3
-
接下來看一下執(zhí)行這個查詢，數(shù)據(jù)庫中自動生成的SQL_ID，與通過函數(shù)轉(zhuǎn)換生成的完全一致：
-
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
-
-
查看執(zhí)行計劃
-
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.
網(wǎng)頁標(biāo)題：如何得到給定SQL的SQL_ID
網(wǎng)頁路徑：
http://weahome.cn/article/iesdhs.html
重慶分公司
重慶分公司
