Home » RDBMS Server » Performance Tuning » Data PERFORMANCE ISSUES (LINUX 5.3 Oracle 11gr2)
Data PERFORMANCE ISSUES [message #624919] Sun, 28 September 2014 12:10 Go to next message
sakamboh
Messages: 24
Registered: March 2013
Location: oman
Junior Member
Hi All,

I have a database running in my production environment having tables with 10 million records each. while retrieving reports its taking around 3 hours to retrieve them. I am not able to identify the reason.

I have took a full dump and imported in my TEST environment and executed same reports, it took 5 to 10 minutes maximum for retrieving same reports. (Both servers prod and test have same processor and memory)

Please advice what could be the reason and how I can achieve the same performance in my production environment.
Re: Data PERFORMANCE ISSUES [message #624920 is a reply to message #624919] Sun, 28 September 2014 12:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET SQL-TRACE=TRUE;

process resultant trace file with TKPROF to see which SQL is taking a long time to complete or that is consuming the most elapsed time.

After you identify the problematic SQL statements the provide details as stated below.

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Data PERFORMANCE ISSUES [message #624929 is a reply to message #624919] Sun, 28 September 2014 21:57 Go to previous message
lzfhope
Messages: 69
Registered: July 2006
Member
same objects do not mean the same query plan .
if ignore the difference between hareware and software(OS and oracle installation),then the most possible reason
is that:plan is different.
--------------------------------
As you imported data into the new environment,the statistics for data may greatly different from the old.

the preceding is just a guess! in fact,the trace files are most useful for your problem.
---
or ,you can use the advice from oem .
Previous Topic: How to tune a slow query
Next Topic: Private message
Goto Forum:
  


Current Time: Thu Mar 28 15:50:14 CDT 2024