Oracle blog

Enhancing Oracle Active Data Guard.

2011-09-08T00:09:00.000-07:00

I think Active Data Guard (ADG) is one of the best features Oracle has ever released and it is remarkably popular considering how easily read work load can be off loaded while guaranteeing data quality (due to the nature of binary replication, good luck to those who rely on logical replication for DR) and ability to ensure zero data loss.

Here are some of the enhancements which can make ADG even more adaptable:

1) Ability to create global temporary tables. Oracle doesn't have to generate redo for GTT's and hence they can be supported on ADG standby. This will enable batch jobs and ETL job's to run on the ADG standby.

2) Continue using parallel threads for redo apply when redo stream encounters new datafile addition. When you add new datafile on primary, redo apply will serialize on creating the datafile on ADG standby. When you add lot of datafiles in quick succession on primary, standby can fall behind by several minutes.

3) When new datafile addition fails on standby for any reason, manual fix requires ADG standby to go in "MOUNT" mode, which is an outage. Most common reason for datafile addition to fail is lack of space, once the space issue is addressed manual fix is to use "create datafile as" command which requires standby to be in "MOUNT" mode.

4) Ability to maintain partial physical standby for ADG. When you use RAC for database consolidation, multiple schemas become part of one big physical database. Not all applications have same business priority, it is very natural that only some of those schema's require read only traffic offload to ADG standby. Instead of creating full physical standby, you only want to create standby with the tablespaces you are interested in and it requires quite a bit of manual scripting and processes involved to take care of new datafiles being created on primary which are not part of the interested tablespaces. Oracle can introduce init parameters like "KEEP_TABLESPACES" and "UNKEEP_TABLESPACES" on the standby which can address this issue by managing new datafiles depending on how it falls into these parameters.

5) Block cleanout that happens as part of commit on primary database is non logged (not recorded in the redolog), which means commit time block cleanout can't happen on the standby. This will cause all read queries on ADG standby to keep visiting undo transaction table (one additional undo block visit for every data segment block that is not cleaned out). I think Oracle should log commit time block cleanout so that standby can also see it and this problem will be resolved.

6) Data Guard role switchover from standby to primary causing ADG standby to go from open read only state to "MOUNT" state and then to "OPEN" state causing all established connections to fail, which can extend total database outage. Although it is not significant, every second counts for availability.

7) Like "CURRENT_SCN" column in v$database, additional column for corresponding time would make it very easier to determine standby lag in real time. All other options currently available are either expensive to query for multiple executions or not fully reliable.

8) When you enable supplemental logging (or force logging) on the primary database, it only gets saved in the control file and this change will not go to the standby unless you regenerate standby control file from the primary or run that command explicitly on the standby too. This can cause huge problem, if you have logical standby or Goldengate being used, after you switchover to the standby. Oracle should record any change in supplemental logging definition to the dictionary also than just to the control file.

9) Ability to determine datafiles impacted by NOLOGGING changes on the standby. Querying "UNRECOVERABLE_TIME" from v$datafile doesn't yield correct result and customer's can falsely rely on this data. Since metadata about blocks impacted by NOLOGGING change is already logged, redo apply on the standby can update standby control file with this information and hence v$datafile can be queried reliably for determining nologging impacted datafiles.

10) Ability to use multiple processes for redo thread merging as part of redo apply for RAC databases. As redo generation is increasing with each release of Oracle and customers increasingly using RAC for database consolidation, one process may not be able to merge redo from all the threads fast enough for recovery slaves to consume. It can be a problem if large number of RAC nodes are being used and redo generation in most of the threads is quite high.

I've opened enhancement request for some of the above points.

Is Oracle ACID compliant database?

2011-08-24T00:17:00.000-07:00

I attended Jonathan Lewis's NOCOUG session on May 18th, 2011. Towards the end of his session, he demonstrated a test case suspending lgwr process using oradebug, issue commit for an open transaction in another session, and read the same data from third session successfully even though the second session's commit record is not yet preserved to disk.

You can read more about it at:

http://tonyhasler.wordpress.com/2011/08/22/why-is-acid-important/

http://jonathanlewis.wordpress.com/2011/08/19/redo-2/

http://www.freelists.org/post/oracle-l/ACID

This basically means Oracle is not fully ACID complaint.

Even though some people are categorizing it as an architectural limitation, it is a bug to the customer.

In reality it is very rare to run into this problem because:

1) Quite often we see that the session (or process) committing a transaction is the one moving on to finish other dependent transactions before returning acknowledgment back to the customer.

2) Session reading commit in flight data usually go on to initiate it's own transaction which will either get blocked by the same underlying redo problem or the whole transaction will fail if redo writes are still blocked.

3) Redo write times are typically under couple of milli seconds and hence the exposure window is very narrow.

4) Distributed databases are not that widely used

5) Event based scheduling with changes spanning more than 1 database is not very common, i.e. batch job waiting for an event to happen (can be simple change in status table) and then run the job.

