Indexing Strategy and Column Ordering - I

I have been reading a lot of blogs and other discussions on column ordering in an index, its impact.I have tried to consolidate what my learing was along with some of my expericences.Please feel free add to this in case i have missed out or overlooked something.

Index skipscan was in Oracle 9i introduced and i feel that we need to discuss skip scan and then examine the impact of column ordering.


test@TEST> create table skip (a varchar2(10), b number);

Table created.



test@TEST>create index idx_skip on skip(a,b);

Index created.

test@TEST>ed Wrote file afiedt.buf

1 begin
2 for i in 1 .. 30000
3 loop
4 insert into SKIP values('A', i);
5 end loop;
6 commit;
7* end;
test@TEST>/
PL/SQL procedure successfully completed.

test@TEST>ed
Wrote file afiedt.buf

1 begin
2 for i in 30001 .. 60000
3 loop
4 insert into SKIP values('B', i);
5 end loop;
6 commit;
7* end;
test@TEST>

PL/SQL procedure successfully completed.

test@TEST>ed
Wrote file afiedt.buf

1 begin
2 for i in 60001 .. 90000
3 loop
4 insert into SKIP values('C', i);
5 end loop;
6 commit;
7* end;
test@TEST>/

PL/SQL procedure successfully completed.

test@TEST>ed
Wrote file afiedt.buf

1 begin
2 for i in 90001 .. 100000
3 loop
4 insert into SKIP values('D', i);
5 end loop;
6 commit;
7* end;
test@TEST>/

PL/SQL procedure successfully completed.

test@TEST>SET AUTOTRACE TRACEONLY EXPLAIN



test@TEST>select * from SKIP where b=100;

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=6)


1 0
INDEX (SKIP SCAN) OF 'IDX_SKIP' (NON-UNIQUE) (Cost=5 Card=1 Bytes=6)





test@TEST>select * from SKIP where b=45000;

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=6)


1 0
INDEX (SKIP SCAN) OF 'IDX_SKIP' (NON-UNIQUE) (Cost=5 Card=1 Bytes=6)





test@TEST>select * from SKIP where b=100000;

Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=6)


1 0
INDEX (SKIP SCAN) OF 'IDX_SKIP' (NON-UNIQUE) (Cost=5 Card=1 Bytes=6)



What is happening here is instead of range scanning the entire index, the index is now treated as a sub index A (1-30000),B(30001-60000),C(60001-90000),D (90001 -100000).

The query select * from SKIP where b=100; would only scan the A (1-30000) part
select * from SKIP where b=45000; This would skip A (1-30000) and scan B(30001-60000) , and same holds true for the last query.

By this way there is no need to range scan the entire index.

Note that the sub index structure/part is not exposed outside this is managed by oracle internally.

CBO Plans and Histograms

Its been a long time since i have blogged.

There was an upgrade that happened in one of our systems.We had upgraded recently from 9i to 10g and found an issue with one of our queries.

The Query was not having an issue with 9i but came as a sudden surprise after the upgrade.We had decided to go with 10g Automatic Statistics Collection and with Histograms on key columns.This particular is one which is executed many times and we wanted that this query be a good perfromant.

While doing our testing we did observe that there were some days when this query was performing well and some days it was absolutely barmy!!.

Tyring to bottom out the issue we could confirm some one else had found this issue earlier and before the start of the test they made sure that the statistics on the tables referenced in this query are refreshed.But the Developer being novice to the DBMS_STATS prefered to use the simple ANALYZE option.

Given the above story we came to a conclusion that the freshly gathered STATS were performing well and later on when oracle runs the Automated Stats Collection job,we are back to the problem.

Investigation showed that the density value for one of the columns was showing an absurd value.This lead us to conclude that the problem is with histograms and histogram statistics are reflected only when the automatic stats job comes into picture.

We raised the issue with oracle support and the answer was this is a bug.

Bug 5483301 --- QUERY WITH PREDICATE VALUE NON-EXISTENT IN FREQUENCY
HISTOGRAM RUNS SLOW

When cardinality estimate is very low resulting in the use of Nested loops join with poor performance, and low estimate is due to the presence of a frequency histogram on a column of an equality predicate with a value that is not in the frequency histogram, then you are likely facing this issue.

WORKAROUND:
Drop the frequency histogram that is on the predicate column.

I raised the same with Jonathan Lewis, this is what he had to say about this:

I'm surprised they call that a bug - it's necessary feature of the optimizer,although a slightly extreme one.

