OraFAQ Forum: Performance Tuning » Data PERFORMANCE ISSUES

Home » RDBMS Server » Performance Tuning » Data PERFORMANCE ISSUES (LINUX 5.3 Oracle 11gr2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data PERFORMANCE ISSUES [message #624919]

Sun, 28 September 2014 12:10

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.

Report message to a moderator

Re: Data PERFORMANCE ISSUES [message #624920 is a reply to message #624919]

Sun, 28 September 2014 12:28

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

Report message to a moderator

Re: Data PERFORMANCE ISSUES [message #624929 is a reply to message #624919]

Sun, 28 September 2014 21:57

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 .

Report message to a moderator

Previous Topic:	How to tune a slow query
Next Topic:	Private message

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 10 17:16:00 CDT 2025