Tips for better performance and tuning in Oracle SQL and PL/SQL

1) FTS (Full Table Scans) are always bad and Index usage is always good.

2) Usage of dynamic SQL within the stored procedure code is always bad even for search procedures.

3) Empty Space in an index that gets created due to the DML operations do not get used.

4) Indexes should be rebuilt at regular intervals.

5) Indexes and statistics are the same thing. Also, histograms are needed only on indexed columns.

6) Usage of cursors is always bad so avoid them like the plague.

7) Truncate command cannot be rolled back because it is a non-logged operation.

8 ) Table variables in SQL Server are always only memory resident.

9) Column order in a covered index does not matter.

10) In the case of SQL Server, one can separate the clustered index from the table.

11) Only committed data gets written to the disk.

12) Logical I/Os (LIO) are not a cause of concern, only Physical IO (PIO) are.

13) Count(1) is better performing than count(*).

14) Issue frequent commits in the application to make the transaction faster and also improve concurrency.

15) Views are evil evil DB Objects that always slow down performance.

Learn Oracle PL/SQL day to day topics

Learn Oracle PL/SQL day to day topics

• Oracle Supplied Packages
Table Storage Statistics
DBMS_STATS
Copying Statistics to Different Databases
DBMS_ALERT Procedures
DBMS_AQ and DBMS_AQADM
DBMS_DDL
DBMS_DEBUG
DBMS_PIPE
DBMS_OUTPUT
DBMS_LOGMNR
DBMS_TRACE
DBMS_SQL
DBMS_STANDARD
DBMS_SESSION
DBMS_LOB
DBMS_JOB
The Run() Procedure
DBMS_LOCK
UTL Packages

• Advanced Features of Procedures and Functions
Autonomous Transactions
Executing Autonomous Transactions
Advantages and Restrictions
Defining an Autonomous Block
Packages and Triggers
Autonomous Triggers
Autonomous PL/SQL Blocks
Transaction Visibility
Definer's and Invoker's Rights
Using the AUTHID Clause
Resolving External References
PL/SQL Wrappers
External Procedures
Alias Library Object
Call Specifications
Mapping Procedure to an External Procedure
Mapping Function to an External Procedure

• Introduction to Object Technology
Software Development
Issues with 3GL Development
Issues with Relational Database Development
Object-Relational Development
Goals of Object-Relational Development
Functional versus Object Hierarchies
Software Evolution
Type Table
Type and Class Definitions
What is an Object?
Creating a User Defined Type
Rules on Working with User Defined Types
Defining an Abstract Data Type
Inheritance
Single versus Multiple Inheritance
Polymorphism
With and Without Polymorphism
Different Object Methodologies
What is UML?
Visual Models
Attributes
Different Types of Methods

• Object-Relational Concepts
Object-Relational Databases
Object-Relational Features
Oracle Object-Relational Features
Oracle Datatypes
Abstract Datatypes
Data Dictionary Views
The CREATE TYPE Command
Object Tables
Type Methods
The Constructor Method
Working with User Defined Datatypes
Modifying Data in Object Tables
Working with Object Tables
Object Identifiers
Defining a REF
Updating REF Values
INSERTING REF Values
Dangling REFS
The DEREF Operator
ROWIDs
DBMS_ROWID Package
Different Types of Rowids
Object Methods
Different Types of Methods
Create Type Body Command
Executing Object Methods
Differences between Packages and Objects
DDL Type Commands
MAP Method
ORDER Method
MAP versus ORDER Method
STATIC Methods
Objects and Nulls
LOBS

