Gathering Data

Gathering Data on the SQL Identified
====================================

If you are most concerned with CPU, then examine the top SQL statements that
performed the most BUFFER_GETS during that interval. Otherwise, start with the SQL
statement that performed the most DISK_READS.

Information to Gather During Tuning
-----------------------------------
The tuning process begins by determining the structure of the underlying tables and
indexes. The information gathered includes the following:

1. Complete SQL text from V$SQLTEXT

2. Structure of the tables referenced in the SQL statement, usually by describing the
table in SQL*Plus

3. Definitions of any indexes (columns, column orderings), and whether the indexes
are unique or non-unique

4. Optimizer statistics for the segments (including the number of rows each table,
selectivity of the index columns), including the date when the segments were last
analyzed

5. Definitions of any views referred to in the SQL statement

6. Repeat steps two, three, and four for any tables referenced in the view definitions found in step five

7. Optimizer plan for the SQL statement (either from EXPLAIN PLAN, V$SQL_PLAN,
or the TKPROF output)

8. Any previous optimizer plans for that SQL statement.

Tuning an Application

Tuning an Application / Reducing Load
-------------------------------------

If your whole application is performing suboptimally, or if you are attempting to reduce the overall CPU or I/O load on the database server, then identifying resource-intensive SQL involves the following steps:

1. Determine which period in the day you would like to examine; typically this is the
application's peak processing time.

2. Gather operating system and Oracle statistics at the beginning and end of that period. The minimum of Oracle statistics gathered should be file I/O (V$FILESTAT), system statistics (V$SYSSTAT), and SQL statistics (V$SQLAREA, V$SQL or V$SQLSTATS, V$SQLTEXT, V$SQL_PLAN, and V$SQL_PLAN_STATISTICS).

3. Using the data collected in step two, identify the SQL statements using the mostresources. A good way to identify candidate SQL statements is to query V$SQLSTATS. V$SQLSTATS contains resource usage information for all SQL statements in the shared pool. The data in V$SQLSTATS should be ordered by resource usage.

The most common resources are:

■ Buffer gets (V$SQLSTATS.BUFFER_GETS, for high CPU using statements)
■ Disk reads (V$SQLSTATS.DISK_READS, for high I/O statements)
■ Sorts (V$SQLSTATS.SORTS, for many sorts)

One method to identify which SQL statements are creating the highest load is to compare the resources used by a SQL statement to the total amount of that resource used in the period. For BUFFER_GETS, divide each SQL statement's BUFFER_GETS by the total number of buffer gets during the period. The total number of buffer gets in the system is available in the V$SYSSTAT table, for the statistic session logical reads.

Similarly, it is possible to apportion the percentage of disk reads a statement performs out of the total disk reads performed by the system by dividing V$SQL_STATS.DISK_READS by the value for the V$SYSSTAT statistic physical reads. The SQL sections of the Automatic Workload Repository report include this data, so you do not need to perform the percentage calculations manually.

After you have identified the candidate SQL statements, the next stage is to gather
information that is necessary to examine the statements and tune them.
Identifying High-Load SQL

TNS easy connection

TNS easy connection
=====================

1. sqlplus islbas/islbas@10.11.201.142/stlbas (if port is default 1521)

2. sqlplus islbas/islbas@10.11.201.142:1522/stlbas


3. connect to a URL using the Easy Connect Syntax

sqlplus islbas/islbas@//10.11.201.142:1521/stlbas

how to insert blob or image file to oracle table

To do apply this example in windows,
first create a os directory like "D:\halim_blob_dir"
and input a image file like 'c.jpg';

then begin

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 3 18:58:02 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL>
SQL> CONN / AS SYSDBA
Connected.
SQL>
SQL> grant dba to halim2 identified by halim2;

Grant succeeded.

SQL> conn halim2/halim2
Connected.
SQL>
SQL> drop directory halim_blob;
drop directory halim_blob
*
ERROR at line 1:
ORA-04043: object HALIM_BLOB does not exist


SQL> create directory halim_blob as 'D:\halim_blob_dir';

Directory created.

SQL> CREATE TABLE image_table (
2 dname VARCHAR2(30), -- directory name
3 sname VARCHAR2(30), -- subdirectory name
4 fname VARCHAR2(30), -- file name
5 iblob BLOB)
6 /

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE halim_load_image_file (
2 dir_name VARCHAR2,
3 sub_dir_name VARCHAR2,
4 file_name VARCHAR2
5 ) IS
6 source_file BFILE;
7 destenation_file BLOB;
8 length_file BINARY_INTEGER;
9 BEGIN
10 source_file := BFILENAME ('HALIM_BLOB', file_name);
11
12 -- insert a NULL record to lock
13 INSERT INTO image_table
14 (dname, sname, fname, iblob
15 )
16 VALUES (dir_name, sub_dir_name, file_name, EMPTY_BLOB ()
17 )
18 RETURNING iblob
19 INTO destenation_file;
20
21 -- lock record
22 SELECT iblob
23 INTO destenation_file
24 FROM image_table
25 WHERE dname = dir_name AND sname = sub_dir_name AND fname = file_nam
26 FOR UPDATE;
27
28 -- open the file
29 DBMS_LOB.fileopen (source_file, DBMS_LOB.file_readonly);
30 -- determine length
31 length_file := DBMS_LOB.getlength (source_file);
32 -- read the file
33 DBMS_LOB.loadfromfile (destenation_file, source_file, length_file);
34
35 -- update the blob field
36 UPDATE image_table
37 SET iblob = destenation_file
38 WHERE dname = dir_name AND sname = sub_dir_name AND fname = file_name;
39
40 -- close file
41 DBMS_LOB.fileclose (source_file);
42 END halim_load_image_file;
43 /

