1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Tuning also required with small tables:

=> Introduction & about
=> The cost of small but frequent operations
=> Reducing number of operations
=> Avoiding calls to database and kernel
=> Bulk binding and collections
=> Reducing cost of operations
=> Small tables and indexing
=> Optimizer and small tables
=> DUAL table issues
=> Summary

The Cost of Small Operations
=> Always start tuning from the biggest
resource eater
=> Operations on small tables are lightweight
=> A single operation on small table doesn?t
usually cause any performance impact
=> Ultrafrequently executed small operations
contribute significantly to response time
=> Loops, lookups, reading/modifying control tables
=> Pseudovariables, sys_context()
=> DUAL table

Reducing Number of Operations
=> The best way for tuning the speed of an
operation, is not to do the operation
=> Start from the highest level - business rules
=> The usual path
=> Design
=> Application code
=> Eventually ending up to database
=> PL/SQL
=> SQL execution plans

Remove Procedural Code
=> Few complex SQL statements instead many
trivial SQLs in PL/SQL procedures
=> Allow Oracle to optimize the operations, that?s what
we are paying them for!
=> If PL/SQL has to be used, work with sets of
data instead of single rows
=> Oracle can use array processing
=> BULK COLLECT INTO for SQL
=> FORALL clause for DML

A Data Warehouse Example
=> Presidential elections
=> Citizens fill out their state name and
preferred president's name
=> Data is loaded to interface table
=> ETL process replaces names with
corresponding Ids
=> Using lookup tables
=> ETL process populates the fact table

Interface and Fact Tables
SQL> select * from elections;
TICKET_ID CANDIDATE_NAME STATE_NAME
---------- -------------------- --------------------
1 Howard Dean Arizona
2 John Kerry California
3 George W. Bush Illinois
4 Howard Dean New Jersey
5 John Kerry New York
6 George W. Bush Texas
...
(Total 100000 rows / 440 blocks / 3520kB)
create table elections_fact (
ticket_id number,
candidate_id number,
state_id char(2)
);

Dimension Tables
create table candidates (
candidate_id number primary key,
candidate_name varchar2(20) not null);
insert into candidates values (1, 'George W. Bush');
insert into candidates values (2, 'Howard Dean');
insert into candidates values (3, 'John Kerry');
...
create table states (
state_id char(2) primary key,
state_name varchar2(20));
insert into states values ('AL', 'Alabama');
insert into states values ('AZ', 'Arizona');
insert into states values ('CA', 'California');
insert into states values ('CO', 'Colorado');
...
.
create or replace procedure transform as
elect_rec elections%ROWTYPE;
fact_rec elections_fact%ROWTYPE;
cursor c is select * from elections;
begin
open c;
loop
fetch c into elect_rec;
exit when c%NOTFOUND;
fact_rec.ticket_id := elect_rec.ticket_id;
select state_id into fact_rec.state_id from states
where state_name = elect_rec.state_name;
select candidate_id into fact_rec.candidate_id from
candidates
where candidate_name = elect_rec.candidate_name;
insert into elections_fact values fact_rec;
end loop;
close c;
end;
/
Elapsed Time: 56.48 seconds
The PL/SQL
Loop Way
very inefficient
. id.info 11/37
Collections and Bulk Binding
create or replace procedure transform_bulk as
i number;
type fact_tab_type is table of elections_fact%ROWTYPE;
fact_tab fact_tab_type;
begin
select
elections.ticket_id,candidates.candidate_id,states.state_id
bulk collect into fact_tab
from elections, candidates, states
where
candidates.candidate_name=elections.candidate_name
and states.state_name = elections.state_name;
forall i in 1..fact_tab.COUNT
insert into elections_fact values fact_tab(i);
end;
/
Elapsed Time: 2.86 seconds
(This is a very trivial example processing whole data at once,
large datasets should be processed in smaller parts)

