tkprof trace file analysis.....

Friends & Experts,

Examine the below trace file, which generated after running
the application for 7 mins. The person who give me this trace
file told me to tune this query, because his application takes
around 7 mins to complete one task which suppose to takes only
few second.


Can you figure out what the problem is ?
SELECT MIN(DATE_Column)
FROM
SOME_TABLE WHERE SOME_COLUMNE = :B1

call     count       cpu    elapsed    disk      query   current      rows
------- ------  -------- ---------- ------- ---------- ---------  --------
Parse       41      0.00       0.00       0          0         0         0
Execute  10846      3.99       4.17       0          0         0         0
Fetch    10846     23.04      24.39       0     264858         0     10646
------- ------  -------- ---------- ------- ---------- ---------  --------
total    21733     27.03      28.57       0     244858         0     10646
Lets, first try to locate the problem.

Execution count=10846
Elapsed Time=28.57, So on avg one execution takes around 3ms....So this is good
Consistent read block=264858, So on avg 24 blocks/execution....So this seams OK..


Than, where the problem is?? Problem is in Application designing(coding).
Out of 7min of tracing only 29 second used by this query, So where the remaining
400 seconds spent by application. One should need to find out this, for this it is
require to look at detailed application tracing and/or statspack/AWR report.

One more thing I like to raise here: Why application executes this query 25 times
per second? If there are multiple session going on simultaneously then this is ok,
Otherwise we need to look at this also.(Actually I saw one application which executes sql
50 times per second because of poor application coding only.)

Why good Database Design is important

It is very important to have good database/Application design as per our current application requirement. But, it is also very important that we should keep in mind that, this is not the only application which uses this database and we are not only one who use this, there may be lots of other applications which may develop on this database. So we should try our best to design one strong base so that other peoples also can easily scale application and can build new features on it.

One very good example of good design is the way Oracle build core database engine /architecture, way back in the past. If you are DBA, then you might note that, despite addition of lots of new features in each version of Oracle RDBMS, the core of Oracle remains same. There is hardly any change on core structure to accommodate/build more feature on it.

Oracle has layered (storage) architecture, and at core it has block(Managed block, for it's own use) than extent than datafile, redo log file, control file. And because of this intelligently designed architecture, it is easy to add functionality and features without changing or rewriting core structure. (From here, I can see that, one day oracle RDBMS not even require OS and because of this, it would be very very fast database engine. ASM is just first step towards this)

Just for one example, take a look on below up-gradation of Oracle RDBMS

• Oracle first have redo log files, which fills circularly.
• Next time Oracle decide to archive them before it get overwrite and we have "Archived Log File"
• Than Oracle decide to use these log file for database recovery.
• Than Oracle thought, if Archived Log file is used to recover the database from which it originates then it might be possible that we can recover copy of original database and upgrade it, and result of this is we have luxury of "Dataguard" and now in 11g there is Active data guard as well....and this up-gradation going on and on...

Similar way, you can consider the improvements in Oracle memory (SGA/PGA) architecutre.

Back to the main point from world or Oracle....!!
Recently, I have one assignment to develop small application for "time calculation" functionality.
When I analyze the architecture and design of database I realize that, it would be much complex to complete this task than my first estimation.

The database misses the core rule of normalization. I don't want to post original design in detail over here as it is strictly confidential things but let me explain it why it is missing some basic design rule with one example.

There is one table which maintain working hours for weekdays. and it is store like one mention below:-
Old Design
Day | Hour(Varchar)
MON| 8:00-12:00,13:00-17:00
TUE | 8:00-12:00,13:00-17:00

As you can see original designer save lots of column :) and uses only one varchar column to save all time part in it. (Thanks god, this enterprise has only one break during the day :).

There are more than one way to redesign this table based on different requirements.
For example we can change this as one shown below:-

Day | InTime | Outtime | Breaktime
Mon | 8:00 | 17:00 | 12:00-13:00

Because of old table design, I have to do some string manipulation operation and type casting operation to get data that I need for my purpose. I wonder, how many other application uses this table and how many other people have to do type casting and string manipulation operation to get data in correct(usable) form. Because of all this, it took double of time for me to complete this task than it would takes if this table design follow some normalization rule.

So, next time when you start working on database design just remember that your database design would save lots of time and efforts of other people in future, if it has really good design.

How to start database tuning and how to know, what to tune?

How to start database tuning and how to know, what to tune?

This question is frequently asked by many people online as well as offline.
Actually there is no rule of thumb for performance tuning. Before starting tuning process, one should ask him self, why he want to tune? Is database really require to tune?

