SELECT 
  r.item_name,
  r.value,
  r.mdlid,
  r.rowsort,
  r.year,
  r.stmtid
FROM 
  ( SELECT t.item_grpflag,
  t.item_name AS item_name,
  ROUND(t.value,
  0) AS value,
  t.mdlid,
  t.item_id AS rowsort,
  t.year,
  t.stmtid FROM( SELECT l.item_grpflag,
  l.item_name AS item_name,
  l.value,
  l.mdlid,
  l.item_id,
  l.stmtid,
  m.year FROM ( SELECT a.item_grpflag,
  NVL(b.stmtid,
  0) AS stmtid,
  a.fm_row,
  d.mdlid,
  LPAD(a.item_name,
  LENGTH(a.item_name)+((a.tr_lvl-1)*5),
  ' ') AS item_name,
  b.value,
  a.item_id FROM coaitems a,
  coabalances b,
  coatree c,
  model d
WHERE 
  a.coaid = b.coaid (+) AND
  a.tr_id = b.tr_id (+) AND
  a.item_id = b.item_id (+) AND
  a.fm_row = b.coarow (+) AND
  a.coaid = c.coaid AND
  a.tr_id = c.tr_id AND
  a.coaid = d.mdlid AND
  trim(LOWER(c.tr_name)) = 'balance sheet' AND
  a.item_id > 0 AND
  d.mdlid  =1 AND
  b.borrid (+) = 3280 AND
  b.user_id (+) = 24  ORDER BY a.coaid, a.fm_row ) l, ( SELECT 0 AS stmtid, '0' AS year FROM dual UNION ALL SELECT n.stmtid, '(' || ROWNUM || ')' || CHR(13) || n.year FROM ( SELECT x.stmtid, z.auditmethod_name||'  '||TO_CHAR(x.stmtdt) || ' [ ' || x.stmtperiod || 'M ]' AS year FROM STATEMENT x, model y, audit_method z WHERE x.audit_method_id = z.id AND
  x.coaid = y.coaid AND
  y.mdlid = 1 AND
  x.borrid = 3280 AND
  x.user_id = 24 AND
  x.stmtid IN (SELECT stmtid FROM (SELECT stmtid, ROWNUM FROM STATEMENT WHERE borrid = 3280 AND
  coaid = 1 AND
  user_id = 24 ORDER BY stmtid DESC) WHERE ROWNUM <= 3)  ) n ) m WHERE(l.stmtid = m.stmtid) ORDER BY l.fm_row, l.stmtid)t WHERE  t.stmtid IN (SELECT 0 AS stmtid FROM dual UNION ALL SELECT stmtid FROM (SELECT stmtid, ROWNUM FROM (SELECT DISTINCT a.stmtid FROM STATEMENT a WHERE A.BORRID = 3280 AND
  A.coaid = 1 AND
  A.USER_ID = 24 ORDER BY a.stmtid DESC )) WHERE ROWNUM <= 3) ORDER BY t.stmtid ASC )r

