Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

December 31, 2006

How shared pool works in Oracle and binding example

Filed under: Oracle Concepts and Architecture,Oracle How To — H.Tonguç Yılmaz @ 5:14 pm

In my previous post on Introduction to Oracle Trace Utulity and Understanding The Fundamental Performance Equation I mentioned how a query is executed in Oracle and the importance of using bind variables for the scalability of your applications in Oracle. With this post I want to show how queries shared on a memory area called shared pool in Oracle, for this purpose I will use V$SQLAREA view;


-- reset the shared pool for the demostration, do ot run this at a production system, only maybe with your local XE
alter system flush shared_pool ;

-- lets create a bind variable at sql*plus and bind 100 to it
variable x number;
exec :x := 100 ;

-- lets get first name from employees table for employee_id 100
select first_name from employees where employee_id = :x ;

FIRST_NAME
----------
Steven

-- and check the shared pool for our first query
SELECT a.sql_text, parse_calls, executions FROM v$sqlarea a
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') ;

SQL_TEXT
-------------------------------------------------------

PARSE_CALLS EXECUTIONS
----------- ----------
select first_name from employees where employee_id = :x
          1          1

-- lets bind 110
exec :x := 110 ;

-- and get first name from employees table for employee_id 110
select first_name from employees where employee_id = :x ;

FIRST_NAME
----------
John

-- and check the shared pool for our first query
SELECT a.sql_text, parse_calls, executions FROM v$sqlarea a
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') ;

SQL_TEXT
-------------------------------------------------------

PARSE_CALLS EXECUTIONS
----------- ----------
select first_name from employees where employee_id = :x
          2          2

-- lets bind 110
exec :x := 120 ;

-- carefull this time I changed the query, upper SELECT FROM and WHERE
SELECT first_name FROM employees WHERE employee_id = :x ;

FIRST_NAME
-----------
Matthew

SELECT a.sql_text, parse_calls, executions FROM v$sqlarea a
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') ;

SQL_TEXT
-------------------------------------------------------

PARSE_CALLS EXECUTIONS
----------- ----------
SELECT first_name FROM employees WHERE employee_id = :x
1          1

select first_name from employees where employee_id = :x
2          2

Two important things I wanted to show here;
1- The first query experienced a hard-parse and since the second query was using bind variable and exactly the same query experienced a soft-parse,
2- Using bind variables is not enough to limit hard parsing overhead, you also need to share your application codes. The third query was different from the first two, SELECT FROM and WHERE was written upper. In order to overcome this setting development standarts and favoring package usage is needed, a function called fnc_get_first_name_by_employee_id for everyone.

Also with 10g V$SQL_BIND_CAPTURE view introduced to give the bind variable values;


SELECT DATATYPE_STRING, VALUE_STRING
FROM v$sqlarea a, V$SQL_BIND_CAPTURE b
WHERE upper(a.sql_text) LIKE upper(’%select%emp%employee_id%’) and upper(a.sql_text) NOT LIKE upper('%sqlarea%') and
a.HASH_VALUE = b.HASH_VALUE and b.ADDRESS = a.ADDRESS;

DATATYPE_STRING
----------------
VALUE_STRING
----------------

NUMBER
120

NUMBER
100

To show the cost of no binding I will use two examples, first with pl/sql and second with jdbc. Both will have a loop yo simulate a heavy On-line Transaction Processing(OLTP) system, like 1000 of people executing the same query.

a) JDBC example;


-- noBind JDBC
..
// record the start time
long start_time = System.currentTimeMillis();

// insert the rows
for (int count = 0; count < TOTAL_NUM_ROWS; count++) {
stmt.executeUpdate("insert into perf_test ( id, code, descr, insert_user, insert_date ) " +
"values ( " + Integer.toString( count ) + ", '123456789012345678901234567890', " +
"'12345678901234567890123456789012345678901234567890123456789012345678901234567890', " +
"USER, to_date('" + sdf.format(new java.util.Date(System.currentTimeMillis())) + "', 'YYYYMMDDHH24MISS'))");
}

// record the end time
long end_time = System.currentTimeMillis();

// display the total time taken to insert the rows
System.out.println("Total time for inserting " + TOTAL_NUM_ROWS +
" rows was " + (end_time - start_time) + " milliseconds");

..
-- bind JDBC
..
// create a PreparedStatement object
PreparedStatement myPrepStatement =
myConnection.prepareStatement(
"INSERT INTO perf_test " +
"(id, code, descr, insert_user, insert_date) VALUES "+
"(?, ?, ?, ?, ?)"
);

//SimpleDateFormat sdf = new SimpleDateFormat( "yyyyMMddHHmmss" );
// record the start time
long start_time = System.currentTimeMillis();

// insert the rows
for (int count = 0; count < TOTAL_NUM_ROWS; count++) {
myPrepStatement.setInt(1, count);
myPrepStatement.setString(2, Integer.toString(count));
myPrepStatement.setString(3, "123456789012345678901234567890");
myPrepStatement.setString(4, "12345678901234567890123456789012345678901234567890123456789012345678901234567890");
myPrepStatement.setString(4, "TONGUC");
myPrepStatement.setDate(5, new java.sql.Date(System.currentTimeMillis()));
myPrepStatement.executeUpdate();
}

// record the end time
long end_time = System.currentTimeMillis();

// display the total time taken to insert the rows
System.out.println("Total time for inserting " + TOTAL_NUM_ROWS +
" rows was " + (end_time - start_time) + " milliseconds");
..

With Prepared Statement…
Total time for inserting 2000 rows was 2703 milliseconds

WithOUT Prepared Statement…
Total time for inserting 2000 rows was 4281 milliseconds

b) PL/SQL Example;