Pure SQL
=> Pure SQL is the fastest and most efficient
=> But sometimes not flexible enough
=> Analytic functions provide new opportunities
=> Also MERGE command, multitable inserts
insert into elections_fact
select elections.ticket_id, candidates.candidate_id,
states.state_id
from elections, candidates, states
where candidates.candidate_name =
elections.candidate_name
and states.state_name = elections.state_name;
Elapsed Time: 1.26 seconds

Comparision
time consistent redo redo
sec. reads entries size
PL/SQL loop 56.48 700562 101059 25793728
Bulk bind&collect 2.86 1315 1662 2280920
Pure SQL 1.26 775 528 1926164
elections table size 440 blocks / 3520kB
elections_fact table size 220 blocks / 1760kB
SELECT *
FROM ELECTIONS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 100001 2.84 2.85 0 100009 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100003 2.84 2.85 0 100009 0 100000
.
PL/SQL Arrays
=> Avoid table accesses at all
=> No cache buffers chains latching required
=> Runtime data, nonvolatile data, great for lookups
=> Can be stored in package variables
=> Can be passed as parameters
=> Associative arrays (index-by tables)
=> Hash tables by structure
=> Indexed using PLS_INTEGER, BINARY_INTEGER,
VARCHAR2
=> Can use table functions for conventionally
selecting data from arrays (9i, 10g)

Tight Loops in Execution Plans
=> It's not only user-programmed loops that benefit
from indexes
=> Nested loop queries
=> Index inner table join column
=> Composite indexes may help avoid table block get
=> Index Organized Tables don't need table block gets
(unless accessing overflow segment)
=> Correlated subqueries, not exists subquery
=> if not transformed to hash or sort-merge join (using
_always_anti_join or _always_semi_join which
should normally be set to choose)
.
Indexing Small Tables
=> Even a one-row table can benefit from
index!
=> Table scan requires additional gets on
segment header block
=> Extent map and high-water mark are read
=> 5 additional LIOs for 8i
=> 3 additional for 9i, 10g
=> Additional LIOs mean additional latching
(cache_buffer_chains)
=> Scan CBC chain, buffer handle get, buffer pin
=> All of the above means extra CPU usage and reduced
scalability

Indexing Small Tables
=> Conventional index scans don?t have
segment header read overhead!
=> Except fast full scan
=> IND$ base table contains index header
block location
=> As for other objects, index information is cached in
dictionary cache (actually in library cache as well)
=> Index root block is always kept next to
segment header
=> Even when root block is split and index height
changes

FTS on Small Tables
SQL> create table lookup (id number, name varchar2(100));
SQL> insert into lookup select rownum, name from sys.obj$
2> where rownum <=1000;
SQL> select blocks from user_tables
2> where table_name = 'LOOKUP';
BLOCKS
----------
4
SQL> select name from lookup where id = 500;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
(Cost=3 Card=1 Bytes=20)
1 0 TABLE ACCESS (FULL) OF 'LOOKUP' (TABLE)
(Cost=3 Card=1 Bytes=20)
Statistics
----------------------------------------------------------
7 consistent gets
. id.info 19/37
Indexing Small Tables
SQL> create index i on lookup(id);
SQL> select name from lookup where id = 500;
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes= 20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'LOOKUP' (TABLE)
(Cost=2 Card=1 Bytes=20)
2 1 INDEX (RANGE SCAN) OF 'I' (INDEX) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
4 consistent gets
SQL> create unique index i on lookup(id);
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'LOOKUP' (TABLE)
(Cost=2 Card=1 Bytes=20)
2 1 INDEX (UNIQUE SCAN) OF 'I' (INDEX (UNIQUE)) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
3 consistent gets

Using Index Organized Tables
SQL> create table lookup (id number primary key, name varchar2(100))
organization index;
SQL> insert into lookup select rownum, name from sys.obj$
where rownum <=1000;
SQL> validate index SYS_IOT_TOP_12125;
SQL> select height from index_stats;
HEIGHT
----------
2
SQL> select name from lookup where id = 500;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=20)
1 0 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_12125' (INDEX(UNIQUE))
(Cost=1 Card=1 Bytes=20)
Statistics
----------------------------------------------------------
2 consistent gets