Ironically, if an application is just reading data from one database and making decisions on transactions happening on other databases, then using active data guard standby (ADG) for the read part will solve the problem naturally, although the same problem can still happen in theory if both primary and ADG standby go down at the same time (It'd be very interesting to test this out).

Here is my take on solving this problem:

When a query is executed, Oracle computes current SCN and construct read consistent data as of that SCN. This is very fundamental to this problem, do they really have to run the query as of current SCN?

I think it won't add too much overhead, if lgwr can post the SCN, to SGA location, up to which it has written redo to the disk from log buffer. Every session executing sql statements can check this memory location for determining on disk SCN up to which redo was flushed and then execute the query as of that SCN. This will solve the problem.

It should not be done for DML's, including select for updates, all of which need to read the data as of current SCN.

By default Oracle should still maintain the current behavior but provide session/system/query level parameter to change the behavior of executing read only queries as of disk SCN.

Since Oracle can write redo out of scn order from log buffer to redo log files (recovery process does sort it by SCN before applying the redo), determining the correct on disk SCN may pose a challenge.

How to create self managing circular tables

2011-05-18T22:03:00.000-07:00

One of the most common tasks DBA's deal with often is to enforce purge policies of data life cycle management for tables. Same thing applies for the logging tables DBA's create internally to track or manage monitoring jobs, performance data, stats collection, auditing, etc.

Even though logging tables DBA's create don't typically grow to gigabytes in size, it'd be ideal to automate data purge process as per retention requirements.

Enter the self managing circular table.

Oralce is internally using circular buffer to implement the same for ASH (active session history) and for some of the fixed tables in the dictionary.

Another way to do this is by designing the table with retention time depicting columns to use for becoming part of the primary key pair, to locate the interested data, and to merge the new data.

Let's say the requirement is to track standby lag for all the standby databases in the environment and to maintain this data for one full year. This needs to be logged in a table in the central repository database.

create table stby_lag_audit (

day_id number not null,

day_hour number not null,

stby_date date,

instance_name varchar2(30) not null,

host_name varchar2(30) not null,

stby_version varchar2(10),

stby_lag number)

create unique index stby_lag_audit_idx on stby_lag_audit (day_id, day_hour, instance_name, host_name)

alter table stby_lag_audit add primary key (day_id, day_hour, instance_name, host_name) using index

Now all we have to do is use "merge" statement if there is a driving table to insert a new record if no record exists for a given primary key or to update an existing record. Pl/sql or other procedural languages can be used to upsert the data into stby_lag_audit table where there is no driving table.

Let's say our driving table is stby_lag_info with columns (instance_name, host_name, stby_version, stby_lag).

merge into stby_lag_audit a using stby_lag_info b

on (a.day_id = to_number(to_char(sysdate, 'DDD')) and

a.day_hour = to_number(to_char(sysdate, 'HH24')) and

a.instance_name = b.instance_name and

a.host_name = b.host_name)

when matched then

update set

a.stby_date = sysdate,

a.stby_version = b.stby_version,

a.stby_lag = b.stby_lag

when not matched then

insert (a.day_id, a.day_hour, a.stby_date, a.instance_name, a.host_name, a.stby_version, a.stby_lag)

values

(to_number(to_char(sysdate, 'DDD')),

to_number(to_char(sysdate, 'HH24')),

sysdate,

b.instance_name,

b.host_name,

b.stby_version,

b.stby_lag)

With this, logging data older than one year gets updated automatically with new data and no need to create any additional jobs to purge the logging data older than retention period.

How to make sqlprompt bold and blinking

2011-04-26T11:21:00.000-07:00

In a large database environment, when DBA's use multiple windows to connect to databases, it is quite possible to type command meant for standby database on a live database window and vice versa.

If all live database sqlplus windows have bold and blinking prompt, then it may reduce the chance of making mistakes like above.

This can be achieved by using external table.

Run this one time shell command on all database boxes:

echo "`tput blink; tput smso`LIVE`tput rmso;tput sgr0`" > /oracle/custom_sqlprompt.log

Add following table to all live databases:

create table custom_sqlprompt (

custom_sqlprompt varchar2(30)

)

organization external

(type oracle_loader

default directory custom_sqlprompt_dir

access parameters

(

records delimited by newline

nobadfile

nologfile

nodiscardfile

fields terminated by '\n'

missing field values are null

(CUSTOM_SQLPROMPT)

)

location ('custom_sqlprompt.log')

)

reject limit unlimited;

create public synonym custom_sqlprompt for custom_sqlprompt;

grant select on custom_sqlprompt to public;

Add following entry in glogin.sql or login.sql:

column sql_prompt new_value sqlprompt

set termout off

select ctype sql_prompt from (select case when controlfile_type = 'STANDBY' then 'STBY'

else

(select custom_sqlprompt from customl_sqlprompt) end as ctype

from v$database);

set termout on

set sqlprompt "&sqlprompt>"

Keep in mind that when you try to open spool files using vi editor, you'll see bunch of control characters, this will not be the case if you use "cat or more" command. You can temporarily disable custom sqlprompt if you don't want to see control characters in the spool file.

What is Oracle Goldengate?

2010-09-22T11:40:00.000-07:00

After Oracle corp. acquiring Goldengate software there is a lot of buzz about Oracle Goldengate and it is one of the hot topics at Oracle open world 2010.

Oracle Goldengate can be used as a replication tool, ETL, and even as a DR solution.

Oracle Goldengate (GG) is probably the best replication software and it is very easy to configure and deploy it in large scale environment. Here are some of the things you need to be aware of:

1) All GG configuration files are ascii text based files. Very easy to make changes but it is prone to human errors in an environment having many DBA's working on it.

2) In order to use parallel apply threads, GG breaks down the database transaction into multiple transactions based on the hashing key defined for range split of the data. So, transactional consistency will not be guaranteed during real time but there won't be any data loss, but make sure that your application can tolerate this.

3) If there is no primary key or unique index exists on any table, GG will use all the columns as supplemental logging key pair for both extracts and replicats. But if you define key columns in the GG extract parameter file and if you don't have the supplemental logging enabled on that key columns combination, then GG will assume missing key columns record data as "NULL", which is a huge deal, and this will introduce logical data corruption on the target.

4) GG started supporting bulk data loads with their 11.1 release but any NOLOGGING data changes will be silently ignored without any warning.

5) GG doesn't support compression on the source database.

6) GG does support DDL replication but it is not easy to do selective DDL replication, it replicates every DDL that happens on the source database which is not desirable for some customers.

7) Tables being replicated to on the target can also be written to by any other application or DBA's.

8) GG supports ignoring data conflicts for updates after the first instantiation of the target database until it catches up. But it is very easy to forget turning off that parameter and any updates being lost will not be alerted by GG.

9) GG still works by reverse engineering the Oracle redolog. This may not be totally true with GG 11, but I expect GG to interpret Oracle redo more directly in later versions of 11 or 12.

10) GG dynamically decides to change the key columns that form the supplemental logging based on the state of primary key (i.e. in VALIDATED or NONVALIDATED state), which can introduce data corruptions on the target databases as the expected key columns data is missing in the trail files and they will be set to NULL. They now have the patch available for this, you can set "_USEALLKEYCOLUMNS and ALLOWNONVALIDATEDKEYS" parameters in GLOBALS file to get around this problem.

Use cases:

I think Oracle is not promoting logical standby as much as they should have. Oracle logical standby guarantees data consistency, data integrity, maintains order of transactions, and doesn't let target database tables to be modified by others which by itself offer great confidence in data quality.

Oracle Xstreams offers greatest flexibility and superior performance in extracting data from the source database and applying the same to the target database.

For Oracle database upgrades or having a logical DR standby it is better to use Oracle logical standby, use Xstreams if you want more flexibility and high performance in moving data across databases, and use GG for keeping the downstream database up to date for reporting, ETL purposes, or to move data across hybrid databases.

Undocumented 11g new features of standby database

2010-09-06T15:19:00.000-07:00

Apart from the documented new features of being able to use BCT (block change tracking) on the physical standby, ability to use rman duplicate command over the sqlnet to build physical standby from the primary database, here are some of the undocumented features (or change in behavior) introduced as of 11.2.0.1:

1) Ability to defer log boundary checkpoints ("_defer_log_boundary_ckpt")

2) Ability to define number of logs checkpoint can get behind ("_defer_log_count")

3) Media recovery process now reads maximum of 1024 pending asynch block reads

4) DBWR only do the writes for incremental checkpoint unless free buffer is requested

5) v$recovery_progress view is updated even when only few redo blocks applied by the MRP

6) Removed v$standby_apply_snapshot view.

7) More smoothened incremental recovery checkpoint writes

The best feature among the above is the ability to defer checkpoints happen at the log boundary.

Parameters "_defer_log_boundary_ckpt" set to "TRUE" by default and "_defer_log_count" set to 2 by default will automatically get in to play only when the physical standby is actively applying the logs instead of waiting for redo or logs to arrive in managed recovery mode.

During my tests, changing "_defer_log_count" to different values didn't work, Oracle seems to use value 2 only (more testing is needed).

If standby ever falls behind or catching up, it doesn't have to wait for the mandatory checkpoint at the log boundary to finish before applying the next log, which is a big deal if the underlying storage system can only support limited iops.

Media recovery process on the standby will read the data blocks in parallel, Oracle used "db file parallel read" wait event for this until 9i, starting 10g they changed it to "recovery read" wait event.

In 10g, MRP process can keep issuing asynch data block read requests in batches until the overall outstanding block reads for a given MRP process reach 4096. This has caused a havoc in our 10g standbys, as this huge burst of I/O caused underlying SAN to suffer even worse (finally causing corruptions). They have done it to improve the performance of redo apply.

Oracle silently changed it to read 1024 outstanding asynch block reads in 11g.

In order to even out the write activity, dbwr seems to be doing incremental recovery checkpoint writes only, unless dbwr is asked to free up buffers, (you can verify it by setting "_disable_incremental_recovery_ckpt" to true ). By default dbwr is writing once the checkpoint batch size reach 500 as defined by the parameter "_incremental_recovery_ckpt_min_batch".

These are great improvements to the performance of physical standby apply process, but it'd be even better if we can control how many logs checkpoint can defer by so that we can do one big mandatory checkpoint for every few logs and have a big enough sga to control the physical reads, this will improve the performance of standby apply process and reduce the overall I/O.