set serveroutput on
declare

-- noBind PL/SQL
procedure step_1 is
TYPE rc IS REF CURSOR;
l_rc    rc;
l_dummy all_objects.object_name%TYPE;
l_start NUMBER DEFAULT dbms_utility.get_time;
BEGIN
FOR i IN 1 .. 1000 LOOP
OPEN l_rc FOR 'select object_name
from all_objects
where object_id = ' || i;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
END LOOP;
dbms_output.put_line(round((dbms_utility.get_time - l_start) / 100,
2) || 'seconds...');
END;

-- bind PL/SQL
procedure step_2 is
TYPE rc IS REF CURSOR;
l_rc    rc;
l_dummy all_objects.object_name%TYPE;
l_start NUMBER DEFAULT dbms_utility.get_time;
BEGIN
FOR i IN 1 .. 1000 LOOP
OPEN l_rc FOR 'select object_name
from all_objects
where object_id = :x' USING i;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
END LOOP;
dbms_output.put_line(round((dbms_utility.get_time - l_start) / 100,
2) || 'seconds...');
end;

begin
runstats_pkg.rs_start;

step_1;

runstats_pkg.rs_middle;

step_2;

runstats_pkg.rs_stop(100000);
end;
/

43,88seconds...
,63seconds...
Run1 ran in 4388 hsecs
Run2 ran in 63 hsecs
run 1 ran in 6965,08% of the time

Name                                  Run1        Run2        Diff
LATCH.library cache                187,190       4,273    -182,917
LATCH.row cache objects            513,635      50,094    -463,541
STAT...session pga memory        2,228,224           0  -2,228,224

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
973,738      69,950    -903,788  1,392.05%

PL/SQL procedure successfully completed.

Heavy loaded Oracle OLTP applications must be optimized for two important topics;

1- SQL Sharing,
2- Fast and reliable transaction handling.

Here I have given examples for JDBC and PL/SQL, please check documentation for others like .Net or ODBC. Before closing I recommend you the asktom thread given at references for deeper information and I want to mention three important related topics more;
1- Peeking of User-Defined Bind Variables
2- Caching Session Cursors
3- When to use CURSOR_SHARING

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

Refences Used :
Using the Shared Pool Effectively Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) Chapter 7 Memory Configuration and Use
http://asktom.oracle.com/tkyte/runstats.html
Let’s go deeper with Mr.Thomas Kyte

Advertisements

Oracle Built-in Functions – Part 1

Filed under: Oracle How To — H.Tonguç Yılmaz @ 11:38 am

1- Date Functions

a) ADD_MONTHS; Add Month(s) To A Date


SELECT SYSDATE today, add_months(SYSDATE, 2) "two-months-later" FROM dual ;

TODAY      two-months
---------- ----------
31/12/2006 28/02/2007

SELECT SYSDATE today, add_months(SYSDATE, -2) "two-months-before" FROM dual ;

TODAY      two-months
---------- ----------
31/12/2006 31/10/2006

b) MONTHS_BETWEEN; Returns The Months Separating Two Dates – MONTHS_BETWEEN(, )


-- how-many-months-between-100-days-before-and-200-days-after
SELECT MONTHS_BETWEEN(SYSDATE+200, SYSDATE-100) MONTHS_BETWEEN FROM dual;

MONTHS_BETWEEN
--------------
9,90322581

-- changed the signs
SELECT MONTHS_BETWEEN(SYSDATE-200, SYSDATE+100) MONTHS_BETWEEN FROM dual;

MONTHS_BETWEEN
--------------
-9,8709677

c) EXTRACT; returns the value of a specified datetime field from a datetime or interval value expression. Also can be used for XMLType data,

EXTRACT(XML) is similar to the EXISTSNODE function.


SELECT EXTRACT(YEAR FROM SYSDATE) year, EXTRACT(MONTH FROM SYSDATE) month FROM DUAL;

YEAR      MONTH
---------- ----------
2006         12

2- Case and Decode Functions


WITH airplanes AS (
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE owner = 'SYS' and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE owner = 'HR' and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE owner = 'SYSTEM' and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE object_id > 1000 and ROWNUM < 3
UNION ALL
SELECT object_id line_number,
owner customer
FROM all_objects
WHERE object_id > 10000 and ROWNUM < 3
UNION ALL
SELECT 150000 line_number,
'TONG' customer
FROM dual )
SELECT line_number,
CASE WHEN (line_number BETWEEN 1 AND 1000) THEN 'Part-One'
WHEN (line_number BETWEEN 1001 AND 10000) THEN 'Part-Two'
ELSE 'Other'
END AS Part_Info, customer,
DECODE(customer,
'SYS', 'American Airlines',
'HR', 'Intl. Leasing Corp.',
'SYSTEM', 'Northwest Orient',
'Southwest Airlines') AIRLINE
FROM airplanes ;

LINE_NUMBER PART_INF CUSTOMER                       AIRLINE
----------- -------- ------------------------------ -------------------
20 Part-One SYS                            American Airlines
44 Part-One SYS                            American Airlines
12088 Other    HR                             Intl. Leasing Corp.
12089 Other    HR                             Intl. Leasing Corp.
2563 Part-Two SYSTEM                         Northwest Orient
2565 Part-Two SYSTEM                         Northwest Orient
1002 Part-Two SYS                            American Airlines
1004 Part-Two SYS                            American Airlines
10086 Other    SYS                            American Airlines
10136 Other    SYS                            American Airlines
150000 Other    TONG                           Southwest Airlines

11 rows selected.

3- NULL functions


DROP TABLE t PURGE ;
CREATE TABLE t (
category  VARCHAR2(20),
outval    NUMBER(3),
inval     NUMBER(3));

