|
Ann, Let me tell you a story: some years ago the situation was rather hot in a big European bank where I was consulting because a report that they were supposed to run daily had been failing every night for a week for lack of temporary space. The DBA had added more space several times, it failed each time, only later in the night. Temporary storage had reached absolutely ridiculous levels - the temporary tablespace alone was bigger than many a database. They were supposed to send the report at the SEC in New-York and they were afraid they might lose their banking license in the US, so there was much at stake. You know how it was fixed? Not by adding still more temporary space, but by rewriting the query in a sounder way. There was a hash join of death somewhere where it should at been a nested loop or something of that kind. Rethinking the query (no, no hints) made it incredibly faster - without any need for much temporary space. You also mention a high open cursor count. All of this points to a poorly written application, and whatever you can do can only, at best, postpone the day of reckoning. Don't fix symptoms, fix root causes. You can "need" far more temporary storage than you have data if you write your application badly enough. HTH, --
Stephane Faroult RoughSea Ltd Coming speaking engagements: Beijing, Shanghai, Hong-Kong and Singapore. Shastry(DBA) wrote: Hello Gurus, -- //www.freelists.org/webpage/oracle-l |
