Hi Oracle community,
I got an Oracle XE 11g database with APEX that worked like a charm for years. A few days ago the server crashed due to a problem with the air condition and shut down due to a heat problem. After restarting the server the database restartet without problems and our users started working and complained about very bad performance.
In fact there is just one page table that got extremely slow when making a query to it with an interactive report. A lot of other pages are also slow because they use views linked to this table. An interactive report query against that table took 1Second before the crash and it takes about 30seconds now (just select * from badtable without any joins).
I tried to rebuild all related indexes (didn't change the speed)
I deleted indexes (didn't change the speed)
i used dbms_stats.gather_table_stats on the table with SQL Developer (took a couple of seconds) and afterwards the interactive (wwith select * from badtable) was much 10 times faster. Looked good.
I rebuild the Indexes again and suddenly the interactive report was slow again like before
tried to use dbms_stats.gather_table_stats again and that time it came to no result at all (the action tool 20minutes and then I did cancel the operation, a lot of I/O going during this action and also doesn't stop now having a lot of I/O action going on since the gather_table_stats job)
I made a backup of the table, when doing a select * from badtable_backup everything is working great and fast
when I make a select * from badtable where rowum <10000 then everythin ist working fast, when making a select * from badtable where rownum < 30000 then it's getting slow.
select * from badtable where ID = xxx also fast
So I am quite sure there is some kind of problem somewhere with a row at that table, or with the memory where it is stored. Since I am no admin but an developer I really got stuck with that - can you please help to debug the problem and help me in finding a solution?
Thanks a lot for your help!
Jürgen