spcusr
spdtab
spdusr
在這一步
SQL> @spdrop
同義詞已丟棄
視圖已丟掉
同義詞已丟棄
視圖已丟掉
同義詞已丟棄
用戶已丟棄
NOTE:
SPDUSR complete
SQL>
三
運行statspack
如果一切正常
SQL>execute statspack
PL/SQL procedure successfully completed
SQL>execute statspack
PL/SQL procedure successfully completed
SQL>@spreport
…
可是有可能你會得到以下錯誤
SQL> exec statspack
BEGIN statspack
*
ERROR at line
ORA
ORA
ORA
ORA
ORA
這是Oracle的一個Bug
該Bug自
這個問題只會出現在多位的字符集
該腳本錯誤部分
select l_snap_id
...........
substr 會將多位的字符
四
Statspack正確安裝以後
先來看看spauto
dbms_job
這個job任務定義了收集數據的時間間隔
一天有
我們可以修改spauto
dbms_job
然後我們執行spauto
SQL>
SQL>
SQL>
SQL>
SQL> variable jobno number;
SQL> variable instno number;
SQL> begin
PL/SQL 過程已成功完成
SQL>
Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:
JOBNO
Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init
parameter
than
SQL> show parameter job_queue_processes
NAME TYPE VALUE
job_queue_processes integer
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
SQL> select job
JOB NEXT_DATE NEXT_SEC
SQL>
五
調用spreport
SQL> @spreport
DB Id DB Name Inst Num Instance
Completed Snapshots
Snap Snap
Instance DB Name Id Snap
eygle EYGLE
………………
eygle EYGLE
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
輸入 begin_snap 的值:
Begin Snapshot Id specified:
輸入 end_snap 的值:
End Snapshot Id specified:
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_
press <return> to continue
輸入 report_name 的值: rep
Using the report name rep
這樣就生成了一個報告
ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID
STATSPACK report for
DB Name DB Id Instance Inst Num Release OPS Host
EYGLE
:ela := ;
*
ERROR 位於第
ORA
PLS
(
<a double
countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute
foralltimetimestampintervaldate
<a string literal with character set specification>
<a number><a single
符號
ORA
PLS
(
<a double
countcurrentexistsmaxminpriorsqlstddevsumvarianceexecute
foralltimetimestampintervaldate
<a stri
六
移除一個定時任務
SQL> select job
JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL
SQL> execute dbms_job
PL/SQL procedure successfully completed
七
刪除stats$snapshot數據表中的相應數據
SQL> select max(snap_id) from stats$snapshot;
MAX(SNAP_ID)
SQL> delete from stats$snapshot where snap_id < =
你可以更改snap_id的范圍以保留你需要的數據
在以上刪除過程中
SQL> select a
from v$locked_object a
where a
/
OBJECT_ID ORACLE_USERNAME OBJECT_NAME
八
Statspack有兩種類型的收集選項
級別(level)
門限(threshold)
Statspack共有三種快照級別
a
b
c
可以通過statspack包修改缺省的級別設置
SQL>execute statspack
通過這樣的設置
如果你只是想本次改變收集級別
SQL>execute statspack
快照門限只應用於stats$sql_summary表中獲取的SQL語句
因為每一個快照都會收集很多數據
門限存儲在stats$statspack_parameter表中
a
b
c
d
任何一個門限值超過以上參數就會產生一條記錄
通過調用statspack
例如
SQL>execute statspack
一一
a
SQL> select * from v$version;
BANNER
Oracle
PL/SQL Release
CORE Version
TNS for HPUX: Version
NLSRTL Version
b
SQL> @statscbps
View created
Synonym created
Grant succeeded
SQL>
其他步驟都是相同的
From:http://tw.wingwit.com/Article/program/Oracle/201311/18787.html