Archive for the ‘BestPractise’ Category

Better way to create plan_table

January 3, 2007

This example shows how to better create plan table.

Time Needed : 10min

1. @?/rdbms/admin/utlxplan.sql
2. Create user utils identified by xxxx
tablespace tools
3. grant create session,create table to utils
4. create plan table in util schema as global temporary table . (use step 1 to copy the definition). on commit preserver rows. So that two ppl with same id does not create proble,.
5. Create a public synonym for this plan table so all develoers can use concurrecny issue/no blocking.

syntax is
explain plan set statement_id = ‘xxx’
[into owner.table_name]
for statement;

script to see the out put for plan is
@?/rdbms/admin/utlxpls.sql OR better in 9i

select plan_table_output
from table(dbms_xplan.display(‘plan_table’,null,’serial);

To see the plan for v$sql_plan is or create table dynamic_view as select…..v$sql_plan and use in dbms_xplan.
insert into plan_table
select rawtohex(address)||’-‘||child_number,
from v$sql_plan
where (address,child_number)
(select address,child_number from v$sql where sql_text like ‘select * from t where t1%’ and child_number =0)


Better Autotrace setup.

January 3, 2007

This example shows how to setup the autotrace,possible options and understanding out of the output.

Time Needed : 20min

How to setup autotrace.

1. cd to $ORACLE_HOME/rdbms/admin
2. Create a user with create table and create public synonym priv.
3. make plan_table under this user(for details see the note in the performance tab. – better way to create plan_table)
4. login as sysdba and run @plustrace.sql
5. grant plustrace to public.

Autotrace options

1. set autotrace off – set the tracing off
2. set autotrace on explain : Runs as normal and shows the execution plan.
3. set autotrace on statistics – Runs as normal and shows the statistics.
4. set autotrace on – same as 3 and 4 together.
5. set autotrace traceonly – same as 4 but does not print the output. GOOD ONE if you don’t interested in o/p.
6. set autotrace traceonly statistics – shows only execution statistics does not print output.
7. set autotrace traceonly explain – show only query plan. for select does not run the sql. for insert/update/delete it still runs.

Good formatting option(better to copy in login.sql)
column id_plus_exp format 990 heading I
column parent_id_plus_exp format 990 heading P
column plan_plus_exp format a60
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44

if you done need then can use
column object_node_plus_exp NOPRINT
column other_tag_plus_exp NOPRINT
column other_plus_exp NOPRINT

Analysis of Autotrace

Recursive calls : Number of SQL statement execute in order to execute your sql. If you rerun this should go to . Else it might have trigger or something that fires more sql.(required for parsing,requesting additional space,temp space etc.) in locally managed tablespace recursive sql gets less for space mgmt.

DB Block Gets : Total Number of blocks read from buffer cache in current mode.
1. Normally its 0 for select. but may be 4+ for reading segment header.
2. Non zero for DMLS.

Consistent Gets : Number of times the block requested in buffer cache.May require read aside to rollback.Normally its a/r+N(what is a,r,N ?) = Total records/Record to read+blocks. Last two are most important. With various different plan try to reduce this. set arraysize 10,100,1000,5000 and see the effect.

Physical Reads : This is the disk read. Should get zero in second iteration.This may be done for sorting as well if it goes to memory because of not enough sort_area_size. See the sorts section also to confirm this. INSERT /*+ append */(beneficial in noarchivelo,or nologging on table but nologging needs db backup next time).even the indexes can generate redo. so mark index unusuable and rebuild nologging after load(parallel too).

Redo size : Total Amount of Redo generated in terms of bytes.

Bytes Sent via SQL*Net to client : Total Number of bytes sent to client from server.

Bytes Received via SQL*Net from client : Total Number of Bytes Received from client.

SQL*Net roundtrips to/from client. : This includes round trip featches for mulitple-row set. look at arraysize to reduce this.

Sorts(Memory) : Sorts done in user’s session memory.controlled via sort_area_size.(auto via pga_aggregate_target)

Sorts(disk) : Sort that used the disk because the sort exceeded the memory.

Rows Processed : Rows processed by modification/select statement.

not in and not exists compared

January 3, 2007

This example compare not in and not exists.When to use them ?

Time Needed : 10Min.


They both are not same when that column can contain null and see other performance implications.

create table big as select * from all_objects;
create table small as select * from
all_objects where rownum < 10;

alter table big modify object_id null;
alter table small modify object_id null;
update small set object_id = null where rownum = 1;
create unique index big_idx on big(object_id);
create unique index small_idx on small(object_id);

analyze table big compute statistics
for table
for all indexes
for all indexed columns;

analyze table small compute statistics
for table
for all indexes
for all indexed columns;

set autotrace traceonly
select * from big
where NOT EXISTS (select null
from small
where small.object_id = big.object_id)

select * from big
where object_id NOT IN ( select object_id
from small )

first returns 16k records and second ‘no rows selected’. Find why different ?

update small set object_id = -1 where object_id is null;
alter table small modify object_id NOT NULL;
analyze table small compute statistics
for table
for all indexes
for all indexed columns;

Select * from big
where NOT EXISTS (select null
from small
where small.object_id = big.object_id)

Select * from big b0
where object_id IS NOT NULL
and object_id NOT IN ( select object_id
from small )

set antijoin to help this
alter session set always_anti_join = MERGE

Select * from big b1
where object_id is not null
and object_id NOT IN ( select object_id
from small )

alter session set always_anti_join = HASH
hints in query could be MERGE_AJ,HASH_AJ

Select /*+ HASH_AJ */ * from big b2
where object_id is not null
and object_id NOT IN ( select object_id
from small )

Backout Procedure
drop table small;
drop table big;