Realtime SQL Trace like MS SQL Server

[Thomas Hase]

Hi NG,

I'm looking for a possibility to show all SQL-Statements (testsystem)

in realtime. (Ora 9i R2 Enterprise).

In MSSQL Server 7.0 this tool called SQL Trace and in 2000 called
profiler. I need 4 mouse click's and the program runs.

I found in oracle9i a performance manager. But there I found only the
SQLs of top sessions.

I need the starttime, exec time and the sql statement in a view in
realtime.

I hope, this is possible in ora9i without big effort.

Can anyone show me the way?




Thanks in advance

Regards Thomas

[Niall Litchfield]

"Thomas Hase" <tohas@freenet.de> wrote in message
news:423e64d3.77396843@news.t-online.de... Hi NG, I'm looking for a possibility to show all SQL-Statements (testsystem) in realtime. (Ora 9i R2 Enterprise). In MSSQL Server 7.0 this tool called SQL Trace and in 2000 called profiler. I need 4 mouse click's and the program runs.

http://www.petefinnigan.com/ramblings/how_to_set_trace.htm

you will need to read the Metalink notes on the trace file format and
tkprof.


--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

[Thomas Hase]

On Sat, 19 Mar 2005 18:22:47 -0000, "Niall Litchfield"
<niall.litchfield@dial.pipex.com> wrote:
"Thomas Hase" <tohas@freenet.de> wrote in messagenews:423e64d3.77396843@news.t-online.de... Hi NG, I'm looking for a possibility to show all SQL-Statements (testsystem) in realtime. (Ora 9i R2 Enterprise). In MSSQL Server 7.0 this tool called SQL Trace and in 2000 called profiler. I need 4 mouse click's and the program runs.http://www.petefinnigan.com/ramblings/how_to_set_trace.htmyou will need to read the Metalink notes on the trace file format andtkprof.


thanks for the link, I know this tool, but I would like to see the
sql-cmd's in realtime. In past, I used tkprof on instance level.
But it isn't realtime.

Regards Thomas

[Daniel Morgan]

Thomas Hase wrote:
On Sat, 19 Mar 2005 18:22:47 -0000, "Niall Litchfield" <niall.litchfield@dial.pipex.com> wrote:"Thomas Hase" <tohas@freenet.de> wrote in messagenews:423e64d3.77396843@news.t-online.de...Hi NG,I'm looking for a possibility to show all SQL-Statements (testsystem)in realtime. (Ora 9i R2 Enterprise).In MSSQL Server 7.0 this tool called SQL Trace and in 2000 calledprofiler. I need 4 mouse click's and the program runs.http://www.petefinnigan.com/ramblings/how_to_set_trace.htmyou will need to read the Metalink notes on the trace file format andtkprof. thanks for the link, I know this tool, but I would like to see the sql-cmd's in realtime. In past, I used tkprof on instance level. But it isn't realtime. Regards Thomas

Real time there are a number of ways. One of the best is with OEM.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Thomas Hase]

On Sat, 19 Mar 2005 12:05:39 -0800, DA Morgan
<damorgan@x.washington.edu> wrote:
Thomas Hase wrote: On Sat, 19 Mar 2005 18:22:47 -0000, "Niall Litchfield" <niall.litchfield@dial.pipex.com> wrote:"Thomas Hase" <tohas@freenet.de> wrote in messagenews:423e64d3.77396843@news.t-online.de...>Hi NG,>>I'm looking for a possibility to show all SQL-Statements (testsystem)>>in realtime. (Ora 9i R2 Enterprise).>>In MSSQL Server 7.0 this tool called SQL Trace and in 2000 called>profiler. I need 4 mouse click's and the program runs.http://www.petefinnigan.com/ramblings/how_to_set_trace.htmyou will need to read the Metalink notes on the trace file format andtkprof. thanks for the link, I know this tool, but I would like to see the sql-cmd's in realtime. In past, I used tkprof on instance level. But it isn't realtime. Regards ThomasReal time there are a number of ways. One of the best is with OEM.


Please, can you specify, wich button in OEM (Perfmon?) did you mean?

[Daniel Morgan]

