这样的sql怎么优化？

rollingpig

参数不一样，执行的效率当然可能不一样。

rollingpig

铁个statspack report上来，也许看得到里面的SQL

Terry916

好像看不到的

[ 本帖最后由 Terry916 于 2008-3-26 16:14 编辑 ]

棉花糖ONE

statspack看全表扫描比较多啊,对那个存储过程做个sql　trace不成吗

[ 本帖最后由 棉花糖ONE 于 2008-3-25 13:01 编辑 ]

rollingpig

就是这个SQL,是procedure里的主要语句，对比一下它们的CPU,buffer get , disk read就知道了
SELECT A.MOVEINQTY MOVEINQTY   
FROM OPERATION C,SPEC B,HISTORYMAINLINE A  
WHERE A.FROMSPECID = B.SPECID  
AND B.OPERATIONID = C.OPERATIONID  
AND A.CDONAME = 'MoveLot'  AND A.CONTAINERID = :b1
AND C.OPERATIONGROUPID = :b2
ORDER BY A.TXNDATE

另外再看看其他Top disk get SQL

Terry916

trace file

Terry916

原帖由 rollingpig 于 2008-3-25 12:54 发表
就是这个SQL,是procedure里的主要语句，对比一下它们的CPU,buffer get , disk read就知道了
SELECT A.MOVEINQTY MOVEINQTY   
FROM OPERATION C,SPEC B,HISTORYMAINLINE A  
WHERE A.FROMSPECID = B.SPECID  
AND B.OPERATIONID = C.OPERATIONID  
AND A.CDONAME = 'MoveLot'  AND A.CONTAINERID = :b1
AND C.OPERATIONGROUPID = :b2
ORDER BY A.TXNDATE

另外再看看其他Top disk get SQL

这个sql的确有问题，但是我在trace里面没有发现是procedure里的语句
SQL> set autotrace on
SQL> SELECT  A.MOVEINQTY MOVEINQTY   
  2  FROM
  3  OPERATION C,
  4  SPEC B,
  5  HISTORYMAINLINE A  
  6  WHERE
  7  A.FROMSPECID = B.SPECID  
  8  AND B.OPERATIONID = C.OPERATIONID  
  9  AND A.CDONAME = 'MoveLot'  
10  AND A.CONTAINERID = :b1
11  AND C.OPERATIONGROUPID = :b2
12  ORDER BY A.TXNDATE
13  /

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8156 Card=1 Bytes=10
          6)

   1    0   SORT (ORDER BY) (Cost=8156 Card=1 Bytes=106)
   2    1     NESTED LOOPS (Cost=8140 Card=1 Bytes=106)
   3    2       MERGE JOIN (CARTESIAN) (Cost=8139 Card=1 Bytes=72)
   4    3         TABLE ACCESS (FULL) OF 'HISTORYMAINLINE' (Cost=8139
          Card=3 Bytes=162)

   5    3         BUFFER (SORT)
   6    5           TABLE ACCESS (BY INDEX ROWID) OF 'OPERATION'
   7    6             INDEX (RANGE SCAN) OF 'OPERATION2' (NON-UNIQUE)
   8    2       TABLE ACCESS (BY INDEX ROWID) OF 'SPEC' (Cost=1 Card=1
           Bytes=34)

   9    8         INDEX (UNIQUE SCAN) OF 'SPEC361' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      93481  consistent gets
      51472  physical reads
          0  redo size
        304  bytes sent via SQL*Net to client
        461  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>
SQL> SELECT  /*+ORDERED*/ A.MOVEINQTY MOVEINQTY   
  2  FROM
  3  OPERATION C,
  4  SPEC B,
  5  HISTORYMAINLINE A  
  6  WHERE
  7  A.FROMSPECID = B.SPECID  
  8  AND B.OPERATIONID = C.OPERATIONID  
  9  AND A.CDONAME = 'MoveLot'  
10  AND A.CONTAINERID = :b1
11  AND C.OPERATIONGROUPID = :b2
12  ORDER BY A.TXNDATE
13  /

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8158 Card=1 Bytes=10
          6)

   1    0   SORT (ORDER BY) (Cost=8158 Card=1 Bytes=106)
   2    1     HASH JOIN (Cost=8142 Card=1 Bytes=106)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'SPEC' (Cost=1 Card=1
           Bytes=34)

   4    3         NESTED LOOPS (Cost=2 Card=1 Bytes=52)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'OPERATION' (Cost
          =1 Card=1 Bytes=18)

   6    5             INDEX (RANGE SCAN) OF 'OPERATION2' (NON-UNIQUE)
          (Cost=1 Card=1)

   7    4           INDEX (RANGE SCAN) OF 'SPEC2' (NON-UNIQUE)
   8    2       TABLE ACCESS (FULL) OF 'HISTORYMAINLINE' (Cost=8139 Ca
          rd=3 Bytes=162)





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

alantany

原帖由 Terry916 于 2008-3-25 13:00 发表
trace file

看起来不是这个function  YieldHandler.ExecuteData的问题。
整个过程不到很短。

Terry916

trace 文件产生的过程
SQL> alter session set events '10046 trace name context forever';

Session altered.

SQL> variable SYS_B_00 varchar2
SQL> variable SYS_B_01 number
SQL> variable SYS_B_02 varchar2
SQL> variable SYS_B_03 varchar2
SQL> variable SYS_B_04 varchar2
SQL> variable SYS_B_05 varchar2
SQL> variable SYS_B_06 varchar2
SQL> variable SYS_B_07 varchar2
SQL> variable SYS_B_08 varchar2
SQL> variable SYS_B_09 varchar2
SQL> variable SYS_B_10 varchar2
SQL> variable SYS_B_11 varchar2
SQL> variable SYS_B_12 varchar2
SQL> variable SYS_B_13 varchar2
SQL> variable SYS_B_14 varchar2
SQL> SELECT YieldHandler.ExecuteData(:"SYS_B_00",:"SYS_B_01",:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",
  2  :"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12",:"SYS_B_13",:"SYS_B_14") Result FROM DUAL
  3  /

RESULT
--------------------------------------------------------------------------------
0 PASS

SQL> alter session set events '10046 trace name context off';

Session altered.

alantany

原帖由 Terry916 于 2008-3-25 13:13 发表
trace 文件产生的过程
SQL> alter session set events '10046 trace name context forever';

Session altered.

SQL> variable SYS_B_00 varchar2
SQL> variable SYS_B_01 number
SQL> variable SYS_B_02 varchar2
SQL> variable SYS_B_03 varchar2
SQL> variable SYS_B_04 varchar2
SQL> variable SYS_B_05 varchar2
SQL> variable SYS_B_06 varchar2
SQL> variable SYS_B_07 varchar2
SQL> variable SYS_B_08 varchar2
SQL> variable SYS_B_09 varchar2
SQL> variable SYS_B_10 varchar2
SQL> variable SYS_B_11 varchar2
SQL> variable SYS_B_12 varchar2
SQL> variable SYS_B_13 varchar2
SQL> variable SYS_B_14 varchar2
SQL> SELECT YieldHandler.ExecuteData(:"SYS_B_00",:"SYS_B_01",:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",
  2  :"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12",:"SYS_B_13",:"SYS_B_14") Result FROM DUAL
  3  /

RESULT
--------------------------------------------------------------------------------
0 PASS

SQL> alter session set events '10046 trace name context off';

Session altered.

执行时间不到1s钟，这个函数有什么问题需要优化？