Home » RDBMS Server » Performance Tuning » Oracle data migration performance issues (10.2.0.3)
Oracle data migration performance issues [message #510803] Tue, 07 June 2011 21:22 Go to next message
shashikantkale
Messages: 2
Registered: June 2011
Junior Member
Hi,

We have a data migration scripts written for oracle. Data is not huge but we are observing that the migration is faster in the development labs but is 5x slower in the production site.

The development Oracle setup is on Windows and Production setup on Solaris.

I have attached the AWR generated for a period where migration was run for 3 hours and stopped due to slow performance.

Here is my initial analysis.
1) The first timed events is the DB CPU. Hence I feel the migration scripts can be modified to run in parallel so that they can finish faster. However here the question arises why it should run faster in development env if this is an issue.
2) I tried increasing the
a.	large_pool_size  set to 512M
b.	sga_max_size  set to 8G
c.	sga_target  set to 8G
from 0, 4G and 4G respectively.

I am a newbie to Oracle tuning. Could somebody please suggest the directions I should start my analysis.

I have attached the AWR and below are the etc/system contents for solaris settings.
* Begin MDD root info (do not edit)
rootdev:/pseudo/md@0:0,1,blk
* End MDD root info (do not edit)
set noexec_user_stack=1
set noexec_user_stack_log=1
* IBMdpo vpath_START (do not remove)
* default SCSI timeout is 60 seconds
* uncomment to change SCSI timeout * set sd:sd_io_time=0x1e
forceload: drv/vpathdd
* IBMdpo vpath_END   (do not remove)

set noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=1024
set semsys:seminfo_semmsl=256
set semsys:seminfo_semvmx=32767
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10


P.S. The awr report is renamed to .txt from .html to be able to upload the file.

Kindly help.
Shashi
Re: Oracle data migration performance issues [message #510806 is a reply to message #510803] Tue, 07 June 2011 22:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Ready, FIRE, Aim!

If you do not know why performance is slow, you can only guess what needs to be fixed.

>Data is not huge
Please quantify both.
>but we are observing that the migration is faster in the development labs
OK, I give up. I know that some bird migrate. Please clarify what happens to the data.
>but is 5x slower in the production site.
Where is time being spent on Production?

Re: Oracle data migration performance issues [message #510810 is a reply to message #510806] Tue, 07 June 2011 22:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN PLAN for code below
DECLARE
    l_no    VARCHAR2(1);
    l_error VARCHAR2(1000) := NULL;
BEGIN
    DELETE FROM migratable_cases
    WHERE  case_id IN (SELECT aers_case_id
                       FROM   migration_status_log
                       WHERE  status = 'Y');

    COMMIT;

    l_no := aers_arisg_case_migration.fn_migrate_case_data;
EXCEPTION
    WHEN OTHERS THEN
      l_error := sqlerrm;

      dbms_output.Put_line('Error at step: '
                           ||aers_arisg_case_migration.g_step_no
                           ||' for case - '
                           ||aers_arisg_case_migration.g_source_case_id);

      dbms_output.Put_line(l_error);
END;  


The AWR report came from "PSSDEV3".
Is this report REALLY, Really, really from Production DB?

[Updated on: Tue, 07 June 2011 22:56]

Report message to a moderator

Re: Oracle data migration performance issues [message #510987 is a reply to message #510810] Wed, 08 June 2011 20:42 Go to previous messageGo to next message
shashikantkale
Messages: 2
Registered: June 2011
Junior Member
Hi,

I will get the explain plan details for the migration code. By production DB I mean, the customer DB and by Dev DB I mean the migration development DB. The customer DB is quite a powerful box with 64 cores and at least 32 GB of RAM. There are no several processes running on this box. The total DB CPU hits just 5% however in the total time consumed by the migration activity 99% is consumed by the migration code. I feel, the explain plan would certainly help and tell us if any indexes missing. I am baffled if indexes are an issue, they should create problem in both development and production environments (Please note DB stats gathering is done periodically).

Thanks and appreciate your help!
Shashi
Re: Oracle data migration performance issues [message #510990 is a reply to message #510987] Wed, 08 June 2011 22:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>l_no := aers_arisg_case_migration.fn_migrate_case_data;

post FORMATTED code for function above

Take the time to READ & absorb each of the threads in URL below

http://www.orafaq.com/forum/t/84315/136107/

[Updated on: Wed, 08 June 2011 22:38]

Report message to a moderator

Re: Oracle data migration performance issues [message #511560 is a reply to message #510803] Mon, 13 June 2011 22:10 Go to previous messageGo to next message
hkchital
Messages: 128
Registered: September 2008
Location: Singapore
Senior Member
A few observations :

1. Yes, only 1 CPU is being used, the process is CPU bound but serial. However, it is doing 8,352 logical i/os per second.

2. A sequence was updated 1,749,508 times in those three hours. (or it could be more than one sequence, but the total number of updates was 1.75million times). This accounted for 5.272million block gets !
Apparently, you have
a. Sequences being incremented at a high rate -- is the sequence being incremented whenever an error occurs ?
b. A very low CACHE value for the sequence(s).
(INSERT INTO AER_TEXT was executed 525,927 times.
INSERT INTO AER_INFM_AUTH was executed 275,375 times.
INSERT INTO DISTRIBUTION_OF_AER_INFO was executed 186,394 times.
All three SQLs increment the same sequence SEQ_RECORD_ID)

3. The SELECT MAX(EC.RECEIVE_DATE) and SELECT MIN(EC.RECEIVE_DATE statements had 200thousand executions each. Why ?
I would ask you to compare the Execution Plans and execution statistics (Buffer Gets per execution) for the top 6 SQLs from the "SQL ordered by Gets" sections in the AWR (excluding 0nm1vy3bgnp8n and 4m7m0t6fjcs5x)


Hemant K Chitale


Re: Oracle data migration performance issues [message #511660 is a reply to message #510803] Tue, 14 June 2011 07:51 Go to previous message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
It looks like you are using a single row inserts.

Row-by-Row = Slow-by-Slow

Rewrite your code with BULK INSERTs.

HTH
Previous Topic: merging 2 big tables
Next Topic: Tuning option selection (merged 2)
Goto Forum:
  


Current Time: Thu Mar 28 05:44:29 CDT 2024