Oracle data migration performance issues [message #510803] |
Tue, 07 June 2011 21:22 |
|
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 #510810 is a reply to message #510806] |
Tue, 07 June 2011 22:44 |
|
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 #511560 is a reply to message #510803] |
Mon, 13 June 2011 22:10 |
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
|
|
|
|