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

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;
Advertisements

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

Blog at WordPress.com.