INSERT INTO t VALUES ('Groceries', 10, NULL);
INSERT INTO t VALUES ('Payroll', NULL, 100);
INSERT INTO t VALUES ('Groceries', 20, NULL);
INSERT INTO t VALUES ('Payroll', NULL, 200);
INSERT INTO t VALUES ('Groceries', 30, NULL);

SELECT * FROM t;

CATEGORY                 OUTVAL      INVAL
-------------------- ---------- ----------
Groceries                    10
Payroll                                100
Groceries                    20
Payroll                                200
Groceries                    30

a) NVL, if the value is NULL returns given value


select category, nvl(outval, 0), inval fro

CATEGORY             NVL(OUTVAL,0)      INVAL
-------------------- ------------- ----------
Groceries                       10
Payroll                          0        100
Groceries                       20
Payroll                          0        200
Groceries                       30

b) NVL2, Returns First Value if NULL, Second Value if NOT NULL


SELECT category, NVL2(outval, -outval, inval)
FROM t;

CATEGORY             NVL2(OUTVAL,-OUTVAL,INVAL)
-------------------- --------------------------
Groceries                                   -10
Payroll                                     100
Groceries                                   -20
Payroll                                     200
Groceries                                   -30

c) COALESCE, Returns the first non-null value


DROP TABLE test PURGE ;
CREATE TABLE test (
col1  VARCHAR2(1),
col2  VARCHAR2(1),
col3  VARCHAR2(1));

INSERT INTO test VALUES (NULL, 'B', 'C');
INSERT INTO test VALUES ('A', NULL, 'C');
INSERT INTO test VALUES (NULL, NULL, 'C');
INSERT INTO test VALUES ('A', 'B', 'C');

SELECT * FROM test ;

C C C
- - -
B C
A   C
C
A B C

SELECT COALESCE(col1, col2, col3) FROM test;

C
-
B
A
C
A

4- String Functions

a) TRANSLATE, Character Replacement function


-- change "'" with "|"
SELECT TRANSLATE('comma,delimited,list', ',', '|') with_pipe FROM dual;

WITH_PIPE
--------------------
comma|delimited|list

-- In this demo a string is first encrypted then decrypted
SELECT TRANSLATE('this is a secret', 'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv') encrypted , TRANSLATE('p78o 8o 0 o42i4p', '0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz') decrypted FROM   dual;

ENCRYPTED        DECRYPTED
---------------- ----------------
p78o 8o 0 o42i4p this is a secret

-- turkish character example
SELECT TRANSLATE('Aç üş jı', 'ığüşöç', 'igusoc') all_english FROM dual;

ALL_ENGL
--------
Ac us ji

b) REVERSE, returns the reverse of the given string(but an un-supported function)


-- finding palidromes example
CREATE TABLE palidrome_test ( text VARCHAR2(100) );

INSERT ALL
INTO palidrome_test VALUES (’Eat More Bananas’)
INTO palidrome_test VALUES (’A Toyota’’s a Toyota’)
INTO palidrome_test VALUES (’Never odd or even.’)
INTO palidrome_test VALUES (’Some men interpret nine memos.’)
INTO palidrome_test VALUES (’Palindromes are the enemy of productivity’)
SELECT * FROM dual;

SELECT rownum, CASE
WHEN text_filtered = REVERSE(text_filtered)
THEN ‘Yes’
ELSE ‘No’
END AS palindrome
FROM   ( SELECT text, UPPER(REPLACE(TRANSLATE(text,’ ‘’,.:;!?”‘,’ ‘),’ ‘)) AS text_filtered FROM   palidrome_test );

ROWNUM PAL
---------- ---
1 No
2 Yes
3 Yes
4 Yes
5 No

5- Numeric Functions

a) GREATEST and LEAST, Returns the largest and the smallest of multiple values


SELECT GREATEST(9, 67.6, 10) the_great, LEAST(9, 67.6, 10) the_small FROM dual;

THE_GREAT  THE_SMALL
---------- ----------
67,6          9

b) NANVL, Returns Alternate Number If The Value Is Not A Number


CREATE TABLE fpd (
dec_num    NUMBER(10,2),
bin_double BINARY_DOUBLE,
bin_float  BINARY_FLOAT);

INSERT INTO fpd VALUES (0, 'NaN', 'NaN');
COMMIT;

SELECT bin_double, NANVL(bin_double, 0) FROM fpd;

BIN_DOUBLE NANVL(BIN_DOUBLE,0)
---------- -------------------
Nan                   0

SELECT bin_float, NANVL(bin_float, 0) FROM fpd;

BIN_FLOAT NANVL(BIN_FLOAT,0)
---------- ------------------
Nan                  0

6- Other

a) SOUNDEX, Returns Character String Containing The Phonetic Representation Of Another String


SELECT CASE WHEN SOUNDEX('doych') = SOUNDEX('deutch') THEN ‘Match’
ELSE ‘No match’
END comparison1,
CASE WHEN SOUNDEX('pee-air') = SOUNDEX('pierre') THEN ‘Match’
ELSE ‘No match’
END comparison2
FROM DUAL;

COMPARIS COMPA
-------- -----
No match Match

b) VSIZE, returns the Byte Size


SELECT VSIZE('Tonguc') character, VSIZE(5) numb, VSIZE(SYSDATE) dat FROM dual;

CHARACTER       NUMB        DAT
---------- ---------- ----------
6          2          7

– DATE type stored as seven bytes; “century, year, month, day, hour, minute, second”,
– VARCHAR2 type stored as its length,
– NUMBER type uses variable-length scientific notation;
* Oracle stores a number in base 100 format.
* Each byte can store 2 digits.
* One byte is reserved for the exponent.
* When a negative number is stored, an additional byte is required for the sign (-).
* NUMBER(10) and NUMBER will take the same amount of space.
* They will also shrink to the minimum size required to store the contained data.
* The maximum precision for a number is NUMBER(38)


