?
Solved

More execution time for spooling....

Posted on 2006-04-27
47
Medium Priority
?
1,281 Views
Last Modified: 2007-11-27
Hi,

   I have a table with 6 lakhs records ...now i need to do some manipulation and insert these records in to another table and spool that table ....so after manipulations i got around 5lakhs50 thousand records and i need to spool this records to the flat file ...

it takes nearly 1 hour for spooling ....

How can i reduce the spooling time ...can anyone help me plz ...

Thanks in advance ...
0
Comment
Question by:sivaprakasam
  • 21
  • 18
  • 6
  • +2
47 Comments
 
LVL 2

Expert Comment

by:fouaddba
ID: 16555834
why ur spooling...?

wht is the purpose?
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16555910
thats is the requiremet ...this spooled file will be loaded in to another table ....in another server ...

so the data in comma delimited flat file is the requirement.
0
 
LVL 17

Expert Comment

by:ram_0218
ID: 16556001
try doing it parellelly if your db can accomodate the load.

Else use some third party tools to dump it.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 1

Author Comment

by:sivaprakasam
ID: 16556355
no the Stored proc has to be executed before spooling the file ...only those data's are spooled ....

give me some suggestions to reduce the time ...
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16556528
>>no the Stored proc has to be executed before spooling the file
     you sure not that your sp is taking too long? how do you spool the file then?
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16556560
i dont understand this

>>>  you sure not that your sp is taking too long? how do you spool the file then?

i know the execution time of my stored proc ...as i executed it seperately and noted the time ...

only spooling takes lots of time ....
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16556601
>>only spooling takes lots of time ...
     how are you doing the spooling now? from a view or a table? using "spool" from sqlplus ? ...
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16556871
i m spooling frm the table ...using the SPOOL command
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16557087
let me explain in detail ...
i have a sql script to spool the table in the txt file ...

SQL script:
1) declared the parematers
2) intially record frm table2 are deleted
3) executed the procedure (this procedure has a cursor which do some manipulations and insert
the records frm table1 to table2 -- table 1 has 6 lakhs records. so each and every record has to be
checked and some values are changed and send to the table 2)
4) Now this table 2 has to spooled to a text file (i used spool command)

and this takes around 1.30 hrs  to 2 hrs and i dono where its excatly taking more time ...

0
 
LVL 19

Expert Comment

by:actonwang
ID: 16558984
>> executed the procedure
     how long does this part take? If this takes too long, focus on this.
 
    for spool, you might substitue UTL_FILE for it which writes data directly on the file system in the server side.

Acton
0
 
LVL 35

Expert Comment

by:johnsone
ID: 16561748
The reason the spool takes so long is the output to the screen.  You need to eliminate that.

In you SQL script, use "set term off" so the output is not duplicated to the screen.
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16561900
k i ll try with set term off
ya the procedure too takes long time to execute ...

actually  i m using the cursor for loop and inserting some records into the table ...the loop runs for 6 lakh times and for every loop 6 records will be inserted ...is there is any other way to improve the performance of this ....
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16561961
well, you'd post your procedure. Maybe you'd post another question.
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16562021
so should i post a seperate question reg the procedure ...i dont have anymore points ...

but the question i asked includes this too ...

incase if the point i gave is not sufficient may be i can post the question again ...
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16562027
post it here then .
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16562028
anyway i can increase the points...
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16562105
no problem,we'd like to help here :) I just thought this post is stretched a little bit long.
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16562193
thank u ....so that s the procedure( i explained above) ...is there is anyway to increase the performance ...
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16562428
>> i m using the cursor for loop and inserting some records into the table ...the loop runs for 6 lakh times and for every loop 6 records will be inserted ...is there is any other way to improve the performance of this ...

    can you post your sp if it is not too long? how many inserts in this sp? also does this table have a lot of indexes? is this table accessed by a lot of users in the same time?
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16562759
here is the SQL scrpt and the procedure (templates...)

SQL SCRIPT:

/* SQL SCRIPT TO EXTRACT THE DATA TO THE FLAT FILE */

WHENEVER OSERROR EXIT FAILURE;
WHENEVER SQLERROR EXIT FAILURE;

SET LINESIZE 32767;
SET LONG 32767;
SET LONGCHUNKSIZE 500;
SET NEWPAGE NONE;
SET PAGESIZE 0;
SET FEEDBACK OFF;
SET ARRAYSIZE 500;
SET ECHO OFF;
SET HEADING OFF;
SET TAB OFF;
SET VERIFY OFF;
SET TRIMSPOOL ON;
SET COLSEP ,;
SET TRIMS ON;
SET TERM OFF;


Delete from sample_Table2;


exec sample_Proc;



column newdate new_value _dt
column newtext new_value _txt
select to_char(sysdate,'MMDDYYYY') newdate from dual;
select to_char('.txt') newtext from dual;


spool file1_&_dt&_txt
 
select col1||','||col2||','||col3||','||col4||','||col5||','||col6||','||col7||','||col8||','||col9||','||
col10||','||col11||','||col12||','||col13 from sample_Table2;
spool off

exit;


**************************************

HERE IS THE SP /...........

