Major performance difference between two Oracle database instances

Question

I am working with two instances of an Oracle database, call them one and two. two is running on better hardware (hard disk, memory, CPU) than one, and two is one minor version behind one in terms of Oracle version (both are 11g). Both have the exact same table table_name with exactly the same indexes defined. I load 500,000 identical rows into table_name on both instances. I then run, on both instances:

delete from table_name;

This command takes 30 seconds to complete on one and 40 minutes to complete on two. Doing INSERTs and UPDATEs on the two tables has similar performance differences. Does anyone have any suggestions on what could have such a drastic impact on performance between the two databases?

Answer 1

I'd first compare the instance configurations - SELECT NAME, VALUE from V$PARAMETER ORDER BY NAME and spool the results into text files for both instances and use some file comparison tool to highlight differences. Anything other than differences due to database name and file locations should be investigated. An extreme case might be no archive logging on one database and 5 archive destinations defined on the other.

If you don't have access to the filesystem on the database host find someone who does and have them obtain the trace files and tkprof results from when you start a session, ALTER SESSION SET sql_trace=true, and then do your deletes. This will expose any recursive SQL due to triggers on the table (that you may not own), auditing, etc.

Answer 2

please note: i am not a dba...

I have the following written on my office window:

In case of emergency ask the on call dba to: 1) Check Plan 2) Run Stats 3) Flush Shared Buffer Pool

Number 2 and/or 3 normally fix queries which work in one database but not the other or which worked yesterday but not today....

Answer 3

If you can monitor the wait_class and event columns in v$session for the deleting session you'll get a clue as to the cause of the delay. Generally I'd expect a full table DELETE to be disk bound (a wait class indication I/O or maybe configuration). It has to read the data from the table (so it knows what to delete), update the data blocks and index blocks to remove the entries which generate a lot of entries for the UNDO tablespace and the redo log.

In a production environment, the underlying files may be spread over multiple disks (even SSD). Dev/test environments may have them all stuck on one device and have a lot of head movement on the disk slowing things down. I could see that jumping an SQL maybe tenfold. Yours is worse than that.

If there is concurrent activity on the table [wait_class of 'Concurrency'] (eg other sessions inserting) you may get locking contention or the sessions are both trying to hammer the index.

Answer 4

Hi jrdioko,

Something is obviously wrong in instance two. I suggest you take a look at these SO questions and their answers:

• Oracle: delete suddenly taking a long time
• oracle delete query taking too much time

In particular:

• Do you have unindexed foreign key references (reason #1 of delete taking a looong time -- look at this script from AskTom),
• Do you have any ON DELETE TRIGGER on the table ?
• Do you have any activity on instance two (if this table is continuously updated, you may be blocked by other sessions)