Introduction to Oracle Trace Utulity and Understanding The Fundamental Performance Equation

So we are as developers look for how to write the application which will use only the database resources necessary to return the result(s) as quickly as possible.And method here to achive this goal is accessing the least number of blocks and reducing and eliminating waits in Oracle. When it comes to waits it is a new domain, I believe this Fundamental Performance Equation can help understanding;

Response Time = Service Time + Wait Time

Simply “Service Time” is time spent on the CPU and “Wait Time” is non-idle time spent away from the CPU waiting for an event to complete or a resource to become available.

Oracle trace facility is with us for a long time, I remember doing my first SQL Trace for Oracle support without knowing how valuable information I generated. After some years SQL Trace has become very dominant in proactive tuning process during development and test steps. With 9i also wait option is included to Tkprof. Below is a simple template you many use for session based tuning needs.

-- it is better to make a new connection before you start a trace each time, if not information
-- will be appended if any trace has already been started

conn hr/hr

-- Set these initialization parameters for your trace session to guarantee the integrity of the trace file

alter session set max_dump_file_size=unlimited;
ALTER session SET timed_statistics = true;
alter session set STATISTICS_LEVEL = ALL ;
alter session set “_rowsource_execution_statistics” = true

-- in order to seperate your produced trace file easily from the others at user_dump_dest folder of Oracle
alter session set tracefile_identifier = SQL_Trace ;

-- also if you are after 10g you can produce session level ASH reports by the help of CLIENT_ID filter
-- call dbms_session.set_identifier('an_identifier') and use this identifier name as a filter

-- start tracing from this session
Alter session set SQL_Trace = true ;
-- ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
-- ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

-- also these can be used for tracing
-- execute DBMS_SESSION.SET_SQL_TRACE ({true|false});
-- execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (session_id, serial_id, {true|false});
-- execute DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046, level, '');
-- for more information; http://www.ubtools.com/ubtools/products/itrprof/itrprof_user_manual.html

-- Run the application that you want to trace, any SQL(s) or any PL/SQL block(s)
select sysdate, user from dual;

-- stop tracing
Alter session set SQL_Trace = false ;
-- ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
-- ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

-- Than go to USER_DUMP_DEST to pick up the trace file. A query that gives your trace file name generated from your session
-- in windows without the tracefile_identifier would be as follows
--
-- select c.value || '\' || d.instance_name || '_ora_' || a.spid || '.trc' trace_file_is_here
-- from v$process a, v$session b, v$parameter c, v$instance d
-- where a.addr = b.paddr
-- and b.audsid = userenv('sessionid')
-- and c.name = 'user_dump_dest' ;
--
-- Format the trace file with TKPROF and interpret the output.
-- $ tkprof tracefile.trc output.txt [set the options as you like]
-- tkprof D:\oraclexee\app\oracle\admin\XE\udump\xe_ora_2220_bind.trc D:\temp\xe_ora_2220_bind.txt explain=hr/hr sys=yes waits=yes sort=prscpu

I want to discuss SQL_TRACE, 10046 Event and 10053 Event as the most popular trace types . By setting different tracefile_identifier for each and uncommenting related start and stop tracing lines in the template you will produce these three different trace files.

Queries are different from other types of SQL statements because, if successful, they return data as results. Whereas other statements simply return success or failure, a query can return one row or thousands of rows. There are three main stages in the processing of a query:
1- Parse
2- Execute
3- Fetch
During the parse stage, the SQL statement is passed from the user process to the server process, and a parsed representation of the SQL statement is loaded into a shared SQL area. During the parse, the server process performs the following functions:
– Searches for an existing copy of the SQL statement in the shared pool
– Validates the SQL statement by checking its syntax
– Performs data dictionary lookups to validate table and column definitions
The execute fetch executes the statement using the best optimizer approach and the fetch retrieves the rows back to the user.