CREATE OR REPLACE PROCEDURE sample_Proc
   IS
   CURSOR cur1 is select col1,col2,col3,col4,col5,
   col6,col7,col8,col9,col10 from Table1;
   V_col11 sample_table2.col11%Type;
   V_col12 sample_table2.col12%Type;
   V_col13 sample_table2.col13%Type;
   V_col14 sample_table2.col14%Type;
   V_col15 sample_table2.col15%Type;
   V_col16 sample_table2.col16%Type;  
   V_col17 varchar2(1) := '0';
   V_col18 varchar2(1) := '1';
   V_col19 Date := sysdate;
   V_col20 varchar2(1) := '1';
   V_col21 varchar2(3) :='xxx';
   BEGIN
   FOR counter In cur1
       LOOP

 /********  THIS IS COMMAN FOR ALL THE SIX INSERTS ***********/

           IF counter.col4=0 THEN V_Transaction_Type := 1;
           ELSIF counter.col4=1 THEN V_Transaction_Type :=2;
           END IF;
              IF counter.col8=0 AND counter.col3 IN ('AAA','BBB') then col13:=0;
           ELSIF counter.col8>0 AND counter.col3 IN ('AAA','BBB') then col13:=1;
           END IF;
         IF counter.col8=0 AND counter.col3 IN ('AAA','BBB') THEN V_Single_Limit:=-1;
           ELSIF  counter.col8>0 AND counter.col3='BBB' THEN V_Single_Limit:=counter.col8;
           ELSIF  counter.col8>0 AND counter.col3='AAA' THEN V_Single_Limit:=counter.col8;
           ELSE V_Single_Limit:='';
           END IF;
              IF counter.col9=0 AND counter.col3 IN ('AAA','BBB') THEN col15:=0;
         ELSIF counter.col9>0 AND counter.col3 IN ('AAA','BBB') THEN col15:=1;

           END IF;
/* ************* FIRST INSERT STMT  *************************/

             IF counter.col4=0 AND (counter.col3 ='AAA' OR counter.col3 ='BBB') AND counter.col6=1 THEN
               V_col11:=3;
                   IF counter.col9=0 THEN col16:=-1;
                 ELSIF counter.col7=3 and counter.col9>0 AND counter.col3='BBB' THEN col16:=counter.col9*0.5;
                 ELSIF counter.col7=3 and counter.col9>0 AND counter.col3='AAA' THEN col16:=counter.col9*0.5;
                   ELSE col16:='';
                   END IF;
               INSERT INTO sample_table2(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13) values
               (counter.col1,counter.col2,V_col17,V_col3,col19,col20,col21,V_col11,V_col12,
               col13,V_col4,col15,V_col16);
           END IF;

%** LIKE FIRST INSERT SIX INSERT STMT REPEATS WITH DIFFERENT CONDITIONS *****************/


     END LOOP;
   EXCEPTION
       WHEN OTHERS then null;
   END sample_Proc;
***************************


0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16562770
no may be 2 users will access it at the same time ..but not alwys the script will run in the server only once a day ....so there is no chance that another user can access it ...

and col1,col2,col5,col6 in table 1 has index ....

0
 
LVL 35

Expert Comment

by:johnsone
ID: 16562897
I would change this:

Delete from sample_Table2;

to

truncate table sample_table2;

That takes care of the commit, is faster than the delete, and needs very little undo.

Quickly looking at your procedure, I do not see a lot of room for improvement.  The cursor has no where clause, so it is doing a full table scan.  The speed of that can be increased using parallel query.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16563199
yup. if you have a large data in your table, truncate would be faster. But be aware that this is DDL and no rollback is permitted.

Also enlarge your DB_CACHE_SIZE if it is small also would increase your performance a bit.
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16563484
basically i dont have any DBA permission none of the commands related to the DBA ...

i couldn't execute ...

also i tried with truncate ....but truncate alwys shows the error ...."ORA -00054 Resource busy and accquire
with NO wait specified"

and to over come this error either i have to kill
teh session which i couldnt able to do as i dont have any DBA privileges...
So i can do it only with delete stmt...

Any comments on this plz suggest me ...

AND FOR THE WHERE CLAUSE ...i dotn have the one as i need to do the full table scan ...bcoz as i
said b4 all the records has to checked ....and the records in table one are unique ...

and this is the prob i m facing ....

So plz suggest something that doesnt need DBA privileges ...
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16563512
And also with the delete stmt sometimes i get the error ...
ORA -01562 - FAILED TO EXTENND ROLL BACK SEGMENT NO.3

the procedure is running fine now in the SQL*PLUS(30 misn) ...but when i try in the UNIX server its takes nearly 2 hrs ..the same table same records and all r time ...but what makes this time difference ...
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16563539
>>.."ORA -00054 Resource busy and accquirewith NO wait specified"
      sb is accessing or using this table so truncate can not execute on it as it is a DDL.

      Also, would you let DBA know your problem because without assistance of your DBA, nothing is nearly possible here.

     
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16563551
>>ORA -01562 - FAILED TO EXTENND ROLL BACK SEGMENT NO.3
     this is due to that you have a large set of data and "delete" will fill out the rollback segment pretty quick. truncate will avoid this anyway.
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16563616
ok i can use the truncate command at the end of spooling script ....but could plz tell me what kind of DBA permission do i need to kill the session ,,,as i can do that b4 issuing the truncate command ,,,