If you query the database for a value that is "known" not to exist then the ptimizer has to assume that there is at most one row with that value, which often makes the table the driving table.If there are lots of rows with that value then the performance can be catastrophic.

A potentially better workaround is to use dbms_stats.set_column_stats to set the density to a value that reflects the fraction of the table you expect to get for the 'unregistered' values.

We adopted the above solution and locked the table statistics so that they are not refreshed when the Auto Stats Job Kicks in.

Redo Logfile Size Advisor

The Redo Logfile Size Advisor can be used to determine the least optimal online redo log file size based on the current FAST_START_MTTR_TARGET setting and MTTR statistics.
Which means that Redo Logfile Size Advisor is enabled only if FAST_START_MTTR_TARGET is set.
The Advisor provides a recommended optimal smallest online redo log file size.
we can then adjust the online redo log file size to the recommended optimal size ,eliminating the need to manually determine an optimal size.

To use the advisor we need to query the V$INSTANCE_RECOVERY VIEW.

If our redo log file size is under sized then the checkpoint process is driven by WRITES_LOGFILE_SIZE i.e. its driven by the smallest redo log file size.

This can be clarified by quering the V$INSTANCE_RECOVERY VIEW.

Lets say one of our Log file is too small and we have set the FAST_START_MTTR_TARGET=30

We have done some Transactions which has generated redo entries , now we would like to capture the minimum optimal size of the redo log file.

SELECT TARGET_MTTR,ESTIMATED_MTTR,WRITES_MTTR,WRITES_LOGFILE_SIZE, OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;

TARGET_MTTR ESTIMATED_MTTR WRITES_MTTR WRITES_LOGFILE_SIZE
----------- -------------- ----------- ------------------- -------------------- -------
30 28 0 1022
------------------------------
OPTIMAL_LOGFILE_SIZE
32

Its clear from the above query that the chekpoint is driven by WRITES_LOGFILE_SIZE as described above and the advice is to have a Log file size of atleast 32MB Size.

Now if we recreate the log file with the size recommendation ie 32 MB and fire the same query again lets see what happens


SELECT TARGET_MTTR,ESTIMATED_MTTR,WRITES_MTTR,WRITES_LOGFILE_SIZE, OPTIMAL_LOGFILE_SIZE FROM V$INSTANCE_RECOVERY;


TARGET_MTTR ESTIMATED_MTTR WRITES_MTTR WRITES_LOGFILE_SIZE
------------------- ------------------- ----------------- ----------------------------
34 38 924 0
OPTIMAL_LOGFILE_SIZE
----------- --------------------
32

This shows that the checkpoint is now driven by WRITES_MTTR ie the FAST_START_MTTR parameter.


CAVEAT: An online redo log file’s size is considered optimal if it does not drive incremental checkpointing more aggressively than needed by FAST_START_MTTR_TARGET.

Oracle Application Server-OPMN

USING OPMN TO MANAGE EXTERNAL COMPONENTS

(Posted By Abby Thomas )




One of the most indispensable part of Oracle Application Server (OAS) 10g is OPMN [Oracle Process Manger and Notification] server. Its one of the best things that has happened to the OAS since its inception. OPMN is installed and configured with every Oracle Application Server installation type and is essential for running Oracle Application Server.
According to Oracle’s documentation OPMN features the following functionality:
Provides a command-line interface for process control and monitoring for single or multiple Oracle Application Server components and instances.
Provides an integrated way to manage Oracle Application Server components.
Enables management of Oracle Application Server subcomponents and sub-subcomponents.
Channels all events from different Oracle Application Server component instances to all Oracle Application Server components that can utilize them.
Solves interdependency issues between Oracle Application Server components by enabling you to start and stop components in order.
Enables customizing of enterprise functionality by using event scripts.
Enables gathering of host and Oracle Application Server process statistics and tasks.
Provides automatic restart of Oracle Application Server processes when they become unresponsive, terminate unexpectedly, or become unreachable as determined by ping and notification operations.
Provides automatic death detection of Oracle Application Server processes.
Does not depend on any other Oracle Application Server component being up and running before it can be started and used.
Did you know that OPMN has the ability to manage arbitrary daemon processes that are not part of your Oracle Application Server installation?You can even create more sophisticated process management services by supplying the opmn.xml file the optional paths to scripts for stopping, restarting, and pinging the daemon process.

If you need a more details on configuring custom processes with OPMN

Refer :http://download-east.oracle.com/docs/cd/B14099_19/core.1012/b13996/custom.htm

