【相克军】SQL语句解析类型_硬解析_软解析

一、shared pool的组成

          3块区域：free、library cache、row cache

          select * from v$sgastat a where a.NAME='library cache';

          select * from v$sgastat a where a.pool='shared pool' and a.NAME='free memory';

          select * from v$sgastat a where a.NAME='row cache';

 

二、硬解析、软解析

         硬解析步骤、软解析步骤

         讲解shared pool内存块组成结构

         ora-4031错误

         select count(*) from x$ksmsp;

         select count(*) from dba_objects;

         select count(*) from x$ksmsp;

         alter system flush shared_pool;

 

         软硬解析的具体情况

         select name,value from v$sysstat where name like 'parse%'

 

三、SQL共享，绑定变量

         SQL语句组成，动态部分、静态部分

         cursor_sharing

         delare

         vl varchar2(10);

         nl int;

         begin

         nl:=1;

         select salary into vl from test where id=n1;

         end;

        

         select SQL_ID,sql_text,EXECUTIONS from v$sql where SQL_TEXT like        

 

四、找出没有共享的SQL语句

         如何找出不能共享cursor的sql

         在v$sql查找执行次数较小的sql语句，观察这些sql语句是否是经常执行的。

         select SQL_FULLTEXT from v$sql where EXECTIONS=1 and sql_text like '%from t%';

         select SQL_FULLTEXT from v$sql where EXECTIONS=1 order by sql_text;

 

五、解析命中率

         select sum(pinhits)/sum(pins)*100 from v$librarycache;

         select sum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets) from v$rowcache where gets>0;

 

六、解决4031错误的办法

         1、alter system flush shared_pool;

         2、共享SQL

         3、select * from v$db_object_cache where sharable_mem > 10000

                           and (type = 'PACKAGE' or type='PACKAGE BODY' or type = 'FUNCTION' or type = 'PROCEDURE')

                           and kept = 'NO';

               执行dbms_shared_pool.keep('对象名');

               DBMS_SHARED_POOL

               @?/rdbms/admin/dbmspool.sql

          4、保留区

           select REQUEST_MISSES from v$shared_pool_reserved;

          5、增加shared pool空间

          select COMPONENT.CURRENT_SIZE  from v$sga_dynamic_components;

          show parameter sga_target;

          show parameter sga_max_target;

          alter system set shared_pool_size=150M scope=both;

 

七、查看执行计划

          select * from table(dbms_xplan.display_cursor('07hpk6hpb7pp8'));