also how the execution times varies  bw UNIX server and SQL*PLUS in windows
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16563765
in order to kill session, you need to issue "alter system kill session ..." so you need to have database privilege on "alter system".

>> how the execution times varies  bw UNIX server and SQL*PLUS in windows
     sp running in server side nothing do with client side. only reason is the pooling. Your unix server might be busier in writing to disk.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 16563882
The ORA-00054 is a little puzzling.  This would mean that another session is using the table, which if this is a temporary table that only the script uses does not make sense.

The ORA-01562 will definitely be solved with a truncatate.

Look into parallel query.  It should help the full table scan.  You do not need any DBA privileges to specify that.  There is a default level of parallel that a DBA can set, however, it can be overridden with a PARALLEL hint.
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16564310
can u give me an example as how my task can be done with this parallel query as i'm  new to this ....
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16564370
i think to work on parallel query i need to set init.ORA parameters....
and for setting init.ora parameters ...do we need DBA privileges  ...

i know that there is a difference bw the  DBA role and DBA priveleges ...

is  it DBA privileges is enough to kill the session and also to set init.ORA parameter or should i have the complete DBA role ....
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 180 total points
ID: 16564424
You need DBA privileges to kill a session.  The specific privilege you need is ALTER SYSTEM.  A DBA is not going to give you this, however they can create a procedure you can run to kill a session.

To use parallel query, there are no special privleges or parameters you need.  There is a parameter called parallel_max_servers which you need to find the setting of, but it is usually set for other reasons.  I always set it in my databases, but you need to use always need a hint to get to it, I do not set default parallel values on tables.

In order to use parallel query, you need to specify the hint:

select /*+ full(table1) parallel(table1 4) */ ...

If you have access to the server, you should see processes names "ora_p000_<SID>" where p000 is the number of the parallel server.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16564491
I am not sure if parallel query is an option here for you as you might need to ask your DBA to set it up for you as well:

beside setting parameters as:

# PARALLEL_MIN_SERVERS
# PARALLEL_MAX_SERVERS

# PARALLEL_AUTOMATIC_TUNING = TRUE

you also need to run DDL as :

ALTER TABLE TAB_XXX PARALLEL (DEGREE xx);
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16564493
try truncate table first to see if helps. Parallel query always is a  last thing to look for.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 16564550
You do not need to do DDL to alter the table for parallel query.  That only sets the default.  I would recommend the hint instead.
0
 
LVL 19

Accepted Solution

by:
actonwang earned 180 total points
ID: 16564584
that's right but still it has to be set up first.

try "truncate" first.

Also you might need to enlarge your rollback segment if you have many data to be inserted.

for parallel query,  I am not very sure if it could help in your case.
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16564781
Hi johnsone  i m trying out the parallel and will post once its executed ...

it didt any for any parameter setup i just execute the query and its running  ...let me c how long it takes ...

hi actonwang i ll def go for truncate ...but b4 that i should get the DBA permission to kill the session as its giving the same resource busy error ....

i ll go for both the concepts and will paste the results soon ....for truncate i need to wait till monday ....

thanks a lot
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16565241
select /*+ full(table1) parallel(table1 4) */ ...

If you have access to the server, you should see processes names "ora_p000_<SID>" where p000 is the number of the parallel server.

can i optionally use 4 for the servers as in the code...or should check the p000 and use it ...plz explain ...
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16565382

I TRED THE PARALLEL QUERYWITH THE NO 4 (AS U GAVE IN THE EXAMPLE) ITS FASTER IN SQL PLUS BUT AGAIN IT TAKES THE SAME 2 HRS IN THE UNIX MACHINE AND I LL ALSO GO FOR THR TRUNCATE OPTION AND CHECK IT ONCE ...

I M SO CONFUSED WHY THE SAME TABLE SAME SCRIPTS SAME DATABASE THE DIFF IN THE EXECUTION TIME BW THE ENVIRONMENT ????????
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16565490
Are they in the same network? Check to see if unix has slow connection to db server?
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16565752
no UNIX server is in the on different network ...i m not sure ...
i ll check it abt the connection ....

plz explain me the issues if its on same network or on diff network ...
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16565765
spooling is through network so it will be a factor. not sp as I pointed.
0
 
LVL 35

Expert Comment

by:johnsone
ID: 16568521
You do not have to look at the server for the ora_p000 processes.  That is something I use to verify that it was actually using parallel query.

I would put "set timing on" at the top of you script.  This should show you the timing of each statement.  Then you can see where the time is being spent.
0
 
LVL 1

Author Comment

by:sivaprakasam
ID: 16573852
where to use the set timing on in my script

its enough to use b4 the exec proc_name

and after the spooling stmt
can show me an example as how t o use it
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16573859
just put it on top of script.

It will show "xxx seconds lasped ... " after each statement.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16579204
how it go?
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question