select vsize(1100) from dual;

VSIZE(1100)
-----------
2 /* 2 bytes : Note that the number is stored as (11*10exp2) Thus it requires 1 byte for the two digits (11) and one byte for the exponent(2) */

select vsize(-10.02) from dual;

VSIZE(-10.02)
————-
4 /* 4 bytes : One for sign, 2 for digits(1002) and one for exponent(-2) */

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

References Used :
Complete grouped list of Oracle functions
Oracle® Database SQL Reference 10g Release 2 (10.2)

December 30, 2006

Introduction to Oracle Trace Utulity and Understanding The Fundamental Performance Equation

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 8:30 pm

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

Cost of Function Call

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 3:35 pm

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

Filed under: Oracle Performance — H.Tonguç Yılmaz @ 10:27 am

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”

December 29, 2006

Happy bayram and new year!

Filed under: Other — H.Tonguç Yılmaz @ 1:23 pm

This year our religious “Kurban Bayramı” and the new year celebrations joined :) Tonight we are starting for a five day holiday, great opportunity to rest, think on last year and plan the new coming.

This year different then previous ones, I wish everyone all around earth more peace..

I hope 2007 will be very happy and successfull new year for you :)
Best regards.

Happy new year!


-- another interesting way to wish happy new year :)
drop table twhat_humans_expect purge ;
create table twhat_humans_expect ( vexpectations varchar2(32) primary key ) ;
insert into twhat_humans_expect values ( 'Peace' );
insert into twhat_humans_expect values ( 'Health' );
insert into twhat_humans_expect values ( 'Success' );
insert into twhat_humans_expect values ( 'Money' );
insert into twhat_humans_expect values ( 'New friends' );

drop table tall_friends purge ;
create table tall_friends ( vfriends varchar2(32) primary key ) ;
insert into tall_friends values ( 'My family' );
insert into tall_friends values ( 'TurkcellStaj@yahoogroups.com' );
insert into tall_friends values ( 'oracleturk@yahoogroups.com' );

CREATE OR REPLACE PROCEDURE prc_happy_new_year(p_cursor SYS_REFCURSOR, p_vfriend varchar2) IS
TYPE array_t IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER;
rec_array array_t;
BEGIN
FETCH p_cursor BULK COLLECT
INTO rec_array;
FOR i IN rec_array.FIRST .. rec_array.LAST LOOP
dbms_output.put_line('Wish '||rec_array(i)||' to '||p_vfriend||' @ 2007 :)');
END LOOP;
END prc_happy_new_year;
/

set serveroutput on
DECLARE
rec_array SYS_REFCURSOR;
BEGIN
FOR cur IN (SELECT vfriends FROM tall_friends) LOOP
OPEN rec_array FOR 'SELECT vexpectations FROM twhat_humans_expect';
prc_happy_new_year(rec_array,
cur.vfriends);
CLOSE rec_array;
END LOOP;
END;
/

Wish Peace to My family @ 2007 :)
Wish Health to My family @ 2007 :)
Wish Success to My family @ 2007 :)
Wish Money to My family @ 2007 :)
Wish New friends to My family @ 2007 :)
Wish Peace to TurkcellStaj@yahoogroups.com @ 2007 :)
Wish Health to TurkcellStaj@yahoogroups.com @ 2007 :)
Wish Success to TurkcellStaj@yahoogroups.com @ 2007 :)
Wish Money to TurkcellStaj@yahoogroups.com @ 2007 :)
Wish New friends to TurkcellStaj@yahoogroups.com @ 2007 :)
Wish Peace to oracleturk@yahoogroups.com @ 2007 :)
Wish Health to oracleturk@yahoogroups.com @ 2007 :)
Wish Success to oracleturk@yahoogroups.com @ 2007 :)
Wish Money to oracleturk@yahoogroups.com @ 2007 :)
Wish New friends to oracleturk@yahoogroups.com @ 2007 :)

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

Refences Used : Our manager Mr.Yucel Karadeniz’s celebration email to Turkcell R&D Software Development department;


TYPE Insanoğlu_ne_bekler IS SET {huzur, mutluluk, sağlık, yeni_heyecan, para, takdir, terfi,...},
DECLARE Yeniyıl[1 TO LENGTH RDSD] IS TYPE Insanuğlu_ne_bekler;
DO every_friend = 1 TO LENGTH RDSD;
INITIALIZE Yeniyıl[every_friend] IS VALUE “yeni_heyecan”
END DO;

Expanded Supplied Packages with 10g – Part 2

Filed under: Oracle 10g New Features — H.Tonguç Yılmaz @ 12:58 pm

In Part 1 I mentioned how pl/sql is empowered by new supplied packages and the importance of using them.

Before I proceed with examples I want to mention a great online resource I always use; psoug.org Please check Oracle Built-in Packages and Oracle Built-in Functions lists in library of Mr.Morgan Daniel. I hope to start also another series on Enhanced Built-in Functions soon :)

1- Monitoring batch processes “kindly” :)

So here is one of my favorite packages all time. I use it as much as DBMS_OUTPUT. I first met this package when I needed something for batch process monitoring when I was a DBA. The primary advantage of application info is it involves very minimal redo cost when compared to a heap table you can design for this monitoring purpose. And the results can be immediately seen on v$session columns with simple select statements without any commit need. “kindly” word in the subject comes from these reasons :) Here are some examples;