Thomas Hase wrote: On Sat, 19 Mar 2005 12:05:39 -0800, DA Morgan <damorgan@x.washington.edu> wrote:Thomas Hase wrote:On Sat, 19 Mar 2005 18:22:47 -0000, "Niall Litchfield"<niall.litchfield@dial.pipex.com> wrote:>"Thomas Hase" <tohas@freenet.de> wrote in message>news:423e64d3.77396843@news.t-online.de...>>>>Hi NG,>>>>I'm looking for a possibility to show all SQL-Statements (testsystem)>>>>in realtime. (Ora 9i R2 Enterprise).>>>>In MSSQL Server 7.0 this tool called SQL Trace and in 2000 called>>profiler. I need 4 mouse click's and the program runs.>>http://www.petefinnigan.com/ramblings/how_to_set_trace.htm>>you will need to read the Metalink notes on the trace file format and>tkprof.thanks for the link, I know this tool, but I would like to see thesql-cmd's in realtime. In past, I used tkprof on instance level.But it isn't realtime.Regards ThomasReal time there are a number of ways. One of the best is with OEM. Please, can you specify, wich button in OEM (Perfmon?) did you mean?

Not from where I am right now, teaching a RAC class, but hopefully
someone else can provide you with navigation that looks at
v_$session_longops.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

[Niall Litchfield]

"Thomas Hase" <tohas@freenet.de> wrote in message
news:423f80a0.84514500@news.t-online.de... thanks for the link, I know this tool, but I would like to see the sql-cmd's in realtime. In past, I used tkprof on instance level. But it isn't realtime.

realtime monitoring can be achieved through the v$sql or v$sqlarea views.
Mark Powell posted a good explanation of exactly this to a remarkably
similar question on c.d.o.server in the last couple of days. However, doing
so regularly is a bad idea on a system that is doing any real work. I'm
interested in what the business need is that you are attempting to address
by monitoring all SQL in realtime, it would seem likely that there are other
ways of doing it.

As an aside the sql profiler tool that you referred to originally also puts
a significant strain on an MSSQL system, if you are doing that regularly it
might be better to look at server side tracing.


--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

[Thomas Hase]

On Mon, 21 Mar 2005 06:30:57 -0000, "Niall Litchfield"
<niall.litchfield@dial.pipex.com> wrote:
"Thomas Hase" <tohas@freenet.de> wrote in messagenews:423f80a0.84514500@news.t-online.de... thanks for the link, I know this tool, but I would like to see the sql-cmd's in realtime. In past, I used tkprof on instance level. But it isn't realtime.realtime monitoring can be achieved through the v$sql or v$sqlarea views.

I will check this
Mark Powell posted a good explanation of exactly this to a remarkablysimilar question on c.d.o.server in the last couple of days.

Is there a link to this topic?
However, doingso regularly is a bad idea on a system

I want to use this feature in a test environment
(show my first post)
because realtime tracing increase the performance in all dbms.
that is doing any real work. I'minterested in what the business need is that you are attempting to addressby monitoring all SQL in realtime, it would seem likely that there are otherways of doing it.

I have to port an access2k-mssql2k (ODBC) app to access2k-ora9i
(ODBC).
The app is not 100% client server. There are some large browsingforms.
If I open an nativ access table with 100.000DS MSSQL-ODBC is 10 time
faster as ora (the ms driver creates automaticly stored procs for
msssql). Its a known problem, that oracle odbc drivers are slow.

I want to see the start und stop time of sql execution.
Currently I use session trace with tkprof, but it is circuitous and I
cant see start and stop time.
As an aside the sql profiler tool that you referred to originally also putsa significant strain on an MSSQL system, if you are doing that regularly itmight be better to look at server side tracing.

Thanks for this informations

[Daniel Morgan]

Thomas Hase wrote:
Mark Powell posted a good explanation of exactly this to a remarkablysimilar question on c.d.o.server in the last couple of days. Is there a link to this topic?

www.google.com
click on groups
However, doingso regularly is a bad idea on a system I want to use this feature in a test environment (show my first post) because realtime tracing increase the performance in all dbms.

On its face your statement is not valid. Tracing always adds a load.
The question is rather one of tuning increasing performance and that
is best accomplished after the fact ... not realtime.

Unless I am misunderstanding your intent.
that is doing any real work. I'minterested in what the business need is that you are attempting to addressby monitoring all SQL in realtime, it would seem likely that there are otherways of doing it. I have to port an access2k-mssql2k (ODBC) app to access2k-ora9i (ODBC). The app is not 100% client server. There are some large browsingforms. If I open an nativ access table with 100.000DS MSSQL-ODBC is 10 time faster as ora (the ms driver creates automaticly stored procs for msssql). Its a known problem, that oracle odbc drivers are slow.

Also a known problem that MS Access is a dog. Almost any other front-end
would be superior and far more compliant with Sarbanes-Oxley if that is
a concern.

HTH
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)