Home » RDBMS Server » Performance Tuning » Improving performance of insert statement
Improving performance of insert statement [message #245138] |
Fri, 15 June 2007 05:03 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rolex.mp
Messages: 161 Registered: February 2007
|
Senior Member |
|
|
The time taken for executing the insert is very high .
Let me know if something can be done to improve the performance .
I have mentioned below the table details and query details.
select count(*) from ad_patch_manifest_files;
COUNT(*)
----------
366539
desc ad_patch_manifest_files
Name Null? Type
----------------------------------------- -------- ------------
PATCH_MANIFEST_FILE_ID NOT NULL NUMBER
PATCH_MANIFEST_ID NOT NULL NUMBER
SRC_FILE_APP_SHORT_NAME NOT NULL VARCHAR2(30)
SRC_FILE_SUBDIR NOT NULL VARCHAR2(256)
SRC_FILE_NAME NOT NULL VARCHAR2(128)
FILE_ID NUMBER
FILE_VERSION_ID NUMBER
LANGUAGE VARCHAR2(4)
FILE_TYPE VARCHAR2(30)
SRC_FILE_VERSION_STRING VARCHAR2(150)
SRC_FILE_VERSION RAW(150)
SRC_FILE_TXK_VERSION NUMBER
SRC_FILE_CUSTOM_VERSION NUMBER
SRC_FILE_TRANS_LVL NUMBER
FILE_METADATA SYS.XMLTYPE
BOOTSTRAP_FLAG VARCHAR2(1)
SRC_CHARSET VARCHAR2(30)
BUG_NUM VARCHAR2(30)
CREATION_DATE NOT NULL DATE
LAST_UPDATE_DATE NOT NULL DATE
CREATED_BY NOT NULL NUMBER
LAST_UPDATED_BY NOT NULL NUMBER
There is a normal multi column index on these columns(PATCH_MANIFEST_ID,SRC_FILE_NAME,SRC_FILE_APP_SHORT_NAME,SRC_FILE_SUBDIR)
insert into ad_patch_manifest_files (patch_manifest_file_id,patch_manifest_id,
file_metadata,bootstrap_flag,bug_num,file_type,src_file_app_short_name,
src_file_subdir,src_file_name,src_file_version_string,src_file_txk_version,
src_file_custom_version,src_file_trans_lvl,src_file_version,language,
src_charset,file_id,file_version_id,created_by,creation_date,
last_updated_by,last_update_date)
values
(AD_PATCH_MANIFEST_FILES_S.nextval ,:b1,SYS.XMLTYPE.createXML(:b2:b3),:b4,
:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,AD_TM_UTILS_PVT.Get_Raw(0,:b10,:b11,
:b13,:b12),:b18,:b19,0,0,5,SYSDATE,5,SYSDATE)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 258 1076.59 1330.94 206728 226960 1190985 258258
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 259 1076.59 1330.94 206728 226960 1190985 258258
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 190 (APPS)
|
|
|
|
Goto Forum:
Current Time: Sat Aug 17 04:10:00 CDT 2024
|