如何捕捉process中引起performance issue的sql的execution plan

jacksan

我這裡有個Jasper Report機能，sql是通過Java提交到Oracle，
然後就發現有個Oracle進程佔用100%的cpu，大概會持續1分
多鐘，才能返回結果，有時候會需要更長時間，導致apache
timeout。

本來正常情況下，只需要幾秒鐘的時間，可是從2個禮拜前，
就開始變得很慢。客戶也開始有抱怨發出來了，客戶的環境
是Redhat Linux 64bit+Oracle 64bit EE。

但是在我們開發環境下Redhat Linux 64bit+Oracle 64bit SE1，
無法再現。

我想通過分析現場的execution plan來判斷一下問題的根源。
如何捕捉這個問題sql的execution plan?

可以用ALTER SYSTEM SET events '10046 trace name context forever, level 8'嗎？
（這樣的做的話，會有很多trace產生）

有沒有其他好的方法？


補充：
即使發現客戶現場的execution plan不是很好，也不能把開發
環境的execution plan應用到現場吧？

jacksan

以前做過AWR，但是SQL Statistics部份沒有數據，估計我這裡的Oracle是SE1的原因。
是不是需要安裝statpack？？

SQL Statistics

    SQL ordered by Elapsed Time
    SQL ordered by CPU Time
    SQL ordered by User I/O Wait Time
    SQL ordered by Gets
    SQL ordered by Reads
    SQL ordered by Physical Reads (UnOptimized)
    SQL ordered by Executions
    SQL ordered by Parse Calls
    SQL ordered by Sharable Memory
    SQL ordered by Version Count
    Complete List of SQL Text

Back to Top

SQL ordered by Elapsed Time

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by User I/O Wait Time

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Gets

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Reads

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Physical Reads (UnOptimized)

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Executions

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

xin_chy

建议 ：
    1、做oracle AWR 报表，根据awr报表中的SQL Statistics 部分找到性能差的SQL ；
    2、使用select plan_table_output from table (dbms_xplan.display_awr(‘&sql_id’,null,null,‘ADVANCED +PEEKED_BINDS’));查看执行计划；