Database Administration Basics -2

Administer Redo Logs

List the number and location of existing logfiles and display the number of logfile groups and members the database has.
This gives us the location of the logfiles
SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------
1 C:\ORACLE\ORADATA\TEST\redo01.log
2 C:\ORACLE\ORADATA\TEST\redo02.LOG
3 C:\ORACLE\ORADATA\TEST\redo03.LOG3 rows selected.

This tells us whether the redologs are multiplexed or not

SQL> select group#, members from V$log;
GROUP# MEMBERS
---------- ----------
1 1
2 1
3 1
3 rows selected.

Since we have only one member each we infer that the redo logs are not Multiplexed.

Check whether the database is in which archiver mode

SQL> select archiver from v$instance;
ARCHIVE
-------
STARTED
1 row selected.

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
1 row selected.

Identify the datafile that makes up the UNDO_3 Tablespace

SQL> select file_name from dba_data_files where tablespace_name='UNDO_3';
FILE_NAME
----------------------------------------
C:\ORACLE\ORADATA\TEST\UNDO3.DBF
1 row selected.

Find the total freespace available in the database

SQL> select sum(bytes/1024/1024) "FREESPACE IN MB" from dba_free_space;
FREESPACE IN MB
----------------
415.875
1 row selected.

Find the total space available in the database

SQL> SELECT SUM(BYTES/1024/1024) "USEDSPACE IN MB" FROM DBA_SEGMENTS; USEDSPACE IN MB
---------------
795.71875
1 row selected.

Add a Redo Log Group 4 with two members log04a.log and log04b.log with 10mb each

SQL> alter database add logfile group 4 ('c:/oracle/oradata/test/redo04a.log' , 'c:/oracle/oradata/test/redo04b.log') size 10m;
Database altered.

Verification

SQL> select member from v$logfile where group#=4;
MEMBER
----------------------------------------
C:\ORACLE\ORADATA\TEST\REDO04A.LOG
C:\ORACLE\ORADATA\TEST\REDO04B.LOG
2 rows selected.

Remove the redolog group created in the above step Before removing We need to make sure that the logfiles are not the current or active ones rather they should be inactive
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
4 UNUSED
4 rows selected.

Since this group 4 is unused we can safely drop the same

SQL> alter database drop logfile group 4;
Database altered.

SQL> select group#,status from v$log;
GROUP# STATUS#
---------- ----------------
1 INACTIVE
2 INACTIVE
3 CURRENT
3 rows selected.

Resize all the online redolog groups to 5mb We cannot resize the online redolog files , the only way to resize is to add new log files and drop the old ones

SQL> alter database add logfile group 4
'c:/oracle/oradata/test/redo04.log' size 5m;
Database altered.

SQL> alter database add logfile group 5
'c:/oracle/oradata/test/redo05.log' size 5m;
Database altered.

Now try to add the group 6 what happens why ?

SQL> alter database add logfile group 6 'c:/oracle/oradata/test/redo06.log' size 5m ; alter database add logfile group 6 'c:/oracle/oradata/test/redo06.log' size 5m*ERROR at line 1:ORA-01185: logfile group number 6 is invalid MAXLOGFILES:
------------
MAXLOGFILES is set during database creation, via the create database statement, and can only be changed by recreating the database or recreating the control file. This is set to 5 currently

Here as we have to drop log group 1,2 and 3 we can create the THIRD group after dropping them
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
3 CURRENT
4 UNUSED
5 UNUSED
3 rows selected.

SQL> alter system switch logfile; System altered.

SQL> alter system switch logfile; System altered.

SQL> alter system switch logfile; System altered.

SQL> alter system switch logfile; System altered.

SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
3 INACTIVE
4 ACTIVE
5 CURRENT
3 rows selected.

SQL> alter database drop logfile group 3;
Database altered.

Now let us create the third group and name it as group 6 and see what happens

SQL> alter database add logfile group 6
'c:/oracle/oradata/test/redo06.log' size 5m ;alter database add logfile group 6
'c:/oracle/oradata/test/redo06.log' size 5m*ERROR at line 1:ORA-01185: logfile group number 6 is invalid
Oracle will not allow to create a logfile group with group# 6

SQL> alter database add logfile group 1 'c:/oracle/oradata/test/redo06.log' size 5m ;Database altered.
We can only specify group numbers from 1 to 5 , to change this behaviour we need to recreate the control file with MAXLOGFILES SET TO 6 .

For more on administration please check out

http://www.exforsys.com/content/category/17/261/343/