• Collections
Records
Creating a PL/SQL Record
PL/SQL2 Tables
Table Attributes
PL/SQL8 Tables
Create a Type Specification
CAST Operator
Operator "THE"
Queries on Nested Tables
Dynamic SQL
Executing Dynamic SQL Statements in PL/SQL
Advantages of Dynamic SQL
Advantages of DBMS_SQL
DBMS_SQL Package
DBMS_SQL Example
Advantages of Native Dynamic SQL in PL/SQL
Syntax: EXECUTE IMMEDIATE Statement
Bind Variables
Defining Modes for Bind Variables
Executing DML, DCL, and DDL Statements
Executing Dynamic Multi-row Queries
Using Schema objects with Dynamic SQL
Cursor Attributes
Assigning Nulls to Bind Variables
Issues When Executing Subprograms
Debugging
Problem Analysis
Handling Exceptions
Continuing After an Exception
Using an Inner Block to Continue after an Exception
Inner Block Approach
Function Approach
Locator Variables
Temporary Tables
Storing Debugging Information
The DBMS_TRACE Package
DBMS_TRACE Constants
Managing the Volume of Tracing Information
The DBMS_DEBUG Package
Procedures in DBMS_DEBUG
Setting up a Debug Session
Performance Tuning
Working with Object Types and Collections
Bulk Binds
Bulk Collect Into Clause
Use the RETURNING Clause
NOCOPY Hint
Memory and PL/SQL
Pinning PL/SQL Programs
KEEP Procedure
What Do I to PIN?
Relative Sizes of Code to Pin
Serially Reusable Packages
Optimizing Integer Operations
Avoid NOT NULL Constraints
Rephrase Conditional Control Statements
Additional Considerations
DBMS_PROFILER
Profiler Tables
Table PLSQL_PROFILER_DATA
Additional Object-Relational Features
Type Evolution
Inheritance
Super and Subtypes
Restrictions on Overriding
Dynamic Method Dispatch
Substitutability
The TREAT Function
NOT INSTANTIABLE
Object View Hierarchies
IS OF Operator
The SYS_TYPEID Function
Object Support in Programmatic Environments
Advanced PL/SQL (I)
CASE Expressions and Statements
Searched CASE expression
The WHEN Clause
CASE Statement
Searched CASE statement
TABLE FUNCTIONS
Pipelined versus Non-pipelined
New SQL Datatypes
Performing DML Operations Inside Table Functions
Table Function with Autonomous Block
Parallelizing Table Functions
Enhancements to Bulk Operations
Bulk FETCH
Bulk FORALL with EXECUTE IMMEDIATE
Errors in DML Operation
ADVANCED QUEUEING - New Features
Java Message Service Enhancements
Advanced PL/SQL (II) and XML
Native Compilation of PL/SQL Code
Better Integration of LOB Datatypes
Multilevel Collections
MERGE STATEMENT
New PL/SQL Supplied Packages
Cursor Subquery
XML Data Type
INSERTING INTO AN XML COLUMN
Retrieving an XML Column
Updating an XML column
XMLTYPE FUNCTIONS
Common Constructs used in Xpath Expressions
DBMS_XMLGEN
SYS_XMLGEN
SYS_XMLAGG
Accessing Metadata
The DBMS_METADATA Package
DBMS_METADATA Object Types
Customizing DDL Output
GET_XML AND GET_DDL

SQL Script to enable and disable Constraints in any schema owner or user of Oracle Databse

Prompt Enter Owner:
ACCEPT i_owner;

Prompt Enter TABLE_NAME:
ACCEPT i_tab_name;

Prompt Enter Column_name:
ACCEPT i_col_name;

Prompt Enter Constraint_Type (U -UNIQUE, N-not null , P - PRIMARY KEY, R-Reference, C-CHECK) :
ACCEPT i_cons_type;

Prompt Do you want ENABLE(Y) OR DISABLE (N) the constraint ?
ACCEPT i_enable_disable;

Prompt Do you want VALIDATE (Y) OR NOVALIDATE (N) Clause ?
ACCEPT i_validate;

DECLARE
BEGIN
EXECUTE Immediate ' Drop table LONG_TEST';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

SQL Script and Package Body to convert Report to Excel

PACKAGE RPT2XLS IS
-- Font style constants
BOLD constant binary_integer := 1;
ITALIC constant binary_integer := 2;
UNDERLINE constant binary_integer := 4;

-- Horizontal alignment constants
SUBTYPE xlHAlign IS binary_integer;
CENTER CONSTANT xlHAlign := -4108;
CENTERACROSSSELECTION CONSTANT xlHAlign := 7;
DISTRIBUTED CONSTANT xlHAlign := -4117;
FILL CONSTANT xlHAlign := 5;
GENERAL CONSTANT xlHAlign := 1;
JUSTIFY CONSTANT xlHAlign := -4130;
LEFT CONSTANT xlHAlign := -4131;
RIGHT CONSTANT xlHAlign := -4152;

PROCEDURE put_cell(ColNo binary_integer, CellValue in varchar2,
FontName in varchar2 DEFAULT null,
FontSize in binary_integer DEFAULT null,
FontStyle in binary_integer DEFAULT null,
FontColor in binary_integer DEFAULT null,
BgrColor in binary_integer DEFAULT null,
Format in varchar2 DEFAULT null,
Align in xlHAlign DEFAULT null
);
PROCEDURE new_line;
PROCEDURE move2Line(line in NUMBER);
PROCEDURE run;
PROCEDURE release_memory;

END;

************************------------*********************************
PACKAGE BODY RPT2XLS IS

TYPE ExcelCell IS RECORD(RowNo binary_integer,
ColNo binary_integer,
Val varchar2(2000),
FontName varchar2(20),
FontSize binary_integer,
FontStyle binary_integer,
FontColor binary_integer,
BgrColor binary_integer,
Format varchar2(60),
Align xlHAlign
);
TYPE ExcelCells IS TABLE OF ExcelCell;
Cell ExcelCells := ExcelCells();

CurrentRow binary_integer := 1;

PROCEDURE new_line IS
BEGIN
CurrentRow := CurrentRow + 1;
END;

PROCEDURE move2Line(line in NUMBER) IS
BEGIN
CurrentRow:=CurrentRow+line;
END;


PROCEDURE put_cell(ColNo binary_integer, CellValue in varchar2,
FontName in varchar2 DEFAULT null,
FontSize in binary_integer DEFAULT null,
FontStyle in binary_integer DEFAULT null,
FontColor in binary_integer DEFAULT null,
BgrColor in binary_integer DEFAULT null,
Format in varchar2 DEFAULT null,
Align in xlHAlign DEFAULT null
) IS
BEGIN
Cell.Extend;
Cell(Cell.Last).RowNo := CurrentRow;
Cell(Cell.Last).ColNo := ColNo;
Cell(Cell.Last).Val := CellValue;
Cell(Cell.Last).FontName := FontName;
Cell(Cell.Last).FontSize := FontSize;
Cell(Cell.Last).FontStyle := FontStyle;
Cell(Cell.Last).FontColor := FontColor;
Cell(Cell.Last).BgrColor := BgrColor;
Cell(Cell.Last).Format := Format;
Cell(Cell.Last).Align := Align;
END;

