Tips to avoid hot block contentio for small look-up tables.

MINIMIZE RECORD_PER_BLOCK

The basic idea of this article is to store a single row into a single block.
This could be useful to avoid the hot block contention for the small look up tables, which access heavily.

The MINIMIZE RECORDS_PER_BLOCK features comes from Oracle 8i, which gives the
facility to almost store a single record into a single block.

Test case:

Normal Heap Table (without MINIMIZE RECORDS_PER_BLOCK)

17:26:39 jaffar@PRIMEDB> create table nomin1 as select * from user_objects where rownum = 1;

Table created.

Elapsed: 00:00:00.01
17:27:18 jaffar@PRIMEDB> analyze table nomin1 compute statistics;

Table analyzed.

Elapsed: 00:00:00.00
17:27:33 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'NOMIN1';

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
NOMIN1 1 4

17:28:22 jaffar@PRIMEDB> insert into nomin1 select * from user_objects;

242 rows created.

Elapsed: 00:00:00.00
17:28:34 jaffar@PRIMEDB> commit;

Commit complete.

Elapsed: 00:00:00.00
17:28:35 jaffar@PRIMEDB> analyze table nomin1 compute statistics;

Table analyzed.

Elapsed: 00:00:00.00
17:28:39 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'NOM
IN1';

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
NOMIN1 243 8


Note : Total rows comes in 8 blocks.


Test case with MINIMIZE RECORD_PER_BLOCK

17:28:47 jaffar@PRIMEDB> create table min01 as select * from user_objects where rownum = 1;

Table created.

Elapsed: 00:00:00.00
17:29:12 jaffar@PRIMEDB> analyze table min01 compute statistics;

Table analyzed.

Elapsed: 00:00:00.00
17:29:22 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'MIN01';

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
MIN01 1 4


17:32:26 jaffar@PRIMEDB> alter table min01 minimize records_per_block;

Table altered.


17:32:53 jaffar@PRIMEDB> insert into min01 select * from user_objects;

243 rows created.

Elapsed: 00:00:00.09
17:33:12 jaffar@PRIMEDB> commit;

Commit complete.

Elapsed: 00:00:00.00
17:33:12 jaffar@PRIMEDB> analyze table min01 compute statistics;

Table analyzed.

Elapsed: 00:00:00.00
17:33:14 jaffar@PRIMEDB>
17:33:14 jaffar@PRIMEDB> select table_name,num_rows,blocks from user_tables where table_name = 'MIN0
1';

TABLE_NAME NUM_ROWS BLOCKS
------------------------------ ---------- ----------
MIN01 244 180

Note : Total rows 243 comes in 180 blocks.


References:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76962/ch2.htm#103385

Sources Used in the test cases:

Orcle Version : 9.2.0.7
db_block_size : 8194
Tablespace : LMT + Auto Extent Size.

tuning query with tkprof output

Being a regular visitor to http://www.jlcomp.demon.co.uk, today, I have came across a good explanation/article by Mr. Jonathan Lewis, about measuring/gussing query performance using tkprof output.

http://www.jlcomp.demon.co.uk/tkprof_01.html

Happy reading,

Jaffar

Auto Undo Management Pefromance Degradation

Auto Undo Management + Fast Ramp-up [Oracle 10g Rel.2, a new feature]


The Auto Undo Management feature brings 10 undo segments online during the database startup,
on most of the SYSTEMS.

Created Undo Segment _SYSSMU1$
Created Undo Segment _SYSSMU2$
Created Undo Segment _SYSSMU3$
Created Undo Segment _SYSSMU4$
Created Undo Segment _SYSSMU5$
Created Undo Segment _SYSSMU6$
Created Undo Segment _SYSSMU7$
Created Undo Segment _SYSSMU8$
Created Undo Segment _SYSSMU9$
Created Undo Segment _SYSSMU10$

and the strategy with Automatic Undo is to have no more than one transaction per undo segment if possible,
therefore, Oracle keeps creating new ones if there is space in the undo tablespace.

There is performance degradation or sometimes database goes in hung stage when undo segments comes online, this
can be mostly seen on highly oltp database systems. I have experienced the problem on a databse (9205) which is
extremly oltp and number of transaction per second are 400+. Refer to metalink note 301432.1.

Setting on instance level 'event=10511 trace name context forever, level 2', would prevent
going undo segment offline.

But, the problem is, when the database shutdown and startup again, it again comes up with 10 undo segmnets online
and repeat the same functionality, i.e. bringing more undo segments online upon requirements.

Oracle 10g Rel.2 Comes up with a solution for the above discussed undo segments issues. Fast Ramp-Up

In previous versions, when the instance was restarted or when you switched undo tablespaces,
the ramp-up time for the system to grow the number of online undo segments could be several minutes.
Generally, this time delay was unacceptable.
After an instance startup or a switch of undo tablespaces in Oracle Database 10g, the database decides how many
undo segments to place online, based on existing data stored in AWR.

