Re: Performance Problem

From: jsfromynr <jatinder.1975_at_gmail.com>
Date: 24 Apr 2006 00:10:20 -0700
Message-ID: <1145862620.635353.154330@j33g2000cwa.googlegroups.com>

Hi Steve,

I tried Full Hint on Voucher Tables and Hash Join between them. The query finished in nearly six minutes.

This is the TKProf output . Only 276 rows are contributed from the upper Part of Union ALL.

Rows Row Source Operation

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

18471 UNION-ALL
*** 276 FILTER

1436 FILTER 550476 HASH JOIN OUTER 550476 HASH JOIN 656792 HASH JOIN 130141 TABLE ACCESS FULL VOUCHER_DTL 46 TABLE ACCESS FULL SERIES 46256 TABLE ACCESS FULL VOUCHER_HDR 950650 TABLE ACCESS FULL INV_HDR 0 TABLE ACCESS FULL SBB_IMPL_BRS 250 INDEX RANGE SCAN IDX_DOC_NUMBER_COMP (object id 81153) 18195 FILTER 19435 NESTED LOOPS OUTER 19435 NESTED LOOPS 19435 MERGE JOIN CARTESIAN 1 VIEW 1 SORT UNIQUE 1 TABLE ACCESS FULL SBB_IMPL_BRS 19435 BUFFER SORT 19435 TABLE ACCESS FULL CUST_VCHR_DATA_SBB 19435 TABLE ACCESS BY INDEX ROWID SERIES 19435 INDEX UNIQUE SCAN PK_SERIES (object id 73167) 10605 TABLE ACCESS BY INDEX ROWID INV_HDR 12880 INDEX UNIQUE SCAN UK_INV_HDR (object id 75211)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

Waited ----------
SQL*Net message to client 2467 0.00 0.00 db file sequential read 3023 0.04 5.63 db file scattered read 53128 0.48 417.20 direct path write 1650 0.32 9.36 direct path read 1677 0.03 6.07 SQL*Net message from client 2466 55.90 94.20 latch free 1 0.00 0.00

&
Statistics

          0  recursive calls
          0  db block gets
     244309  consistent gets
     169874  physical reads
          0  redo size
     691171  bytes sent via SQL*Net to client
      46994  bytes received via SQL*Net from client
       1233  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      18471  rows processed

Any help is greatly appreciated.

With Warm regards
Jatinder Singh
jsfromynr wrote:

> Hi ianal,
>
> Thanks I did not know this site exists all I know was metalink.
> http://metalink.oracle.com
> As I am making a shift from SQL Server front so know little about these
> (TKProf).
> I will make sure to ask an informed question .
> 
> With Warm regards
> Jatinder Singh

Received on Mon Apr 24 2006 - 02:10:20 CDT