BEGIN
dbms_application_info.set_module(module_name => 'add_employee',
action_name => 'insert INTO emp');
INSERT INTO emp (ename, empno) VALUES ('TONG', 1234);
dbms_application_info.set_module(NULL, NULL);
END;
/

The following sample query illustrates the use of the MODULE and ACTION column of the V$SQLAREA.


SELECT sql_text
FROM    v$sqlarea
WHERE module = 'add_employee';

SQL_TEXT
-----------------------------------------------------
INSERT INTO EMP (ENAME, EMPNO) VALUES ('TONG', 1234)

You can also read the information via the functions provided;


DBMS_APPLICATION_INFO.READ_CLIENT_INFO ( client_info OUT VARCHAR2 );
DBMS_APPLICATION_INFO.READ_MODULE ( module_name OUT VARCHAR2, action_name OUT VARCHAR2 );

Here is another example how to monitor a long running process with this package, as this example completes each iteration Oracle updates V$SESSION_LONGOPS on the procedure’s progress;


CREATE TABLE test (testcol NUMBER(10));

-- Session 1
SELECT DISTINCT sid FROM gv$mystat;
-- use this sid number in the session 2 query below

DECLARE
rindex  BINARY_INTEGER;
slno    BINARY_INTEGER;
sofar   NUMBER(6,2);
target  BINARY_INTEGER;
totwork NUMBER := 300;
BEGIN
rindex := dbms_application_info.set_session_longops_nohint;

SELECT object_id
INTO target
FROM user_objects
WHERE object_name = 'TEST';

FOR i IN 1 .. totwork
LOOP
sofar := i/3;
dbms_application_info.set_session_longops(rindex, slno,
'something you want to be seen when queried', target, 0, sofar, 100, 'Pct Complete');

INSERT INTO test VALUES (i);

-- to delay the process in order to watch from another session
dbms_lock.sleep(0.25);
END LOOP;
END;
/

-- Session 2 substitute the sid returned above from session 1
SELECT sofar, totalwork FROM gv$session_longops WHERE sid = 10;

2- Lempel-Ziv compression of RAW and BLOB data

UTL_COMPRESS package can be used on binary data like RAW, BLOB and BFILE for compression and decompression. Like gzip UTL_COMPRESS uses Lempel-Ziv compression algoritm. The package provides a set of data compression utilities. Here is a simple example;


SET SERVEROUTPUT ON
DECLARE
l_original_blob     BLOB;
l_compressed_blob   BLOB;
l_uncompressed_blob BLOB;
BEGIN
-- initialize both blobs TO something.
l_original_blob     := to_blob(utl_raw.cast_to_raw('1234567890123456789012345678901234567890'));
l_compressed_blob   := to_blob('1');
l_uncompressed_blob := to_blob('1');

-- compress THE data.
utl_compress.lz_compress(src => l_original_blob,
dst => l_compressed_blob);

-- uncompress THE data.
utl_compress.lz_uncompress(src => l_compressed_blob,
dst => l_uncompressed_blob);

-- display lengths.
dbms_output.put_line('original length :' || length(l_original_blob));
dbms_output.put_line('compressed length :' || length(l_compressed_blob));
dbms_output.put_line('uncompressed length :' || length(l_uncompressed_blob));

-- free temporary blobs.
dbms_lob.freetemporary(l_original_blob);
dbms_lob.freetemporary(l_compressed_blob);
dbms_lob.freetemporary(l_uncompressed_blob);
END;
/

original length :40
compressed length :33
uncompressed length :40

PL/SQL procedure successfully completed

3- Which Oracle version I am developing on

The DBMS_DB_VERSION package specifies the Oracle version numbers and other information useful for simple conditional compilation selections based on Oracle versions. The boolean constants in the package follow a naming convention. Each constant gives a name for a boolean expression. For example:
– VER_LE_9_1 represents version <= 9 and release <= 1
– VER_LE_10_2 represents version <= 10 and release <= 2
– VER_LE_10 represents version <= 10

Each version of Oracle from Oracle Database 10g Release 2 will contain a DBMS_DB_VERSION package with Boolean constants showing absolute and relative version information.


SET SERVEROUTPUT ON
BEGIN
dbms_output.put_line('VERSION ' || dbms_db_version.version);
dbms_output.put_line('RELEASE ' || dbms_db_version.release);
IF dbms_db_version.ver_le_10_2 THEN
dbms_output.put_line('10gR2 TRUE');
ELSE
dbms_output.put_line('10gR2 FALSE');
END IF;
END;
/

VERSION 10
RELEASE 2
10gR2 TRUE

PL/SQL procedure successfully completed

Also with 10g there is a new feature called PL/SQL Conditional Compilation This feature is useful for;
– compatibility between releases(10.2 and upwards only),
– trace and debugging,
– testing and quality assurance

By default conditional compilation flag will be NULL, let’s try on this example;


SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE p1 AS
BEGIN
dbms_output.put_line('Before');
$IF $$trace_enabled $THEN
dbms_output.put_line('Conditional Code');
$END
dbms_output.put_line('After');
END;
/
EXEC p1;

Procedure created

Before
After

PL/SQL procedure successfully completed

Conditional compilation can be enabled as follows:


ALTER PROCEDURE p1 COMPILE PLSQL_CCFLAGS = 'trace_enabled: true' REUSE SETTINGS;
EXEC p1;

Procedure altered

Before
Conditional Code
After

PL/SQL procedure successfully completed

In this example another 10g new feature is used mentioned with Managing Commit Redo Behavior title in the documentation.


