核心：減少IO——隻要将大多數磁盤操作轉換成記憶體操作，資料庫系統的效率就會顯著提高。
一、記憶體優化
1.1 SGA
1. sga_max_size：配置設定給SGA的最大記憶體。
SGA不能太大，一般設定可以設定為目前記憶體大小即可。靜态參數，改後重新開機生效。
SQL> show parameter sga_max_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 3200M
SQL> alter system set sga_max_size=3500M scope=spfile;
System altered.
SQL> shutdown immediate; [遇到報錯]：
SQL> startup;
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 3674210304 cannot be set to more than MEMORY_TARGET 3355443200. [原因]：修改sga_max_size=3500M之後，停機重新開機報錯，因為設定的值過大，無法啟動，甚至無法startup nomount;
[解決方法]：找到參數檔案目錄，由于spfile是二進制的無法直接修改，需要從動态檔案生成一個靜态檔案，修改sga_max_size的值，并通過pfile啟動資料庫
① 建立一個pfile檔案
SQL> create pfile from spfile; ② 修改pfile中的值
[oracle@test1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@test1 dbs]$ vim initorcl.ora
...
*.sga_max_size=3000016000
... ③ 通過pfile啟動Oracle
SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup pfile=/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora
ORACLE instance started. ④ 備份或删除原有的spfile，通過pfile生成新的spfile
[oracle@test1 dbs]$ mv spfileorcl.ora spfileorcl.ora.bak
SQL> create spfile from pfile; 2. lock_sga：将SGA鎖定在實體記憶體，不使用swap。靜态參數，改後重新開機生效。
SQL> show parameter lock_sga;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
SQL> alter system set lock_sga=true scope=spfile; 3. sga_target：自動共享記憶體管理。
指定了SGA可以使用的最大記憶體大小，而SGA中各個記憶體的大小由Oracle自行控制，不需要人為指定。Oracle可以随時調節各個區域的大小，使之達到系統性能最佳狀态的個最合理大小，并且控制他們之和在SGA_TARGET指定的值之内。一旦給SGA_TARGET指定值後（預設為0，即沒有啟動ASMM），就自動啟動了ASMM特性。
SQL> show parameter sga_target;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0
SQL> alter system set sga_target=3200m; 1.2 PGA
PGA優化就是将大規模資料排序放在PGA裡運作。
1. PGA排序區自動管理
SQL> select name,value,isdefault
from v$parameter
where name in ('pga_aggregate_target','workarea_size_policy');
NAME VALUE ISDEFAULT
------------------------------------------------------------------------------
pga_aggregate_target 0 TRUE
workarea_size_policy AUTO TRUE pga_aggregate_target：定義了執行個體中所有伺服器程序的PGA總和。
workarea_size_policy：是否為排序自動管理。
2. 檢視PGA狀态
SQL> select name,round(value/1024/1204,2) as "value(MB)" from v$pgastat
where name in ('aggregate PGA target parameter','aggregate PGA auto target','cache hit percentage'); -- 三個重要參數
NAME value(MB)
------------------------------------------------
aggregate PGA target parameter 1088.64 --目前系統PGA總和
aggregate PGA auto target 864.37 -- 排序區配置設定的記憶體大小
cache hit percentage 0 -- 排序區完成比例 3. 調整PGA記憶體大小
alter system set PGA_aggregate_target=1500M
show parameter pga_aggregate_target; 二、IO優化
2.1 檔案層面
1. 給I/O帶來很大負荷的是重做日志檔案、資料檔案、索引檔案的讀寫。
2. 資料檔案與重做日志檔案盡量放在不同的磁盤上；
原因：CKPT、DBWn、SERVER三大程序都對資料檔案進行操作； LGWR、ARCn、SERVER三大程序都對重做日志檔案進行操作； 若兩個檔案存放在同一磁盤上，會導緻兩個檔案的I/O競争非常大。
3. 表和索引分别存放在不同的表空間中，因為一個表和表上的索引是明顯存在競争的。
2.2資料檔案IO資訊
select a.file# "檔案号",NAME "檔案名",phyblkrd "讀的實體塊數",phyblkwrt "寫的實體塊數",readtim "讀時間",writetim "寫時間" from v$filestat a join v$datafile b
on a.FILE#=b.FILE#;
檔案号 檔案名 讀的實體塊數 寫的實體塊數 讀時間 寫時間
-----------------------------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/orcl/system01.dbf 19369 15060 119 906
2 /u01/app/oracle/oradata/orcl/sysaux01.dbf 69371 241200 280 13425
3 /u01/app/oracle/oradata/orcl/undotbs01.dbf 27206 78275 69 3092
4 /u01/app/oracle/oradata/orcl/users01.dbf 5272 0 7 0
5 /u01/app/oracle/oradata/orcl/example01.dbf 582 0 5 0
6 /u01/app/oracle/oradata/orcl/test01.dbf 2 0 0 0
7 /u01/app/oracle/oradata/orcl/undotbs02.dbf 22 0 0 0
8 /u01/app/oracle/oradata/orcl/testtbs01.dbf 2 0 0 0
8 rows selected 2.3 重做日志優化
redo-log不能設定太小，太小會造成日志組的頻繁切。切換時還要觸發CKPT。CKPT還要讀寫資料檔案和控制檔案。是以會産生大量的輸入輸出。
檢視redo-log的切換曆史
select sequence#,to_char(first_time,'RR-MM-DD HH:MM:SS') "Date Time" from v$log_history;
SEQUENCE# Date Time
......
405 21-11-08 10:11:54
406 21-11-08 10:11:57
407 21-11-08 11:11:15
408 21-11-09 02:11:36
409 21-11-09 07:11:03
410 21-11-09 11:11:46 增加redo-log大小
ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo06.log') SIZE 500M; ![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)