This morning a colleague of mine noticed an alert from our plan change monitor and prevented any impact on the users by forcing a SQL’s plan to its normal fast plan before the users could be affected. I want to share the monitor script and describe how we use its output.
I know this is long but I want to paste the SQL text of the monitor script here. Review my comments on each step. (more...)
I mentioned this class earlier in a blog post but I wanted to remind people who read this blog that the class is starting again on Wednesday. Here is the URL for the class: link
The class is completely free and taught at a very high level of quality.
It teaches computer science concepts that apply in any programming language but also teaches Python programming.
It is valuable information in the increasingly computer oriented world (more...)
Development asked me to look at ways to cut the run time of a series of PeopleSoft payroll batch jobs so I took an AWR report of the entire 4 hour period. Based on the waits, the percentage of the elapsed time spent using I/O and the buffer pool advisory I chose to double the size of the buffer cache. But, this added memory did not improve the run time of the batch jobs. Maybe the affected (more...)
We keep 6 weeks of history in the AWR on our databases, but I want to capture some information for long-term trending. What I really want to do is capture some metrics and put them in some database tables to use to generate reports, but I have not had time to build the scripts to do that. So, instead I built a simple set of scripts to capture an AWR for the previous month. Since we have 6 (more...)
I applied the current July patch sets to a 11.2 and a 12.1 test database. Now I have a 184.108.40.206.7 and a 220.127.116.11.4 test database. It is helpful to have test databases that are on the most current patch sets and releases. If I see unexpected behavior on some other database I can try the same thing on the patched test databases to see if some (more...)
I registered myself for Oracle OpenWorld and I have my hotel reserved and my flights ticketed.
I think it has been over 12 years – probably more like 15 years – since I went to OpenWorld. I went at least once between December 1994 and November 2003 when I still lived in Florida and was working on Oracle databases. But since I moved from Florida I do not believe that I have been to the conference. (more...)
I just finished the last program for a computer science class on edX and I urge you to try it.
I took this class:
MITx: 6.00.1x Introduction to Computer Science and Programming Using Python
I was more interested in how MIT taught the class than in the material itself because I already know the subjects covered.
The class taught the basics of programming – expressions, variables, loops, if statements, and functions.
It also had a large focus on bisection or binary (more...)
I’m still working on resolving the issues caused by bug 13914613.
Oracle support recommended that we apply a parameter change to resolve the issue but that change requires us to bounce the database and I was looking for a resolution that does not need a bounce. The bug caused very bad shared pool latch waits when the automatic memory management feature of our 18.104.22.168 database expanded the shared pool. Oracle support recommending setting _enable_shared_pool_durations=false (more...)
I hacked together a query today that shows the overall I/O performance that a database is experiencing.
The output looks like this:
End snapshot time number of IOs ave IO time (ms) ave IO size (bytes)
------------------- ------------- ---------------- -------------------
2015-06-15 15:00:59 359254 20 711636
2015-06-15 16:00:59 805884 16 793033
2015-06-15 17:00:13 516576 13 472478
2015-06-15 18:00:27 471098 6 123565
2015-06-15 19:00:41 201820 9 294858
2015-06-15 20:00:55 117887 5 158778
2015-06-15 21:00:09 85629 1 79129
Oracle support says we have hit bug 13914613. Here is what our wait events looked like in an AWR report:
Top 5 Timed Foreground Events
||Avg wait (ms)
||% DB time
|latch: shared pool
|latch: row cache objects
|db file sequential read
|library cache: mutex X