Oracle第二話之調優工具

原創if 0 = -I can 釋出于2019-04-09 19:53:12 閱讀數 172 收藏

展開

1、告警日志

2、使用者程序trace檔案

3、動态性能視圖

4、statspack 安裝産生報告

5、生成AWR ADDM ASH 報告

6、explan --用來看執行計劃

資料庫出現任何問題，第一件事就是先看告警日志檔案。

1）告警日志内容：

（1）啟動時間以及操作模式的啟動和關閉指令。

（2）涉及實體結構的操作，使用ALTER DATABASE指令。

（3）表空間操作。

（4）所有日志切換與歸檔，包括所影響檔案的名稱。

（5）用于啟動執行個體的非預設初始化參數，含ALTER SYSTEM修改的參數。

（6）告警日志包含了影響資料庫結構和執行個體的各種操作的連續曆史記錄。

（7）告警日志不包含SQL語句（DML,DDL）

2）告警日志包含常見的告警與錯誤：

（1）檢查點不完全：說明日志檔案過小而引起日志切換頻繁。

（2）無法打開檔案：在資料庫啟動過程中産生。

（3）塊訛誤：某個資料檔案損壞引起。與DBA_EXTENTS視圖一起檢視。

（4）歸檔存在問題：歸檔目的地滿或不可用。

（5）死鎖。

3）檢視告警日志

（1）SQL> show parameter dump --檢視告警日志存放位址

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

background_core_dump string partial

background_dump_dest string /u01/app/oracle/oradata/PROD/b

dump

core_dump_dest string /u01/app/oracle/oradata/PROD/c

max_dump_file_size string UNLIMITED

shadow_core_dump string partial

user_dump_dest string /u01/app/oracle/oradata/PROD/u

（2）[oracle@gc1 ~]$ cd /u01/app/oracle/oradata/PROD/bdump

（3）[oracle@gc1 bdump]$ ls

alert_PROD.log prod_arc1_2547.trc prod_arc2_2576.trc prod_lgwr_2533.trc prod_rvwr_2542.trc

（4）[oracle@gc1 bdump]$ tail -f alert_PROD.log --檢視告警日志

4）定期管理：時間一長，告警日志就會增多，告警日志可以删除，但作業系統日志不可删（系統會起不來的）。

删除告警日志：

$ cat /dev/null > /u01/app/oracle/oradata/PROD/bdump/alert_PROD.log

使用者程序trace檔案一般由server程序産生。

1）SQL> show parameter trace; --檢視

log_archive_trace integer 0

sql_trace boolean FALSE --可以跟蹤session中的操作，可以顯示解析了幾次，傳回了幾行資料等

trace_enabled boolean TRUE

tracefile_identifier string

用于統計使用者執行的sql語句，可以通過這些統計資訊，判斷sql語句的執行過程和對資源調。預設，oracle對使用者執行的sql語句不做跟蹤。

2）實驗一 --跟蹤目前session

（1）SQL> grant alter session to scott; --授予更改會話權限個scott使用者

（2）SQL> conn scott/tiger --用scott使用者登入

（3）SQL> alter session set sql_trace=true; --在scott使用者會話中打開跟蹤，該為true就可以跟蹤了

（4）SQL> select empno,ename,job from emp where empno=7788;

EMPNO ENAME JOB

---------- ---------- ---------

7788 SCOTT ANALYST

（4）SQL> select empno,ename,job from emp where empno=7900;

7900 JAMES CLERK

（4）SQL> alter session set sql_trace=false; --關閉跟蹤

（5）sys使用者查詢追蹤資訊

SQL> col machine for a10

SQL> col username for a10

SQL> select p.spid,s.machine,s.username,s.sid,s.serial#,s.status from v$session s,v$process p where s.sid in (select sid from v$session where username is not null) and s.paddr=p.addr;

SPID MACHINE USERNAME SID SERIAL# STATUS

------------ ---------- ---------- ---------- ---------- --------

2585 gc1 SYS 289 3 ACTIVE

2618 gc1 SCOTT 275 8 INACTIVE

另一種查詢方法：

SQL> select username,sid,serial# from v$session where username is not null;

USERNAME SID SERIAL#

---------- ---------- ----------

SCOTT 275 8

SYS 289 3

（6）檢視檔案内容

$ cd /u01/app/oracle/oradata/PROD/udump --到udump下

$ ls -lht

-rw-r----- 1 oracle oinstall 82K May 3 12:50 prod_ora_2618.trc

$ more prod_ora_2618.trc --看不懂，需要借助工具

$ tkprof --檢視tkprof使用方法，将檔案變為可讀形式

$ tkprof prod_ora_2618.trc /home/oracle/scott.txt sys=no sort=fchela --将源檔案變換後儲存在/home/oracle/scott.txt 中，sys=no表示把sys使用者的操作排除在外，sort=fchela 表示排序。

$ more /home/oracle/scott.txt --檢視資訊

select empno,ename,job

from

emp where empno=7788

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.01 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 2 2 0 1

total 4 0.01 0.01 2 2 0 1 --傳回行數4行

Misses in library cache during parse: 1 --硬解析一次

Optimizer mode: ALL_ROWS

Parsing user id: 25

3）實驗二 --跟蹤其他session

（1）SQL> select username,sid,serial# from v$session where username is not null;

（2）SQL> exec dbms_system.set_sql_trace_in_session(275,8,sql_trace=>true); --在sys使用者下開起跟蹤scott使用者的會話

（3）在scott使用者下，使用綁定變量查詢

SQL> variable enum number;

SQL> exec :enum:=7788; --引用變量進行指派

SQL> select empno,deptno,job,sal,deptno from emp where empno=:enum;

EMPNO DEPTNO JOB SAL DEPTNO

---------- ---------- --------- ---------- ----------

7788 20 ANALYST 3000 20

SQL> exec :enum:=7900; --在指派

7900 30 CLERK 950 30

（4）SQL> exec dbms_system.set_sql_trace_in_session(275,8,sql_trace=>false); --關閉追蹤

select empno,deptno,job,sal,deptno

emp where empno=:enum

BEGIN :enum:=7788; END;

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 1

Fetch 0 0.00 0.00 0 0 0 0

total 2 0.00 0.00 0 0 0 1 --傳回兩行資料

Misses in library cache during parse: 1 --解析1次

Misses in library cache during execute: 1

4）搜集統計資訊

（1）對表進行分析擷取最新資料

SQL> analyze table scott.emp compute statistics; --對小表用compute

SQL> analyze table scott.emp estimate statistics; --對大表用estimate

（2）

a）

b）

c）

d）粘貼如下資訊