Only downside to this is, if standby crashes, it'd have to apply all the logs since the last checkpoint. But physical standby for RAC databases will greatly benefit from it especially when you have many nodes.

I've opened enhancement request 9943911 for the above request.

Overall, these are great improvements Oracle has introduced in 11g.

Beware of data loss in BCT based RMAN backups

2010-09-04T22:57:00.000-07:00

Block change tracking feature introduced in Oracle 10g is intended for RMAN incremental backups to directly go after the changed data blocks. It can even be used on active data guard standby in 11g.

Alex Gorbachev has written excellent white paper about BCT, it is at http://www.pythian.com/documents/Pythian-oracle-block-change.pdf

By default BCT maintains 8 bitmap versions for a given datafile in the change tracking file, background process ctwr process is responsible for maintaining BCT bitmap file.

BCT background process ctwr is hooked up with the redo apply mechanism, as user process executes any transaction on the primary database (internally Oracle will apply the generated redo to the data blocks for making changes to the data blocks) and as media recovery process applies the redo on the standby database.

One of the most popular database backup method is to run rman incremental level 0 image copy of all datafiles, followed by periodic incremental level 1 backups which can be merged with the previous level 0 backup for rolling full backup of the database. BCT is very useful for this purpose.

So BCT is a good feature and it has very light overhead on primary or the standby database and it has been out there for few years now with many customers using it; are there any issues with using this feature?

There is a possibility of data loss in rman incremantal backups based on BCT.

Data loss scenario 1:

BCT works on the physical standby only when managed recovery is in use and of course active data guard license is needed.

Enable BCT on the standby.

Create a test table on the primary and insert one record.

Identify which datafile the test table belongs to.

Use standby managed recovery to bring it current with the primary.

Stop the managed recovery on the standby.

Take rman incremental backup (backup incremental level 1 for recover of copy with tag 'bct1' tablespace test_tbs)

Insert second row in test table on the primary and switch the logfile.

Apply new logs on the standby using traditional recovery (i.e. recover standby database)

Run the rman incremental backup again with the same command as above

Merge the rman incremental backup with the first image copy (i.e. recover copy of datafile 5 with tab 'bct1')

Offline drop the datafile having the test table on the standby and rename it to the rman backup copy of that datafile.

Now open the standby in read only mode and select from the test table. Second row will be missing.

Bug# 10094823 was opened for this. It is now fixed and the patch is available for 11.2.0.1

Data loss scenario 2:

If you ever have to refresh a datafile on the standby with later checkpoint time (SCN) and you run the rman incremental backup right after that before bringing rest of the datafiles on the standby to the consistent checkpoint time with the recently refreshed datafile, then there will be a data loss in the just refreshed datafile if the same backup is restored (and rename the just refresh datafile to the rman backup copy) before running the next rman incremental.

This problem can get automatically corrected in the next rman incremental but only if you use the latest backup.

Data loss scenario 3:

Offline drop a datafile on the standby

Create a test table on production. Make sure that the table extents are in the above datafile.

Copy the datafile from production with later checkpoint time and leave the datafile in offline status.

Apply few logs on the standby using managed recovery mode.

Online the datafile.

Apply logs on the standby using managed recovery until standby is caught up with live.

Take rman incremental backup of that datafile

Update incremental backup copy with "recover copy of datafile with tag" command

Rename datafile to the rman backup copy

Apply few more logs and open the standby in read only mode

You will now see the test table data missing in that datafile

Reliability of BCT:

On 11.2.0.1 standby, I've seen managed standby recovery failing to start until BCT is reset at least while running the above tests. I'm working with Oracle support to get all these issues fixed.

As of 11.2.0.1, make sure to get the above mentioned issues addressed before using BCT on the standby.

Update (09/23/2010):

I've opened an enhancement request for Oracle support to implement the following features in BCT:

1) Ability to enable/disable BCT at datafile level.

2) Make rman to check checkpoint_time seen from BCT for a given datafile with the actual checkpoint_time from the datafile header and use BCT only if both the checkpoint_time's match.

Internals of online index build

2010-08-09T16:59:00.000-07:00

Internals of online index build:
Basic premise for online index build, which was introduced in Oracle 8i, was to let DML's to continue while online index build is in progress. It greatly improved database availability for most of the applications. But it suffered from complete hang of DML's during initial and end phases of online index build, which is resolved in 11g.

The way online index build (OIB) works is by creating an IOT journal table to keep track of changes while the OIB is in progress and merge all the changes from journal table to complete index build operation.

If I have a table with the structure of (A NUMBER, B NUMBER, C CHAR(100), D NUMBER) and to create index on (A, B, C) column, Oracle would create journal table like:

create table "ORACLE"."SYS_JOURNAL_18155" (C0 NUMBER, C1 NUMBER, C2 VARCHAR2(100), opcode char(1), partno number, rid rowid, primary key( C0, C1, C2 , rid )) organization index;

Before 11g, OIB will get in the DML queue to lock the table exclusively while preventing the new DML's to go through, once all the active transactions (ones which were initiated before the OIB) are completed, OIB will create the journal IOT table and release the exclusive table lock (it'll still keep the share lock on the table to prevent any other DDL's) for DML's to continue.

As part of journal table creation, Oracle would create an internal trigger on the primary table to record all the changes to the journal table. Along with using all the index columns, journal table will add "ROWID" to that list to make it as primary key.

"OPCODE" column represents the type of operation like "I" for Insert and "D" for Delete.
"PARTNO" column represents partition number of the underlying table.

Among all the changes to a given row for any of the index key columns, only the most recent change for that record is visible in the journal table. Any update operation of index key columns would be converted to "DELETE" and "INSERT" in the journal table.

While rest of the user sessions populate journal table with the ongoing changes for index key columns, OIB session would be reading the table in consistent mode (as of the time journal table is created) to create the index followed by the final merge operation.

During the merge process, Oracle will read the journal table (IOT) leaf blocks from left to right to merge those changes with the index being built. As the journal table leaf block changes are applied, once a leaf block is fully consumed, it's reference will be deleted from the branch block.

This process will continue until all leaf blocks are consumed and when it comes to the last leaf block, Oracle would stop all the DML's again to do the final merge and drop the journal table before releasing the mode 6 exclsuive table lock.

As each leaf block is consumed, Oracle would mark each entry as deleted. If more DML's happen while Oracle is doing the merge, it'll do one more pass of walking through the leaf blocks, this process continues until the merge process is all done.

If there is any long running DML before issuing the OIB, Oracle would wait on the long running transaction and it will prevent upcoming DML's also. This is one of the major problems with OIB. Same thing can happen when it is doing the final merge.

With 11g, significant changes were introduced to address all these problems.

Oracle will still wait for long running transactions to complete before starting the OIB process, but it will not block the upcoming DML's. Oracle will not use exclusive table lock (mode 6) for OIB, which will eliminate any DML hang situations.

As soon as OIB process is initiated, Oracle will create IOT journal table and use internal trigger on the underlying table to keep track of the changes.

Once the journal table is created and the long running transactions are completed, Oracle will create the index by reading the table blocks in "CURRENT" mode, which means any committed changes in a block happened before the current SCN are read instead of the old way of accessing the blocks as of journal table creation time. This will virtually eliminate chance of running into rollback segment too small errors.

Oracle will recreate the internal trigger to directly modify the target index structure once the index build is completed (but the merge job is still pending). This means all user sessions will now directly update the target index instead of updating the journal table. Any change coming through would first be checked in the journal table before consolidating the change for the target index (this is best explanied with the example below).

Record "A" was inserted with rowid "RID" before the merge phase and hence it is tracked in the journal table.
Record "A" was deleted during the merge phase. Now the user session will read the journal table by record "A" with rowid "RID" and apply that change to the target index before deleteing the same record. It will also mark that record as consumed (i.e. deleted) in the IOT journal table leaf block.

