I the big motivation for the ol' SQL*Plus COPY command
was to improve upon this.
Of course COPY is disappearing as well isn't it?
Cheers
Connor
- Jared Still <jkstill_at_cybcon.com> wrote: > My
apologies if there are multiple copies of this.
>
> Something funky going on with email from work.
>
> --------------------------------
>
> First, the basics:
>
> System Configuration: Sun Microsystems sun4u SUN
> Enterprise 420R (2 X
> UltraSPARC-II 450MHz)
> System clock frequency: 113 MHz
> Memory size: 1024 Megabytes
> ========================= CPUs
> =========================
> Run Ecache CPU CPU
> Brd CPU Module MHz MB Impl. Mask
> --- --- ------- ----- ------ ------ ----
> 0 2 2 450 4.0 US-II 10.0
> 0 3 3 450 4.0 US-II 10.0
>
> The CPU's are about 95% idle. Not much memory
> paging activity.
>
> The disk is (gasp!) a single RAID 5 volume. As this
> is a mostly read
> system, it (usually) doesn't matter.
>
> Oracle is 8.1.7.2
>
> Doing an import into the database with the following
> script:
>
> imp userid=$USERNAME/$PASSWORD@$DB \
> file=/u03/tmp/${OWNER}_dv01.dmp \
> buffer=10485760 \
> fromuser=$OWNER \
> touser=$OWNER \
> ignore=y \
> commit=y \
> constraints=n \
> indexes=n \
> grants=n \
> log=imp_${OWNER}.log
>
>
> Notice that the buffer is 10m and commit=y.
>
> This job is running very slowly. Querying
> v$session_event reveals many
> and
> long waits for log file sync.
>
> TIME AVG
>
> TOTAL TOTAL
> WAITED WAIT
> USERNAME SID EVENT
> WAITS
> TIMEOUTS SECONDS 100ths
> ---------- ----------
> ----------------------------------- --------
> -------- -------- ------
> JKSTILL 12 latch free
> 63 49 0
> 1
> log buffer space
> 4818 2 641
> 13
> log file switch completion
> 9 0 3
> 37
> log file sync
> 628432 6
> 212193 34
> db file sequential read
> 27 0 3
> 10
> file open
> 3 0 0 0
> SQL*Net message to client
> 1257012 0 3 0
> SQL*Net message from client
> 1257012 0
> 211774 17
> SQL*Net more data from client
> 118572 0 9 0
>
>
> 9 rows selected.
>
> Notice that the value for log file sync seems a bit
> high for a session
> that has been connected for a little over 2 hours.
>
> Even so, it does accumulate rapidly. 10 seconds of
> activity garners 8
> seconds of log file sync waits.
>
> This is not a terribly fast system, but it should
> not be this slow.
>
> The following query shows that the average blocks
> per commit is about 4.5.
>
> select blocks_changed, user_commits,
> blocks_changed / user_commits blocks_per_commit
> from (
> select
> stat.value blocks_changed
> from v$sesstat stat, v$statname name, v$session
> sess
> where
> stat.sid = sess.sid
> and stat.statistic# = name.statistic#
> and name.name = 'db block changes'
> and stat.sid = 12
> ) r1,
> (
> select
> stat.value user_commits
> from v$sesstat stat, v$statname name, v$session
> sess
> where
> stat.sid = sess.sid
> and stat.statistic# = name.statistic#
> and name.name = 'user commits'
> and stat.sid = 12
> ) r2
>
>
> With an 8k block, that is about 36k per commit.
> Somewhat less than the
> 10m per commit I expected.
>
> Suspecting that the LONG datatype in some of the
> tables may be the
> culprit, a quick perusal of TFM
> reveals the following regarding the use of the LONG
> datatype with the imp
> utility:
>
> ============================
> The integer specified for BUFFER is the size, in
> bytes, of the buffer
> through which data rows are transferred.
> BUFFER determines the number of rows in the array
> inserted by Import.
> The following formula gives an approximation of the
> buffer size that
> inserts a given array of rows:
> buffer_size = rows_in_array * maximum_row_size
>
> For tables containing LONG, LOB, BFILE, REF, ROWID,
> UROWID, or DATE
> 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.
> ============================
>
> So, the buffer parameter has no effect on tables
> containing columns of the
> type long, lob, bfile, ref, rowid, urowid or date.
>
> This seems rather limiting for such an important
> utility. This applies to
> versions 8.1.7 and 9.2.0
>
> I ran a test to load 90k rows into 2 different
> tables, the only difference
> being that one used a long column for text and
> the other used a varchar2(4000).
>
> The long table took 90 seconds to load with imp and
> committed every 4.5
> blocks.
>
> The varchar2 table took 9 seconds to load and
> committed every 1000 blocks.
>
> This is know doubt old hat to many of you, but it's
> the first time I can
> recall encountering this. Don't
> really use imp too much.
>
> The fact that writes on this system are *so* slow is
> what made it worth
> investigating.
>
> This 1 gig file took 10 hours to load. On our
> speedy linux dev box with
> fast IO it took quite
> awhile, though I'm not going to run it again to get
> the exact timing. And
> that without indexes
> or constraints.
>
> It's sqlloader from now on.
>
> Jared
>
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Jan 14 2004 - 05:19:25 CST