首页 > 数据库 >oracle11g 常用基本参数优化设置

oracle11g 常用基本参数优化设置

时间:2024-11-07 11:11:06浏览次数:5  
标签:name oracle select SQL 基本参数 优化 alter 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.		

标签:name,oracle,select,SQL,基本参数,优化,alter,oracle11g
From: https://www.cnblogs.com/ywdba668/p/18531768

相关文章

  • Centos7.8静默安装企业版Oracle11g和创建实例
    1、安装环境准备:A、系统版本和oracle11g企业版安装软件压缩包:[root@dbprimary07~]#cat/etc/redhat-releaseCentOSLinuxrelease7.8.2003(Core)[root@dbprimary07~]#uname-aLinuxdbprimary073.10.0-1127.el7.x86_64#1SMPTueMar3123:36:51UTC2020x86_64x......
  • oracle11g启动过程中加载配置文件
    oracle指定配置文件启动,要是不指定配置文件启动的话默认找的参数文件顺序如下:在oracle11g中oracle启动过程中默认会加载相应的配置文件来启动oracle服务。检查参数文件有两个,一个是spfile<ORACLE_SID>.ora文件,另一个是inti<ORACLE_SID>.ora文件。oracle软件服务安装完成后......
  • Java面试系列-MySQL面试题20道,InnoDB,索引类型,事务隔离级别,锁机制,MVCC,主从复制,慢查询,分
    文章目录1.MySQL中的InnoDB和MyISAM存储引擎有什么区别?2.MySQL中的索引类型有哪些?3.MySQL中的索引是如何工作的?4.MySQL中的事务隔离级别有哪些?5.MySQL中的锁机制有哪些?6.MySQL中的MVCC(多版本并发控制)是如何工作的?7.MySQL中的主从复制是如何工作的?8.MySQL中的分区......
  • Go-性能优化、优化分析、调优实战pprof
    使用官方自带benchmark进行基准性能测试第一个是函数名-核数第二个是执行次数第三个是一次执行时间第四个是一次执行的多大的内存第五个是一次执行申请几次内存slice用的时候在make()初始化切片时提供容量信息data:=make([]int,0)data:=make([]int,0,size)//goodpre......
  • 剑侠情缘2Online单机安装教程+汉化第六版任务优化+GM
    天给大家带来一款单机游戏的架设:剑侠情缘2Online只适用于单机娱乐,此教程是本人亲测所写,踩坑无数,如果你是小白跟着教程走也是可以搭建    亲测视频演示https://githubs.xyz/show/303.mp4 游戏安装步骤此游戏架设需要安装虚拟机,没有虚拟机的请先安装虚拟机,请见:ht......
  • 优化生产流程:Ftrans机台设备数据采集方案智能解决!
    机台设备数据采集在工业生产与制造、能源管理、交通运输、建筑物管理以及医疗健康等多个领域都有着广泛的应用场景。机台设备数据采集可以提高生产效率与质量控制、降低运营成本与维护成本、提升决策效率与准确性等。企业通常采取FTP脚本进⾏统一机台设备数据采集,虽然可以满⾜基础......
  • 基于ACO蚁群优化的VRPSD问题求解matlab仿真,输出规划路径结果和满载率
    1.程序功能描述基于ACO蚁群优化的VRPSD问题求解matlab仿真,输出ACO优化的收敛曲线,规划路径结果和每一条路径的满载率。2.测试软件版本以及运行结果展示MATLAB2022a版本运行 3.核心程序%搜索fori=1:Iterationiis_best=0;forj=1:Npop......
  • 5个维度对Kubernetes集群优化
    5个维度对Kubernetes集群优化一、节点配额和内核参数调整对于公有云上的Kubernetes集群,规模大了之后很容器碰到配额问题,需要提前在云平台上增大配额。这些需要增大的配额包括:虚拟机个数vCPU个数内网IP地址个数公网IP地址个数安全组条数路由表条数持久化存储大小......
  • PHP常见性能瓶颈分析与优化策略
    PHP常见性能瓶颈分析与优化策略在现代网站和应用开发中,PHP作为一种广泛使用的服务器端脚本语言,其性能优化至关重要。尽管PHP的易用性和强大的功能受到开发者青睐,但在高并发和大流量的环境下,性能瓶颈常常会影响网站的响应速度和用户体验。本文将分析PHP常见的性能瓶颈,并探讨相应的......
  • 网站关键词挖掘与优化:解锁流量潜力的关键
    在当今数字化时代,搜索引擎已成为人们获取信息、产品和服务的主要途径。对于网站运营者来说,如何有效地挖掘并优化关键词,以提升网站在搜索引擎结果页(SERP)中的排名,进而吸引更多目标流量,是至关重要的一环。本文将深入探讨网站关键词挖掘与优化的策略,帮助网站管理者解锁流量潜力,实......