Have Geek Brain

Big performance difference between two Oracle database instances

I am working with 2 instances of Oracle database, name them one

and two

. two

runs on better hardware (hard disk, memory, CPU) than one

, and two

is one minor version per one

in terms of Oracle version (both versions are 11g). Both have the same table table_name

with exactly the same indices defined. I am uploading 500,000 identical lines in table_name

for both instances. Then I run on both instances:

delete from table_name;

This command takes 30 seconds to complete at one

and 40 minutes to complete at two

. Performing INSERT and UPDATE on the two tables has similar performance differences. Does anyone have any suggestions on what could have such a dramatic performance impact between the two databases?

+2


a source to share


4 answers


If you can keep track of the wait_class and event columns in the v $ session to delete the session, you will get an idea of ​​the reason for the delay. Generally, I would expect the complete DELETE table to be disk-related (indicating the wait class I / O, or perhaps the configuration). It must read data from the table (so it knows what to delete), update the data blocks and index blocks to delete records that generate many records for the UNDO tablespace and redo log.

In a production environment, base files can be spread across multiple drives (even SSDs). Dev / test environments could be all stuck on one device and have a lot of head movement on disk slowing things down. I could see that the SQL jump can be tenfold. Yours is worse.



If there is concurrent [wait_class of 'Concurrency'] activity on the table (eg inserting other sessions), you might get a lock conflict or the sessions are trying to pop the index.

+2


a source


I first compared the instance configuration - SELECT NAME, VALUE from V $ PARAMETER ORDER BY NAME and spool the results to text files for both instances and use the file compare tool to highlight the differences. You should investigate everything except the differences related to database name and file locations. An extreme case might be non-archive logging in one database and 5 archive destinations defined on another.



If you don't have access to the filesystem on the database node, find who does and whether they get the trace files and tkprof results when the session starts, ALTER SESSION SET sql_trace = true, and then do your deletes.This will call any recursive SQL from -for triggers on the table (which you cannot have), auditing, etc.

+3


a source


Please note: I am not dBA ...

The following is written in my office window:

In case of an emergency, the dba call on call:

  • Check plan
  • Run statistics
  • Shared buffer pool

The number 2 and / or 3 usually fixes queries that work in one database but not others, or that worked yesterday but not today ....

+1


a source


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

In particular:

  • You have links without foreign key referencing (reason # 1 of the deletion using timeouts , take a look at the script from AskTom ),
  • Do you have REMOVE TRIGGER INCLUDED?
  • Do you have any activity on instance two (if this table is constantly updating you might be blocked by other sessions).
+1


a source






More articles:


All Articles