9i Slow SQL Performance - EXPERTS apply within

From: Johne_uk <edgarj_at_tiscali.co.uk>
Date: 5 Apr 2006 06:42:58 -0700
Message-ID: <1144244578.655301.77610@i40g2000cwc.googlegroups.com>

Hi,

I have a sql performance problem and would appreciate some input to resolve it.

I operate 2 9i DB instances (1 x Win and 1 x Solaris) and 1 8i instance (Solaris). Both Solaris boxes are identical in terms of hardware / performance. The Windows box is a little faster.

All 3 instances have an identical schema. The Solaris 9i instance is the production with the other 2 test instances. All schema objects on the 3 instances are identical (tables / indexes etc) but the 2 test systems have a little less data in some tables (approx 10%)

The problem is one of performance. When running a particular query on all 3 instances both the test systems return a resultset in about 1 second. The production instance is taking 34 secs. There are no issues with tablespace sizes etc. and no other areas of the application appear to be running slowly.

I've take a number of steps to identify the problem.

Rebuilt all indexes.
Gathered up-to-date stats.
Changed sort_area_size parameter (increased). No difference.
Ran Explain Plans on both 9i instances (execution plans were identical as were stats).
Ran trace and examined output with TKPROF. Output is below but there is nothing alarming other than that the Fetch time is 1 second whilst the entire query takes 34 secs to run.

I'm not an experienced DBA so would appreciate some input on where to start looking next. I'm wondering if some of the init.ora parameters may be unoptimised.

Thanks in advance.
John

TKPROF

call count cpu elapsed disk query current

rows
------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.06       0.05          0          0          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        3      0.97       1.00          3      14054         56
      21

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

total        5      1.03       1.05          3      14054         56
      21

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation

-------  ---------------------------------------------------
     21  FILTER
     22   NESTED LOOPS
     22    NESTED LOOPS
      2     TABLE ACCESS BY INDEX ROWID MA_COMPANY
      2      INDEX UNIQUE SCAN (object id 108099)
     22     VIEW VA_VOYAGE_LIST
     22      SORT GROUP BY
     81       FILTER
     82        NESTED LOOPS
     82         HASH JOIN
    317          INDEX FAST FULL SCAN (object id 108180)
     81          FILTER
   9092           HASH JOIN OUTER
   3124            NESTED LOOPS OUTER
   3125             NESTED LOOPS
   3127              HASH JOIN
   3126               HASH JOIN
   9094                INDEX FAST FULL SCAN (object id 108335)
   3145                TABLE ACCESS FULL CH_FIXTURE_SUMMARY
   9139               TABLE ACCESS FULL OP_VOYAGE_MARKER
   6250              TABLE ACCESS BY INDEX ROWID OP_POSITION_DETAILS
   6250               INDEX RANGE SCAN (object id 108308)
   3124             TABLE ACCESS BY INDEX ROWID OP_POSITION_DETAILS
   6248              INDEX UNIQUE SCAN (object id 108308)
  26608            TABLE ACCESS FULL OP_POSITION_DETAILS
    162         INDEX UNIQUE SCAN (object id 108164)
     42    TABLE ACCESS BY INDEX ROWID CH_FIXTURE_SUMMARY
     42     INDEX UNIQUE SCAN (object id 107964)
      9   TABLE ACCESS FULL VA_CONFIG_INFO
      0        SORT AGGREGATE
      0         TABLE ACCESS BY INDEX ROWID OP_POSITION_DETAILS
      0          AND-EQUAL
      0           INDEX RANGE SCAN (object id 108307)
      0           INDEX RANGE SCAN (object id 108306)

********************************************************************************

Received on Wed Apr 05 2006 - 08:42:58 CDT