BEGIN
$IF DBMS_DB_VERSION.VER_le_10_1 $THEN
$ERROR 'Unsupported database release or feature!' $END
$ELSE
DBMS_OUTPUT.PUT_LINE ('Release ' || DBMS_DB_VERSION.VERSION || '.' ||
DBMS_DB_VERSION.RELEASE || ' is supported.');
-- Note that this COMMIT syntax is newly supported in 10.2
COMMIT WRITE IMMEDIATE NOWAIT;
$END
END;
/

Release 10.2 is supported.

PL/SQL procedure successfully completed

Continue reading with Part 3

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

Refences Used : Metalink Note 61722.1 How to use the DBMS_APPLICATION_INFO Package

If only more developers used the DBMS_APPLICATION_INFO package post on oracleandy blog

Action, Module, Program ID and V$SQL… post on Mr.Kytes blog

New Dictionary Enhancements with 10g – Part 2

Filed under: Oracle 10g New Features — H.Tonguç Yılmaz @ 9:47 am

In Part 1 I mentioned three dictionary enhancements;

1- OS Statistics and Finding System CPU Utilization with 10g
2- BLOCKING_SESSION_STATUS and BLOCKING_SESSION columns on V$SESSION
3- Identify Tracing Enabled Sessions

Some more coming :)

4- Tuning and monitoring enhancements; latch free and enqueue examples

a) Prior 10g finding details for a latch free wait was as follows;


SELECT event, state, p1, p2, p3 FROM v$session_wait WHERE sid = 162;

EVENT         STATE            P1      P2    P3
————- ——- ———–  —— —–
latch free    WAITING 15113593728      97     5

SELECT * FROM v$event_name WHERE name = 'latch free';

EVENT# NAME       PARAMETER1      PARAMETER2      PARAMETER3
—— ———- ————— ————— —————
3 latch free address         number          tries

SELECT name FROM v$latch WHERE latch# = 97;

NAME
——————–
cache buffers chains

After 10g all these steps are in just one sql;


SELECT event, state
FROM   v$session_wait
WHERE  sid = 162;

EVENT                          STATE
—————————— ——-
latch: cache buffers chains    WAITING

b) Prior 10g finding details for enqueue waits were like this;


SELECT event, state, seconds_in_wait FROM v$session_wait WHERE sid = 96;

EVENT                               STATE               SECONDS_IN_WAIT
———————————– ——————- —————
enqueue                             WAITING                          24

SELECT     sid, CHR (BITAND (p1,-16777216) / 16777215) ||
CHR (BITAND (p1, 16711680) / 65535) enq,
DECODE (CHR (BITAND (p1,-16777216) / 16777215) ||
CHR (BITAND (p1, 16711680) / 65535),
‘TX’, ‘Transaction (RBS)’,
…        :)
CHR (BITAND (p1, 16711680) / 65535)) enqueue_name,
DECODE (BITAND (p1, 65535), 1, ‘Null’, 2, ‘Sub-Share’,
3, ‘Sub-Exclusive’, 4, ‘Share’, 5, ‘Share/Sub-Exclusive’,
6, ‘Exclusive’, ‘Other’) lock_mode
FROM       v$session_wait
WHERE sid = 96;

SID ENQ  ENQUEUE_NAME                   LOCK_MODE
—– —- —————————— ———-
96 TX   Transaction (RBS)              Exclusive

After 10g like latch free example this is also much more easier;


SELECT event, state, seconds_in_wait FROM v$session_wait WHERE sid = 143;

EVENT                               STATE               SECONDS_IN_WAIT
———————————– ——————- —————
enq: TX - row lock contention       WAITING                         495

also after 10g different naming with ‘TX’ events giving more information;


SELECT name, parameter1, parameter2, parameter3
FROM   v$event_name
WHERE  name LIKE 'enq: TX%';

NAME                           PARAMETER1    PARAMETER2      PARAMETER3
—————————— ————- ————— ————-
enq: TX - contention           name|mode     usn<<16 | slot  sequence
enq: TX - row lock contention  name|mode     usn<<16 | slot  sequence
enq: TX - allocate ITL entry   name|mode     usn<<16 | slot  sequence
enq: TX - index contention     name|mode     usn<<16 | slot  sequence

and clearer parameter naming;


SELECT name, parameter1, parameter2, parameter3
FROM   v$event_name
WHERE  name IN (’enq: HW - contention’, ‘enq: SQ - contention’);

NAME                           PARAMETER1    PARAMETER2      PARAMETER3
—————————— ————- ————— ————-
enq: HW - contention           name|mode     table space #   block
enq: SQ - contention           name|mode     object #        0

c) Prior 10g we needed a join to get the wait parameters of a session;


SELECT s.sid, w.state, w.event, w.seconds_in_wait siw,
s.sql_address, s.sql_hash_value hash_value, w.p1, w.p2, w.p3
FROM   v$session s, v$session_wait w
WHERE  s.sid = w.sid
AND    s.sid = 154;

After 10g it is located in v$session;


SELECT     sid, state, event, seconds_in_wait siw,
sql_address, sql_hash_value hash_value, p1, p2, p3
FROM       v$session
WHERE      sid = 154;

d) Prior 10g it is hard to say but after 10g “sessions are waiting for what or whom” query is as follows;


SELECT     sid, blocking_session, blocking_session_status block_status,
username, event, seconds_in_wait siw
FROM   v$session
WHERE  sid = 154;

BLOCKING
SID _SESSION BLOCK_STATUS USERNAME EVENT                          SIW
— ——– ———— ——– —————————— —
154      157 VALID        TSUTTON  enq: TX - row lock contention  318

5- What are the valid values for Oracle parameters?

V$PARAMETER_VALID_VALUES view is introduced on 10g Release 2, returns one row for each valid value for each parameter taking scalar values.


SELECT NAME, VALUE, isdefault
FROM v$parameter_valid_values
ORDER BY NAME, isdefault DESC, VALUE