Procedure created.

SQL> EXEC halim_load_image_file('HALIM_BLOB','Image_test','c.jpg');

PL/SQL procedure successfully completed.

SQL> SELECT *
2 FROM image_table;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL>
SQL>
SQL> DECLARE
2 image_file BLOB;
3 BEGIN
4 SELECT iblob
5 INTO image_file
6 FROM image_table;
7
8 DBMS_OUTPUT.put_line (DBMS_LOB.getlength (image_file));
9 END;
10 /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL>
SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 image_file BLOB;
3 BEGIN
4 SELECT iblob
5 INTO image_file
6 FROM image_table;
7 DBMS_OUTPUT.put_line (DBMS_LOB.getlength (image_file));
8* END;
9 /

105542

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>

Bind variable in parameter's list of value

use of Bind variable in parameter's list of value.

1. First need to create a Global Temporary table .
scrift for Global Temporary table .
this table is useful for global user

CREATE GLOBAL TEMPORARY TABLE STGLOBTM
(
COLMN1 NUMBER(18,2),
COLMN2 NUMBER(18,2),
COLMN3 NUMBER(18,2),
COLMN4 NUMBER(18,4),
COLMN5 NUMBER(18,2),
COLMN6 NUMBER(18,2),
COLMN7 NUMBER(18,2),
COLMN8 NUMBER(18,2),
COLMN9 NUMBER(18,2),
COLMN10 NUMBER(18,2),
COLMN11 NUMBER(18,4),
COLMN12 NUMBER(18,2),
COLMN13 NUMBER(18,2),
COLMN14 NUMBER(18,2),
COLMN15 NUMBER(18,2),
COLMC1 VARCHAR2(25 BYTE),
COLMC2 VARCHAR2(25 BYTE),
COLMC3 VARCHAR2(25 BYTE),
COLMC4 VARCHAR2(50 BYTE),
COLMC5 VARCHAR2(50 BYTE),
COLMC6 VARCHAR2(50 BYTE),
COLMC7 VARCHAR2(200 BYTE),
COLMC8 VARCHAR2(200 BYTE),
COLMC9 VARCHAR2(200 BYTE),
COLMC10 VARCHAR2(50 BYTE),
COLMC11 VARCHAR2(50 BYTE),
COLMC12 VARCHAR2(50 BYTE),
COLMC13 VARCHAR2(50 BYTE),
COLMC14 VARCHAR2(50 BYTE),
COLMC15 VARCHAR2(50 BYTE),
COLMD1 DATE,
COLMD2 DATE,
COLMD3 DATE,
COLMD4 DATE,
COLMD5 DATE,
COLMD6 DATE,
COLMD7 DATE,
COLMD8 DATE,
COLMD9 DATE
)
ON COMMIT PRESERVE ROWS
NOCACHE;


2. Before parameter form trigger

to do that first need to write following (example) query
(list of values query using bind variable :p0) in "before parameter form"
trigger in oracle report builder.
--------------------------------

function BeforePForm return boolean is
Begin
srw.do_sql('delete from stglobtm') ;
for i in (select a.lcnumb,b.acttit
from stilcmas a,stfacmas b
where a.brancd = :p0
and a.lcnumb is not null
and a.actype = 'T01'
and a.brancd = b.brancd
and a.actype = b.actype
and a.actnum=b.actnum)
loop
insert into stglobtm (colmc1,colmc7) values (i.lcnumb,i.acttit) ;
end loop ;

Return (TRUE);
End;
----------------------

3.

then write query in the report parameter's list of value
like following example.


select colmc1,colmc7 from stglobtm

Stglobtm is a Global Temporary table .

First need to create a Global Temporary table .
scrift for Global Temporary table .
this table is useful for global user

CREATE GLOBAL TEMPORARY TABLE STGLOBTM
(
COLMN1 NUMBER(18,2),
COLMN2 NUMBER(18,2),
COLMN3 NUMBER(18,2),
COLMN4 NUMBER(18,4),
COLMN5 NUMBER(18,2),
COLMN6 NUMBER(18,2),
COLMN7 NUMBER(18,2),
COLMN8 NUMBER(18,2),
COLMN9 NUMBER(18,2),
COLMN10 NUMBER(18,2),
COLMN11 NUMBER(18,4),
COLMN12 NUMBER(18,2),
COLMN13 NUMBER(18,2),
COLMN14 NUMBER(18,2),
COLMN15 NUMBER(18,2),
COLMC1 VARCHAR2(25 BYTE),
COLMC2 VARCHAR2(25 BYTE),
COLMC3 VARCHAR2(25 BYTE),
COLMC4 VARCHAR2(50 BYTE),
COLMC5 VARCHAR2(50 BYTE),
COLMC6 VARCHAR2(50 BYTE),
COLMC7 VARCHAR2(200 BYTE),
COLMC8 VARCHAR2(200 BYTE),
COLMC9 VARCHAR2(200 BYTE),
COLMC10 VARCHAR2(50 BYTE),
COLMC11 VARCHAR2(50 BYTE),
COLMC12 VARCHAR2(50 BYTE),
COLMC13 VARCHAR2(50 BYTE),
COLMC14 VARCHAR2(50 BYTE),
COLMC15 VARCHAR2(50 BYTE),
COLMD1 DATE,
COLMD2 DATE,
COLMD3 DATE,
COLMD4 DATE,
COLMD5 DATE,
COLMD6 DATE,
COLMD7 DATE,
COLMD8 DATE,
COLMD9 DATE
)
ON COMMIT PRESERVE ROWS
NOCACHE;