Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Procedure taking too much time to complete

Sudhir_MeruApr 1 2014 — edited Apr 2 2014

Hi, 

I am doing a DML action on a table. below are the steps used to perform the action. 

There are two tables IB_SUPPORT_CONTRACTS_MV which has 174435 records and RENEWAL_ASSETS_TEMPS is a Temporary table  

Step 1: Created a Procedure ( I am inserting all the row from IB_SUPPORT_CONTRACTS_MV and Inserting NULL records into below columns )

create or replace PROCEDURE renewal_asset_one AS

  CURSOR s_cur

  IS

SELECT    

    SERIAL_NUMBER,

    NULL SERVICE_START_DATE,

    NULL SERVICE_END_DATE,

    NULL IB_CREATION_DATE,

    NULL AS MIN_SERVICE_START_DATE,

    NULL AS MAX_SERVICE_END_DATE,

    NULL   AS MIN_IB_CREATION_DATE,

    NULL CONTRACT_NUMBER,

    ITEM_DESCRIPTION PRODUCT_ID

    FROM IB_SUPPORT_CONTRACTS_MV

WHERE

LENGTH(SERIAL_NUMBER) > 10 ;

   

TYPE fetch_array

IS

  TABLE OF s_cur%ROWTYPE INDEX BY PLS_INTEGER;

  s_array fetch_array;

BEGIN

 

DELETE FROM RENEWAL_ASSETS_TEMPS;

COMMIT;

  OPEN s_cur;

  

    FETCH s_cur BULK COLLECT INTO s_array;

    FORALL i IN 1..s_array.COUNT

    INSERT INTO RENEWAL_ASSETS_TEMPS VALUES s_array

      (i

      );

   

  CLOSE s_cur;

  COMMIT

RENEWAL_ASSET_TWO();

RENEWAL_ASSET_THREE();

 

END renewal_asset_one;



Step 2:  I am updating table RENEWAL_ASSETS_TEMPS using BULK collect (This procedure is taking lot of time to update)


create or replace PROCEDURE RENEWAL_ASSET_TWO AS

  CURSOR s_cur

  IS

SELECT

    SERIAL_NUMBER,

    MIN(SERVICE_START_DATE) AS MIN_SERVICE_START_DATE,

      MAX(SERVICE_END_DATE) AS MAX_SERVICE_END_DATE,

    MIN(IB_CREATION_DATE)   AS MIN_IB_CREATION_DATE

    FROM IB_SUPPORT_CONTRACTS_MV

    WHERE

    LENGTH(SERIAL_NUMBER) > 10 

    GROUP BY SERIAL_NUMBER ;

   

TYPE fetch_array

IS

  TABLE OF s_cur%ROWTYPE INDEX BY PLS_INTEGER;

  s_array fetch_array;

 

BEGIN

  OPEN s_cur;

  

    FETCH s_cur BULK COLLECT INTO s_array;

    FORALL i IN 1..s_array.COUNT

    

     UPDATE RENEWAL_ASSETS_TEMPS

     SET

     MIN_SERVICE_START_DATE = s_array(i).MIN_SERVICE_START_DATE,

     MAX_SERVICE_END_DATE   = s_array(i).MAX_SERVICE_END_DATE,

     MIN_IB_CREATION_DATE   = s_array(i).MIN_IB_CREATION_DATE

     WHERE

     SERIAL_NUMBER = s_array(i).SERIAL_NUMBER;

   

  CLOSE s_cur;

  COMMIT

 

END;



Step 3: I am doing a final update on same table RENEWAL_ASSETS_TEMPS ( Even this Procedure is taking lot of time to update)


create or replace PROCEDURE RENEWAL_ASSET_THREE AS

CURSOR u_crr

  IS

SELECT

IBV.SERIAL_NUMBER,

RA.MAX_SERVICE_END_DATE,

IBV.CONTRACT_NUMBER

FROM

RENEWAL_ASSETS_TEMPS RA,

IB_SUPPORT_CONTRACTS_MV IBV

WHERE

LENGTH(IBV.SERIAL_NUMBER) > 10 AND

RA.SERIAL_NUMBER = IBV.SERIAL_NUMBER AND

RA.MAX_SERVICE_END_DATE = IBV.SERVICE_END_DATE;

TYPE s_curr

IS

  TABLE OF u_crr%ROWTYPE INDEX BY PLS_INTEGER;

  up_crr s_curr;

BEGIN

  OPEN u_crr;

    FETCH u_crr BULK COLLECT INTO up_crr ;

    FORALL i IN 1..up_crr.count

    UPDATE RENEWAL_ASSETS_TEMPS

    SET CONTRACT_NUMBER        = up_crr(i).CONTRACT_NUMBER

    WHERE SERIAL_NUMBER        = up_crr(i).SERIAL_NUMBER

    AND MAX_SERVICE_END_DATE = up_crr(i).MAX_SERVICE_END_DATE;

  CLOSE u_crr;

  COMMIT;

END;



Please suggest me how to improve performance to update the procedure.


Thanks

Sudhir

Comments

Maran Viswarayar

GOOGLE!!!!!!!

Salman Qureshi

Hello,

Before moving forward, you would need to note following to clear your concepts

1. If you have a request to create a tablespace, you don't need to check existing free space in the database because somehow, you have to create a new tablespace.

2. You can get a query to check free space in the database, but not for all databases in your environment, You would need to run that query in each database individually to find out free space in it.

See following scripts from my blog, these would give you highlight of allocated and free space in your database and some more information as well

Salman - Oracle DBA: Tablespace Growth History and Forecast

http://salmandba.blogspot.sg/2015/01/database-growth-history-and-forecast.html

Salman

salmandba.blogspot.com

trajon

Hi, you need to check your freespace on level tablspaces:


select  a.tablespace_name,

       round(a.bytes_alloc / 1024 / 1024) megs_alloc,

       round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,

       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,

       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,

       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,

       round(maxbytes/1048576) Max,

      c.status, c.contents

from  ( select  f.tablespace_name,

               sum(f.bytes) bytes_alloc,

               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes

        from dba_data_files f

        group by tablespace_name) a,

      ( select  f.tablespace_name,

               sum(f.bytes)  bytes_free

        from dba_free_space f

        group by tablespace_name) b,

      dba_tablespaces c

where a.tablespace_name = b.tablespace_name(+)

and a.tablespace_name = c.tablespace_name

union all

select h.tablespace_name,

       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,

       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576) megs_free,

       round(sum(nvl(p.bytes_used, 0))/ 1048576) megs_used,

       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,

       100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,

       round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes) / 1048576)) max,

      c.status, c.contents

from   sys.v_$TEMP_SPACE_HEADER h,

       sys.v_$Temp_extent_pool p,

       dba_temp_files f,

      dba_tablespaces c

where  p.file_id(+) = h.file_id

and    p.tablespace_name(+) = h.tablespace_name

and    f.file_id = h.file_id

and    f.tablespace_name = h.tablespace_name

and f.tablespace_name = c.tablespace_name

group by h.tablespace_name, c.status, c.contents

ORDER BY 1

good luck...

kt1

HI

Are using ASM??, I use the following at database level.

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,

fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,

(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,

fs.free_space_mb FREE_SPACE_MB,

ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE

FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,

      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB

      FROM dba_data_files

      GROUP BY tablespace_name) df,

     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,

       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB

       FROM dba_free_space

       GROUP BY tablespace_name) fs

WHERE df.tablespace_name = fs.tablespace_name(+)

ORDER BY fs.tablespace_name;

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 30 2014
Added on Apr 1 2014
14 comments
328 views