oracle维护.doc

1、AWR报告 SQL@?/rdbms/admin/awrrpt.sql 说明：@是指要运行一个脚本的关键字 ?是指ORACLE_HOME 重要的几个点： Instance Efficiency Percentages (Target 100%) Buffer Nowait %: 99.99 Redo NoWait %: 100.00 Buffer Hit %: 99.25 In-memory Sort %: 100.00 Library Hit %: 98.87 Soft Parse %: 99.55 Execute to Parse %: 3.15 Latch Hit %: 99.98 Parse CPU to Parse Elapsd %: 0.01 % Non-Parse CPU: 93.84 红色的可以很低，因为我们写的sql没有用变量的方式，每次都要从新解析sql，导致效率很低 其他几项的值都应该在 99%---100%之间 Top 5 Timed Foreground Events Event Waits Time(s) Avg wait (ms) % DB time Wait Class gc cr multi block request 1,179,207 13,395 11 46.63 Cluster db file sequential read 490,628 5,343 11 18.60 User I/O DB CPU ? 3,056 ? 10.64 ? gc buffer busy acquire 22,032 2,311 105 8.04 Cluster read by other session 6,198 1,132 183 3.94 User I/O 占用CPU前5位的事件，要根据占用事件，分析原因，然后去解决 SQL Statistics SQL ordered by Elapsed Time SQL ordered by CPU Time SQL ordered by Gets SQL ordered by Reads SQL ordered by Executions SQL ordered by Parse Calls SQL ordered by Sharable Memory SQL ordered by Version Count SQL ordered by Cluster Wait Time Complete List of SQL Text Back to Top SQL ordered by Elapsed Time Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 Total DB Time (s): 28,724 Captured SQL account for 53.2% of Total Total DB Time (s): 28,724 Captured PL/SQL account for 0.2% of Total Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text 1,846 14 11 167.79 6.43 99apnauk2qz8f JDBC Thin Client insert into pm4h_db.qtm_perio... 1,254 5 9 139.38 4.37 12usgvww5wr11 JDBC Thin Client insert into pm4h_db.qtm_perio... 952 3 5 190.36 3.31 4vagc1zyrr61y JDBC Thin Client insert into pm4h_db.qtm_perio... 890 3 6 148.32 3.10 4xw90uddu8v28 JDBC Thin Client insert into pm4h_db.qtm_perio... 599 4 8 74.86 2.08 79hff738w98us JDBC Thin Clien