beacon ora perf class 1

2007-12-251www.oracleact.com

Oracle Performance Tuning

Tamilselvan G

B

e

a

c

o

n

In

f

o

t

e

c

h

C

o

r

p

o

r

a

t

io

n

C

l

a

s

s

-

1


Contents

1. Table Types Normal (heap) table, Index Cluster, Hash Cluster, IOT, Partition,

Global Temporary Table, External Table

2. Table Access When to use Full Table Scan, NO ROT

3. Avoiding accidental table scans Queries involved NOT EQUAL,

Searching for NULL values, Using a function on the indexed column.

4. Functional Indexes

5. IOT Including clause, over flow

6. Using Concatenated Index

7. Index Merges Hint AND_EQUAL(tablename, index1, index2,)

8. Searching for Ranges Unbounded Range Scan ( < or >) ; Bounded Range Scans

( between 2 values) ; Range Look Up ( value between lowvalue_column and

highvolue_column)

9. Queries Involving OR Useful Hint USE_CONCAT.

10. INLIST Iterator

11. Fast Full Index Scan

12. Bind Variable and 5 Percent Guess

13. Optimizing Full Table PCTFREE and PCTUSED

14. Update Statement


1 Table Types

1.1 Relation Table Basic Structure to hold relational data

Heap Table

Index Cluster

Hash Cluster

Partition

Global Temporary Table

External Table

1.2 Object Table Uses Object Type for a column definition

INDEX CLUSTER

A cluster provides an optional method of storing table data.

A cluster is made up of a group of tables that share the same data blocks.

The tables are grouped together because they share common columns and

are often used together. Example, DEPTNO column in EMP and DEPT tables.

To find or store a row in an indexed table or cluster, a minimum of two

(there are usually more) I/Os must be performed:

One or more I/Os to find or store the key value in the index

Another I/O to read or write the row in the table or cluster


CREATE CLUSTER EMP_DEPT (deptno NUMBER(3))

SIZE 7000

TABLESPACE users

STORAGE (INITIAL 200K NEXT 300K MINEXTENTS 2

MAXEXTENTS 20 PCTINCREASE 33);

Create an index on the cluster.

CREATE INDEX EMP_DEPT_INDEX

ON CLUSTER EMP_DEPT

TABLESPACE users

STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 2

MAXEXTENTS 10 PCTINCREASE 33);

Without an index on the cluster, you cannot insert rows into EMP and DEPT

tables.


Create tables on the cluster.

CREATE TABLE DEPT (

deptno NUMBER(3) PRIMARY KEY,

deptname varchar2(30) )

CLUSTER EMP_DEPT (deptno);

CREATE TABLE EMP (

empno NUMBER(5) PRIMARY KEY,

ename VARCHAR2(15) NOT NULL, salary number(6),

deptno NUMBER(3) REFERENCES dept)

CLUSTER EMP_DEPT (deptno);

Insert rows:

insert into dept values (10,'Sales');

insert into emp values (1001, 'Tamil',1000, 10);

insert into emp values (1002, 'Tandon',2000,10) ;

insert into dept values (20,'Marketing');

insert into emp values (1003,'Muthu',3000,20);

insert into emp values (1004,'Ravi',4000,20) ;

commit;


SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,

2 dbms_rowid.rowid_block_number(rowid) block#,

3 a.*

4* from dept a ;

FILE# BLOCK# DEPTNO DEPTNAME

---------- ---------- ---------- ------------------------------

3 3643 10 Sales

3 3644 20 Marketing

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,

2 dbms_rowid.rowid_block_number(rowid) block#,

3 a.*

4* from emp a ;

FILE# BLOCK# EMPNO ENAME SALARY DEPTNO

------ - --------- ---------- --------------- ---------- ----------

3 3643 1001 Tamil 1000 10

3 3643 1002 Tandon 2000 10

3 3644 1003 Muthu 3000 20

3 3644 1004 Ravi 4000 20

Sh

