oracle sql语句命中率考究

恬谧7377

1.提高命中率是为了减少硬编译（hard parse），增加软编译（soft parse），从而提高sql语句的解析效率
2.sql语句运行过程
1).使用hash算法得到sql语句的hash_value值
2).如果hash_value值在内存中，叫做命中执行软解析
3).如果hash_value值不存在，执行硬解析
4).语法解析，查看是否有错误
5).语意解析，查看权限是否符合
6).若有视图，取出视图的定义
7).进行sql语句的自动改写，如将子查询改写为连接
8).选择最优的执行计划
9).变量绑定
10).运行执行计划
11).返回结果给用户

因为软解析是从此11步骤中第9步开始的，因此软解析比硬解析节约大量的系统开销，应该尽量降低硬解析的次数。

3.共享池的命中率
sec@ora10g> select namespace, pins, pinhits, reloads, invalidations from v$librarycache order by namespace;

NAMESPACE             PINS    PINHITS    RELOADS INVALIDATIONS
--------------- ---------- ---------- ---------- -------------
BODY                  4380       4307         14             0
CLUSTER                942        928          3             0
INDEX                 2378       1665         25             0
JAVA DATA                0          0          0             0
JAVA RESOURCE            0          0          0             0
JAVA SOURCE              0          0          0             0
OBJECT                   0          0          0             0
PIPE                     0          0          0             0
SQL AREA            132073     121593        370            74
TABLE/PROCEDURE      29526      24022        971             0
TRIGGER                935        926          2             0

11 rows selected.

4.实例启动以来的命中率
sec@ora10g> select sum(pinhits)/sum(pins) from v$librarycache;

SUM(PINHITS)/SUM(PINS)
----------------------
            .901459105

5.如果sql的命中率小于90%就需要对其进行优化，优化方法
1).加大shared_pool_size的大小，过犹不及，太大会增加数据的额外管理负担
2).书写程序是尽量使用变量不要过多的使用常量
3).将大的包pin在内存中
4).修改cursor_sharing初始化参数

6.实验，验证cursor_sharing参数三个不同选项（exact, similar, force）的差别
1).构造一个列值分布不均匀的大表，empno列只有一行等于2000其他都为1000
sys@ora10g> conn sec/sec
Connected.
sec@ora10g>
sec@ora10g> create table t1 as select * from emp;

Table created.

sec@ora10g> insert into t1 select * from t1;

14 rows created.

sec@ora10g> /
sec@ora10g> /

57344 rows created.

sec@ora10g> commit;

Commit complete.

sec@ora10g> select count(*) from t1;

  COUNT(*)
----------
    114688

sec@ora10g> update t1 set empno=1000;

114688 rows updated.

sec@ora10g> commit;

Commit complete.

sec@ora10g> update t1 set empno=2000 where rownum=1;

1 row updated.

2).建立索引
sec@ora10g> create index i_t1 on t1(empno);

Index created.

3).对表进行分析，告知数据库表的大小
sec@ora10g> analyze table t1 compute statistics;

Table analyzed.

4).对列进行分析，数据库可以识别出来表中数据是分布不均匀的
sec@ora10g> analyze table t1 compute statistics for columns empno;

Table analyzed.

5).exact精确匹配（系统默认的模式）
sec@ora10g> show parameter cursor_sharing

NAME                           TYPE                 VALUE
------------------------------ -------------------- --------
cursor_sharing                 string               EXACT

sec@ora10g> set autotrace traceonly explain;
sec@ora10g> select * from t1 where empno=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   114K|  3471K|   168   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T1   |   114K|  3471K|   168   (2)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=1000)

sec@ora10g> select * from t1 where empno=2000;


Execution Plan
----------------------------------------------------------
Plan hash value: 4068921349

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=2000)

6).similar近似匹配
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> alter system set cursor_sharing=similar scope=spfile;

System altered.

sys@ora10g> startup force;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1266392 bytes
Variable Size             113249576 bytes
Database Buffers           50331648 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
sys@ora10g> show parameter cursor_sharing

NAME                                     TYPE                 VALUE
---------------------------------------- -------------------- ------------------
cursor_sharing                           string               SIMILAR

sec@ora10g> set autot traceonly explain
sec@ora10g> select * from t1 where empno=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   114K|  3471K|   168   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T1   |   114K|  3471K|   168   (2)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=1000)

sec@ora10g> select * from t1 where empno=2000;

Execution Plan
----------------------------------------------------------
Plan hash value: 4068921349

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=2000)

7).force，强制匹配
sec@ora10g> conn / as sysdba
Connected.
sys@ora10g> alter system set cursor_sharing=force scope=spfile;

System altered.

sys@ora10g> startup force;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1266392 bytes
Variable Size             113249576 bytes
Database Buffers           50331648 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
sys@ora10g> show parameter cursor_sharing

NAME                                     TYPE                 VALUE
---------------------------------------- -------------------- --------
cursor_sharing                           string               FORCE
sys@ora10g> conn sec/sec
Connected.
sec@ora10g> set autotrace traceonly explain;
sec@ora10g> select * from t1 where empno=1000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   114K|  3471K|   168   (2)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T1   |   114K|  3471K|   168   (2)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=1000)

sec@ora10g> select * from t1 where empno=2000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |   696 |   167   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T1   |     8 |   696 |   167   (1)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=2000)

Note
-----
   - dynamic sampling used for this statement

sec@ora10g> select * from t1 where empno=2000;

Execution Plan
----------------------------------------------------------
Plan hash value: 4068921349

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    31 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |    31 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=2000)


7.小结：
1).EXACT精确匹配，原语句不做处理，降低了sql的命中率，但可以保证执行计划是准确的，此种模式为系统默认的模式
2).SIMILAR近似匹配，将where条件都用变量来处理，单可以区分列值的数据敏感性，一种折中的方案，但是oracle在处理该类参数的sql语句时会有一定的问题，慎用
3).FORCE强制匹配，将where条件都用变量来处理，提高了SQL的命中率，但不能区分列值的数据敏感性，执行计划有时是正确的，但是有时会出现错误
4).经可能的保持系统默认的精确匹配模式，如进行调整，需要进行大量的前期测试

-- The End --