PROCEDURE run IS
Application OLE2.OBJ_TYPE;
Workbooks OLE2.OBJ_TYPE;
Workbook OLE2.OBJ_TYPE;
Worksheets OLE2.OBJ_TYPE;
Worksheet OLE2.OBJ_TYPE;
WorkCell OLE2.OBJ_TYPE;
WorkColumn OLE2.OBJ_TYPE;
WorkFont OLE2.OBJ_TYPE;
WorkInterior OLE2.OBJ_TYPE;
ArgList OLE2.LIST_TYPE;
BEGIN
Application := OLE2.create_obj('Excel.Application');
OLE2.set_property(Application, 'Visible', 1);
Workbooks := OLE2.get_obj_property(Application, 'Workbooks');
Workbook := OLE2.invoke_obj(WorkBooks, 'Add');
Worksheets := OLE2.get_obj_property(Workbook, 'Worksheets');
Worksheet := OLE2.get_obj_property(Application, 'ActiveSheet');

for i in Cell.First .. Cell.Last
loop
if Cell(i).Val is not null then
ArgList := OLE2.create_arglist;
OLE2.add_arg(ArgList, Cell(i).RowNo);
ole2.add_arg(ArgList, Cell(i).ColNo);
WorkCell := OLE2.get_obj_property(Worksheet, 'Cells', ArgList);
ole2.destroy_arglist(ArgList);
ole2.set_property(WorkCell, 'Value', Cell(i).Val);
ole2.set_property(WorkCell, 'NumberFormat', Cell(i).Format);

if Cell(i).Align is not null then
ole2.set_property(WorkCell, 'HorizontalAlignment', Cell(i).Align);
end if;

WorkFont := OLE2.get_obj_property(WorkCell, 'Font');
WorkInterior := ole2.Get_Obj_Property(WorkCell, 'Interior');

if Cell(i).FontName is not null then
OLE2.set_property(WorkFont, 'Name', Cell(i).FontName);
end if;
if Cell(i).FontSize is not null then
OLE2.set_property(WorkFont, 'Size', Cell(i).FontSize);
end if;
if mod(Cell(i).FontStyle, 2) = 1 then
OLE2.set_property(WorkFont, 'Bold', 1);
end if;
if mod(Cell(i).FontStyle, 4) > 2 then
OLE2.set_property(WorkFont, 'Italic', 1);
end if;
if mod(Cell(i).FontStyle, 8) > 4 then
OLE2.set_property(WorkFont, 'Underline', 2);
end if;
if Cell(i).FontColor is not null then
OLE2.set_property(WorkFont, 'ColorIndex', Cell(i).FontColor);
end if;
if Cell(i).BgrColor is not null then
OLE2.set_property(WorkInterior, 'ColorIndex', Cell(i).BgrColor);
end if;

OLE2.release_obj(WorkInterior);
OLE2.release_obj(WorkFont);
OLE2.release_obj(WorkCell);
end if;
end loop;

ArgList := ole2.create_arglist;
ole2.add_arg(ArgList, 'A:Z');
WorkColumn := ole2.Get_Obj_Property(WorkSheet, 'Columns', ArgList);
ole2.destroy_arglist(ArgList);
ole2.invoke(WorkColumn, 'AutoFit');

OLE2.release_obj(WorkColumn);
OLE2.release_obj(Worksheet);
OLE2.release_obj(Worksheets);
OLE2.release_obj(Workbook);
OLE2.release_obj(Workbooks);
OLE2.release_obj(Application);

END;

PROCEDURE release_memory IS
BEGIN
Cell := ExcelCells();
SYS.DBMS_SESSION.free_unused_user_memory;
END;


END;
/

Script to convert table data into comma delimited format

set pagesize 0
set trimspool on
set serverout on
clear buffer
undef dumpfile
undef dumptable
undef dumpowner
var maxcol number
var linelen number
var dumpfile char(40)
col column_id noprint
set pages 0 feed off termout on echo off verify off
accept dumpowner char prompt 'Owner of table to dump: '
accept dumptable char prompt 'Table to dump: '

begin
select max (column_id)
into :maxcol
from all_tab_columns
where table_name = rtrim (upper ('&dumptable')) and
owner = rtrim (upper ('&dumpowner'));

select sum (data_length) + (:maxcol * 3)
into :linelen
from all_tab_columns
where table_name = rtrim (upper ('&dumptable')) and
owner = rtrim (upper ('&dumpowner'));
end;
/

print linelen
print maxcol

SQL Script to view and count number of sessions to the database

set pages 100
set lines 3000

col machine format a25
col username format a15
col program format a25
break on 1;

set heading off;
select
'Sessions on database '|| d.name ||' having instance name '|| i.instance_name
from v$database d,v$instance i;
set heading on;

