Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › SQL PROFILE固定执行计划

yorkshi

0 积分	1 好友	1 主题

发消息

[ORA-600/ORA-7445] SQL PROFILE固定执行计划

1^#

发表于 2013-9-23 13:19:57 | 查看: 6569| 回复: 14

ML给看一下我这个问题：

我这边是一套11.1.0.7的库，EBS应用，其中一个请求巨慢无比，从TRACE里面抓出了SQL并对SQL进行了优化，增加了hint，执行速度得到大大提升，但是由于该请求为EBS标准请求不能修改SQL，了解到通过固定执行计划可以使SQL走我们指定的执行计划，但先后查了下对于11g下固定执行计划资料比较少，ML是否能给予些许指导？下面为相关SQL

优化前该SQL需执行10分钟出结果，并且逻辑读物理读比较严重。

SQL> SELECT  nvl(count(*), 0)
  2 FROM gl_je_segment_values sv,
  3       gl_je_lines jel,
  4       gl_ledger_segment_values lsv
  5 WHERE jel.je_header_id IN ('7217', '7227', '7228', '7231', '7226', '7229', '7225', '4033', '4035', '4036')
  6    AND jel.je_line_num = 1
  7    AND sv.je_header_id = jel.je_header_id
  8    AND sv.segment_type_code = 'B'
  9    AND lsv.ledger_id = 2021
10    AND lsv.segment_type_code = 'B'
11    AND lsv.segment_value = sv.segment_value
12    AND jel.effective_date BETWEEN nvl(lsv.start_date, jel.effective_date - 1) AND nvl(lsv.end_date, jel.effective_date + 1);

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                            |    1 | 50 | 15 (0)|
| 1 |  SORT AGGREGATE                |                            |    1 | 50 |          |
| 2 | NESTED LOOPS                |                            |    |    |          |
| 3 | NESTED LOOPS                |                            |    1 | 50 | 15 (0)|
| 4 |    NESTED LOOPS             |                            |    1 | 35 | 13 (0)|
|*  5 |    TABLE ACCESS BY INDEX ROWID| GL_JE_LINES                |    1 | 20 | 11 (0)|
|*  6 |    INDEX RANGE SCAN       | GL_JE_LINES_IDX1          |    8 |    |    4 (0)|
|*  7 |    INDEX RANGE SCAN          | GL_JE_SEGMENT_VALUES_U1    |    1 | 15 |    2 (0)|
|*  8 |    INDEX RANGE SCAN          | GL_LEDGER_SEGMENT_VALUES_N1 |    1 |    |    1 (0)|
|*  9 | TABLE ACCESS BY INDEX ROWID  | GL_LEDGER_SEGMENT_VALUES |    1 | 15 |    2 (0)|
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - filter("JEL"."JE_HEADER_ID"=4033 OR "JEL"."JE_HEADER_ID"=4035 OR
            "JEL"."JE_HEADER_ID"=4036 OR "JEL"."JE_HEADER_ID"=7217 OR "JEL"."JE_HEADER_ID"=7225 OR
            "JEL"."JE_HEADER_ID"=7226 OR "JEL"."JE_HEADER_ID"=7227 OR "JEL"."JE_HEADER_ID"=7228 OR
            "JEL"."JE_HEADER_ID"=7229 OR "JEL"."JE_HEADER_ID"=7231)
6 - access("JEL"."JE_LINE_NUM"=1)
7 - access("SV"."JE_HEADER_ID"="JEL"."JE_HEADER_ID" AND "SV"."SEGMENT_TYPE_CODE"='B')
   filter("SV"."JE_HEADER_ID"=4033 OR "SV"."JE_HEADER_ID"=4035 OR
            "SV"."JE_HEADER_ID"=4036 OR "SV"."JE_HEADER_ID"=7217 OR "SV"."JE_HEADER_ID"=7225 OR
            "SV"."JE_HEADER_ID"=7226 OR "SV"."JE_HEADER_ID"=7227 OR "SV"."JE_HEADER_ID"=7228 OR
            "SV"."JE_HEADER_ID"=7229 OR "SV"."JE_HEADER_ID"=7231)