Using Single Table Hash Clusters
SQL> create cluster c (id number(10,0))
size 100 single table hashkeys 1000 hash is id;
SQL> create table lookup (id number(10,0), name varchar2(100))
cluster c(id);
SQL> insert into lookup select rownum, name from sys.obj$ where
rownum <=1000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=20)
1 0 TABLE ACCESS (HASH) OF 'LOOKUP' (CLUSTER (HASH))
(Cost=1 Card=1 Bytes=20)
Statistics
----------------------------------------------------------
1 consistent gets

Single Table Hash Cluster
SQL> create cluster c (id int)
size 100 single table hashkeys 1000 hash is id;
=> The fastest way for lookups from tables
=> Even faster than ?Single block? IOT for lookups
=> HASHKEYS is rounded to next higher prime
=> SIZE bytes is allocated for rows
corresponding to each hash value
=> If more data than allocated is inserted for a
value, overflow blocks are created =>
performance drops
=> Best performance with no hash key collisions

Optimizer Decisions
=> Unique index and unique constraint
=> Tables having equality predicates on unique columns
are known to return only one row
=> CBO puts them first in join order
=> Unique constraint has to be VALIDATED if on nonunique
index
=> If getting counts, B-tree index can only be
used if:
=> at least one columns in index is NOT NULL or
=> query contains IS NOT NULL on an indexed column

CBO Issues
=> Older versions didn?t take segment header
block into account on full segment scan
=> Caused table scans to be preferred over index scans
for tiny tables
=> _table_scan_cost_plus_one
=> Adds one ?block get? to segment scan cost
=> 8i defaults to false
=> 9i, 10g default to true
=> Affects both table scan and index fast full scan

Keeping Small Tables / Indexes
=> Not a big issue from 8i, with touch-count
based buffer aging algorithm
=> Frequently accessed buffers now survive
excessive full segment scans and big
cached LOB scans
=> Keep segments in keep buffer pool
=> db_keep_cache_size (9i, 10g)
=> buffer_pool_keep (..8i)
=> create table t(a int) storage (buffer_pool keep);
=> alter table t storage (buffer_pool default);

Issues With DUAL Table
=> Scanned very often
=> Select <anything> from dual;
=> Variable := USER
=> Variable := SYS_CONTEXT('...
=> Causes Full Table Scan every access
=> 5 LIOs every scan in 8i
=> 3 LIOs every scan in 9i
=> Avoid selecting from DUAL if possible
=> Or operations which cause frequent selects from dual
=> At least as long they do FTS on SYS.DUAL table

Speeding Up DUAL Access
=> Local Custom FASTDUAL Table
=> IOT or Single-Table Hash Cluster
=> Requires modifications in application code
=> Local Custom DUAL Table
=> Overrides public synonym for SYS.DUAL
=> Can be protected from accidential changes using
trigger and alter table disable table lock
=> If dropped, normal dual will be accessed instead
=> Analyze it => CBO should know there's only one row
=> X$DUAL => needs some tricks (one issue
with materialized view refreshes)
=> FAST DUAL access path in 10g

X$DUAL Example
Under SYS:
SQL> create view xdual as select dummy from x$dual;
SQL> select * from xdual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
(Cost=24 Card=100 Bytes=3200)
1 0 FIXED TABLE (FULL) OF 'X$DUAL' (TABLE (FIXED))
(Cost=24 Card=100 Bytes=3200)
Statistics
----------------------------------------------------------
0 consistent gets
SQL> create view xdual as select dummy from x$dual where rownum = 1;
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=24 Card=1 Bytes=2)
1 0 VIEW OF 'XDUAL' (VIEW) (Cost=24 Card=1 Bytes=2)
2 1 COUNT (STOPKEY)
3 2 FIXED TABLE (FULL) OF 'X$DUAL' (TABLE (FIXED))
(Cost=24 Card=100 Bytes=200)

