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
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)
Re: Improving performance of insert statement [message #245145 is a reply to message #245138] Fri, 15 June 2007 05:15 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is not INSERT, it is AD_TM_UTILS_PVT.Get_Raw function.

Regards
Michel
Previous Topic: query tuning
Next Topic: Buffer Busy waits, db file scatteredl read, sequential read
Goto Forum:
  


Current Time: Fri Dec 02 22:41:35 CST 2016

Total time taken to generate the page: 0.05093 seconds