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.

活动

智算服务

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

oracle11g 常用基本参数优化设置

oracle11g 常用基本参数优化设置

相关文章

C语言：深入理解指针(1)

【Java】类与对象简介

【Java】引用传递的实例分析

【数据结构】时间复杂度与空间复杂度

【C++】优先级队列(容器适配器)

CUDA从入门到精通（三）——CUDA编程示例

Redis多级缓存指南：从前端到后端全方位优化！

表与索引分布在不同表空间，删除其中一个表空间的测试

主库添加临时表空间 dg端只会同步临时表空间

深入探讨Linux中的文本文件查看命令

作者介绍

最新文章

Redis多级缓存指南：从前端到后端全方位优化！

php phpexcel导文件爆500(爆内存) && 应对

Linux系统性能调优技巧

极限挑战：40亿个非负整数中找到没有出现的数（bit数组）

算法实战：快速找到100亿个URL中的重复项！

【malloc和calloc】malloc和calloc函数区别

热门文章

获取服务器CPU、内存等各类信息工具类

linux的存储管理

libcurl第十二课 内存分析

JS 变量、作用域与内存

Confluence 7 如何修改启动内存

JVM内存模型

热门标签

相关产品

弹性云主机

天翼云电脑（公众版）

对象存储

云硬盘

随机文章

Linux--CPU简述

模拟实现【memmove】函数

redis内存突然暴增，排查思路是什么

使用 Rust 实现零拷贝数据处理：性能优化的极致探索

oracle内存碎片

自定义类型：结构体

libcurl第十二课内存分析