Oracle 参数收取

最新推荐文章于 2024-07-03 15:53:51 发布

原创最新推荐文章于 2024-07-03 15:53:51 发布 · 243 阅读

0 ·

CC 4.0 BY-SA版权

本文详细介绍Oracle数据库中关键内存参数的检查与调整方法，包括sga_max_size、sga_target等，通过PL/SQL脚本验证实例名称，并收集当前参数设置，为数据库性能优化提供依据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 ----内存重要参数
sga_max_size
sga_target
shared_pool_size
db_cache_size
streams_pool_size
java_pool_size
large_pool_size
shared_pool_reserved_size

2 ----判断实例名是否匹配，如果不匹配则退出
var inst_name varchar2(40)
define v_inst_name=orcl
whenever sqlerror exit
declare
v_inst_name varchar2(40);
begin
select instance_name into :inst_name from v$instance where instance_name='&&v_inst_name';
exception when no_data_found then
raise_application_error(-20001,'Instance Name Does Not Match !');
end;
/

3 ----收集当前参数信息
set lines 180 pages 9999 heading off feed off trims off verify off
col name for a30
col display_value for a10
col PLAN for a10
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') cur_time from dual;
set feed 1 heading on
spool parameter.log
prompt ==============================================================
prompt Checking Current Parameter Values In Memory
select name , display_value , '0M' PLAN from v$parameter where name='sga_max_size'
union all
select name , display_value , '0M' PLAN from v$parameter where name='sga_target'
union all
select name , display_value , '0M' PLAN from v$parameter where name='shared_pool_size'
union all
select name , display_value , '0M' PLAN from v$parameter where name='db_cache_size'
union all
select name , display_value , '0M' PLAN from v$parameter where name='streams_pool_size'
union all
select name , display_value , '0M' PLAN from v$parameter where name='java_pool_size'
union all
select name , display_value , '0M' PLAN from v$parameter where name='large_pool_size'
union all
select name , display_value , '0M' PLAN from v$parameter where name='shared_pool_reserved_size';
spool