Insert performance is very bad - Page 3 Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 23 of 23

Thread: Insert performance is very bad

  1. #21
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    Quote Originally Posted by vnktummala View Post
    ... I don't have 10046 trace file.
    This is a job for oradebug -one of the many Oracle unsong super heroes.

    Please try this...

    0- Initiate offending process.

    1- Figure out the SPID of the offending process.

    2- Log into the system as sysdba

    3- Start oradebug...
    oradebug setospid SPID-from_step_1;

    4- Enable 10046...
    oradebug event 10046 trace name context forever, level 12

    5- Wait... wait... keep waiting... then wait a little more

    6- Disable 10046 tracing...
    oradebug event 10046 trace name context off

    7- tkprof trace file and bingo! you got your 10046 trace on remote session.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  2. #22
    Join Date
    Mar 2006
    Charlotte, NC
    Thanks Paul.

    Unfortunately, client has not given the complete DBA privileges on this DB So I can not use oradebug. Even for the trace file I am generating, I need to ask client employee to send me the file, then I am doing tkprof in my laptop.

    well, we can not customize DML queries in Informatica. We can customize the queries reading from source but not the queries writing. At the most I can do is take the risk of putting table in nologging.

    I am very much pity on myself.

    Paul, can you help me to understand the v$segment_statistics better?

    select OBJECT_NAME,SUBOBJECT_NAME,object_type,statistic_name,statistic#,value from v$segment_statistics where obj# in (
    select object_id from dba_objects where object_name='A_IDX') order by object_type,value desc;
    OBJECT_NAME                    SUBOBJECT_ OBJECT_TYPE        STATISTIC_NAME                 STATISTIC#          VALUE
    ------------------------------ ---------- ------------------ ------------------------------ ---------- --------------
    A_IDX             INDEX              space allocated                        15     3757047808
    A_IDX             INDEX              space used                             14     2555428919
    A_IDX             INDEX              logical reads                           0      352393280
    A_IDX             INDEX              db block changes                        3      119409200
    High value means DB is spending that much time to allocate space?

    Also, I would like to update that the 12 hours INSERT job for which I started this thread is completed in two and half hours today after dropping all the Indexes associated. But still I am not happy because it has just inserted 600K rows. I am working with ETL guys to find the ETL side waits.

    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #23
    Join Date
    Mar 2006
    Charlotte, NC
    Quote Originally Posted by PAVB View Post
    As far as I can see wait events on db_file_sequential_read account for about 20% of the total elapsed time therefore even if you manage to get rid of 100% of this particular wait event - which is not possible - you are looking at a performance gain of about 20%

    What are the chances of rewritting this Informatica mapping as a PL/SQL block? - just for testing purposes

    I would write the code without using explicit cursors a.k.a. set-based and, I'll eventually use APPEND hint during insert. On the first try I'll commit only once at the end of the job.
    So is Oracle waiting another approximate 20% for Informatica to push some work??

    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width