Oracle利用awrrpt.sql導出DB效能資料

 Oracle Database有一個AWR的工具,它收集了與效能有關的統計數據。

首先進入$ORACLE_HOME/rdbms/admin的目錄下,登入sqlplus並執行一個腳本:@awrrpt.sql,接著按照提示就可以將效能資料導出成txt或html的格式


SQL> @awrrpt.sql


Current Instance

~~~~~~~~~~~~~~~~


   DB Id    DB Name      Inst Num Instance

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

  259210000 TEST                1 TEST



Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type: html


Type Specified:  html  <--輸入html,表示要導出成網頁的格式



Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


   DB Id     Inst Num DB Name      Instance     Host

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

* 259210000         1 TEST         TEST         TEST_DB


Using  259210000 for database Id

Using          1 for instance number



Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.



Enter value for num_days: 14  <--選擇snapshot的天數


Listing the last 14 days of Completed Snapshots


                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

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

TEST         TEST             79405 08 Feb 2024 01:00      1

                              79406 08 Feb 2024 02:00      1

                              79407 08 Feb 2024 03:00      1

(略)

                              79603 16 Feb 2024 07:00      1

                              79604 16 Feb 2024 08:00      1

                              79605 16 Feb 2024 09:00      1




Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 79405

Begin Snapshot Id specified: 79405 <--輸入開始的Snap Id


Enter value for end_snap: 79605

End   Snapshot Id specified: 79605  <--輸入結束的Snap Id




Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_79405_79605.html.  To use this name,

press <return> to continue, otherwise enter an alternative.


Enter value for report_name:  <--輸入指定的檔名,如不輸入,則以預設值


Using the report name awrrpt_1_79405_79605.html


<html lang="en"><head><title>AWR Report for DB: TEST, Inst: TEST, Snaps: 79405-79605</title>

<style type="text/css">

body.awr {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;color:black; background:White;}

pre.awr  {font:8pt Courier;color:black; background:White;}

(略)


最後會產生一個html的檔案,就可以看指定的時段內,DB的效能資料了。