compute sum label 'Total Sessions' of sessions on 1
select
1,username,machine,program,count(*) sessions
from v$session
group by username,machine,program
order by username,sessions desc;

SQL to find Oracle Database locks

column blocker format a25
column blockee format a25
column sid_serial format a10

select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;

SQL to compile invalid objects in Oracle DB after refreshing

set pagesize 0
set feedback off
set trimspool on
prompt
prompt Run the script as sysdba otherwise invalid objects will remain (ORA-01031 error)
prompt
prompt Run the script several times. It only takes long the first time
prompt
prompt check the progress of compilation by issueing from another session
prompt select count(*) "invalid" from dba_objects where status<>'VALID';
prompt
prompt hit to continue
pause
spool compile.lis
select 'alter '||object_type||' '||owner||'."'||object_name||'" compile;'
from dba_objects
where status<>'VALID'
and object_type not in ('PACKAGE BODY','TYPE BODY','UNDEFINED','JAVA CLASS','SYNONYM')
union
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects
where status<>'VALID'
and object_type='PACKAGE BODY'
union
select 'alter type '||owner||'.'||object_name||' compile body;'
from dba_objects
where status<>'VALID'
and object_type='TYPE BODY'
union
select 'alter materialized view '||owner||'.'||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='UNDEFINED'
union
select 'alter java class '||owner||'."'||object_name||'" resolve;'
from dba_objects
where status<>'VALID'
and object_type='JAVA CLASS'
union
select 'alter synonym '||owner||'.'||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='SYNONYM'
and owner<>'PUBLIC'
union
select 'alter public synonym '||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='SYNONYM'
and owner='PUBLIC';
spool off
set feedback on
@compile.lis

SQL to see the Reserved, Used and Free space of Oracle database