Sometime people read article from web regarding "Best practice for database PT" and start to implementing same on their production database. Don't do this. Always take some precautionary steps to avoid bottleneck but this is not mean that you should implement each "Best Practice" on your database.

Performance tuning practice is like medical practice. When you visit your doctor, he/she first ask you few questions to find out what is problem and what is CAUSE of problem, before suggesting any medicine. Just imagine what happen if he/she give you medicine for all general disease without diagnosing any thing. In that case do we really need doctor? No.

So, the important thing is diagnosis of problem. Without knowing actual cause of problem you can't resolve it for a long time. :(though, you can resolve it for a short time :). Suppose you have fever and because of that you have headache and body-ache. In this case root cause of problem is fever, so take medicine for fever, not for headache.

What I am trying to convey is, first identify symptom, root cause of problem and then take necessary steps to solve it. This will solve your problem for long time without any side effect.

Most of the time you have message like application is running slow from end user. After this, it is your task to find out why it is running slow. Most of the time you will found that slow response is because of poorly written sql query. So, try to rewrite it before upgrading hardware or software for this :)

And remember that the person suffering from last stage of cancer do not need medicine for headache.

Share your thoughts for the same. Have a happy weekend..

Redefine your funda of "How to crack interview?"

Recently I give interview in one MNC (Multinational company). After few initial general discussion, she ask me to clarify difference between standby database and dataguard. I surprise by this question at first instant because I accept more challenging and discussion about some problematic scenario. In reply of this question I give clarification like one given below:-

Dataguard is set of process(you can call it infrastrcutre/architecture provided by Oracle) to configure standby database in order to protect your database against disaster.

But,.... But she was not convince by my explanation and stick to her question and then she redefine her question and asked me in another way:- why we want dataguard if we configure standby manually.

I thought, she wants more detailed answer for her initial question, So I try and give it as mention below

In absence of dataguard you have to transfer archive redo log manually(or by some code written by you) from primary database to standby database and then you have to apply it on your standby database. This require lots of manual work and efforts. To overcome this Oracle has its own environment/architecture to complete this task automatically, what you need is, just configuration of environment, and then oracle take care of all this stuff.

 After this explanation, I found that still she was not happy by my explanation(not even 1% !!) though my explanation is correct, and she want something else. I don't know, what she was expecting as answer of her question. Still, I am wondering what are the other possible explanation that I miss at that time, Please, let me know if you have some better explanation :) 

Moral of the story:- It is not important what is wrong and what is right during interview process, the important thing is, you should have answer what your interviewer has in her mind (No maters, wrong or right).

Physical standby database configuration (Oracle 9i)

Hi, this is my first blog, I am little bit late on blogging. But, it is good to be late then never.

I decide to start with standby database configuration because lots of people still confuse for data guard. This configuration is for Oracle 9i, little bit old, but still this is very good for beginner to understand architecture of standby database and to familiar with terminology used.

I intentionally keep this very simple to maintain full concentrate on standby database configuration.

Let me first explain my requirements and technical details before starting configuration.

Scenario:- (This is for windows machine.)

I want to create physical standby database for one database named “PriDB” running on host machine named “PrimaryHost”. The database is in Archive Log mode with automatic archiving enable (If it is not in Archive Log mode then first you should place database in Archive Log mode).

I take another server where I want to host my standby database. The name of that machine is “StandbyHost”. I installed Oracle software on it without database.

In next step I have to take backup of my primary database “PriDB”. For that, first I execute following query to get information of data files of primary database “PriDB”.

Sql> SELECT NAME FROM V$DATAFILE;

C:\ORACLE\ORADATA\SSDB\SYSTEM01.DBF

C:\ORACLE\ORADATA\SSDB\UNDOTBS01.DBF

C:\ORACLE\ORADATA\SSDB\CWMLITE01.DBF

C:\ORACLE\ORADATA\SSDB\DRSYS01.DBF

C:\ORACLE\ORADATA\SSDB\EXAMPLE01.DBF

C:\ORACLE\ORADATA\SSDB\INDX01.DBF

C:\ORACLE\ORADATA\SSDB\ODM01.DBF

C:\ORACLE\ORADATA\SSDB\TOOLS01.DBF

C:\ORACLE\ORADATA\SSDB\USERS01.DBF

Now, To take backup, I shutdown (consistent shutdown) primary database “PriDB”, and take physical copy of all data files which is listed above and move it into the standby host (in my case it is “StandbyHost”.).

Note:- You can use RMAN to create standby database without stopping primary database, refer online manual for that

