All the Views expressed here are my own and do not reflect opinions or views of the anyone else.All the views are tested on my testing environment and kindly test the post before applying anything on production.You can reach to me at neeraj.vishen@gmail.com .

Saturday, April 28, 2012

Indexes plays and crucial role in the performance tunning of a database . It is very important to know how the index work i.e, how indexes fetches the data's from a tables . There is a very good post by rleishman on the working of indexes . Let's have a look .

What is an Index ?

An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows. It is just as the index in this manual helps us to locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data .

Blocks

First we need to understand a block. A block - or page for Microsoft boffins - is the smallest unit of disk that Oracle will read or write. All data in Oracle - tables, indexes, clusters - is stored in blocks. The block size is configurable for any given database but is usually one of 4Kb, 8Kb, 16Kb, or 32Kb. Rows in a table are usually much smaller than this, so many rows will generally fit into a single block. So we never read "just one row"; we will always read the entire block and ignore the rows we don't need. Minimising this wastage is one of the fundamentals of Oracle Performance Tuning.

Oracle uses two different index architectures: b-Tree indexes and bitmap indexes. Cluster indexes, bitmap join indexes, function-based indexes, reverse key indexes and text indexes are all just variations on the two main types. b-Tree is the "normal" index .

The "-Tree" in b-Tree

A b-Tree index is a data structure in the form of a tree - no surprises there - but it is a tree of database blocks, not rows. Imagine the leaf blocks of the index as the pages of a phone book . Each page in the book (leaf block in the index) contains many entries, which consist of a name (indexed column value) and an address (ROWID) that tells us the physical location of the telephone (row in the table).

The names on each page are sorted, and the pages - when sorted correctly - contain a complete sorted list of every name and address

A sorted list in a phone book is fine for humans, beacuse we have mastered "the flick" - the ability to fan through the book looking for the page that will contain our target without reading the entire page. When we flick through the phone book, we are just reading the first name on each page, which is usually in a larger font in the page header. Oracle cannot read a single name (row) and ignore the reset of the page (block); it needs to read the entire block.

If we had no thumbs, we may find it convenient to create a separate ordered list containing the first name on each page of the phone book along with the page number. This is how the branch-blocks of an index work; a reduced list that contains the first row of each block plus the address of that block. In a large phone book, this reduced list containing one entry per page will still cover many pages, so the process is repeated, creating the next level up in the index, and so on until we are left with a single page: the root of the tree.

For example :

To find the name Gallileo in this b-Tree phone book, we:

=> Read page 1. This tells us that page 6 starts with Fermat and that page 7 starts with Hawking.

=> Read page 6. This tells us that page 350 starts with Fyshe and that page 351 starts with Garibaldi.

=> Read page 350, which is a leaf block; we find Gallileo's address and phone number.

=> That's it; 3 blocks to find a specific row in a million row table. In reality, index blocks often fit 100 or more rows, so b-Trees are typically quite shallow. I have never seen an index with more than 5 levels. Curious? Try this:

SQL> select index_name, blevel+1 from user_indexes order by 2 ;

user_indexes.blevel is the number of branch levels. Always add 1 to include the leaf level; this tells us the number of blocks a unique index scan must read to reach the leaf-block. If we're really, really, insatiably curious; try this in SQL*Plus:

Give the name of an index on a smallish table (because this will create a BIG file). Now, on the Oracle server, go to the directory shown by the final SHOW PARAMETER user_dump_dest command and find the trace file - the file name will contain the index name. Here is a sample:

As we insert new rows into the table, new rows are inserted into index leaf blocks. When a leaf block is full, another insert will cause the block to be split into two blocks, which means an entry for the new block must be added to the parent branch-block. If the branch-block is also full, it too is split. The process propagates back up the tree until the parent of split has space for one more entry, or the root is reached. A new root is created if the root node splits. Staggeringly, this process ensures that every branch will be the same length.

How are Indexes used ?
Indexes have three main uses:

To quickly find specific rows by avoiding a Full Table Scan

We've already seen above how a Unique Scan works. Using the phone book metaphor, it's not hard to understand how a Range Scan works in much the same way to find all people named "Gallileo", or all of the names alphabetically between "Smith" and "Smythe". Range Scans can occur when we use >, <, LIKE, or BETWEEN in a WHERE clause. A range scan will find the first row in the range using the same technique as the Unique Scan, but will then keep reading the index up to the end of the range. It is OK if the range covers many blocks.

To avoid a table access altogether

If all we wanted to do when looking up Gallileo in the phone book was to find his address or phone number, the job would be done. However if we wanted to know his date of birth, we'd have to phone and ask. This takes time. If it was something that we needed all the time, like an email address, we could save time by adding it to the phone book.

Oracle does the same thing. If the information is in the index, then it doesn't bother to read the table. It is a reasonably common technique to add columns to an index, not because they will be used as part of the index scan, but because they save a table access. In fact, Oracle may even perform a Fast Full Scan of an index that it cannot use in a Range or Unique scan just to avoid a table access.

To avoid a sort

This one is not so well known, largely because it is so poorly documented (and in many cases, unpredicatably implemented by the Optimizer as well). Oracle performs a sort for many reasons: ORDER BY, GROUP BY, DISTINCT, Set operations (eg. UNION), Sort-Merge Joins, uncorrelated IN-subqueries, Analytic Functions). If a sort operation requires rows in the same order as the index, then Oracle may read the table rows via the index. A sort operation is not necessary since the rows are returned in sorted order.

Despite all of the instances listed above where a sort is performed, I have only seen three cases where a sort is actually avoided.

This is an extraordinary tuning technique in OLTP systems like SQL*Forms that return one page of detail at a time to the screen. A SQL with a DISTINCT, GROUP BY, or ORDER BY that uses an index to sort can return just the first page of matching rows without having to fetch the entire result set for a sort. This can be the difference between sub-second response time and several minutes or hours.

Full table Scans are not bad :

Up to now, we've seen how indexes can be good. It's not always the case; sometimes indexes are no help at all, or worse: they make a query slower.

A b-Tree index will be no help at all in a reduced scan unless the WHERE clause compares indexed columns using >, <, LIKE, IN, or BETWEEN operators. A b-Tree index cannot be used to scan for any NOT style operators: eg. !=, NOT IN, NOT LIKE. There are lots of conditions, caveats, and complexities regarding joins, sub-queries, OR predicates, functions (inc. arithmetic and concatenation), and casting that are outside the scope of this article. Consult a good SQL tuning manual.

Much more interesting - and important - are the cases where an index makes a SQL slower. These are particularly common in batch systems that process large quantities of data.

To explain the problem, we need a new metaphor. Imagine a large deciduous tree in our front yard. It's Autumn, and it's our job to pick up all of the leaves on the lawn. Clearly, the fastest way to do this (without a rake, or a leaf-vac...) would be get down on hands and knees with a bag and work our way back and forth over the lawn, stuffing leaves in the bag as we go. This is a Full Table Scan, selecting rows in no particular order, except that they are nearest to hand. This metaphor works on a couple of levels: we would grab leaves in handfuls, not one by one. A Full Table Scan does the same thing: when a bock is read from disk, Oracle caches the next few blocks with the expectation that it will be asked for them very soon. Type this in SQL*Plus:

SQL> show parameter db_file_multiblock_read_count

Just to shake things up a bit (and to feed an undiagnosed obsessive compulsive disorder), we decide to pick up the leaves in order of size. In support of this endeavour, we take a digital photograph of the lawn, write an image analysis program to identify and measure every leaf, then load the results into a Virtual Reality headset that will highlight the smallest leaf left on the lawn. Ingenious, yes; but this is clearly going to take a lot longer than a full table scan because we cover much more distance walking from leaf to leaf.

So obviously Full Table Scan is the faster way to pick up every leaf. But just as obvious is that the index (virtual reality headset) is the faster way to pick up just the smallest leaf, or even the 100 smallest leaves. As the number rises, we approach a break-even point; a number beyond which it is faster to just full table scan. This number varies depending on the table, the index, the database settings, the hardware, and the load on the server; generally it is somewhere between 1% and 10% of the table.

The main reasons for this are :

As implied above, reading a table in indexed order means more movement for the disk head.

Oracle cannot read single rows. To read a row via an index, the entire block must be read with all but one row discarded. So an index scan of 100 rows would read 100 blocks, but a FTS might read 100 rows in a single block.

Even if none of these things was true, accessing the entire index and the entire table is still more IO than just accessing the table.

So what's the lesson here? Know our data! If our query needs 50% of the rows in the table to resolve our query, an index scan just won't help. Not only should we not bother creating or investigating the existence of an index, we should check to make sure Oracle is not already using an index. There are a number of ways to influence index usage; once again, consult a tuning manual. The exception to this rule - there's always one - is when all of the columns referenced in the SQL are contained in the index. If Oracle does not have to access the table then there is no break-even point; it is generally quicker to scan the index even for 100% of the rows.

Summary :

Indexes are not a dark-art; they work in an entirely predictable and even intuitive way. Understanding how they work moves Performance Tuning from the realm of guesswork to that of science; so embrace the technology and read the manual.

Thursday, April 26, 2012

