Re: Very high 'log file sync' wait time with no 'log file parallel write' wait time

mccmx_at_hotmail.com wrote:
> > "For tables containing LOBs or LONG, BFILE, REF, ROWID,UROWID, or
> > TIMESTAMP columns, rows are inserted individually. The size of the
> > buffer must be large enough to contain the entire row, except for LOB
> > and LONG columns. If the buffer cannot hold the longest row in a table,
> > Import attempts to allocate a larger buffer."
> >
> > Does your table have any of these datatypes?
>
> No,
>
> SQL> select distinct data_type from user_tab_columns where table_name =
>
> 'PS_TL_RPTD_TIME';
>
> DATA_TYPE
> --------------------------------------------------------------------------------
>
> NUMBER
> DATE
> VARCHAR2
I could not dupllicate this in my imp test case (3MB table, buffer of 100K, ~504 byte row size suggesting about 190-200 rows per array insert, 10.2.0.1 on Windows XP Pro).

What I did see is something similar to the following...

-------------execute as the buffer has filled (190

rows)...---------------------------

EXEC
#5:c=10015,e=3722,p=0,cr=23,cu=154,mis=0,r=190,dep=0,og=1,tim=4640523894 WAIT #5: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=4640523957
WAIT #5: nam='SQL*Net message from client' ela= 48 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=4640524028

---------------Recursive commit for the 190 rows above--------------

XCTEND rlbk=0, rd_only=0
WAIT #0: nam='log file sync' ela= 34749 buffer#=2961 p2=0 p3=0 obj#=-1 tim=4640559015
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=4640559091
WAIT #0: nam='SQL*Net message from client' ela= 565 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=4640559684

---------------Start the next 190 row

chunk---------------------------------

I'm not sure if it would help, but the raw trace file may show something that the aggregated tkprof doesn't. What does it look like sequentially? I can't even imagine, because the non-recursive section of your trace shows that the recursive INSERT statement was executed 17 times for 184773 rows, or 10,869 rows per execution, which syncs up with your 20MB buffer and 200 bytes/row. If there is a recursive commit in there for every row, I would like to see that raw trace file.

Regards,

Steve Received on Mon Nov 27 2006 - 09:38:59 CST