Database Reference
In-Depth Information
All the other statements with the same hash value also had 53740 as the bind variable value
for Bind#9 . The update of SYS.SEQ$ did not contribute significantly to the total response time.
However it was unnecessary overhead and very easy to fix with an ALTER SEQUENCE statement.
Another issue I noticed in the ESQLTRCPROF report was 71 commits.
Statistics:
-----------
COMMITs (read write): 71 -> transactions/sec 12.617
COMMITs (read only): 0
ROLLBACKs (read write): 0
ROLLBACKs (read only): 0
Apparently, each row inserted and LOB loaded was committed separately, adding over-
head. Each commit may cause waiting for the wait event log file sync .
Phase 3—Reproduction
The client agreed to provide the Perl subroutine used for loading LOBs as a stand-alone Perl
program for further investigation on a test system. The DDL was also provided. It showed that
both the LOB and the sequence did not have caching enabled.
CREATE TABLE images(
id number,
date_loaded date,
exif_make varchar2(30),
exif_model varchar2(30),
exif_create_date date,
exif_iso varchar2(30),
exif_f_number varchar2(30),
exif_exposure_time varchar2(30),
exif_35mm_focal_length varchar2(30),
image_data BLOB,
CONSTRAINT images_pk PRIMARY KEY(id)
)
LOB (image_data) STORE AS images_image_data;
CREATE SEQUENCE image_id_seq NOCACHE;
The first thing I did was to instrument the Perl program with the Hotsos instrumentation
library for ORACLE 4 (ILO). Instrumentation with ILO is straight forward. The procedure HOTSOS_
ILO_TASK.BEGIN_TASK is used to start a new task with a certain module and action name. BEGIN_
TASK pushes the previous module and action on a stack as discussed in Chapter 23. The proce-
dure HOTSOS_ILO_TASK.END_TASK terminates a task and restores the previous module and action.
Both module and action are reflected in V$ views such as V$SESSION and V$SQL . To enable SQL
trace as soon as the first task is begun, the package HOTSOS_ILO_TIMER is called by the applica-
tion itself as follows:
ILO is free software and may be downloaded from http://sourceforge.net/projects/hotsos-ilo .
The download package includes documentation in HTML format.
4.
 
Search WWH ::




Custom Search