sql优化

1.发现问题，从statspack中抓到一个sql需要的buffer gets和disk reads很高

STATSPACK SQL report for Hash Value: 2088862589 Module: InSiteXMLServer.exe

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
INST933 3144597339 inst933 1 9.2.0.8.0 NO mesdb01

Start Id Start Time End Id End Time Duration(mins)
--------- ------------------- --------- ------------------- --------------
7017 16-Mar-08 00:00:04 7041 17-Mar-08 00:00:04 1,440.00

SQL Statistics
~~~~~~~~~~~~~~
-> CPU and Elapsed Time are in seconds (s) for Statement Total and in
milliseconds (ms) for Per Execute
% Snap
Statement Total Per Execute Total
--------------- --------------- ------
Buffer Gets: 64,135,530 386,358.6 9.76
Disk Reads: 59,235,355 356,839.5 14.17
Rows processed: 0 0.0
CPU Time(s/ms): 1,663 10,020.4
Elapsed Time(s/ms): 1,678 10,105.9
Sorts: 332 2.0
Parse Calls: 0 .0
Invalidations: 0
Version count: 1
Sharable Mem(K): 57
Executions: 166

SQL Text
~~~~~~~~
SELECT A.MOVEINQTY MOVEINQTY FROM OPERATION C,SPEC B,HISTORYMA
INLINE A WHERE A.FROMSPECID = B.SPECID AND B.OPERATIONID = C.O
PERATIONID AND A.CDONAME = 'MoveLot' AND A.CONTAINERID = :b1
AND C.OPERATIONGROUPID = :b2 ORDER BY A.TXNDATE

Known Optimizer Plan(s) for this Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows all known Optimizer Plans for this database instance, and the Snap Id's
they were first found in the shared pool. A Plan Hash Value will appear
multiple times if the cost has changed
-> ordered by Snap Id

First First Plan
Snap Id Snap Time Hash Value Cost
-------- --------------- ------------ ----------
5253 02 Jan 08 12:00 3096070363 27904
5709 21 Jan 08 12:00 3096070363 31028
6407 19 Feb 08 14:00 3096070363 33979
7011 15 Mar 08 18:00 2693563219 37125

Plans in shared pool between Begin and End Snap Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Shows the Execution Plans found in the shared pool between the begin and end
snapshots specified. The values for Rows, Bytes and Cost shown below are those
which existed at the time the first-ever snapshot captured this plan - these
values often change over time, and so may not be indicative of current values
-> Rows indicates Cardinality, PHV is Plan Hash Value
-> ordered by Plan Hash Value

--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 2693563219 ----| | | 2324 |
|SORT ORDER BY | | 1 | 111 | 2324 |
| NESTED LOOPS | | 1 | 111 | 2322 |
| MERGE JOIN CARTESIAN | | 1 | 77 | 2321 |
| TABLE ACCESS FULL |HISTORYMAINLINE | 1 | 58 | 2320 |
| BUFFER SORT | | 1 | 19 | 1 |
| TABLE ACCESS BY INDEX ROWID |OPERATION | 1 | 19 | 1 |
| INDEX RANGE SCAN |OPERATION2 | 1 | | |
| TABLE ACCESS BY INDEX ROWID |SPEC | 1 | 34 | 1 |
| INDEX UNIQUE SCAN |SPEC361 | 1 | | |
--------------------------------------------------------------------------------

End of Report

2.查看涉及的表及索引状况

OPERATION, SPEC表数据很小，大概只有100多条记录
HISTORYMAINLINE 比较大，有1百多万条记录

3.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=106)
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=1Bytes=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 Card=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

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10897379/viewspace-1001410/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10897379/viewspace-1001410/