Re: troubleshooting hangs & endless loops in Oracle

Looking for some long parses and other waits, hmmmmm

Just drop the underlying indexs on DBA_OBJECTS, DBA_HISTOGRAMS, DBA_TAB_COLUMNS, etc., flush the shared pool, and that should do it. You can always rebuild them later. In Oracle v7.3 there was a huge bug related to long parsing because there was an index missing on DBA_HISTOGRAMS. We either had to build the index manually or not collect column level statistics.

Sort waits, maybe set your sessions sort_area_size down low and do a cartesian join with an order by clause...that would do it.

Setting your redo_log_buffer parameter and your other SGA parameters low can cause lots of waits if you are able to generate some traffic on the database.

An old fun favorite wait of mine is to put some big columns on a row, insert several rows into a block, commit, then in session one update one row so that it's a massive row expansion but don't commit the change, repeat that using more sessions against more rows (in the same table segment block) each session updating a different row. Eventually you should have one session waiting for a block reorganize and/or an ITL wait.....at least that was the way things were up though Oracle 9. I haven't tried that out in years so I don't know if Oracle has resolved the issue. You can also just set the INITTRANS and MAXTRANS parameters to 1on an object in a non system managed tablespace, and then issue updates in multiple sessions working on rows in the same blocks to get some interesting waits.

Long disk waits, hell, every DBA is good a burying a database to get those.

Issue a full table (all rows) update against a table over and over again without committing, in a few other sessions issue some nice cartesian joins on the table. You should see lots of waits for CR block creations and block waits (can't remember all the wait events this will create right now).

Issue a long update, kill the session, then run a select count(unindexexed_column_name) from tab_name in another session. You should see some interesting waits related to session cleanup and rollback.

Keep inserting the same rows over and over again into the same table with the same index. You should generate some nice block split activity.

Undersize your undo tablespace and set your undo retention high, then issue lots of little medium sized updates. You should see some waits related to cleanup or undo space requests or something like that.

That's enough fun for now.

ciao
On 2/12/08, Roman Podshivalov <roman.podshivalov_at_gmail.com> wrote:
>
> I'm not sure about multiminute parsing but for inter process
> communication, parallel execution, sorts and CPU hog I use very simple
> statement:
>
> select /*+ PARALLEL (a, 5) PARALLEL (b, 10) PARALLEL (c, 15) */ * from
> all_objects a, all_objects b, all_objects c order by 1,2,3,4,5,6;
>
> Just make sure your temp space is adequate because it will be funny if
> during the live demo you will get out of temp error ;-) This statement
> should last for about 3-5 minutes depending on your CPU speed. If you need
> more you can scale it very easily.
>
> --romas
>
>
> On 2/12/08, Tanel Poder <tanel.poder.003_at_mail.ee> wrote:
> >
> > Hi,
> >
> > I'm looking for ways to hang/crash an Oracle process and/or get it into
> > an
> > endless loop (or very long loops like multiminute parsing etc).
> >
> > I plan to use those as case studies for my systematic troubleshooting
> > presentation at Hotsos Symposium this year (preferably versions 10.2 and
> > 11g), so any examples on how to create weird (performance) problems in
> > Oracle are welcome!
> >
> > Please send me the answer in private in order to not flood Oracle-L with
> > junk ;)
> > I'll blog the most interesting ones later on and give credits
> > accordingly.
> >
> > Thanks!
> >
> > --
> > Regards,
> > Tanel Poder
> > http://blog.tanelpoder.com
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>

--
http://www.freelists.org/webpage/oracle-l