There are two types of parse operations:
1- Hard parsing: A SQL statement is submitted for the first time, and no shareable match is found in the shared pool. Hard parses are the most resource-intensive and unscalable, because they perform all the operations involved in a parse.
2- Soft parsing: A SQL statement is submitted, and a match is found in the shared pool. The match can be the result of a previous execution by another user. The SQL statement is shared, which is good for performance. However, soft parses still require syntax and security checking, which consume system resources.
When bind variables are used properly, more soft parses are possible, thereby reducing hard parses and keeping parsed statements in the library cache for a longer period. This is very important for an Oracle application to be scalable.

The optimizer is the part of the Oracle Database that creates the execution plan for a SQL statement. The determination of the execution plan is an important step in the processing of any SQL statement and can greatly affect execution time. The execution plan is a series of operations that are performed in sequence to execute the statement. The optimizer considers many factors related to the objects referenced and the conditions specified in the query. The information necessary to the optimizer includes:
– Statistics gathered for the system (I/O, CPU, and so on) as well as schema objects (number of rows, index, and so on)
– Information in the dictionary
– WHERE clause qualifiers
– Hints supplied by the developer
When we use diagnostic tools such as Enterprise Manager, EXPLAIN PLAN, and SQL*Plus AUTOTRACE, we can see the execution plan that the optimizer chooses.

The Oracle query optimizer(Cost Based Optimize-CBO) determines the most efficient execution plan and is the most important step in the processing of any SQL statement. The optimizer:
– Evaluates expressions and conditions
– Uses object and system statistics
– Decides how to access the data
– Decides how to join tables
– Decides which path is most efficient

A good understanding of SQL processing is essential for writing optimal SQL statements. These tracing features and Tkprof are free, when used effectively they help to understand what the performance problems are and they directly point the bottlenecks. No need to guess or try and see methods anymore :) Also there are alternative analyzing tools for tkprof like ubtools’ itrprof , web based and produced reports with charts improves understanding.

When you open these trc extentioned files with any text editor you use like notepad you will immediately see that the only difference between SQL_TRACE and 10046 Level 8 trace files are the WAIT lines in 10046 trace file;

Event 10046 Raw Output

10053 Event with 10gR2 version provides very detailed information on how and why Cost Based Optimizer choosed to execute the query.

Continue reading with Part 2 Optimizer Debug Trace Event – 10053 Trace File.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – Understanding SQL Trace and TKPROF
Note:39817.1 – Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output

Advertisement

Cost of Function Call

If you’re calling a routine frequently and that routine contains constants then store them in variables;

set serveroutput on

declare

procedure step_1 is
BEGIN
for i in 1 .. 1000000 loop
if sysdate+i/10000 > TO_DATE(’18/02/1977′, ‘DD/MM/YYYY’) then
null;
end if;
end loop;
end;

procedure step_2 is
l_date_1 date := TO_DATE(’18/02/1977′, ‘DD/MM/YYYY’);
l_date_2 date := sysdate;
BEGIN
for i in 1 .. 1000000 loop
if l_date_2+i/10000 > l_date_1 then
null;
end if;
end loop;
end;

begin
runstats_pkg.rs_start;

step_1;

runstats_pkg.rs_middle;

step_2;

runstats_pkg.rs_stop(1000);
end;
/

Run1 ran in 1047 hsecs
Run2 ran in 147 hsecs
run 1 ran in 712,24% of the time

Name Run1 Run2 Diff
STAT…session pga memory 65,536 0 -65,536

Run1 latches total versus runs — difference and pct
Run1 Run2 Diff Pct
1,032 470 -562 219.57%

PL/SQL procedure successfully completed.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used :
http://asktom.oracle.com/tkyte/runstats.html
http://www.oracledba.co.uk

There are more than one TABLE Type – Part 1

1- Temporary Tables

In addition to permanent(heap) tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.

The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data.

Temporary tables can be indexed, analyzed and you can also create triggers on temporary tables;

