ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【实验】shared_pool的sql命中率--cursor_sharing参数研究

【实验】shared_pool的sql命中率--cursor_sharing参数研究

原创 Linux操作系统 作者:secooler 时间:2009-03-06 09:17:52 0 删除 编辑
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).建议尽可能的保持系统默认的
EXACT精确匹配模式,如需调整,建议在测试环境做好充足的验证。

secooler
09.03.06

-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-562987/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE 总监,阿里云MVP,北京大学理学硕士,恩墨学院创始人,教育专家,中国区 Cloudera 首位官方授权大数据讲师,金牌培训专家,BDA大数据联盟创始人,OCM联盟创始人,ACCUG创始人、ACOUG核心专家,Blogger。

注册时间:2008-03-16

  • 博文量
    797
  • 访问量
    8009260