Conclusion:
I have not tested the Fast Ramp-up feature. However, waiting for the opportunity to upgrade our
database to Oracle 10g Rel.2 to test the Sast Ramp-up feature.

References :

Metalink Note : 301432.1 Performance Slowdown During Heavy Undo Segment Onlining
Metalink Note : 266159.1 SMON: ABOUT TO RECOVER UNDO SEGMENT %s messages in alert log

Transitive Closure behaviour when query_rewrite_enabled parameter set true/false

The article covers our experiences with the impact of various settings
of the parameter query_rewrite_enabled on sql performance whilst
migrating an Oracle database from AIX to HP.

All testing for this article was performed on Oracle 9.2.0.5.

TRANSITIVE CLOSURE WITH QUERY_REWRITE_ENABLED

Transitivity and Transitive Closure

Transitivity (or Transitive Closure) is the generation of additional
predicates that may open additional access methods. It is only performed by the CBO.

We have recently migrated one of our data warehouse databases (Oracle 9i Rel.2 (9205)) from AIX to HP-SUPERDOME.
The data warehouse is 1.8TB. It was migrated using export and import utilities.

The major problem we encountered post migration was a dramatic increase in the response time of one of our queries.

The query was taking 5 minutes on AIX. The same query on HP ran for 2 days.

Following is the query and its execution plan on AIX and HP.

SELECT max(as_of_date), max(cust_code), nvl(abs(sum(run_offs_sar)), 0),
nvl(abs(sum(inst_amt_sar)), 0), nvl(abs(sum(bal_sar)), 0)
FROM account a
WHERE acct_no = '00100100120'
AND as_of_date = (SELECT max(as_of_date)
FROM account b
WHERE b.acct_no = a.acct_no
AND b.run_offs_sar <> 0)

Execution Plan (AIX)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=602 Card=1 Bytes=55)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=602 Card=1 Bytes=55)
3 2 FILTER
4 3 SORT (GROUP BY) (Cost=602 Card=1 Bytes=75)
5 4 HASH JOIN (Cost=529 Card=167 Bytes=12525)
6 5 PARTITION RANGE (ALL)
7 6 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'A_ACCOUNT' (Cost=264 Card=167 Bytes=4509)
8 7 INDEX (SKIP SCAN) OF 'IDX_DT_ACCT_FLAG' (NON-UNIQUE) (Cost=189 Card=167)
9 5 PARTITION RANGE (ALL)
10 9 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'A_ACCOUNT' (Cost=264 Card=167 Bytes=8016)
11 10 INDEX (SKIP SCAN) OF 'IDX_DT_ACCT_FLAG' (NON-UNIQUE) (Cost=189 Card=167)

Execution Plan (HP)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=630319908 Card=1 Bytes=55)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=630319908 Card=1602 Bytes=88110)
3 2 FILTER
4 3 SORT (GROUP BY) (Cost=630319908 Card=1602 Bytes=107334)
5 4 MERGE JOIN (CARTESIAN) (
7 6 Cost=630319180 Card=225809 Bytes=15129203)
6 5 PARTITION RANGE (ALL)TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'A_ACCOUNT' (Cost=700 Card=440 Bytes=10560)
8 7 INDEX (SKIP SCAN) OF 'IDX_DT_ACCT_FLAG' (NON-UNIQUE) (Cost=519 Card=513)
9 5 BUFFER (SORT) (Cost=630319208 Card=513 Bytes=22059)
10 9 PARTITION RANGE (ALL)
11 10 TABLE ACCESS (FULL) OF 'A_ACCOUNT' (Cost=1432542 Card=513 Bytes=22059)

I then turned on the ‘10053 trace event with level 1’ on both databases. Comparing the trace outputs,
I found that the only difference was in the setting of the ‘query_rewrite_enabled’ parameter.
On AIX this parameter was set to TRUE and on HP it was at the default setting of FALSE.

After setting this parameter to TRUE on HP, the query achieved the same response time as on AIX.
The execution plan now is identical on both systems.

Thanks to Jonathan Lewis for explaining Transitive Closure behavior:
<<
When you have a problem like this, use dbms_xplan()
to get the more detailed execution plan than you don't
get from autotrace (until 10.2). In this case I think you
would find that you have a join predicate that disappears
(to produce the cartesian join) because transitive closure
has been used to copy a filter predicate from one side of
the join to the other.
============
In the case where the constant predicate is an equality the
optimizer will generate a second constant predicate, but
lose the join predicate if query_rewrite_enabled = false, hence ...

On the other hand, if query_rewrite_enabled = true, then
8i and 9i will keep the join predicate even in this case, hence ...
============

>>