DROP TABLE aglbtemptbl PURGE;
CREATE GLOBAL TEMPORARY TABLE aglbtemptbl (
acolumn VARCHAR2(5),
anumber NUMBER,
adate DATE) ;

INSERT INTO aglbtemptbl VALUES (’0123′, 123, SYSDATE);

create index anindex_aglbtemptbl on aglbtemptbl (anumber) ;

analyze table aglbtemptbl compute statistics ;

set autot traceonly explain
SELECT adate FROM aglbtemptbl where anumber = 123 ;
set autot off

Execution Plan
———————————————————-
..
|   1 |  TABLE ACCESS BY INDEX ROWID| AGLBTEMPTBL         |     1 |    22 |1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | ANINDEX_AGLBTEMPTBL |     1 |       |1   (0)| 00:00:01 |
..

So when it is more advantageous to use a permanent table or a global temporary table, lets try this example. Before the comparison we need some grants to be given by sys to the user we will be working with.

conn sys/passwd as sysdba

grant select on v_$statname to hr ;
grant select on v_$mystat to hr ;

conn hr/passwd

CREATE OR REPLACE VIEW redo_size AS
SELECT value
FROM v$mystat, v$statname
WHERE v$mystat.statistic# = v$statname.statistic#
AND v$statname.name = ‘redo size’;

DROP TABLE perm PURGE;
CREATE TABLE perm
( x CHAR(2000) DEFAULT ‘x’,
y CHAR(2000) DEFAULT ‘y’,
z CHAR(2000) DEFAULT ‘z’ );

DROP TABLE temp PURGE;
CREATE GLOBAL TEMPORARY TABLE temp
( x CHAR(2000) DEFAULT ‘x’,
y CHAR(2000) DEFAULT ‘y’,
z CHAR(2000) DEFAULT ‘z’ )
ON COMMIT PRESERVE ROWS;

CREATE OR REPLACE PROCEDURE do_sql (p_sql IN VARCHAR2) AS
l_start_redo NUMBER;
l_redo       NUMBER;
BEGIN
SELECT value
INTO l_start_redo
FROM redo_size;

EXECUTE IMMEDIATE p_sql;

COMMIT;

SELECT value - l_start_redo
INTO l_redo
FROM redo_size;

DBMS_OUTPUT.PUT_LINE( TO_CHAR(l_redo, ‘9,999,999′) || ‘ bytes of redo generated for “‘||
SUBSTR( REPLACE (p_sql, CHR(10), ‘’), 1, 25) || ‘”…’);
END;
/

