Re: shared pool size question

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Thu, 10 Apr 2003 01:44:10 GMT
Message-ID: <K%3la.85$FC3.78@news02.roc.ny.frontiernet.net>

Gabriel,

Would would care to give example of what temporary work that would need to be done to cause a 4031 error? I had a similar issue in a dev box where the offending query producted a monstrous explain plan of AND EQUAL

     index scan
     index scan

AND EQUAL
    index scan
     index scan
.. repeated 10 times.
The query gave a 4031 error on an 8.1.6.3 database running on Solaris 8. The SPA was ~240M and I set the reserved pool size to ~80M (higher than normal). Gave up after increasing SPA till 240M ... since I realized that query was the problem and a hint resolved the issue. The hint being of choosing the right index (table analyzed with 10% statistics).

However, even after restarting the database and immediately trying the query gave a 4031 within minutes. All this pointing to the fact that one query can fragment the SPA, so much as to cause a 4031 within minutes?.

The table in question was ~600M big. and only one table was being used in the query. Nothing else was running in the database except that query (and nothing ran in between me restarting the db and running the query)!

Sorry, cannot provide exact detail since that was an experiment a developer was doing and he dropped the table, the next day.

Thanks,
Anurag

"Gabriel Gonzalez" <no-spam_at_no-spam.com> wrote in message news:TKadnZaI6KdgwgmjXTWcqw_at_giganews.com...
> One is a join of several tables that requires temporary work memory and the
> other is not (since it comes from a single table)
>
>
Received on Wed Apr 09 2003 - 20:44:10 CDT