• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

how to improve performance in oracle while inserting data selected from an unorganized table/

I have a table of 15 000 000 rows with data inserted i 12 times so it is an unorganized data.
I need to join that table with another one and insert it into the third table which has been truncated. It seems impossible to do it. It takes ages till it inserts about three millions and start complaining  something like 'cant extend segment for 8 in undo tablespace ' I tried to insert data commiting more often but it doesn't solve the problem. However, in the same tablespace in the same project I easily insert 10 millions in one go. I tried setting indexes but it didn't help. Please help :)

The 'tmp' table contains about 15 millions of rows and the 'sset_typ_attr ' is a small table with 10 thousands of rows
0
JelaV
Asked:
JelaV
  • 2
  • 2
1 Solution
 
MilleniumaireCommented:
From your description, it's difficult to know exactly where the problem lies.  Do you know how the tables are being joined?  If so can you provide sample queries and a query plan.

Have you gathered statistics for all tables? What version of Oracle are you using?

Have you tried inserting into the "third" table with nologging to reduce the amount of redo being generated?
0
 
ajexpertCommented:
You can gather statistics on source and target schema before starting the insert process.
0
 
JelaVAuthor Commented:
I'm using oracle oracle 11 and this is my query . I'll try to use nologging table



INSERT /*+ APPEND */ 
INTO txt_attr 
( 
   SELECT  /*+ parallel(ata, 4) parallel(tmp, 4)*/ 
   tmp.a_lyr_typ, 
   tmp.a_id, 
   tmp.a_seq_no, 
   tmp.at_cde, 
   ata.ata_col_no, 
   CASE 
                   WHEN ata.ata_str_flg = 'V' 
                           THEN ' ' 
                   ELSE ata.ata_ecf_fld 
   END, 
   ata.ata_desc, 
   tmp.attr_t_val AS a_exp_t_shrt, 
   CASE 
                   WHEN ata.ata_str_flg = 'V' 
                           THEN tmp.attr_t_val 
                   ELSE ' ' 
   END 
   FROM 
        typ_attr ata 
        INNER JOIN tmp_asst_ex_txt_val tmp ON 
            ( 
                      tmp.at_cde = ata.at_cde 
                      AND tmp.column_no = ata.ata_col_no 
                   ) 
   WHERE 
        ata.ata_actv_flg = 'A' 
        AND ata.ata_typ = 'T' 
) 
; 
COMMIT; 

Open in new window

0
 
JelaVAuthor Commented:
Putting the table into NOLOGGING mode helped a lot. seems  that it decrease significantly  the redo logging.
Thanks
0
 
MilleniumaireCommented:
That's good to hear. Is the insert now performing acceptably or are you still trying to tune it further?  If you need further help can you provide a query plan and some statistics?

Don't forget, because data has been inserted without logging, the txt_attr table will no longer be recoverable using archive logs so, if necessary, you will need to take a backup of the table.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now