arin

g

of B

locks

am

on

g 2

tab

les


Common ROWIDs among the tables:

SQL> select rowid from emp

2 intersect

3 select rowid from dept ;

ROWID

------------------

AAAQrtAADAAAA47AAA

AAAQrtAADAAAA48AAA

.

1. ROWID is not UNIQUE in the Database

but in the table only.

2. You should not use clusters for tables

that are frequently accessed

individually.

3. If all the rows for a given cluster key

value cannot fit in one block, the blocks

are chained together to speed access to

all the values with the given key. The

cluster index points to the beginning of

the chain of blocks, each of which

contains the cluster key value and

associated rows. If the cluster SIZE is

such that more than one key fits in a

block, blocks can belong to more than

one chain.


HASH CLUSTER

Oracle document says

To use hashing, you create a hash cluster and load tables into it. The database

physically stores the rows of a table in a hash cluster and retrieves them

according to the results of a hash function.

Oracle Database uses a hash function to generate a distribution of numeric

values, called hash values, that are based on specific cluster key values. The

key of a hash cluster, like the key of an index cluster, can be a single column or

composite key (multiple column key). To find or store a row in a hash cluster, the

database applies the hash function to the cluster key value of the row. The

resulting hash value corresponds to a data block in the cluster, which the

database then reads or writes on behalf of the issued statement.

The database uses a hash function to locate a row in a hash cluster; no I/O is

required. As a result, a minimum of one I/O operation is necessary to read or

write a row in a hash cluster.


HASH CLUSTER

CREATE CLUSTER call_detail_cluster (

telephone_number NUMBER,

call_timestamp NUMBER ,

call_duration NUMBER )

HASHKEYS 10000 HASH IS telephone_number

SIZE 256;

CREATE TABLE call_detail (

telephone_number NUMBER,

call_timestamp NUMBER ,

call_duration NUMBER ,

other_info VARCHAR2(30) )

CLUSTER call_detail_cluster (

telephone_number, call_timestamp, call_duration );

When to HASH Cluster

1. Predicate has equality condition

2. Table is static.


2 When To Use Full Table Scan

Many experts suggest to use full table scan when any or all of the following occur:

More than 15 % of rows from the table are selected

More than 25 % of Index blocks are accessed

When a column has low selectivity and it is being used in the WHERE

clause

It is really very hard to determine whether full table scan is appropriate or not.

There are many factors involved row length, number of rows in the table,

whether LOB is stored in-line or out-of-line, number of rows selected etc

If the table and indexes are analyzed, then CBO will decide whether a full table or

index lookup is appropriate based on the statistics available at that time

and predicates in the query.


Disabling Index Scan

Concatenating with the character column or adding 0 to number column will

disable index scan.

3 Avoiding accidental full table scan

3.1 Greater Than and NOT EQUAL

SQL> var b1 number ;

SQL> select * from ssn_region where reg_id > :b1 ;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=500 Bytes=13500)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SSN_REGION' (Cost=3 Card=500 Bytes=13500)

2 1 INDEX (RANGE SCAN) OF 'SSN_REGION_PK' (UNIQUE) (Cost=2 Card=90)

Note: When the value of bind variable is not known, Oracle assumes 5 % of the table rows

will be returned. (Card =500). The table has 10,000 rows.

However, there are some situation, you may want to use full table

scan, then you just add a hint /*+ full(table_name) */ . Use

parallel hint, if the table is really very big, /*+ full(table_name)

parallel(table_name, degree_of_parallelism) */ .


SQL>select * from ssn_region where reg_id :b1 ;

Execution Plan

----------------------------------------------------------


1 0 TABLE ACCESS (FULL) OF 'SSN_REGION' (Cost=6 Card=9999 Bytes=269973)

Since Reg_id is unique in the table, Oracle assumes (all rows - 1 row) will be returned,

hence it opts for FULL table scan.

3.2 NULL Search

SQL> select * from ssn_region where low_ssn_num is null ;