X$DUAL Example cont'd
Under SYS:
create or replace view xdual as select dummy from x$dual
where inst_id = USERENV('Instance')
and rownum = 1
SQL> grant select on xdual to public;
Under application user:
SQL> create synonym dual for sys.xdual;
SQL> select sysdate from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=24 Card=1)
1 0 VIEW OF 'XDUAL' (VIEW) (Cost=24 Card=1)
2 1 COUNT (STOPKEY)
3 2 FIXED TABLE (FULL) OF 'X$DUAL' (TABLE (FIXED))
(Cost=24 Card=1 Bytes=13)

More DUAL-related Issues
SQL> var v3 varchar2(20)
SQL> exec :v3:=user;
PL/SQL procedure successfully completed.
PARSING IN CURSOR #1 len=23 dep=0 uid=34 oct=47 lid=34 tim=1053549856429527
hv=2522751703 ad='520bd484'
BEGIN :v3:=user; END;
END OF STMT
PARSE #1:c=0,e=1819,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1053549856429506
BINDS #1:
bind 0: dty=1 mxl=32(20) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0010 size=32
offset=0
bfp=b6a9ffe0 bln=32 avl=00 flg=05
=====================
PARSING IN CURSOR #2 len=25 dep=1 uid=34 oct=3 lid=34 tim=1053549856435104
hv=4010258057 ad='5203a54c'
SELECT USER FROM SYS.DUAL
END OF STMT
PARSE #2:c=0,e=267,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1053549856435085
BINDS #2:
EXEC #2:c=0,e=146,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1053549856435437
FETCH #2:c=0,e=212,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,tim=1053549856435704

FAST DUAL in 10g
=> A new access path
=> Hardcoded operation
=> No tablescan or buffer gets involved
=> No X$DUAL issues
=> Good for RAC (less global cache traffic)
=> Used only if selecting a constant or variable
from DUAL
=> select * from dual causes FTS on the table
=> There must be no local objects named DUAL
=> Controlled using _fast_dual_enabled
parameter
=> If false, the old FTS on DUAL access path is used
FAST

FAST DUAL Example
SQL> select sysdate from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
1 0 FAST DUAL (Cost=2 Card=1)
Statistics
----------------------------------------------------------
0 consistent gets
SQL> alter session set "_fast_dual_enabled"=false;
SQL> select sysdate from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
1 0 TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1)
Statistics
----------------------------------------------------------
3 consistent gets

Heavily Modified Tables
=> Buffer busy waits is the main problem
=> Rows have to be spread up more
=> PCTFREE trick
=> For both tables and fairly unique indexes
=> Hash partitioning
=> Multiple freelists for FIFO/Queue tables
=> Allows inserts to be spread into more blocks
=> In ASSM IO is spread anyway
=> Hash Clustering to spread rows
=> Alter table <t> minimize records_per_block

Reducing Locking Activity
=> Disable TM enqueue get on DML
=> alter table <table name> disable table lock;
=> No TM lock acquisition for preventing DDL
during the transaction
=> TX lock is still acquired
=> Great in OPS & RAC environment
=> Less global enqueue services traffic
=> Have to enable table lock for DDL
=> alter table <table name> enable table lock;

Customer Case
=> DW data transformation batch job ?too slow?
=> Ran as a single tight PL/SQL loop
=> Indexed small lookup tables as a quick fix
=> Composite indexes including queried columns
=> Job completion time from 16hr to 9hr
=> Parallelized manually using rowid ranges
=> 4 parallel jobs, completion time from 9hr to 2hr
=> Used pure SQL statements for transforming
=> Completion time under one hour serially

Summary
=> Small tables DO need to be tuned!
=> First reduce number of accesses to
tables
=> Bulk binding, collections
=> Speed up table access
=> Composite indexes, IOTs, hash tables
=> Regular DUAL is very inefficient table
=> Manual workarounds, tuned DUAL, X$DUAL
=> FAST DUAL access path

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy