SQL*net message from client severly impact the Parse call of an insert statement

) it is 2,5 to 3 times slower. I have traced the session on both occasions and reading the book of Carry Milsap I have spotted that the WAIT for SQL*Net message from client is very high for the PARSE call of an INSERT statement, whereas for the test instance there is no delay. I cannot however explain

RE: SQL*net message from client severly impact the Parse call of an insert statement

(dwods) it is 2,5 to 3 times slower. I have traced the session on both occasions and reading the book of Carry Milsap I have spotted that the WAIT for SQL*Net message from client is very high for the PARSE call of an INSERT statement, whereas for the test instance there is no delay. I cannot however

Re: undo and insert

What is ITL ? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 4:59 PM Just the previous version of the changed columns, plus an overhead of about 80 bytes which relates to ITLs, linked lists, operation descriptions

Re: undo and insert

Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: undo and insert What is ITL ? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 4:59 PM

undo and insert

An undo segment is used to save the old value of data. For insert operation, there is no old data to be saved. So, there should be no undo generated. Right? Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337

Re: undo and insert

For insert, in order to rollback, Oracle will still have to get the rowid of the new inserted rows, so that it can rollback when needed. So there will still be undo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 25, 2003 1:49

Re: undo and insert

files generated. Thank youzhu chao [EMAIL PROTECTED] wrote: For insert, in order to rollback, Oracle will still have to get the rowid of the new inserted rows, so that it can rollback when needed.So there will still be undo. Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard

Re: undo and insert

And then there's the previous version of whichever ITL entry gets taken by the transaction doing the insert. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick

Re: undo and insert

Just the previous version of the changed columns, plus an overhead of about 80 bytes which relates to ITLs, linked lists, operation descriptions etc. Bear in mind that undo relating to indexes is not the same as undo relating to tables, though. An update to an indexed column results in one

Re: insert in batch loading

Janne, Thanks very much for your wonderful detail suggestion. I'll definitely use it to analyze the redo. David From: Jan Korecki [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov

insert in batch loading

Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause cannot allocate new log, Checkpoint not complete. All of tables and their indexes

Re: insert in batch loading

How big are yor redo logs? How many o them do you have? On 11/25/2003 10:29:37 AM, David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when

Re: insert in batch loading

David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select from tables through database link. Those jobs run daily. Every time when those jobs run, they cause cannot allocate new log, Checkpoint not complete. All

Re: insert in batch loading