Performance tunning is one of the biggest responsibilities of a DBA to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance. Performance tunning is not an easy task. The main issues with tunning for beginners is that from where to start and what should be the right approach . Here is very good presesntation by Arup Nanda who is having more than 16 years of experience as Oracle DBA . Click the below link to find his presentation .

Monday, April 23, 2012

Sometimes, we may not very sure whether an oracle parameter is static(restarting database is required to come under the action) parameter or dynamic(can be changed without restarting) parameter . We can check this by using the v$parameter2 view which is very similar to v$parameter having few extra rows for long parameters . The another difference between the v$parameter and v$parameter2 is that the format of the output .. For example, if a parameter value say "x,y" in V$PARAMETER view does not tell us if the parameter has two values ("x" and "y") or one value ("x, y") whereas V$PARAMETER2 makes the distinction between the list parameter values clear.

SQL> select value from v$parameter WHERE name LIKE 'control_files' ;

SQL> select value from v$parameter2 WHERE name LIKE 'control_files' ;

Here, If ISSES_MODIFIABLE parameter is true, the parameter can be changed on session level , and if ISSES_MODIFIABLE or ISINSTANCE_MODIFIABLE is true, then parameter can be changed on system level. Here is an example

SQL> SELECT name,Value ,ISSES_MODIFIABLE , ISINSTANCE_MODIFIABLE FROM v$parameter2 WHERE name LIKE '%target%' ;

Friday, April 13, 2012

OUI (Oracle Universal Installer) is a program used to install Oracle software and database options.We generally use the OUI in GUI mode to install the Oracle software . Sometimes, it may required to install the oracle software in silent mode that is without invoking the OUI . This can be done by using the reponse file .

An Oracle database response file specifies parameters for configuring an Oracle event database.The response file can be created by running the Oracle universal installer (OUI) and records the steps and stages in the response file or we can found the demo response file in oracle software in "response" directory .We can create the response file by invoking the below command

The "-record" parameter tells the installer to write to the response file and the "-destinationFile" parameter defines the name and location of the response file.The process is similar to using Kickstart for Linux installations .We can prepare the oracle environment from here . I have editted by response file and highlighted(bold with blue shade) the modified value. Below is response file.

Wednesday, April 11, 2012

On googling about the redo log thread, i have not found proper documentation that clearly explains clearly what the redo log thread is . Here i am trying to cover the redo log threads in case of single instance and RAC taking reference from ASKTOM site .

Each instance has it's own personal set of redo and each redo thread is made up of at least two groups that have one or more members (files) .Two instances will never write to the same redo files - each instance has it's own set of redo logs to write to . Another instance may well READ some other instances redo logs - after that other instance fails for example - to perform recovery. Here is a scenario which helps us to understand the thread concepts .

Most V$ views work by selecting information from the corresponding GV$ view with a predicate "where instance_id = <that instance>". So V$SESSION in single Instance(i.e, 1) is actually

SQL>select * from gv$instance where inst_id= 1 ;

On a three node RAC database, if we select from v$session, we get sessions from that instance only. Selecting from GV$SESSION creates parallel query slaves on the other instances and gets the information back to our session.

This works fine in almost all cases. There are few exceptions: in case of redo logs, the RAC instance must see all the redo logs of other instances as they become important for its recovery. Therefore, V$LOG actually shows all the redo logs, of all the instances, not just of its own. Contrast this with V$SESSION, which shows only sessions of that instance, not all. So, if there are 3 log file groups per instance (actually, per "thread") and there are 3 instances, V$LOG on any instance will show all 9 logfile groups, not 3.

When we select form GV$LOG, remember, the session gets the information from other instances as well. Unfortunately, the PQ servers on those instances also get 9 records each, since they also see the same information seen by the first instance. On a three instance RAC, we will get 3X9 = 27 records in GV$LOG!

To avoid this:

1.) Always select from V$LOG, V$LOGFILE and V$THREAD in a RAC instance. GV$ views are misleading or

2.) Add a predicate to match THREAD# with INST_ID. (Beware: thread numbers are by default the same as the instance_id; but we may have defined a different thread number while creating the database) as

Tuesday, April 3, 2012

A cold backup does have the somewhat bad side effect of wiping out our shared pool, our buffer cache and preventing our users from logging in to do work. Our database is like a car, it runs better when it is warmed up. If we want to cold start it - be prepared for rough running when we restart as we have to rebuild that shared pool, that buffer cache and so on . I would never pick cold over hot given the chance. No benefit, only downsides (Acc. to Tkye). The only kind of backup we do on our production systems here is hot .

There are two ways to perform Oracle backup and recovery :

