Working with Oracle’s Time Model Data 2

A fairly long VBS script connects to the Oracle database, performs a lot of calculations on the data returned from the database, and then outputs the formatted result to a web page in Internet Explorer. The VBS script continues to control the web page once the page is built, automatically refreshing the web page after a specified number of seconds, and responding to button clicks on the web page. Easy, right? Because this is done using a VBS script, the client computer must be running on Windows, while the server may run Unix, Linux, or Windows (I suggest not running this script directly on the server, instead run it from another computer). The Show Detail button acts like a toggle to either show or hide the session details (on the yellow lines) that contributed to the system-wide statistic values (by default, the sessions must contribute to at least 10% of the total to be included in the session-level output). Clicking the Re-Query button causes the script to update the page with the latest statistic delta values prior to the automatic refresh timer expiring.

So, where is the code? Note that there may be bugs in the code – don’t step on them. Also, the code is mostly written in a very verbose syntax so that it is easy to follow along with the script logic.

There is no need to stop at this level – dig into the session-level wait events, enable a 10046 trace for sessions, set up the script to send an email to a user if the user consumes more than 10% of the server’s capacity, … and most important, have fun.

Thanks for the feedback regarding the second chapter that Randolf and I wrote. What is your opinion of the first chapter, “Understanding Performance Optimization Methods”? There were several significant changes made to both chapters between the time the alpha copy was captured and the final copy was submitted, so hopefully you were able to download the final version.

I downloaded it on 22th of December,
I could not see any mistake so far but I hope you submitted the right by that time one otherwise I have to read 100 pages again :)

I did not go through the first chapter yet because this was initially to take a look at the chapter and I could not stop reading. I will definitely write my opinions about the chapters both here and my blog.

The final version of the chapter was made available on the Apress website on January 4, at around 7 PM UTC time (I do not know the reason for the delay, the final proof copies were due a couple of weeks earlier). Prior to that, Apress only offered the version of the chapters as of roughly October 1, which is just after the Apress lead editor split our chapter into 2 chapters and did his best to shrink the length of the chapter. We rewrote several sections of the two chapters based on the lead editor’s comments, adding a bit more detail in a couple of the sections.

I am very happy to hear that you are finding that the chapter is helpful/useful.

I think I was already reading the Understanding Performance Optimization Methods :). I just downloaded the whole book and realized that the chapter name changed on top of the pages. It was labeled choosing a performance optimization method for the alpha download and I just looked at the top of the page while I wrote here :)

I think the chapter is very definitive and overall system tuning information gathering, plus how to go through a certain tracefiles was very educational for me.

While the example is very good, here are some ideas for expanding the example (someone else feel free to make the changes):
* In theory, it would be possible to read the tnsnames.ora file, extract the aliases, and create a drop-down list with all of the different databases that are defined – you could then use that list to select the database of interest to monitor.

* You could also write the retrieved statistics to a text file, Excel file, or database table for later analysis.

* I think that I also posted a VBS example where the computer speaks – if you find one session that is consuming 50% of the server capacity, you could have the script announce that USER1 is possibly suffering performance problems – it could be fun to listen to this for possibly 5 minutes.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: