关于shared pool的深入探讨

xiangzhao

我们进一步来讨论一下shared pool的处理:

先进行相应查询，获得测试数据:

[oracle@jumper udump]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.3.0 - Production on Thu Aug 26 10:21:54 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

SQL> connect eygle/eygle
Connected.

SQL> create table emp as select * from scott.emp;

Table created.

SQL>
SQL> connect / as sysdba
Connected.
SQL> startup force;
ORACLE instance started.

Total System Global Area   47256168 bytes
Fixed Size                   451176 bytes
Variable Size              29360128 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> set linesize 120
SQL> connect scott/tiger
Connected.
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      8888 EYGLE      MANAGER              11-AUG-04       9999         10         10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

11 rows selected.

SQL> connect eygle/eygle
Connected.
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      8888 EYGLE      MANAGER              11-AUG-04       9999         10         10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

11 rows selected.

SQL> select SQL_TEXT,VERSION_COUNT,HASH_VALUE,to_char(HASH_VALUE,'xxxxxxxxxx') HEX,ADDRESS
  2  from v$sqlarea where sql_text like 'select * from emp%';

SQL_TEXT             VERSION_COUNT HASH_VALUE HEX         ADDRESS
-------------------- ------------- ---------- ----------- --------
select * from emp                2 2648707557    9de011e5 52D9EA28


SQL> select sql_text,username,ADDRESS,HASH_VALUE,to_char(HASH_VALUE,'xxxxxxxxxx') HEX_HASH_VALUE,CHILD_NUMBER,CHILD_LATCH
  2  from v$sql a,dba_users b where a.PARSING_USER_ID = b.user_id and  sql_text like 'select * from emp%';

SQL_TEXT             USERNAME                       ADDRESS  HASH_VALUE HEX_HASH_VA CHILD_NUMBER CHILD_LATCH
-------------------- ------------------------------ -------- ---------- ----------- ------------ -----------
select * from emp    SCOTT                          52D9EA28 2648707557    9de011e5            0           1
select * from emp    EYGLE                          52D9EA28 2648707557    9de011e5            1           1

注意:这里我们可以看出v$sqlarea和v$sql两个视图的不同之处
v$sql中为每一条sql保留一个条目，而v$sqlarea中根据sql_text进行group by,通过version_count计算子指针的个数.

我们注意到,这两条sql语句因为其代码完全相同,所以其ADDRESS,HASH_VALUE也完全相同.
这就意味着,这两条sql语句在shared pool中的存储位置是相同的(尽管其执行计划可能不同),代码得以共享.

在此过程中Oracle完成sql解析的第一个步骤:语法解析
Oracle进行语法检查时遵循自右向左,自下向上的原则,如果发现语法错误就马上返回错误.

语法检查通过以后,Oracle将sql文本转换为相应的ASCII数值,然后根据数值通过Hash函数计算其HASH_VALUE
在shared pool中寻找是否存在相同的sql语句,如果存在,则进入下一步骤;如果不存在则尝试获取shared pool latch
请求内存,存储该sql代码

在这里有一个问题需要说明一下:
因为大小写字母的ascii值是不同的,所以Oracle会把大小写不同的代码作为不同的sql来处理,我们看一下测试:

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from scott.DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> col sql_text for a30
SQL> select sql_text,hash_value from v$sql where sql_text like 'select * from scott%';

SQL_TEXT                       HASH_VALUE
------------------------------ ----------
select * from scott.DEPT       4096614922
select * from scott.dept       2089404358

我们注意到,仅只大小写的不同使得原本相同的sql语句变成了两条"不同的代码"
所以从这里我们可以看出,sql的规范编写非常重要.

完成这一个步骤以后,Oracle需要进行的是语义分析:
在此步骤中,Oracle需要验证对象是否存在,相关用户是否具有权限,引用的是否是相同的对象...
对于我们第一个查询,实际上emp表来自不同的用户,那么sql的执行计划也就不同了
当然影响sql执行计划的因素还有很多,包括优化器模式等

SQL> select a.*,to_char(to_hash,'xxxxxxxxxx') Hex_HASH_VALUE
  2  from V$OBJECT_DEPENDENCY  a where to_name='EMP';

FROM_ADD  FROM_HASH TO_OWNER             TO_NAME              TO_ADDRE    TO_HASH    TO_TYPE HEX_HASH_VA
-------- ---------- -------------------- -------------------- -------- ---------- ---------- -----------
52D9EA28 2648707557 SCOTT                EMP                  52D9DEBC  828228010          2    315dc1aa
52D9EA28 2648707557 EYGLE                EMP                  52D82E58 1930491453          2    7310f63d

SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 1';

Session altered.

SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 2';

Session altered.

SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 4';

Session altered.

SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 8';

Session altered.

SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 16';

Session altered.

SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 32';

Session altered.


SQL> ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 100';

Session altered.