1.) Recovery Manager (RMAN) : It is an Oracle utility that can backup, restore, and recover database files. It is a feature of the Oracle database server and does not require separate installation.

2.) User-Managed backup and recovery :We use operating system commands for backups and SQL*Plus for recovery. This method is called user-managed backup and recovery and is fully supported by Oracle, although use of RMAN is highly recommended because it is more robust and greatly simplifies administration.

There are basically two types of backup .The backup are as

1.) Consistent Backup : This is also know as Cold Backup . A consistent backup is one in which the files being backed up contain all changes up to the same system change number (SCN). This means that the files in the backup contain all the data taken from a same point in time .

2.) Inconsistent Backup : This is also known as Hot backup . An inconsistent backup is a backup in which the files being backed up do not contain all the changes made at all the SCNs . This can occur because the datafiles are being modified as backups are being taken.

There are some DBAs which prefer oracle user-managed backups.They put their database into backup mode prior to backing up and take it out of backup mode after backup. If we 're going to perform user-managed backups, we must back up all of the following file :

Datafiles

Control files

Online redo logs (if performing a cold backup)

The parameter file (not mandatory )

Archived redo logs

Password file if used

The below diagram shows the Whole Database Backup Options :

A hot backup requires quite a bit more work than cold backup.Below are steps required for Hot backup.

Step 1 : Check the log mode of the database Whenever we go for hot backup then the database must be in archivelog mode . SQL> SELECT LOG_MODE FROM V$DATABASE ;LOG_MODE---------------ARCHIVELOG

Step 2 : Put the database into backup mode If we are using the oracle 10gR2 or later , then we can put the entire database into backup mode and if we are using the oracle prior to 10gR2 ,then we have to put each tablespace in backup mode . In my case , I am having 11gR2 . SQL> alter database begin backup ; Database altered.In case of oracle prior to 10gR2 use the below command as SQL> set echo off SQL> set heading off SQL> set feedback off SQL> set termout off SQL> spool backmode.sql SQL> select 'alter tablespace '||name||' begin backup ;' "Tablespace in backup mode" from v$tablespace;
SQL> spool off SQL> @C:\backmode.sql

Step 3 : Backup all the datafiles Copy all the datafile using the operating system command and Paste it on the desired backup location .Meanwhile,we can verify the status of the datafile by using the v$backup view to check the status of the datafiles.

Step 4 : Take out the database from backup modeIf we are using 10gR2 or above version of oracle , we use the below command to take out the database from backup mode as

SQL> alter database end backup ; Database Altered If we are having version prior to 10gR2 , then we use the below command as above : SQL> set echo off SQL> set heading off SQL> set feedback off SQL> set termout off SQL> spool end_mode.sql SQL> select 'alter tablespace '||name||' end backup ;' "tablespace in backup mode" from v$tablespace ;
SQL> spool off SQL> @C:\endmode.sql

Step 5 : Switch the redolog file and backup archivelogs After taking the database out of Hot Backup we must switch logfile (preferably more than once) and backup the archivelogs generated .We may backup archivelogs while the database is in backup mode but we must also backup the first archivelog(s) after the end backup. The best method to do both is to run the SQL command alter system archive log current. This switches the logfile but does not return the prompt until the previous redo log has been archived. We can run alter system switch logfile, but then we won't be sure that the latest redo log has been archived before we move on to the next step.

SQL> alter system archive log current ; System altered.SQL> / System altered.Now backup the archivelogs to the backup location .

Step 6 : Back up the control file Now , we can backup the controlfile as binary file and as human readable .We should use both methods to back up the control file; either one may come in handy at different times . The commands are as (Human readable)SQL> alter database backup controlfile to trace ; or Database altered.SQL> alter database backup controlfile to trace as '<backup location>' ; Database altered.(Binary format)SQL> alter database backup controlfile to '<backup location>' ; Database altered.

Step 7 : Backup the passwordfile and spfile We can backup the passwordfile and spfile though it is not mandatory.

Some Points Worth Remembering

We need to backup all the archived log files, these files are very important to do recovery.

It is advisable to backup all of tablespaces (except read-only tablespaces), else complete recovery is not possible.

Backup of online redo log files are not required, as the online log file has the end of backup marker and would cause corruption if used in recovery.

It is Preferable to start the hot backups at low activity time.

When hot backups are in progress we "cannot" shutdown the database in NORMAL or IMMEDIATE mode (and it is also not desirable to ABORT).

Search This Blog

Translate My Page

About Me

I have started this blog to share my knowledge and experience with other Oracle DBA enthusiasts.I have experience on database architecture ,design and administrating ranging from 9i,10g,11g on various platforms.My main interests are high availability and disaster recovery solutions for mission critical 24×7 systems.