By setting the parameter ‘query_rewrite_enabled=FALSE’, then to ‘TRUE’, I ran the query with ‘explain plan for’
to produce the explain plan. Using the ‘select * from table(dbms_xplan.display) dual’, I got the following predicate
information used/created by the Optimizer:

with query_rewrite_enabled=FALSE

3 - filter("A"."AS_OF_DATE"=MAX("B"."AS_OF_DATE"))
7 - filter("B"."RUN_OFFS_SAR"<>0)
8 - access("B"."ACCT_NO"='00100100120')
filter("B"."ACCT_NO"='00100100120')
11 - filter("A"."ACCT_NO"='00100100120')

with query_rewrite_enabled=TRUE

3 - filter("A"."AS_OF_DATE"=MAX("B"."AS_OF_DATE"))
5 - access("B"."ACCT_NO"="A"."ACCT_NO") ====
7 - filter("B"."RUN_OFFS_SAR"<>0)
8 - access("B"."ACCT_NO"='00100100120')
filter("B"."ACCT_NO"='00100100120')
11 - access("A"."ACCT_NO"='00100100120')
filter("A"."ACCT_NO"='00100100120')


When the parameter was set to FALSE, the optimizer was dropping the following in the predicate information,
compared to the one where the parameter was set to TRUE.

’access("B"."ACCT_NO"="A"."ACCT_NO")’ and ‘access("A"."ACCT_NO"='00100100120'

In this example, I have used the two traditional tables, emp & dept for my testing.

Alter session set query_rewrite_enabled=FALSE;

EXPLAIN PLAN FOR
SELECT e.ename,d.dname
FROM emp e,dept d
WHERE d.deptno=10
AND e.deptno =d.deptno

select * from table(dbms_xplan.display) dual;

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24939 | 462K| 51 |
| 1 | NESTED LOOPS | | 24939 | 462K| 51 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 |
|* 3 | INDEX UNIQUE SCAN | SYS_C002803 | 1 | | |
|* 4 | TABLE ACCESS FULL | EMP | 24939 | 194K| 50 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------
3 - access("D"."DEPTNO"=10)
4 - filter("E"."DEPTNO"=10)

In the predicate information above, the optimizer doesn’t use predicate join e.deptno=d.deptno. Instead,
it substitutes the referenced column’s (d.deptno) value of 10 in the filter e.deptno=10.

Jonathan Lewis, in his book “CBO Fundamentals”, Chapter 6, Selectivity Issue, suggests some bypasses for
this problem, such as:

EXPLAIN PLAN FOR
SELECT e.ename,d.dname
FROM emp e,dept d
WHERE d.deptno=10
AND e.deptno = d.deptno
AND e.deptno = d.deptno
select * from table(dbms_xplan.display) dual;

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8313 | 154K| 51 |
| 1 | NESTED LOOPS | | 8313 | 154K| 51 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 |
|* 3 | INDEX UNIQUE SCAN | SYS_C002803 | 1 | | |
|* 4 | TABLE ACCESS FULL | EMP | 8313 | 66504 | 50 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=10)
4 - filter("E"."DEPTNO"=10 AND "E"."DEPTNO"="D"."DEPTNO")

The above output clearly shows the difference in the execution plan of the query, cost, rows and filter.
Some other work arounds are,

EXPLAIN PLAN FOR
SELECT e.ename,d.dname
FROM emp e,dept d
WHERE d.deptno = 10
AND e.deptno = d.deptno+0

EXPLAIN PLAN FOR
SELECT e.ename,d.dname
FROM emp e,dept d
WHERE d.deptno = 10
AND e.deptno = 10
AND e.deptno = d.deptno

Note:

To use dbms_xplan.display(), you need to have plan_table of Oracle >=9206 and <=10g Rel.1.

From Release 10.2, the auto trace uses dbms_xplan.display format.

Conclusion:

If you see a similar performance degradation after a Oracle upgrade
Or migration, it can be very worthwhile to perform the above checks.

Acknowledgements:

I owe thanks to Jonathan Lewis for his kind support and also thanks to my colleague Wayne Phillips for
his technical review and corrections.

References:

Cost-Based Oracle Fundamentals – by Jonathan Lewis
Metalink Note. 68979.1 - Transitivity and Transitive Closure

Mr. Syed Jaffar Hussain[sjaffarhussain@gmail.com] is the Sr. Oracle DBA at a private bank in Saudi Arabia.
He has worked in IT for 12 years, and as an Oracle DBA for 6 years.

Oracle 10g Upgrade (OCP)

I have just cleared my OCP 10g upgrade (IZO-040) exam.
Couple of weeks ago, I have attend the 10g New Features for Database Administor course and prepared my self for the exam.
During my course, I have come across few of new features which we could use in our database.
I will discuss about them in my coming posts.

Its time to party.