Indexing and Tuning

Indexing and Tuning

--------------------------------------------------------------------------------
One of the great dividends of investing in an RDBMS is that you don't have to think too much about the computer's inner life. You're the programmer and say what kinds of data you want. The computer's job is to fetch it and you don't really care how.
Maybe you'll start caring after that $500,000 database server has been grinding away on one of your queries for two solid hours...

While software is being developed, it is rare for tables to contain more than a handful of rows. Nobody wants to sit in SQL*Plus or at Web forms and type in test data. After the application launches and tables begin to fill up, people eventually notice that a particular section of the site is slow. Here are the steps that you must take

Find a URL that is running too slowly.
If possible, enable query logging from your Web or application server. What you want is for the Web server to write every SQL query and transaction into a single file so that you can see exactly what the database management system was told to do and when. This the kind of feature that makes a Web programming environment truly productive that it is tough to advertise it to the Chief Technology Officer types who select Web programming environment (i.e., if you're stuck using some closed-source Web connectivity middleware/junkware you might not be able to do this).
With AOLserver, enable query logging by setting Verbose=On in the [ns/db/pool/**poolname**] section of your .ini file. The queries will show up in the error log ("/home/nsadmin/log/server.log" by default).

Request the problematic URL from a Web browser.
fire up Emacs and load the query log into a buffer; spawn a shell and run sqlplus from the shell, logging in with the same username/password as used by the Web server
you can now cut (from server.log) and paste (into sqlplus) the queries performed by the script backing the slow URL. However, first you must turn on tracing so that you can see what Oracle is doing.
SQL> set autotrace on
Unable to verify PLAN_TABLE format or existence
Error enabling EXPLAIN report

Oops! It turns out that Oracle is unhappy about just writing to standard output. For each user that wants to trace queries, you need to feed sqlplus the file $ORACLE_HOME/rdbms/admin/utlxplan.sql which contains a single table definition:
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long);

Type "set autotrace on" again (it should work now; if you get an error about the PLUSTRACE role then tell your dbadmin to run $ORACLE_HOME/sqlplus/admin/plustrce.sql as SYS then GRANT your user that role).
Type "set timing on" (you'll get reports of elapsed time)
cut and paste the query of interest.
Now that we're all set up, let's look at a few examples.
A simple B-Tree Index
Suppose that we want to ask "Show me the users who've requested a page within the last few minutes". This can support a nice "Who's online now?" page, like what you see at http://www.photo.net/shared/whos-online. Here's the source code to find users who've requested a page within the last 10 minutes (600 seconds):

select user_id, first_names, last_name, email
from users
where last_visit > sysdate - 600/86400
order by upper(last_name), upper(first_names), upper(email)

We're querying the users table:

create table users (
user_id integer primary key,
first_names varchar(100) not null,
last_name varchar(100) not null,
...
email varchar(100) not null unique,
...
-- set when user reappears at site
last_visit date,
-- this is what most pages query against (since the above column
-- will only be a few minutes old for most pages in a session)
second_to_last_visit date,
...
);

Suppose that we ask for information about User #37. Oracle need not scan the entire table because the declaration that user_id be the table's primary key implicitly causes an index to be constructed. The last_visit column, however, is not constrained to be unique and therefore Oracle will not build an index on its own. Searching for the most recent visitors at photo.net will require scanning all 60,000 rows in the users table. We can add a B-Tree index, for many years the only kind available in any database management system, with the following statement:

create index users_by_last_visit on users (last_visit);

Now Oracle can simply check the index first and find pointers to rows in the users table with small values of last_visit.
Tracing/Tuning Case 1: did we already insert the message?
The SQL here comes from an ancient version of the bulletin board system in the ArsDigita Community System (see http://www.photo.net/bboard/ for an example). In the bad old days when we were running the Illustra relational database management system, it took so long to do an INSERT that users would keep hitting "Reload" on their browsers. When they were all done, there were three copies of a message in the bulletin board. So we modified the insertion script to check the bboard table to see if there was already a message with exactly the same values in the one_line and message columns. Because message is a CLOB column, you can't just do the obvious "=" comparison and need to call the PL/SQL function dbms_lob.instr, part of Oracle's built-in DBMS_LOB package.
Here's a SQL*Plus session looking for an already-posted message with a subject line of "foo" and a body of "bar":

SQL> select count(*) from bboard
where topic = 'photo.net'
and one_line = 'foo'
and dbms_lob.instr(message,'bar') > 0 ;

COUNT(*)
----------
0


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BBOARD'
3 2 INDEX (RANGE SCAN) OF 'BBOARD_BY_TOPIC' (NON-UNIQUE)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
59967 consistent gets
10299 physical reads
0 redo size
570 bytes sent via SQL*Net to client
741 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

Note the "10,299 physical reads". Disk drives are very slow. You don't really want to be doing more than a handful of physical reads. Let's look at the heart of the query plan:
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BBOARD'
3 2 INDEX (RANGE SCAN) OF 'BBOARD_BY_TOPIC' (NON-UNIQUE)

Looks as though Oracle is hitting the bboard_by_topic index for the ROWIDs of "just the rows that have a topic of 'photo.net'". It is then using the ROWID, an internal Oracle pointer, to pull the actual rows from the BBOARD table. Presumably Oracle will then count up just those rows where the ONE_LINE and MESSAGE columns are appropriate. This might not actually be so bad in an installation where there were 500 different discussion groups. Hitting the index would eliminate 499/500 rows. But BBOARD_BY_TOPIC isn't a very selective index. Let's investigate the selectivity with the query select topic, count(*) from bboard group by topic order by count(*) desc:
topic count(*)
photo.net 14159
Nature Photography 3289
Medium Format Digest 1639
Ask Philip 91
web/db 62

The bboard table only has about 19,000 rows and the photo.net topic has 14,000 of them, about 75%. So the index didn't do us much good. In fact, you'd have expected Oracle not to use the index. A full table scan is generally faster than an index scan if more than 20% of the rows need be examined. Why didn't Oracle do the full table scan? Because the table hadn't been "analyzed". There were no statistics for the cost-based optimizer so the older rule-based optimizer was employed. You have to periodically tell Oracle to build statistics on tables if you want the fancy cost-based optimizer:
SQL> analyze table bboard compute statistics;

Table analyzed.

SQL> select count(*) from bboard
where topic = 'photo.net'
and one_line = 'foo'
and dbms_lob.instr(message,'bar') > 0 ;

COUNT(*)
----------
0

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1808 Card=1 Bytes=828)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'BBOARD' (Cost=1808 Card=1 Bytes=828)

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
74280 consistent gets
12266 physical reads
0 redo size
572 bytes sent via SQL*Net to client
741 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

The final numbers don't look much better. But at least the cost-based optimizer has figured out that the topic index won't be worth much. Now we're just scanning the full bboard table. While transferring 20,000 rows from Illustra to Oracle during a photo.net upgrade, we'd not created any indices. This speeded up loading but then we were so happy to have the system running deadlock-free that we forgot to recreate an index that we'd been using on the Illustra system expressly for the purpose of making this query fast.
SQL> create index bboard_index_by_one_line on bboard ( one_line );

Index created.

Bboard postings are now indexed by subject line, which should be a very selective column because it is unlikely that many users would choose to give their question the same title. This particular query will be faster now but inserts and updates will be slower. Why? Every INSERT or UPDATE will have to update the bboard table blocks on the hard drive and also the bboard_index_by_one_line blocks, to make sure that the index always has up-to-date information on what is in the table. If we have multiple physical disk drives we can instruct Oracle to keep the index in a separate tablespace, which the database administrator has placed on a separate disk:
SQL> drop index bboard_index_by_one_line;

SQL> create index bboard_index_by_one_line
on bboard ( one_line )
tablespace philgidx;

Index created.

Now the index will be kept in a different tablespace (philgidx) from the main table. During inserts and updates, data will be written on two separate disk drives in parallel. Let's try the query again:
SQL> select count(*) from bboard
where topic = 'photo.net'
and one_line = 'foo'
and dbms_lob.instr(message,'bar') > 0 ;

COUNT(*)
----------
0

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=828)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BBOARD' (Cost=2 Card=1 Bytes=828)
3 2 INDEX (RANGE SCAN) OF 'BBOARD_INDEX_BY_ONE_LINE' (NON-UNIQUE) (Cost=1 Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
3 physical reads
0 redo size
573 bytes sent via SQL*Net to client
741 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

We've brought physical reads down from 12266 to 3. Oracle is checking the index on one_line and then poking at the main table using the ROWIDs retrieved from the index. It might actually be better to build a concatenated index on two columns: the user ID of the person posting and the subject line, but at this point you might make the engineering decision that 3 physical reads is acceptable.
Tracing/Tuning Case 2: new questions
At the top of each forum page, e.g., http://www.photo.net/bboard/q-and-a.tcl?topic=photo.net, the ArsDigita Community System shows questions asked in the last few days (configurable, but the default is 7 days). After the forum filled up with 30,000 messages, this page was perceptibly slow.

SQL> select msg_id, one_line, sort_key, email, name
from bboard
where topic = 'photo.net'
and refers_to is null
and posting_time > (sysdate - 7)
order by sort_key desc;

...

61 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1828 Card=33 Bytes=27324)

1 0 SORT (ORDER BY) (Cost=1828 Card=33 Bytes=27324)
2 1 TABLE ACCESS (FULL) OF 'BBOARD' (Cost=1808 Card=33 Bytes=27324)


Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
13188 consistent gets
12071 physical reads
0 redo size
7369 bytes sent via SQL*Net to client
1234 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
61 rows processed

A full table scan and 12,071 physical reads just to get 61 rows! It was time to get medieval on this query. Since the query's WHERE clause contains topic, refers_to, and posting_time, the obvious thing to try is building a concatenated index on all three columns:
SQL> create index bboard_for_new_questions
on bboard ( topic, refers_to, posting_time )
tablespace philgidx;

Index created.

SQL> select msg_id, one_line, sort_key, email, name
from bboard
where topic = 'photo.net'
and refers_to is null
and posting_time > (sysdate - 7)
order by sort_key desc;

...

61 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=33 Bytes=27324)

1 0 SORT (ORDER BY) (Cost=23 Card=33 Bytes=27324)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BBOARD' (Cost=3 Card=33 Bytes=27324)
3 2 INDEX (RANGE SCAN) OF 'BBOARD_FOR_NEW_QUESTIONS' (NON-UNIQUE) (Cost=2 Card=33)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
66 consistent gets
60 physical reads
0 redo size
7369 bytes sent via SQL*Net to client
1234 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
61 rows processed


60 reads is better than 12,000. One bit of clean-up, though. There is no reason to have a BBOARD_BY_TOPIC index if we are going to keep this BBOARD_FOR_NEW_QUESTIONS index, whose first column is TOPIC. The query optimizer can use BBOARD_FOR_NEW_QUESTIONS even when the SQL only restricts based on the TOPIC column. The redundant index won't cause any services to fail, but it will slow down inserts.
SQL> drop index bboard_by_topic;

Index dropped.

We were so pleased with ourselves that we decided to drop an index on bboard by the refers_to column, reasoning that nobody ever queries refers_to without also querying on topic. Therefore they could just use the first two columns in the bboard_for_new_questions index. Here's a query looking for unanswered questions:
SQL> select msg_id, one_line, sort_key, email, name
from bboard bbd1
where topic = 'photo.net'
and 0 = (select count(*) from bboard bbd2 where bbd2.refers_to = bbd1.msg_id)
and refers_to is null
order by sort_key desc;

...

57 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=33 Bytes=27324)

1 0 SORT (ORDER BY) (Cost=49 Card=33 Bytes=27324)
2 1 FILTER
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'BBOARD' (Cost=29 Card=33 Bytes=27324)
4 3 INDEX (RANGE SCAN) OF 'BBOARD_FOR_NEW_QUESTIONS' (NON-UNIQUE) (Cost=2 Card=33)
5 2 INDEX (FULL SCAN) OF 'BBOARD_FOR_NEW_QUESTIONS' (NON-UNIQUE) (Cost=26 Card=7 Bytes=56)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
589843 consistent gets
497938 physical reads
0 redo size
6923 bytes sent via SQL*Net to client
1173 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
57 rows processed

Ouch! 497,938 physical reads. Let's try it with the index in place:
SQL> create index bboard_index_by_refers_to
on bboard ( refers_to )
tablespace philgidx;

Index created.

SQL> select msg_id, one_line, sort_key, email, name
from bboard bbd1
where topic = 'photo.net'
and 0 = (select count(*) from bboard bbd2 where bbd2.refers_to = bbd1.msg_id)
and refers_to is null
order by sort_key desc;

...

57 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=33 Bytes=27324)
1 0 SORT (ORDER BY) (Cost=49 Card=33 Bytes=27324)
2 1 FILTER
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'BBOARD' (Cost=29 Card=33 Bytes=27324)
4 3 INDEX (RANGE SCAN) OF 'BBOARD_FOR_NEW_QUESTIONS' (NON-UNIQUE) (Cost=2 Card=33)
5 2 INDEX (RANGE SCAN) OF 'BBOARD_INDEX_BY_REFERS_TO' (NON-UNIQUE) (Cost=1 Card=7 Bytes=56)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8752 consistent gets
2233 physical reads
0 redo size
6926 bytes sent via SQL*Net to client
1173 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
57 rows processed


This is still a fairly expensive query, but 200 times faster than before and it executes in a fraction of a second. That's probably fast enough considering that this is an infrequently requested page.
Tracing/Tuning Case 3: forcing Oracle to cache a full table scan
You may have a Web site that is basically giving users access to a huge table. For maximum flexibility, it might be the case that this table needs to be sequentially scanned for every query. In general, Oracle won't cache blocks retrieved during a full table scan. The Oracle tuning guide helpfully suggests that you include the following cache hints in your SQL:
select /*+ FULL (students) CACHE(students) */ count(*) from students;

You will find, however, that this doesn't work if your buffer cache (controlled by db_block_buffers; see above) isn't large enough to contain the table. Oracle is smart and ignores your hint. After you've reconfigured your Oracle installation to have a larger buffer cache, you'll probably find that Oracle is still ignoring your cache hint. That's because you also need to
analyze table students compute statistics;

and then Oracle will work as advertised in the tuning guide. It makes sense when you think about it because Oracle can't realistically start stuffing things into the cache unless it knows roughly how large the table is.
If it is still too slow
If your application is still too slow, you need to talk to the database administrator. If you are the database administrator as well as the programmer, you need to hire a database administrator ("dba").
A professional dba is great at finding queries that are pigs and building indices to make them faster. The dba might be able to suggest that you partion your tables so that infrequently used data are kept on a separate disk drive. The dba can make you extra tablespaces on separate physical disk drives. By moving partitions and indices to these separate disk drives, the dba can speed up your application by factors of 2 or 3.

A factor of 2 or 3? Sounds pretty good until you reflect on the fact that moving information from disk into RAM would speed things up by a factor of 100,000. This isn't really possible for database updates, which must be recorded in a durable medium (exception: fancy EMC disk arrays, which contain write caches and batteries to ensure durability of information in the write cache). However, it is relatively easy for queries. As a programmer, you can add indices and supply optimizer hints to increase the likelihood that your queries will be satisfied from Oracle's block cache. The dba can increase the amount of the server's RAM given over to Oracle. If that doesn't work, the dba can go out and order more RAM!


Oracle SQL High-Performance Tuning
Oracle8 Server Tuning

How do I delete an entire database ?

How do I delete an entire database ?

The key to removing a database is locating all of the files that may be relevant to it. Typically this will consist of:

database files
redo logs
archived logs
control files
OS related files (alert log, trace files)
UTL_FILE directories
Password files
SQL Net configuration files
(NT) Service entries
(NT) Registry entries
In most cases, the database assistant (dbassist or dbca on most platforms) can be used to delete a database as appropriate for the relavant platform. Alternatively, the following process can be used as a rough guide (assuming your database still is running)

Task How to
List database files select name from V$DATAFILE
List redo logs select member from V$LOGFILE
List archived logs select value from v$parameter where name like 'log_archive_dest%'
List control files select name from v$controlfile
List OS related files (alert log, trace files) select value from v$parameter where name like '%dest'
List UTL_FILE directories select value from v$parameter2 where name = 'utl_file_dir'
List Password files location will vary from platform to platform
List SQL Net configuration files Search for files listener.ora, tnsnames.ora, oratab, or use the Network manager tool to remove the appropriate entries
(NT) Service entries Services under NT
(NT) Registry entries Via regedit

You can now shut the database. In most cases, the appropriate files as determined from above can then be deleted, but don't forget common sense. For example, your listener.ora file may be shared by other databases, the directories for UTL_FILE may also be shared etc. Even your database files could be shared if you were running parallel server (or sharing read-only tablespaces between two totally separate databases)

-------------------------------------------------------------
How do I delete an entire database (UNIX only)?
The following shows the steps to drop a database in Unix enviroment. In order to delete a database, there are few things need to be taken care of. First, all the database related files eg *.dbf, *.ctl, *.rdo, *.arc need to be deleted. Then, the entry in listener.ora and tnsnames.ora need to be removed. Third, all the database links need to be removed since it will be invalid anyways.

It depends how you login to oracle account in Unix, you should have environment set for the user oracle. To confirm that the environment variable is set, do a env|grep ORACLE and you will notice that your ORACLE_SID=SOME_SID and ORACLE_HOME=SOME_PATH. If you do not already have the ORACLE_SID and ORACLE_HOME set, do it now.

Make sure also, that you set the ORACLE_SID and ORACLE_HOME correct else you will end up deleting other database. Next, you will have to query all the database related files from dictionaries in order to identify which files to delete. Do the following:

01. Login as connect / as sysdba at svrmgrl
02. startup the database if it's not already started. The database must at least mounted.
03. spool /tmp/deletelist.lst
04. select name from v$datafile; (This will get all the datafiles; alternatively, you can select file_name from dba_data_files)
05. select member from v$logfile;
06. select name from v$controlfile;
07. archive log list (archive_log_dest is where the archived destination is)
08. locating ifile by issuing show parameter ifile (alternatively, check the content of init.ora)
09. spool off
10. Delete in O/S level the files listed in /tmp/deletelist.lst
11. remove all the entries which refer to the deleted database in tnsnames.ora and listener.ora (located in $ORACLE_HOME/network/admin)
12. remove all database links referring to the deleted database.
13. check "/var/opt/oracle/oratab" to make sure there is no entry of the database deleted. If yes, remove it.
14. DONE


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

Cloning an Oracle Database

Cloning an Oracle Database

Author: Sridhar.Kasukurthi
sridhar.kasukurthi@gmail.com

Table of Contents


1. Purpose of the document 3
2. Document Usage 3
3. Overview of the Procedure 3
3.1 Introduction 3
3.2 Environments 4
3.3 Assumption 4
3.4 Cloning Process 4
3.4.1 Steps to be followed in Primary database: 4
3.4.2 Steps to be followed in the Secondary database server: 5
5. Conclusion 7




































1. Purpose of the document

This document illustrates a process to clone an Oracle database using hot backup. The following sections are covered in this document

Overview
Environments
Assumptions
Cloning process


2. Document Usage

This document can be useful for any Oracle DBA for cloning an Oracle database. Generally cloning databases with the help of source database hot backup is a standard process to effectively and efficiently create a replica of the source database. The process illustrated in this document can be used by any DBA who wants to create a replica of an Oracle database with the help of hot backup and the associated archive logs.


3. Overview of the Procedure
3.1 Introduction

Cloning database is one of the routine DBA activities. Any DBA who administers various environments and is supporting the application development team for database activities has to periodically synchronize the acceptance/testing/development with the Production data.

This frequency of synchronization depends upon the business and application development teams requirements. Generally this process can be done with the below mentioned procedures

1) EXPORT/IMPORT utilities
2) DATA PUMP
3) Cold Backup Restoration
4) Hot Backup Recovery

