issue des: loading time to shoot up from 45 mins to 1 hr

Step1:

DBA collecte statspack on ES

Step2:

Superseded by Automatic Workload Repository (AWR) in Oracle 10g.

Step3:

Use for taking "snapshots" of performance data
Generate reports for analysis
Statspack reports are read top down

Step4:

Identify high-load SQL statements
Useful for performance analysis and SQL tuning

Step5: 怎么分析指标？

Statspack Report- environment section

Stats Report - Load Profile

Step6:

hard parsing VS soft Parsing

硬解析资源包括CPU，LIBRARY CACHE ,共享池

　　　　

硬解析超过100 per second，说明系统存在高硬解析的情况。高硬解析将会导致严重的性能问题。一般来说，高解析率伴随着共享池的锁存和library cache的锁存。

　　

软解析超过300每秒，意味着高软解析。不必要的软解析也会限制应用程序的可测性。最优的状态是，一个session中sql语句软解析一次，执行多次。

　　

参考：《Database Performance Tuning Guide and Reference》、《Oracle 9i DBA 101》

Step7:

How do we minimize hard parses?
Ensure SQL is sharable (私有SQL区和共享SQL区)

In other words, avoid string literals
SELECT * FROM ES_User WHERE last_name LIKE 'jupca%';
Use bind variables
SELECT * FROM ES_User WHERE last_name LIKE :1;

Step8:

oracle Statspack 报告解析之 Instance Efficiency Percentages (实例命中率)

该部分可以提前找出ORACLE潜在将要发生的性能问题，很重要

参考地址:http://blog.csdn.net/tianlesoftware/article/details/4682329

Step9:

Pay attention to parse-related statistics
Library Hit, Soft Parse
How often SQL statements and PL/SQL codes were found in shared pool, i.e., library cache
Low values could mean shared pool too small or SQL not using bind variables
Non-Parse CPU
How much CPU resources were spent on actual SQL execution instead of parsing SQL
High value means CPU was used mainly for execution not parsing
Parse CPU to Parse Elapsd
How is CPU time spent on parsing SQL statements
Low value could mean database was waiting for some resources when parsing SQL

Low % SQL with executions>1 indicates many SQL statements were executed only once
E.g.: SQL using string literals instead of bind variables
Could cause performance problem

Step10:

Top 5 Timed Events

Report shows a high CPU time
CPU time was 1.711 hrs over a 2 hrs snapshot!
System is CPU-bound
Matches what customer is experiencing!
Could also indicate excessive logical I/O against data buffers (buffer gets)
Need to drill down to detail sections of report to identify problematic SQL statements

Step 11:

Organized by different "views" (buffer gets, physical reads, executions, parse calls, sharable memory)
Shows "top" SQL statements under each view

issue1:

Buffer Gets counts logical I/O requests
Large value could be caused by suboptimal execution plan

issue2:

High value of physical reads could be caused by full table scans or database updates

issue3:

Which SQLs get executed the most
Not necessarily problematic unless suboptimal execution plan

issue4:

SQLs that had to be re-executed (and reparsed) the most
SQLs also likely to have high executions

issue5:

SQLs taking up the most sharable memory