Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Unanswered: Spooling using a sql query

    Hi all,
    I am having a performance delay in the sql writing to a file.
    While the query itself starts spooling from the starting of the sql in 20 mins. But the writing of the contents to the file
    takes for ever.

    Is there anything i could do. Si it because i do not have enough CPU time? Of course the data it tries to write is around 21,000,000

    Or changing the query format , does it help spooling faster.


    Thanks in advance for any help that you can provide

    Saratha

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Spooling using a sql query

    Tune the query - this almost certainly has nothing to do with the fact that you are spooling to a file.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    when spooling use silent mode

    sqlplus -s


    post your query and explain plan.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Nov 2003
    Posts
    33
    select
    /*+ INDEX(PM_PREMISE PM_PM_IDX1) */
    eb_reading.premnum,
    a.DEBTORNUM,
    eb_reading.servicenum,
    eb_reading.meternum,
    EB_READING.registernum,
    A2.ext_sdp_code, -- ESI_ID
    A2.PARTICIPANT_CODE,
    to_char(eb_reading.prevdate,'yyyy-mm-dd hh24:mi:ss') prevdate,
    to_char(eb_reading.readdate,'yyyy-mm-dd hh24:mi:ss') readdate,
    abs(eb_reading.reading) reading,
    B_UNITS.UNIT_ABBREV,
    eb_reading.readcode,
    decode(eb_reading.reversed,'Y','C','N','R') reversed,
    to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') datetime
    from
    PM_E_CONSPREM a,
    PM_SDP_ROLE A2,
    pm_svc_deliv_pt B2,
    eb_reading,
    eb_invoice,
    pm_premise,
    pm_division,
    EB_METERMODEL,
    B_UNITS,
    dedw_cust_prem_weekly w
    where
    a.premnum = pm_premise.premnum
    and pm_premise.division_code = pm_division.division_code
    and eb_reading.e_invnum = eb_invoice.e_invnum
    and eb_invoice.debtornum = a.debtornum
    and pm_premise.premnum = B2.ref_no1
    and a.premnum = eb_reading.premnum
    and B2.SDP_CODE = A2.sdp_code
    and A2.participant_role = 'TDSP'
    and pm_division.company_code in ('WTUR','CPLR','DEBS')
    and (eb_reading.readcode != 'M' or eb_reading.readcode is null)
    and ((a.finalreaddate >= '23-DEC-2002' and a.status_cons in ('P','D'))
    OR (a.finalreaddate is NULL and a.status_cons in ('F','C'))
    OR (a.supplyreqdate >= '23-DEC-2002' and a.status_cons in ('D')))
    and a.supplyreqdate = (select max(supplyreqdate) from pm_e_consprem b
    where a.premnum = b.premnum
    AND A.DEBTORNUM = B.DEBTORNUM
    AND b.consnum = (select max(consnum) from pm_e_consprem c
    where a.premnum = c.premnum
    and a.DEBTORNUM = c.DEBTORNUM
    and a.supplyreqdate = c.supplyreqdate
    group by c.premnum,c.debtornum,c.supplyreqdate)
    group by b.premnum,b.debtornum)
    and A2.effective_from = (select max(d.effective_from)
    from pm_sdp_role d where
    A2.sdp_code = d.sdp_code
    and d.participant_role = 'TDSP')
    AND EB_METERMODEL.METERMOD = EB_READING.METERMOD
    AND EB_METERMODEL.UNIT_CODE = B_UNITS.UNIT_CODE
    and a.premnum = w.premnum
    and a.debtornum = w.debtornum




    SELECT STATEMENT, GOAL = CHOOSE 1205 1 181
    FILTER
    NESTED LOOPS 1205 1 181
    NESTED LOOPS 1204 1 174
    NESTED LOOPS 1203 1 166
    NESTED LOOPS 1173 15 2340
    NESTED LOOPS 1165 1 94
    NESTED LOOPS 1161 1 61
    NESTED LOOPS 1158 1 51
    NESTED LOOPS 1157 1 42
    HASH JOIN 1155 1 32
    TABLE ACCESS FULL ENERGYDB PM_E_CONSPREM 1152 621 13662
    INDEX FAST FULL SCAN ENERGYDB DEDW_CUST_WEEKLY_IDX 2 6194 61940
    TABLE ACCESS BY INDEX ROWID ENERGYDB PM_PREMISE 2 1100134 11001340
    INDEX UNIQUE SCAN ENERGYDB PM_PM_IDX1 1 1100134
    TABLE ACCESS BY INDEX ROWID ENERGYDB PM_DIVISION 1 4 36
    INDEX UNIQUE SCAN ENERGYDB PM_DIVISION_IDX1 4
    TABLE ACCESS BY INDEX ROWID ENERGYDB PM_SVC_DELIV_PT 3 2468620 24686200
    INDEX RANGE SCAN ENERGYDB PM_SDP_IDX3 2 2468620
    TABLE ACCESS BY INDEX ROWID ENERGYDB PM_SDP_ROLE 4 2 66
    INDEX RANGE SCAN ENERGYDB SDP_ROLE_IDX1 3 2
    SORT AGGREGATE 1 16
    INDEX RANGE SCAN ENERGYDB SDP_ROLE_IDX1 3 1 16
    PARTITION RANGE ALL
    TABLE ACCESS BY LOCAL INDEX ROWID ENERGYDB EB_READING 8 15172247 940679314
    INDEX RANGE SCAN ENERGYDB EB_RDG_IDX4 7 15172247
    TABLE ACCESS BY INDEX ROWID ENERGYDB EB_INVOICE 2 22038060 220380600
    INDEX UNIQUE SCAN ENERGYDB EINV_IDX1 1 22038060
    TABLE ACCESS BY INDEX ROWID ENERGYDB EB_METERMODEL 1 6 48
    INDEX UNIQUE SCAN ENERGYDB EMMODEL_IDX1 6
    TABLE ACCESS BY INDEX ROWID ENERGYDB B_UNITS 1 15 105
    INDEX UNIQUE SCAN ENERGYDB UNITS_IDX1 15
    SORT GROUP BY NOSORT 3 1 20
    INDEX RANGE SCAN ENERGYDB ECONSP_IDX3 3 1 20
    SORT GROUP BY NOSORT 3 1 20
    INDEX RANGE SCAN ENERGYDB ECONSP_IDX3 3 1 20

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    holy crap ...
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Nov 2003
    Posts
    33
    Could anyone else give any suggestions or help !

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    (you could be using analytics for a lot of those subqueries)

    first crack (edited):
    PHP Code:
    SELECT
        
    /*+ INDEX(PM_PREMISE PM_PM_IDX1) */
        
    eb_reading.premnum,
        
    a.debtornum,
        
    eb_reading.servicenum,
        
    eb_reading.meternum,
        
    eb_reading.registernum,
        
    a2.ext_sdp_code, -- esi_id
        a2
    .participant_code,
        
    TO_CHAR(eb_reading.prevdate,'yyyy-mm-dd hh24:mi:ss'prevdate,
        
    TO_CHAR(eb_reading.readdate,'yyyy-mm-dd hh24:mi:ss'readdate,
        
    ABS(eb_reading.readingreading,
        
    eb_metermodel.unit_code,
        (
    select b_units.unit_abbrev from b_units
          where eb_metermodel
    .unit_code b_units.unit_codeunit_abbrev,
        
    eb_reading.readcode,
        
    DECODE(eb_reading.reversed,'Y','C','N','R'reversed,
        
    TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss'datetime
    FROM
        pm_e_consprem a
    ,
        
    pm_sdp_role a2,
        
    pm_svc_deliv_pt b2,
        
    eb_reading,
        
    eb_metermodel,
        
    b_units
    WHERE
            EXISTS 
    (select NULL from pm_divisionpm_premise
                    where pm_premise
    .division_code pm_division.division_code
                    
    and a.premnum pm_premise.premnum
                    
    and b2.ref_no1 pm_premise.premnum
                    
    and pm_division.company_code IN ('WTUR','CPLR','DEBS'))
        AND 
    EXISTS (select NULL from eb_invoice
                    where eb_reading
    .e_invnum eb_invoice.e_invnum
                    
    and  a.debtornum          eb_invoice.debtornum)
        AND 
    a.premnum eb_reading.premnum
        
    AND b2.SDP_CODE a2.sdp_code
        
    AND a2.participant_role 'TDSP'
        
    AND (eb_reading.readcode != 'M' OR eb_reading.readcode IS NULL)
        AND ((
    a.finalreaddate >= '23-DEC-2002' AND a.status_cons IN ('P','D')) 
                              OR (
    a.finalreaddate IS NULL AND a.status_cons IN ('F','C'))
                              OR (
    a.supplyreqdate >= '23-DEC-2002' AND a.status_cons IN ('D')))
        AND 
    a.supplyreqdate = (SELECT MAX(supplyreqdateFROM pm_e_consprem b
                               WHERE a
    .premnum b.premnum
                               
    AND a.debtornum b.debtornum
                               
    AND b.consnum = (SELECT MAX(consnumFROM pm_e_consprem c
                                                WHERE a
    .premnum c.premnum
                                                
    AND a.debtornum c.debtornum
                                                
    AND a.supplyreqdate c.supplyreqdate
                                                GROUP BY c
    .premnum,c.debtornum,c.supplyreqdate)
                               
    GROUP BY b.premnum,b.debtornum)
        AND 
    A2.effective_from = (SELECT MAX(d.effective_from)
                                 
    FROM pm_sdp_role d 
                                 WHERE A2
    .sdp_code d.sdp_code
                                 
    AND d.participant_role 'TDSP')
        AND 
    eb_metermodel.metermod eb_reading.metermod
        
    AND EXISTS (select null from dedw_cust_prem_weekly w
                    where a
    .premnum w.premnum
                    
    and a.debtornum w.debtornum
    Last edited by The_Duck; 03-03-04 at 14:59.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Nov 2003
    Posts
    33
    Do you suggest, breaking the query into smaller chunks of temp tables ?

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    heck yeah.

    If you already know a few things then create a
    materialized view or a regular view.

    for example:

    create a materialized view of eb_reading, pm_e_consprem
    based on:
    PHP Code:
    select /* or select the columns you need */
    from eb_readingpm_e_consprem a
    where 
      a
    .premnum eb_reading.premnum
      
    AND ((a.finalreaddate >= '23-DEC-2002' AND a.status_cons IN ('P','D')) 
         OR (
    a.finalreaddate IS NULL AND a.status_cons IN ('F','C')) 
         OR (
    a.supplyreqdate >= '23-DEC-2002' AND a.status_cons IN ('D')))
      AND (
    eb_reading.readcode != 'M' OR eb_reading.readcode IS NULL)
      AND 
    EXISTS (select null from dedw_cust_prem_weekly w
                    where a
    .premnum w.premnum
                    
    and a.debtornum w.debtornum
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Nov 2003
    Posts
    33
    Thanks,
    I shall try that.

    I thought since the query starts retrieving the records in 10 min and most of it uses index by rowid, I never bothered to break it up.

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What happens if you remove that hint? It might (only might) be better of not using the index and doing more HASH JOINS. In fact, try using the ALL_ROWS hint instead.

    I'm not generally in favour of splitting queries up into pieces using temp tables. That isn't usually necessary, and it gives the optimizer less options rather than more.

    Not relevant to the performance, but those date literals really should be converted using TO_DATE and a format mask rather than relying on implicit conversions.

  12. #12
    Join Date
    Nov 2003
    Posts
    33
    Hi
    I tried , removing the HInt, and using all rows. Even this tim ethe rows were retrieved in 10 min. But then on it wrote the file only for every 1 min. Sometimes sits there for 2 to 3 min and then writes. Usually i have seen the file size change every second.

    On the Other hand, tried creating 2 temp tables, and created
    indexes and analysed the tables. Spooled using a simple query
    (select * from temp table ) in to a file. Finished in an hour.

    Well, I sure did not understand why it works faster when creating temp tables , Or it that your final query used for spooling simple?

    Even the Other one big query started writing onto a file in less than mins. But writing takes for ever.

    I thought all the rows will be retrieved and stored in buffer before spooling.
    I would appreciate if any one of you can help me in understanding the difference.

    Thanks
    Saratha

  13. #13
    Join Date
    Nov 2003
    Posts
    33
    Hi all,
    Interesting thing is, I created one temp table by supplying the whole big query , using "create table as ". It finished in 15 mins.

    Using a simple select query out of this temp table may help save time.

    Thanks
    Saratha

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    do you use silent mode?

    do you set the arraysize?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Nov 2003
    Posts
    33
    I am using

    set term off;

    I am creating couple of tables and indexes and functions before starting the query, I have to capture the results in a log file. Hence could not use the sqlplus in silent mode.

    But for the query alone i had used set term off;

    What is the value i should set for the arraysize, and what is the benefit> Could you help

    Saratha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •