Re: Polling SQL Queries

From: Jim Kennedy <kennedy-down_with_spammers_at_no_spam.comcast.net>
Date: Sat, 13 Sep 2003 06:50:44 GMT
Message-ID: <87z8b.430267$o%2.195628@sccrnsc02>

"KJD" <kjdama_at_yahoo.com> wrote in message news:8c907343.0309122126.4e7bb06b_at_posting.google.com...
> 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.

Use dbms_job and put the information in a package in the database. Then you don't ahve the connect, disconnect etc. It runs as long as the database is running. Also it is portable across platforms.

What do you mean by active?
Jim Received on Sat Sep 13 2003 - 01:50:44 CDT