audit_trail    DB    FALSE
audit_trail    DB_EXTENDED    FALSE
..
workarea_size_policy    AUTO    TRUE
workarea_size_policy    MANUAL    FALSE

6- Oracle Active Session History(ASH) samples

With 10g by the Active Session History(ASH) feature the V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA.

As part of the Automatic Workload Repository(AWR) snapshots, the content of V$ACTIVE_SESSION_HISTORY is also flushed to disk. Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, often avoiding the need to replay the workload to gather additional performance tracing information. The data present in ASH can be rolled up on various dimensions that it captures, some samples are as follows;


– Top CPU Session in last 5 minutes
SELECT session_id, COUNT(*)
FROM v$active_session_history
WHERE session_state = 'ON CPU'
AND sample_time > SYSDATE - (5 / (24 * 60))
GROUP BY session_id
ORDER BY COUNT(*) DESC

– Top Waiting Session in last 5 minutes
SELECT session_id, COUNT(*)
FROM v$active_session_history
WHERE session_state = 'WAITING'
AND sample_time > SYSDATE - (5 / (24 * 60))
GROUP BY session_id
ORDER BY COUNT(*) DESC

– Top SQL Queries from ASH
SELECT     ash.sql_id,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
FROM     v$active_session_history ash, v$event_name en
WHERE     sql_id IS NOT NULL
AND     en.event# = ash.event#
GROUP BY sql_id
ORDER BY sum(decode(session_state,'ON CPU',1,1)) desc

– Top Sessions
SELECT     ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" ,
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" ,
sum(decode(session_state,'ON CPU',1,1)) "TOTAL"
FROM v$active_session_history ash, v$event_name en
WHERE en.event# = ash.event#
GROUP BY session_id, user_id, session_serial#, program
ORDER BY sum(decode(session_state,'ON CPU',1,1))

Some additional ASH information

7- New views for usage pattern of the database

DBA_HIGH_WATER_MARK_STATISTICS displays information about database high-watermark statistics;


NAME             HIGHWATER LAST_VALUE DESCRIPTION
————— ———- ———- ———————————————————-
USER_TABLES            401        401 Number of User Tables
SEGMENT_SIZE    1237319680 1237319680 Size of Largest Segment (Bytes)
PART_TABLES             12          0 Maximum Number of Partitions belonging to an User Table
PART_INDEXES            12          0 Maximum Number of Partitions belonging to an User Index
USER_INDEXES           832        832 Number of User Indexes
SESSIONS                19         17 Maximum Number of Concurrent Sessions seen in the database
DB_SIZE         7940079616 7940079616 Maximum Size of the Database (Bytes)
DATAFILES                6          6 Maximum Number of Datafiles
TABLESPACES              7          7 Maximum Number of Tablespaces
CPU_COUNT                4          4 Maximum Number of CPUs
QUERY_LENGTH          1176       1176 Maximum Query Length

DBA_FEATURE_USAGE_STATISTICS displays information about database feature usage statistics.


DBID                          : 4133493568
NAME                          : Partitioning
VERSION                       : 10.1.0.1.0
DETECTED_USAGES               : 12
TOTAL_SAMPLES                 : 12
CURRENTLY_USED                : FALSE
FIRST_USAGE_DATE              : 16-oct-2003 13:27:10
LAST_USAGE_DATE               : 16-dec-2003 21:20:58
AUX_COUNT                     :
FEATURE_INFO                  :
LAST_SAMPLE_DATE              : 23-dec-2003 21:20:58
LAST_SAMPLE_PERIOD            : 615836
SAMPLE_INTERVAL               : 604800
DESCRIPTION                   : Oracle Partitioning option is being used -
there is at least one partitioned object created
..

Continue reading with Part 3

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0

Refences Used : Wait Event Enhancements in Oracle 10g presentation of Terry Sutton and Roger Schrag, Database Specialists, Inc.
http://Oraperf.sourceforge.net by Kyle Hailey, Embarcadero Technologies

December 28, 2006

Expanded Supplied Packages with 10g – Part I

Filed under: Oracle 10g New Features — H.Tonguç Yılmaz @ 1:33 pm

Beside New Dictionary Enhancements with 10g Series I will start to write on Expanded Supplied Packages with 10g. With 10g several packages upgraded and enhanced, also documentation is upgraded. “catproc.sql” can be used to identify these packages installed by default.

Oracle 9iR2 Supplied PL/SQL Packages & Types Reference Covers 101 Packages
Oracle 10gR1 PL/SQL Packages & Types Reference Covers 166 Packages
Oracle 10gR2 PL/SQL Packages & Types Reference Covers 180 Packages

– New Oracle9i Oracle Supplied Packages (19)

DBMS_APPLY_ADM DBMS_CAPTURE_ADM
DBMS_FGA DBMS_FLASHBACK
DBMS_LDAP DBMS_LIBCACHE
DBMS_LOGMNR_CDC_PUBLISH DBMS_LOGMNR_CDC_SUBSCRIBE
DBMS_METADATA DBMS_ODCI
DBMS_OUTLN_EDIT DBMS_REDEFINITION
DBMS_TRANSFORM DBMS_WM
DBMS_XMLGEN DBMS_XMLQUERY
DMBS_XMLSAVE UTL_ENCODE
UTL_URL

– New Oracle9i R2 Oracle Supplied Packages (18)

DBMS_APPLY_ADM DBMS_CAPTURE_ADM
DBMS_LOGSTDBY DBMS_MGWADM
DBMS_MGWMSG DBMS_PROPAGATION_ADM
DBMS_RULE DBMS_RULE_ADM
DBMS_STORAGE_MAP DBMS_STREAMS
DBMS_STREAMS_ADM DBMS_XDB
DBMS_XDBT DBMS_XDB_VERSION
DBMS_XMLDOM DBMS_XMLPARSER
DBMS_XPLAN DBMS_XSLPROCESSOR