After copying databases to standby host, startup “PriDB” database to create physical standby control file. This control file later on used as control file for standby database.

Sql> Startup pfile=’c:\oracle\ora92\database\initPriDB.ora’

Sql> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'd:\StbCntrl.ctl’

    Once, standby control file created, move it to the standby host.

      Now, we have all required data files and control file for standby database, now it is require to configure tnsnames.ora, listener.ora 

and initialization parameters file  for both databases.

     To establish connection between primary database and standby database it is require to setup oracle net correctly. 

 At Primary Database site:-

       Verify your listener.ora is properly configure or not for SID “PriDB”. Add one entry of connection 

service for standby database in tnsnames.ora called it “StbyConn”. Don’t try to test the service for connection 

with standby database, as there is no standby database created yet. Just make an entry which is used later on.

Sample of Net config files at PrimaryHost site 

Listener.ora

LISTENER = 

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP) (HOST = PrimaryHost) (PORT = 1521))

      )

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:\oracle\ora92)

      (PROGRAM = extproc)

    )

    SID_DESC =

      (SID_NAME = PriDB)

      (ORACLE_HOME = C:\oracle\ora92)

    )

  )

TnsNames.ora

StbyConn =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP) (HOST = StandbyHost) (PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PriDB)

    )

  )

PriDb =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP) (HOST = PrimaryHost) (PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PriDB)

    )

  )

At Standby Database site:-

      As there is no listener.ora and tnsnames.ora on standby database site we have to create it manually. For this, 

I copy these files from primary database and modify it for standby database. First correctly configure listener.ora 

for the SID “PriDB” which is same as primary database SID. (In our case the standby and primary database name are same.)

Now add two entry of service name in tnsname.ora file. One service name is use to make connection with Primary Database, 

called “PriConn”. Another service name for standby database, called “StbyPriConn”. Keep these two files at standby host site, 

we need these files later on.

Sample of Net config files at StandbyHost site 

Listener.ora

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP) (HOST = StandbyHost) (PORT = 1521))

      )

      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC0))

      )

    )

  )

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = PLSExtProc)

      (ORACLE_HOME = C:\oracle\ora92)

      (PROGRAM = extproc)

    )

    SID_DESC =

      (SID_NAME = PriDB)

      (ORACLE_HOME = C:\oracle\ora92)

    )

  )

TnsNames.ora

PriConn =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP) (HOST = PrimaryHost) (PORT = 1521))

    )

   (CONNECT_DATA =

      (SERVICE_NAME = PriDB)

    )

  )

StbyPriConn =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP) (HOST = StandbyHost) (PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = PriDB)

    )

  )

    Now, copy initialization parameters file of primary database “PriDB” called “InitPriDB.ora” and put it in temp location, 

rename new file to “InitStbPriDB”, which is used for standby database. We have to make some changes in both initialization parameters files.

    It is not require to do changes in all initialization parameters, only some of parameters need to change. 

(1) Keep db_name identical for Primary database and Standby database.

(2) Specify "StandbyHost" as one of destination for Archive redo log files generated at Primary Database site by specifying ,

 Log_archive_dest_2='service= StbyConn MANDATORY reopen=120'

Here, “StbyConn” is service name for standby database which we configure earlier in TnsNames.ora file on PrimaryHost.

(3) Set Remote_archive_enable=send [or true] for PraimryHost and set Remote_archive_enable=receive [or true] for StandbyHost

Note:- Remote_archive_enable is deprecated in oracle 10g. 

(4) Background_dump_dest, User_dump_dest, Core_dump_dest should point to valid physical location in InitStbPriDB.ora

(5) Change control_files parameter in InitStbPriDB.ora by specifying 

 control_files='C:\oracle\oradata\PriDB\StbCntrl.ctl'

 Here, “StbCntrl.ctl” is standby control file which we create from primary database earlier.

(6) To configure FAL service we have to specify two parameters in IniStbPriDB.ora

  Fal_server= PriConn

  Fal_client= StbyPriConn

Here, PriConn  is for Primary Database and StbyPriConn is for Standby Database, which we configure in TnsNames.ora file at standbyHost earlier.

(7) Check Log_archive_dest_1 in InitStbPriDB.ora point to valid location or not. If not then make modification in it.

(8) standby_archive_dest='c:\oracle\oradata\RecArc', is used to specify location in “StandbyHost” where Archive redo log files of primary database 

are placed by Log Transport Service.

Below is listing of sample initialization parameter files for Primary Database and Standby Database.

InitPriDB.ora

*.background_dump_dest='C:\oracle\admin\ PriDB \bdump'

