All about Oracle

Tools for tuning the database

2009-06-30T22:46:00.001+05:30

In previous segment, we talked about various aspects of Performance Tuning. There are many tools provided by Oracle to monitor these aspects. These are1. Oracle Enterprise Manager : This is a web based tool provided by Oracle to manage databases efficiently. OEM helps in all kind of DBA activities including create tables, manage tablespaces etc.. In addition, OEM provides interface for various

Performance Tuning – Overview

2009-06-25T00:01:00.002+05:30

The first and foremost question which comes in mind is what is the need for tuning? This is the world of fast food, fast bikes, fast music, fast cars, in short fast everything, then why not fast database as well? Today everyone is in some kind of a race, and to achieve his/her goal one needs to win that race. To win a race one needs to either run fastest of all or make the horse (or car) run

Performance Tuning

2009-06-22T18:47:00.003+05:30

The word itself is vast enough to write a book on it. And many have already been written. Then why am I writing a blog on PT? Answer is simple; for quick check. Books are very tedious for searching something quickly. So I am trying to consolidate things and put it in a place easy to find. You can say it is kind of quick reference when I am in mid of something and do not want to go and search on

Oracle Listener Security

2007-11-16T15:36:00.000+05:30

You are working on a critical issue on your database. You login to your database and you get the error "ORA-12541: TNS:no listener". Now you start wondering how the listener has been stopped. You have not stopped it. It means "SOMEONE ELSE HAS STOPPED THE LISTENER". It shows, you need to secure your listener from unauthorized users.You can protect your listener with a password so that only the

Auditing ALTER SYSTEM commands

2007-11-16T12:11:00.000+05:30

Wonder, who is changing the system date? Who is changing the parameter? Who is giving all those ALTER SYSTEM commands which are causing havoc for others?The simplest solution of the problem is, "DO NOT PROVIDE PASSWORD OF DBA USER TO A PERSON WHO IS NOT A DBA". Looks simple, right? Wrong. In developement environments it is not possible to restrict the access of DBA user to 1 or 2 persons. And it

Virtual Indexes

2007-09-21T17:47:00.000+05:30

Recently I have been to one of our clients for performance tuning of few processes. The process of tuning SQL queries requires testing of different alternatives of indexes. It becomes difficult in production environment where lots of people are connected to the database and tables are large.Virtual indexes helps in such situations. Since virtual indexes does not use any segment, time required to

Auditing SYS users

2007-02-06T13:57:00.000+05:30

The SYS user is the most usefull and important user for any Oracle database. Lets take a look how a DBA can take hold of it if it is being misused.Oracle provides the auditing facility of SYS account since the version 7 but after 9iR2, it has become more powerful in many ways.How to Audit SYS accountMainly there are only two parameters which need to be set to audit any SYS account,

Trace Analyzer

2006-12-18T17:06:00.000+05:30

Trace analyzer is used to interpret raw trace files. It is similar to tkprof but its output is more readable than tkprof. It generates a report with details: time summary, call summary (parse, execute, fetch), identification of top SQL, row source plan, explain plan, CBO statistics, wait events, values of bind variables, I/O summary per schema object, latches, hot blocks, etc.I like it for its

Renaming table when MV log is associated

2006-12-15T12:08:00.000+05:30

Recently, I faced problem in renaming a table. The table had a materialized view log associated with it and I wanted to move the older version of the table and place a new version of the same instead.But I was getting following error while renaming the old table:ORA-26563: renaming this table is not allowedOne of the reasons of getting this error is, the table is used in a materialized view and a

Automatic SQL Tuning

2006-12-14T12:08:00.000+05:30

As we are aware, manual SQL tuning process is very complex and time consuming, Oracle 10g introduces automatic SQL tuning to ease out the tuning process. To use this feature, we need to use SQL tuning advisor.Here, I will be giving a brief introduction of Automatic SQL Tuning and the simplest way of using SQL Tuning Advisor.The query optimizer can run in two modes:1. Normal mode: In this mode,

COUNT(*) Vs. COUNT(1)

2006-12-13T17:18:00.000+05:30

The debat COUNT(*) vs. COUNT(1) is on for years now. So I thought of doing experiment on the same and here are the result:I have done the test for indexed table as well as non-indexed table. The table has 6 millions of rows in both the tables and the rowwidth is approx 4000 bytes. 1. COUNT(1) is internally converted to COUNT(*).SQL> EXPLAIN PLAN FOR 2 select max(REG_COUNT) from f_reg 3 group

Parameterized View

2006-12-13T14:56:00.001+05:30

I always used to think why oracle didn't provide parameterized views similar to parameterized cursors, till I found workaround for the same.Following is an example which shows use of parameterized view:CREATE OR REPLACE VIEW test_vw ASSELECT empno,ename,salFROM empWHERE empno = to_number(userenv('client_info')) ;In Stored Procedure, will have to call following procedure before executing the View

DDL Triggers

2006-10-18T15:30:00.000+05:30

A trigger is a block of PL/SQL or Java or C code that executes or fires when a particular event occurs within database.Triggers are useful for implementing code that must be executed on regular basis due to a pre-defined event. By utilizing, triggers, scheduling and data integrity problems can be eliminated because the trigger will be fired whenever the triggering event occurs. You need not

Dangers of Bind Variable

2006-10-18T15:25:00.000+05:30

We have seen and heard many advantages of using bind variables in a query, one of which is doing hard parse when a query is executed for the first time and then reusing the plan created at the time of first execution.But there is a catch in this. This is advantageous for less skewed data but if a column is heavily skewed, it becomes disadvantage. Look at the example given below:DROP TABLE t;

Use hints like FIRST_ROWS(n)

2006-10-18T14:43:00.000+05:30

Usually queries used for search fetch lots of records if proper search condition is not provided. If the query is fetching thousands of rows, that will be of no use for the end user. In such scenario, no of records should be limited by using ROWNUM <>/*+ FIRST_ROWS (n) */ should be used, where n is no of rows required. Note: Hint /*+ FIRST_ROWS */ does not know the scope of the query and