oracle11g 常用基本参数优化设置
1、进程及会话数
进程默认150，会话默认是247;查看进程及会话数
show parameter process;
show parameter sessions;
2、修改进程及会话数
alter system set processes=1250 scope=spfile;
alter system set sessions=1380 scope=spfile;
SQL> alter system set processes=1250 scope=spfile;
System altered.
SQL> alter system set sessions=1380 scope=spfile;
System altered.
特别说明：
修改processes和sessions值必须重启oracle服务器才能生效
ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关，它们的关系如下：
sessions=(1.1*process+5)
3、查询数据库当前进程的连接数和当前会话的连接数
select count(*) from v$process;
select count(*) from v$session;
4、查看数据库的并发连接数
select count(*) from v$session where status='ACTIVE';
5、查看当前数据库建立的会话情况
select sid,serial#,username,program,machine,status from v$session;
6、查询数据库允许的最大连接数
select value from v$parameter where name = 'processes';或者：show parameter processes;
7、修改数据库允许的最大连接数
alter system set processes = 300 scope = spfile;
(需要重启数据库才能实现连接数的修改)
8、重启数据库
shutdown immediate;
startup;
9、查看表空间存储位置
select file_name , tablespace_name from dba_data_files;
SQL> select file_name , tablespace_name from dba_data_files;
select file_name , tablespace_name from dba_data_files
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> alter database open;
Database altered.
SQL>
SQL> set linesize 300;
SQL> select file_name , tablespace_name from dba_data_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/oracle/tools/oracle11g/oradata/orcl/users01.dbf
USERS
/u01/oracle/tools/oracle11g/oradata/orcl/undotbs01.dbf
UNDOTBS1
/u01/oracle/tools/oracle11g/oradata/orcl/sysaux01.dbf
SYSAUX
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/oracle/tools/oracle11g/oradata/orcl/system01.dbf
SYSTEM
10、查看游标数和查看当前打开的游标数目
查看游标数
SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
open_cursors integer 300
SQL>
查看当前打开的游标数目
SQL> select count(*) from v$open_cursor;
COUNT(*)
----------
8
11、修改最大游标数
SQL> alter system set open_cursors=1000 scope=both;
System altered.
SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
open_cursors integer 1000
SQL>
12、字符集（建议建库时就设置好）
--查询数据库服务端的字符集：NLS_LANG = language_territory.charset
select * from nls_database_parameters;
--常见服务器参数设置
export NLS_LANG=AMERICAN_AMERICA.UTF8
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
13、sga pga大小
oltp系统参考（要设置好，防止内存抖动）： sga=内存80%80% pga=内存80%20%
--如果要防止高并发情况下的内存抖动,考虑固定内存:
show parameter memory;
show parameter sga;
show parameter pga;
--如果要防止高并发情况下的内存抖动,考虑固定内存
alter system set memory_target=4096m scope=spfile;
alter system set memory_max_target=4096m scope=spfile;
alter system set sga_target=3027m scope=spfile;
alter system set sga_max_size=3027m scope=spfile;
alter system set pga_aggregate_target=3027m scope=spfile;
alter system set pga_aggregate_target=1024m scope=spfile;
SQL> set linesize 200;
SQL> show parameter memory;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL>
SQL>
SQL> show parameter sga;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1136M
sga_target big integer 1136M
SQL>
SQL>
SQL> show parameter pga;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
pga_aggregate_target big integer 376M
SQL>
14、控制文件
说明：控制文件默认2个，建议增加一个在不同位置
show parameter control;
alter system set control_files="/u01/oracle/tools/oracle11g/oradata/orcl/control01.ctl,/u01/oracle/tools/oracle11g/flash_recovery_area/orcl/control02.ctl" scope=spfile;
shutdown immediate;
startup;
show parameter control;
15、redo日志
redo 默认50M, 改成100M或更大，具体根据每天产生redo的量来设置
--查看相关设置
select member from v$logfile;
select group#,members,bytes/1024/1024,status from v$log;
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/oracle/tools/oracle11g/oradata/orcl/redo03.log
/u01/oracle/tools/oracle11g/oradata/orcl/redo02.log
/u01/oracle/tools/oracle11g/oradata/orcl/redo01.log
SQL> select group#,members,bytes/1024/1024,status from v$log;
GROUP# MEMBERS BYTES/1024/1024 STATUS
---------- ---------- --------------- ------------------------------------------------
1 1 50 INACTIVE
2 1 50 CURRENT
3 1 50 INACTIVE
--增加日志组
alter database add logfile group 4 ('/u01/oracle/tools/oracle11g/oradata/orcl/redo04.log') size 100M;
alter database add logfile group 5 ('/u01/oracle/tools/oracle11g/oradata/orcl/redo05.log') size 100M;
alter database add logfile group 6 ('/u01/oracle/tools/oracle11g/oradata/orcl/redo06.log') size 100M;
--redo每组默认3组每组一个成员，建议每组两个成员以上
alter database add logfile member '/u01/oracle/tools/oracle11g/oradata/orcl/redo04_2.log' to group 4;
alter database add logfile member '/u01/oracle/tools/oracle11g/oradata/orcl/redo05_2.log' to group 5;
alter database add logfile member '/u01/oracle/tools/oracle11g/oradata/orcl/redo06_2.log' to group 6;
--切换
alter system switch logfile;
--删除之前小的日志组
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3；
16、undo(20~30G,具体看实际业务而定)
--设置undo_retention 参数，默认是900s ，推荐设置为设置为10800，即3个小时
show parameter undo_retention;
alter system set undo_retention=10800 scope=spfile;
--查看undo表空间大小
select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';
--增大undo表空间大小
alter database datafile '/home/u01/app/oracle/oradata/ytzx/undotbs01.dbf' resize 10240M;
--给undo表空间增加数据文件
alter tablespace UNDOTBS1 add datafile '/home/u01/app/oracle/oradata/ytzx/undotbs2.dbf' size 10240M autoextend on;
--查看现在undo表空间大小
select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';
--查看表空间大小
SQL> select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';
current undo size(M)
--------------------
30
--增大undo表空间大小到100M
alter database datafile '/u01/oracle/tools/oracle11g/oradata/orcl/undotbs01.dbf' resize 100M;
SQL> alter database datafile '/u01/oracle/tools/oracle11g/oradata/orcl/undotbs01.dbf' resize 100M;
Database altered.
--查看此时undo表空间大小
SQL> select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';
current undo size(M)
--------------------
100
--给undo表空间增加数据文件
alter tablespace UNDOTBS1 add datafile '/u01/oracle/tools/oracle11g/oradata/orcl/undotbs02.dbf' size 100M autoextend on;
--查看此时undo表空间大小
SQL> select sum(bytes)/1024/1024 "current undo size(M)" from dba_data_files where tablespace_name='UNDOTBS1';
current undo size(M)
--------------------
200
17、临时表空间(20~30G,具体看实际业务而定)
--查看临时表空间大小、是否自动扩展
select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files;
增加现有临时文件temp01.dbf的大小到50M
ALTER DATABASE TEMPFILE '/u01/oracle/tools/oracle11g/oradata/orcl/temp01.dbf' RESIZE 50M ;
增加新的临时文件temp02.dbf的大小100M,自动扩展每次增加10M 最大可增加到10G
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oracle/tools/oracle11g/oradata/orcl/temp02.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oracle/tools/oracle11g/oradata/orcl/temp02.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 10G;
SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/oracle/tools/oracle11g/oradata/orcl/temp02.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 10G;
Tablespace altered.
SQL>
SQL> !ls -lh /u01/oracle/tools/oracle11g/oradata/orcl/temp02.dbf
-rw-r----- 1 oracle oinstall 101M Sep 23 17:02 /u01/oracle/tools/oracle11g/oradata/orcl/temp02.dbf
18.查看表空间使用率SQL
SQL>set line 200 pagesize 5000
SQL> col f.tablespace_name format a15
SQL> col d.tot_grootte_mb format a10
SQL> col ts-per format a8
select upper(f.tablespace_name) "TS-name",
d.tot_grootte_mb "TS-bytes(m)",
d.tot_grootte_mb - f.total_bytes "TS-used (m)",
f.total_bytes "TS-free(m)",
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,
2),
'990.99') "TS-per"
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) total_bytes,
round(max(bytes) / (1024 * 1024), 2) max_bytes
from sys.dba_free_space
group by tablespace_name) f,
(select dd.tablespace_name,
round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb
from sys.dba_data_files dd
group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
order by 5 desc;
SQL> set line 200 pagesize 5000
SQL> col f.tablespace_name format a15
SQL>
SQL> col d.tot_grootte_mb format a10
SQL> col ts-per format a8
SQL>
select upper(f.tablespace_name) "TS-name",
d.tot_grootte_mb "TS-bytes(m)",
d.tot_grootte_mb - f.total_bytes "TS-used (m)",
f.total_bytes "TS-free(m)",
to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,
2),
'990.99') "TS-per"
from (select tablespace_name,
round(sum(bytes) / (1024 * 1024), 2) total_bytes,
round(max(bytes) / (1024 * 1024), 2) max_bytes
from sys.dba_free_space
group by tablespace_name) f,
(select dd.tablespace_name,
round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb
from sys.dba_data_files dd
group by dd.tablespace_name) d
where d.tablespace_name = f.tablespace_name
order by 5 desc;
8 9 10 11 12 13 14 15 16 17 18
TS-name TS-bytes(m) TS-used (m) TS-free(m) TS-per
------------------------------ ----------- ----------- ---------- --------
SYSTEM 670 669.94 .06 99.99
UNDOTBS1 75 70.81 4.19 94.41
SYSAUX 490 461.69 28.31 94.22
USERS 5 1.31 3.69 26.20
SQL>
19、查看表空间是否开启自动扩展
SQL> select file_name,tablespace_name,autoextensible from dba_data_files;
FILE_NAME
--------------------------------------------------
TABLESPACE_NAME AUT
------------------------------ ---
/u01/oracle/tools/oracle11g/oradata/orcl/users01.d
bf
USERS YES
/u01/oracle/tools/oracle11g/oradata/orcl/undotbs01
.dbf
UNDOTBS1 YES
/u01/oracle/tools/oracle11g/oradata/orcl/sysaux01.
dbf
SYSAUX YES
/u01/oracle/tools/oracle11g/oradata/orcl/system01.
dbf
SYSTEM YES
20.调整时间窗口
--查询窗口定义详情--10g
select window_name,repeat_interval,duration,enabled from dba_scheduler_windows;
--查询窗口定义详情--11g和12c（周一到周五每晚10点开始收集统计信息，duration是4h；周六周日早上6点开始收集统计信息，duration是20h）
SQL> select window_name,repeat_interval,duration,enabled from dba_scheduler_windows;
--根据具体业务情况调整
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW','repeat_interval','freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW','repeat_interval','freq=daily;byday=SUN;byhour=22;byminute=0;bysecond=0');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW', 'duration', '+000 08:00:00');
EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW', 'duration', '+000 08:00:00');
exec dbms_scheduler.disable('WEEKNIGHT_WINDOW', TRUE);
exec dbms_scheduler.disable('WEEKEND_WINDOW', TRUE);
SQL> col REPEAT_INTERVAL FOR A70;
SQL> col REPEAT_INTERVAL FOR A100;
SQL> select window_name,repeat_interval,duration,enabled from dba_scheduler_windows;
WINDOW_NAME REPEAT_INTERVAL DURATION ENABL
------------------------------ ---------------------------------------------------------------------- --------------------------------------------------------------------------- -----
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
TUESDAY_WINDOW freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
WEDNESDAY_WINDOW freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
FRIDAY_WINDOW freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00 TRUE
SATURDAY_WINDOW freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0 +000 08:00:00 TRUE
SUNDAY_WINDOW freq=daily;byday=SUN;byhour=22;byminute=0;bysecond=0 +000 08:00:00 TRUE
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00 FALSE
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 FALSE
9 rows selected.
