Solved

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

Posted on 2009-04-02
5
381 Views
Last Modified: 2013-12-07
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
Comment
Question by:JelaV
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

by:Milleniumaire
ID: 24048872
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
 
LVL 14

Expert Comment

by:ajexpert
ID: 24052640
You can gather statistics on source and target schema before starting the insert process.
0
 

Author Comment

by:JelaV
ID: 24053531
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
 

Author Comment

by:JelaV
ID: 24066142
Putting the table into NOLOGGING mode helped a lot. seems  that it decrease significantly  the redo logging.
Thanks
0
 
LVL 16

Accepted Solution

by:
Milleniumaire earned 500 total points
ID: 24075255
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
statspack purge automate 7 53
Need a replacement data type in Oracle 6 65
Best RAID for a BDD Oracle 4 62
Export table into csv file in oracle 10 45
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

920 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now