We have 5 groups of redo log. Each group has two members. Each member is 100 MB. David From: Mladen Gogala [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 07:44:33 -0800 How

Re: insert in batch loading

PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 08:19:26 -0800 David Boyd wrote: Hi All, We have some batch loading jobs that truncate the tables first, then insert into the tables as select

Re: insert in batch loading

. The table has two indexes. We don't set them to unusable during inserting. David From: Jan Korecki [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 08:19:26 -0800 David

Re: insert in batch loading

checkpoint? Thanks for your inputs. David From: Mladen Gogala [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: insert in batch loading Date: Tue, 25 Nov 2003 12:49:28 -0800 What does it mean that redo log size dominates checkpoint

Re: insert in batch loading

10 SQL set autotrace on STATISTICS SQL insert /*+ append */ into append_test select * from [EMAIL PROTECTED]; 35605 rows created. Statistics -- 1213732 redo size 35605 rows processed SQL rollback; Rollback complete. SQL alter index

insert/read Blob from a table

Hi , Does anyone have a sample code to insert/update/delete a BLOB into a table from a stored procedure. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Basavaraja, Ravindra INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http

Re: insert and commit 1000 records at a time

I still dont get it... I dont know what I have done to have me confused more than I first asked the question... --- [EMAIL PROTECTED] wrote: My bad. The SQL is not quite right: 'append' is a hint: alter table resource nologging; dont know what nologging does. insert /*+ append

Re: insert and commit 1000 records at a time

Nologging suspends redo log entries generation FOR DIRECT OPERATIONS ONLY! Direct operations do not use SQL, they use Lisp (just kidding). Direct insert preformats blocks and appends blocks as a whole after the highwater mark, without using SQL in the process. On 10/17/2003 02:43:29 PM, MaryAnn

RE: insert and commit 1000 records at a time

asked the question... --- [EMAIL PROTECTED] wrote: My bad. The SQL is not quite right: 'append' is a hint: alter table resource nologging; dont know what nologging does. insert /*+ append */ into resource select * from rqmt; me no understand... me no see 1000 anywhere... Read up

Re: insert and commit 1000 records at a time

MaryAnn, To get back to the original question, use a MOD function on rownum to get your commit point. Check the docs to make sure, but try If mod(rownum, 1000) = 0 then commit; end if; Daniel Fink MaryAnn Atkinson wrote: I still dont get it... I dont know what I have done to have me

Re: insert and commit 1000 records at a time

: 'append' is a hint: alter table resource nologging; dont know what nologging does. insert /*+ append */ into resource select * from rqmt; me no understand... me no see 1000 anywhere... Read up on direct load insert in the concepts manual, along with nologging. And one more

RE: insert and commit 1000 records at a time

IF; Max = tbl_resource_id(tbl_resource_id.MAX) FORALL j IN tbl_resource_id.FIRST .. tbl_resource_id.LAST INSERT INTO RESOURCE VALUES tbl_resource_id(j), tbl_classification(j); DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || sql%Rowcount || ' Rows

Re: insert and commit 1000 records at a time

At 04:44 PM 10/17/2003, you wrote: Got it, thanks to both of you, I really appreciate it. maa MaryAnn, To get back to the original question, use a MOD function on rownum to get your commit point. Check the docs to make sure, but try If mod(rownum, 1000) = 0 then commit; end if; Daniel Fink

insert and commit 1000 records at a time

I have 2 tables, Rqmt and Resource, same structure. I need to take all almost-one-million records from Rqmt and insert them to Resource. So far this worked ok: DECLARE RowCount NUMBER:= 0; BEGIN SELECT Count(*) INTO RowCount FROM RQMT; IF RowCount 0

Re: insert and commit 1000 records at a time

yeah dont commit every 1000 records and do it in one shot. this is going to be much slower. why do you want to do it this way? Ive done 100m inserts with just an insert select and one commit. From: Maryann Atkinson [EMAIL PROTECTED] Date: 2003/10/16 Thu AM 11:54:33 EDT To: Multiple

Re: insert and commit 1000 records at a time

'; SELECT Count(*) INTO RowCount FROM RQMT; IF RowCount 0 THEN INSERT /*+ APPEND */ INTO RESOURCE SELECT Resource_Id, Classification FROM RQMT; RowCount := SQL%RowCount; DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || ' Rows

Re: insert and commit 1000 records at a time

That will work, slowly. You might like to try something like this insert into resource nologging select * from rqmt append; Read up on the 'append' and 'nologging' first. Jared Maryann Atkinson [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/16/2003 08:54 AM Please respond to ORACLE

Re: insert and commit 1000 records at a time

--- [EMAIL PROTECTED] wrote: That will work, slowly. You might like to try something like this insert into resource nologging select * from rqmt append; How's that commiting every 1000 records? Read up on the 'append' and 'nologging' first. ??? Maryann Atkinson

RE: insert and commit 1000 records at a time

log into target database. SQL set long 32000 (or whatever if you have long datatype involved) SQL set arraysize 100 SQL set copycommit 1000 -- LOOKY!! SQL COPY FROM ${REMOTE_LOGIN}/[EMAIL PROTECTED] INSERT ${LOCAL_SCHEMA}.${THE_TABLE} USING ${QUERY}; In this case QUERY will probably be select

Re: insert and commit 1000 records at a time

My bad. The SQL is not quite right: 'append' is a hint: alter table resource nologging; insert /*+ append */ into resource select * from rqmt; Read up on direct load insert in the concepts manual, along with nologging. Bypass the redo and undo - no need for commits. Just back it up when

Re: insert and commit 1000 records at a time

to explain why this rule doesn't make sense. One approach is to consider the amount of rollback - if the row length is (perhaps) 100 bytes then a 1000 row insert is only looking to use about 10k of Rollback... How large is the rollback on your system. Having said that, it's time to answer your

Re: insert and commit 1000 records at a time

SESSION ENABLE PARALLEL DML'; SELECT Count(*) INTO RowCount FROM RQMT; IF RowCount 0 THEN INSERT /*+ APPEND */ INTO RESOURCE SELECT Resource_Id, Classification FROM RQMT; RowCount := SQL%RowCount

Re: insert and commit 1000 records at a time

'ALTER SESSION ENABLE PARALLEL DML'; SELECT Count(*) INTO RowCount FROM RQMT; IF RowCount 0 THEN INSERT /*+ APPEND */ INTO RESOURCE SELECT Resource_Id, Classification FROM RQMT; RowCount := SQL%RowCount

Re: Insert performance

r primary keys values to be spread over different value ranges (either by using two sequences, one is "main" seq, other is a "wrapping" cycling from 1 to 1000 for example - the result sequence would be wrapping seq * main seq, thus each insert is going to different part in i

RE: Insert performance

, 2003 11:10 PM To: Multiple recipients of list ORACLE-L Subject: Re: Insert performance At 09:44 PM 9/22/2003, you wrote: Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? Is the sequence cache set

RE: Insert performance

, September 23, 2003 4:05 PM To: Multiple recipients of list ORACLE-L Subject: RE: Insert performance I have already bumped that up quite a bit as well, but I still get many waits. Thanks, Rick Stephenson Oracle Database Administrator Ovid Technologies, Inc. [EMAIL PROTECTED

Insert performance

Does anyone have any idea how to improve performance for multiple inserts into a table that uses a sequence generated primary key? I have approximately 6 concurrent inserts per second into this table which causes the primary key index to become a hot block. This in turn causes "buffer

RE: Insert performance

Rick, I haven't tried this myself but you could consider a reverse key index (depending on your version). That way multiple inserts won't go to the same block. However, (from Perf Tuning 101) you will incur much more IO than a normal index if you do range scans, so you'd need to consider how

Re: Insert performance

Re: Can't insert into partition

Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 6:39 PM We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing

RE: Can't insert into partition

tried adding the USE_HASH hint to the overall INSERT statement, to no effect. Here is the relevant portion of the tkprof output. Thanks again to eveyone. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] INSERT /*+ APPEND use_hash(CJS, PS, APC, MD, C, WV1, WV

RE: Can't insert into partition

? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert

RE: Can't insert into partition

USE_HASH hints on the subqueries. Somewhere I thought I recalled that you could only put hints on the outer SQL statement -- is that true? So we tried adding the USE_HASH hint to the overall INSERT statement, to no effect. Here is the relevant portion of the tkprof output. Thanks again to eveyone. Dennis

RE: Can't insert into partition

Not certain that this is the case, but could it be that statistics are missing for one partition, thus occulting (as in 'undefined and something is undefined') statistics for the other partitions and the table ? Not necessarily for the table you are trying to insert into. Not sure

RE: Can't insert into partition

But then it's not the same sql anymore and the access plan can be wildly different. You need to use bind variables in your sqlplus session as well. Unfortunately, even then it is not guaranteed that you'll get the same plan as you get in the plsql proc. At 06:44 AM 8/7/2003 -0800, you wrote:

Re: Can't insert into partition

? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL

RE: Can't insert into partition

You are welcome. Happens a lot. You see what should be there rather than what IS there and wonder why it's not working as designed. Someone else, uninvolved, comes along, takes one look at the thing, points out the error and leaves you (me) feeling like an idiot. At 12:54 PM 8/8/2003 -0800,

RE: Can't insert into partition

To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours

RE: Can't insert into partition

the table which probably would have accomplished nothing. [...] SQL explain plan for 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING 3 ( bunch of columns ) 21SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID

RE: Can't insert into partition

CBO to consider HASH JOIN? The original query had USE_HASH hints on the subqueries. Somewhere I thought I recalled that you could only put hints on the outer SQL statement -- is that true? So we tried adding the USE_HASH hint to the overall INSERT statement, to no effect. Here is the relevant

RE: Can't insert into partition

would have accomplished nothing. [...] SQL explain plan for 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING 3 ( bunch of columns ) 21SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, 23 CJS.MARKETINGCODE

RE: Can't insert into partition

More information: - Running the insert statement from SQL*Plus works fine. - Normally this is run by executing a stored procedure that is in a package. Specifically, a master procedure calls a series of procedures within the package. The first 5 work fine, then this one doesn't complete. - Next

RE: Can't insert into partition

? So we tried adding the USE_HASH hint to the overall INSERT statement, to no effect. Here is the relevant portion of the tkprof output. Thanks again to eveyone. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] INSERT /*+ APPEND use_hash(CJS, PS, APC, MD, C, WV1, WV

RE: Can't insert into partition

Dennis, Could you plese post the v$session_wait. Do you have a 10046 trace? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned

RE: Can't insert into partition

ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP

RE: Can't insert into partition

plese post the v$session_wait. Do you have a 10046 trace? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process

Re: Maximum Open Cursors on Insert Trigger

, Jay Hostetter wrote: We have a developer that is inserting a large number of records using a VB program. An insert trigger exists on the table. This trigger checks a parent table for records. I know this trigger really is not needed, since a Foreign Key exists to enforce referential

Maximum Open Cursors on Insert Trigger

We have a developer that is inserting a large number of records using a VB program. An insert trigger exists on the table. This trigger checks a parent table for records. I know this trigger really is not needed, since a Foreign Key exists to enforce referential integrity, so I plan

RE: Maximum Open Cursors on Insert Trigger

Title: RE: Maximum Open Cursors on Insert Trigger Just for a more _completeness_ I'd put a curser close statement in the exception clause ... I agree with you that for-update is a no no in this context. Raj

Re: Maximum Open Cursors on Insert Trigger

There are some relevant notes on MetaLink that may be of help. Search on 'visual basic ora-1000' Jared On Monday 28 July 2003 07:54, Jay Hostetter wrote: We have a developer that is inserting a large number of records using a VB program. An insert trigger exists on the table. This trigger

Insert 8000 Byte into LONG column

Hi dear list members, we run a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too

Re: Insert 8000 Byte into LONG column

got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have an idea how I can get the data into the table? Any

Antw: Re: Insert 8000 Byte into LONG column

of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 16, 2003 9:54 AM Hi dear list members, we run a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue

RE: Insert 8000 Byte into LONG column

I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have an idea how I can get

Re: Antw: Re: Insert 8000 Byte into LONG column

a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have

Antw: RE: Insert 8000 Byte into LONG column

run a database 8.1.6.2 I've run into a problem. I've got a table that contains a LONG column (I know, I know). I have to insert a string into it that has a length 8500 Byte. Whenever I issue the insert statement SQL/PLUS throws the error 'ORA-01704: string literal too long'. Does anyone have

RE: Antw: RE: Insert 8000 Byte into LONG column

Guido, Where are getting the data from - other database tables? Because, you could build a PL/SQL block that contains PL/SQL variables that can hold up to 32k of data. You could build the string in the variable and insert it into the LONG column. Something like: declare str_var varchar2

Re: insert stmt disk reads

headers as well. 6) Read the first free block on the list to memory. Insert data. 7) Read the appropriate branch and leaf blocks for each index 8) If space management is required, read the extent map(s) (dictionary or bitmap) As you can see, an insert of a single record can cause many reads

Using the /*+ append */ insert hint

Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic won't have any problems with the limitations this implies. Has anyone had any experience

Re: Using the /*+ append */ insert hint

I can't say in regards to 7.3.x or 8.0.x, but in an 8.1.7.4, I've traced a complete snapshot refresh and seen that Oracle is using an insert /*+ append */. Good, bad, or otherwise, someone at Oracle believes in it. I will say that it is very likely the hint will just be ignored if not supported

RE: Using the /*+ append */ insert hint

From experience, do not use the APPEND hint for singular inserts. You will get tons of wasted space. Only use it for bulk inserts such as INSERT INTO .. SELECT FROM, sqlldr, PL/SQL bulk inserts and the like. Converting from buld inserts without the append hint to bulk inserts with the append hint

RE: Using the /*+ append */ insert hint

-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Connor McDonald Sent: Monday, March 17, 2003 13:34 To: Multiple recipients of list ORACLE-L Subject: Re: Using the /*+ append */ insert hint APPEND came in at 8.0 so it will work there. The hint can

Re: Using the /*+ append */ insert hint

a rethink of your backup strategy. hth connor --- Grant Allen [EMAIL PROTECTED] wrote: Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting - the business logic

RE: Using the /*+ append */ insert hint

-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Toepke, Kevin M Sent: Monday, March 17, 2003 13:24 To: Multiple recipients of list ORACLE-L Subject: RE: Using the /*+ append */ insert hint From experience, do not use the APPEND hint for singular

RE: Using the /*+ append */ insert hint

. Stephane -Original Message- Sent: Monday, March 17, 2003 5:24 AM To: Multiple recipients of list ORACLE-L Hi all, In a discussion with an Oracle rep last week it was suggested we use the /*+ append */ insert hint to allow some inserts to use direct-path. The suggestion is interesting

insert stmt disk reads

In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak

Re: insert stmt disk reads

Partial List Foreign Key Validation Primary Key Validation Reading blocks on the freelist for insert Before Insert/After Insert Triggers -- Daniel W. Fink http://www.optimaldba.com IOUG-A Live! April 27 - May 1, 2003 Orlando, FL Sunday, April 27 8:30am - 4:30pm - Problem Solving

RE: insert stmt disk reads

: insert stmt disk reads In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak

RE: insert stmt disk reads

Constraint validations? RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 3/17/2003 11:22 AM In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak -- Please see the official

Re: insert stmt disk reads

Do you have many indexes on your system ? A table insert often results in index updates; and for large tables with many indexes you usually find that some of the index leaf blocks have to be read from disk. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials

RE: insert stmt disk reads

insert into ... select * from .. -Original Message-From: AK [mailto:[EMAIL PROTECTED]Sent: Monday, March 17, 2003 12:22 PMTo: Multiple recipients of list ORACLE-LSubject: insert stmt disk reads In sqlarea I am finding some insert statements with high disk reads . why

Re: insert stmt disk reads

? RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 3/17/2003 11:22 AM In sqlarea I am finding some insert statements with high disk reads . why would an insert statement will generate disk reads ? Any idea . -ak -- Please see the official ORACLE-L FAQ: http

RE: Using the /*+ append */ insert hint

To: Multiple recipients of list ORACLE-L Subject: Re: Using the /*+ append */ insert hint APPEND came in at 8.0 so it will work there. The hint can be very useful, but it works best with unindexed tables (that are set to NOLOGGING). If tables are indexed, then you still might get some

Re: High current mode buffer gets on insert

I thought I'd repost to see if I could get a response. Anyone? --- Paul Baumgartel [EMAIL PROTECTED] wrote: I'm looking at a client's tkprof output, showing among other things that the insertion of about 135,000 rows taking 450 seconds of CPU, and with current mode buffer gets numbering

High current mode buffer gets on insert

I'm looking at a client's tkprof output, showing among other things that the insertion of about 135,000 rows taking 450 seconds of CPU, and with current mode buffer gets numbering almost 800,000. This is a daily warehouse load process, and I know that indexes are left in place during the load.

RE: slow insert

8.1.7. u could not have any stmts in the FORALL loop. only 1, i.e. insert/update/delete. but with 9i u can have a pl/sql block any dynamic stmts too. moreover, with 9i u can also trap for individual exception whereas with 8.1.7. if there was an error, entire bulk process would rollback. hope

RE: slow insert

is best for u. considering my hardware etc, i got better performance with 5000 fetches at a time. also, till 8.1.7. u could not have any stmts in the FORALL loop. only 1, i.e. insert/update/delete. but with 9i u can have a pl/sql block any dynamic stmts too. moreover, with 9i u can also trap

RE: slow insert

Ignoring bulk insert, and assuming you are performing a single insert statement have you looked at the following (as already suggested): 1) What, and how many, indexes exist on the destination table. Each index requires updating as records are inserted. If there are indexes not required

RE: slow insert

Thanks for the inputs. Here are some important results. 1)The insert into Original table with 43million records takes about 10minutes and the explain plan as attached in the text file original table insert into original table... select ... from a,b where a.col1=b.col1 ---index columns

RE: slow insert

. finally, try playing with the limit clause in the fetch and see what number is best for u. considering my hardware etc, i got better performance with 5000 fetches at a time. also, till 8.1.7. u could not have any stmts in the FORALL loop. only 1, i.e. insert/update/delete. but with 9i u can have a pl

slow insert

hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement

RE: slow insert

ORACLE-L hi, I have an insert statement that will insert about 40 records into a table having 43million records.The values for the insert statement are from a select statement that has a join.This query take about 5-10minutes.What are the ways in which we can speed up this process.the statement

RE: slow insert

! and of course, u surely might have thought of APPEND hint dropping/disabling indexes etc. -Original Message- Ravindra Sent: Friday, February 21, 2003 3:08 PM To: Multiple recipients of list ORACLE-L hi, I have an insert statement that will insert about 40 records into a table having

Re: Index update = Delete + insert ?

, Does a DELETE RE-Insert of the Same Row to the index happen nevertheless ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California

Re: Index update = Delete + insert ?

structure. hth connor --- VIVEK_SHARMA [EMAIL PROTECTED] wrote: Is an index Fields' update actually a DELETE followed by an INSERT of the index row ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network

RE: Index update = Delete + insert ?

Vijay,List When Updating to the Field to the SAME (Previously Existent) Data Value , Does a DELETE RE-Insert of the Same Row to the index happen nevertheless ? Thanks -Original Message- Sent: Wednesday, February 12, 2003 12:50 PM To: VIVEK_SHARMA Hi Vivek, Index rows are first

INSERT ... RETURNING ROWIDTOCHAR(ROWID) INTO problem on 9.2.0.2.1

on a different server. When we run the insert statement separately, it succeeds. We have not encountered this problem when running the command from developer workstations running 9i release 2 without the patch. It has also worked on the other Oracle versions. We have NT 4.0 SP6. Does anybody have

  1   2   3   4   >