【實驗】sql語句在shared_pool中的查詢（程式 繫結變數）

secooler發表於2009-03-04
SQL變數
實驗目的：理解sql語句如何儲存在記憶體中

1.不使用繫結變數的例子
sec@ora10g> select * from emp where empno=7900;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

sec@ora10g> select * from emp where empno= 7900;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

sec@ora10g> select * from emp where empno=                     7900;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

sec@ora10g> select * from emp where empno=7902;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

sec@ora10g> select * from emp where empno=    7902;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

2.使用繫結變數的例子
sec@ora10g> var v1 number
sec@ora10g> begin
  2  :v1:=7900;
  3  end;
  4  /

PL/SQL procedure successfully completed.

sec@ora10g> select * from emp where empno=:v1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

sec@ora10g> var v1 number
sec@ora10g> begin
  2  :v1:=7902;
  3  end;
  4  /

PL/SQL procedure successfully completed.

sec@ora10g> select * from emp where empno=:v1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

3.驗證是否能夠共享到shared_pool中解析過的sql語句
sec@ora10g> select sql_text from v$sqlarea where sql_text like 'select * from emp where %';

SQL_TEXT
-------------------------------------------------------------
select * from emp where empno=7900
select * from emp where empno=:v1
select * from emp where empno=    7902
select * from emp where empno=7902
select * from emp where empno= 7900
select * from emp where empno=                     7900

6 rows selected.

4.檢視shared_pool剩餘大小，若不足需要對共享池進行最佳化
sec@ora10g> select * from v$sgastat where name = 'free memory' and pool = 'shared pool';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  free memory                  15717892

5.共享sql語句的條件
1).記憶體中有相同的文字串
2).Hash值相同，比較兩個語句的文字是否相同
3).物件的owner必須相同，不同賬號的同名表不能共享
4).使用繫結變數時，變數名稱必須相同
5).執行語句的環境相同，比如最佳化模式等引數

6.總結
使用繫結變數可以大大的減少sql被反覆的解析，提高sql的執行效率。
給開發同學們的建議：
1).儘量使用繫結變數，並且統一繫結變數名字
2).規範sql語句和pl/sql語句的格式
3).規範在sql語句中出現的空格個數

-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-561359/，如需轉載，請註明出處，否則將追究法律責任。

相關文章