Home » RDBMS Server » Performance Tuning » Performance Issue on a specific Table only (Oracle 10G V3`)
icon5.gif  Performance Issue on a specific Table only [message #320612] Thu, 15 May 2008 11:57 Go to next message
DBAsal
Messages: 5
Registered: May 2008
Location: T,Dot
Junior Member
Hello All,

Maybe this was asked before but I am in a bind here. Sad

I have an issue with a select statement, it is taking way to long to fetch. I have run a full analyze on the schema, and a full analyze ont he table, and a full analyze on the indexes. It is still slow to select from it. it has roughly 225K records, some of which hold CLOB XML Values.

Now all the tables in this schema are running VERY FAST!
I am only experiencing this problem with this one table.

Now I have also checked the locks on this table, it isnt locked.
There are no active sessions on the databse except for mine.

I really could use a hand or 2, or 3 if you can offer.

Best Regards,
Re: Performance Issue on a specific Table only [message #320613 is a reply to message #320612] Thu, 15 May 2008 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which of the suggestions in URL above have you tried & what were the results?


post the EXPLAIN PLAN for the slow SQL.
Re: Performance Issue on a specific Table only [message #320616 is a reply to message #320613] Thu, 15 May 2008 12:13 Go to previous messageGo to next message
DBAsal
Messages: 5
Registered: May 2008
Location: T,Dot
Junior Member
Explain Plan below.
I forgot to mention that Select * from is taking a long time.

I have a mirror database very similar and doesnt take time at all.

THE SLOW DATABASE
SELECT STATEMENT, GOAL = ALL_ROWS 4250 224894 62745426
TABLE ACCESS FULL SDEDBA REF_ITEM 4250 224894 62745426


THE FAST DATABASE

SELECT STATEMENT, GOAL = ALL_ROWS 911 225886 26654548
TABLE ACCESS FULL SDEDBA REF_ITEM 911 225886 26654548


I will start to look into some of those FAQ in the link above.
Please Advise,
Regards,
Re: Performance Issue on a specific Table only [message #320617 is a reply to message #320612] Thu, 15 May 2008 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Performance Issue on a specific Table only [message #320639 is a reply to message #320617] Thu, 15 May 2008 13:45 Go to previous messageGo to next message
DBAsal
Messages: 5
Registered: May 2008
Location: T,Dot
Junior Member
If you know of a simple problem I am having, can you just hint me. That page is very very long and I don't have enough time as my bind I am in is closing on me.....

I would appreciate it

Regards,
Re: Performance Issue on a specific Table only [message #320641 is a reply to message #320639] Thu, 15 May 2008 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
That page is very very long and I don't have enough time as my bind I am in is closing on me.....

I don't think it would take the 2 hours you just wasted to not read it.

Regards
Michel
Re: Performance Issue on a specific Table only [message #320642 is a reply to message #320641] Thu, 15 May 2008 13:57 Go to previous messageGo to next message
DBAsal
Messages: 5
Registered: May 2008
Location: T,Dot
Junior Member
Okay, Thanks for the tip.

Merci Beaucoup

Ciao

[Updated on: Thu, 15 May 2008 13:58]

Report message to a moderator

Re: Performance Issue on a specific Table only [message #320643 is a reply to message #320612] Thu, 15 May 2008 14:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> Select * from is taking a long time.
at any time in the past were there lots more rows in this table which have been subsequently deleted?
Re: Performance Issue on a specific Table only [message #320840 is a reply to message #320643] Fri, 16 May 2008 07:31 Go to previous message
DBAsal
Messages: 5
Registered: May 2008
Location: T,Dot
Junior Member
Hello,

No, both databases have had pretty much the same things done to them. the fast environment actually has had more things deleted and inserted. I would say after my initial load of the data, maybe 1-2% have been changed or deleted in the environment that is going slow.

I have tried so many things up till now, and I have a little bit more to share, in this table I have 2 columns which hold XML values in a BLOB. I copied this table to a different schema and it still ran slow, but when I dropped the 2 columns with the XML values the table selected very very fast. I am keeping in mind that the other Environment is going fast and it also contains pretty much the same values, also with 2 columns that hold XML values. so there is something going on with the 2 XML columns(BLOB).

Anyone ever have this issue before?

Regards,
Previous Topic: High enq: TX - row lock contention
Next Topic: connect by prior is killing me again
Goto Forum:
  


Current Time: Sun Dec 11 04:04:42 CST 2016

Total time taken to generate the page: 0.05752 seconds