Execution Plan

----------------------------------------------------------



Note: A null search stops index usage.

Use default value instead of NULL, if the

column is frequently checked for NULL value


3.3 Function on Indexed Column

A function on a indexed will force the CBO to opt for full table scan.

SQL>select * from ssn_region where to_char(low_ssn_num) = :b1 ;

Execution Plan

----------------------------------------------------------



Note: Rewrite the query.

Example: to_char(low_ssn_num) = :b1 is same as

low_ssn_num = to_number(:b1)

4. FUNCTIONAL INDEXES

SQL> select email_status, count(*) from cust_order group by email_status;

EMAIL_STATUS COUNT(*)

------------------------------ ----------

Not Sent 299

Sent 23770

SQL> alter session set QUERY_REWRITE_ENABLED=TRUE;

Session altered.

SQL> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;

Session altered.


SQL> create index cust_order_idx on cust_order

(decode(email_status,'Not Sent',1,NULL)) ;

Index created.

SQL> analyze table cust_order compute statistics

2 for table

3 for all indexes

4 for all indexed columns ;

Table analyzed.

SQL> set autot on expla

SQL> select count(*) from cust_order where email_status = 'Not Sent' ;

COUNT(*)

----------

299

Execution Plan

----------------------------------------------------------


1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'CUST_ORDER' (Cost=3 Card=241 Bytes=4097)

SQL> select count(*) from cust_order

where decode(email_status,'Not Sent',1,NULL) = 1 ;

COUNT(*)

----------

299


SQL> analyze index cust_order_idx validate structure;

Index analyzed.

SQL> select lf_rows from index_stats;

LF_ROWS

----------

299

----------------------------------------------------------



2 1 INDEX (RANGE SCAN) OF 'CUST_ORDER_IDX' (NON-UNIQUE) (Cost=1 Card=299 Bytes=5083)

SQL> select table_name, index_name, num_rows, last_analyzed

from user_indexes where table_name = 'CUST_ORDER' ;

TABLE_NAME INDEX_NAME NUM_ROWS LAST_ANALYZED

------------ ----------------------- ---------- --------------------

CUST_ORDER CUST_ORDER_IDX 299 18-FEB-2005 13:47:32

SQL> select email_status, count(*) from cust_order group by email_status;

EMAIL_STATUS COUNT(*)

------------------------------ ----------

Error 100

Not Sent 300

Sent 23770


SQL> drop index cust_order_idx;

Index dropped.

SQL> create index cust_order_idx on cust_order

(decode(email_status,'Not Sent',1,'Error',2,NULL));

Index created.

SQL> analyze table cust_order compute statistics

2 for table

3 for all indexes

4 for all indexed columns;

Table analyzed.

SQL> select table_name, index_name, num_rows, last_analyzed

2 from user_indexes where table_name = 'CUST_ORDER' ;

TABLE_NAME INDEX_NAME NUM_ROWS LAST_ANALYZED

------------ ----------------------- ---------- --------------------

CUST_ORDER CUST_ORDER_IDX 400 18-FEB-2005 14:07:49



where decode(email_status,'Not Sent',1,'Error',2,NULL) = 2 ;

COUNT(*)

----------

100

Execution Plan

----------------------------------------------------------



