Re: Polling SQL Queries

From: Sybrand Bakker <gooiditweg_at_sybrandb.nospam.demon.nl>
Date: Sat, 13 Sep 2003 10:04:41 +0200
Message-ID: <ppj5mv8q2ulrsvn6t05bt6kfjlbaokfhe7@4ax.com>

On 12 Sep 2003 22:26:31 -0700, kjdama_at_yahoo.com (KJD) wrote:

>I want to write a shell script as such
>
> sqlpeek dbname polling_interval max_interval
>
>where polling_interval and max_intefrval are a # of seconds.
>
>The script would monitor dbname by connecting to it every
>polling_interval
>seconds for a maximum of max_interval seconds. The goal is to
>generate a report at the end to show which SQL queries appeared the
>most times.
>
>My question is which view can show the active SQL queries being run by
>the server at the time the script is polling. I have looked at V$SQL
>and V$OPEN_CURSOR and they do not seem to be showing the current
>active SQL queries.
>
>I think V$SQL shows all the parsed/cahced SQL and V$OPEN_CURSOR is
>always showing the same 155 rows.

v$sqlarea captures the number of executions. The problem with your approach is that tools like you propose to develop usually impose a significant overhead on the database. You would also better use statspack as it is already capable of tracking sql.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Sep 13 2003 - 03:04:41 CDT