SQL Profiler

From: Ina Schmitz <web_at_inalein.net>
Date: Fri, 6 May 2005 08:26:42 +0200
Message-ID: <d5f2n1$86n$03$1@news.t-online.com>

Hi all,

I send a series of queries with JDBC to my Oracle 10g database. For these queries, I like to write down the cpu time, the total execution time and the logical reads, for example. I would need the result in a way that I could create a table results(query, cpu, total_time, log_reads) for example to list the measuring results for each query. Then I would do some evaluation with these results, for example computing the average.

In MS SQL Server 2000 or 2005 there I have the "SQL Profiler" where I could automatically create a table with the selected values during tracing. Is there also such a simple possibility with Oracle 10g?

I created a trace file for my session and then formatted it by tkprof, but there I get results in the following form FOR EACH of my queries: SELECT *
FROM TEST
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.06 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 44 0.35 4.74 10618 10674 0 612
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 48 0.35 4.81 10618 10674 0 612

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation

-------  ---------------------------------------------------

    306 TABLE ACCESS FULL TEST(cr=5337 pr=5310 pw=0 time=2296984 us)

---

With these result, I don't have the chance to put for example the total cpu time for each query into a table in a simple way. So I think tkprof isn't the right tool for that what I would like to do!?

Do you have any idea how to do what I would like to do? Is there any tool for Oracle which does this? I would need a tool which is either freeware or has a trial version to download, because it is for my diploma thesis.

Would be glad if anybody could help me there!

Thanks in advance.
Cheers,
Ina Received on Fri May 06 2005 - 01:26:42 CDT