2 1 INDEX (RANGE SCAN) OF 'CUST_ORDER_IDX' (NON-UNIQUE) (Cost=1 Card=100 Bytes


where decode(email_status,'Not Sent',1,'Error',2,NULL) = 1 ;

COUNT(*)

----------

300


Execution Plan

----------------------------------------------------------



2 1 INDEX (RANGE SCAN) OF 'CUST_ORDER_IDX' (NON-UNIQUE) (Cost=1 Card=300 Bytes


5. IOT - Index Organized Table

Well suited for time interval data.

CREATE TABLE COUNTRIES

(

COUNTRY_ID CHAR(2 BYTE) CONSTRAINT COUNTRY_ID_NN NOT NULL,

COUNTRY_NAME VARCHAR2(40 BYTE),

CURRENCY_NAME VARCHAR2(25 BYTE),

CURRENCY_SYMBOL VARCHAR2(3 BYTE),

REGION VARCHAR2(15 BYTE),

CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY (COUNTRY_ID)

)

ORGANIZATION INDEX LOGGING

TABLESPACE TAMIL_LARGE_DATA

PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE ( INITIAL 16K NEXT 100M

MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1

FREELIST GROUPS 1 BUFFER_POOL DEFAULT )


PCTTHRESHOLD 2

INCLUDING COUNTRY_NAME

OVERFLOW

TABLESPACE USERS

PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255

STORAGE ( INITIAL 16K NEXT 100M

MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1

FREELIST GROUPS 1 BUFFER_POOL DEFAULT )

LOGGING

NOPARALLEL;

Note: PCTTHRESHOLD integer specifies percentage of space reserved

in an IOT. Any portion of the row that exceeds the specified threshold

is stored in the overflow area. PCTTHRESHOLD must be a value from

1 to 50.


There are many benefits having a fact table defined as IOT. They are:

1 The space used for PK is not required. Hence, multiple I/Os on Index and data are

not required.

2 Since most of the fact table has date (or time key) as the first column in the table

as well as first column in the index, that is always used in the WHERE clause, that

leads to speed of retrieving the data.

3 IOTs can be partitioned

Disadvantages are:

1 Physical ROWID is NOT maintained on IOT. The table is left with only logical

ROWID.

In case of block corruption, and you want to extract maximum number of rows

from the table, you have to use PK only, not the min or max of ROWID.

2 PK constraint can not be dropped

3 Unique constraints are not allowed

4 Distribution and replication are not allowed

Carefully evaluate the requirements, particularly ad hoc queries.

If the table requires bit-mapped indexes on columns such as Male/Female, Status

(Active/Inactive), YES/NO etc, then IOT is NOT a good choice.

Also, be careful when you use INCLUDE CLAUSE AND OVERFLOW.


6. Using CONCATENATED INDEX

Create concatenated index on multiple columns if all the columns

(or some of the leading columns ) are used in the predicate.

Myth: Always use most selective column as the first column in a concatenated

index, so that Oracle will use the index in the query execution plan.

Create index cust_idx on customer(last_name, first_name) ;

7. Index Merges

SQL> select * from t1

where object_id between 1000 and 1020 and object_name like 'T%' ;

Execution Plan

----------------------------------------------------------


1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=21 Card=1 Bytes=96)

2 1 INDEX (RANGE SCAN) OF 'T1_IDX_1' (NON-UNIQUE) (Cost=2 Card=20)


SQL> select /*+ AND_EQUAL(T1, T1_IDX_1, T1_IDX_2) */ *

from T1

where object_id between 1000 and 1020 and object_name like 'T%' ;

Execution Plan

----------------------------------------------------------



2 1 BITMAP CONVERSION (TO ROWIDS)

3 2 BITMAP AND

4 3 BITMAP CONVERSION (FROM ROWIDS)

5 4 SORT (ORDER BY)



8 7 SORT (ORDER BY)



8 Searching for Ranges

SQL> desc ssn_region

Name Null? Type

-------------------------- -------- ------------------

REG_ID NUMBER(38)

REG_NAME VARCHAR2(20)

LOW_SSN_NUM NUMBER(38)

HIGH_SSN_NUM NUMBER(38)

SQL> select min (low_ssn_num) , max(high_ssn_num), count(*) from ssn_region ;

MIN(LOW_SSN_NUM) MAX(HIGH_SSN_NUM) COUNT(*)

---------------- ----------------- ----------

101010001 101110000 10000

Query -1

select * from ssn_region

where 101010998 between low_ssn_num and high_ssn_num

Rows Row Source Operation

------- ---------------------------------------------------

1 TABLE ACCESS BY INDEX ROWID OBJ#(68289)

100 INDEX RANGE SCAN OBJ#(68293) (object id 68293) -- index used (SSN_REG_LOW)


Query -2


where 701010998 between low_ssn_num and high_ssn_num ;


------- ---------------------------------------------------

0 TABLE ACCESS BY INDEX ROWID OBJ#(68289)

0 INDEX RANGE SCAN OBJ#(68294) (object id 68294) --- index used (SSN_REG_HIGH)

Query -3 Dropped Index SSN_REG_HIGH


where 701010998 between low_ssn_num and high_ssn_num

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.01 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 0.02 0.03 45 47 0 0

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 3 0.03 0.04 45 47 0 0


------- ---------------------------------------------------

0 TABLE ACCESS FULL OBJ#(68289)

Note: When the input value is not in the range, then Oracle chooses FULL table scan.


9 Queries Involving OR


where object_id = 800 or status = 'INVALID' or object_name = 'T1' ;

Execution Plan

----------------------------------------------------------


1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=33 Card=243 Bytes=23328))

