How to improve the performance of Oracle database

By admin News

Published by Jacob Martin on June 18, 2015

To solve oracle issue, one must keep in mind that each and every technique must be tested, as it improves the performance in one area, while it may degrade the performance of another area. For Example: Adding an index improves the performance of Query, but the performance of delete and inserts becomes slower, as the entries of the index also gets updated.

Before starting, one must be aware of on improving response time and how memory usage can be reduced. This may lead to rewriting or redesigning some stored procedures. It is done by tuning the performance of Oracle.

Full Table Scan to tune the performance of Oracle: This improves the performance in two ways:- while querying tables of large size and tables of small size.

1.1. Query of small Table and its effect: A table has 2 columns – Customer id and customer name; with index on customer id. Now to search the customer name for a particular id, it will first read the index and then the table. As the table is small, and as Oracle can read multiple database blocks in a single read operation. The entire table is scanned in a single read only. Here the full table scanning is quicker than scanning through an index and table lookup. There is only one exception is when the table is created as index only table, that is the entire table is stored in B-Tree structure.

1.2. Query of Large Table and its effect: Full Table scan of large table scans the entire data blocks, this is better than performing table lookup and an index scan. This happens when retrieving is 10% or more of data from the table. It will also perform better when the retrieving is as little as 1% from the table.

In order to decide, whether an index read is better than full table scan. One has to consider, if data in the large table is denser than full table scan is more efficient. And for the small table it is generally done by using an index-organized table.

Using B-Tree Indexes to tune the performances of Oracle: It is most appropriate to retrieve small amount of data from a table of larger size. Retrieving of data through index is more efficient for tables of larger size and the smaller number of rows.

By using sub-query, Indexes improves update operation and data access. The indexes has to be adjusted, when a row is deleted from, updated or inserted into the database. Index is ideal for only read only database, but if the proportion of deletes and inserts increases then adding more indexes will degrade its performance.

In B-tree index only one row of the table is referred as primary key. Thus, it reduces the number of inputs to fetch the data.

These are the ways how we can increase the performance the Oracle Performance tuning.

If you find that there should be more improvement in the performance of the oracle database, then it can be done by using the Oracle File Repair Tool software. By using this software one can also recover damaged, corrupt or inaccessible files from the database.

Steps to recover damaged or corrupted Oracle File

Step 1: Search Initial screen of Stellar Phoenix Oracle Recovery with a pop-up window showing options to select or search corrupt Oracle databases in your computer. 1-1439296

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

2-9613517

Step 3: Click an object to see its preview.

3-8003050

Step 4: : Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

4-9758622