Database Optimization

Oracle Archive log

Often time when we work with test databases, a full  backup of the database would be available. Depending on the test required, most of the time, we do not need to have ongoing ARCHIVELOG. We can reduce the number of I/O and processing time on the test database server by turning the ARCHIVELOG off.

In order to turn this option off, we can follow these steps:

Step 1: Connect as internal (as sysdba)

Step 2: shutdown immediate;

Step 3: startup mount;

Step 4: alter database noarchivelog;

Step 5: alter database open;

Optionally, you can restart the database and check the value in V$DATABASE

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

Be the first to comment - What do you think?  Posted by ProAdmin - May 12, 2014 at 10:59 am

Categories: Database Optimization   Tags:

Optimizing Oracle11g with Result_Cache

Oracle11g comes with a lot of new features to help improve database performance. One of the feature that can be used to drastically improve performance is the Result_Cache option.

Used wisely, the Result_Cache can improve queries to commonly used tables such as Cities and Countries lookup tables. On one of our project, we used Result_Cache option to retrieve a list of active medical doctors and we were able to achieve 30X performance improvement compared to not using that option.

The Result_Cache option is an indicator to command Oracle to store the result set in memory (SGA) as long as there is no change in the data. The initial query will take longer to return data compared to subsequent queries which will fetch the result set from memory.

Oracle11g Rel2 has some minor enhancement made to the command issued to handle the Result_Cache. Oracle has simplified the command and developers do not have to worry about having to keep track of the specific tables used in functions. Namely the Relies_On command is no longer required in Rel2.

Sample Oracle functions and SQL statements can be reviewed from Oracle’s AskTom http://www.oracle.com/technology/oramag/oracle/07-sep/o57asktom.html

or on ToadWorld’s Steven Feuerstein blog http://www.toadworld.com/Experts/StevenFeuersteinsPLSQLExperience/TrainingandPresentations/PLSQLNewFeatures/tabid/161/Default.aspx#Oracle11g

If you would like to comment on this blog or contact me personally, I can be reached at mike_blogger@optimal.ca

Be the first to comment - What do you think?  Posted by ProAdmin - August 21, 2010 at 1:09 am

Categories: Database Optimization   Tags: