Slow sorting

[vnktummala]

DB - Oracle 10g (10.2.0.4)
OS - HP UNIX
db_file_multiblock_read_count=64

I have a query which fetches around 6 million records and do order by. Please find the query below (I have changed the table / column names)

SELECT /*+ PARALLEL(TBL 8) */
COLA,
COLB,
COLC,
COLD,
COLE,
COLF,
COLG,
COLH,
COLI,
COLJ,
COLK,
COLL
FROM
TBL
ORDER BY
COLA,
COLB,
COLC,
COLD,
COLE,
COLF,
COLG,
COLH,
COLI,
COLJ,
COLK,
COLL

Please find the auto trace output below.

Statistics
----------------------------------------------------------
4001 recursive calls
18 db block gets
276723 consistent gets
525087 physical reads
1480 redo size
789593219 bytes sent via SQL*Net to client
4448243 bytes received via SQL*Net from client
404343 SQL*Net roundtrips to/from client
9 sorts (memory)
1 sorts (disk)
6065116 rows processed

Thou explain plan showing 12 minutes for this query, actually it is taking close to 2 hours to complete. I am trying to find why it is taking 2 hours and couple of questing flashing in my mind.

is this because of (disk) sort?

is this because of more recursive calls / consistent gets?

As this is simple query, not much options in my hand. Could some one drop some hints to me so that I can tune this query in a better way?



Thanks,