Below is the explain plan of the above query

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=21399039 Card=669617
          7 Bytes=14296337895)

   1    0   VIEW (Cost=21399039 Card=6696177 Bytes=14296337895)
   2    1     SORT (ORDER BY) (Cost=21399039 Card=6696177 Bytes=143699
          95842)

   3    2       HASH JOIN (Cost=17665 Card=6696177 Bytes=14369995842)
   4    3         VIEW OF 'VW_NSO_1' (Cost=16 Card=8169 Bytes=106197)
   5    4           SORT (UNIQUE) (Cost=16 Card=8169 Bytes=13)
   6    5             UNION-ALL
   7    6               TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=81
          68)

   8    6               COUNT (STOPKEY)
   9    8                 VIEW (Cost=5 Card=1 Bytes=13)
  10    9                   COUNT
  11   10                     VIEW (Cost=5 Card=1 Bytes=13)
  12   11                       SORT (UNIQUE) (Cost=4 Card=1 Bytes=10)
  13   12                         TABLE ACCESS (BY INDEX ROWID) OF 'ST
          ATEMENT' (Cost=2 Card=1 Bytes=10)

  14   13                           INDEX (RANGE SCAN) OF 'IDX_BORRID_
          USER_COAID' (NON-UNIQUE) (Cost=1 Card=1)

  15    3         VIEW (Cost=17341 Card=13935 Bytes=29723355)
  16   15           SORT (ORDER BY) (Cost=17341 Card=13935 Bytes=30085
          665)

  17   16             HASH JOIN (Cost=46 Card=13935 Bytes=30085665)
  18   17               VIEW (Cost=21 Card=29 Bytes=60001)
  19   18                 SORT (ORDER BY) (Cost=21 Card=29 Bytes=11368
          )

  20   19                   HASH JOIN (OUTER) (Cost=18 Card=29 Bytes=1
          1368)

  21   20                     HASH JOIN (Cost=15 Card=29 Bytes=10759)
  22   21                       MERGE JOIN (CARTESIAN) (Cost=3 Card=1
          Bytes=168)

  23   22                         TABLE ACCESS (BY INDEX ROWID) OF 'CO
          ATREE' (Cost=1 Card=1 Bytes=155)

  24   23                           INDEX (RANGE SCAN) OF 'IDX_TR_NAME
          ' (NON-UNIQUE) (Cost=1 Card=1)

  25   22                         BUFFER (SORT) (Cost=2 Card=1 Bytes=1
          3)

  26   25                           TABLE ACCESS (FULL) OF 'MODEL' (Co
          st=2 Card=1 Bytes=13)

  27   21                       TABLE ACCESS (FULL) OF 'COAITEMS' (Cos
          t=11 Card=258 Bytes=52374)

  28   20                     TABLE ACCESS (BY INDEX ROWID) OF 'COABAL
          ANCES' (Cost=2 Card=1 Bytes=21)

  29   28                       INDEX (RANGE SCAN) OF 'IDX_BORRID_USER
          _ID' (NON-UNIQUE) (Cost=1 Card=5)

  30   17               VIEW (Cost=22 Card=8169 Bytes=735210)
  31   30                 UNION-ALL
  32   31                   TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Car
          d=8168)

  33   31                   COUNT
  34   33                     HASH JOIN (Cost=11 Card=1 Bytes=100)
  35   34                       TABLE ACCESS (BY INDEX ROWID) OF 'STAT
          EMENT' (Cost=2 Card=1 Bytes=21)

  36   35                         NESTED LOOPS (Cost=8 Card=1 Bytes=60
          )

  37   36                           MERGE JOIN (CARTESIAN) (Cost=6 Car
          d=1 Bytes=39)

  38   37                             TABLE ACCESS (FULL) OF 'MODEL' (
          Cost=2 Card=1 Bytes=26)

  39   37                             BUFFER (SORT) (Cost=4 Card=1 Byt
          es=13)

  40   39                               VIEW OF 'VW_NSO_2' (Cost=4 Car
          d=1 Bytes=13)

  41   40                                 SORT (UNIQUE)
  42   41                                   COUNT (STOPKEY)
  43   42                                     VIEW (Cost=4 Card=1 Byte
          s=13)

  44   43                                       SORT (ORDER BY STOPKEY
          ) (Cost=4 Card=1 Bytes=10)

  45   44                                         COUNT
  46   45                                           TABLE ACCESS (BY I
          NDEX ROWID) OF 'STATEMENT' (Cost=2 Card=1 Bytes=10)

  47   46                                             INDEX (RANGE SCA
          N) OF 'IDX_BORRID_USER_COAID' (NON-UNIQUE) (Cost=1 Card=1)

  48   36                           INDEX (RANGE SCAN) OF 'IDX_STATEME
          NT_4FLDS' (NON-UNIQUE) (Cost=1 Card=1)

  49   34                       TABLE ACCESS (FULL) OF 'AUDIT_METHOD'
          (Cost=2 Card=82 Bytes=3280)





Statistics
----------------------------------------------------------
       2699  recursive calls
          0  db block gets
        872  consistent gets
        189  physical reads
          0  redo size
      11942  bytes sent via SQL*Net to client
        689  bytes received via SQL*Net from client
         18  SQL*Net roundtrips to/from client
         39  sorts (memory)
          0  sorts (disk)
        247  rows processed