Insert performance is very bad - Page 3
DBAsupport.com 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
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
    Location
    Charlotte, NC
    Posts
    865
    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?

    Code:
    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.


    Thanks,
    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
    Location
    Charlotte, NC
    Posts
    865
    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??

    Thanks,
    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