为了不重复解析完全相同的SQL语句，在第一次解析之后，Oracle将解析后的SQL语句存储在内存中。

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

Oracle对Share SQL Area的共享要求两个SQL语句在文本上必须完成相同，包括空格、换行、大小写都必须完全相同。

点击(此处)折叠或打开

--清空shared pool
sys@TESTDB11>alter system flush shared_pool;
--使用scott执行下面的语句
scott@TESTDB11>select * from emp where empno = 7788;
scott@TESTDB11>select * from emp where empno = 7788;
scott@TESTDB11>SELECT * FROM EMP WHERE EMPNO = 7788;
scott@TESTDB11>Select * From Emp Where Empno = 7788;
scott@TESTDB11>select * from emp where empno = 7788;
--查看在SCOTT方案中解析的SQL语句,结论：语句的文本必须完全相同才能被认为是相同的SQL语句
sys@TESTDB11>select sql_id, child_number, sql_text from v$sql where parsing_schema_name = \'SCOTT\';
SQL_ID CHILD_NUMBER SQL_TEXT
------------- ------------ ------------------------------------------------------------------------
fnzhctjcmspgz 0 SELECT * FROM EMP WHERE EMPNO = 7788
073yn322p1cx9 0 select * from emp where empno = 7788
7mc5065rargfv 0 select * from emp where empno = 7788
7mh165n4umhsx 0 Select * From Emp Where Empno = 7788

两语句引用的对象必须完全相同。

点击(此处)折叠或打开

--授权
scott@TESTDB11>grant select on emp to hr;
--在HR方案中创建表
hr@TESTDB11>create table emp as select * from scott.emp;
--清空shared pool
sys@TESTDB11>alter system flush buffer_cache;
--执行下面2条SQL语句
scott@TESTDB11>select * from emp;
hr@TESTDB11>select * from emp;
--查看结果. 两个语句共享相同的父游标,但对应于不同的子游标
sys@TESTDB11>select sql_id, hash_value, child_number, sql_text from v$sql
where parsing_schema_name = \'SCOTT\' or parsing_schema_name =\'HR\';
SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ ----------------------------------------------------------------------------------------------------
gx6qnrgvv63mx 4155707005 0 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param(\'paralle
l_execution_enabled\', \'false\') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:\"SYS_B_0\"),
NVL(SUM(C2),:\"SYS_B_1\") FROM (SELECT /*+ NO_PARALLEL(\"EMP\") FULL(\"EMP\") NO_PARALLEL_INDEX(\"EMP\") */
:\"SYS_B_2\" AS C1, :\"SYS_B_3\" AS C2 FROM \"HR\".\"EMP\" \"EMP\") SAMPLESUB
a2dk8bdn0ujx7 1745700775 0 select * from emp
a2dk8bdn0ujx7 1745700775 1 select * from emp