SQL Sinppet : Size of BLOB for each row.

Assuming there's a table with a blob column to get the size of each row's blob size

select object_name,DBMS_LOB.GETLENGTH(blob_column) from table;

or

select object_name,lengthb(blob_column) from table;

Scalar subqueries to reduce PL/SQL to SQL switching

Tom Kyte has written an excellent article on the current issue (September/October) of Oracle Magazine which shows how to reduce switching between pl/sql and sql using scalar subquery.

Tom describes a scalar subquery as "What exactly is a scalar subquery? It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used."

On the article there are many comparisons of using scalar subquery vs deterministic pl/sql, result cache and etc.
Below is the simplest case of performance comparison with and without scalar subquery.

1.Create a big table to query

SQL> create table bigtable as select * from all_objects nologging;

2. Create the function that will be used in this test case

create or replace function mytest(p_owner varchar2) return number is
obj_count number :=0;
begin 
 select count(*) into obj_count from bigtable bt where bt.owner = p_owner; 
 return obj_count; 
end; 
/

3. Run the first test case without scalar subquery

set timing on
variable cpu number;
begin
:cpu := dbms_utility.get_cpu_time;
end;
/

SQL> select owner,mytest(owner) from bigtable;
....
SYS                                    31328
SYS                                    31328

72232 rows selected. -- this is all the rows in the table
Elapsed: 00:05:11.01

SQL> select dbms_utility.get_cpu_time-:cpu cpu_hsecs from dual;

CPU_HSECS
---------
30931

4. Second test case with scalar subquery caching

set timing on
variable cpu number;
begin
:cpu := dbms_utility.get_cpu_time;
end;
/

SQL> select owner,(select mytest(owner) from dual) mo from bigtable;

SYS                                                    31328
SYS                                                    31328
SYS                                                    31328

72232 rows selected.  -- all the rows in the table

Elapsed: 00:00:17.05

SQL> select dbms_utility.get_cpu_time-:cpu cpu_hsecs from dual;
CPU_HSECS
---------
56

As seen from the outputs both cpu usage and elapsed time has reduced. How this happens has been explained in the above mentioned article.

SQL Sinppet : Inter Instance Lockings

Identify the inter instance locks in a RAC environment.