SQL> select * from t1 where object_id = 800 or object_name = 'T1' ;

Execution Plan

----------------------------------------------------------




3 2 BITMAP OR


5 4 INDEX (RANGE SCAN) OF 'T1_IDX_1' (NON-UNIQUE) (Cost=1)




SQL> select /*+ USE_CONCAT */ *

from t1 where object_id = 800 or object_name = 'T1' ;

Execution Plan

----------------------------------------------------------


1 0 CONCATENATION






10 Inlist Iterator

Query -1


where object_name IN ( 'T1', 'T1_IDX_1', 'T1_IDX_2' ) ;

Execution Plan

----------------------------------------------------------


1 0 INLIST ITERATOR




10 Inlist Iterator . Contd

Query 2


where object_name IN ( 'T1', 'T1_IDX_1', 'T1_IDX_2' ) or

object_id between 69000 and 70000 ;

Execution Plan

----------------------------------------------------------




3 2 BITMAP OR








11 10 SORT (ORDER BY)



11 FAST_FULL INDEX SCAN

Works best on the not null column if you want to count total number of rows.

SQL> select /*+ INDEX_FFS(t1 t1_idx_1) */ count(*) from t1;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1)


2 1 INDEX (FAST FULL SCAN) OF 'T1_IDX_1' (NON-UNIQUE) (Cost=7

Card=23990)

Parallel Fast Full Index Scan

SQL> select /*+ INDEX_FFS(t1 t1_idx_1) parallel_index(t1 t1_idx_1 8) */

count(*) from t1 ;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1)


2 1 SORT* (AGGREGATE)

3 2 INDEX* (FAST FULL SCAN) OF 'T1_IDX_1' (NON-UNIQUE) (Cost=7 Card=23990)

2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F

3 PARALLEL_COMBINED_WITH_PARENT


13 Bind Variable and 5 Percent Guess

SQL> create table t1 as select * from dba_objects ;

Table created.

SQL> desc t1

Name Null? Type

------------------------- -------- ----------------------------

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(18)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

SQL> var b1 varchar2(30);


where LAST_DDL_TIME > to_date(:b1, 'dd-mon-yyyy hh24:mi:ss') ;


12 Bind Variable and 5 Percent Guess . Contd..

SQL> var b1 varchar2(30);



Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 TABLE ACCESS (FULL) OF 'T1

SQL> analyze table t1 compute statistics ;



Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=1200

Bytes=103200)

1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=33 Card=1200 Bytes=103200)

SQL> select count(*) from t1 ;

COUNT(*)

----------

23992


12 Bind Variable and 5 Percent Guess . Contd..

SQL> select 23992*5/100 from dual;

23992*5/100

-----------

1199.6 ----- Approximately equal to 1200 reported in the Cardinality

Let us see when 2 bind variables are used

SQL> var b2 varchar2(30) ;


