欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle数据库下五种解决ORA-04031错误问题的方法

程序员文章站 2023-11-20 08:46:40
如何解决ora-04031错误 一)查找执行次数为一的语句 select sql_fulltext from v$sql where executions=1 order by sql_text;...

如何解决ora-04031错误

一)查找执行次数为一的语句

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错误基本就这五种办法