 | | | ** temp table or permanent | ** temp table or permanent 2006-07-05 - By Mark Brinsmead
Back
You seem to be jumping through a lot of hoops to solve a fairly
straight-forward (sounding) problem.
Your first step should be to optimize / rewrite the query to perform less
I/O and or execute more quickly.
Your second step should be to increase UNDO / Rollback as needed, within
practical limits, of course.
After this, you might consider temporary tables (Global Temporary Table
recommended) etc. or
Materialized Views.
Of course, advice like this is easy to give when I know *nothing* of your
specific situation. ;-)
On 7/5/06, A Joshi <ajoshi977@(protected)> wrote:
>
> Hi,
> I had a big query which was running out of rollback so after some
> tuning i had to break it down in to two. from the first query i store the
> result in a temporary table and then use it for second part. it is still
> slow. i did not use permament table since that could involve
> rollback/redo. but still i will try to check it. i am also thinking of
> pl/sql table but if that always remains in memory then it could impact
> memory or sga. pl/sql table uses pga or OS memory but if paging swapping
> occurs then it would impact sga too. Will it always be faster than temp or
> permanent table? Can some one give feedback if you had similar experience.
> Thanks for help.
>
>
>
--
Cheers,
-- Mark Brinsmead
Staff DBA,
The Pythian Group
http://www.pythian.com/blogs
You seem to be jumping through a lot of hoops to solve a fairly straight
-forward (sounding) problem.<br><br>Your first step should be to optimize /
rewrite the query to perform less I/O and or execute more quickly.<br><br>
Your second step should be to increase UNDO / Rollback as needed, within
practical limits, of course.<br><br>After this, you might consider temporary
tables (Global Temporary Table recommended) etc. or <br>Materialized Views.
<br><br>Of course, advice like this is easy to give when I know *nothing* of
your specific situation. ;-)<br><br><br><div><span class="gmail_quote">On
7/5/06, <b class="gmail_sendername">A Joshi</b> <<a href="mailto:ajoshi977
@(protected)">
ajoshi977@(protected)</a>> wrote:</span><blockquote class="gmail_quote" style=
"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding
-left: 1ex;"><div><div>Hi,</div> <div> I had a big query which was
running out of rollback so after some tuning i had to break it down in to two.
from the first query i store the result in a temporary table and then use it
for second part. it is still slow. i did not use permament table
since that could involve rollback/redo. but still i will try to check it.
i am also thinking of pl/sql table but if that always remains in memory then it
could impact memory or sga. pl/sql table uses pga or OS memory but if paging
swapping occurs then it would impact sga too. Will it always be faster than
temp or permanent table? Can some one give feedback if you had similar
experience. Thanks for help.
</div></div><div><span class="ad"><p>
</p><br></span></div></blockquote></div><br><br>-- <br>Cheers,<br>-- Mark
Brinsmead<br> Staff DBA,<br> The Pythian Group<br> 
; <a href="http://www.pythian.com/blogs">http://www.pythian.com/blogs</a>
|
|
|