select query taking long time

I am running a very simple query without any where clause . It is full table select in spool file. Query is taking 15 hours to generate spool file. spool file size is 15G.

• table size 9G
• sga_max_size-9G
• db_cache size- 5G
• shared pool size 2G
• pga size 5G

• table have no indexes Query as follows:

   SET head OFF;
   SET feed OFF;
   spool /data13/reptest/rep.dat
  
   select 
   LEA_DWH_REPAYSCH.EMI_NUM || '~' ||
   LEA_DWH_REPAYSCH.FINNESS_ACCT_NUM|| '~' ||
   to_char(LEA_DWH_REPAYSCH.MISDATE,'dd/mm/yyyy')|| '~' ||
   LEA_DWH_REPAYSCH.PRINCOMP|| '~' ||
   LEA_DWH_REPAYSCH.INTCOMP|| '~' ||
   LEA_DWH_REPAYSCH.EXINTCOM开发者_如何学PythonP|| '~' ||
   LEA_DWH_REPAYSCH.EMI_AMT|| '~' ||
   LEA_DWH_REPAYSCH.INTCOMP_RECD|| '~' ||
   LEA_DWH_REPAYSCH.PRINCOMP_RECD|| '~' ||
   LEA_DWH_REPAYSCH.TOTAL_RECDAMT|| '~' ||
   LEA_DWH_REPAYSCH.EXINTCOMP_RECD|| '~' ||
   LEA_DWH_REPAYSCH.BILLFLAGE|| '~' ||
   LEA_DWH_REPAYSCH.ADVFLAG|| '~' ||
   to_char(LEA_DWH_REPAYSCH.DUEDATE,'dd/mm/yyyy')|| '~' ||
   to_char(LEA_DWH_REPAYSCH.BILLEDDATE,'dd/mm/yyyy')|| '~' ||
   to_char(LEA_DWH_REPAYSCH.PAYMENTDATE,'dd/mm/yyyy')|| '~' ||
   LEA_DWH_REPAYSCH.OVERDUE_AMT|| '~' ||
   to_char(LEA_DWH_REPAYSCH.OVERDUE_DATE,'dd/mm/yyyy') 
   from FIN_LEA.LEA_DWH_REPAYSCH;
   spool off;
  

*Query execution plan is Plan hash value: 2170819202

------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |   192 |     4  (25)| 00:00:01
|   1 | TABLE ACCESS FULL | LEA_DWH_REPAYSCH |     1 |   192 |     4  (25)| 00:00:01
------------------------------------------------------------------------------------

.

---

All those concats || are what's taking the time, yet logically you are just putting ~ between simple column values. To speed it up, select the parts as separate columns and dump to a file, then use a shell script to replace all the column separators with ~. ie

 select 
 LEA_DWH_REPAYSCH.EMI_NUM,
 LEA_DWH_REPAYSCH.FINNESS_ACCT_NUM,
 to_char(LEA_DWH_REPAYSCH.MISDATE,'dd/mm/yyyy'),
 ...

Dump your output to a file (as are probably now doing) and use something like sed, for example (assuming the data is dumped with a comma):

sed -i '' -e 's/,/~/g' /data13/reptest/rep.dat

Even better, change your script to use ~ as the column separator character, which avoids having to use a column separator that might appear in column values (causing data to be corrupted by the sed command)