– New Oracle10g R1 Oracle Supplied Packages (51) and 60 Packages Updates in Oracle10gR1

DBMS_ADVANCED_REWRITE DBMS_SERVICE
DBMS_ADVISOR DBMS_SQLTUNE
DBMS_CRYPTO DBMS_STAT_FUNCS
DBMS_DATAPUMP DBMS_STREAMS_AUTH
DBMS_DATA_MINING DBMS_STREAMS_MESSAGING
DBMS_DATA_MINING_TRANSFORM DBMS_STREAMS_TABLESPACE_ADM
DBMS_DIMENSION DBMS_WARNING
DBMS_FILE_TRANSFER DBMS_WORKLOAD_REPOSITORY
DBMS_FREQUENT_ITEMSET DBMS_XDBZ
DBMS_JAVA DBMS_XMLSTORE
DBMS_MONITOR HTF
DBMS_SCHEDULER HTMLDB_APPLICATION
DBMS_SERVER_ALERT HTMLDB_CUSTOM_AUTH
HTMLDB_ITEM SDO_GEOR
HTMLDB_UTIL SDO_GEOR_UTL
HTP SDO_NET
OWA_CACHE SDO_SAM
OWA_COOKIE SDO_TOPO
OWA_CUSTOM SDO_TOPO_MAP
OWA_IMAGE UTL_COMPRESS
OWA_OPT_LOCK UTL_DBWS
OWA_PATTERN UTL_I18N
OWA_SEC UTL_LMS
OWA_TEXT UTL_MAIL
OWA_UTIL WPG_DOCLOAD
SDO_GCDR

– New Oracle10g R2 Oracle Supplied Packages (14) and 53 Packages Updates in Oracle10gR2

DBMS_AQELM DBMS_FILE_GROUP
DBMS_AQIN DBMS_PREDICTIVE_ANALYTICS
DBMS_CHANGE_NOTIFICATION DBMS_PREPROCESSOR
DBMS_DB_VERSION DBMS_RLMGR
DBMS_EPG DBMS_TDB
DBMS_ERRLOG SDO_NET_MEM
DBMS_EXPFIL UTL_NLA

PL/SQL has grown tremendously with Oracle 10g R1 and R2. This growth includes a wide range of both DBA and Developer enhancements. We need to learn these features and make sure thoroughly understand these prior to start using. Knowing what Oracle has provided will expand your arsenal, utilize your cost and get you a competitive advantage especially in terms of being time-to-market. These packages are all supported by Oracle and tested over and over again, so dont reinvent the wheel and get rid of the maintenance costs of a newly developed applications ;)

Continue reading with Part 2

References used :
“The PL/SQL Grid: Time to Expand to 10g R1 & 10g R2¨ presentation by Joe Trezzo
What’s New in PL/SQL in Oracle Database 10g? Oracle Technology Network(OTN) Paper

Oracle Product Family and Pricing Highlights

Filed under: Oracle Other — H.Tonguç Yılmaz @ 12:54 pm

This Oracle Whitepaper prepared at August 2006 summaries the five oracle editions, the advanced options and the management pack of Oracle;

The editions:
Standard Edition One,Standard edition, Entreprise Edition, Personal Edition, Express Editions

The options:
RAC, Partitioning, Advanced Security, Label Security, OLAP, Data Mining, Spatial

The management packs:
Change management, Diagnostic, Tuning and Configuration Management Packs

Oracle database software comes at a cost. As of March 2006, the Enterprise Edition sells at a list price of US$40,000 per machine processor. Standard Edition comes cheaper, $15,000 per processor (it can run on up to 4 processors but has fewer features than Enterprise Edition, it lacks proper parallelization, etc; but remains quite suitable for running medium-sized applications). Standard ONE edition sells even more cheaply, $5000 per processor (but limited to 2 CPUs). Standard Edition ONE sells on a per-seat basis, and costs $149 per user, with a 5-user minimum. Oracle Corporation usually sells the licenses with an extra 22% cost for support and upgrades (access to MetaLink – Oracle’s support site) which customers need to renew annually.

Oracle Express Edition(Oracle XE), an addition to the Oracle database product family(beta version released in 2005, production version released in February 2006), offers a free version of the Oracle 10g Release 2 RDBMS, but limited to 4 Gb of user data, 1 Gb of RAM, and which will use no more than one CPU and which lacks some Oracle features like an internal JVM, partitioning, bitmap indexes and materialized views.

So what does “Release 10.2.0.2.0” mean?

1st Digit: “10” is a major database release number.
2nd Digit: “2” is the database maintenance release number.
3rd Digit: “0” is the application server release number.
4th Digit: “2” identifies a release level specific to a component.
5th Digit: “0” identifies a platform specific release.

To check the Oracle version from the SQL*Plus prompt, issue following sql:

SELECT banner FROM v$version WHERE banner LIKE ‘Oracle%’;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bi

SELECT version FROM product_component_version WHERE product LIKE ‘Oracle%’;

VERSION
—————————————————————-
10.2.0.2.0

From banner column of v$version you can also identify if you are runing Oracle in 32-bit or 64-bit architecture. For another easy way to identify this you may check this link.

Refences Used :
http://www.oracle.com/database/product_editions.html
http://www.oracle.com/support/lifetime-support-policy.html
Oracle® Wiki
OracleBrains.Com Archive, Understanding Oracle Release Number Format Article

Next Page »

Blog at WordPress.com.