SELECT r.tablespace_name, reserved_space "RESERVED_SPACE(MB)",
reserved_space - free_space "USED_SPACE(MB)",
free_space "FREE_SPACE(MB)"
FROM (SELECT tablespace_name, SUM (BYTES)
/ (1024 * 1024) reserved_space
FROM dba_data_files
GROUP BY tablespace_name) r,
(SELECT tablespace_name, SUM (BYTES) / (1024 * 1024) free_space
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE r.tablespace_name = f.tablespace_name
ORDER BY r.tablespace_name;

Procedure To ENABLE and DISABLE Triggers on a Schema level

CREATE or replace procedure enable_triggers as
v_tablename varchar2(100);
v_query1 varchar2(100);
v_query2 varchar2(100);
v_query varchar2(100);

-- A cursor is used to fetch all the Tables in the Schema
cursor v_cursor is
select table_name from User_tables;

v_row v_cursor%rowtype;

begin

open v_cursor;
loop
fetch v_cursor into v_row;
if v_cursor%notfound then
exit;
end if;

v_tablename := v_row.table_name;

v_query1:=\'alter table\';

-- For disabling the triggers on the tables ,
-- the following line can be changed as \" v_query2:=\'disable all triggers\';\"
v_query2:=\'enable all triggers\';
v_query:=v_query1||\' \'||v_tablename||\' \'||v_query2;
dbms_output.put_line(v_query);
execute immediate ( v_query);

end loop;

close v_cursor;
end;
/


--- Execute the Procedure ------
EXECUTE ENABLE_TRIGGERS;

BULK COLLECT and BULK BIND sample program in Oracle

set serveroutput on size 50000

DECLARE
CURSOR emp_cur IS SELECT * FROM EMP;

TYPE emp_tab_t IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
emp_tab emp_tab_t; -- In-memory table

rows NATURAL := 10000; -- Number of rows to process at a time
i BINARY_INTEGER := 0;
BEGIN
OPEN emp_cur;
LOOP
-- Bulk collect data into memory table - X rows at a time
FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT rows;
EXIT WHEN emp_tab.COUNT = 0;

DBMS_OUTPUT.PUT_LINE( TO_CHAR(emp_tab.COUNT)|| ' rows bulk fetched.');

FOR i IN emp_tab.FIRST .. emp_tab.LAST loop
-- Manipumate data in the memory table...
dbms_output.put_line('i = '||i||', EmpName='||emp_tab(i).ename);
END LOOP;

-- Bulk bind of data in memory table...
FORALL i in emp_tab.FIRST..emp_tab.LAST
INSERT /*+APPEND*/ INTO emp2 VALUES emp_tab(i);

END LOOP;
CLOSE emp_cur;
END;
/

What to read in DBMS and Oracle Architecture

What to read in DBMS and Oracle Architecture
- Architecture
- Installing the Oracle Database Software
- Creating an Oracle Database
- Managing the Oracle Instance
- Managing Database Storage Structures
- Administering User Security
- Managing Schema Objects
- Managing Data and Concurrency
- Managing Undo Data
- Implementing Oracle Database Security
- Configuring the Oracle Network Environment
- Proactive Maintenance
- Performance Management
- Backup and Recovery Concepts
- Performing Database Backup
- Performing Database Recovery
- Performing Flashback
- Moving Data
- Using Globalization Support Objectives
- Configuring Recovery Manager
- Recovering from User Errors
- Dealing with Database Corruption
- DBVERIFY
- Automatic Database Management
- Using Recovery Manager
- Recovering from Non-Critical Losses
- Monitoring and Managing Storage
- Automatic Storage Management
- Monitoring and Managing Memory
- Database Recovery
- Flashback Database
- Managing Resources
- Automating Tasks with the Scheduler
- Installation
- Server Configuration
- Load and Unload Data
- Automatic Management
- Manageability Infrastructure
- Application Tuning
- System Resource Management
- Automating Tasks with the Scheduler
- Space Management
- Improved VLDB Support
- Backup and Recovery Enhancements
- Flashback Any Error
- General Storage Enhancement
- Automatic Storage Management
- Maintain Software
- Security
- Miscellaneous New Features

Important topics in SQL & PL/SQL, Native and other tools, Unix Tools, Oracle Forms and Reports

SQL & PL/SQL
Simple way to get number decimal (group) separator
"Holefinder" query
Getting node id in tree structure using OS directory syntax input string
Regular expressions in Oracle
Compress / decompress Oracle data package
Creating more demonstrable queries in SQL*Plus
SQL exercises with text data
Nice Calendar view
From javascript to ORACLE database SQL query demonstration

Tools
ODBC data importer
Quick drop all schema objects script
Pls2html - PL/SQL code html publishing with syntax highlighting
Sql2bat - Convert Sql*Plus script into a NT batch file
TSMap - Oracle tablespace map photographer
Oracle developer background engines manager
Oracle SQL*Plus script Organizer
Oracle Forms PL/SQL Code Counter
Fmbgrep - Oracle Forms Code Searcher

Miscellaneous
HTML analog of the unix oerr
Euro foreign exchange reference rates daily loader
E-mail commander - Administering database server by e-mail
Oracle Reports Server 6i buffer overflow vulnerability
"Oracle Server History" SVG example

Non-Oracle Stuff
Myrhythm - MIDI Rhythm Composer
Text animation examples using html marquee tag
Creating and Using Index-Organized XML Data

Oracle Forms
Sending mail from host command
Forms 6i server tips collection
Tetris game example
Checking connection status
Embedding Perl into Oracle Developer applications
Closing the Graphics Batch window from forms application
Oracle Forms XML Explorer
Evaluating expressions inside Oracle Forms number item
Text-to-Speech capabilities in Oracle Developer Applications
Getting WEB page content from Oracle Developer Application

Oracle Reports
Returning to parameter form after report completion
From report to excel export package
Page break in HTML report
Simple analog of forms HOST procedure for NT platform
Setting field attributes in HTML reports (including borders and background)

Gun Zip Oracle Data using Java Stored procedure

Zip/ Unzip Oracle data

GZIP - Compress / decompress oracle data package

Description: Implements pl/sql pack and unpack methods using JDK java.util.zip package for both RAW and BLOB Oracle types

Methods:


FUNCTION pack(buf IN raw) RETURN raw;
FUNCTION unpack(buf IN raw) RETURN raw;
PROCEDURE packBlob(src IN blob, dst IN OUT blob);
PROCEDURE unpackBlob(src IN blob, dst IN OUT blob);


Installation steps:


Create java class to implement gzip methods:
CREATE OR REPLACE JAVA SOURCE NAMED "GZIPImpl"
AS
import java.io.*;
import java.util.zip.*;
import java.sql.*;
import oracle.sql.*;

public class GZIPImpl
{
public static byte[] packRaw(byte[] b) {
ByteArrayOutputStream outBuffer = new ByteArrayOutputStream();
try {
GZIPOutputStream gzip = new GZIPOutputStream(outBuffer);
gzip.write(b);
gzip.close();
}
catch (IOException e) {
System.err.println(e);
}
return outBuffer.toByteArray();
}

public static byte[] unpackRaw(byte[] b) {
ByteArrayOutputStream outBuffer = new ByteArrayOutputStream();
ByteArrayInputStream inBuffer = new ByteArrayInputStream(b);
try {
GZIPInputStream gzip = new GZIPInputStream(inBuffer);
byte[] tmpBuffer = new byte[256];
int n;
while ((n = gzip.read(tmpBuffer)) >= 0)
outBuffer.write(tmpBuffer, 0, n);
}
catch (IOException e) {
System.err.println(e);
}
return outBuffer.toByteArray();
}

public static void packBlob(oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob[]) {
try {
OutputStream outBuffer = dstBlob[0].getBinaryOutputStream();
InputStream inBuffer = srcBlob.getBinaryStream();
GZIPOutputStream gzip = new GZIPOutputStream(outBuffer);
byte[] tmpBuffer = new byte[256];
int n;
while ((n = inBuffer.read(tmpBuffer)) >= 0)
gzip.write(tmpBuffer, 0, n);
gzip.close();
}
catch (SQLException e) {
System.err.println(e);
}
catch (IOException e) {
System.err.println(e);
}
}

public static void unpackBlob(oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob[]) {
try {
OutputStream outBuffer = dstBlob[0].getBinaryOutputStream();
InputStream inBuffer = srcBlob.getBinaryStream();
GZIPInputStream gzip = new GZIPInputStream(inBuffer);
byte[] tmpBuffer = new byte[256];
int n;
while ((n = gzip.read(tmpBuffer)) >= 0)
outBuffer.write(tmpBuffer, 0, n);
outBuffer.close();
}
catch (SQLException e) {
System.err.println(e);
}
catch (IOException e) {
System.err.println(e);
}
}

};
/

Compile java class and check for errors:
alter java source "GZIPImpl" compile
/
show errors

Create a wrapper PL/SQL package:
CREATE PACKAGE GZIP AS
FUNCTION pack(buf IN raw) RETURN raw;
FUNCTION unpack(buf IN raw) RETURN raw;
PROCEDURE packBlob(src IN blob, dst IN OUT blob);
PROCEDURE unpackBlob(src IN blob, dst IN OUT blob);
END;
/

CREATE PACKAGE BODY GZIP AS
FUNCTION pack(buf IN raw) RETURN raw
AS LANGUAGE JAVA
NAME 'GZIPImpl.packRaw(byte[]) return byte[]';

FUNCTION unpack(buf IN raw) RETURN raw
AS LANGUAGE JAVA
NAME 'GZIPImpl.unpackRaw(byte[]) return byte[]';

PROCEDURE packBlob(src IN blob, dst IN OUT blob)
AS LANGUAGE JAVA
NAME 'GZIPImpl.packBlob(oracle.sql.BLOB, oracle.sql.BLOB[])';

PROCEDURE unpackBlob(src IN blob, dst IN OUT blob)
AS LANGUAGE JAVA
NAME 'GZIPImpl.unpackBlob(oracle.sql.BLOB, oracle.sql.BLOB[])';
END;
/

Test GZIP package:
DECLARE
src varchar2(32767);
srcRaw raw(32767);
zip raw(32767);
unzip varchar2(32767);
tmp varchar2(250);
tmpRaw raw(250);
srcBlob BLOB;
zipBlob BLOB;
unzipBlob BLOB;
read_amount binary_integer;
BEGIN
src := 'Many sources of information contain redundant data or data that adds '||
'little to the stored information. This results in tremendous amounts '||
'of data being transferred between client and server applications or '||
'computers in general. The obvious solution to the problems of data '||
'storage and information transfer is to install additional storage '||
'devices and expand existing communication facilities. To do so, however, '||
'requires an increase in an organization''s operating costs. One method '||
'to alleviate a portion of data storage and information transfer is '||
'through the representation of data by more efficient code. This article '||
'shows how to compress and decompress data, efficiently and conveniently, '||
'from within your ORACLE applications using the GZIP package.';

DBMS_OUTPUT.put_line('1. RAW TEST');
DBMS_OUTPUT.put_line('===========');

DBMS_OUTPUT.put_line('1.1 SOURCE TEXT:');
DBMS_OUTPUT.put_line(' Cut = '||substr(src, 1, 60)||' ...');
DBMS_OUTPUT.put_line(' Length = '||length(src));

DBMS_OUTPUT.put_line('1.2 COMPRESSED RAW:');
srcRaw := UTL_RAW.cast_to_raw(src); -- Don't forget explicit convertion!
zip := GZIP.pack(srcRaw);
tmp := '';
for i in 1 .. 15 loop
tmp := tmp||'0x'||UTL_RAW.substr(zip, i, 1)||' ';
end loop;
DBMS_OUTPUT.put_line(' Cut = '||tmp||' ...');
DBMS_OUTPUT.put_line(' Length = '||UTL_RAW.length(zip));

DBMS_OUTPUT.put_line('1.3 DECOMPRESSED TEXT:');
unzip := UTL_RAW.cast_to_varchar2(GZIP.unpack(zip)); -- Decompress and convert to varchar2
DBMS_OUTPUT.put_line(' Cut = '||substr(unzip, 1, 60)||' ...');
DBMS_OUTPUT.put_line(' Length = '||length(unzip));

DBMS_OUTPUT.put_line('2. BLOB TEST');
DBMS_OUTPUT.put_line('============');

-- Prepare temporary Blobs
DBMS_LOB.CreateTemporary(srcBlob, TRUE);
DBMS_LOB.CreateTemporary(zipBlob, TRUE);
DBMS_LOB.CreateTemporary(unzipBlob, TRUE);

DBMS_OUTPUT.put_line('2.1 SOURCE BLOB:');
srcRaw := UTL_RAW.cast_to_raw(src); -- Don't forget explicit convertion!
DBMS_LOB.write(srcBlob, UTL_RAW.length(srcRaw), 1, srcRaw); -- Fill source blob
-- Print results
read_amount := 60;
DBMS_LOB.read(srcBlob, read_amount, 1, tmpRaw);
DBMS_OUTPUT.put_line(' Cut = '||UTL_RAW.cast_to_varchar2(tmpRaw)||' ...');
DBMS_OUTPUT.put_line(' Length = '||to_char(DBMS_LOB.GetLength(srcBlob)));

DBMS_OUTPUT.put_line('2.2 COMPRESSED BLOB:');
GZIP.packBlob(srcBlob, zipBlob); -- Compress srcBlob into zipBlob
-- Print results
read_amount := 60;
DBMS_LOB.read(zipBlob, read_amount, 1, tmpRaw);
tmp := '';
for i in 1 .. 15 loop
tmp := tmp||'0x'||UTL_RAW.substr(tmpRaw, i, 1)||' ';
end loop;
DBMS_OUTPUT.put_line(' Cut = '||tmp||' ...');
DBMS_OUTPUT.put_line(' Length = '||to_char(DBMS_LOB.GetLength(zipBlob)));

DBMS_OUTPUT.put_line('2.3 DECOMPRESSED BLOB:');
GZIP.unpackBlob(zipBlob, unzipBlob); -- Decompress zipBlob into unzipBlob
-- Print results
read_amount := 60;
DBMS_LOB.read(unzipBlob, read_amount, 1, tmpRaw);
DBMS_OUTPUT.put_line(' Cut = '||UTL_RAW.cast_to_varchar2(tmpRaw)||' ...');
DBMS_OUTPUT.put_line(' Length = '||to_char(DBMS_LOB.GetLength(unzipBlob)));

-- Release temporary blobs
DBMS_LOB.FreeTemporary(srcBlob);
DBMS_LOB.FreeTemporary(zipBlob);
DBMS_LOB.FreeTemporary(unzipBlob);
END;
/

Code above should produce next results:

1. RAW TEST
===========
1.1 SOURCE TEXT:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
1.2 COMPRESSED RAW:
Cut = 0x1F 0x8B 0x08 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x95 0x92 0xCB 0x8E 0x14 ...
Length = 408
1.3 DECOMPRESSED TEXT:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
2. BLOB TEST
============
2.1 SOURCE BLOB:
Cut = Many sources of information contain redundant data or data t ...
Length = 754
2.2 COMPRESSED BLOB:
Cut = 0x1F 0x8B 0x08 0x00 0x00 0x00 0x00 0x00 0x00 0x00 0x95 0x92 0xCB 0x8E 0x14 ...
Length = 408
2.3 DECOMPRESSED BLOB:
Cut = Many sources of information contain redundant data or data t ...
Length = 754


Examples


Compress/decompress on fly stored data using DML (less when 4000 bytes in size):

insert into my_table (compressed_text) values (GZIP.pack(UTL_RAW.cast_to_raw('1234567890')));
select UTL_RAW.cast_to_varchar2(GZIP.unpack(compressed_text)) from my_table;

Compress/decompress BLOB stored data (size <= 4Gb):

DECLARE
SrcBlobLocator BLOB;
DstBlobLocator BLOB;
BEGIN
select blob_data into SrcBlobLocator from src_table where id=1;
select blob_data into DstBlobLocator from dst_table where id=1;
GZIP.packBlob(SrcBlobLocator, DstBlobLocator);
commit;
END;

Cursor details in shared pool with statistics in Oracle

SET pages 100

SELECT sql_text, executions,
ROUND ((disk_reads + buffer_gets) / executions, 2) average,
(disk_reads + buffer_gets) READS, first_load_time
FROM v$sql
WHERE 1 = 1
--and UPPER(sql_text) like '%LATEST%'
--and UPPER(sql_text) not like '%DUAL%'
AND executions != 0
ORDER BY (disk_reads + buffer_gets);

Indexe details on a table

COLUMN index_name format A20

COLUMN column_name format A25

COLUMN column_position format 999 heading 'Pos'

COLUMN uniq format a5

SET verify off

BREAK on index_name skip 1



SELECT c.index_name, SUBSTR (i.uniqueness, 1, 1) uniq, c.column_name,
c.column_position
FROM all_ind_columns c, all_indexes i
WHERE c.table_owner = UPPER ('&table_owner')
AND c.table_name = UPPER ('&table_name')
AND c.index_owner = i.owner
AND c.index_name = i.index_name
ORDER BY 2 DESC, 1, 4
/

v$session: Session deatils in oracle

/* Formatted on (Formatter Plus v4.8.8) */
SET lines 120

COLUMN sid format a5

COLUMN client format a13

COLUMN inlogtijd heading logon_time

COLUMN inlogtijd format a18

COLUMN ora_user heading oracle_user

COLUMN ora_user format a12

COLUMN serial# format a5

COLUMN serial# heading ser#

COLUMN programma format a35

COLUMN computer format a17

SELECT DECODE (s.TYPE, 'BACKGROUND', 'INTERNAL', username) ora_user,
SUBSTR
(DECODE (UPPER (SUBSTR (s.terminal, 1, 3)),
'PTS', machine || ':' || s.osuser,
DECODE (SUBSTR (s.terminal, 1, 1),
'?', s.machine || ':' || s.osuser,
DECODE (s.terminal,
NULL, s.machine
|| DECODE (s.osuser, NULL, '', ':')
|| s.osuser,
DECODE (s.terminal,
'DOS PC', s.terminal,
SUBSTR (s.machine,
INSTR (s.machine,
'\',
1
)
+ 1,
6
)
|| ':'
|| s.osuser
)
)
)
),
1,
13
) client,
DECODE (SUBSTR (s.machine, INSTR (s.machine, '\', 1) + 1, 6),
'W12021', 'Edwin Korsten',
s.machine
) computer,
TO_CHAR (SID) SID, TO_CHAR (serial#) serial#,
TO_CHAR (logon_time, 'DD-MM-YYYY HH24:MI') logon_time,
SUBSTR (DECODE (lockwait,
NULL, DECODE (command,
0, 'NO OP',
1, 'CR TAB',
2, 'INSERT',
3, 'SELECT',
4, 'CR CLU',
5, 'AL CLU',
6, 'UPDATE',
7, 'DELETE',
8, 'DROP',
9, 'CR IND',
10, 'DR IND',
11, 'AL IND',
12, 'DR TAB',
13, 'CR SEQ',
14, 'AL SEQ',
15, 'AL TAB',
16, 'DR SEQ',
17, 'GRANT',
18, 'REVOKE',
19, 'CR SYN',
20, 'DR SYN',
21, 'CR VIW',
22, 'DR VIW',
23, 'VAL IND',
24, 'CR PRC',
25, 'AL PRC',
26, 'LK TAB',
27, 'NO-OPE',
28, 'RENAME',
29, 'COMMEN',
30, 'AUDIT',
31, 'NOAUD',
32, 'CR DBLNK',
33, 'DR DBLNK',
34, 'CR DB',
35, 'AL DB',
36, 'CR RBS',
37, 'AL RBS',
38, 'DR RBS',
39, 'CR TSP',
40, 'AL TSP',
41, 'DR TSP',
42, 'AL SES',
43, 'AL USR',
44, 'COMMIT',
45, 'ROLLBCK',
46, 'SAVEPNT',
47, 'EX PLS',
48, 'SET TRN',
49, 'SW LOG',
50, 'EXPLAIN',
51, 'CR USR',
52, 'CR ROL',
53, 'DR USR',
54, 'DR ROL',
55, 'SET ROL',
56, 'CR SCH',
57, 'CR CFIL',
58, 'AL TRC',
59, 'CR TRG',
60, 'AL TRG',
61, 'DR TRG',
62, 'AN TAB',
63, 'AN IND',
64, 'AN CLU',
65, 'CR PROF',
67, 'DR PROF',
68, 'AL PROF',
69, 'DR PRC',
70, 'AL RC',
71, 'CR SNLOG',
72, 'AL SNLOG',
73, 'DR SNLOG',
74, 'CR SNSHT',
75, 'AL SNSHT',
76, 'DR SNSHT',
79, 'AL ROL',
85, 'TRN TAB',
86, 'TRN CLU',
88, 'AL VW',
91, 'CR FNC',
92, 'AL FNC',
93, 'DR FNC',
94, 'CR PCK',
95, 'AL PCK',
96, 'DR PCK',
97, 'CR PCKB',
98, 'AL PCKB',
99, 'DR PCKB',
TO_CHAR (command)
),
'WAITING'
),
1,
10
) command
FROM v$session s
ORDER BY username;

Show locks and locks waiting per session

/* Formatted on (Formatter Plus v4.8.8) */
COL uname head "Username" form a12
COL sid head "SID" form 999
COL ltype head "Type" form a4
COL lmode head "Mode" form a10
COL blocked head "Wait" form a4
COL details head "Details" form a40


SET verify off



ACCEPT user prompt "Username [%]: "



SELECT s.SID SID, s.username uname, 'DML' ltype,
DECODE (l.lmode,
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive'
) lmode,
DECODE (l.request, 0, 'No', 'Yes') blocked,
u.NAME || '.' || o.NAME details
FROM v$session s, v$lock l, SYS.obj$ o, SYS.user$ u
WHERE s.username LIKE NVL (UPPER ('&user' || '%'), '%')
AND s.SID = l.SID
AND l.id1 = o.obj#
AND l.TYPE = 'TM'
AND o.owner# = u.user#(+)
UNION ALL
SELECT s.SID SID, s.username uname,
DECODE (l.TYPE, 'TX', 'TX', 'UL', 'USR', 'SYS') ltype,
DECODE (l.lmode,
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive'
) lmode,
DECODE (l.request, 0, 'No', 'Yes') blocked,
DECODE (l.request,
0, NULL,
'Waiting on session ' || TO_CHAR (b.SID)
) details
FROM v$session s, v$lock l, v$lock b
WHERE s.username LIKE NVL (UPPER ('&user' || '%'), '%')
AND s.SID = l.SID
AND l.TYPE != 'TM'
AND l.id1 = b.id1(+)
AND b.request(+) = 0
ORDER BY 5 DESC, 3 DESC, 2, 1;



SET verify on

Show open cursors per user/process in Oracle

SELECT SUBSTR (ses.username || ' ' || ses.osuser, 1, 30) user_osuser,
stat.VALUE open_cursor, ses.process process
FROM v$sesstat stat, v$session ses
WHERE stat.statistic# = 3 AND stat.SID = ses.SID;

Show open cursors per user/process in Oracle

SELECT SUBSTR (ses.username || ' ' || ses.osuser, 1, 30) user_osuser,
stat.VALUE open_cursor, ses.process process
FROM v$sesstat stat, v$session ses
WHERE stat.statistic# = 3 AND stat.SID = ses.SID;