Each of the above mentioned process has got its own pros and cons. To state a few vital reasons why Hot backup recovery process is chosen widely

a) You can recover to the most recent time period.
b) Doesn’t require outage of the source database
c) Requires less time compared to the other process
d) Best method for large databases


3.2 Environments

1. Primary or the Source database system
2. Secondary or the Target database system

3.3 Assumption

Primary database operated in ARCHIVELOG mode.
Secondary database to be created by cloning in a different server
Primary and Secondary environment Operating system are same.


3.4 Cloning Process
3.4.1 Steps to be followed in Primary database:


1) Perform HOTBACKUP of all the data files

2) LSN number

Login as sys as sysdba

Sql> Alter system archive log current;
Sql> Archive log list

Note down the Current Log Sequence

3) Control file trace

In Sql> prompt issue the statement

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

You can find the trace file in the udump directory.













3.4.2 Steps to be followed in the Secondary database server:

1) If the secondary database is to be created in a different server
FTP the following to a temporary location in the secondary database server.

a) hot backup of all the primary database data files
b) all the archive log files generated in the primary database after you start hot backup
c) control file trace of primary database
d) pfile of the primary database

2) Edit the pfile as it would be for secondary database server locations

a) Control_files- new locations
b) instance_name – new name
c) background_dump_dest – new location
d) core_dump_dest- new location
e) user_dump_dest – new location
f) log_archive_dest- new location


Save the pfile as init_.ora in

$ORACLE_HOME/dbs directory if UNIX
$ORACLE_HOME\database if WINDOWS

Where SID is the instance name given above in the edited pfile

3) In the control file trace Use the “SET #2. RESTLOGS case” CREATE CONTROLFILE section (Since we don’t use the primary database redolog files, we use this option). In that section Copy from “CREATE CONTROLFILE” to “CHARACTER SET ” to another file.
Save the file as cr_control.sql

Edit the following section in cr_control.sql

a) Use SET instead of REUSE

Old: CREATE CONTROLFILE REUSE DATABASE <"PRIMARY_DBNAME" >RESETLOGS

NEW: CREATE CONTROLFILE SET DATABASE <"SECONDARY_DBNAME"> RESETLOGS

b) Edit the LOGFILE paths as per the secondary database environment
c) Edit the DATAFILE paths as per the secondary database environment
d) Save the cr_control.sql file








4) Move the ftp ed Hot backup DATAFILE from temporary location of specific location as specified in the control file cr_control.sql

5) Move the archived logs from temporary location to the location specified in log_archive_dest in pfile

6) If Windows,

Create an oracle service using ORADIM utility


ORADIM -NEW -SID sid | -SRVC service [-INTPWD password] [-MAXUSERS number] [-STARTMODE a|m] [-PFILE file] [-TIMEOUT secs]

In the CMD prompt

SET ORACLE_SID=

Sqlplus “/ as sysdba”

You will get a Connected to an idle instance message.


7) If UNIX,

In the $ prompt give

EXPORT ORACLE_SID=

Sqlplus “/ as sysdba”

You will get a Connected to an idle instance message.


8) In the SQL> Prompt give

STARTUP NOMOUNT


9) Run the cr_control.sql file in the SQL> prompt

If Windows

@/cr_control.sql

If Unix

@\cr_control.sql if in Unix

You should get a message like Control File created




9) Recover the database using backup control file. In the SQL> prompt give

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Note: this can also be done with UNTIL TIME or UNTIL SCN provided you know the time or the SCN until which you want to perform the recovery

10) The recovery process will keep prompting for archive logs. Give return for each prompt of archive log.

Since the required archive logs from Primary database are restored in archive_log_dest of secondary, it will get applied for every return you give in the prompt.

Once the recovery process prompts for the archive log with the LSN number noted in the Primary database, in step 2) of Steps to be done in Primary database section

Give CANCEL in the recovery sql prompt

11) Now you must get a message like

Media recovery cancelled.

12) Now in the SQL prompt give.

ALTER DATABASE OPEN RESETLOGS;

You should get a message like.

Database opened.

12) Bounce the database

SHUTDOWN IMMEDIATE;
STARTUP;

13) Check for errors in bdump.

14) Check log switch.

15) Check redo log archival

16) Compare the users, objects, size with the Primary database

17) Take a complete cold back up and maintain that as generation 0 backup.

You have cloned the database.


5. Conclusion
Hot backup recovery is one of the best methods to clone database because there is no outage or down time required in the primary database and data is replicated in secondary database to the most recent time period.

ORAPWD Utility

Page 1 of 3
ORAPWD Utility
As Of Oracle9i Connect Internal Is No Longer Supported. By Default, The User SYS Is The Only
User That Has The SYSDBA Privilege. Oracle ORAPWD Utility Assists The DBA With Granting
SYSDBA And SYSOPER Privileges To Other Users.
Creating A Password File Via ORAPWD Enables Remote Users To Connect With Administrative
Privileges Through SQL*Net.
If ORAPWD Has Not Yet Been Executed, Attempting To Grant SYSDBA Or SYSOPER Privileges
Will Result In The Following Error:
SQL> Grant SYSDBA To Scott;
ORA-01994: GRANT Failed: Cannot Add Users To Public Password File
The Following Steps Can Be Performed To Grant Other Users These Privileges:
1. Create The Password File.
This Is Done By Executing The Following Command:
$ ORAPWD file= password= entries=
where
File Specifies The Name Of The File That Will Hold The Password Information. The
Default Location Will Be Current Directory Through Which This Utility Is Executed,
But Must Be Preferably Set To oracle_home/database Directory In Window
Platforms And oracle_home/dbs In Unix Platforms.
And, Also The Filename Should Be Named As PWD.ORA
The Contents Are Encrypted And Are Unreadable.
Password The Required Password Is The One For The SYS User Of The Database
Entries Specified As i.e. An Integer Value Which Denotes The Number Of
Database Users That Cab Be Granted SYSDBA OR SYSOPER Privilege.
This Parameter Should Be Set To A Higher Value Than The Number Of Anticipated
Users To Prevent Having To Delete And Recreate The Password File.
Page 2 of 3
2. Edit The Init.Ora Parameter REMOTE_LOGIN_PASSWORDFILE.
In Addition To Creating The Password File, You Must Also Set The Database Parameter Files
REMOTE_LOGIN_PASSWORDFILE Parameter To Either NONE Or EXCLUSIVE Or
SHARED.
Where
None
(default)
Oracle Ignores Any Password File. Privileged Users Must Be Authenticated By The
Operating System. This Is The Default Value For This Parameter.
Exclusive An EXCLUSIVE Password File Can Be Used With Only One Database. An
EXCLUSIVE File Can Contain The Names Of Users Other Than SYS. Using An
EXCLUSIVE Password File Lets You Grant SYSDBA And SYSOPER System
Privileges To Individual Users And Have Them Connect As Themselves.
Shared A SHARED Password File Can Be Used By Multiple Databases Running On The
Same Server. The Only User Recognized By A SHARED Password File Is SYS. All
Users Needing SYSDBA Or SYSOPER System Privileges Must Connect Using The
Same Name, SYS, And Password. You Cannot Change The Password For SYS If The
Password Fole Is SHARED.
SQL> Show Parameter Password
NAME TYPE VALUE
------------------------------------ ----------- ----------
remote_login_passwordfile String exclusive
3. Grant SYSDBA or SYSOPER to users.
When SYSDBA Or SYSOPER Privileges Are Granted To A User, That User's Name And Privilege
Information Are Added To The Password File.
SQL> Grant Sysdba To Scott;
Grant Succeeded.
4. Confirm which user is registered to password file.
You Can Query The Data Dictionary View Named V$PWFile_Users For Finding Users To Whom
You Have Granted SYSDBA OR SYSOPER Privilege.
SQL> Select * From V$Pwfile_Users;
USERNAME SYSDBA SYSOPER
------------------------------ ------ -------
SYS TRUE TRUE
SCOTT TRUE FALSE
Now The User SCOTT Can Connect As SYSDBA. Administrative Users Can Be Connected And
Authenticated To A Local Or Remote Database By Using The SQL*Plus Connect Command. They
Must Connect Using Their Username And Password, And With The AS SYSDBA Or AS SYSOPER
Clause:
SQL> Connect Scott/Tiger As Sysdba;
Connected.
SQL> Show User
USER is “SYS”
Page 3 of 3
SQL> Connect Scott/Tiger
Connected.
SQL> Show User
USER is “SCOTT”
Removing The Password File
If You Determine That You No Longer Require A Password File To Authenticate Users, You Can
Delete The Password File And Reset The REMOTE_LOGIN_PASSWORDFILE Init.Ora
Parameter To NONE. After You Remove This File, Only Those Users Who Can Be Authenticated
By The Operating System Can Perform Database Administration Operations.
Do Not Remove Or Modify The Password File If You Have A Database Or Instance Mounted
Using REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE Or SHARED. If You Do, You Will
Be Unable To Reconnect Remotely Using The Password File. Even If You Replace It, You Cannot
Use The New Password File, Because The Timestamps And Checksums Will Be Wrong.
Changing The Password For The SYS User.
Either Of These Commands Will Change The Password For The SYS User:
ORAPWD Force=Y File=Orapwsid Password=New_Password
-- OR --
SQL> Alter User Sys Identified By New_Password
PDF created with pdfFactory trial version www.pdffactory.com

Bitmap Index vs. B-tree Index: Which and When?

Bitmap Index vs. B-tree Index: Which and When?

Understanding the proper application of each index can have a big impact on performance.
Conventional wisdom holds that bitmap indexes are most appropriate for columns having low distinct values—such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not completely accurate, however. In reality, a bitmap index is always advisable for systems in which data is not frequently updated by many concurrent systems. In fact, as I'll demonstrate here, a bitmap index on a column with 100-percent unique values (a column candidate for primary key) is as efficient as a B-tree index.
In this article I'll provide some examples, along with optimizer decisions, that are common for both types of indexes on a low-cardinality column as well as a high-cardinality one. These examples will help DBAs understand that the usage of bitmap indexes is not in fact cardinality dependent but rather application dependent.
Comparing the Indexes
There are several disadvantages to using a bitmap index on a unique column—one being the need for sufficient space (and Oracle does not recommend it). However, the size of the bitmap index depends on the cardinality of the column on which it is created as well as the data distribution. Consequently, a bitmap index on the GENDER column will be smaller than a B-tree index on the same column. In contrast, a bitmap index on EMPNO (a candidate for primary key) will be much larger than a B-tree index on this column. But because fewer users access decision-support systems (DSS) systems than would access transaction-processing (OLTP) ones, resources are not a problem for these applications.
To illustrate this point, I created two tables, TEST_NORMAL and TEST_RANDOM. I inserted one million rows into the TEST_NORMAL table using a PL/SQL block, and then inserted these rows into the TEST_RANDOM table in random order:
Create table test_normal (empno number(10), ename varchar2(30), sal number(10));

Begin
For i in 1..1000000
Loop
Insert into test_normal
values(i, dbms_random.string('U',30), dbms_random.value(1000,7000));
If mod(i, 10000) = 0 then
Commit;
End if;
End loop;
End;
/

Create table test_random
as
select /*+ append */ * from test_normal order by dbms_random.random;

SQL> select count(*) "Total Rows" from test_normal;

Total Rows
----------
1000000

Elapsed: 00:00:01.09

SQL> select count(distinct empno) "Distinct Values" from test_normal;

Distinct Values
---------------
1000000

Elapsed: 00:00:06.09
SQL> select count(*) "Total Rows" from test_random;

Total Rows
----------
1000000

Elapsed: 00:00:03.05
SQL> select count(distinct empno) "Distinct Values" from test_random;

Distinct Values
---------------
1000000

Elapsed: 00:00:12.07
Note that the TEST_NORMAL table is organized and that the TEST_RANDOM table is randomly created and hence has disorganized data. In the above table, column EMPNO has 100-percent distinct values and is a good candidate to become a primary key. If you define this column as a primary key, you will create a B-tree index and not a bitmap index because Oracle does not support bitmap primary key indexes.
To analyze the behavior of these indexes, we will perform the following steps:
1. On TEST_NORMAL:
A. Create a bitmap index on the EMPNO column and execute some queries with equality predicates.
B. Create a B-tree index on the EMPNO column, execute some queries with equality predicates, and compare the logical and physical I/Os done by the queries to fetch the results for different sets of values.
2. On TEST_RANDOM:
A. Same as Step 1A.
B. Same as Step 1B.
3. On TEST_NORMAL:
A. Same as Step 1A, except that the queries are executed within a range of predicates.
B. Same as Step 1B, except that the queries are executed within a range of predicates. Now compare the statistics.
4. On TEST_RANDOM:
A. Same as Step 3A.
B. Same as Step 3B.
5. On TEST_NORMAL:
A. Create a bitmap index on the SAL column, and then execute some queries with equality predicates and some with range predicates.
B. Create a B-tree index on the SAL column, and then execute some queries with equality predicates and some with range predicates (same set of values as in Step 5A). Compare the I/Os done by the queries to fetch the results.
6. Add a GENDER column to both of the tables, and update the column with three possible values: M for male, F for female, and null for N/A. This column is updated with these values based on some condition.
7. Create a bitmap index on this column, and then execute some queries with equality predicates.
8. Create a B-tree index on the GENDER column, and then execute some queries with equality predicates. Compare to results from Step 7.
Steps 1 to 4 involve a high-cardinality (100-percent distinct) column, Step 5 a normal-cardinality column, and Steps 7 and 8 a low-cardinality column.
Step 1A (on TEST_NORMAL)
In this step, we will create a bitmap index on the TEST_NORMAL table and then check for the size of this index, its clustering factor, and the size of the table. Then we will run some queries with equality predicates and note the I/Os of these queries using this bitmap index.
SQL> create bitmap index normal_empno_bmx on test_normal(empno);

Index created.

Elapsed: 00:00:29.06
SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;


Table analyzed.

Elapsed: 00:00:19.01
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3* where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_BMX');

SEGMENT_NAME Size in MB
------------------------------------ ---------------
TEST_NORMAL 50
NORMAL_EMPNO_BMX 28

Elapsed: 00:00:02.00
SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME CLUSTERING_FACTOR
------------------------------ ---------------------------------
NORMAL_EMPNO_BMX 1000000

Elapsed: 00:00:00.00
You can see in the preceding table that the size of the index is 28MB and that the clustering factor is equal to the number of rows in the table. Now let's execute the queries with equality predicates for different sets of values:
SQL> set autotrace only
SQL> select * from test_normal where empno=&empno;
Enter value for empno: 1000
old 1: select * from test_normal where empno=&empno
new 1: select * from test_normal where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=4 Car
d=1 Bytes=34)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_EMPNO_BMX'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Step 1B (on TEST_NORMAL)
Now we will drop this bitmap index and create a B-tree index on the EMPNO column. As before, we will check for the size of the index and its clustering factor and execute the same queries for the same set of values, to compare the I/Os.
SQL> drop index NORMAL_EMPNO_BMX;

Index dropped.

SQL> create index normal_empno_idx on test_normal(empno);

Index created.

SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3 where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_IDX');

SEGMENT_NAME Size in MB
---------------------------------- ---------------
TEST_NORMAL 50
NORMAL_EMPNO_IDX 18

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME CLUSTERING_FACTOR
---------------------------------- ----------------------------------
NORMAL_EMPNO_IDX 6210
It is clear in this table that the B-tree index is smaller than the bitmap index on the EMPNO column. The clustering factor of the B-tree index is much nearer to the number of blocks in a table; for that reason, the B-tree index is efficient for range predicate queries.
Now we'll run the same queries for the same set of values, using our B-tree index.
SQL> set autot trace
SQL> select * from test_normal where empno=&empno;
Enter value for empno: 1000
old 1: select * from test_normal where empno=&empno
new 1: select * from test_normal where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=4 Car
d=1 Bytes=34)
2 1 INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE) (C
ost=3 Card=1)

Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see, when the queries are executed for different set of values, the number of consistent gets and physical reads are identical for bitmap and B-tree indexes on a 100-percent unique column.
BITMAP EMPNO B-TREE
Consistent Reads Physical Reads Consistent Reads Physical Reads
5 0 1000 5 0
5 2 2398 5 2
5 2 8545 5 2
5 2 98008 5 2
5 2 85342 5 2
5 2 128444 5 2
5 2 858 5 2
Step 2A (on TEST_RANDOM)
Now we'll perform the same experiment on TEST_RANDOM:
SQL> create bitmap index random_empno_bmx on test_random(empno);

Index created.

SQL> analyze table test_random compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3* where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_BMX');

SEGMENT_NAME Size in MB
------------------------------------ ---------------
TEST_RANDOM 50
RANDOM_EMPNO_BMX 28

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME CLUSTERING_FACTOR
------------------------------ ---------------------------------
RANDOM_EMPNO_BMX 1000000
Again, the statistics (size and clustering factor) are identical to those of the index on the TEST_NORMAL table:
SQL> select * from test_random where empno=&empno;
Enter value for empno: 1000
old 1: select * from test_random where empno=&empno
new 1: select * from test_random where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=34)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'RANDOM_EMPNO_BMX'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Step 2B (on TEST_RANDOM)
Now, as in Step 1B, we will drop the bitmap index and create a B-tree index on the EMPNO column.
SQL> drop index RANDOM_EMPNO_BMX;

Index dropped.

SQL> create index random_empno_idx on test_random(empno);

Index created.

SQL> analyze table test_random compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3 where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_IDX');

SEGMENT_NAME Size in MB
---------------------------------- ---------------
TEST_RANDOM 50
RANDOM_EMPNO_IDX 18

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME CLUSTERING_FACTOR
---------------------------------- ----------------------------------
RANDOM_EMPNO_IDX 999830
This table shows that the size of the index is equal to the size of this index on TEST_NORMAL table but the clustering factor is much nearer to the number of rows, which makes this index inefficient for range predicate queries (which we'll see in Step 4). This clustering factor will not affect the equality predicate queries because the rows have 100-percent distinct values and the number of rows per key is 1.
Now let's run the queries with equality predicates and the same set of values.
SQL> select * from test_random where empno=&empno;
Enter value for empno: 1000
old 1: select * from test_random where empno=&empno
new 1: select * from test_random where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=34)
2 1 INDEX (RANGE SCAN) OF 'RANDOM_EMPNO_IDX' (NON-UNIQUE) (Cost=3 Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Again, the results are almost identical to those in Steps 1A and 1B. The data distribution did not affect the amount of consistent gets and physical reads for a unique column.
Step 3A (on TEST_NORMAL)
In this step, we will create the bitmap index (similar to Step 1A). We know the size and the clustering factor of the index, which equals the number of rows in the table. Now let's run some queries with range predicates.
SQL> select * from test_normal where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1: select * from test_normal where empno between &range1 and &range2
new 1: select * from test_normal where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=451 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=451 Card=2299 Bytes=78166)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_BMX'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
331 consistent gets
0 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed
Step 3B (on TEST_NORMAL)
In this step, we'll execute the queries against the TEST_NORMAL table with a B-tree index on it.
SQL> select * from test_normal where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1: select * from test_normal where empno between &range1 and &range2
new 1: select * from test_normal where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=23 Card=2299 Bytes=78166)
2 1 INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE) (Cost=8 Card=2299)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
329 consistent gets
15 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed
When these queries are executed for different sets of ranges, the results below show:
BITMAP EMPNO (Range) B-TREE
Consistent Reads Physical Reads Consistent Reads Physical Reads
331 0 1-2300 329 0
285 0 8-1980 283 0
346 19 1850-4250 344 16
427 31 28888-31850 424 28
371 27 82900-85478 367 23
2157 149 984888-1000000 2139 35
As you can see, the number of consistent gets and physical reads with both indexes is again nearly identical. The last range (984888-1000000) returned almost 15,000 rows, which was the maximum number of rows fetched for all the ranges given above. So when we asked for a full table scan (by giving the hint /*+ full(test_normal) */ ), the consistent read and physical read counts were 7,239 and 5,663, respectively.
Step 4A (on TEST_RANDOM)
In this step, we will run the queries with range predicates on the TEST_RANDOM table with bitmap index and check for consistent gets and physical reads. Here you'll see the impact of the clustering factor.
SQL>select * from test_random where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1: select * from test_random where empno between &range1 and &range2
new 1: select * from test_random where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:08.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=453 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=453 Card=2299 Bytes=78166)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (RANGE SCAN) OF 'RANDOM_EMPNO_BMX'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2463 consistent gets
1200 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed

Step 4B (on TEST_RANDOM)
In this step, we will execute the range predicate queries on TEST_RANDOM with a B-tree index on it. Recall that the clustering factor of this index was very close to the number of rows in a table (and thus inefficient). Here's what the optimizer has to say about that:
SQL> select * from test_random where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old 1: select * from test_random where empno between &range1 and &range2
new 1: select * from test_random where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:03.04

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=613 Card=2299 Bytes=78166)
1 0 TABLE ACCESS (FULL) OF 'TEST_RANDOM' (Cost=613 Card=2299 Bytes=78166)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6415 consistent gets
4910 physical reads
0 redo size
111416 bytes sent via SQL*Net to client
2182 bytes received via SQL*Net from client
155 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2300 rows processed
The optimizer opted for a full table scan rather than using the index because of the clustering factor:
BITMAP EMPNO (Range) B-TREE
Consistent Reads Physical Reads Consistent Reads Physical Reads
2463 1200 1-2300 6415 4910
2114 31 8-1980 6389 4910
2572 1135 1850-4250 6418 4909
3173 1620 28888-31850 6456 4909
2762 1358 82900-85478 6431 4909
7254 3329 984888-1000000 7254 4909
For the last range (984888-1000000) only, the optimizer opted for a full table scan for the bitmap index, whereas for all ranges, it opted for a full table scan for the B-tree index. This disparity is due to the clustering factor: The optimizer does not consider the value of the clustering factor when generating execution plans using a bitmap index, whereas for a B-tree index, it does. In this scenario, the bitmap index performs more efficiently than the B-tree index.
The following steps reveal more interesting facts about these indexes.
Step 5A (on TEST_NORMAL)
Create a bitmap index on the SAL column of the TEST_NORMAL table. This column has normal cardinality.
SQL> create bitmap index normal_sal_bmx on test_normal(sal);

Index created.

SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;

Table analyzed.
Now let's get the size of the index and the clustering factor.
SQL>select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2* from user_segments
3* where segment_name in ('TEST_NORMAL','NORMAL_SAL_BMX');

SEGMENT_NAME Size in MB
------------------------------ --------------
TEST_NORMAL 50
NORMAL_SAL_BMX 4

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME CLUSTERING_FACTOR
------------------------------ ----------------------------------
NORMAL_SAL_BMX 6001
Now for the queries. First run them with equality predicates:
SQL> set autot trace
SQL> select * from test_normal where sal=&sal;
Enter value for sal: 1869
old 1: select * from test_normal where sal=&sal
new 1: select * from test_normal where sal=1869

164 rows selected.

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=168 Bytes=4032)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=39 Card=168 Bytes=4032)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
165 consistent gets
0 physical reads
0 redo size
8461 bytes sent via SQL*Net to client
609 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
164 rows processed
and then with range predicates:
SQL> select * from test_normal where sal between &sal1 and &sal2;
Enter value for sal1: 1500
Enter value for sal2: 2000
old 1: select * from test_normal where sal between &sal1 and &sal2
new 1: select * from test_normal where sal between 1500 and 2000

83743 rows selected.

Elapsed: 00:00:05.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes
=2001024)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376
Bytes=2001024)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11778 consistent gets
5850 physical reads
0 redo size
4123553 bytes sent via SQL*Net to client
61901 bytes received via SQL*Net from client
5584 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
83743 rows processed
Now drop the bitmap index and create a B-tree index on TEST_NORMAL.
SQL> create index normal_sal_idx on test_normal(sal);

Index created.

SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;

Table analyzed.
Take a look at the size of the index and the clustering factor.
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3 where segment_name in ('TEST_NORMAL','NORMAL_SAL_IDX');

SEGMENT_NAME Size in MB
------------------------------ ---------------
TEST_NORMAL 50
NORMAL_SAL_IDX 17

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME CLUSTERING_FACTOR
------------------------------ ----------------------------------
NORMAL_SAL_IDX 986778

In the above table, you can see that this index is larger than the bitmap index on the same column. The clustering factor is also near the number of rows in this table.
Now for the tests; equality predicates first:
SQL> set autot trace
SQL> select * from test_normal where sal=&sal;
Enter value for sal: 1869
old 1: select * from test_normal where sal=&sal
new 1: select * from test_normal where sal=1869

164 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=169 Card=168 Bytes=4032)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=169 Card=168 Bytes=4032)
2 1 INDEX (RANGE SCAN) OF 'NORMAL_SAL_IDX' (NON-UNIQUE) (Cost=3 Card=168)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
177 consistent gets
0 physical reads
0 redo size
8461 bytes sent via SQL*Net to client
609 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
164 rows processed
...and then, range predicates:
SQL> select * from test_normal where sal between &sal1 and &sal2;
Enter value for sal1: 1500
Enter value for sal2: 2000
old 1: select * from test_normal where sal between &sal1 and &sal2
new 1: select * from test_normal where sal between 1500 and 2000

83743 rows selected.

Elapsed: 00:00:04.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes
=2001024)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376
Bytes=2001024)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11778 consistent gets
3891 physical reads
0 redo size
4123553 bytes sent via SQL*Net to client
61901 bytes received via SQL*Net from client
5584 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
83743 rows processed
When the queries were executed for different set of values, the resulting output, as shown in the tables below, reveals that the numbers of consistent gets and physical reads are identical.
BITMAP SAL (Equality) B-TREE Rows Fetched
Consistent Reads Physical Reads Consistent Reads Physical Reads
165 0 1869 177 164
169 163 3548 181 167
174 166 6500 187 172
75 69 7000 81 73
177 163 2500 190 175
BITMAP SAL (Range) B-TREE Rows Fetched
Consistent Reads Physical Reads Consistent Reads Physical Reads
11778 5850 1500-2000 11778 3891 83743
11765 5468 2000-2500 11765 3879 83328
11753 5471 2500-3000 11753 3884 83318
17309 5472 3000-4000 17309 3892 166999
39398 5454 4000-7000 39398 3973 500520
For range predicates the optimizer opted for a full table scan for all the different set of values—it didn't use the indexes at all—whereas for equality predicates, the optimizer used the indexes. Again, the consistent gets and physical reads are identical.
Consequently, you can conclude that for a normal-cardinality column, the optimizer decisions for the two types of indexes were the same and there were no significant differences between the I/Os.
Step 6 (add a GENDER column)
Before performing the test on a low-cardinality column, let's add a GENDER column to this table and update it with M, F, and null values.
SQL> alter table test_normal add GENDER varchar2(1);

Table altered.

SQL> select GENDER, count(*) from test_normal group by GENDER;

S COUNT(*)
- ----------
F 333769
M 499921
166310

3 rows selected.
The size of the bitmap index on this column is around 570KB, as indicated in the table below:
SQL> create bitmap index normal_GENDER_bmx on test_normal(GENDER);

Index created.

Elapsed: 00:00:02.08

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3 where segment_name in ('TEST_NORMAL','NORMAL_GENDER_BMX');

SEGMENT_NAME Size in MB
------------------------------ ---------------
TEST_NORMAL 50
NORMAL_GENDER_BMX .5625

2 rows selected.
In contrast, the B-tree index on this column is 13MB in size, which is much bigger than the bitmap index on this column.
SQL> create index normal_GENDER_idx on test_normal(GENDER);

Index created.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
2 from user_segments
3 where segment_name in ('TEST_NORMAL','NORMAL_GENDER_IDX');

SEGMENT_NAME Size in MB
------------------------------ ---------------
TEST_NORMAL 50
NORMAL_GENDER_IDX 13

2 rows selected.
Now, if we execute a query with equality predicates, the optimizer will not make use of this index, be it a bitmap or a B-tree. Rather, it will prefer a full table scan.
SQL> select * from test_normal where GENDER is null;

166310 rows selected.

Elapsed: 00:00:06.08

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=166310 Bytes=4157750)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=166310 Bytes=4157750)

SQL> select * from test_normal where GENDER='M';

499921 rows selected.

Elapsed: 00:00:16.07

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=499921 Bytes=12498025)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=499921Bytes=12498025)

SQL>select * from test_normal where GENDER='F'
/

333769 rows selected.

Elapsed: 00:00:12.02

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=333769 Byte
s=8344225)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=333769
Bytes=8344225)
Conclusions
Now that we understood how the optimizer reacts to these techniques, let's examine a scenario that clearly demonstrates the best respective applications of bitmap indexes and B-tree indexes.
With a bitmap index on the GENDER column in place, create another bitmap index on the SAL column and then execute some queries. The queries will be re-executed with B-tree indexes on these columns.
From the TEST_NORMAL table, you need the employee number of all the male employees whose monthly salaries equal any of the following values:
1000
1500
2000
2500
3000
3500
4000
4500
Thus:
SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';
This is a typical data warehouse query, which, of course, you should never execute on an OLTP system. Here are the results with the bitmap index in place on both columns:
SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';

1453 rows selected.

Elapsed: 00:00:02.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=198 Card=754 Bytes=18850)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=198 Card=754 Bytes=18850)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP AND
4 3 BITMAP OR
5 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
6 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
7 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
8 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
9 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
10 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
11 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
12 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
13 4 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
14 3 BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_GENDER_BMX'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1353 consistent gets
920 physical reads
0 redo size
75604 bytes sent via SQL*Net to client
1555 bytes received via SQL*Net from client
98 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1453 rows processed
And with the B-tree index in place:
SQL>select * from test_normal
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';

1453 rows selected.

Elapsed: 00:00:03.01

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=754 Bytes=18850)
1 0 TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=754 Bytes=18850)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6333 consistent gets
4412 physical reads
0 redo size
75604 bytes sent via SQL*Net to client
1555 bytes received via SQL*Net from client
98 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1453 rows processed
As you can see here, with the B-tree index, the optimizer opted for a full table scan, whereas in the case of the bitmap index, it used the index to answer the query. You can deduce performance by the number of I/Os required to fetch the result.
In summary, bitmap indexes are best suited for DSS regardless of cardinality for these reasons:
• With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)
• With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).
• Most important, bitmap indexes in DSS systems support ad hoc queries, whereas B-tree indexes do not. More specifically, if you have a table with 50 columns and users frequently query on 10 of them—either the combination of all 10 columns or sometimes a single column—creating a B-tree index will be very difficult. If you create 10 bitmap indexes on all these columns, all the queries can be answered by these indexes, whether they are queries on all 10 columns, on 4 or 6 columns out of the 10, or on a single column. The AND_EQUAL hint provides this functionality for B-tree indexes, but no more than five indexes can be used by a query. This limit is not imposed with bitmap indexes.
In contrast, B-tree indexes are well suited for OLTP applications in which users' queries are relatively routine (and well tuned before deployment in production), as opposed to ad hoc queries, which are much less frequent and executed during nonpeak business hours. Because data is frequently updated in and deleted from OLTP applications, bitmap indexes can cause a serious locking problem in these situations.
The data here is fairly clear. Both indexes have a similar purpose: to return results as fast as possible. But your choice of which one to use should depend purely on the type of application, not on the level of cardinality.

Application Tuning Using Extended SQL Trace And TKPROF

Introduction

Sometimes it’s useful to be able to trace an Oracle session. This could be because you’ve written a batch process, it’s taking a long time to run, and you want to find out what’s causing it to run slow. It could also be because you’ve written a web or desktop application, and users complain that certain parts of the application run too slow. By tracing an Oracle session, which contains one or more SQL statements executed in order, you want find out precisely what statements your session executed, how they were executed by Oracle, and what delays occurred as the statements were executed.

This is particularly useful when you’ve written a batch job or program that, when run in isolation, runs very quickly, but when you try and run multiple copies of it concurrently the response time increases exponentially. This is usually because each concurrent program is fighting over the same database blocks and Oracle resources at the same time, or because an underlying inefficiency in your program becomes more apparent when multiple copies are run at the same time.

One of the features that Oracle has got that you don’t find in databases like Access, SQL Server and DB2 is that the database kernel is ‘instrumented’ and, more importantly, you can easily ask Oracle to provide you with a detailed breakdown of exactly what happens when you execute a set of queries. By tracing your Oracle session or batch job, you can list out the statements that you’ve executed, the cost of each statement, and, using the Oracle kernel diagnostics, list out in detail what events happened in the background that caused your statements to run slower than you expected.

This document sets out how to enable SQL tracing for any Oracle database (8i, 9i, 10g), how to format and interpret the trace files, and where to look next to resolve your performance issues.

Scenario 1 : PL/SQL Simulate Workload, single process.

To help explain how tracing in Oracle works, I have provided some PL/SQL code that simulates two typical workloads, with code at the start of the procedure that turns on extended SQL tracing for the rest of the session. The code works off of the sample data within the SCOTT/TIGER schema found within every Oracle database.

The first bit of PL/SQL code simulates a typical workload. Note – no comments about the code, this is just a rough and ready set of inserts, updates, selects and deletes put together to simulate a workload. (download PL/SQL code)

CREATE OR REPLACE procedure simulate_workload_with_binds
as
var1 varchar2(100);
begin

-- turn on extended SQL tracing

execute immediate 'alter session set timed_statistics = true';
execute immediate 'alter session set max_dump_file_size = unlimited';
execute immediate 'alter session set tracefile_identifier = ''test1_binds_single_process''';
execute immediate 'alter session set events ''10046 trace name context forever, level 8''';

-- main body of code

for c in 1 .. 500 loop
insert into test (col1) values (c);
commit;
end loop;

commit;

for c in 1 .. 50 loop
update test
set col1 = col1;
commit;
end loop;

for c in 1 .. 500 loop
begin
select col1
into var1
from test
where col1 = c;
exception
when others then null;
end;
end loop;

for c in 1 .. 500 loop
insert into test(col1) values (c);
commit;
end loop;

commit;

for c in 1 .. 500 loop
delete from test where col1=c;
commit;
end loop;

commit;

execute immediate 'alter session set events ''10046 trace name context off''';

end;
/

points to note :

The following code turns on what’s termed ‘extended SQL tracing’ for this session:

-- turn on extended SQL tracing

execute immediate 'alter session set timed_statistics = true';
execute immediate 'alter session set max_dump_file_size = unlimited';
execute immediate 'alter session set tracefile_identifier = ''test_1 _binds_single_process''';
execute immediate 'alter session set events ''10046 trace name context forever, level 8''';

“alter session set timed_statistics=true” ensures that the trace data contains timings for the various events and statements contained within it.

“alter session set max_dump_file_size = unlimited” ensures that your trace doesn’t prematurely end because you’ve reached the default max dump file size.

“alter session set tracefile_identifier = ‘’test1_binds_single_process’’” puts the specified text into the trace file name, making it easier to identify later

“alter session set events ‘’10046 trace name context forever, level 8’’’” is the important line, and tells Oracle to emit trace data, including information on ‘wait events’ (this is what the level 8 bit refers to) to the trace file.

Note the bit about ‘extended SQL tracing’. It’s called ‘extended’ because it’s like regular SQL tracing (which you can turn on by issuing the command Alter session set sql_trace=true ) but it additionally captures diagnostic information on what’s called ‘wait events’ – behind the scenes events that delay your SQL statement – events such as locks on tables, contention for disk blocks and so on. Also, with later versions of Oracle, you can active extended SQL trace using new built in Oracle packages - however, the method detailed above works for all Oracle versions, doesn’t require you to install any additional packages, and captures all the information we need for tracing. Note also that it’s possible to interactively turn on tracing for a session using the SQL*Plus prompt, and you can also turn it on for sessions other than your own – however for simplicity’s sake I’m embedding the relevant code directly into the PL/SQL procedure. For more details on the various ways to invoke extended SQL tracing see this presentation by David Kurtz.

The PL/SQL code is the compiled, and then executed with the commands :

SQL> exec simulate_workload;

PL/SQL procedure successfully completed.

The trace file is then located (within my c:\oracle\admin\markr9i\udump directory) and you can download the raw trace file here. Interpreting this raw trace file is outside the scope of this document, but if you’re interested more details can be found in the book “Optimising Oracle Performance” by Cary Millsap and Jeff Holt, a copy of which can be found in the office.

The next step is to use TKPROF (provided by default with all recent Oracle installations) to process and report on this raw trace file. From the DOS command line, find the raw trace file, and issue the command:

TKPROF raw_trace_file_name formatted_trace_file_name

e.g.

C:\oracle\admin\markr9i\udump>tkprof markr9i_ora_5820_test1_binds_single_process
.trc markr9i_ora_5820_test1_binds_single_process.txt

TKPROF: Release 9.2.0.5.0 - Production on Mon Oct 4 11:03:48 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


C:\oracle\admin\markr9i\udump>

Then, open up the formatted trace file (download here) with notepad, textpad or similar.

The formatted trace file has the following sections:

1. The preamble that tells you about TKPROF formatted trace files, and the SQL statement that enabled extended SQL trace.
TKPROF: Release 9.2.0.5.0 - Production on Mon Oct 4 14:19:34 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: markr9i_ora_5820_test1_binds_single_process.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

alter session set events '10046 trace name context forever, level 8'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
********************************************************************************

2. The statements that were executed as part of the PL/SQL packages.
INSERT INTO TEST (COL1)
VALUES
(:B1)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 500 0.09 0.08 0 1 1007 500
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 501 0.09 0.08 0 1 1007 500

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
********************************************************************************

COMMIT


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 1553 0.08 0.07 0 0 1550 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1560 0.08 0.07 0 0 1550 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
********************************************************************************

UPDATE TEST SET COL1 = COL1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 50 6.12 6.19 0 452 27078 25000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 51 6.12 6.19 0 452 27078 25000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
undo segment extension 293 0.00 0.00
********************************************************************************

select file#
from
file$ where ts#=:1


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 2 0.00 0.01 2 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 2 3 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID FILE$
1 INDEX RANGE SCAN I_FILE2 (object id 42)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.01
********************************************************************************

SELECT COL1
FROM
TEST WHERE COL1 = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 500 0.01 0.00 0 0 0 0
Fetch 500 0.09 0.08 0 4500 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1001 0.10 0.09 0 4500 0 500

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
********************************************************************************

INSERT INTO TEST(COL1)
VALUES
(:B1)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 500 0.09 0.09 0 2 1026 500
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 501 0.09 0.09 0 2 1026 500

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
********************************************************************************

DELETE FROM TEST
WHERE
COL1=:B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 500 0.25 0.25 0 4500 1516 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 501 0.25 0.25 0 4500 1516 1000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
********************************************************************************

alter session set events '10046 trace name context off'


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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)

Taking the first PL/SQL commands and looking at them more detail

for c in 1 .. 500 loop
insert into test (col1) values (c);
commit;
end loop;

This results in the following formatted trace output:

INSERT INTO TEST (COL1)
VALUES
(:B1)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 500 0.09 0.08 0 1 1007 500
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 501 0.09 0.08 0 1 1007 500

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
********************************************************************************

The PL/SQL commands set up a loop that executes 500 times, with the value of the loop counter being inserted into the ‘test’ table each time the loop executes.

What the trace file tells us is that a single command was parsed once, then executed 500 times. This is because PL/SQL uses bind variables by default, and Oracle would therefore only need to hard parse the statement once, with subsequent executions being able to use the stored parsed value of the statement each time a new value was inserted. For more details on bind variables, see http://www.rittman.net/archives/000832.html. Therefore, this section of the trace file refers to 500 separate SQL code executions, and the trace file has wrapped these up into a single section of the trace file.

It’s also useful to look at the ‘totals’ at the bottom of the statement breakdown, that show you the cpu time taken up by the statements and the total elapsed time. Note that the elapsed time is shorter than the CPU time – this is a rounding error and infact the elapsed time in this case should be considered to be the CPU time or a few microseconds more.

Once you have reviewed the rest of the SQL statements in the trace file, move on to the final part of the trace file.

3. The trace file summary
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 14 0.00 0.00 0 0 0 0
Execute 3606 6.64 6.70 0 4955 32177 27000
Fetch 502 0.09 0.09 2 4503 0 501
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4122 6.73 6.80 2 9458 32177 27501

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
undo segment extension 293 0.00 0.00
db file sequential read 2 0.00 0.01

14 user SQL statements in session.
1 internal SQL statements in session.
15 SQL statements in session.
********************************************************************************
Trace file: markr9i_ora_5820_test1_binds_single_process.trc
Trace file compatibility: 9.02.00
Sort options: default

1 session in tracefile.
14 user SQL statements in trace file.
1 internal SQL statements in trace file.
15 SQL statements in trace file.
9 unique SQL statements in trace file.
6053 lines in trace file. 5728 lines in trace file.

The main Area of interest in this section is the ‘Overall Totals For All Recursive Statements’ section. This is the SQL statements contained within your PL/SQL package and comprises the totals for all parses, executes and fetches incurred by your statements. In this particular bit of PL/SQL, bind variables are used and statements are executed many times, which leads to a much smaller amount of parses than executes – which is generally a good thing.

If your parses were more or less the same as your executes, you should check your code to make sure bind variables are being used – in PL/SQL, it is difficult to not use bind variables, but with Java, VB etc you have to consciously use them, and therefore it’s not unusual to find excessive amounts of parsing in their traced code, which is generally a bad thing as parsing ties up the CPU and prevents the application scaling if many users are using the program concurrently.

Note also the totals, where the elapsed time is only slightly higher than the CPU time – this shows that the program only took slightly longer to execute than the CPU time it required, meaning that it wasn’t unduly delayed by waiting for contended resources, network traffic and so on. If you were tuning this application, you wouldn’t really worry about this sort of profile.

Lastly, although it’s not really an issue for this simulated workload, note also the columns headed ‘query’ ‘current’ and ‘rows’ – these detail the amount of rows, blocks and so on that the query requested and worked on – if your program appears to be requesting and processing an excessive number of rows, look to reduce these (by altering the SQL statements) to bring down the total amount of data the query is looking to process.

Scenario 2 : PL/SQL Simulated Workload, 5 concurrent processes.

This scenario looks at a more common situation, where a program that previously performed without too much of an issue now is exponentially slower when a larger number of copies are run concurrently.

To test this, a line in the PL/SQL program was changed to alter the trace file identifier:

-- turn on extended SQL tracing

execute immediate 'alter session set timed_statistics = true';
execute immediate 'alter session set max_dump_file_size = unlimited';
execute immediate 'alter session set tracefile_identifier = ''test2_binds_concurrent_processes''';
execute immediate 'alter session set events ''10046 trace name context forever, level 8''';

(download the altered PL/SQL package here) and then five copies of the program were executed simultaneously, by opening up five DOS command windows and then executing the PL/SQL program within each session at the same time.

The first thing that you will notice is that each copy of the program takes considerably longer to run when run concurrently with the others. In short, this is because each copy is trying to insert, update, select from and delete from the same table, leading to contention for resources.

You will notice that five separate trace files have been output into the /udump directory, with a different process ID embedded within each filename. Assuming that all processes were started at the same time, take one trace file (download here) and process it using TKPROF:

C:\oracle\admin\markr9i\udump>tkprof markr9i_ora_2652_test2_binds_concurrent_pro
cesses.trc markr9i_ora_2652_test2_binds_concurrent_processes.txt

TKPROF: Release 9.2.0.5.0 - Production on Mon Oct 4 12:50:46 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


C:\oracle\admin\markr9i\udump>

Then, open up the formatted trace file (download here) and view using Notepad.exe. You will find the same structure within the trace file, the same order of statements and so on. However, looking at for example the set of SQL statements analyzed before, note this time that the elapsed time is twice as much as the CPU time.

INSERT INTO TEST (COL1)
VALUES
(:B1)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 500 0.14 0.31 0 2 1021 500
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 501 0.14 0.31 0 2 1021 500

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
********************************************************************************

Also, look at the part of the trace file that analyzes the SQL statement

UPDATE TEST SET COL1 = COL1

In our original trace file (where the command was run in isolation) the trace file shows the following analysis:

UPDATE TEST SET COL1 = COL1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 50 6.13 6.27 0 451 25600 25000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 51 6.13 6.27 0 451 25600 25000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)
********************************************************************************

whilst in our current trace file, where the command was run concurrently, the trace file analysis shows:

UPDATE TEST SET COL1 = COL1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 50 114.77 781.11 6 277901 128304 124500
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 51 114.77 781.11 6 277901 128304 124500

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
enqueue 305 3.12 605.91
buffer busy waits 146 0.08 1.38
undo segment extension 162 0.00 0.00
db file sequential read 6 0.06 0.12
********************************************************************************

Our original trace file shows a total elapsed time of 6.27 seconds and a CPU time of 6.13 seconds, which is acceptable. However our current trace file shows a total elapsed time of 781.11 seconds and a CPU time of 114.77 seconds – our CPU time has risen dramatically, and our elapsed time is several times longer again.

However, note the section that follows the statement breakdown, headed by ‘Elapsed Times Include Waiting On The Following Events’. This is called the wait event analysis, and it tells us exactly what kept our statement waiting.

The key column is the ‘total waited’ column, and you can see in this instance that our statement was kept waiting for 605.91 seconds by the ‘enqueue’ wait event – which means ‘locks’. Looking back at our PL/SQL code, this problem is happening because each of the five copies of the program are trying to update the same columns in the same table, and more often than not the row is locked when a copy of the application needs to write to it.
Also, note the ‘buffer busy waits’ event. Buffer busy waits (http://www.dba-oracle.com/art_builder_bbw.htm) occur when an Oracle session needs to access a block in the buffer cache, but cannot because the buffer copy of the data block is locked. This buffer busy wait condition can happen for either of the following reasons:
• The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.

• Another session has the buffer block locked in a mode that is incompatible with the waiting session's request.
Buffer busy waits are happening in our system because each session is trying to access the same blocks in the database buffer, meaning that most of them have to wait until the other one has finished reading the buffer block. However, note that whilst the number of buffer busy waits is 50% of the enqueue waits, the total waited time is only a fraction of the wait time caused by enqueue events – therefore, if you were tuning this application, you would focus the vast majority of your time on solving the enqueue problem, as this will have the greatest impact on your application (i.e. it will potentially remove 605.91 seconds from the time your session takes to run, rather than the 1.38 seconds that would be removed by dealing with the buffer busy waits issue)
The rest of our statements in the formatted trace file also suffer from the buffer busy waits problem, but the total delay is very small compared to our enqueue problem detailed before.
SELECT COL1
FROM
TEST WHERE COL1 = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 500 0.00 0.00 0 0 0 0
Execute 500 0.00 0.00 0 0 0 0
Fetch 500 0.17 1.08 0 2499 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1500 0.17 1.09 0 2499 0 500

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS FULL TEST


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
buffer busy waits 27 0.05 0.23
latch free 2 0.01 0.01
********************************************************************************
INSERT INTO TEST(COL1)
VALUES
(:B1)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 500 0.09 0.56 0 149 1010 500
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 501 0.09 0.56 0 149 1010 500

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
buffer busy waits 4 0.05 0.13
********************************************************************************

DELETE FROM TEST
WHERE
COL1=:B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 500 0.71 3.91 0 8466 845 566
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 501 0.71 3.91 0 8466 845 566

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 59 (recursive depth: 1)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
buffer busy waits 81 0.06 1.11
latch free 11 0.03 0.06
enqueue 3 0.09 0.16
********************************************************************************
Note that the last statement that deletes from the test table also has issues with locking and latches, but the total contribution to total session time is very small.
Next, look at the summary section of the formatted trace file.
********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 512 0.00 0.00 0 0 0 0
Execute 3605 115.77 787.28 6 286518 132493 126066
Fetch 500 0.17 1.08 0 2499 0 500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4617 115.94 788.37 6 289017 132493 126566

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
enqueue 308 3.12 606.08
buffer busy waits 258 0.08 2.85
undo segment extension 162 0.00 0.00
db file sequential read 6 0.06 0.12
latch free 15 0.03 0.10

513 user SQL statements in session.
0 internal SQL statements in session.
513 SQL statements in session.
********************************************************************************
Trace file: markr9i_ora_2652_test2_binds_concurrent_processes.trc
Trace file compatibility: 9.02.00
Sort options: default

1 session in tracefile.
513 user SQL statements in trace file.
0 internal SQL statements in trace file.
513 SQL statements in trace file.
8 unique SQL statements in trace file.
9491 lines in trace file.

Looking at the ‘Overall Totals For All Recursive Statements’ section, it shows that the total CPU time was 115.94 and the total elapsed time was 788.37, with the difference between the two being the amount of time Oracle was having to wait around for resources or other wait events.

The ‘Elapsed times include waiting on following events’ section then summarises all of the wait events, with ‘enqueue’ again being by far the largest contributor to the total time taken up by the session.

The outcome of tracing this session would be that you would concentrate your efforts on resolving the locking problems being experienced by the UPDATE statement in your code, and this would have a major effect on the total running time of your application.
(Footnote : We did in fact also have a wait event section in the previous formatted trace file markr9i_ora_5820_test1_binds_single_process.txt:
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
undo segment extension 293 0.00 0.00
db file sequential read 2 0.00 0.01

However, the total waiting time was virtually nil – 0.01 seconds - and would not be something you would need to concern yourself with when tuning a particular session.)

Conclusions

Tuning your batches, statements and applications using extended SQL trace and TKPROF allows you to examine exactly what statements your code is executing, whether they used bind variables, how many rows they processes and what wait events occurred whilst they were trying to execute. By tuning your applications using this approach, you can determine exactly what is slowing your code down, allowing you to concentrate your efforts on eliminating those waits that add the most to your total execution time.

Tuning applications in this way takes much of the ‘guess work’ out of tuning Oracle applications and makes it a process based on logical steps and documented approaches rather than picking solutions out of the air and hoping they’l have an effect.

Further Reading

For more details on extended SQL tracing and TKPROF, take a look at the following

• Optimising Oracle Performance, Cary Millsap & Jeff Holt (considered to be the canonical text on tuning using wait event analysis)
• Effective Oracle By Design, Tom Kyte (pages 120-135)
• Oracle Performance Tuning 101, Gaja Krishna Vaidyanatha (chapter 4)
Also, take a look at these articles

• Diagnosing Oracle Performance Problems by Cary Millsap
• Oracle Performance Tuning Using Event 10046
• Watching Your users' every move: All about SQL Tracing by Thiru Vadivelu
• "Oracle System Performance Analysis Using Oracle Event 10046" by Cary Millsap & Jeff Holt
• "Diagnosing Performance Problems Using Extended Trace" by Cary Millsap
• Why a 99%+ Database Buffer Cache Hit Ratio is Not Ok by Cary Millsap, (free reg. Required)
• How to Manage an Oracle Optimization Project by Gary Goodman (free reg. required)
• Performance Management Myths and Facts by Cary Millsap
• Introduction to Method R and Hotsos Profiler by Cary Millsap (free reg. required)
• How to Activate Extended SQL Trace by Cary Millsap (free reg. required)
• Performance Problems from the Field, Part 1 by Cary Millsap (free reg. required)
Note : Extended SQL Tracing When Using Connection Pooling

One problem that comes up with extended SQL tracing is when your application is an application (typically Java, or .NET) that uses connection pooling. In this sort of situation, the statements for a typical user session can end up spread over several separate trace files, each one relating to a separate pooled connection. In addition (and this is the worst bit) each trace file can contain SQL statements for other users who are using the same pooled connection, mixing up their statements with yours.
Connection pooling is dealt with in the above books and articles, but in summary the problem is usually dealt with in one of the following ways.
• Oracle 10g has specific features (detailed in Tracing SQL in Oracle Database 10g by Kimberly Floss) which allow you to identify just those SQL statements that pertain to a particular user/application.
• Force all users to log off the system then run the application, and cut and paste the separate trace files into one before analyzing using TKPROF
• Run a separate copy of the application on a different port number (for example, port 81) and have this version connect using a dedicated connection, and then TKPROF the resultant trace file as normal.