共享SQL語句

LuiseDalian發表於2014-01-14
SQL

為了不重複解析完全相同的SQL語句，在第一次解析之後，Oracle將解析後的SQL語句儲存在記憶體中。

SGA->Shared Pool->Library Cache->Shared SQL Area。

Oracle對Share SQL Area的共享要求兩個SQL語句在文字上必須完成相同，包括空格、換行、大小寫都必須完全相同。

點選(此處)摺疊或開啟

  1. --清空shared pool
  2. sys@TESTDB11>alter system flush shared_pool;

  4. --使用scott執行下面的語句
  5. scott@TESTDB11>select * from emp where empno = 7788;
  6. scott@TESTDB11>select * from emp where empno = 7788;
  7. scott@TESTDB11>SELECT * FROM EMP WHERE EMPNO = 7788;
  8. scott@TESTDB11>Select * From Emp Where Empno = 7788;
  9. scott@TESTDB11>select * from emp where empno = 7788;

  11. --檢視在SCOTT方案中解析的SQL語句,結論：語句的文字必須完全相同才能被認為是相同的SQL語句
  12. sys@TESTDB11>select sql_id, child_number, sql_text from v$sql where parsing_schema_name = \'SCOTT\';

  14. SQL_ID CHILD_NUMBER SQL_TEXT
  15. ------------- ------------ ------------------------------------------------------------------------
  16. fnzhctjcmspgz 0 SELECT * FROM EMP WHERE EMPNO = 7788
  17. 073yn322p1cx9 0 select * from emp where empno = 7788
  18. 7mc5065rargfv 0 select * from emp where empno = 7788
  19. 7mh165n4umhsx 0 Select * From Emp Where Empno = 7788

兩語句引用的物件必須完全相同。


點選(此處)摺疊或開啟

  1. --授權
  2. scott@TESTDB11>grant select on emp to hr;
  3. --在HR方案中建立表
  4. hr@TESTDB11>create table emp as select * from scott.emp;
  5. --清空shared pool
  6. sys@TESTDB11>alter system flush buffer_cache;

  8. --執行下面2條SQL語句
  9. scott@TESTDB11>select * from emp;
  10. hr@TESTDB11>select * from emp;

  12. --檢視結果. 兩個語句共享相同的父遊標,但對應於不同的子游標
  13. sys@TESTDB11>select sql_id, hash_value, child_number, sql_text from v$sql
  14.              where parsing_schema_name = \'SCOTT\' or parsing_schema_name =\'HR\';

  16. SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
  17. ------------- ---------- ------------ ----------------------------------------------------------------------------------------------------
  18. gx6qnrgvv63mx 4155707005 0 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param(\'paralle
  19.                                       l_execution_enabled\', \'false\') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:\"SYS_B_0\"),
  20.                                       NVL(SUM(C2),:\"SYS_B_1\") FROM (SELECT /*+ NO_PARALLEL(\"EMP\") FULL(\"EMP\") NO_PARALLEL_INDEX(\"EMP\") */
  21.                                       :\"SYS_B_2\" AS C1, :\"SYS_B_3\" AS C2 FROM \"HR\".\"EMP\" \"EMP\") SAMPLESUB

  23. a2dk8bdn0ujx7 1745700775 0 select * from emp
  24. a2dk8bdn0ujx7 1745700775 1 select * from emp

兩個SQL語句必須使用相同的繫結變數名。(否則，即使是使用相同的繫結變數值，也不會共享SQL文字)


點選(此處)摺疊或開啟

  1. --清空shared pool
  2. sys@TESTDB11>alter system flush buffer_cache;

  4. --定義繫結變數並賦值
  5. scott@TESTDB11>variable empno1 number
  6. scott@TESTDB11>execute :empno1 := 7788;

  8. PL/SQL procedure successfully completed.

  10. scott@TESTDB11>variable empno2 number
  11. scott@TESTDB11>execute :empno2 := 7788;

  13. PL/SQL procedure successfully completed.

  15. --執行兩條語句
  16. scott@TESTDB11>select e.empno, e.ename, e.job from emp e where empno = :empno1;


點選(此處)摺疊或開啟

  1. scott@TESTDB11>select e.empno, e.ename, e.job from emp e where empno = :empno2;


點選(此處)摺疊或開啟

  1. --檢視結果. 是不同的游標
  2. sys@TESTDB11>select sql_id, hash_value, child_number, sql_text from v$sql where parsing_schema_name = \'SCOTT\';


點選(此處)摺疊或開啟

  1. --繫結變數賦予不同的值,但SQL語句在Shared SQL Area中是共享的
  2. scott@TESTDB11>execute :empno1 := 7369;
  3. scott@TESTDB11>select e.empno, e.ename, e.job from emp e where empno = :empno1;


點選(此處)摺疊或開啟

  1. sys@TESTDB11>select sql_id, hash_value, child_number, sql_text from v$sql where parsing_schema_name = \'SCOTT\';






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

相關文章