While the user sessions continue to update the target index with the ongoing changes, OIB session will do the merge of journal table changes into the final index, as there will not be any ongoing DML changes being tracked in the journal table, this process will complete faster than before (i.e. pre 11g).

If you are creating unique index online, that uniqueness will not be enforced in the IOT journal table, but rather in the target index from the initiation of build/merge phase.

If you are creating either a reverse key index or global hash partitioned index to resolve any index contention (because you have sequentially increasing leading key column), then the OIB process will suffer from the contention because the underlying IOT journal table is created as normal B-tree index (i.e. no reverse key or hash partitioning).

I've created enhancement request 9912950 for this issue with Oracle support. Hopefully they resolve it in 12g.

Full table scan behavior in 11g

2010-06-15T11:19:00.000-07:00

Before 11gr2, full table scan access path read all the blocks of a table (or index fast full scan) under high water mark into the buffer cache unless either "_serial_direct_read" hidden parameter is set to true or the table/index have default parallelism set.

In 11gR2, Oracle will automatically decide whether to use direct path reads bypassing buffer cache for serial full table scans.

For using direct path reads, Oracle first has to write any dirty blocks of the table to disk by issuing object level checkpoint and then read all the blocks from disk into the PGA of server process. If it has to undo any uncommitted transactions to provide read consistency or to do delayed block clean out, Oracle will use server process PGA to construct read consistent block buffers.

If block clean out has to be performed by the server process using direct path reads for full table scans, it won't write those modified blocks back to disk, so every time you perform full table scan using direct path reads it'd have to keep doing the same work of cleaning out the blocks for every execution. For this reason, it is recommended to manually clean out those blocks by performing full table scan without using direct path reads.

Following behavior was observed with my testing on 11.2.0.1:

Hidden parameter "_small_table_threshold" defines the number of blocks to consider a table as small. Any table having more blocks (about 5 times the value of "_small_table_threshold" if you leave it at default value) will automatically use direct path reads for serial full table scans (FTS).

Hidden parameter "_very_large_object_threshold" defines the upper limit of object size in MB for using direct path reads. There is no effect on FTS behavior just by setting this parameter alone.

Event 10949 set to any level will disable direct path reads for serial scans only if the size of an object is less then 5 times the size of buffer cache.

Combination of event 10949 and "_very_large_object_threshold" parameter will disable direct path reads for serial scans if the size of an object is less than 5 times the size of buffer cache or the value of "_very_large_object_threshold" is less than about 0.8 times the size of an object.

So, if you want to disable direct path reads for serial scans for any object, then set event 10949 at any level and set "_very_large_object_threshold" to greater than the size of largest object in MB.

Hidden parameter "_serial_direct_read" (or event 10355 set at any level) set to TRUE will enable direct path reads for all serial scans, unless the table is considered as small table and it's caching attribute is set (by issuing alter table xxxx cache). Remember that any sql statement already parsed and not using direct path reads will continue to do so unless hard parse is forced after setting these parameters. For this reason, it is better not to set these parameters.

It is not recommended to set any of the above mentioned hidden parameters if you want direct path reads to be used for serial scans, let Oracle decide dynamically based on the size of an object.

Read only tables in 11g is not fully read only.

2010-05-29T01:05:00.000-07:00

When any large transaction is committed, it is quite possible that not all blocks it modified are cleaned out and these blocks have to be cleaned out before any data in them can be retrieved. But what happens if you open the database in read only mode, or make table/tablespace read only.

All the blocks that require a clean out will be done in shadow process PGA memory when parallel query option or direct path reads are used.

Blocks will be clean out but not written to disk when the database is opened in read only mode or tablespace in read only mode.

But after the table is put in read only mode, any further select queries against this table that require block cleanout (dirty blocks) will be written to the disk. In this sense, it is a surprise that read only table can have dirty buffers which will be written to disk. It is definitely the right thing to do.

Oracle can safely ignore any block clean out operations for read only tables and tablespaces as it is given that there can't be any other open DML transactions against them. I'll try to open an enhancement request for this.

Until this issue is fixed, make sure to manually clean out the blocks (by running full scans of the table) before you put any tablespace in read only mode to avoid repeated clean out of the same blocks later on which would incur more performance overhead.

Also, be aware that in 11g by default Oracle will use direct path reads for full table scans if the table size is large enough as dictated by _small_table_threshold/_very_large_object_threshold, you can use event 10949 at level 1 to disable direct path reads for serial full table scans (parallel query option will still use direct path reads).

All about ora_rowscn

2010-05-28T22:47:00.000-07:00

SCN information has always been maintained at the block level as long as it have some data in it. Ora_rowscn basically piggyback on that fact to expose SCN (or timestamp) conservatively when a block has been updated.

When a transaction is committed Oracle will revisit either all or few blocks, it has modified as part of the transaction, to mark end of transaction as long as those block buffers are available in the buffer cache. Rest of the blocks will be cleaned out during subsequent access of those blocks as part of any future transaction. During block clean out operation, if the transaction information is not available in the undo segments, Oracle will mark transaction in that block with the SCN of first available transaction in the undo segments. It is this SCN that will be reported for ora_rowscn.

Some weirdness of selecting ora_rowscn from dual:

13:58:23 SQL> show rel
release 1102000100
13:58:28 SQL> select ora_rowscn from dual;

ORA_ROWSCN
----------

13:58:32 SQL> select ora_rowscn, sysdate from dual;

ORA_ROWSCN SYSDATE
---------- ---------------
27-MAY-10

13:58:42 SQL> select ora_rowscn, dummy from dual;

ORA_ROWSCN D
---------- -
1329 X

13:58:48 SQL> select * from dual where ora_rowscn > 1;

D
-
X

13:58:58 SQL> select 1 from dual where ora_rowscn > 1;
select 1 from dual where ora_rowscn > 1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qkeIsExprReferenced1], [], [], [], [], [], [], [], [], [], [], []

Opened bug# 9761357 for the above issue.

Problems with ora_rowscn:

1) bug# 9761357 for the above issue
2) bug# 5270479 for not doing ora_rowscn predicate checking in the current version of the block
3) returning null ora_rowscn when rowdependencies is enabled and querying data from the modified data blocks in an open transaction.
4) Different ora_rowscn values can be reported over time when the underlying block has not been modified.
5) Reporting same ora_rowscn for all the records in a given block when not all those records were changed as part of that commit SCN. This is only when rowdependencies is not used.

Problem #2:
This will basically prevent us from using ora_rowscn for optimistic locking unless following work around is used.

Either use one of the columns being modified in the predicate along with using ora_rowscn predicate or use something like (ora_rowscn+length(column_being_modified) = ora_rowscn_value+length(column_being_modified). But this will not work when other columns are updated by a competing transaction which blocked our transaction, in this case we will update the same record again but not the same columns. If this is not desirable, then don't use ora_rowscn for implementing optimistic locking until the current bug is fixed.

problem #3:
Oracle records SCN for each row in the block when ROWDEPENDENCIES is enabled. Since current SCN can only be recorded for each row when the transaction is not committed (lets call this DSCN), ITL transaction entries in the block is updated with the commit SCN later when the transaction is committed (read above for further explanation of block clean out). DSCN recorded for each row in the block will not be cleaned out as part of normal block clean out process.

Commit SCN (or the upper bound SCN) reported in the block ITL enrties is used for reporting ora_rowscn of all the rows corresponding to an ITL entry modified in the past. But if a given ITL entry needs to be reused for other transactions later on, commit SCN of ITL entry will be used to update DSCN of all the rows it modified in the past before that ITL entry can be reused.

Oracle can report current SCN recorded with each row, instead of returning null Ora_rowscn, when the data from the modified blocks is queried as part of an open transaction. I don't see any open bug created for this one yet.

Problem #4:
Different ora_rowscn values can be reported over time even though the underlying block contents have not been modified. There are 2 reasons for this:

1) If that block was modified with direct path operations, like CTAS and insert with append, and the transaction commit SCN information is not available in the undo segments. Direct path loads write above HMW (and hence very little undo unless indexes exist) and commit SCN is only updated in few blocks when the transaction is completed. When the remaining blocks are accessed again and the corresponding direct path transaction information is not available in any UNDO segments, Oracle will report conservative SCN (least SCN available from the undo transaction table) but it will not clean out the blocks (i.e. all these blocks loaded using direct path operations will remain clean with no commit (or upper bound) SCN available in them).

2) Using direct path operations (or parallel query option) to access the data blocks which needs to be cleaned out. This is very evident in 11g since direct path reads are used for serial full table scans when the table size is big enough. You can change this behavior by setting "alter system set events '10949 trace name context forever, level 1'". It will still do the block clean out but it does this in PGA memory of user process. Over time, as the transaction state information is aged out from the undo segments, different ora_rowscn's will be reported.

Problem #5:
It is not a problem per say, but rather the way it is implemented. Use ROWDEPENDENCIES if you want SCN information at row level more accurately.

Use cases:

You can use ora_rowscn for:
1) To implement optimistic locking
2) To report active time window of table data (i.e. how far back in time data in the table is modified). This information can be used for any archival or data purge analysis.
3) To figure out the last time table data is changed.

Oracle new feature enhancement requests for 11gr2 and above

2009-06-19T00:43:00.000-07:00

Here is my wish list:

1) Ability to create composite partitioned local indexes when the table is not composite partitioned and first level partitioning for index and table is same.

If we have a table range partitioned by TIME_CREATED column and column like "ID" is generated off of a sequence then the local index on "ID" column is prone to index contention as DML concurrency goes up.

Creating reverse key indexes would resolve the contention but would limit our ability to range scans and it also introduces more physical I/O for DML's and select queries.

If we can partition index on "ID" column by TIME_CREATED and do subpartition by hash on "ID", it will not only resolve index contention and would also give us the ability to do the range scans along with less physical I/O for DML's and select queries.

2) Ability to use In memory undo and private redo for short transactions when supplemental logging is enabled.

Right now, when supplemental logging is enabled, in memory undo is siltenly turned off automatically. This provides more scalability to OLTP type of database applications.

3) Adding more intelligence in determining amount of space reserved for ITL entries during index leaf block splits.

Right now, if you have an index on a sequence based column and when sudden surge in DML's occurs, Oracle would reserve as much space as possible for all the concurrent transactions in the form of ITL entries and the same amount of space reserved for ITL slots in future leaf block splits even though the spike in DML's may be one time activity.

It should basically add a timestamp to when the maximum number of ITL slots (and also maintain average number of ITL's used) were used and if it was done more than an hour ago (or a day ago) then reserve
only the space required for average number of ITL's used for future leaf block splits.

This would avoid wasting lot of space when the transaction concurrency spikes are very rare (we should be altering index with more initrans in this case anyways).

4) Often times, DBA's create function based mod index to resolve right most leaf block index contention by dividing index column (say "ID") into few buckets (i.e. create index on (mod(ID,10), ID)). This requires change in application for all the sql queries belong to this table and hence it may force DBA's to create reverse key indexes, which is not the ideal solution for big tables.

Just like the way Oracle is recognizing upper or lower functions, when used on an indexed column, to consider index access path, it'd be better if Oracle can recognize mod function also (i.e. convert "WHERE ID=10" to "WHERE ID=10 and mod(id,10)=0" automatically depending on the way mod function index was used.

5) Ability to define index leaf block split records ratio, right now, Oracle put's last record in the new leaf block and the rest will be left in the current leaf block when the right most leaf block is split due to insertion of maximum key value, and use 50-50 ratio for all other leaf block split operations.

When application is designed to address index contention of sequence based column's by dividing them into several buckets (like mod index explained above) and grow them sequentially in each bucket, then Oracle would be wasting lot of space when 50-50 leaf block splits happen.

By having ability to define leaf block split ratio (not for right most leaf block split) then indexes will be more skinnier (for this used case) and hence improved performance due to the possibility of caching more index blocks.

6) Ability to define index affinity. If we have a table with (account#, phone_number, pin_number, etc) and have indexes on (account#, phone_number) and (account#, pin_number), then if most executed queries use the first index and if we have a query with "where account# = ?" then oracle can use first or second index based on the optimizer statistics.

In this case, even if statistics indicate that the second index is better, just from caching perspective first index is ideal. We need to have ability to alter an particular index to more affinity either by having alter index command (which will be enforced when leading columns of the indexes were used as predicates and more than one index has this.

7) Ability to enforce delayed (or on demand) global index maintenance. With one process trying to sort all the indexed columns from the table partition being altered and deleting them from the index requires significant amount of time and if we have multiple partition level changes to be done in a given partition maintenance window, it can run into peak business time or it can pose other operational issues like what happens if instance is crashed, etc.

Since object_id is part of rowid to be stored in global indexes, Oracle can simply mark an object_id as "INVALID" and when user tries to query the data belong to that object, then either perform the cleanup in those leaf blocks at that time or ignore the entries belong to that object. This way, global index maintenance will be like local indexes, with the only difference of on demand cleanup of global index leaf block entries (and we need to have ability to explicitly perform the cleanup of leaf blocks entries).

8) Include access and filter predicates of v$sql_plan in DBA_HIST_SQL_PLAN; right now Oracle doesn't record those column values in any awr base tables.

9) Record the first snap_id of when a sql statement first executed on the database in awr base table (DBA_HIST_SQLTEXT). This information is recorded in statspack base table STATS$SQLTEXT.LAST_SNAP_ID, but not in any awr tables. This is useful in figuring out what changed when a new application rolls out.

10) Record out of normal entries from ash buffer into awr table (DBA_HIST_ACTIVE_SESS_HISTORY) instead of just picking one sample id on 10 second interval. DBA can define threshold for out of normal activity in terms of number of sessions waiting on any given waitevent, etc. This will be very helpful in doing root cause analysis and for any scalability analysis.

11) Honour "_db_block_max_cr_dba" parameter which has a default value of 6, limiting how many clone copies of one block can exist in buffer cache. I've see it going over 10 in many situation. Cloning a buffer is needed for read consistency purposes, but having more copies of them than 6 is hardly ever a requirement and Oracle should reuse an existing clone
if no other process is pinned it (this by itself resolves the problem). Not doing so can increase cache buffer chains latch contention since the latch is needed to be held longer to figure out the right clone buffer image to use.

12) Record redo usage per sql statement in v$sql. This will be very useful for troubleshooting and root cause analysis.

13) Use direct path reads when parallel query option is used with rowid range scans, i.e. select /*+ rowid(w) parallel(w, 2) */ ... where rowid between :1 and :2". Right now, Oracle used scattered reads to read the blocks into buffer cache, but using direct path reads bypassing buffer cache has it's used cases.

Since Oracle is doing direct path reads for each extent of the segment, it can easily get the extent boundaries from the rowid range and hence should be able to direct path reads.

Cheap and light way to get record count frequently in a big table

2009-04-29T23:33:00.000-07:00

How to get count(*) without really scanning all the records in a huge table:

If your requirement is to frequently get count of records in a huge table and to get it quickly, then you may use the following method to achieve it. One real life requirement example is to find the count of records in an exception queue table to determine how many application threads need to be started to process the exception queue for meeting SLA's.

