Skip to main content

Conventional and Direct-Path Insert

Today I came across one interesting scenario, Where was using Insert statement with hint /*+ APPEND */.
With traditional insert statement we can use it like:

INSERT INTO SHIPMENTLIST (<<Column list>>) VALUES (<<List of Values>>);
INSERT INTO SHIPMENTLIST (<<Column list>>) VALUES (<<List of Values>>);
.
.
INSERT INTO SHIPMENTLIST (<<Column list>>) VALUES (<<List of Values>>);

However when you use same with hint /*+ APPEND */
INSERT /*+ APPEND */INTO SHIPMENTLIST (<<Column list>>) VALUES (<<List of Values >>);
INSERT /*+ APPEND */INTO SHIPMENTLIST (<<Column list>>) VALUES (<<List of Values >>);

Oracle raises run time exception: ORA-12838: cannot read/modify an object after modifying it in parallel.  

This happens because, when we use the Append hint oracle uses direct-path INSERT and Data is written directly into datafiles, bypassing the buffer cache. This reduces generation of redo.
“Conventional and Direct-Path Insert
You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERT and direct-path INSERT. When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-path INSERT, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader.
For more details please refer: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2191836

Solution: Use commit between two successive inserts.

Comments

Post a Comment

Popular posts from this blog

IP – Based Storage Area Network Configuration using iSCSI

Standard configuration options Tool provided by Storage vendors OpenFiler Open source   DOS Manual Setup using iSCSI protocol   Target is the storage server and Initiator is the client.   Target (Ubuntu 12.04) Ø   Install iscsitarget, iscsitarget-source, iscsitarget-dkms package Ø   Format the disk and create the partitions as per your requirements. ( I have 10 GB for OCR &Voting Disk; 61 GB for oracle storage). Ø   edit /etc/default/iscsitarget . Change the default value of ISCSITARGET_ENABLE=false over to ISCSITARGET_ENABLE=true. Ø   Define LUNs in  /etc/iet/ietd.conf       Target iqn.2012-08.in.co.persistent:storage.disk0. pts0012                   LUN 0 Path =/dev/cciss/c0d1p5,Type=fileio,ScsiId=lun0,ScsiSN=lun0 Node: ―         0th LUN is mandatory. ―  ...
1 comment
My Other Post >>

Drop all Objects from Schema In Postgres

To Drop all objects from Postgres Schema there could be following two approaches: Drop Schema with cascade all and re-create it again.  In some cases where you dont want to/not allowed to drop and recreate schema, its easy to look for objects on current schema and drop them. following script would help to do so, Create function which would do the task and then drop that function too. --- CREATE OR REPLACE FUNCTION drop_DB_objects() RETURNS VOID AS $$ DECLARE  rd_object RECORD; v_idx_statement VARCHAR(500);   BEGIN ---1. Dropping all stored functions RAISE NOTICE '%', 'Dropping all stored functions...'; FOR rd_object IN ( SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) as functionDef     FROM pg_proc p     INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)    WHERE ns.nspname = current_schema      AND p.proname <...
2 comments
My Other Post >>

Distributed transaction in Oracle ( Over Oracle DBLink)

To fetch the data from one server to and other Oracle server over DBLink, I experienced the following facts of Oracle Distributed transactions: Security issue: -           We cannot create Public synonym for the remote object accessed over Private DBLink of other Database.   -           It’s allowed to create private synonym for remote object, but you cannot grant the access over this synonym to any other schema. If you try to provide the grants to other schema Oracle raises an error:              [ORA-02021: DDL operations are not allowed on a remote database] “In an all you can access remote objects over private DBLink in the same schema where DBLink is created”. Fetching the Ref Cursor at Remote site:                   Let’s say we have two site...
1 comment
My Other Post >>