Home » RDBMS Server » Performance Tuning » Query Running for a long time in Second Schema (Oracle 11G)
Query Running for a long time in Second Schema [message #552635] Fri, 27 April 2012 02:29 Go to next message
gan.chowdary
Messages: 10
Registered: February 2007
Location: India
Junior Member
/forum/fa/10102/0/Hi,

I have a Query(report) which is running in <5 mins in one Scheme, where as the same is running for a long time in second schema. I have identified that an Index is scanning for more than 2000 Millions of records in second Schema, but this is scanning only 440 Millions in First Schema and hence it is fast. I am expecting the same to be done in Second schema.

I have verified the following
All records in tables in 2 schemas are same.
All indexes are same
Analyzed the tables
Gathered Histogram on all the columns as per the first schema.

But now i still have the same problem, don't know what could be the problem. Can some once suggest me please....

Table_name Num_Rows Blocks
PRPSL_LST_T 586610 7159
PRPSL_WKFLW_ACTVTY_T 582990 4030
ITEM_CHR_VAL_T 513434010 4049020
ITEM_RGN_ASSN_T 8571220 137215

Also attached 2 screen shots of OEM Plans..

Report message to a moderator

Re: Query Running for a long time in Second Schema [message #552643 is a reply to message #552635] Fri, 27 April 2012 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Re: Query Running for a long time in Second Schema [message #552817 is a reply to message #552643] Sat, 28 April 2012 19:14 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Those numbers you see in Explain Plan are only estimates made by the optimizer, not real row counts. To look at this further, we will need Explain Plans from BOTH schemas and they MUST include the Predicate information displayed by DBMS_PLAN. If you follow the link posted above you will find instructions on running such an Exlain Plan.
Ross Leishman

Report message to a moderator

Previous Topic: Stale statistics for table
Next Topic: Gather statistics on SYS objects
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jan 26 14:29:46 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.