8 - access("LSV"."LEDGER_ID"=2021 AND "LSV"."SEGMENT_TYPE_CODE"='B' AND
            "LSV"."SEGMENT_VALUE"="SV"."SEGMENT_VALUE")
9 - filter("JEL"."EFFECTIVE_DATE">=NVL("LSV"."START_DATE",INTERNAL_FUNCTION("JEL"."EFFECT
            IVE_DATE")-1) AND "JEL"."EFFECTIVE_DATE"<=NVL("LSV"."END_DATE",INTERNAL_FUNCTION("JEL"."EFFE
            CTIVE_DATE")+1))

Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   525869  consistent gets
   55991  physical reads
      672  redo size
      529  bytes sent via SQL*Net to client
      520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

优化后，增加hint /*+ ORDERED */,基本1秒钟出结果：

SQL> SELECT /*+ ORDERED */ nvl(count(*), 0)
  2 FROM gl_je_segment_values sv,
  3       gl_je_lines jel,
  4       gl_ledger_segment_values lsv
  5 WHERE jel.je_header_id IN ('7217', '7227', '7228', '7231', '7226', '7229', '7225', '4033', '4035', '4036')
  6    AND jel.je_line_num = 1
  7    AND sv.je_header_id = jel.je_header_id
  8    AND sv.segment_type_code = 'B'
  9    AND lsv.ledger_id = 2021
10    AND lsv.segment_type_code = 'B'
11    AND lsv.segment_value = sv.segment_value
12    AND jel.effective_date BETWEEN nvl(lsv.start_date, jel.effective_date - 1) AND nvl(lsv.end_date, jel.effective_date + 1);

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                            |    1 | 50 | 25 (0)|
| 1 |  SORT AGGREGATE                |                            |    1 | 50 |          |
| 2 | NESTED LOOPS                |                            |    |    |          |
| 3 | NESTED LOOPS                |                            |    1 | 50 | 25 (0)|
| 4 |    NESTED LOOPS             |                            |    1 | 35 | 23 (0)|
| 5 |    INLIST ITERATOR          |                            |    |    |          |
|*  6 |    INDEX RANGE SCAN       | GL_JE_SEGMENT_VALUES_U1    | 10 | 150 | 12 (0)|
| 7 |    TABLE ACCESS BY INDEX ROWID| GL_JE_LINES                |    1 | 20 |    3 (0)|
|*  8 |    INDEX UNIQUE SCAN       | GL_JE_LINES_U1             |    1 |    |    2 (0)|
|*  9 |    INDEX RANGE SCAN          | GL_LEDGER_SEGMENT_VALUES_N1 |    1 |    |    1 (0)|
|* 10 | TABLE ACCESS BY INDEX ROWID  | GL_LEDGER_SEGMENT_VALUES |    1 | 15 |    2 (0)|
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access(("SV"."JE_HEADER_ID"=4033 OR "SV"."JE_HEADER_ID"=4035 OR
            "SV"."JE_HEADER_ID"=4036 OR "SV"."JE_HEADER_ID"=7217 OR "SV"."JE_HEADER_ID"=7225 OR
            "SV"."JE_HEADER_ID"=7226 OR "SV"."JE_HEADER_ID"=7227 OR "SV"."JE_HEADER_ID"=7228 OR
            "SV"."JE_HEADER_ID"=7229 OR "SV"."JE_HEADER_ID"=7231) AND "SV"."SEGMENT_TYPE_CODE"='B')
8 - access("SV"."JE_HEADER_ID"="JEL"."JE_HEADER_ID" AND "JEL"."JE_LINE_NUM"=1)
   filter("JEL"."JE_HEADER_ID"=4033 OR "JEL"."JE_HEADER_ID"=4035 OR
            "JEL"."JE_HEADER_ID"=4036 OR "JEL"."JE_HEADER_ID"=7217 OR "JEL"."JE_HEADER_ID"=7225 OR
            "JEL"."JE_HEADER_ID"=7226 OR "JEL"."JE_HEADER_ID"=7227 OR "JEL"."JE_HEADER_ID"=7228 OR
            "JEL"."JE_HEADER_ID"=7229 OR "JEL"."JE_HEADER_ID"=7231)
9 - access("LSV"."LEDGER_ID"=2021 AND "LSV"."SEGMENT_TYPE_CODE"='B' AND
            "LSV"."SEGMENT_VALUE"="SV"."SEGMENT_VALUE")
  10 - filter("JEL"."EFFECTIVE_DATE">=NVL("LSV"."START_DATE",INTERNAL_FUNCTION("JEL"."EFFECT
            IVE_DATE")-1) AND "JEL"."EFFECTIVE_DATE"<=NVL("LSV"."END_DATE",INTERNAL_FUNCTION("JEL"."EFFE
            CTIVE_DATE")+1))

Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
      70  consistent gets
      13  physical reads
      0  redo size
      529  bytes sent via SQL*Net to client
      520  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

但是由于不能改SQL，需要固定执行计划，ML你看看是否可以指导下11g下如何固定该执行计划，谢谢!

分享0

收藏0 回复只看该作者道具举报

Liu Maclean(刘相兵

2^#

发表于 2013-9-23 13:41:23

1、你的统计信息肯定有问题

2、
set linesize 200 pagesize 1400
@?/rdbms/admin/sqltrpt

输入sqlid 并给出输出

回复只看该作者道具举报

yorkshi

3^#

发表于 2013-9-23 17:03:03

SQL> @?/rdbms/admin/sqltrpt

15 Most expensive SQL in the cursor cache
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID          ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
fnr5818a3n66z  19,102.61
SELECT PID INTO :opid FROM V$PROCESS P, V$SESSION S

gyxv37z6aqyqp  16,152.98
SELECT P.PID, P.SPID, AUDSID, PROCESS, SUBSTR(USERENV('

5t39uchjqpyfm  13,717.23
BEGIN xla_accounting_pkg.unit_processor_batch(:errbuf,:

adw0hu9p9wyz1 9,956.45
BEGIN :1 := XLA_00707_AAD_C_000024_PKG.CreateJournalEnt

2vq15mt5gn0uz 7,329.46
BEGIN FND_CP_OPP_IPC.Subscribe_to_group(:1,:2); END;

53y9d2gsqsw0x 7,118.89
SELECT MIN(CREATION_DATE) FROM PO_DISTRIBUTIONS_ALL WHE

7b5w34atn8q1h 6,821.20
BEGIN FND_CP_OPP_IPC.Get_Message(:1,:2,:3,:4,:5,:6,:7,:

a71ts273jqc3a 6,453.58
UPDATE sqlobj$data

48zvyw6p3tmku 6,140.95
BEGIN FND_CONCURRENT.INIT_SQL_REQUEST; END;

5vbbcp6pd58b8 5,722.06
begin Fnd_Concurrent.Init_Request; end;

bq7ct6kf8k64q 5,170.51
select count(*) from x$kgllk where kgllkhdl=:1 and kgll

8xpmn5xx6y24v 4,344.26
BEGIN portal_po_p.import_po(:errbuf,:rc); END;

74gs47war08j3 4,165.70
INSERT INTO XLA_DIAG_SOURCES ( EVENT_ID , LEDGER_ID , S

5zhs3c9wwzrty 3,455.18
INSERT INTO XLA_DIAG_SOURCES ( EVENT_ID , LEDGER_ID , S

993qmrqk1y3jr 3,356.57
select MP.organization_id  from mtl_parameters MP where

15 Most expensive SQL in the workload repository
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL_ID          ELAPSED
------------- ----------
SQL_TEXT_FRAGMENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
fnr5818a3n66z  13,341.43
SELECT PID INTO :opid FROM V$PROCESS P, V$SESSION S

gyxv37z6aqyqp  12,691.98
SELECT P.PID, P.SPID, AUDSID, PROCESS, SUBSTR(USERENV('

5t39uchjqpyfm  12,403.42
BEGIN xla_accounting_pkg.unit_processor_batch(:errbuf,:

adw0hu9p9wyz1  11,735.05
BEGIN :1 := XLA_00707_AAD_C_000024_PKG.CreateJournalEnt

8xpmn5xx6y24v  10,752.60
BEGIN portal_po_p.import_po(:errbuf,:rc); END;

5vbbcp6pd58b8 9,899.19
begin Fnd_Concurrent.Init_Request; end;

53y9d2gsqsw0x 7,192.82
SELECT MIN(CREATION_DATE) FROM PO_DISTRIBUTIONS_ALL WHE

a71ts273jqc3a 6,450.66
UPDATE sqlobj$data

993qmrqk1y3jr 5,824.03
select MP.organization_id  from mtl_parameters MP where

74gs47war08j3 5,241.53
INSERT INTO XLA_DIAG_SOURCES ( EVENT_ID , LEDGER_ID , S

a8s0srb9jmzyy 4,908.62
UPDATE APPS.PORTAL_PO_HEADERS PPH SET PPH.POSTATUS = 'A

48zvyw6p3tmku 4,823.76
BEGIN FND_CONCURRENT.INIT_SQL_REQUEST; END;

acc988uzvjmmt 4,690.51
delete from WRH$_SYSMETRIC_HISTORY tab where (:beg_snap

bq7ct6kf8k64q 3,978.63
select count(*) from x$kgllk where kgllkhdl=:1 and kgll

5zhs3c9wwzrty 3,759.63
INSERT INTO XLA_DIAG_SOURCES ( EVENT_ID , LEDGER_ID , S

回复只看该作者道具举报

yorkshi

4^#

发表于 2013-9-23 17:03:20

Specify the Sql id
~~~~~~~~~~~~~~~~~~
Enter value for sqlid: gm7ak5qtcrd3m

Sql Id specified: gm7ak5qtcrd3m

Tune the sql
~~~~~~~~~~~~

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_64684
Tuning Task Owner  : SYS
Workload Type    : Single SQL Statement
Scope             : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at       : 09/23/2013 16:21:58
Completed at    : 09/23/2013 16:35:19

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID    : gm7ak5qtcrd3m
SQL Text : SELECT  nvl(count(*), :"SYS_B_00")
            FROM gl_je_segment_values sv,
                  gl_je_lines jel,
                  gl_ledger_segment_values lsv
            WHERE jel.je_header_id IN (:"SYS_B_01", :"SYS_B_02",
         :"SYS_B_03", :"SYS_B_04", :"SYS_B_05", :"SYS_B_06", :"SYS_B_07",
         :"SYS_B_08", :"SYS_B_09", :"SYS_B_10")
            AND jel.je_line_num = :"SYS_B_11"
            AND sv.je_header_id = jel.je_header_id
            AND sv.segment_type_code = :"SYS_B_12"
            AND lsv.ledger_id = :"SYS_B_13"
            AND lsv.segment_type_code = :"SYS_B_14"
            AND lsv.segment_value = sv.segment_value
            AND jel.effective_date BETWEEN nvl(lsv.start_date,
         jel.effective_date - :"SYS_B_15") AND nvl(lsv.end_date,
         jel.effective_date + :"SYS_B_16")

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.57%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile. A SQL plan baseline
corresponding to the plan with the SQL profile will also be created.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_64684',
         task_owner => 'SYS', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                        Original Plan  With SQL Profile  % Improved
                        -------------  ----------------  ----------
  Completion Status:          PARTIAL       COMPLETE
  Elapsed Time(ms):             30161             154    99.48%
  CPU Time(ms):                   1980             30    98.48%
  User I/O Time(ms):             29230             126    99.56%
  Buffer Gets:                   29029             120    99.58%
  Disk Reads:                   17638             39    99.77%
  Direct Writes:                      0                0
  Rows Processed:                   0                1
  Fetches:                            0                1
  Executions:                         0                1

回复只看该作者道具举报

yorkshi

5^#

发表于 2013-9-23 17:04:08

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 2873749939

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                            |    1 | 50 |  1380 (1)| 00:00:17 |
| 1 |  SORT AGGREGATE                |                            |    1 | 50 |          |       |
| 2 | NESTED LOOPS                |                            |    |    |          |       |
| 3 | NESTED LOOPS                |                            |    1 | 50 |  1380 (1)| 00:00:17 |
| 4 |    NESTED LOOPS             |                            | 342 | 11970 | 696 (1)| 00:00:09 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| GL_JE_LINES                | 342 |  6840 | 11 (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN       | GL_JE_LINES_IDX1          |    8 |    |    4 (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | GL_JE_SEGMENT_VALUES_U1    |    1 | 15 |    2 (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN          | GL_LEDGER_SEGMENT_VALUES_N1 |    1 |    |    1 (0)| 00:00:01 |
|*  9 | TABLE ACCESS BY INDEX ROWID  | GL_LEDGER_SEGMENT_VALUES |    1 | 15 |    2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - filter("JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_01) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_02)
            OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_03) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_04) OR
            "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_05) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_06) OR
            "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_07) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_08) OR
            "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_09) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_10))
6 - access("JEL"."JE_LINE_NUM"=:SYS_B_11)
7 - access("SV"."JE_HEADER_ID"="JEL"."JE_HEADER_ID" AND "SV"."SEGMENT_TYPE_CODE"=:SYS_B_12)
   filter("SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_01) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_02) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_03) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_04) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_05) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_06) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_07) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_08) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_09) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_10))
8 - access("LSV"."LEDGER_ID"=:SYS_B_13 AND "LSV"."SEGMENT_TYPE_CODE"=:SYS_B_14 AND
            "LSV"."SEGMENT_VALUE"="SV"."SEGMENT_VALUE")
9 - filter("JEL"."EFFECTIVE_DATE">=NVL("LSV"."START_DATE",INTERNAL_FUNCTION("JEL"."EFFECTIVE_DATE")-
            :SYS_B_15) AND "JEL"."EFFECTIVE_DATE"<=NVL("LSV"."END_DATE",INTERNAL_FUNCTION("JEL"."EFFECTIVE_DATE")+:
            SYS_B_16))

2- Original With Adjusted Cost
------------------------------
Plan hash value: 2873749939

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                            |    1 | 50 |  1380 (1)| 00:00:17 |
| 1 |  SORT AGGREGATE                |                            |    1 | 50 |          |       |
| 2 | NESTED LOOPS                |                            |    |    |          |       |
| 3 | NESTED LOOPS                |                            |    1 | 50 |  1380 (1)| 00:00:17 |
| 4 |    NESTED LOOPS             |                            | 342 | 11970 | 696 (1)| 00:00:09 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| GL_JE_LINES                | 342 |  6840 | 11 (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN       | GL_JE_LINES_IDX1          |    8 |    |    4 (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | GL_JE_SEGMENT_VALUES_U1    |    1 | 15 |    2 (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN          | GL_LEDGER_SEGMENT_VALUES_N1 |    1 |    |    1 (0)| 00:00:01 |
|*  9 | TABLE ACCESS BY INDEX ROWID  | GL_LEDGER_SEGMENT_VALUES |    1 | 15 |    2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

回复只看该作者道具举报

yorkshi

6^#

发表于 2013-9-23 17:04:30

Predicate Information (identified by operation id):
---------------------------------------------------

5 - filter("JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_01) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_02)
            OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_03) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_04) OR
            "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_05) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_06) OR
            "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_07) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_08) OR
            "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_09) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_10))
6 - access("JEL"."JE_LINE_NUM"=:SYS_B_11)
7 - access("SV"."JE_HEADER_ID"="JEL"."JE_HEADER_ID" AND "SV"."SEGMENT_TYPE_CODE"=:SYS_B_12)
   filter("SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_01) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_02) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_03) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_04) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_05) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_06) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_07) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_08) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_09) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_10))
8 - access("LSV"."LEDGER_ID"=:SYS_B_13 AND "LSV"."SEGMENT_TYPE_CODE"=:SYS_B_14 AND
            "LSV"."SEGMENT_VALUE"="SV"."SEGMENT_VALUE")
9 - filter("JEL"."EFFECTIVE_DATE">=NVL("LSV"."START_DATE",INTERNAL_FUNCTION("JEL"."EFFECTIVE_DATE")-
            :SYS_B_15) AND "JEL"."EFFECTIVE_DATE"<=NVL("LSV"."END_DATE",INTERNAL_FUNCTION("JEL"."EFFECTIVE_DATE")+:
            SYS_B_16))

3- Using SQL Profile
--------------------
Plan hash value: 2935681456

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                         |    1 | 50 | 29 (4)| 00:00:01 |
| 1 |  SORT AGGREGATE             |                         |    1 | 50 |          |       |
|*  2 | HASH JOIN                   |                         |    1 | 50 | 29 (4)| 00:00:01 |
| 3 | NESTED LOOPS             |                         |    |    |          |       |
| 4 |    NESTED LOOPS             |                         | 342 | 11970 | 23 (0)| 00:00:01 |
| 5 |    INLIST ITERATOR          |                         |    |    |          |       |
|*  6 |    INDEX RANGE SCAN       | GL_JE_SEGMENT_VALUES_U1  | 10 | 150 | 12 (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN       | GL_JE_LINES_U1          |    1 |    |    2 (0)| 00:00:01 |
| 8 |    TABLE ACCESS BY INDEX ROWID| GL_JE_LINES             |    1 | 20 |    3 (0)| 00:00:01 |
|*  9 | TABLE ACCESS FULL          | GL_LEDGER_SEGMENT_VALUES |  1017 | 15255 |    5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("LSV"."SEGMENT_VALUE"="SV"."SEGMENT_VALUE")
   filter("JEL"."EFFECTIVE_DATE">=NVL("LSV"."START_DATE",INTERNAL_FUNCTION("JEL"."EFFECTIVE_DAT
            E")-:SYS_B_15) AND "JEL"."EFFECTIVE_DATE"<=NVL("LSV"."END_DATE",INTERNAL_FUNCTION("JEL"."EFFECTIVE_
            DATE")+:SYS_B_16))
6 - access(("SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_01) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_02) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_03) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_04) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_05) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_06) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_07) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_08) OR "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_09) OR
            "SV"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_10)) AND "SV"."SEGMENT_TYPE_CODE"=:SYS_B_12)
7 - access("SV"."JE_HEADER_ID"="JEL"."JE_HEADER_ID" AND "JEL"."JE_LINE_NUM"=:SYS_B_11)
   filter("JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_01) OR
            "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_02) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_03) OR
            "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_04) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_05) OR
            "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_06) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_07) OR
            "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_08) OR "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_09) OR
            "JEL"."JE_HEADER_ID"=TO_NUMBER(:SYS_B_10))
9 - filter("LSV"."LEDGER_ID"=:SYS_B_13 AND "LSV"."SEGMENT_TYPE_CODE"=:SYS_B_14)

-------------------------------------------------------------------------------

回复只看该作者道具举报

Stone

7^#

发表于 2013-9-23 21:43:42

本帖最后由 Stone 于 2013-9-23 21:46 编辑

从sqlrpt的输出可以看出有多个sql plan, 多数是由于统计信息和其他因素的变化导致执行计划不稳定，最好还是要及时地针对sql语句所设计的表做信息统计，消除由于信息陈旧或者不准造成的执行计划的改变，建议对sql做一个健康检查，可以使用sqlhc，然后上传诊断结果，大家再一起研究下。详细可以参考：http://www.askmaclean.com/archives/sql-tuning-health-check-script.html

Good luck.

回复只看该作者道具举报

yorkshi

8^#

发表于 2013-9-23 22:39:37

好，我尝试下用sqlhc！谢谢

回复只看该作者道具举报

xifenfei

9^#

发表于 2013-9-24 07:44:03

sql profile 使用http://www.xifenfei.com/3044.html

回复只看该作者道具举报

Liu Maclean(刘相兵

10^#

发表于 2013-9-24 09:35:38

execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_64684',task_owner => 'SYS', replace => TRUE);

执行下即可

回复只看该作者道具举报

yorkshi

11^#

发表于 2013-9-24 14:03:09

sqlhc执行了，各位看一下附件！

sqlhc_UAT_apptest_11.1.0.7.0_gm7ak5qtcrd3m_20130924133324.html

54.68 KB, 下载次数: 744

回复只看该作者道具举报

yorkshi

12^#

发表于 2013-9-24 14:03:59

Stone 发表于 2013-9-23 21:43
从sqlrpt的输出可以看出有多个sql plan, 多数是由于统计信息和其他因素的变化导致执行计划不稳定，最好还是 ...

已经执行sqlhc，见附件

回复只看该作者道具举报

yorkshi

13^#

发表于 2013-9-24 14:34:34

Liu Maclean(刘相兵发表于 2013-9-24 09:35
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_64684',task_owner => 'SYS', replace => T ...

非常好用，光SQL是可以了！呵呵谢谢！现在去看看请求速度如何！

回复只看该作者道具举报

Stone

14^#

发表于 2013-9-24 22:14:10

本帖最后由 Stone 于 2013-9-24 22:17 编辑

yorkshi 发表于 2013-9-24 14:03
已经执行sqlhc，见附件

Maclean的建议很实用，反正是accept sql profile应该至少可以保证一个相对比较好的执行计划。当然这样做，如果各个表的数据分布在没有大的变化情况下，应该是没有问题的。

不过如果有机会根据数据的具体情况看看为什么原来快，现在慢；为什么执行计划在不断地变化，会更加明白导致变慢的原因。

从上传的sqlhc分析来看，查询涉及到的表已经60多天没有重新收集数据啦，如果在这个期间，数据的变化比较大的话，就可能会影响到执行计划啦。所以建议对这三个表做分析，当然在做以前，可以先看看表的实际数据是多少，再跟sqlhc报告的数据是否有差异，这样就可以帮助进一步来低位问题啦。

12	TABLE	GL.GL_JE_LINES	Table CBO statistics are 66 days old: 2013-07-20/02:36:05.	Consider gathering fresh table statistics with a sample size of 3%. Old statistics could contain low/high values for which a predicate may be out of range, producing then a poor plan.
13	TABLE	GL.GL_JE_SEGMENT_VALUES	Table CBO statistics are 66 days old: 2013-07-20/02:57:22.	Consider gathering fresh table statistics with a sample size of 30%. Old statistics could contain low/high values for which a predicate may be out of range, producing then a poor plan.
14	TABLE	GL.GL_LEDGER_SEGMENT_VALUES	Table CBO statistics are 66 days old: 2013-07-20/02:57:37.	Consider gathering fresh table statistics with a sample size of 100%. Old statistics could contain low/high values for which a predicate may be out of range, producing then a poor plan.

另外也可以考虑收集下典型负载时间段的"System Statistics"，这样CBO在决定执行计划时会跟接近实际的情况。

[tr][/tr]

DBMS_STATS

SYSTEM STATISTICS

Workload CBO System Statistics are not gathered. CBO is using default values.

Consider gathering workload system statistics using DBMS_STATS.GATHER_SYSTEM_STATS.
See also 465787.1.

分析仅是一家之言，供参考。
Stone

回复只看该作者道具举报

yorkshi

15^#

发表于 2013-9-25 09:40:45

Stone 发表于 2013-9-24 22:14
Maclean的建议很实用，反正是accept sql profile应该至少可以保证一个相对比较好的执行计划。当然这样做 ...

由于我做测试的库是一套开发库平时没有定时收集统计信息，里面的数据不会有变化，但该SQL在生产及开发环境的效果是一样的，找时间我去生产拉一份，做一下比对。谢谢！

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册