-
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
-
do a 10046 level 8 trace, see what the waits are
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|