Hi Kelly
The best way to find the root of the problem is to see where the resources and calls
are using the most time. The only way to see un-skewed information is raw trace data.
Use a logon trigger like this:
create or replace trigger logon_trace
after logon on database
if user in ('YOUR_APP_USER') then
begin
execute immediate 'alter session set timed_statistics=true';
execute immediate 'alter session set max_dump_file_size=unlimited';
execute immediate 'alter session set events ''10046 trace name context forever, level 12'' ';
end if;
end;
/
Then you can either run tkprof on the trace file or read them in raw format.
You can also concatenate all tracefiles for a session.
Check for which event that needs (much) more time than on the database where it
runs fast. This needs the logon trigger on both machines.
If you want, you can send the trace files to me (zipped please).
Regards
Måns Axell
________________________________
Von: Todd Pepling [mailto:oracledba-ezmlmshield-x26768384.[Email address protected]
Gesendet: Di 7/3/2007 16:14
An: LazyDBA Discussion
Betreff: RE: what is wrong?
Could be any/all of the following:
*) optimizer stats due to tables being analyzed differently
*) init parameters
*) hardware (slower/older/fewer CPUs, slower disk)
*) swapping for virtual memory
*) RAID configuration (unlikely for READ-ONLY transactions)
*) segment fragmentation (HWM for tables could be different)
To isolate the cause, I'd check the host systems for differences (i.e. CPUs, RAM, disk RAID config, etc.). If there are no differences, I'd check for differences with loads on the host systems (i.e. number of processes, disk shared with other systems, etc). As more of a last resort, you could clone the DB with good performance to the machine with poor performance, including the init parameters. That'd elliminate DB differences (other than typically defaulted values like cpu_count and asynch_io).
Todd Pepling
[Email address protected]
724-766-3492
________________________________
From: Kelly Z. Yi [mailto:oracledba-ezmlmshield-x83060021.[Email address protected]
Sent: Mon 7/2/2007 14:00
To: LazyDBA Discussion
Subject: what is wrong?
I have an application installed in two different database instances.The
databases both configured the same. But the application run in one
database very fast ( 1 min) and the other takes 25 mins to run.
Does anyone know what is possible reason?
Thanks,
Kelly Z. Yi
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com <http://jobs.lazydba.com/>
To Subscribe : http://www.LazyDBA.com <http://www.lazydba.com/>
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Oracle LazyDBA home page