DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Insert is taking longer time in a Partition of a Table

  1. #1
    Join Date
    May 2002
    Location
    Mumbai, India
    Posts
    27

    Insert is taking longer time in a Partition of a Table

    Hello,
    In our Data Warehouse I'm populating a table MC_FACT after doing some calculation and I'm selecting data
    from a very large fact table called T_FACT ( 450 million ) + Q_FACT (2.5 million).

    Following is the partiton policy for each table.


    MC_FACT has 1 partiton per 3 months. ( 1 day data is 11MB and count is around 110K )
    T_FACT has 1 partiton per every 10 days. ( 1 day data is 150MB and count is around 1.2 million )
    Q_FACT has 1 partiton per year. ( 1 day data is very small and count is around 7K )

    For MC_FACT currently we have 3 tablespaces,
    MCFDATA01 ( <= 31-DEC-2004 ), where still we have 2GB free space.
    MCFDATA02 ( <= 31-DEC-2005 ), where we have 5GB free space.
    MCFINDX01 ( for 8 LOCAL BITMAP indexes ), where still we have 2GB free space.

    When we are running a procedure to load MC_FACT for 1 day (16-dec-2004) it hangs, but initially
    this procedure took 10 Mins to complete for the same date mentioned above. It will insert only 110K
    records into MC_FACT and it is also generating huge undo (35 GB) while executing. When we put a
    commit after 1000 records, it finishes after 55 Mins. But when we run the same procedure for a
    day in JAN-2005 (07-JAN-2005) it took 11 Mins.

    Why this is happening ? Where is the bottleneck ??

    Please give me a solution ...

    Thanks in Advance
    Anomitro

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    do a 10046 level 8 trace, see what the waits are

  3. #3
    Join Date
    May 2002
    Location
    Mumbai, India
    Posts
    27
    Hi,

    I've done the tracing for that Procedure.

    In that trace file there are WAIT #1 to WAIT #8,
    and in most of the WAIT #, the details are

    WAIT #2: nam='db file sequential read' ela= 5860 p1=179 p2=9836 p3=1
    WAIT #2: nam='db file sequential read' ela= 4372 p1=179 p2=10157 p3=1
    WAIT #2: nam='db file sequential read' ela= 8535 p1=179 p2=9914 p3=1
    WAIT #2: nam='db file sequential read' ela= 17017 p1=50 p2=39972 p3=1
    WAIT #2: nam='db file sequential read' ela= 11276 p1=50 p2=39988 p3=1
    WAIT #2: nam='db file sequential read' ela= 10378 p1=50 p2=39960 p3=1
    WAIT #2: nam='db file sequential read' ela= 574 p1=50 p2=39992 p3=1
    WAIT #2: nam='db file sequential read' ela= 574 p1=50 p2=39964 p3=1
    WAIT #2: nam='db file sequential read' ela= 5138 p1=50 p2=39976 p3=1

    (Considering all the WAIT # the description
    nam='db file sequential read' is more than 95% of the total WAIT #'s)

    What is this issue? I'm not familiar with this. Please help me in this regard.

    Thanks,
    Anomitro

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You're waiting on an index read. I would look to see if you have a global unique pk or maybe a fk lookup that is not efficient.
    Jeff Hunter

  5. #5
    Join Date
    Jul 2000
    Posts
    521
    Is this a INSERT INTO....SELECT .... FROM...WHERE... type statement ?

    If so, I guess it isn't the INSERT that is taking time. Its the SELECT that is taking more time than you expect.
    svk

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