###################################################
原创文章,转载请注明出处!
作者:Lilge
链接:http://liglexiner.itpub.net/post/43018/517486
###################################################
修改了上次的undo tablespace的问题后,工作人员反映说,可以不调一下数据库,感觉查询很慢,于是看了下,不看不知道,一看吓一跳,呵呵~~~
DB Version:9.2.0.6
$ sqlplus '/as sysdba'
SQL> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0
SQL> show parameter log_buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 65536
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 25165824
SQL> show parameter large_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
large_pool_size big integer 0
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
这个比较可恨,pga竟然是0,谁设置滴???
调整内存参数:
SQL> alter system set pga_aggregate_target=100M scope=both;
System altered.
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 104857600
SQL> alter system set log_buffer=1048576 scope=spfile;
System altered.
SQL> alter system set sga_max_size=629145600 scope=spfile;
System altered.
SQL> alter system set shared_pool_size=104857600 scope=spfile;
System altered.
SQL> alter system set db_cache_size=314572800 scope=spfile;
System altered.
SQL> alter system set java_pool_size=52428800 scope=spfile;
System altered.
SQL> alter system set large_pool_size=20971520 scope=spfile;
System altered.
重启DB,使参数修改生效:
SQL> !lsnrctl stop
SQL> shutdown immediate;
SQL> startup;
ORA-00381: cannot use both new and old parameters for buffer cache size specification
报ORA-00381错误,意思是说指定buffer cache 大小的新旧参数不能一起使用,于是想起来,Oracle早起版本中使用的db_cache_buffers参数,而自Oracle 9i起,已经使用db_cache_size取而代之了。
生成pfile,并编辑pfile注释掉参数db_cache_buffers
SQL> create pfile='/opt/oracle/product/9.2.0/dbs/initopenview.ora' from spfile;
File created.
编辑initopenview.ora文件,在如下行加上注释:
#*.db_block_buffers=2000# MEDIUM
使用pfile启动数据库:
SQL> startup pfile='/opt/oracle/product/9.2.0/dbs/initopenview.ora';
ORACLE instance started.
生成spfile,替换之前的spfile文件:
SQL> create spfile from pfile;
File created.
SQL> shutdown immediate;
SQL> !lsnrctl start
SQL> startup;
通过修改sga的几个参数和pga_aggregate_target后,数据库的查询性能明显提升。
[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25748672/viewspace-1049537/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25748672/viewspace-1049537/