甲骨论11课SQL语句解析类型硬解析软解析
SQL语句执行过程：parse-execute-fetch
sharedPool组成：
l Free空间
l Library库缓存：保存库和执行计划
l rowCache字典缓存：保存Oracle数据信息，如数据库的表数、用户数等。
数据字典的作用：（需要多了解数据字典表）
1) #sqlplus / as sysdba
2) >Create table t1 as select *from dba_objects;拷贝建立一个表
3) >desc dba_tables
4) >select table_name,OWNERfrom dba_tables where table_name like ‘T1%’
查看共享池内存：
1) #select * from v$sgastat awhere a.pool =’shared pool’ and a.NAME=’free memory’
2) #select * from v$sgastat awhere a.NAME=’library cache’
3) #select * from v$sgastat awhere a.NAME=’row cache’
SQL解析类型：硬解析（bufferCache无缓存有执行方案）、软解析、软软解析（也称不解析）。
硬解析的执行方案选优最消耗资源。软解析不需要执行方案选优，
#selectname,value from v$sysstat where name like ‘parse%’ //查看解析中硬解析数量，解析失败数量。
甲骨论12课shared内存块组成结构及4031错误产生原因分析
内容：chain、trunk、硬解析/软解析过程、ora4031错误。
sharedPool内存块组成：Oracle只能总体设置sharedPool大小，不能单独libraryCache或rowCache大小。Free空间的内存块挂在链上，小内存块在链前，大内存块在链后。只有硬解析使用Free空间，Free空间中过多的小内存块遇到需要大内存块的长sql语句，就会出现硬解析失败Ora4031错误。
硬解析失败Ora4031错误原因：
l 过多硬解析，Free空间不足
l Free空间碎片（小内存块）过多，而需要大内存块的硬解析。
Free空间中链chain的作用：Free空间通过链的遍历找到内存块。使用锁latch保护共享池中空闲空间中的链。
libraryCache中挂链的方法是：sql语句转为ASCII码，转为Hash值，转为数字，转为链编号。libraryCache中内存块按Hash值排序，这与Free中按内存块大小排序的方式不同。
Ora4031错误检查语句：
l #select count(*) from x$ksmsp //检查libraryCache中的trunk数
l #selec count(*) from dba_objects //任意执行一条未执行的语句，触发硬解析
l #select count(*) from x$ksmsp //
l #alter system flush shared_pool //清空libraryCache和rowCache，执行后Free空间的碎片减少，ora4031错误减少，同时会带来大量的硬解析。此命令只能缓解ora4031错误，解决还需要优化数据库结构。
l
l #select name,value from v$sysstat where name like ‘parse%’ //查看软硬解析具体情况
方法：检查两次trunk数，计算一段时间内增加trunk数。产生trunk数少说明系统很稳定（硬解析少），多则说明硬解析较多。也可以用v$sysstat查看。
甲骨论13课共享SQL减少硬解析
硬解析缺点：消耗资源、出现ora4031错误。完全相同（包括空格大小写）的sql语句才能共享，才能软解析。
#selectsql_id,sql_text,excecutions from v$sql where sql_text like ‘%hello%’ //查看sql语句的sql_id
避免硬解析方法：
l 统一书写风格
l 变量不同的使用绑定变量，将sql语句分为动态部分和静态部分。
1) >declare v_sql varchar2(50);
2) >begin for I in 1..10000loop
3) >v_sql :=’insert /hello/into test values (:1)’;
4) >execute immediate v_sqlusing I;
5) >end loop;
6) >commit;
7) >end;
>show prarmeter cursor //显示cursor sharing参数
>alter system set session_cached_cursors=150 scope=both; //改cursors参数
甲骨论14课如何解决ORA4031错误
找出没有共享的SQL语句：
1) >select SQL_FULLTEXT fromv$sql where EXECUTIONS=1 and sql_text like ‘%from t%’; //找只执行一次的变量
2) >spool 1.1st
3) >select SQL_FULLTEXT fromv$sql where EXECUTIONS=1 order by sql_text;
4) >spool off
5) >exit
查看解析命中率（要求运行稳定后命中率90%以上）：
1) >selectsum(pinhits)/sum(pins)*100 from v$librarycache; //library命中率，一般出问题较多
2) >selectsum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets) from v$rowcachewhere gets>0; //rowcache命中率
解决ora4031错误的方法：
1) >alter system flushshared_pool;
2) 共享SQL
>alter system set cursor_sharing=’force’;
>show parameter cursor;
这种方法只能在共享SQL中的强制绑定变量，不能解决书写规范问题。
3) 使用dbms_shared_pool.keep包
>@?/rdbms/admin/dbmspool.sql //执行sql语句，产生对象dbms_shared_pool.keep
>select * from v$db_object_cache where sharable_mem>10000
and (type=’PACKAGE’ or type=’PACKAE_BODY’ ortype=’FUNCTION’ or type=’PROCEDURE’) and kept=’NO’//查看占内存较大的sql语句
> //强制keep到内存中
4) 保留区
>select REQUEST_MISSES from v$shared_pool_reserved; //如果发现“保留区请求失败次数REQUEST_MISSES”
>show paremeter shared //显示共享池保留空间
5) 增加shared_pool空间
>select COMPONENT,CURRENT_SIZE fromV$SGA_DYNAMIC_COMPONENTS;
>show parameter sga_target
>show parameter sga_max_size
>alter system set shared_pool_size=150M scope=both;
从oracle10后，只需要设定6个池共享空间sga_target，由oracle分配各个池大小。此参数设定需要重启oracle。sga_target_max参数可以防止sga_target设置过大导致系统死机。
1) >show parameter shared_pool;//查看共享池设置值
2) >alter system setshared_pool_size=20; //设置共享池的值
3) >select COMPONENTCSURRENT_SIZEfrom V$SGA_DYNAMIC_COMPONENTS; //查看共享的池的实际值
若共享池大小设置小于sga_target分配，则不会被采纳。注意到从V$SGA才显示共享池实际值。
甲骨论15课如何设置shared_pool及sga大小
共享池过大的坏处：
l 只执行一次的sql语句（没有享受到共享）也进行共享机制（到libraryCache查找，解析，Free空间中找空trunk，写入解析内容，挂到libraryCache上），还不如直接硬解析，不查找不缓存不挂链快速。
l 共享池过大，libraryCache链长遍历比较慢。
shared_pool大小设置：
l 使用脚本算：共享池大小-反应时间曲线
l EM-指导中心-内存指导-SGA建议大小
查看执行计划：
1) >sql语句
2) >select sql_id,sql_text fromv$sql where sql_text like ‘%语句%’; //取得sql_id
3) >select * fromtable(dbms_xplan.display_cursor(‘sql_id’)); //显示执行计划
后面的内容讲执行计划。
网友评论