一、awr使用
从8版本开始oracle就提供了强大的性能分析工具,可以通过statspack报表观察和分析。
statspack使用起来不是太方便,需要自己安装和规划安装statspack的用户和表空间,还需要直接设置历史信息清理策略,否则表空间会无限扩大。
从10g开始提供了awr代替statspack,进行了很大的改进。
awr自动工作负载信息库。
Automatic Workload Repository
安装oracle时候自动安装,无需另外安装。
已经考虑了统计信息收集的清理问题,在默认情况下报了一周的统计信息。
可以和statspack共存。
默认收集7天的采集信息。
SQL> select * from dba_hist_wr_control ;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------------------------- --------------------------------------- ----------
1991219404 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------------------------- --------------------------------------- ----------
1991219404 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
可以通过 dbms_workload_repository工具包实现。
将间隔时间设置为30分钟,保留时间设置为7天。
SQL> begin
2 dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>10*24*60);
3 end ;
4 /
2 dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>10*24*60);
3 end ;
4 /
此时可以查看一下awr设置信息
SQL> select * from dba_hist_wr_control ;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------------------------- --------------------------------------- ----------
1991219404 +00000 00:30:00.0 +00010 00:00:00.0 DEFAULT
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- --------------------------------------- --------------------------------------- ----------
1991219404 +00000 00:30:00.0 +00010 00:00:00.0 DEFAULT
报表使用:
使用脚本生成
SQL> @/usr/oracle/product/10.2.0/rdbms/admin/awrrpt.sql
生成文件的位置为:
可以通过find命令查找一下具体位置
[root@xxtdwhtest ~]# find /usr/oracle/ -name awrrpt_1_630_640.html
/usr/oracle/product/10.2.0/network/admin/awrrpt_1_630_640.html
可以把里面的内容下载下来,linux下载命令sz 需要下载的文件名。
[root@xxtdwhtest admin]# ls
awrrpt_1_630_640.html awrrpt_2_631_640.txt samples sqlnet.log
awrrpt_1_631_639.html listener.ora shrept.lst tnsnames.ora
[root@xxtdwhtest admin]# sz awrrpt_*
rz
正在开始 zmodem 传输。 按 Ctrl+C 取消。
正在传输 awrrpt_1_630_640.html...
100% 237 KB 39 KB/s 00:00:06 0 错误
正在传输 awrrpt_1_631_639.html...
100% 236 KB 236 KB/s 00:00:01 0 错误
正在传输 awrrpt_2_631_640.txt...
100% 101 KB 101 KB/s 00:00:01 0 错误
awrrpt_1_630_640.html awrrpt_2_631_640.txt samples sqlnet.log
awrrpt_1_631_639.html listener.ora shrept.lst tnsnames.ora
[root@xxtdwhtest admin]# sz awrrpt_*
rz
正在开始 zmodem 传输。 按 Ctrl+C 取消。
正在传输 awrrpt_1_630_640.html...
100% 237 KB 39 KB/s 00:00:06 0 错误
正在传输 awrrpt_1_631_639.html...
100% 236 KB 236 KB/s 00:00:01 0 错误
正在传输 awrrpt_2_631_640.txt...
100% 101 KB 101 KB/s 00:00:01 0 错误
如何分析awr报表。
Load Profile
Per Second | Per Transaction | |
---|---|---|
Redo size: | 1,122.88 | 77,285.67 |
Logical reads: | 48.05 | 3,306.90 |
Block changes: | 6.44 | 443.18 |
Physical reads: | 7.55 | 519.99 |
Physical writes: | 0.32 | 22.37 |
User calls: | 0.41 | 28.37 |
Parses: | 0.58 | 39.71 |
Hard parses: | 0.04 | 2.44 |
Sorts: | 0.43 | 29.41 |
Logons: | 0.01 | 0.44 |
Executes: | 1.57 | 108.13 |
Transactions: | 0.01 |
可以看Transactions来分析系统的压力负责。
Top 5 Timed Events
Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
---|---|---|---|---|---|
db file sequential read | 115,819 | 204 | 2 | 27.6 | User I/O |
CPU time | 117 | 15.9 | |||
db file scattered read | 10,092 | 60 | 6 | 8.1 | User I/O |
latch: shared pool | 252 | 12 | 48 | 1.6 | Concurrency |
control file parallel write | 10,792 | 12 | 1 | 1.6 | System I/O |
看等待时间。db file sequential read于sql执行有关,
如果日志写log_file switch等待时间产生的原因,一般是当日志组写完后,lgwr试图写下一个log file ,如果这时候数据库没有完成记录在第一个logfile中的dirty块,就会出现这个事件,标识dbwr写速度太慢,解决方法就是增加额外的dbwr或者增加日志组和日志文件大小。
具体每个日志的平均读写的大小量计算公式:
根据
Instance Activity Stats
Statistic | Total | per Second | per Trans |
---|---|---|---|
CPU used by this session | 7,109 | 0.22 | 15.13 |
CPU used when call started | 1,098 | 0.03 | 2.34 |
CR blocks created | 1,388 | 0.04 | 2.95 |
Cached Commit SCN referenced | 35,194 | 1.09 | 74.88 |
Commit SCN cached | 38 | 0.00 | 0.08 |
DB time | 3,334,772 | 103.09 | 7,095.26 |
DBWR checkpoint buffers written | 5,492 | 0.17 | 11.69 |
DBWR checkpoints | 0 | 0.00 | 0.00 |
DBWR object drop buffers written | 0 | 0.00 | 0.00 |
DBWR revisited being-written buffer | 0 | 0.00 | 0.00 |
DBWR thread checkpoint buffers written | 0 | 0.00 | 0.00 |
DBWR transaction table writes | 1,137 | 0.04 | 2.42 |
DBWR undo block writes | 3,555 | 0.11 | 7.56 |
IMU CR rollbacks | 14 | 0.00 | 0.03 |
IMU Flushes | 6,595 | 0.20 | 14.03 |
IMU Redo allocation size | 634,932 | 19.63 | 1,350.92 |
IMU commits | 325 | 0.01 | 0.69 |
IMU contention | 3 | 0.00 | 0.01 |
IMU ktichg flush | 23 | 0.00 | 0.05 |
IMU pool not allocated | 0 | 0.00 | 0.00 |
IMU recursive-transaction flush | 2 | 0.00 | 0.00 |
IMU undo allocation size | 2,963,816 | 91.62 | 6,305.99 |
IMU- failed to get a private strand | 0 | 0.00 | 0.00 |
Misses for writing mapping | 1 | 0.00 | 0.00 |
SMON posted for undo segment recovery | 0 | 0.00 | 0.00 |
SMON posted for undo segment shrink | 1 | 0.00 | 0.00 |
SQL*Net roundtrips to/from client | 11,305 | 0.35 | 24.05 |
SQL*Net roundtrips to/from dblink | 0 | 0.00 | 0.00 |
active txn count during cleanout | 514 | 0.02 | 1.09 |
application wait time | 0 | 0.00 | 0.00 |
background checkpoints completed | 0 | 0.00 | 0.00 |
background checkpoints started | 0 | 0.00 | 0.00 |
background timeouts | 103,524 | 3.20 | 220.26 |
branch node splits | 0 | 0.00 | 0.00 |
buffer is not pinned count | 681,226 | 21.06 | 1,449.42 |
buffer is pinned count | 814,014 | 25.16 | 1,731.94 |
bytes received via SQL*Net from client | 1,857,036 | 57.41 | 3,951.14 |
bytes received via SQL*Net from dblink | 0 | 0.00 | 0.00 |
bytes sent via SQL*Net to client | 2,571,367 | 79.49 | 5,470.99 |
bytes sent via SQL*Net to dblink | 0 | 0.00 | 0.00 |
calls to get snapshot scn: kcmgss | 193,488 | 5.98 | 411.68 |
calls to kcmgas | 114,625 | 3.54 | 243.88 |
calls to kcmgcs | 882 | 0.03 | 1.88 |
change write time | 1,415 | 0.04 | 3.01 |
cleanout - number of ktugct calls | 1,052 | 0.03 | 2.24 |
cleanouts and rollbacks - consistent read gets | 7 | 0.00 | 0.01 |
cleanouts only - consistent read gets | 337 | 0.01 | 0.72 |
cluster key scan block gets | 138,309 | 4.28 | 294.27 |
cluster key scans | 47,091 | 1.46 | 100.19 |
commit batch performed | 5 | 0.00 | 0.01 |
commit batch requested | 5 | 0.00 | 0.01 |
commit batch/immediate performed | 42 | 0.00 | 0.09 |
commit batch/immediate requested | 42 | 0.00 | 0.09 |
commit cleanout failures: block lost | 81 | 0.00 | 0.17 |
commit cleanout failures: buffer being written | 2 | 0.00 | 0.00 |
commit cleanout failures: callback failure | 118 | 0.00 | 0.25 |
commit cleanout failures: cannot pin | 0 | 0.00 | 0.00 |
commit cleanouts | 9,208 | 0.28 | 19.59 |
commit cleanouts successfully completed | 9,007 | 0.28 | 19.16 |
commit immediate performed | 37 | 0.00 | 0.08 |
commit immediate requested | 37 | 0.00 | 0.08 |
commit txn count during cleanout | 868 | 0.03 | 1.85 |
concurrency wait time | 1,740 | 0.05 | 3.70 |
consistent changes | 4,256 | 0.13 | 9.06 |
consistent gets | 1,331,333 | 41.16 | 2,832.62 |
consistent gets - examination | 501,375 | 15.50 | 1,066.76 |
consistent gets direct | 143 | 0.00 | 0.30 |
consistent gets from cache | 1,331,190 | 41.15 | 2,832.32 |
current blocks converted for CR | 0 | 0.00 | 0.00 |
cursor authentications | 138 | 0.00 | 0.29 |
data blocks consistent reads - undo records applied | 1,488 | 0.05 | 3.17 |
db block changes | 208,293 | 6.44 | 443.18 |
db block gets | 222,910 | 6.89 | 474.28 |
db block gets direct | 1,143 | 0.04 | 2.43 |
db block gets from cache | 221,767 | 6.86 | 471.84 |
deferred (CURRENT) block cleanout applications | 4,939 | 0.15 | 10.51 |
dirty buffers inspected | 2,684 | 0.08 | 5.71 |
enqueue conversions | 6,533 | 0.20 | 13.90 |
enqueue releases | 322,528 | 9.97 | 686.23 |
enqueue requests | 322,528 | 9.97 | 686.23 |
enqueue timeouts | 0 | 0.00 | 0.00 |
enqueue waits | 1 | 0.00 | 0.00 |
exchange deadlocks | 0 | 0.00 | 0.00 |
execute count | 50,821 | 1.57 | 108.13 |
failed probes on index block reclamation | 0 | 0.00 | 0.00 |
free buffer inspected | 210,389 | 6.50 | 447.64 |
free buffer requested | 248,386 | 7.68 | 528.48 |
heap block compress | 383 | 0.01 | 0.81 |
hot buffers moved to head of LRU | 13,904 | 0.43 | 29.58 |
immediate (CR) block cleanout applications | 344 | 0.01 | 0.73 |
immediate (CURRENT) block cleanout applications | 2,885 | 0.09 | 6.14 |
index crx upgrade (found) | 0 | 0.00 | 0.00 |
index crx upgrade (positioned) | 22,843 | 0.71 | 48.60 |
index fast full scans (full) | 708 | 0.02 | 1.51 |
index fetch by key | 302,293 | 9.34 | 643.18 |
index scans kdiixs1 | 85,109 | 2.63 | 181.08 |
leaf node 90-10 splits | 128 | 0.00 | 0.27 |
leaf node splits | 288 | 0.01 | 0.61 |
lob reads | 1,880 | 0.06 | 4.00 |
lob writes | 7,809 | 0.24 | 16.61 |
lob writes unaligned | 7,809 | 0.24 | 16.61 |
logons cumulative | 205 | 0.01 | 0.44 |
messages received | 6,191 | 0.19 | 13.17 |
messages sent | 6,191 | 0.19 | 13.17 |
no buffer to keep pinned count | 0 | 0.00 | 0.00 |
no work - consistent read gets | 725,519 | 22.43 | 1,543.66 |
opened cursors cumulative | 31,189 | 0.96 | 66.36 |
parse count (failures) | 3 | 0.00 | 0.01 |
parse count (hard) | 1,148 | 0.04 | 2.44 |
parse count (total) | 18,664 | 0.58 | 39.71 |
parse time cpu | 334 | 0.01 | 0.71 |
parse time elapsed | 3,807 | 0.12 | 8.10 |
physical read IO requests | 125,864 | 3.89 | 267.80 |
physical read bytes | 2,002,092,032 | 61,890.12 | 4,259,770.28 |
physical read total IO requests | 143,144 | 4.42 | 304.56 |
physical read total bytes | 2,283,642,880 | 70,593.63 | 4,858,814.64 |
physical read total multi block requests | 10,092 | 0.31 | 21.47 |
physical reads | 244,396 | 7.55 | 519.99 |
physical reads cache | 244,253 | 7.55 | 519.69 |
physical reads cache prefetch | 118,532 | 3.66 | 252.20 |
physical reads direct | 143 | 0.00 | 0.30 |
physical reads direct (lob) | 140 | 0.00 | 0.30 |
physical reads direct temporary tablespace | 0 | 0.00 | 0.00 |
physical reads prefetch warmup | 0 | 0.00 | 0.00 |
physical write IO requests | 6,456 | 0.20 | 13.74 |
physical write bytes | 86,122,496 | 2,662.28 | 183,239.35 |
physical write total IO requests | 40,564 | 1.25 | 86.31 |
physical write total bytes | 665,353,728 | 20,567.90 | 1,415,646.23 |
physical write total multi block requests | 2,749 | 0.08 | 5.85 |
physical writes | 10,513 | 0.32 | 22.37 |
physical writes direct | 1,143 | 0.04 | 2.43 |
physical writes direct (lob) | 5 | 0.00 | 0.01 |
physical writes direct temporary tablespace | 1,138 | 0.04 | 2.42 |
physical writes from cache | 9,370 | 0.29 | 19.94 |
physical writes non checkpoint | 5,972 | 0.18 | 12.71 |
pinned buffers inspected | 8 | 0.00 | 0.02 |
prefetch warmup blocks aged out before use | 0 | 0.00 | 0.00 |
prefetched blocks aged out before use | 15,887 | 0.49 | 33.80 |
process last non-idle time | 32,318 | 1.00 | 68.76 |
recursive aborts on index block reclamation | 0 | 0.00 | 0.00 |
recursive calls | 533,381 | 16.49 | 1,134.85 |
recursive cpu usage | 6,256 | 0.19 | 13.31 |
redo blocks written | 74,879 | 2.31 | 159.32 |
redo buffer allocation retries | 0 | 0.00 | 0.00 |
redo entries | 107,520 | 3.32 | 228.77 |
redo log space requests | 0 | 0.00 | 0.00 |
redo log space wait time | 0 | 0.00 | 0.00 |
redo ordering marks | 2,118 | 0.07 | 4.51 |
redo size | 36,324,264 | 1,122.88 | 77,285.67 |
redo subscn max counts | 4,005 | 0.12 | 8.52 |
redo synch time | 30 | 0.00 | 0.06 |
redo synch writes | 50 | 0.00 | 0.11 |
redo wastage | 472,740 | 14.61 | 1,005.83 |
redo write time | 726 | 0.02 | 1.54 |
redo writer latching time | 0 | 0.00 | 0.00 |
redo writes | 1,737 | 0.05 | 3.70 |
rollback changes - undo records applied | 63 | 0.00 | 0.13 |
rollbacks only - consistent read gets | 1,381 | 0.04 | 2.94 |
rows fetched via callback | 112,489 | 3.48 | 239.34 |
session connect time | 83,331 | 2.58 | 177.30 |
session cursor cache hits | 39,544 | 1.22 | 84.14 |
session logical reads | 1,554,243 | 48.05 | 3,306.90 |
session pga memory | 30,538,792 | 944.04 | 64,976.15 |
session pga memory max | 57,539,624 | 1,778.71 | 122,424.73 |
session uga memory | 90,199,764,200 | 2,788,320.57 | 191,914,391.91 |
session uga memory max | 152,440,872 | 4,712.36 | 324,342.28 |
shared hash latch upgrades - no wait | 23,450 | 0.72 | 49.89 |
shared hash latch upgrades - wait | 0 | 0.00 | 0.00 |
sorts (disk) | 0 | 0.00 | 0.00 |
sorts (memory) | 13,821 | 0.43 | 29.41 |
sorts (rows) | 8,629,066 | 266.75 | 18,359.71 |
sql area evicted | 608 | 0.02 | 1.29 |
sql area purged | 39 | 0.00 | 0.08 |
summed dirty queue length | 2,777 | 0.09 | 5.91 |
switch current to new buffer | 166 | 0.01 | 0.35 |
table fetch by rowid | 270,315 | 8.36 | 575.14 |
table fetch continued row | 208 | 0.01 | 0.44 |
table scan blocks gotten | 286,941 | 8.87 | 610.51 |
table scan rows gotten | 10,571,952 | 326.81 | 22,493.51 |
table scans (long tables) | 19 | 0.00 | 0.04 |
table scans (short tables) | 7,564 | 0.23 | 16.09 |
total number of times SMON posted | 28 | 0.00 | 0.06 |
transaction rollbacks | 42 | 0.00 | 0.09 |
undo change vector size | 13,595,072 | 420.26 | 28,925.69 |
user I/O wait time | 26,456 | 0.82 | 56.29 |
user calls | 13,335 | 0.41 | 28.37 |
user commits | 459 | 0.01 | 0.98 |
user rollbacks | 11 | 0.00 | 0.02 |
workarea executions - multipass | 0 | 0.00 | 0.00 |
workarea executions - onepass | 0 | 0.00 | 0.00 |
workarea executions - optimal | 10,022 | 0.31 | 21.32 |
write clones created in background | 0 | 0.00 | 0.00 |
write clones created in foreground | 6 | 0.00 | 0.01 |
中的
average redo write size=(redo blocks written/redo writes)*512 bytes
=(74,879/1,737)*512 bytes=22K
这个值非常小了,可以是每次写的数据量只有22k,远远小于log_buffer的大小。
可以查看一下log_buffer的大小SQL> show parameter log_buffer;
NAME_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
log_buffer
integer
6108160
------------------------------------------------------------------------------
TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
------------------------------------------------------------------------------
log_buffer
integer
6108160
大小为6M
topsql优化
整体优化思路:
1、将redo放到写的快的磁盘上而非raid5.
2、插入语句带序列,增加序列的cache。
3、增大redo file的大小,增加log_buffer的大小。
4、批量提交sql。
优化后的标准是cpu time出现在top 5的第一位。
rac的 datablock gc 状态busy,rac争用一般dml引起争用,查询一般是不会争用的,可以查出来开销比较大的dml语句。将这种查询定位到一个实例去处理,争用大大减少。
在rac设计的时候避免不同节点大规模频繁修改同一数据块,这样可以避免大量的cache fusion于全局等待事件的发生。可以用不同业务单独运行在不同的rac节点的方式来优化rac环境。
二、sql_trace/10046事件。
10046事件是sql_trace的增强版
trace的时间包括三种开启和关闭的方法。
1、当前session的
开启alter session set sql_trace=true;
关闭alter session set sql_trace=false;
10046事件的关闭和开启
开启SQL> alter session set events '10046 trace name context forever,level 12';
关闭SQL> alter session set events '10046 trace name context off';
其中level代表了10046的级别
一共有4个级别:
1- 启动标准的sql_trace功能,相当于sql_trace
4-level 1 加上绑定变量。
8-level1+等待事件跟踪。
12 -level1 +level 4+level 8
2、全局session
尽量避免使用,对系统的性能影响非常严重。
开启
SQL> alter system set sql_Trace=true;
System altered.
关闭
SQL> alter system set sql_Trace=false;
System altered.
3、指定用户的session
对于郑州登录的用户进行设置,利用dbms_system包实现。
si,se,nm是v$session总的sid,serial#和username。
le代表trace的级别,le=0标识跟踪结束,ev表示跟踪时间的类型,ev=10046表示跟踪10046事件。
SQL> select sid,serial#,username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
139 9
141 14
143 24 SYS
144 8
145 7
147 10
149 15
150 165
152 1
155 3
156 3
158 44 SYS
159 3 SYS
160 1
161 1
162 1
163 1
164 1
165 1
166 1
SID SERIAL# USERNAME
---------- ---------- ------------------------------
167 1
168 1
169 1
170 1
SID SERIAL# USERNAME
---------- ---------- ------------------------------
139 9
141 14
143 24 SYS
144 8
145 7
147 10
149 15
150 165
152 1
155 3
156 3
158 44 SYS
159 3 SYS
160 1
161 1
162 1
163 1
164 1
165 1
166 1
SID SERIAL# USERNAME
---------- ---------- ------------------------------
167 1
168 1
169 1
170 1
开启158 sys用户的10046事件:
SQL> exec dbms_system.set_ev(158,44,10046,12,'SYS');
PL/SQL procedure successfully completed.
关闭:
SQL> exec dbms_system.set_ev(158,44,10046,0,'SYS');
PL/SQL procedure successfully completed.
对trace文件进行格式化,便于分析。
tkprof tracefile outputfile [option]
一般trace文件放置在oracle安装目录下的admin/db_name/udump下面
如:
tkprof tracefile outputfile sys=no sort=prsela,exeela,fchela
其中prsela表示对一个游标解析消耗的时间。
exeela表示针对游标执行消耗的时间。
fchela游标获取数据行花费的时间。
sys表示是否接卸sys对数据字典的递归查询语句,只有在分析细节的时候开考了开启,多数情况下关闭。
三、计时和剖析工具
在sqlplus中对执行时间查看可以用set timing on和set timing off来设置。
SQL> select count(*) from all_objects;
COUNT(*)
----------
50500
----------
50500
SQL> set timing on
SQL> select count(*) from all_objects;
SQL> select count(*) from all_objects;
COUNT(*)
----------
50500
----------
50500
Elapsed: 00:00:00.60
SQL> set timing off
SQL> select count(*) from all_objects;
SQL> set timing off
SQL> select count(*) from all_objects;
COUNT(*)
----------
50500
----------
50500
缩写形式为set timi on 和set timi off
SQL> set timi off
SQL> select count(*) from all_objects;
SQL> select count(*) from all_objects;
COUNT(*)
----------
50500
----------
50500
SQL> set timi on
SQL> select count(*) from all_objects;
SQL> select count(*) from all_objects;
COUNT(*)
----------
50500
----------
50500
Elapsed: 00:00:00.47
如果要分析性能,仅仅开执行时间是远远不够的,系统包函数
dbms_utility.get_time 可以返回执行时候的时间整数,两个时间点想减得出代码块执行的时间,单位是毫秒10ms
。
函数没有参数,所有后面的括号可以省略。
oracle到10g时候还提供了dbms_utility.get_cpu_time函数。这个函数和前面的dbms_utility.get_time的区别是,get_time=get_cpu_time +其他非cpu时间,比如等待时间。
对于pl/sql的性能 oracle从8i开始提供了dbms_profiler包,叫剖析器。
配置环境。
1、用sysdba角色执行
SQL> @/usr/oracle/product/10.2.0/rdbms/admin/profload.sql
2、用pl/sql剖析执行者执行
SQL> @/usr/oracle/product/10.2.0/rdbms/admin/proftab.sql
。
配置完成。
涉及到的表
SQL> desc plsql_profiler_runs
Name Type Nullable Default Comments
--------------- -------------- -------- ------- --------
RUNID NUMBER 主键,从序列plsql_profiler_runnumber生成的唯一运行标识
RELATED_RUN NUMBER Y 相关运行的runid
RUN_OWNER VARCHAR2(32) Y 启动运行的用户
RUN_DATE DATE Y 运行开始时间
RUN_COMMENT VARCHAR2(2047) Y 运行的注释
RUN_TOTAL_TIME NUMBER Y 运行的时间,一纳秒为单位
RUN_SYSTEM_INFO VARCHAR2(2047) Y 尚未使用
RUN_COMMENT1 VARCHAR2(2047) Y 额外的注释
SPARE1 VARCHAR2(256) Y 未使用
Name Type Nullable Default Comments
--------------- -------------- -------- ------- --------
RUNID NUMBER 主键,从序列plsql_profiler_runnumber生成的唯一运行标识
RELATED_RUN NUMBER Y 相关运行的runid
RUN_OWNER VARCHAR2(32) Y 启动运行的用户
RUN_DATE DATE Y 运行开始时间
RUN_COMMENT VARCHAR2(2047) Y 运行的注释
RUN_TOTAL_TIME NUMBER Y 运行的时间,一纳秒为单位
RUN_SYSTEM_INFO VARCHAR2(2047) Y 尚未使用
RUN_COMMENT1 VARCHAR2(2047) Y 额外的注释
SPARE1 VARCHAR2(256) Y 未使用
SQL> desc plsql_profiler_units;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- --------
RUNID NUMBER 主键,参照plsql_profiler_runs
UNIT_NUMBER NUMBER 主键一部分,内部生成的库单元号
UNIT_TYPE VARCHAR2(32) Y 库单元类型
UNIT_OWNER VARCHAR2(32) Y 库单元所有者名字
UNIT_NAME VARCHAR2(32) Y 库单元名字时间戳
UNIT_TIMESTAMP DATE Y 用来检测单元多次运行的变化
TOTAL_TIME NUMBER 0 一纳秒为单位的这个单元的花费时间,概要工具不设置这个参数,为分析工具提供方便
SPARE1 NUMBER Y 未使用
SPARE2 NUMBER Y 未使用
Name Type Nullable Default Comments
-------------- ------------ -------- ------- --------
RUNID NUMBER 主键,参照plsql_profiler_runs
UNIT_NUMBER NUMBER 主键一部分,内部生成的库单元号
UNIT_TYPE VARCHAR2(32) Y 库单元类型
UNIT_OWNER VARCHAR2(32) Y 库单元所有者名字
UNIT_NAME VARCHAR2(32) Y 库单元名字时间戳
UNIT_TIMESTAMP DATE Y 用来检测单元多次运行的变化
TOTAL_TIME NUMBER 0 一纳秒为单位的这个单元的花费时间,概要工具不设置这个参数,为分析工具提供方便
SPARE1 NUMBER Y 未使用
SPARE2 NUMBER Y 未使用
SQL> desc plsql_profiler_data
Name Type Nullable Default Comments
----------- ------ -------- ------- --------
RUNID NUMBER 主键,参照plsql_profiler_runs
UNIT_NUMBER NUMBER 主键一部分,内部生成的库单元号
LINE# NUMBER 主键一部分,非空,单元内部源代码的行号
TOTAL_OCCUR NUMBER Y line#所在的行执行的总次数
TOTAL_TIME NUMBER Y 纳秒为单位的line#所在行执行的总时间
MIN_TIME NUMBER Y 纳秒为单位的line#所在行执最小执行时间
MAX_TIME NUMBER Y 纳秒为单位的line#所在行执行最大执行时间
SPARE1 NUMBER Y 以下都未使用
SPARE2 NUMBER Y
SPARE3 NUMBER Y
SPARE4 NUMBER Y
Name Type Nullable Default Comments
----------- ------ -------- ------- --------
RUNID NUMBER 主键,参照plsql_profiler_runs
UNIT_NUMBER NUMBER 主键一部分,内部生成的库单元号
LINE# NUMBER 主键一部分,非空,单元内部源代码的行号
TOTAL_OCCUR NUMBER Y line#所在的行执行的总次数
TOTAL_TIME NUMBER Y 纳秒为单位的line#所在行执行的总时间
MIN_TIME NUMBER Y 纳秒为单位的line#所在行执最小执行时间
MAX_TIME NUMBER Y 纳秒为单位的line#所在行执行最大执行时间
SPARE1 NUMBER Y 以下都未使用
SPARE2 NUMBER Y
SPARE3 NUMBER Y
SPARE4 NUMBER Y
可以看出用plsql_profiler_units表示用来运行某次的单元数据,而用plsql_profiler_data记录详细的语句级别的累积信息。
也可以单独用一个用户将这三个表放进去。然后授予必要访问权限给予其他需要执行剖析的用户。这样好处是不用在需要执行剖析的用户schema中产生额外的表。
SQL> @/usr/oracle/product/10.2.0/rdbms/admin/profload.sql
SQL> create user profiler identified by profiler default tablespace users quota unlimited on users;
User created.
Elapsed: 00:00:00.28
SQL> grant connect to profiler;
Grant succeeded.
Elapsed: 00:00:00.02
SQL> create public synonym plsql_profiler_units for profiler.plsql_profiler_units;
SQL> create public synonym plsql_profiler_data for profiler.plsql_profiler_data;
SQL> create public synonym plsql_profiler_runs for profiler.plsql_profiler_runs;
SQL> create public synonym plsql_profiler_runnumber for profiler.plsql_profiler_runnumber;
SQL> GRANT CREATE TABLE TO PROFILER;
SQL> GRANT RESOURCE TO PROFILER;
SQL> @/usr/oracle/product/10.2.0/rdbms/admin/proftab.sql
SQL>GRANT SELECT ON PLSQL_PROFILER_RUNNUMBER TO PUBLIC ;
SQL>GRANT SELECT,INSERT ,UPDATE,DELETE ON PLSQL_PROFILER_DATA TO PUBLIC ;
SQL>GRANT SELECT,INSERT ,UPDATE,DELETE ON PLSQL_PROFILER_UNITS TO PUBLIC ;
SQL>GRANT SELECT,INSERT ,UPDATE,DELETE ON PLSQL_PROFILER_RUNS TO PUBLIC ;
已经设置完毕
用法
dbms_profiler.start_profiler('用户自定义的注释');
......
需要剖析的代码块
......
dbms_profiler.stop_profiler;--结束一次运行的信息记录。
过程代码:
CREATE OR REPLACE PROCEDURE a_proc_test1(i_u IN INTEGER) IS
v_flag BOOLEAN;
BEGIN
FOR i IN 2 .. i_u
LOOP
v_flag := TRUE;
FOR j IN 2 .. trunc(power(i, 0.5))
LOOP
IF MOD(i, j) = 0
THEN
v_flag := FALSE;
EXIT;
END IF;
END LOOP;
IF v_flag = TRUE
THEN
NULL;
END IF;
END LOOP;
END a_proc_test1;
v_flag BOOLEAN;
BEGIN
FOR i IN 2 .. i_u
LOOP
v_flag := TRUE;
FOR j IN 2 .. trunc(power(i, 0.5))
LOOP
IF MOD(i, j) = 0
THEN
v_flag := FALSE;
EXIT;
END IF;
END LOOP;
IF v_flag = TRUE
THEN
NULL;
END IF;
END LOOP;
END a_proc_test1;
测试代码:
declare
-- Local variables here
i integer;
begin
-- Test statements here
i:=dbms_profiler.start_profiler(run_comment=>'test1:'||sysdate);
a_proc_test1(10000);
i:=dbms_profiler.stop_profiler;
end;
-- Local variables here
i integer;
begin
-- Test statements here
i:=dbms_profiler.start_profiler(run_comment=>'test1:'||sysdate);
a_proc_test1(10000);
i:=dbms_profiler.stop_profiler;
end;
查询语句:
select runid,run_date,run_comment,run_total_time from plsql_profiler_runs order by runid;
RUNID RUN_DATE RUN_COMMENT RUN_TOTAL_TIME
---------- ----------- -------------------------------------------------------------------------------- --------------
1 2011/3/26 1 test1:2011-03-26 14:20:03 1030591000
---------- ----------- -------------------------------------------------------------------------------- --------------
1 2011/3/26 1 test1:2011-03-26 14:20:03 1030591000
select u.runid,u.unit_number,u.unit_type,u.unit_owner,u.unit_name,d.line#,d.total_occur,d.total_time,d.min_time,d.max_time
from plsql_profiler_units u
join plsql_profiler_data d on u.runid=d.runid and u.unit_number=d.unit_number
where u.runid=1
order by u.unit_number,d.line#
from plsql_profiler_units u
join plsql_profiler_data d on u.runid=d.runid and u.unit_number=d.unit_number
where u.runid=1
order by u.unit_number,d.line#
可以下载prof.zip包可以生成更详细的过程分析报告。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25296295/viewspace-690325/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25296295/viewspace-690325/