RE: Steps for performance tuning/debugging
Date: Thu, 20 Jul 2023 21:38:02 -0400
Message-ID: <4435200e-ed95-58e4-83c8-8bfaf670a6d8_at_comcast.net>
Having dealt with many of these types of issues one of the biggest problems I ran into were Dynamic Views. These come from a number of places but the developer's notepad files are some of the worse sources. They are cut and pasted into the sql to produce a result set that then needs to be merged/joined into another. You can find them as :
Select a, b, c, g1,......
from (select something1, something2, something3,..........
from table1, table2, table3 where table1.column1 = table2.column3 and table3.column4 in (select .............) as r1 join (select ......... from ..... where .....) as r2 where r1.c1 = r2.c6 and r2,c5 not in (here we go again)
And so on. You end up burning through a ton of temp space, never mind execution time and very often end up with an explain plan that ignores just about every index on earth. Trying to simplify one of these and make them run in reasonable time is an extremely frustrating task as there is no one who can explain in a reasonable fashion what they are trying to do, never mind that the statement can be 100 or more lines long and many times are generated by third party tools (such as drag and drop ETL tools) or the cut and paste method where the original developer is no longer available.
Oh, don't try dissecting one of these yourself, the amount of time you spend (several weeks) and the frustration caused are not worth it. Simply alter the users profile so the statement will fail within an hour & watch how fast you do start getting answers or better yet the statements use ends.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 21 2023 - 03:38:02 CEST