SET SERVEROUTPUT ON FORMAT WRAPPED
BEGIN
do_sql( ‘INSERT INTO perm
SELECT 1,1,1
FROM all_objects
WHERE ROWNUM <= 500');

do_sql( 'INSERT INTO temp
SELECT 1,1,1
FROM all_objects
WHERE ROWNUM <= 500');

do_sql( 'UPDATE perm SET x = 2');
do_sql( 'UPDATE temp SET x = 2');

do_sql( 'DELETE FROM perm');
do_sql( 'DELETE FROM temp');
END;
/

3,283,452 bytes of redo generated for "INSERT INTO perm         "
66,488 bytes of redo generated for "INSERT INTO temp         "
2,249,824 bytes of redo generated for "UPDATE perm SET x = 2"
1,152,380 bytes of redo generated for "UPDATE temp SET x = 2"
3,215,780 bytes of redo generated for "DELETE FROM perm"
3,213,460 bytes of redo generated for "DELETE FROM temp"

PL/SQL procedure successfully completed.

As a result we may say for insert operations global temporary table usage decreases the redo generated dramatically, where as for delete operations there is a discardable difference. Before using a temporary table you may build up a similar test case and check the amounts of redo for your case.

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used : Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions book by Mr.Thomas Kyte

2- Object Tables

Object-oriented programming is especially suited for building reusable components and complex applications. In PL/SQL, object-oriented programming is based on object types. They let you model real-world objects, separate interfaces and implementation details, and store object-oriented data persistently in the database.

Object types are abstractions of the real-world entities—for example, purchase orders—that application programs deal with. An object type is a schema object with three kinds of components:

– A name, which serves to identify the object type uniquely within that schema
– Attributes, which model the structure and state of the real-world entity. Attributes are built-in types or other user-defined types.
– Methods, which are functions or procedures written in PL/SQL or Java and stored in the database, or written in a language such as C and

stored externally. Methods implement operations the application can perform on the real-world entity.

-- AN OBJECT TYPE EXAMPLE

DROP TABLE sustenance ;
DROP TYPE cake_t ;
DROP TYPE dessert_t ;
DROP TYPE food_t ;
CREATE TYPE food_t AS OBJECT (
-- Attributes
name VARCHAR2(100),
food_group  VARCHAR2 (100),
grown_in    VARCHAR2 (100),

-- Methods
MEMBER PROCEDURE who_are_you ,
MEMBER FUNCTION how_much RETURN INTEGER
);
/

set serveroutput on
DECLARE
-- Create a new object with a constructor
my_favorite_vegetable   food_t
:= food_t ('Brussel Sprouts',
'VEGETABLE',
'Farm,Greenhouse,Backyard');
BEGIN
-- Read an attribute value
DBMS_OUTPUT.put_line (
my_favorite_vegetable.name);

-- Modify an attribute value
my_favorite_vegetable.food_group :=
'SATISFACTION';

IF INSTR (
my_favorite_vegetable.grown_in,
'yard') > 0
THEN
NULL;
--    Pass an object as a parameter
--      order_seeds (my_favorite_vegetable);
END IF;

-- Invoke object type methods using dot notation
-- my_favorite_vegetable.who_are_you ();
END;
/

-- INHERITANCE EXAMPLE; CREATING A SIMPLE OBJECT TYPE HIERARCHY

DROP TYPE food_t ;
CREATE TYPE food_t AS OBJECT (
name VARCHAR2(100),
food_group  VARCHAR2 (100),
grown_in    VARCHAR2 (100))
NOT FINAL;
/
CREATE TYPE dessert_t UNDER food_t (
contains_chocolate    CHAR(1),
year_created          NUMBER(4))
NOT FINAL;
/
CREATE TYPE cake_t UNDER dessert_t (
diameter      NUMBER,
inscription   VARCHAR2(200));
/

-- POPULATE AN OBJECT TABLE

CREATE TABLE sustenance OF food_t;

DECLARE
my_favorite_vegetables food_t :=
food_t ('Brussel Sprouts', 'VEGETABLE', 'farm' );
BEGIN
INSERT INTO sustenance VALUES (my_favorite_vegetables);

INSERT INTO sustenance
VALUES (dessert_t ('Jello', 'PROTEIN', 'bowl', 'N', 1887 ) );

INSERT INTO sustenance
VALUES (cake_t (
'Marzepan Delight', 'CARBOHYDRATE', 'bakery',
'N', 1634, 8, 'Happy Birthday!' ) );
END;
/

select x.name, x.food_group, x.grown_in from sustenance x ;

-- OBJECT TABLES ALSO CAN BE INDEXED

create unique index ui_sustenance_name on sustenance(name);
analyze table sustenance compute statistics for all indexed columns;
set autotrace traceonly
select x.food_group from sustenance x where x.name = 'Jello' ;
set autotrace off

..
|   1 |  TABLE ACCESS BY INDEX ROWID| SUSTENANCE         |     1 |    64 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | UI_SUSTENANCE_NAME |     1 |       |     0   (0)| 00:00:01 |
..

Here is a sample application that provides an overview of how to create and use user-defined datatypes(Oracle Objects). An typical order management application is first developed with the relational model and then with the object-relational model; A Sample Application Using Object-Relational Features

After all these introductions what about the performance comparison with a heap table?

– insert comparison with runstats; object table ~%16 slower
Run1 ran in 568 hsecs
Run2 ran in 490 hsecs
15.92% RESPONCE TIME CHANGE

– storage amount from show_space ; object table ~2 times more

‘RELATIONAL_TABLE’
Total Blocks……………………….320
Total MBytes……………………….5

‘OBJECT_TABLE’
Total Blocks……………………….640
Total MBytes……………………….10

– primary key based read with runstats – nearly the same
Run1 ran in 1560 hsecs
Run2 ran in 1570 hsecs

a) redo details;

drop index ui_sustenance_name;
truncate table sustenance;

DROP TABLE perm_sustenance PURGe ;
CREATE TABLE perm_sustenance (
name VARCHAR2(100),
food_group VARCHAR2 (100),
grown_in VARCHAR2 (100));

SET SERVEROUTPUT ON FORMAT WRAPPED
BEGIN
do_sql( 'INSERT INTO perm_sustenance
SELECT ''Brussel Sprouts'', ''VEGETABLE'', ''farm''
FROM all_objects where rownum < 500');

do_sql( 'INSERT INTO sustenance
SELECT food_t (''Brussel Sprouts'', ''VEGETABLE'', ''farm'')
FROM all_objects where rownum < 500');

do_sql( 'DELETE FROM perm_sustenance');
do_sql( 'DELETE FROM sustenance');
END;
/

22,224 bytes of redo generated for "INSERT INTO perm_sustenan"
99,356 bytes of redo generated for "INSERT INTO sustenanceSEL"
134,268 bytes of redo generated for "DELETE FROM perm_sustenan"
256,760 bytes of redo generated for "DELETE FROM sustenance"

PL/SQL procedure successfully completed.

b) runstats details;

truncate table sustenance;
truncate table perm_sustenance;

set serveroutput on

declare

procedure step_insert_perm is
BEGIN
INSERT INTO perm_sustenance
SELECT 'Brussel Sprouts', 'VEGETABLE', 'farm'
FROM all_objects ;
end;

procedure step_insert_obj is
BEGIN
INSERT INTO sustenance
SELECT food_t ('Brussel Sprouts', 'VEGETABLE', 'farm')
FROM all_objects ;
end;

begin
runstats_pkg.rs_start;

step_insert_perm ;

runstats_pkg.rs_middle;

step_insert_obj ;

runstats_pkg.rs_stop(100000);
end;
/

Run1 ran in 100 hsecs
Run2 ran in 646 hsecs
run 1 ran in 15,48% of the time

Name                                  Run1        Run2        Diff
STAT...session pga memory        1,048,576      65,536    -983,040
STAT...undo change vector size      38,028   1,584,968   1,546,940
STAT...redo size                   553,424   4,704,652   4,151,228

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
101,767     194,786      93,019     52.25%

PL/SQL procedure successfully completed.

c) space usage details;

set serveroutput on

exec show_space(upper('sustenance')) ;

Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               1
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................               2
Full Blocks        .....................              91
Total Blocks............................             104
Total Bytes.............................         851,968
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           2,081
Last Used Block.........................               8

PL/SQL procedure successfully completed.

exec show_space(upper('perm_sustenance')) ;

Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               1
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               4
Full Blocks        .....................              59
Total Blocks............................              72
Total Bytes.............................         589,824
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           1,665
Last Used Block.........................               8

PL/SQL procedure successfully completed.

Before deciding on using object tables on OLTP systems for development with OO features provided you may want to test the performance cost for your application. Besides Oracle is relational, object tables map to relational tables behind :)

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

References Used :
Oracle® Database Application Developer’s Guide – Object-Relational Features 10g Release 2 ( 10.2)
AskTom thread on “Oracle’s Object Relational Model”