More execution time for spooling....

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 ...
LVL 1
sivaprakasamAsked:
Who is Participating?
 
actonwangConnect With a Mentor Commented:
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
 
fouaddbaCommented:
why ur spooling...?

wht is the purpose?
0
 
sivaprakasamAuthor Commented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
ram_0218Commented:
try doing it parellelly if your db can accomodate the load.

Else use some third party tools to dump it.
0
 
sivaprakasamAuthor Commented:
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
 
actonwangCommented:
>>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
 
sivaprakasamAuthor Commented:
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
 
actonwangCommented:
>>only spooling takes lots of time ...
     how are you doing the spooling now? from a view or a table? using "spool" from sqlplus ? ...
0
 
sivaprakasamAuthor Commented:
i m spooling frm the table ...using the SPOOL command
0
 
sivaprakasamAuthor Commented:
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
 
actonwangCommented:
>> 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
 
johnsoneSenior Oracle DBACommented:
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
 
sivaprakasamAuthor Commented:
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
 
actonwangCommented:
well, you'd post your procedure. Maybe you'd post another question.
0
 
sivaprakasamAuthor Commented:
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
 
actonwangCommented:
post it here then .
0
 
sivaprakasamAuthor Commented:
anyway i can increase the points...
0
 
actonwangCommented:
no problem,we'd like to help here :) I just thought this post is stretched a little bit long.
0
 
sivaprakasamAuthor Commented:
thank u ....so that s the procedure( i explained above) ...is there is anyway to increase the performance ...
0
 
actonwangCommented:
>> 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
 
sivaprakasamAuthor Commented:
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
 
sivaprakasamAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
actonwangCommented:
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
 
sivaprakasamAuthor Commented:
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
 
sivaprakasamAuthor Commented:
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
 
actonwangCommented:
>>.."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
 
actonwangCommented:
>>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
 
sivaprakasamAuthor Commented:
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
 
actonwangCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
sivaprakasamAuthor Commented:
can u give me an example as how my task can be done with this parallel query as i'm  new to this ....
0
 
sivaprakasamAuthor Commented:
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
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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
 
actonwangCommented:
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
 
actonwangCommented:
try truncate table first to see if helps. Parallel query always is a  last thing to look for.
0
 
johnsoneSenior Oracle DBACommented:
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
 
sivaprakasamAuthor Commented:
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
 
sivaprakasamAuthor Commented:
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
 
sivaprakasamAuthor Commented:

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
 
actonwangCommented:
Are they in the same network? Check to see if unix has slow connection to db server?
0
 
sivaprakasamAuthor Commented:
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
 
actonwangCommented:
spooling is through network so it will be a factor. not sp as I pointed.
0
 
johnsoneSenior Oracle DBACommented:
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
 
sivaprakasamAuthor Commented:
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
 
actonwangCommented:
just put it on top of script.

It will show "xxx seconds lasped ... " after each statement.
0
 
actonwangCommented:
how it go?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.