Select (Select locking.sid||','||se.serial# From Gv$session Se Where Se.Sid=Locking.Sid And Se.Inst_Id = Locking.Inst_Id) As Locking_Session_serial,
Locking.Type As holding_lock_Type,
Locking.Inst_Id As Lock_Instance,
Decode (Locking.Lmode,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') Locking_Mode,
(Select waiting.sid||','||se.serial# From Gv$session Se Where Se.Sid=Waiting.Sid And Se.Inst_Id = Waiting.Inst_Id) As Waiting_Session,
Waiting.Type As Wait_Lock_Type,
Waiting.Inst_Id As Wait_Instance,
DECODE(waiting.REQUEST,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') REQUEST_mode,
(Select Prev_Sql_Id||' @ '||Machine From Gv$session Se Where Se.Sid=Locking.Sid And Se.Inst_Id = Locking.Inst_Id) Locking_Sql_And_Machine,
(Select Sql_Id||' @ '||Machine From Gv$session Se Where Se.Sid=Waiting.Sid And Se.Inst_Id = Waiting.Inst_Id) Waiting_Sql_and_machine,
Case When Locking.Type='TM' Then
(select object_name from dba_objects where object_id=locking.id1)
end as TM_lock_Object
FROM Gv$lock Locking,
  Gv$lock Waiting
WHERE Locking.Id1   = Waiting.Id1
And Locking.Id2     = Waiting.Id2
And Locking.Block   > 0
And Waiting.Request >0;

Above gives sid,serial# from locking and waiting sessions (makes it easy to kill them), client machine names of the session in the lock situation as well as sql ids to identify the sql involved in the lock and instance ids.

There's also gv$global_blocked_locks view which gives global locks.

SELECT g.inst_id INST,
  s.sid,
  g.type,
  s.username,
  s.serial#,
  s.process,
  DECODE (LMODE,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') LMODE,
  DECODE(REQUEST,0,'None',1,'Null',2,'Row-S',3, 'Row-X',4,'Share',5,'S/Row',6,'Exclusive') REQUEST
FROM gv$global_blocked_locks g,
  gv$session s
WHERE g.sid   = s.sid
And G.Inst_Id = S.Inst_Id
ORDER BY state;

To test this create the tables listed in TM Enq post.
From instance 1 run

update y set c = 8 where d=10;

and from instance 2 run

update x set a = 12 where a = 7;

second session will hang. From a third session run the first SQL above to identify the locks and objects involved.



Kill blocking sessions across all instances

Select (Select 'alter system kill session '''||locking.sid||','||se.serial#||',@'||locking.inst_id||''';' 
From Gv$session Se Where Se.Sid=Locking.Sid And Se.Inst_Id = Locking.Inst_Id) As kill_stmt
FROM Gv$lock Locking,
  Gv$lock Waiting
WHERE Locking.Id1   = Waiting.Id1
And Locking.Id2     = Waiting.Id2
And Locking.Block   > 0
And Waiting.Request >0;


KILL_STMT
--------------------------------------------------
alter system kill session '762,397,@2';
alter system kill session '16,8893,@1';

SQL Sinppet : Foreign Key Columns not Inside an Index

--select 'create index '||child_table||'_aidx on '||child_table||'('||child_table_column||') Tablespace Tbxindexes Online Nologging Compute Statistics;' from (
SELECT p.table_name   AS parent_table,
  pc.column_name      AS parent_table_column,
  C.Table_Name        AS Child_Table,
  Cc.Column_Name      AS Child_Table_Column ,
  C.R_Constraint_Name As Parent_Constraint_Name,
  C.Constraint_Name   As Child_Constraint_Name,
  cc.position
FROM user_constraints p,
  user_constraints c,
  user_cons_columns pc,
  user_cons_columns cc
WHERE p.constraint_name = c.r_constraint_name
AND p.constraint_name   = pc.constraint_name
AND pc.position         = cc.position
And C.Constraint_Name   = Cc.Constraint_Name
And C.Table_Name        =  '<<child_table_name_here>>'
--And C.Table_Name   like  '<<child_table_name_here>>_%'
And Not Exists (Select 1 from user_ind_columns Inds
Where Inds.Table_Name = C.Table_Name
And Inds.Column_Name=Cc.Column_Name
And Inds.Column_Position = Cc.Position) 
 Order By Parent_Table,child_table,child_constraint_name, Position;
--);

Useful in situations where foreign keys need to be indexed.

Relational Division

Relational division normally requires two not exists sub queries used in three different tables. Below sql snippet shows how division could be employed when all the data is in a single table.

 PROJ  SUBJ
----- -----
    1     1
    1     2
    1     3
    2     3
    2     1
    3     1
    3     2
    3     3
    4     1
    5     2
    6     3                    

Hypothetical situation is that, there are several projects and each project uses number of different subjects. Idea is to find projects that are uses all the subjects.

The theory


theory implementation

select distinct proj from x where proj not in 
(select distinct proj from (select * from 
(select distinct proj from x) t1, 
(select distinct subj from x)  t2) t3
where (proj,subj) not in (select proj,subj from x));

Using With clause in Oracle

with t1 as (select DISTINCT proj from x),
t2 as (select distinct subj from x)
select distinct proj from x where proj not in 
( select proj from t1,t2 where (proj,subj) not in (select * from x));

Using not exists sub queries

select * from (select distinct proj from x) projects
where not exists(
  select * from (select distinct subj from x) subject
  where not exists
    (select * from x
      where x.proj = projects.proj
      and x.subj = subject.subj
      ));

SQL Sinppet : Parent/Child tables

SQL to find out foreign key relationships between tables

select p.table_name as parent_table,
pc.column_name as parent_table_column,
C.Table_Name As Child_Table,
Cc.Column_Name As Child_Table_Column , 
C.R_Constraint_Name As Parent_Constraint_Name,
c.constraint_name as child_constraint_name
from user_constraints p, user_constraints c,
user_cons_columns pc, user_cons_columns cc
where p.constraint_name = c.r_constraint_name
and p.constraint_name = pc.constraint_name
and pc.position = cc.position
and c.constraint_name = cc.constraint_name
and c.table_name = 'table name';

SQL Design Patterns

Same table refered twice in a multi level subquery.

SELECT calc_scheme.scheme_id
FROM calc_scheme
WHERE NOT EXISTS
  (SELECT 'PRODUCT'
   FROM calc_rule_product
   WHERE calc_rule_product.scheme_id = calc_scheme.scheme_id)
OR EXISTS
  (SELECT 'PRODUCT'
   FROM calc_rule_product
   WHERE calc_rule_product.scheme_id = calc_scheme.scheme_id
   AND 'FLT' = calc_rule_product.code
   AND 'FLT' IS NOT NULL)

Becomes

SELECT calc_scheme.scheme_id
FROM calc_scheme,
    (SELECT scheme_id,
     code
   FROM calc_rule_product)
x
WHERE(calc_scheme.scheme_id = x.scheme_id(+)
 AND x.rowid IS NULL) OR(x.scheme_id = calc_scheme.scheme_id
 AND 'FLT' = x.code
 AND 'FLT' IS NOT NULL) 

For WHERE EXISTS single table sub-queries, try a Semi-Join. If this doesn't work, try rewriting the query as a join

SELECT *
FROM   table_a
WHERE  EXISTS (
       SELECT 1
       FROM   table_b b, table_c c
       WHERE  b.key2 = c.key2
       AND    b.key1 = a.key1);

becomes

SELECT a.*
FROM   table_a a
, (    SELECT DISTINCT b.key1
       FROM   table_b b, table_c c
       WHERE  b.key2 = c.key2
  ) d
WHERE  a.key1 = d.key1

For WHERE NOT EXISTS sub-queries, try re-writing the SQL as an outer join with a b.ROWID IS NULL clause. eg.

SELECT a.*
FROM   table_a a
WHERE  NOT EXISTS (
       SELECT 1
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a , table_b b
WHERE  a.col1 = b.col1 (+)
AND    b.rowid IS NULL

For IN and NOT IN sub-queries, eliminate correlated sub-queries, they must use a Nested Loops join. eg.

SELECT a.*
FROM   table_a a
WHERE  col2 IN (
       SELECT col2
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a
WHERE  (col1, col2) IN (
       SELECT col2
       FROM   table_b)

For IN sub-queries that are not correlated, try an ORDERED hint and a USE_HASH or USE_MERGE hint. eg:

SELECT /*+ ORDERED USE_HASH(a)*/ *
FROM table_a a
WHERE col1 IN (
      SELECT col1
      FROM   table_b b)

In this way, Oracle can run the outer query and the sub-query independently and perform a hash join.


For correlated col = (sub-query) sub-queries, Oracle must use a Filter. Try to re-write as a table join. eg.

SELECT a.*
FROM   table_a a
WHERE  col2 = (
       SELECT max(col2)
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes

SELECT a.*
FROM   table_a a,
(      SELECT col1, max(col2)
       FROM   table_b
       GROUP  BY col1
) b
WHERE  a.col1 = b.col1