where LAST_DDL_TIME > to_date(:b1, 'dd-mon-yyyy hh24:mi:ss') and

LAST_DDL_TIME < to_date(:b2, 'dd-mon-yyyy hh24:mi:ss') ;

Execution Plan

----------------------------------------------------------


1 0 FILTER

2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=33 Card=60 Bytes=5160)

SQL> select 5*1200/100 from dual;

5*1200/100

----------

60 -- Same as reported in Card column


13 PCTFREE and PCTUSED

PCTFREE 20 Minimum % of data block free and available for future update to the

existing rows already within each block

What it means that block allows row inserts until 80 ( = 100 -20) % is occupied.

PCTUSED 40 The data segment is considered unavailable for the insertion of new

rows until the amount of used space falls to 39 % or less (assuming that the

blocks used space reached PCTFREE 80%)

In short, PCTFREE parameter is for INSERT operation where as PCTUSED for

DELETE operation.

ROW CHAINING When data in a row is too large to fit into one data block, then

Oracle allocates 2 or more data blocks for that row. This is called Row Migration.

ROW MIGRATION A row that originally fit into one data block is updated so that the

overall row length increases, and the block's free space is already completely

filled. Oracle migrates the data for the entire row to a new data block, assuming

the entire row can fit in a new block. Oracle preserves the original row piece of a

migrated row to point to the new block containing the migrated row. The ROWID

of a migrated row does not change.


13 PCTFREE and PCTUSED . Contd

Minimize Records Per Block

determines whether Oracle restricts the number of records (rows) that

can be stored in a block

In this way, we can restrict Oracle blocks to store fixed number of rows.

Need not worry about future updates, row length increases etc

SQL> create table my_table ( id number(7), name varchar2(30),

some_text varchar2(1500) ) ;

SQL> analyze table my_table compute statistics ;

SQL> select object_id, data_object_id from dba_objects

where object_name = 'MY_TABLE';

OBJECT_ID DATA_OBJECT_ID

---------- --------------

68357 68357



Before You change the table definition,

Login as sys account, run the below SQL

SQL> select dataobj#, obj#, spare1

2 from tab$

3 where obj# = 68357 ;

DATAOBJ# OBJ# SPARE1

---------- ---------- ----------

68357 68357 736

Note down the value of SPARE1 column.



SQL> insert into my_table values (1, 'Tamil', rpad('x', 1500) ) ;

SQL> insert into my_table values (2, 'Tandon', rpad('x', 1500) ) ;

SQL> insert into my_table values (3, 'Selvan', rpad('x', 1500) ) ;

SQL> insert into my_table values (4, 'Kannan', rpad('x', 1500) ) ;

SQL> insert into my_table values (5, 'Ram', rpad('x', 1500) ) ;

SQL> commit;

SQL> select length(id), length(name), length(some_text) from my_table ;

LENGTH(ID) LENGTH(NAME) LENGTH(SOME_TEXT)

---------- ------------ -----------------

1 5 1500

1 6 1500

1 6 1500

1 6 1500

1 3 1500

SQL> alter table MY_TABLE minimize records_per_block ;



SQL> get x

1 select dataobj#, obj#, spare1 , spare2, spare3, spare4, spare5

2 from tab$

3* where obj# = 68357

4 /

DATAOBJ# OBJ# SPARE1 SPARE2 SPARE3 SPARE4 SPARE5

---------- ---------- ---------- ---------- ---------- ---------- ----------

68357 68357 32771

Spare1 column value has been increased to 32771 from 736.

SQL> truncate table my_table ;

Table truncated.



SQL> begin

2 for K in 1 .. 8 loop

3 insert into my_table values (K, 'name'||to_char(k), 'y') ;

4 end loop ;

5 end;

6 /

SQL> select dbms_rowid.rowid_relative_fno(rowid) filenum ,

2 dbms_rowid.rowid_block_number(rowid) blocknum,

3 id

4 from my_table ;

FILENUM BLOCKNUM ID

---------- ---------- ---------

31 12810 1

31 12810 2

31 12810 3

31 12810 4

31 12811 5

31 12811 6

31 12811 7

31 12811 8

8 rows selected.

W

a

tc

h

o

u

t th

e

B

L

O

C

K

N

U

M

B

E

R

.

O

ra

c

le

a

llo

c

a

te

d

4

ro

w

s

p

e

r b

lo

c

k

b

a

s

e

d

o

n

o

ld

ro

w

le

n

g

th

.



SQL> analyze table my_table compute statistics ;

Table analyzed.

SQL> select table_name, blocks, avg_row_len from user_tables

2 where table_name = 'MY_TABLE' ;

TABLE_NAME BLOCKS AVG_ROW_LEN

------------------------------ ---------- -----------

MY_TABLE 2 14


Strategies To Speed Up Full Table Scan

Decrease PCTFREE until row chaining or migration is not happening

Increase PCTUSED so that deleted space will be reused immediately

with high cost of FREELIST management

Consider MINIMIZE RECORD PER BLOCK if you know the actual length

of the rows after update

Use Partitions Oracle will decide which partition to be used based on

the predicate

Use Parallel threads Oracle will determine the number of threads

based on the resource available

Store LONG / LOB columns in a separate TABLESPACE

Use IOT if the table is always accessed via Primary Key

Use CACHE option if the tables data are frequently accessed by many

queries

Use parallel degree on large tables in the create table statement

Use separate memory pool for the tables that undergo full table scan

operation

Use HASH Cluster if the table is static

Use FULL and PARALLEL Hints in the Query, if its appropriate

Increase DB_FILE_MULTIBLOCK_READ_COUNT value


14 UPDATE Statement Old Method

SQL> select * from emp2 ;

EMPID BONUS

---------- ----------

1001 101

1002 102

1003 103

SQL> select * from new_bonus;

EMPID BONUS

---------- ----------

1001 501

1003 502

1 update emp2 a

2 set bonus = (select bonus

3 from new_bonus b

4 where b.empid = a.empid)

5 where exists (select null

6 from new_bonus b

7* where b.empid = a.empid)

SQL> /

2 rows updated.


EMPID BONUS

---------- ----------

1001 501

1002 102

1003 502


UPDATE Statement New Method -1


EMPID BONUS

---------- ----------

1001 101

1002 102

1003 103


EMPID BONUS

---------- ----------

1001 501

1003 502

SQL> update emp2 a set

2 a.bonus = coalesce (

3 (select b.bonus

4 from new_bonus b

5 where b.empid = a.empid),

6 a.bonus) ;

3 rows updated.

SQL> commit;

Commit complete.


EMPID BONUS

---------- ----------

1001 501

1002 102

1003 502


UPDATE Statement New Method -2


EMPID BONUS

---------- ----------

1001 101

1002 102

1003 103


EMPID BONUS

---------- ----------

1001 501

1003 502

SQL> update (select a.bonus old_value ,

2 b.bonus new_value

3 from emp2 a , new_bonus b

4 where a.empid = b.empid)

5* set old_value = new_value

SQL> /

set old_value = new_value

*

ERROR at line 5:

ORA-01779: cannot modify a column which maps

to a non key-preserved table

SQL> alter table emp2 add constraint emp2_uq unique

(empid) ;

Table altered.

SQL> alter table new_bonus add constraint nb_uq

unique (empid) ;

Table altered.

SQL> update (select a.bonus old_value ,

2 b.bonus new_value

3 from emp2 a , new_bonus b

4 where a.empid = b.empid)

5* set old_value = new_value

SQL> /

2 rows updated.

SQL> select * from emp2;

EMPID BONUS

---------- ----------

1001 501

1002 102

1003 502


Oracle Performance Tuning Class - 1

I value your questions, comments and suggestions.

Please send your comment to [email protected].

Beacon Infotech Corporation.

Web: www.oracleact.com

beacon ora perf class 1

Documents