Basically, we would create a new tracking table to be populated though trigger on the main table to maintain the counters for any inserts or deletes on the main table. We also need an Oracle sequence to distribute the counter trackers for better concurrency and locking mechanism. In essence, counter will be incremented by 1 for an insert and decremented by 1 for a delete statement.

Oracle Materialized view does similar thing but it is not scalable for a busy table (you will see enqueue contention).

create table test_tmp(id1 varchar2(30), id2 number, id3 number);
Table created.

create table test_cnt (id1 varchar2(30), id2 number, c1 char(25),
c2 number, c3 number) initrans 50; #This is the tracking table
Table created.

create unique index test_cnt_idx on test_cnt(id1, id2, c2);
Index created.

create sequence test_cnt_seq start with 1 increment by 1 maxvalue 100 cycle cache 20;
Sequence created.

create or replace trigger test_tr before insert or delete on test_tmp
for each row
declare
v1 number;
begin
select test_cnt_seq.nextval into v1 from dual;
if inserting then
update test_cnt set c3 = c3+1 where id1=:new.id1 and id2 = :new.id2 and c2=v1;
if sql%notfound then
insert into test_cnt values (:new.id1, :new.id2, 'X', v1, 1);
end if;
end if;
if deleting then
update test_cnt set c3 = c3-1 where id1=:old.id1 and id2 = :old.id2 and c2=v1;
if sql%notfound then
insert into test_cnt values (:old.id1, :new.id2, 'X', v1, -1);
end if;
end if;
end;
/

Trigger created.

insert into test_tmp values ('A', 1, 1);
1 row created.

insert into test_tmp values ('B', 2, 2);
1 row created.

insert into test_tmp values ('C', 3, 3);
1 row created.

insert into test_tmp values ('D', 4, 4);
1 row created.

insert into test_tmp values ('E', 5, 5);
1 row created.

commit;
Commit complete.

select id1, sum(c3) from test_cnt group by id1 having sum(c3) > 0;

id1 SUM(C3)
------------------------------ ----------
D 1
A 1
B 1
C 1
E 1

delete from test_tmp where id3=5;
1 row deleted.

commit;
Commit complete.

select id1, sum(c3) from test_cnt group by id1 having sum(c3) > 0;

id1 SUM(C3)
------------------------------ ----------
D 1
A 1
B 1
C 1

Triggers have it's downside and they can fire multiple times for the same record in a single sql statement execution as per http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:11504247549852

Beware of index contention after mass delete and insert

2009-04-28T00:36:00.000-07:00

One insert can cause an outage on your system:

If you delete large number of records from a table having an index and insert the same records as part of another transaction, then one of the subsequent inserts would have to perform tremendous amount of work while splitting an index block as it has to find a free block from the free list but the blocks reused from delete-insert operation were still there in free list and hence this operation would have to unlink them one by one until it finds an all empty block.

This can be demonstrated easily with the following test case (on 10.2.0.3 with 8k block size and no assm tablespace):
create tablespace idx1 datafile '?/dbs/idx1.dbf' size 500M
segment space management manual
extent management local uniform size 10M;

create table idx1(a number) tablespace idx1;

create index idx1_idx on idx1 (a) tablespace idx1 pctfree 0;

insert into idx1 select rownum from all_objects, all_objects where rownum <= 250000;

commit;

create table idx2 tablespace idx1 as select * from idx1 where 1=2;

insert into idx2
select * from idx1 where rowid in
(select rid from
(select rid, rownum rn from
(select rowid rid from idx1 where a between 10127 and 243625 order by a)
)
where mod(rn, 250) = 0
)
/

commit;

delete from idx1 where a between 10127 and 243625;

commit;

insert into idx1 select * from idx2;

commit;

insert into idx1 select 250000+rownum from all_objects where rownum <= 126;

commit;

select sql_id, executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql
where sql_text like '%insert%idx1%' and sql_text not like '%v$sql%';

insert into idx1 values (251000);

commit;

select sql_id, executions, buffer_gets, disk_reads, cpu_time, elapsed_time, rows_processed, sql_text from v$sql
where sql_text like '%insert%idx1%' and sql_text not like '%v$sql%';

If you run the above statements, you will see that one insert statement consuming over 900 buffer gets. While it is in the middle of getting 900 blocks (some of them may be on the disk), all other inserts would have to wait for it to finish.

If you either coalesce or shrink the index with an alter statement, then this problem will go away (alternatively you can also do online index rebuild), but the best way to deal with it is by doing the delete and insert in one transaction.

I've opened bug# 8446989 with Oracle support on this one, hopefully it will be fixed soon.

Thanks,
Sai.

How to bypass buffer cache for full table scans.

2007-12-08T00:15:00.000-08:00

How to reduce impact of full table scans:

Full table scans (FTS) have their place, even on OLTP databases, and they are not evil when used properly. FTS are unavoidable on most DW databases. So apart from popular belief of possible increased response time and increased I/O, is there any impact on the database?

FTS on a large table can effectively pollute the buffer cache by aging out blocks in the buffer cache otherwise needed by other sessions, even though the FTS blocks are placed at LRU end. It will also have increased latch activity (cache buffer chains, cache buffer lru, etc.). It can also create many CR cloned buffers, if there are any blocks for that table already in the cache.

All these problems can be solved by setting one hidden parameter "_serial_direct_read = true" for that session or at the system level. The beauty of this parameter is that once execution plan is generated for any sql with this setting on, same behavior will be exhibited even when it is not set as long as the cursor is not re-parsed.

"_serial_direct_read = true" basically use "direct path reads" for single threaded multi block read operations like FTS. Oracle will first issue fast checkpoint at object level to write all dirty buffers of that object to the disk and perform direct path reads in to the PGA of shadow process.
This will be especially useful for batch jobs.

Oracle introduced event 10379 to do the same for rowid range scans but never really implemented it fully. Of course you can always use parallel query option to achieve the same thing with multiple threads, but you will be consuming additional resources on the system.

00:50:19 SQL> alter session set "_serial_direct_read" = true;
Session altered.

00:50:19 SQL> select avg(id) from test;

00:50:32 SQL> select event, total_waits from v$session_event
00:50:40 2 where sid = (select sid from v$mystat where rownum = 1)
00:50:40 3 and event like '%read%';

EVENT TOTAL_WAITS
------------------------------ -----------
direct path read 124

Librarycache changes in 10g

2007-05-23T22:58:00.000-07:00

Shared pool improvements:

Oracle introduced multiple shared pool latches (up to 7) in Oracle 9i, this is to reduce shared pool latch contention and the impact of shared pool fragmentation is localized to that latched pool only.

With Oracle 10g R2, some types of chunk (like sqlarea) allocation sizes are standardized to reduce shared pool fragmentation. Parent and child cursor heaps are allocated explicitly.

Since all the linked list structures (for buffer headers, etc) are mapped within the defined shared pool size, be sure to allocate more memory (may be 20%) to the shared pool.

Librarycache improvements:

Oracle implemented mutexes for some of the library cache latching operations, this will reduce the library cache latch gets tremendously if you are using bind variables in sql statements.

Implications of this change is greater scalability & availability, and increased shadow server process memory usage.

Finding table extents after losing a datafile in LMT

2006-12-06T00:06:00.000-08:00

What it means of losing a datafile in locally managed tablespace:

Finding the list of tables and the number of extents stored in a lost datafile could prove very important under some situations; you could either resort to old copies of those tables or make better decision about the impact of lost datafile.

This is not a problem in dictionary managed tablespace as all the extent related information is stored in dictionary objects, i.e., DBA_EXTENTS and DBA_SEGMENTS.

As all the extents map related information is stored in the actual datafiles, losing a datafile could very well mean losing extents information partially or fully.

If the datafile lost don't have any segment headers, then you can find all the segments and it's extent information from the following query:

select count(*), sum(blocks), sum(bytes) from dba_extents where file_id = &file_id and
segment_name in (select segment_name from dba_segments where extents is not null and header_file = &&file_id);

If the datafile lost have some segment headers stored in it, you can find the list of those segments by:

select segment_name, segment_type, owner from dba_segments where extents is null and header_file = &file_id);

And hence, it is highly advisable to backup dba_extents periodically.

How to update joining column using MERGE.

2006-06-15T23:40:00.000-07:00

Updating joining column in MERGE command:

Let's say you want to merge "SOURCE" table into "TARGET" table with "ID" column as joining condition and you want to update "ID" column too.

merge into TARGET using
(select target.rowid rid, source.* from SOURCE, TARGET
where source.id = target.id(+)
) SOURCE
on (TARGET.rowid = SOURCE.rid)
when matched then
update set target.id = source.id, target.column2 = source.column2, ....
when not matched then
insert values (source.id, source.column2, ....)
/

In fact this is the only to come up with a plan using INDEX_FFS.

Say, you have about 90% updates and 10% inserts to go into TARGET table from SOURCE table, and you do have an index on "TARGET.ID" column, but you are not intending to update "ID" column.

If doing full table scan of "TARGET" table is more expensive than doing index fast full scan on "TARGET.ID" with table lookups by rowid, then above mentioned merge command would give you the best plan that Oracle is unable to come up with if you dont use inline view subquery in place of "SOURCE" table.

How much shared_pool_reserved_size?

2006-04-03T07:45:00.000-07:00

What is shared_pool_reserved_size:

Shared pool memory is allocated in chunks and as shared pool fragments with time you will see more smaller chunks being allocated for all sql statements requiring hard parse.

So, to reduce shared pool fragmentation due to large memory allocation requirements, Oracle reserves a minimum of 5% shared pool size for such allocations.

Any chunk allocation with more than "_shared_pool_reserved_min_alloc = 4400" bytes, would go to reserved shared pool unless such memory allocation can be satisfied from the shared pool memory without aging out any other chunks.

Interestingly all sessions connected to the database require 27224 bytes (in Oracle 9i) each from shared pool, which is called "session param v" chunk, and it has to come out from one chunk.

If you have an application which keeps closing and opening new connections, you would need to set minimum shared_pool_reserved_size as follows:

Maximum of (shared_pool_size*(5/100), "sessions" * 27224)

This is one more reason why it is a good idea to use connection pooling and don't bother much about above equation.

You can check the utilization of shared pool reserved size by querying v$shared_pool_reserved view.

Difference between SCN and checkpoint.

2006-03-30T20:27:00.000-08:00

SCN and checkpoint:

System change number(SCN) is represented with SCN_WRAP and SCN_BASE. Whenever SCN_BASE reaches 4294967290 (2 power 32), SCN_WRAP goes up by one and SCN_BASE will be reset to 0. This way you can have a maximum SCN at 1.8E+19.

SCN = (SCN_WRAP * 4294967290) + SCN_BASE

Checkpoint number is the SCN number at which all the dirty buffers are written to the disk, there can be a checkpoint at object/tablespace/datafile/database level.

Checkpoint number is never updated for the datafiles of readonly tablespaces.

Oracle doesn't use any sequence to generate SCN_BASE numbers, it make calls to "kcmgas" function (per Steve Adams), which is a permanent memory structure for the instance. It is reported in v$sysstat under "calls to kcmgas" name.

Does Oracle do either crash recovery or transaction recovery after shutdown abort if the checkpoint was taken right before the instance crash?

Yes, Oracle perform rollforward first if there are any changes beyond last checkpoint and rollback any uncommitted transactions.

SCN numbers are being reported at frequent intervals by SMON in "SMON_SCN_TIME" table.

You can also query v$transaction to arrive at the SCN for that transaction.

Controlfile records information about last checkpoint and archived sequence along with other information.

What is "index scans kdiixs1 " statistic?

2006-03-14T03:17:00.000-08:00

Statistic "index scans kdiixs1"

On Oracle 9.2.0.5:

stat# 206 -> Index fetch by key
stat# 207 -> index scans kdiixs1

Index fetch by key:
This statistic will be incremented for each "INDEX (UNIQUE SCAN)". This also applies to all DML statements which has "INDEX(UNIQUE SCAN)" in the execution plan.

Index scans kdiixs1:
This statistic is incremented for each index range scan operation, except for index fat full scans, index full scan, and index unique scan.

Both of the above statistics will be incremented for index lookups against recursive statements also.

create table t(a number not null, b number);
create index t_idx1 on t(a):
create unique index t_idx2 on t(b);
analyze table t compute statistics;
insert into t select rownum, rownum+50000 from dba_objects;

23:29:56 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:29:56 2 /

SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 66

23:29:56 SQL> select count(*) from t where a=17;

COUNT(*)
----------
1

23:30:16 SQL> @t
23:30:17 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:30:17 2 /

SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 67

23:30:17 SQL> select a.a, b.a from t a, t b where a.a=b.a and a.a=100;

A A
---------- ----------
100 100

23:30:24 SQL> @t
23:30:26 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:30:26 2 /

SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 69

23:30:26 SQL> select a.a, b.a from t a, t b where a.a=b.a and a.a=100;

A A
---------- ----------
100 100

23:30:29 SQL> @t
23:30:31 SQL> select * from v$sesstat where sid=181 and statistic#
between 203 and 207
23:30:31 2 /

SID STATISTIC# VALUE
----- ---------- ----------
181 203 4
181 204 0
181 205 0
181 206 21
181 207 71

How to predict Index blevel and when to rebuild.

2006-03-11T16:50:00.000-08:00

Determining Index blevel's

Depth of an index(blevel plus one) would determine the minimum number of consistent gets required for Index lookups in any joins and in index range scans. In nested loop joins, Oracle would always read index root block into process PGA memory and hence one less block to read from an index in every loop iteration. For queries like "where x between :a and :b" Oracle would always do leaf block level walk throughs, hence caching any index blocks is not required here.

So, index blevel is one of the key indicators of performance of sql queries doing Index range scans.

Index depth is one of the prime factor involved in determining whether to rebuild an index. In most cases, you wouldn't need to rebuild any index. You may consider doing it when you have one off major delete/update done on that table.

You can more or less predict when would blevel of an index would go from one level to another level. If you have right hand index it is very easy to predict blevel, otherwise it becomes very hard. Here is a simple test case.

create table t(a number);
create index t_idx on t(a);
insert into t select rownum from all_objects where rownum <= 500;
analyze index t_idx compute statistics;
select blevel from user_indexes where index_name='T_IDX';

You would see around 540 rows to fit in one Index 8K blocksize. So, Index blevel would change when the number of index records reach power(540, blevel). Bear in mind that the number of indexed columns, their datatype, and the column size would dictate the number of records fit in one index block.

Blevel #Records
0 540
1 29K
2 150 million
3 8.5 billion

See the difference in number of records from blevel 2 to 3, that is the reason why you would hardly see index blevel going beyond 3.

If you have one off major deletes/updates done on your table, then consider rebuilding the affected indexes on that table. It all depends on the way you access and modify key values of that index.

Weirdness of v$datafile on a standby db.

2006-03-10T22:41:00.000-08:00

Here are some oddities of v$datafile on a standby

Oracle gets content of v$datafile from controlfile, but on a standby database "STATUS" column wouldn't get updated in some scenarios. All datafiles created after the standby controlfile creation timestamp would get "RECOVER" status as they get pushed to the standby database. But how to find out if the datafile is offline or online?

V$datafile_header is the answer. Whenever we query v$datafile_header, Oracle would visit all datafile header blocks, and it gives us the right "STATUS" of datafiles.

What you see in v$datafile for Checkpoint_change# and Checkpoint_time may not be correct if you refresh that datafile from the primary and the recovery on those datafiles haven't started yet, and here also v$datafile_header is an answer.

If a datafile is offline, it's tablespace number will be "0" in v$datafile_header, and you need to use v$datafile for this purpose.

Also, Unrecoverable_change# and Unrecoverable_time columns would never get updated in v$datafile on any standby database.

How do you know where to start for the recovery, you can in general use v$recover_file. But on a standby database, you may not see entry for some datafiles in v$recover_file, and v$datafile_header is an answer here also.

You may occasionally get ORA-235 "controlfile fixed table inconsistent due to concurrent update" while querying v$datafile, then again you can get most of that information with more correctness from v$datafile_header.

You may sometimes find using rule hint may help your query run faster against v$datafile, but try it only if you get ORA-235 error.

How to split a table into rowid ranges.

2006-03-10T20:20:00.000-08:00

This script helps immensely to copy any table with "out of row" lob columns in it across dblink. This can also be used for CTAS over dblinks, any parallel insert/delete/update operations.

This is not a duplicate to what Oracle parallel query option does, it basically fills in the gaps of Oracle parallel query option.

For example:

Out of row lobs takes hell lot of time to be copied over to another table, Oracle parallel query doesn’t help here across dblinks.

You can’t get parallel queries on both source and target tables of CTAS operation across db links.

Good amount of limitations on Oracle parallel DML operations.

By using this script you can split any table into up to 255 rowid ranges.

-- This script spits desired number of rowid ranges to be used for any parallel operations.
-- Best to use it for copying a huge table with out of row lob columns in it or CTAS/copy the data over db links.
-- This can also be used to simulate parallel insert/update/delete operations.
-- Maximum number of rowid ranges you can get here is 255.
-- Doesn't work for partitioned tables, but with minor changes it can be adopted easily.

-- Doesn't display any output if the total table blocks are less than rowid ranges times 128.

-- It can split a table into more ranges than the number of extents

set verify off
undefine rowid_ranges
undefine seg_name
undefine owner
set head off
set pages 0
set trimspool on

select decode(rn3, &&rowid_ranges, 'rowid >= ''', 'rowid between ''')||
sys.dbms_rowid.rowid_create(1, id, file_id, new_bid, 0)||
decode(rn3, &&rowid_ranges, '''', ''' and ''')||
decode(rn3, &&rowid_ranges, null, sys.dbms_rowid.rowid_create(1, id, fid2, bid2-1, 99999))||
decode(rn3, &&rowid_ranges, null, '''') from (select id, file_id, new_bid, rn3, lead(file_id) over (order by rn3) fid2,
lead(new_bid) over (order by rn3) bid2
from
(select file_id, block_id, rn3,
round(block_id + (equi_blks * (rn5 - 1))) new_bid, id
from
(select file_id, block_id, equi_blks, rn3,
row_number() over (partition by rn1 order by rn3) rn5
from
(select file_id, block_id, equi_blks, rn1, rn3,
row_number() over (partition by rn3 order by rn1) rn4
from
(select file_id, block_id, sum2, equi_blks, rn1
from
(select file_id, block_id, sum2, rn1, equi_blks,
row_number() over (partition by rn1 order by sum2) rn2
from
(select file_id, block_id, sum1, sum2, (sum1/&&rowid_ranges) equi_blks,
trunc((sum2 * &&rowid_ranges)/(sum1 + 1))+1 rn1
from
(select relative_fno file_id, block_id, sum(blocks) over () sum1,
sum(blocks) over (order by relative_fno, block_id) sum2
from dba_extents
where
segment_name = upper('&&seg_name') and
owner = upper('&&amp;amp;owner') and
256 > &&rowid_ranges
)
where
sum1 > (128 * &&rowid_ranges)
)
)
where rn2 = 1
) a,
(select rownum rn3 from (select 1 from dual group by cube (1, 1, 1, 1, 1, 1, 1, 1))
where rownum <= &&rowid_ranges ) b where b.rn3 <= a.rn1 ) where rn4 = 1 ) c, (select max(data_object_id) id from dba_objects where object_name = upper('&seg_name') and owner = upper('&owner') and data_object_id is not null ) d ) ) /

-------------------------> End of the sql script
-------------------------> <-----------------------

--- Update on April 28th 2009, above script has a bug, please use the corrected script below:

select
'rowid between '''||sys.dbms_rowid.rowid_create(1, d.oid, c.fid1, c.bid1, 0)||''' and '''||
sys.dbms_rowid.rowid_create(1, d.oid, c.fid2, c.bid2, 9999)||''''
from
(select
distinct b.rn,
first_value(a.fid) over (partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid1,
last_value(a.fid) over (partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid2,
first_value(decode(sign(range2-range1), 1, a.bid+((b.rn-a.range1)*a.chunks1), a.bid)) over
(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid1,
last_value(decode(sign(range2-range1), 1, a.bid+((b.rn-a.range1+1)*a.chunks1)-1, (a.bid+a.blocks-1))) over
(partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid2
from
(select
fid,
bid,
blocks,
chunks1,
trunc((sum2-blocks+1-0.1)/chunks1) range1,
trunc((sum2-0.1)/chunks1) range2
from
(select /*+ rule */
relative_fno fid,
block_id bid,
blocks,
sum(blocks) over () sum1,
trunc((sum(blocks) over ())/&&rowid_ranges) chunks1,
sum(blocks) over (order by relative_fno, block_id) sum2
from dba_extents
where
segment_name = upper('&&segment_name') and
owner = upper('&&owner')
)
where
sum1 > &&rowid_ranges
) a,
(select rownum-1 rn from dual connect by level <= &&rowid_ranges) b
where
b.rn between a.range1 and a.range2
) c,
(select max(data_object_id) oid from dba_objects
where object_name = upper('&&segment_name') and owner = upper('&&owner') and data_object_id is not null
) d
/

Quick summary of locks and skip v$lock.

2006-03-10T19:54:00.000-08:00

Lock summary without accessing v$lock

/*
Script to report summary of lock activity by waiters on the database.
OBJ -> Table: waitevent: p1: p2: locktype: ROWID.
CMD -> Type of command being waited on.
SES_CNT -> Number of sessions waiting.
HASH -> Hash value being waited on.
MODULE -> Module they are coming from.
Written by Sai */

col obj for a70
col cmd for a19
col module for a15
set lines 160
set trimspool on

select obj, cmd, count(*) as ses_cnt, min(hash_value) hash, module
from
(select decode(sign(ROW_WAIT_OBJ#), 1,
(select object_name from dba_objects where object_id=ROW_WAIT_OBJ# and data_object_id is not null)
,-1, 'UNKNOWN', 'UNKNOWN') || ':' ||
w.event || ':' || w.p2 || ':' || w.p3 || ':' ||
chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1, 16711680)/65535) || ':''' ||
decode(sign(ROW_WAIT_OBJ#), 1, dbms_rowid.rowid_create(1, (select data_object_id from dba_objects
where object_id = ROW_WAIT_OBJ# and data_object_id is not null),
ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#), 'No rowid') || '''' as obj,
decode(command, 2, 'INSERT', 6, 'UPDATE', 7, 'DELETE', 182, 'UPDATE INDEXES', 3, 'SELECT FOR UPDATE', command) as cmd,
decode(sign(sql_hash_value), 1, sql_hash_value, prev_hash_value) hash_value,
module
from
v$session s, v$session_wait w
where
status = 'ACTIVE' and
lockwait is not null and
s.sid = w.sid)
group by obj, cmd, module
order by 3, 1
/