*.core_dump_dest='C:\oracle\admin\ PriDB \cdump'

*.control_files='C:\oracle\oradata\ PriDB \CONTROL01.CTL','C:\oracle\oradata\ PriDB \CONTROL02.CTL','C:\oracle\oradata\ PriDB \CONTROL03.CTL'

*.db_block_size=8192

*.db_cache_size=4194304

*.db_name='PriDB'

*.fast_start_mttr_target=300

*.instance_name='PriDB'

*.java_pool_size=33554432

*.job_queue_processes=10

*.large_pool_size=8388608

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=134217728

*.shared_pool_size=50331648

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.user_dump_dest='C:\oracle\admin\PriDB\udump'

*.log_archive_start=TRUE

*.Log_archive_dest_1='LOCATION=C:\oracle\oradata\PriDBArc MANDATORY'

*.Log_archive_dest_2='service= StbyConn MANDATORY reopen=120'

*.Log_archive_dest_state_1=enable

*.Log_archive_dest_state_2=enable

*.Remote_archive_enable=send

InitStbPriDB.ora

*.background_dump_dest='C:\oracle\admin\PriDB\bdump'

*.control_files='C:\oracle\oradata\PriDB\StbCntrl.ctl'

*.core_dump_dest='C:\oracle\admin\PriDB\cdump'

*.db_block_size=8192

*.db_cache_size=4194304

*.db_name='PriDB'

*.fast_start_mttr_target=300

*.instance_name='PriDB'

*.java_pool_size=33554432

*.job_queue_processes=10

*.large_pool_size=8388608

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=134217728

*.shared_pool_size=50331648

*.undo_management='AUTO'

*.undo_retention=10800

*.user_dump_dest='C:\oracle\admin\ PriDB \udump'

*.Fal_server= PriConn

*.Fal_client= StbyPriConn

*.log_archive_start=False

*.Remote_archive_enable=receive

*.standby_archive_dest='c:\oracle\oradata\RecArc'

Aahhh!!! Now we have all require files are ready to setup new standby database. We have “standbyHost” ready with Oracle software installed into it, we will create database into that. First place the Data files with same directory structure as it is for Primary database and place control files in location specified by control_files init parameter. Place “InitStbPriDB.ora” in <OracleHome>/Database/. Place listener.ora and TnsNames.ora in <OracleHome>/Network/Admin/

To create oracle instance/service for PriDB in StandbyHost, execute following command from command prompt.

C:/> oradim –NEW –SID PriDB –IntPwd PasswordForSys

This will create one instance / service and password file for the PriDB on standByHost. Now Standby Database setup is complete. Perform following steps to start working of Standby database architecture.

On Standby Database

Sql> Startup Nomount

Sql> Alter database mount standby database;

To place standby database in managed recovery mode execute following command (in this mode Log apply service apply Archive redo log file which are coming from Primary database)

Sql> Alter database recover managed standby database disconnect from session;

The inclusion of “disconnect from session” option in sql comand will start MRP (Managed Recovery Process) in background and return control after executing command.

On Primary Database

Sql>Shutdown immediate

Sql> startup pfile=’<OracleHome>/Database/InitPriDB.ora’;

To test these implementations create one table on Primary Database and insert data into it.

Sql> Create table TestStandby ( col1 number, col2 char(1000));

Sql> Insert into TestStandby values(1,’Pratik’);

Sql> Insert into TestStandby select * from TestStandby;

Sql>/

If Archive file is not generated then generate Archive redo log file manually using sql command and verify same archive log file is created or not on “StandbyHost” site (Archived log files are put on “StanbyHost” at location pointed by “standby_archive_dest” parameter, if this parameter is not specified then default location will be <OracleHome>/RDBMS).

Sql> Alter database Archive log current;

If Archived redo log file successfully transformed on “StandbyHost”, then to test whether Log Apply Service works properly or not we need to query table which we create on primary database.

To query on standby database we have to first stop Managed Recovery and open it in read only mode by executing any of following set of sql commands.

Sql> Alter database recover managed standby database cancel;

Sql> Alter database open read only;

OR

Sql> shutdown immediate

Sql> Startup Nomount

Sql> Alter database mount standby database;

Sql> Alter database open read only;

Once standby database open in "Read only" mode, we can query from table “TestStandby” which we created on Primary Database.

Note:- When Physical standby Database is open in Managed Recovery Mode at that time you can’t query from standby Database(In case of Oracle 11g, things are change now). To query or generate report from Standby Database you need to first open it in Read only mode.

I hope this help in clearing doubts without adding new one.