Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to run a query on a local Oracle DB and output the results to a .CSV file. The query runs in a decent amount of time (usually less than 5 minutes), but returns tens of thousands (possibly over 100,000) records. Upon export, the query runs through and fetches only 500 records at a time, and the whole process takes over an hour (very possibly longer!).

I have already tried some tricks that were not completely helpful (specifically turning off Display all results in grid), so I'm wondering if there's anything else I can do to get my data more quickly?

Here's the explain plan for the query. I only have read access to this db.

Plan hash value: 782492959

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                             |     3 |  1860 |   110   (0)| 00:00:01 |       |       |
|*  1 |  VIEW                                          |                             |     3 |    36 |     2   (0)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS FULL                            | SYS_TEMP_0FD9D7532_BC34A52C |     3 |  1677 |     2   (0)| 00:00:01 |       |       |
|*  3 |  VIEW                                          |                             |     3 |    36 |     2   (0)| 00:00:01 |       |       |
|   4 |   TABLE ACCESS FULL                            | SYS_TEMP_0FD9D7532_BC34A52C |     3 |  1677 |     2   (0)| 00:00:01 |       |       |
|*  5 |  VIEW                                          |                             |     3 |    36 |     2   (0)| 00:00:01 |       |       |
|   6 |   TABLE ACCESS FULL                            | SYS_TEMP_0FD9D7532_BC34A52C |     3 |  1677 |     2   (0)| 00:00:01 |       |       |
|*  7 |  VIEW                                          |                             |     3 |    36 |     2   (0)| 00:00:01 |       |       |
|   8 |   TABLE ACCESS FULL                            | SYS_TEMP_0FD9D7532_BC34A52C |     3 |  1677 |     2   (0)| 00:00:01 |       |       |
|*  9 |  VIEW                                          |                             |     3 |    36 |     2   (0)| 00:00:01 |       |       |
|  10 |   TABLE ACCESS FULL                            | SYS_TEMP_0FD9D7532_BC34A52C |     3 |  1677 |     2   (0)| 00:00:01 |       |       |
|* 11 |  VIEW                                          |                             |     3 |    36 |     2   (0)| 00:00:01 |       |       |
|  12 |   TABLE ACCESS FULL                            | SYS_TEMP_0FD9D7532_BC34A52C |     3 |  1677 |     2   (0)| 00:00:01 |       |       |
|* 13 |  VIEW                                          |                             |     3 |    36 |     2   (0)| 00:00:01 |       |       |
|  14 |   TABLE ACCESS FULL                            | SYS_TEMP_0FD9D7532_BC34A52C |     3 |  1677 |     2   (0)| 00:00:01 |       |       |
|* 15 |  VIEW                                          |                             |     3 |    36 |     2   (0)| 00:00:01 |       |       |
|  16 |   TABLE ACCESS FULL                            | SYS_TEMP_0FD9D7532_BC34A52C |     3 |  1677 |     2   (0)| 00:00:01 |       |       |
|  17 |  TEMP TABLE TRANSFORMATION                     |                             |       |       |            |          |       |       |
|  18 |   LOAD AS SELECT                               | SYS_TEMP_0FD9D7532_BC34A52C |       |       |            |          |       |       |
|  19 |    WINDOW SORT                                 |                             |     3 |  1677 |   106   (0)| 00:00:01 |       |       |
|  20 |     VIEW                                       |                             |     3 |  1677 |   106   (0)| 00:00:01 |       |       |
|  21 |      VIEW                                      |                             |     3 |  1704 |   106   (0)| 00:00:01 |       |       |
|  22 |       NESTED LOOPS                             |                             |       |       |            |          |       |       |
|  23 |        NESTED LOOPS                            |                             |     3 |  1734 |   106   (0)| 00:00:01 |       |       |
|  24 |         VIEW                                   |                             |     3 |  1614 |   104   (0)| 00:00:01 |       |       |
|* 25 |          COUNT STOPKEY                         |                             |       |       |            |          |       |       |
|  26 |           VIEW                                 |                             |     3 |  1614 |   104   (0)| 00:00:01 |       |       |
|  27 |            CONCATENATION                       |                             |       |       |            |          |       |       |
|  28 |             PARTITION RANGE SINGLE             |                             |     1 |   514 |    35   (0)| 00:00:01 |    89 |    89 |
|  29 |              PARTITION LIST SINGLE             |                             |     1 |   514 |    35   (0)| 00:00:01 |     1 |     1 |
|* 30 |               TABLE ACCESS BY LOCAL INDEX ROWID| BILL_HDR                    |     1 |   514 |    35   (0)| 00:00:01 |   529 |   529 |
|* 31 |                INDEX RANGE SCAN                | BILL_HDR_IE13               |   305 |       |     1   (0)| 00:00:01 |   529 |   529 |
|  32 |             PARTITION RANGE SINGLE             |                             |     1 |   514 |    35   (0)| 00:00:01 |    89 |    89 |
|  33 |              PARTITION LIST SINGLE             |                             |     1 |   514 |    35   (0)| 00:00:01 |     1 |     1 |
|* 34 |               TABLE ACCESS BY LOCAL INDEX ROWID| BILL_HDR                    |     1 |   514 |    35   (0)| 00:00:01 |   529 |   529 |
|* 35 |                INDEX RANGE SCAN                | BILL_HDR_IE13               |   305 |       |     1   (0)| 00:00:01 |   529 |   529 |
|  36 |             PARTITION RANGE SINGLE             |                             |     1 |   514 |    35   (0)| 00:00:01 |    89 |    89 |
|  37 |              PARTITION LIST SINGLE             |                             |     1 |   514 |    35   (0)| 00:00:01 |     1 |     1 |
|* 38 |               TABLE ACCESS BY LOCAL INDEX ROWID| BILL_HDR                    |     1 |   514 |    35   (0)| 00:00:01 |   529 |   529 |
|* 39 |                INDEX RANGE SCAN                | BILL_HDR_IE13               |   305 |       |     1   (0)| 00:00:01 |   529 |   529 |
|* 40 |         INDEX RANGE SCAN                       | BILL_STATUS_METRIC_IE9      |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 41 |        TABLE ACCESS BY GLOBAL INDEX ROWID      | BILL_STATUS_METRIC          |     1 |    40 |     1   (0)| 00:00:01 |   529 |   529 |
|* 42 |   HASH JOIN OUTER                              |                             |     3 |  1860 |     5  (20)| 00:00:01 |       |       |
|  43 |    VIEW                                        |                             |     3 |  1746 |     2   (0)| 00:00:01 |       |       |
|  44 |     TABLE ACCESS FULL                          | SYS_TEMP_0FD9D7532_BC34A52C |     3 |  1677 |     2   (0)| 00:00:01 |       |       |
|  45 |    VIEW                                        |                             |     3 |   114 |     2   (0)| 00:00:01 |       |       |
|  46 |     TABLE ACCESS FULL                          | SYS_TEMP_0FD9D7532_BC34A52C |     3 |  1677 |     2   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("FNL_C"."SYST_LCTN_ID"='078' AND "FNL_C"."ICN"=:B1)
3 - filter("FNL_C"."SYST_LCTN_ID"='079' AND "FNL_C"."ICN"=:B1)
5 - filter("FNL_C"."SYST_LCTN_ID"='073' AND "FNL_C"."ICN"=:B1)
7 - filter("FNL_C"."SYST_LCTN_ID"='074' AND "FNL_C"."ICN"=:B1)
9 - filter("FNL_C"."SYST_LCTN_ID"='166' AND "FNL_C"."ICN"=:B1)
11 - filter("FNL_C"."SYST_LCTN_ID"='066' AND "FNL_C"."ICN"=:B1)
13 - filter("FNL_C"."SYST_LCTN_ID"='068' AND "FNL_C"."ICN"=:B1)
15 - filter(("FNL_C"."SYST_LCTN_ID"='028' OR "FNL_C"."SYST_LCTN_ID"='031' OR "FNL_C"."SYST_LCTN_ID"='035' OR
"FNL_C"."SYST_LCTN_ID"='037' OR "FNL_C"."SYST_LCTN_ID"='038' OR "FNL_C"."SYST_LCTN_ID"='039' OR "FNL_C"."SYST_LCTN_ID"='174') AND
"FNL_C"."ICN"=:B1)
25 - filter(ROWNUM<=100000)
30 - filter("SRC1"."TTL_CHRG">0 AND "SRC1"."FNLZD_PAID_POSTING_DT"=TO_DATE(' 2013-02-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"SRC1"."BILLREVW_BE_ID"='008')
31 - access("SRC1"."BILL_ORGN_ID" LIKE 'GAL%')
filter("SRC1"."BILL_ORGN_ID" LIKE 'GAL%')
34 - filter("SRC1"."TTL_CHRG">0 AND "SRC1"."FNLZD_PAID_POSTING_DT"=TO_DATE(' 2013-02-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"SRC1"."BILLREVW_BE_ID"='008')
35 - access("SRC1"."BILL_ORGN_ID" LIKE 'GEE%')
filter("SRC1"."BILL_ORGN_ID" LIKE 'GEE%' AND LNNVL("SRC1"."BILL_ORGN_ID" LIKE 'GAL%'))
38 - filter("SRC1"."TTL_CHRG">0 AND "SRC1"."FNLZD_PAID_POSTING_DT"=TO_DATE(' 2013-02-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"SRC1"."BILLREVW_BE_ID"='008')
39 - access("SRC1"."BILL_ORGN_ID" LIKE 'GSA%')
filter("SRC1"."BILL_ORGN_ID" LIKE 'GSA%' AND LNNVL("SRC1"."BILL_ORGN_ID" LIKE 'GEE%') AND LNNVL("SRC1"."BILL_ORGN_ID" LIKE
'GAL%'))
40 - access("SRC2"."DW_BILL_HDR_ROW_ID"="TEMP_B"."LNK")
41 - filter("SRC2"."BILLREVW_BE_ID"='008' AND "SRC2"."FNLZD_PAID_POSTING_DT"=TO_DATE(' 2013-02-15 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "SRC2"."LAST_ACTN_USER" IS NOT NULL)
42 - access("FNL_A"."LNK"="FNL_B"."LNK"(+) AND "FNL_A"."ROW_NUMBER"="FNL_B"."ROW_NUMBER"(+)+1)

UPDATE

I tried improving the query by eliminating certain calculation steps that can be performed after the fact (e.g. Excel - though ideally this should all be done in SQL at the first step, so as to prevent errors later in processing), and while this still takes some time, the export/fetch of all records appears to be running significantly faster.

Perhaps the issue is in my confusion about what is initially displayed in the data grid. Are the records shown here simply an output of the query with an additional constraint added WHERE ROWNUM<=500?

share|improve this question

migrated from superuser.com Feb 22 '13 at 15:17

This question came from our site for computer enthusiasts and power users.

    
Looks like the execution plan of your query is not quite optimal. Please provide more info. Run this first: explain plan for <your query>; select * from table(dbms_xplan.display); And paste results here. –  Štefan Oravec Feb 23 '13 at 7:31
    
Can you just right click the grid and export the dataset to a csv file? –  benRollag Feb 25 '13 at 15:23
    
@ŠtefanOravec I added the explain plan to my question. –  Gaffi Feb 25 '13 at 15:26
    
@benRollag I have tried that. I also tried a select all to try and copy the results, but this also takes too long. The initial fetch that is displayed in the data grid is only 500 records, but takes only a minute to produce. Exporting/Selecting all then tries to fetch all the records, 500 at a time. Watching the screen move, I can see that the first 10-20k are fetched very quickly. After that, it starts to look like the system gets low on RAM, so each subsequent fetch of 500 takes longer and longer. –  Gaffi Feb 25 '13 at 15:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.