Oracle硬解析的幾個例子

Oracle進(jìn)行軟解析的SQL必須是完全相同的，所謂相同的SQL必須是大小寫一致（測試?yán)?），甚至是不能多一個或者少一個空格，這個結(jié)

為了驗證SQL硬解析的場景,設(shè)置了下面六個測試用的例子:

1、沒有綁定變量下的普通查詢
2、測試綁定變量下的查詢
3、測試綁定變量下sql有變化的查詢
4、測試DML非綁定變量的解析
5、測試在過程中執(zhí)行插入的時候非綁定變量的SQL解析
6、使用了綁定變量之后的,過程中的SQL解析情況

/**
測試?yán)?:
沒有綁定變量下的普通查詢
**/
drop table foo purge;

CREATE TABLE foo AS
SELECT LEVEL AS x,100000-LEVEL AS y FROM dual CONNECT BY LEVEL<=100000;

ALTER SYSTEM FLUSH SHARED_POOL;

SELECT * FROM foo WHERE x = 100;
SELECT * FROM foo WHERE x =999;
SELECT * FROM foo WHERE x=10000;

SELECT T.SQL_TEXT, T.SQL_ID, T.EXECUTIONS, T.PARSE_CALLS
FROM V$SQL T
WHERE UPPER(T.SQL_TEXT) LIKE '%FOO%';

/**
測試?yán)?:
測試綁定變量下的查詢
**/
drop table foo purge;

CREATE TABLE foo AS
SELECT LEVEL AS x,100000-LEVEL AS y FROM dual CONNECT BY LEVEL<=100000;

ALTER SYSTEM FLUSH SHARED_POOL;

VARIABLE temp NUMBER;

exec :temp :=99;
SELECT * FROM foo WHERE X = :temp;

exec :temp :=100;
SELECT * FROM foo WHERE X = :temp;

exec :temp :=101;
SELECT * FROM foo WHERE X = :temp;

SELECT T.SQL_TEXT, T.SQL_ID, T.EXECUTIONS, T.PARSE_CALLS
FROM V$SQL T
WHERE UPPER(T.SQL_TEXT) LIKE '%FOO%';

/**

測試?yán)?:
測試綁定變量下sql有變化的查詢
**/

drop table foo purge;

CREATE TABLE foo AS
SELECT LEVEL AS x,100000-LEVEL AS y FROM dual CONNECT BY LEVEL<=100000;

ALTER SYSTEM FLUSH SHARED_POOL;

VARIABLE temp NUMBER;

exec :temp :=99;
SELECT * FROM foo WHERE X = :temp;

exec :temp :=100;
SELECT * FROM FOO WHERE X = :temp;

exec :temp :=101;
SELECT * FROM foo WHERE X = :temp;

SELECT T.SQL_TEXT, T.SQL_ID, T.EXECUTIONS, T.PARSE_CALLS
FROM V$SQL T
WHERE UPPER(T.SQL_TEXT) LIKE '%FOO%';

/**
測試?yán)?:
測試DML非綁定變量的解析
**/

drop table foo purge;

CREATE TABLE foo AS
SELECT LEVEL AS x,100000-LEVEL AS y FROM dual CONNECT BY LEVEL<=100000;

ALTER SYSTEM FLUSH SHARED_POOL;

INSERT INTO FOO VALUES(100,200);
INSERT INTO FOO VALUES(101,201);
INSERT INTO FOO VALUES(103,203);

SELECT T.SQL_TEXT, T.SQL_ID, T.EXECUTIONS, T.PARSE_CALLS
FROM V$SQL T
WHERE UPPER(T.SQL_TEXT) LIKE '%FOO%';

/**
測試?yán)?:
測試在過程中執(zhí)行插入的時候的SQL解析
**/

drop table foo purge;

CREATE TABLE foo AS
SELECT LEVEL AS x,100000-LEVEL AS y FROM dual CONNECT BY LEVEL<=100000;

ALTER SYSTEM FLUSH SHARED_POOL;

BEGIN
FOR I IN 1..3 LOOP
IF I=1 THEN
INSERT INTO FOO VALUES(1,1);
ELSIF I=2 THEN
INSERT INTO FOO VALUES(2,2);
ELSIF I=3 THEN
INSERT INTO FOO VALUES(3,3);
END IF;
END LOOP;
END;
/
SELECT T.SQL_TEXT, T.SQL_ID, T.EXECUTIONS, T.PARSE_CALLS
FROM V$SQL T
WHERE UPPER(T.SQL_TEXT) LIKE '%FOO%';

/**
測試?yán)?:
使用了綁定變量之后的,過程中的SQL解析情況
**/

drop table foo purge;

CREATE TABLE foo AS
SELECT LEVEL AS x,100000-LEVEL AS y FROM dual CONNECT BY LEVEL<=100000;

ALTER SYSTEM FLUSH SHARED_POOL;

BEGIN
FOR I IN 1..200 LOOP
INSERT INTO FOO VALUES(I,100000-I);
END LOOP;
END;
/
SELECT T.SQL_TEXT, T.SQL_ID, T.EXECUTIONS, T.PARSE_CALLS
FROM V$SQL T
WHERE UPPER(T.SQL_TEXT) LIKE '%FOO%';

通過上述六個情況的試驗，我們最終可以得到如下結(jié)論：

Oracle進(jìn)行軟解析的SQL必須是完全相同的，所謂相同的SQL必須是大小寫一致（測試?yán)?），甚至是不能多一個或者少一個空格，這個結(jié)論可以通過修改測試?yán)?增加一個空格得到，結(jié)果就得到了不同的SQL_ID。只有完全一致的SQL，才可以得到相應(yīng)的HASH值，從而才可以進(jìn)行軟解析。對于在SQL池中，我們需要分析在SQL池中出現(xiàn)的只有參數(shù)部分不同的SQL，如果出現(xiàn)了很多次，，我們就有必要對其進(jìn)行相應(yīng)的變量綁定，從而降低硬解析成本，提高性能。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com