DBMS_STATS.GATHER_TABLE_STATS (

（3）SQL> exec DBMS_STATS.GATHER_TABLE_STATS ('SCOTT','EMP'); --用包收集SCOTT使用者的EMP表的統計資訊

（4）收集索引統計資訊

SQL> analyze index scott.pk_emp validate structure; --收集索引統計資訊

或

SQL> exec DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP'); --用包收集SCOTT使用者EMP表上的索引的統計資訊

（5）收集使用者統計資訊

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

（6）收集資料庫統計資訊

SQL> exec DBMS_STATS.GATHER_DATADASE_STATS( --格式不清除，看官方文檔

1）靜态視圖： --體系第一章表空間段區塊的查詢

在資料庫open狀态下通路，用于了解資料庫的實體結構資訊。

大部分以dba，all，user打頭，并多用複數形式。例如表和索引的統計資訊。

User_：存儲目前使用者所擁有的對象的相關資訊

All_：存儲目前使用者能夠通路的對象（包括使用者所擁有的對象和别的使用者授權通路的對象）的資訊。

Dba_：存儲所有使用者對象的資訊（預設隻能有sys/system使用者通路）

2）動态視圖：

大部分在mount下就可以通路，反映資料庫實時的狀态。

大部分以v$開頭，多用單數，從控制檔案和記憶體中讀出。

從v$fixed_table這個視圖查到所有的動态視圖的名稱

用于調優和資料庫監控

SQL> select name from v$tablespace; --查詢表空間

NAME

------------------------------

SYSTEM

UNDOTBS

SYSAUX

TEMPTS

USERS

SQL> select file#,name from v$datafile; --查詢資料檔案

FILE# NAME

---------- --------------------------------------------------

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf

SQL> select file#,name from v$tempfile; --查詢臨時檔案

1 /u01/app/oracle/oradata/PROD/disk5/temp01.dbf

3）系統統計資訊

統計資訊大部分都記錄在v$sysstat中。

（1）SQL> desc v$sysstat;

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

STATISTIC# NUMBER

NAME VARCHAR2(64)

CLASS NUMBER

VALUE NUMBER

STAT_ID NUMBER

（2）SQL> select count(*) from v$sysstat;

COUNT(*)

----------

363

（4）SQL> select name,value from v$sysstat;

（5）檢視SGA裡的統計資訊

SQL> select * from v$sgastat where name like 'log%';

POOL NAME BYTES

------------ -------------------------------------------------- ----------

log_buffer 2973696

shared pool log_simultaneous_copies 248

shared pool log file size history arr 168

shared pool log_checkpoint_timeout 12360

（6）檢視各個池的大小

SQL> select name,bytes/1024/1024 m from v$sgainfo;

NAME M

-------------------------------------------------- ----------

Fixed SGA Size 1.16325378

Redo Buffers 2.8359375

Buffer Cache Size 276

Shared Pool Size 112

Large Pool Size 4

Java Pool Size 4

Streams Pool Size 0

Granule Size 4

Maximum SGA Size 400

Startup overhead in Shared Pool 40

Free SGA Memory Available 0

4）等待事件統計資訊

（1）查詢等待事件

SQL> desc v$event_name;

SQL> select name from v$event_name where name like 'log%';

SQL> select name from v$event_name where name like 'buffer busy wait%';

--------------------------------------------------

buffer busy waits

（2）查詢等待事件的值

SQL> desc v$system_event;

SQL> col event for a30

SQL> select EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT from v$system_event where event like 'log%';

EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT

------------------------------ ----------- -------------- ----------- ------------

log file sequential read 8 0 0 .05

log file single write 8 0 0 .04

log file parallel write 2570 0 453 .18

log file sync 19 2 256 13.48

5）會話統計資訊

（1）SQL> desc v$session --查詢目前登入使用者資訊。

（2）SQL> select username,sid,serial# from v$session where username is not null;

（3）查詢更詳細的資訊

col username for a10

col name for a30

select se.sid,se.serial#,se.username,st.class,st.name,ses.value；

from v$session se,v$statname st,v$sesstat ses

where se.sid=ses.sid

and ses.statistic#=st.statistic#

and se.username is not null

and ses.value<>0

and se.username<>'SYS';

select se.sid,se.serial#,se.username,st.class,st.name,ses.value

and se.username='&username'; --可以動态檢視某個使用者的會話統計資訊

（4）class

1 代表執行個體活動

2 代表redo buffer活動

4 代表鎖

8 代表資料緩沖活動

16 代表并行活動

64 代表表通路

128 代表調試資訊

在本session中查詢，pga超過300k的使用者資訊。

select username,name,trunc(value/1024) k

from v$statname n,v$session s,v$sesstat t

where s.sid=t.sid

and n.statistic#=t.statistic#

and s.type='USER'

and s.username is not null

and n.name='session pga memory'

and t.value>300000;

USERNAME NAME K

---------- -------------------- ----------

SYS session pga memory 1403

報告就是各個快照統計值比較的結果

statspack的由來——statistics package

Inspect 檢查

Expect 期望

Respect 尊重

Prospect 展望

運作statspack 期間必須session 上設定TIMED_STATISTICS=TRUE,否則統計的資料将失真。

Job_queue_processes=15,該參數不大于1就生成不了報告。

兩個重要參數：

（1）SQL> show parameter job;

job_queue_processes integer 15 --該參數不大于1就生成不了報告

（2）SQL> show parameter timed;

timed_os_statistics integer 0

timed_statistics boolean TRUE --設定TIMED_STATISTICS=TRUE,否則統計的資料将失真

1）建立表空間

（1）SQL> select tablespace_name,contents,status from dba_tablespaces;

TABLESPACE_NAME CONTENTS STATUS

------------------------------ --------- ---------

SYSTEM PERMANENT ONLINE

UNDOTBS UNDO ONLINE

SYSAUX PERMANENT ONLINE

TEMPTS TEMPORARY ONLINE

USERS PERMANENT ONLINE

（2）SQL> select file#,name from v$datafile;

（3）SQL> create tablespace tools datafile '/u01/app/oracle/oradata/PROD/disk3/tool01.dbf' size 10m; --statspack至少需要200M，OCM考試會故意讓你設小，需要将它改大。

（4）SQL> select file#,name from v$datafile;

5 /u01/app/oracle/oradata/PROD/disk3/tool01.dbf

（5）SQL> alter database datafile 5 resize 200m; --将表空間改為200M

SQL> alter database datafile 5 autoextend on next 10m maxsize unlimited; --設為自動增長，無最大限制

（6）檢視文集大小

SQL> select file_id,file_name,tablespace_name,bytes/1024/1024 m from dba_data_files;

FILE_ID FILE_NAME TABLESPACE M

---------- -------------------------------------------------- ---------- ----------

1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf SYSTEM 325

2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf UNDOTBS 200

3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf SYSAUX 325

4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf USERS 50

5 /u01/app/oracle/oradata/PROD/disk3/tool01.dbf TOOLS 200

2）跑腳本 --注意是用sys使用者跑

（1）SQL> @?/rdbms/admin/spcreate

Enter value for perfstat_password: perfstat

Enter value for default_tablespace:TOOLS

Enter value for temporary_tablespace: TEMPTS

（2）檢視資訊

$ cd $ORACLE_HOME/rdbms/admin

$ ls |grep spcpkg

spcpkg.sql

$ more spcpkg.sql

3）調整快照級别為7

SQL> conn perfstat/perfstat

SQL> desc statspack;

SQL> exec statspack.MODIFY_STATSPACK_PARAMETER(I_SNAP_LEVEL=>7);

4）建立一個job，每隔五分鐘執行

[oracle@gc1 admin]$ pwd

/u01/app/oracle/product/10.2.0/db_1/rdbms/admin

[oracle@gc1 admin]$ ls |grep spauto

spauto.sql

$ vi spauto.sql --修改參數，原本1小時生成1個快照，該為5分鐘

spool spauto.lis

-- Schedule a snapshot to be run on this instance every hour, on the hour

variable jobno number;

variable instno number;

begin

select instance_number into :instno from v$instance;

dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/288,'MI'), 'trunc(SYSDATE+1/288,''MI'')', TRUE, :instno);

commit;

end;

SQL> select 12*24 from dual; --1小時有12個5分鐘，1天分為24個小時

12*24

288

5）執行@?/rdbms/admin/spauto --在perfstat使用者中，之前已經連接配接

（1）SQL> @?/rdbms/admin/spauto --跑腳本

如下是腳本中的内容，不用執行：

（SQL> select job, next_date, next_sec

2 from user_jobs

3 where job = :jobno;

JOB NEXT_DATE NEXT_SEC

---------- --------- ----------------

1 04-MAY-14 09:51:00

）

（2）SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; --檢視時間

TO_CHAR(SYSDATE,'YY

-------------------

2014-05-04 09:49:51

（3）SQL> select snap_id,snap_level from stats$snapshot; --檢視快照資訊

SNAP_ID SNAP_LEVEL

---------- ----------

1 7

（4）手動生成快照

SQL> exec statspack.snap();

SQL> select snap_id,snap_level from stats$snapshot;

3 7

6）跑腳本 --用SYS使用者跑

（1）SQL> show user

USER is "SYS"

（2）SQL> create table test(id number(8),name varchar2(30));

（3）插入100萬行資料 --爛代碼

declare

sql_text varchar2(100);

for i in 1..1000000 loop

sql_text:='insert into test values('||i||','''||'zhangsan'||i||''')';

execute immediate sql_text;

if mod(i,1000)=0 then

commit;

end if;

end loop;

commit;

7）檢視statspack報告 --如下5、中的3），發現這段代碼問題很多

8）清除快照

AWR是oracle的自動快照工具，一般每小時産生一個快照。

AWR（Automatic workload repository)工作負載自動存儲報告。

AWR是ORACLE 10g後推出的一個新技術構架，其實就是9i的statspack的演變和增強版本。通過AWR，ORACLE可自動采集，儲存和管理系統負載和性能統計資料，用于問題診斷分析和系統自動調整等。AWR面向的服務對象包括内部的ADDM和其他自動優化和診斷工具，以及外部的EM、SQL*PLUS等工具。AWR原理意圖如下所示。

即AWR資料主要由兩部分組成。

（1）儲存在記憶體中的系統負載和性能統計資料，主要通過v$視圖進行通路。

（2）ORACLE通過10g新的MMPN程序定期自動以快照（snapshot）形式将儲存在記憶體中的AWR資料，轉存到磁盤中，用于性能資料的長期儲存和分析，這些資料主要通過DBA_*視圖進行通路。

ADDM（automatic database dignostics monitor)資料庫自動診斷監控報告

ASH（active session history）曆史報告

可以通過v$ACTIVE_SESSION_HISTORY視圖檢視記憶體中ASH的資訊

AWR是oracle 10g中的一個新特性，類似于10g以前的statspack。不過在使用上要比statspack簡單，提供的新能名額要比statspack多很多，能更好的幫助DBA來發現資料庫的性能瓶頸。

AWR是oracle安裝好後自動啟動的，不需要特别的設定。收集的統計資訊存儲在SYSAUX表空間SYS模式下，以WRM$_*和WRH$_*的格式命名，預設會保留最近7天收集的統計資訊。每個小時将收集到的資訊寫到資料庫中，這一系列操作是由一個叫MMON的程序來完成的。

AWR存儲的資料分類：

WRM$表存儲AWR的中繼資料（awrinfo.sql腳本）

WRH$表存儲采樣快照的曆史資料（awrrpt.sql腳本）

WRI$表存儲同資料庫建議功能相關的資料（ADDM相關資料）

awr報表實際上是statpck報表的延伸，當然10gR2中還是報留了statpack,并且statpack也增加了對stream_pool的監控，awr與statpack的差別就是awr的快照的收集與維護更加自動化，預設的保留七天的快照，并且可以通過dbms_workload_repository表修改快照的收集頻率與快照的保留時間，dba要幹預的已經很少了，比statpack維護更簡單（sp是手動的，awr是自動的）。

awr與ash的最主要的差別在于：awr是平面的，全面的，ash是立體的，更側重于session的event跟蹤，由于業務量大的資料庫的event wait是瞬息萬變，awr很可能會監控不到，為了彌補這個不足，ash才可以對session的event進行跟蹤。

ash與addm的差別在于：addm更側重于基于對當資料庫目前狀态的分析，對存在的問題提供指導性的意見，可以說ash，addm是awr的補充，awr全面的收集資料庫的狀态，但ash/addm是側重要對收集的資料進行分析，并提供一些有益的建議。

1）準備工作 --設定導出報告存放位置

如圖所示：設定預設上傳和下載下傳位址，下面進行的sz操作會預設導出檔案到如下目錄中。

1）手工生成報告步驟

指令：手工抓取快照方式，快照級别TYPICAL ALL(采集名額更完整）

SQL> exec dbms_workload_repository.create_snapshot('TYPICAL'); --手工生成快照

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention => 14400,interval =>30,topnsql => 30); --保留10天10*24*60=14400分鐘，30分鐘一個快照，一個快照中最多儲存30個SQL

2）生成AWR ADDM ASH報告：

（1）生成statspack報告

a）SQL> @?/rdbms/admin/spreport

Instance DB Name Snap Id Snap Started Level Comment

------------ ------------ --------- ----------------- ----- --------------------

PROD PROD 1 04 May 2014 09:51 7

3 04 May 2014 09:54 7

11 04 May 2014 10:51 7

Enter value for begin_snap: 3 --從3開始

Enter value for end_snap: 11 --到11結束

Enter value for report_name: /home/oracle/statspack.1st --儲存位址

b）[oracle@gc1 ~]$ ls

Desktop h PROD spauto.lis spctab.lis statspack.1st

control01.bak flash logmnr prod_bak spcpkg.lis spcusr.lis utl

c）将檔案從虛拟機中取到windows下載下傳中

$ sz statspack.1st --導出檔案

d）打開“我的電腦”收藏夾裡的“下載下傳”中檢視

（2）生成AWR報告

a）SQL> show user

USER is "PERFSTAT"

b）SQL> select job from dba_jobs;

JOB

c）SQL> exec dbms_job.remove(1); --如果這句話失敗，執行SQL> exec dbms_ijob.remove(1);

d）SQL> conn /as sysdba

e）SQL> @?/rdbms/admin/awrrpt

Enter value for report_type: --回車就可以，預設html格式

Enter value for num_days: 2 --檢視2天内的報告

Instance DB Name Snap Id Snap Started Level

------------ ------------ --------- ------------------ -----

PROD PROD 29 04 May 2014 09:47 1

30 04 May 2014 11:00 1

31 04 May 2014 11:29 1

32 04 May 2014 11:34 1

33 04 May 2014 11:35 1

Enter value for begin_snap: 32

Enter value for end_snap: 33

Enter value for report_name: /home/oracle/myawr.html --存放位址

f）$ ls --檢視

control01.bak flash logmnr PROD spauto.lis spctab.lis statspack.1st

Desktop h myawr.html prod_bak spcpkg.lis spcusr.lis utl

g）$ sz myawr.html

（3）生成ADDM報告 --預設類型TXT

a）SQL> @?/rdbms/admin/addmrpt

Enter value for begin_snap: 32 --開始

Enter value for end_snap: 33 --結束

Enter value for report_name: /home/oracle/myaddm.txt --存放位址

b）$ ls --檢視

control01.bak flash logmnr myawr.html prod_bak spcpkg.lis spcusr.lis utl

Desktop h myaddm.txt PROD spauto.lis spctab.lis statspack.1st

e）$ sz myaddm.txt

（4）生成ASH報告

a）SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; --檢視目前時間

2014-05-04 11:51:47

b）SQL> @?/rdbms/admin/ashrpt

Enter value for report_type: --直接回車，預設類型html

Enter value for begin_time: -30 --看30分鐘前的資訊

Enter value for duration: 30 --到現在的

Enter value for report_name: /home/oracle/myash.html --存儲位址

c）$ ls

control01.bak flash logmnr myash.html PROD spauto.lis spctab.lis statspack.1st

Desktop h myaddm.txt myawr.html prod_bak spcpkg.lis spcusr.lis utl

d）$ sz myash.html

3）檢視statspack報告 --文檔補充中詳細解讀statspack報告，發現之前的代碼問題很大。

檢視統計報表，首先檢視十項内容：

１）首要的５個等待事件（TOP 5 WAIT EVENTS)

２）負載簡檔（LOAD PROFILE）

３）執行個體效率點選率（INSTANCE EFFICIENCY HIT RATIOS)

４）等待事件（WAIT EVENTS)

５）闩鎖等待（LATCH WAITS) latch activity

６）首要的SQL(TOP SQL) sql ordered by cpu

７）執行個體活動（INSTANCE ACTIVITY) instance activity

８）檔案I/O（FILE I/O) ordered by ios

９）記憶體配置設定（MEMORY ALLOCATION) sga memory summary library cache activity

１０）緩沖區等待（BUFFER WAITS) buffer pool advisory

cd /home/oracle

more statspack.1st

１１）PGA統計資訊（PGA STATS）pga aggr target stats pga memory advisory

4）檢視AWR報告 --直接用浏覽打開檢視

6、explan --用來看執行計劃

explan多用于開發階段，隻生成執行計劃。

SQL> @?/rdbms/admin/utlxplan

也可以SQL> @?/rdbms/admin/utlxpls.sql

utlxplan腳本如果裝過autotrace的都跑過了。

1）執行一句話

SQL> explain plan set statement_id='p_emp_dept' for select e.empno,ename,e.sal,d.deptno from scott.emp e,scott.dept d where e.deptno=d.deptno; --id值是自己設定的名字

2）檢視執行計劃

SQL> col operation for a18

SQL> col options for a15

SQL> col object_name for a10

SQL> col object_type for a15

SQL> select a.operation,options,object_name,cost,object_type,id,parent_id from plan_table a where statement_id='p_emp_dept' order by id;

OPERATION OPTIONS OBJECT_NAM COST OBJECT_TYPE ID PARENT_ID

------------------ --------------- ---------- ---------- --------------- ---------- ----------

SELECT STATEMENT 2 0

NESTED LOOPS 2 1 0

TABLE ACCESS FULL EMP 2 TABLE 2 1

INDEX UNIQUE SCAN PK_DEPT 0 INDEX (UNIQUE) 3 1

另一種檢視方法： --檢視執行計劃

SQL> select * from table(dbms_xplan.display);

------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 14 | 280 | 2 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 14 | 280 | 2 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 238 | 2 (0)| 00:00:01 |

|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |

--但是沒有統計資訊，看不到讀磁盤的次數。因為隻産生執行計劃，沒有真正執行語句。

3）另一種常用的寫法 --檢視執行計劃

SQL> set pagesize 0 linesize 200 --不分頁，不換行

SQL> set timing on --檢視執行所消耗的時間

SQL> alter session set statistics_level=all; --把統計級别調高

SQL> select e.empno,e.ename,e.sal,d.deptno,d.dname from scott.emp e,scott.dept d where e.deptno=d.deptno; --查資料

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); --檢視執行計劃，沒成功

4）utlbstat.sql和utlestat.sql --很少使用，也是用于檢視執行計劃

我們可以定義一個開始時間和結束時間，來分析一天當中的繁忙時段，這兩個腳本用的較少，收集的統計資訊不完整。b代表begin，e代表end。用的較多的是statspack和awr（自動快照工具）。

報告用來比較的，是以要加上一個時間段的前提才有意義。

要對比繁忙階段和一般時段（baseline)。單個快照是看不出問題的。

5）v$sql或v$sql_plan --檢視執行計劃

用于根據sql_id檢視相應的執行計劃：

（1）SQL> select e.empno,e.ename,e.sal,d.deptno,d.dname from scott.emp e,scott.dept d where e.deptno=d.deptno;

（2）SQL> select sql_id,sql_text from v$sql where sql_text like 'select e.empno,e.ename,e.sal,d.deptno,d.dname from scott.emp e,scott.dept d where e.deptno=d.deptno%';

SQL_ID SQL_TEXT

------------- ---------------------------------------------------

2hjnq24dd2khj select e.empno,e.ename,e.sal,d.deptno,d.dname from

scott.emp e,scott.dept d where e.deptno=d.deptno

（3）SQL> select plan_table_output from table(dbms_xplan.display_cursor('2hjnq24dd2khj'));

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 3 (100)| |

| 1 | NESTED LOOPS | | 14 | 420 | 3 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL | EMP | 14 | 238 | 2 (0)| 00:00:01 |

| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |

|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |