一）查找执行次数为一的语句
select SQL_FULLTEXT from v$sql where EXECUTIONS=1 order by sql_text;
此语句出来的结果按sql_text排序
如果某些sql没有共享的话
在某一个区域你会发现有一堆的sql语句
它执行一次而且它的静态部分是相同的动态部分不相同
这时就能知道没有共享
可以这么做
SQL> spool 1.lst
然后执行
SQL> select SQL_FULLTEXT from v$sql where EXECUTIONS=1 order by sql_text;
然后
SQL> spool off
1)spool命令
SPOOL是SQLPLUS的命令，不是SQL语法里面的内容
在sqlplus中用来保存或打印查询结果
spool 1.lst
表示将此后的命令及命令的输出结果保存到用户目录下的1.lst文件中
spool off
结束内容的输出
举个小例子：
SQL> spool example.lst
SQL> set linesize 100
SQL> spool off
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@redhat4 ~]$ vi example.lst
下面两行是spool保存到example.lst文件的内容
SQL> set linesize 100
SQL> spool off
2)v$sql视图
看一下查询用到的视图v$sql的结构
SQL> DESC v$sql;
可以发现
SQL_TEXT VARCHAR2(1000)
SQL_FULLTEXT CLOB
SQL_ID VARCHAR2(13)
语句中使用的视图v$sql中有很多字段，目前只看这三个字段
LOB (large object) datatypes(大数据类型)
oracle中有四种大数据类型blob,clob,bfile,nclob
SQL_FULLTEXT的数据类型为CLOB
CLOB: Character Large Object(字符型大数据类型)
即字符型lob,可容纳单字节的字符,最长可以达到4GB，存贮在数据库中
oracle中有多种方法来检索或操作lob数据，通常的处理方法是通过dbms_lob包
SQL_FULLTEXT字段可以把sql语句所有内容保存起来
是为了防止一些超大的sql语句使用一般数据类型保存时不够长度的情况。
但使用select语句直接查询此字段时只显示了最前面的一部分字符(80个字符)，
显示时内容被截断了，但实际内容没有被截断。
简单查询时使用SQL_TEXT字段1000个字符的显示，反而显示的内容多一些。
3)看结果
[oracle@redhat4 ~]$ ls
1.lst Desktop
[oracle@redhat4 ~]$ vi 1.lst
查看命令及结果被存到了1.lst文件中
可以把这个文件放到windows系统里面
放到excel里面排一下序，更好看一些。
如何从系统里面找一些哪些sql语句没有共享
查询执行次数为一的语句是一个小技巧是很好的一个方法
二）命中率
就是librarycache命中率和rowcache命中率
命中率反应的是软解析成功的次数
严格要求99%以上
甚至接近100%
如果是98%就说明命中率比较差了。
应该在数据库跑了一段时间以后去判断命中率
库刚起来就看命中率，那时刚刚执行，命中率肯定低
跑了一段时间以后命中率肯定是很高的。
软解析的命中率
SQL> select sum(pinhits)/sum(pins)*100 from v$librarycache;
SUM(PINHITS)/SUM(PINS)*100
--------------------------
90.5713553
本人的演示的数据库没有什么负载而且跑得比较少
所以命中率比较低。
rowcache的命中率
SQL> select sum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets) from v$rowcache where gets>0;
SUM(GETS) SUM(GETMISSES) 100*SUM(GETS-GETMISSES)/SUM(GETS)
---------- -------------- ---------------------------------
1234298 130366 89.4380449
一般rowcache的命中率很高的
因为实验环境没有运行多少时间，不能真实反应实际情况
rowcache一般都非常高，很少出问题
容易出问题的是librarycache
三）如何解决oracle的4031错误
简单的讲一下如何解决oracle的4031错误
1）alter system flush shared_pool;
临时性的解决办法，执行上面语句
这时sharedpool的librarycache里面所有的chunk会释放
会有大量的大大小小的chunk回到free里面去
这时4031错误暂时会缓解
但这只是治标不治本的一个办法
2）共享SQL
最好还是要判断一下哪些语句没有共享sql
然后把这个问题告诉开发人员
让开发人员去解决这个问题，让他共享sql
共享sql如果开发人员做不到的话
我们可以改一个参数cursor_sharing
sql语句没有共享有很多种原因
第一种原因是因为里面有字面值
没有使用绑定变量
第二种我们的sql语句里面加了空格、大小写、回车
造成书写不规范
对于第一种情况如果是字面值没有使用绑定变量
把cursor_sharing改成force以后主动可以解决这个问题
就是你如果使用字面值oracle会强行绑定变量
对于第二种改cursor_sharing对书写不规范这个问题是没法解决的
尽量的让开发人员去改，改不了的话
我们把cursor_sharing 改成force
3）把执行计划keep起来
oracle数据库中有一个包dbms_shared_pool
它可以实现keep功能
大量的硬解析出现，产生很多很小的free trunk
接着出现一个比较大的sql
这时候在sharedpool里面的free里面就找不到合适的truck使用
于是出现了4031错误。
也有可能数据库正常运行
free少了是sharedpool分的比较小
某个大sql产生4031不是因为硬解析过多造成的
而确实是这个sql太大了
这时可以使用dbms_shared_pool包里面的存储过程
将这个sql语句强行缓存到sql里面去
然后它永远不会被置换出来
它不会因为sharedpool空间不够了被挤出来
这时也可以避免一个大的sql语句
在后面执行时出现问题
举例讲
数据库里面容易出现这种情况
当oracle在free里面找不到大truck的时候
它会想办法到librarycache里面找大truck
找到以后把它释放了，把空间要回来
如数据库里面librarycache里面有一个很大的truck长时间没有执行
它被置换出去了到free里面去了
被分为两半使用了
如果后面在执行这个大truck的时候可能就没有空间了
这时候就容易出现4031错误
为了解决这个问题
可以将最大的前几个truck（我们可以排序）
强行keep起来
避免4031错误出现
要使用dbms_shared_pool包首先要创建它
方法是执行ORACLE_HOME目录下的/rdbms/admin/dbmspool.sql文件
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SQL> @?/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
View created.
Package body created.
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
包创建完成
此包的创建只能以管理员身份运行才能正常完成。
然后使用
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';
查一下sql里面有哪些比较大的
sharable_mem > 10000
占用sharedmemeory内存大于10K的哪些对象
然后把对象的拥有者和名字找出来
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SQL> 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'; 2 3
OWNER
----------------------------------------------------------------
NAME
----------------------------------------------------------------------------------------------------
DB_LINK NAMESPACE
---------------------------------------------------------------- ----------------------------
TYPE SHARABLE_MEM LOADS EXECUTIONS LOCKS PINS KEP
---------------------------- ------------ ---------- ---------- ---------- ---------- ---
CHILD_LATCH INVALIDATIONS
----------- -------------
SYSMAN
EMD_COLLECTION
BODY
PACKAGE BODY 33217 1 5 0 0 NO
3 0
SYS
DBMS_APPLICATION_INFO
TABLE/PROCEDURE
PACKAGE 16745 2 0 4 0 NO
3 0
SYSMAN
EM_PING
TABLE/PROCEDURE
PACKAGE 37238 2 0 2 0 NO
2 0
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
结果较多就不全列了
如其中的一行
SYS
DBMS_APPLICATION_INFO
TABLE/PROCEDURE
PACKAGE 16745 2 0 4 0 NO
3 0
包名DBMS_APPLICATION_INFO
拥有者SYS
KEP状态为NO，即没有被keep
把它keep到内存里面去
需要使用dbms_shared_pool.keep('对象名');
包的执行可以使用如下形式
SQL> execute dbms_shared_pool.keep('DBMS_APPLICATION_INFO');
或
SQL> begin
dbms_shared_pool.keep('DBMS_APPLICATION_INFO');
commit;
end;
执行一下
SQL> execute dbms_shared_pool.keep('DBMS_APPLICATION_INFO');
PL/SQL procedure successfully completed.
查询一下一keep的对象
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SQL> 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 = 'YES'; 2 3
OWNER
----------------------------------------------------------------
NAME
----------------------------------------------------------------------------------------------------
DB_LINK NAMESPACE
---------------------------------------------------------------- ----------------------------
TYPE SHARABLE_MEM LOADS EXECUTIONS LOCKS PINS KEP
---------------------------- ------------ ---------- ---------- ---------- ---------- ---
CHILD_LATCH INVALIDATIONS
----------- -------------
SYS
DBMS_APPLICATION_INFO
TABLE/PROCEDURE
PACKAGE 16745 2 0 6 0 YES
3 0
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
我的结果只有一条就是上面keep的DBMS_APPLICATION_INFO对象
因为以前没有做过keep工作
在dbms_shared_pool包执行时默认操作的对象的所有者是SYS
属于其它所有者的对象默认操作会报错
如这个对象
SYSMAN
EMD_COLLECTION
BODY
PACKAGE BODY 33217 1 5 0 0 NO
3 0
拥有者SYSMAN
对象名EMD_COLLECTION
如这样执行
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SQL> execute dbms_shared_pool.keep('EMD_COLLECTION');
BEGIN dbms_shared_pool.keep('EMD_COLLECTION'); END;
*
ERROR at line 1:
ORA-06564: object EMD_COLLECTION does not exist
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53
ORA-06512: at line 1
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
结果报错
解决办法要在对象名前面标示拥有者
SQL> execute dbms_shared_pool.keep('SYSMAN.EMD_COLLECTION');
PL/SQL procedure successfully completed.
执行成功
解除某个对象的keep状态可以使用dbms_shared_pool包提供的unkeep方法
SQL> execute dbms_shared_pool.unkeep('DBMS_APPLICATION_INFO');
PL/SQL procedure successfully completed.
和
SQL> execute dbms_shared_pool.unkeep('SYSMAN.EMD_COLLECTION');
PL/SQL procedure successfully completed.
执行清理shared_pool内存
alter system flush shared_pool;
命令后keep状态的对象仍然保持keep状态
而在oralce数据库重启后不再有处于keep状态的对象
4）如何增加sharedpool
使用命令
alter system set shared_pool_size=150M scope=both;
150M就是要设置的值
1、sga_target和sga_max_size的设置及关系
oracle以前的版本10以前
数据库里面重点有6个大的池子
在oracle老的版本里面每个池子需要给它供应大小
sharedpool，buffer cache,redolog buffer，stream，large，javapool
都要固定的大小，一旦固定大小它就不能变
当然我们可以改
但是有可能这种情况
sharedpool设了两个G大小但实际用了一个G
但是buffercache设了九个G它可能需要9.5个G
oralce里面有空闲空间，
但2G给了sharedpool，还浪费着呢，可buffercache还不够
所以oracle10开始做这么一件事情
oracle对SGA来讲
它统一设一个参数SGA target
比如设sga_target等于12个G
这个时候oracle的
sga_target里面包括sharedpool和buffercache那六个池子
把参数统一设了12个G以后
oracle对各个内存块六个池子oracle根据需求动态的去分配
我们从理论上看上去应该很好啊
不浪费空间
所以说oracle新的版本里面我们只设一个参数sga_target
这是个动态参数
在数据库运行期间我们可以动态去设置
SGA里面的六个池子空间可以动态分配
即保证了空间又没有浪费了空间
还有个参数sga_max_size
是个静态参数
一般的情况下
sga_target等于sga_max_size
但sga_max_size改完以后数据库需要重启
sga_max_size是用来约束sga_target的
举一个假设的例子：
数据库使用物理内存比如64G
我们给了oracle数据库50%，也就是给了SGA_target 32G
我们想这么做，也给了
max参数32G
target参数也是32G
这时假设没有max这个东西
target是32G
我们知道sga_target可以动态改
想把它改成36个G
结果多写了一个0，把它改成360个G了
这有可能，因为是动态参数
出现一个问题内存一共才64G
这时oracle要360个G
一下就会把内存所有都给了oracle
而且swap空间也给了oracle
这时系统会瞬间因为内存耗尽，操作系统挂起
操作系统挂起了oracle也就挂起了
会死机、导致数据文件损坏
所以sga_target可以动态设
但是一旦设错了数据库可能会引起死机
出现问题
所以需要另外设一个参数
oracle提供了一个参数max参数
平时
物理内存64G
可以把max参数设为48G
因为规定oracle target参数再怎么设置高
target只能在max的48个G以下设置
设40G或42G都没问题但不要超过max
max它是静态参数要改必须重启数据库
存在的目的就是为了约束设sga_target时的随意性
max设了个sga_target可用的最大值
现在看一下当前这两个参数的值
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 272M
sga_target big integer 272M
目前sga_target和sga_max_size大小为272M
2、shared_pool大小的设置
先看一下目前大小
SQL> show parameter shared_pool;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 8M
shared_pool_size big integer 0
shared_pool_size的默认值oracle设的是0M
就是未指定由oracle自动分配大小
给它分配个值
SQL> alter system set shared_pool_size=20;
System altered.
再看大小
SQL> show parameter shared_pool;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 8M
shared_pool_size big integer 4M
这里设置时shared_pool_size=20，20后面没有带参数，20被理解为20个字节
因为此参数设置时以4M为基本单位，
最终的值都是4M的整数倍，所以系统给了它一个值4M。
再去查一个参数
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SQL> select COMPONENT,CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 88080384
large pool 4194304
java pool 4194304
streams pool 0
DEFAULT buffer cache 180355072
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
ASM Buffer Cache 0
13 rows selected.
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
实际sharedpool的大小不是4M是88M
参数值改成了4M实际大小88M
并没有变化
我们可以设sga_target 1G或500M
目前我使用的是272M
然后orale根据
sga_target设置大小、shared_pool_size设置大小、系统的负载
自动的把sharedpool设为88M
我们也可以手工的把sharedpool设一下
如果小于当前值88M，oracle不予理会还是88M
如果这时shared_pool_size设为180M
oracle就会用180M
原理
新设置的参数大小必须大于先前sga_target总的自动给它分配的空间大小
才会使shared pool实际的空间大小发生改变
所以show parameter看的并不准
需要查询
这就是讲的增加sharedpool空间
新设置的参数必须大于sga_target目前总的自动给它分配的空间
才能在设置新的大小值后立即使实际值增加
例子：
SQL> alter system set shared_pool_size=100M;
System altered.
参数设置值
SQL> show parameter shared_pool;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 8M
shared_pool_size big integer 100M
实际大小
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
SQL> select COMPONENT,CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 104857600
large pool 4194304
java pool 4194304
streams pool 0
DEFAULT buffer cache 163577856
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
ASM Buffer Cache 0
13 rows selected.
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
这时候shared pool的实际空间发生了变化，
增大了，值为104M左右。
即使
alter system flush shared_pool;
此值也是sga自动分配给它的值，并且自动分配给它的值肯定大于自己设置的值。
5）保留区
我们知道shared_pool里面有free、librarycache、rowcache
随着硬解析的增加
free里面的chunk很多变小将来可能出现4031错误
我们也知道不管硬解析多少，随着时间的增长
free里面都有可能出现空间不够的情况因为大量的小的chunk
都有可能会出现数据库跑了一段时间以后出现一个大的sql
有时候4031错误看上去不能避免
oracle为了解决这个问题做了另一个事情
shared_pool里面单独的划出一块空间来，
保留区
叫shared_pool_reserved
这个空间，就是只是用来缓存大对象
当一个对象的尺寸超过一定的阈值的时候
它就不会到free里面去找空间
而是到保留区里面找空间
如果我们把保留区设的足够大的话
可以减少很多4031错误的产生
我们看一个查询
select REQUEST_MISSES from v$shared_pool_reserved;
就是用来查在保留区里请求空间失败的次数
只要是有一次就肯定会发生4031错误
因为既然到保留区里面找空间
说明是大对象
在保留区都找不到的话，它就会直接报错，报4031错误
所以这个数值最好是零
SQL> select REQUEST_MISSES from v$shared_pool_reserved;
REQUEST_MISSES
--------------
0
值为0说明从来没有发生因为在保留区找不到空间而产生4031错误的情况。
所以保留区我们要设的大一些。
如果查询值大于0，我们要调整参数shared_pool_reserved_size
SQL> show parameter shared;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 8M
shared_pool_size big integer 4M
shared_server_sessions integer
shared_servers integer 1
其中
shared_pool_reserved_size big integer 8M
8M即为值大小
如果REQUEST_MISSES大于0的话
或者数值比较大的话
我们要将reserved调大一些
shared_pool_reserved_size是一个静态参数所以修改要使用
SQL> alter system set shared_pool_reserved_size=10M scope=spfile;
System altered.
值修改后重启oracle才能生效。
系统重启然后查询参数值：
SQL> show parameter shared_pool;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 10M
shared_pool_size big integer 4M
设置保留区这也是4031错误的一个解决办法
四）解决oracle的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_size
alter system set shared_pool_size=150M scope=both;
上面的方法摘自老师的教案
简单回顾一下
1、flush一下 治标不治本
2、共享sql 最好使用绑定变量
绑定变量实现不了的话
可以改cursor_sharing只能解决一种办法，就是字面值没有使用绑定变量的问题。
3、我们可以对一些大的对象进行keep
4、单独的划出一块保留区
5、增加shared_pool的大小
解决4031错误基本就这五种办法
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2019-9-18 14:29:22 |
赞(
操作
