OraFAQ Forum: Performance Tuning » How to monitor query

Home » RDBMS Server » Performance Tuning » How to monitor query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to monitor query [message #128006]

Thu, 14 July 2005 09:44

Hina
Messages: 51
Registered: April 2004

Member

How to monitor a query, or queries those taking enough time, and
more resources e.g. cpu

Regards
Hina

Report message to a moderator

Re: How to monitor query [message #128043 is a reply to message #128006]

Thu, 14 July 2005 16:04

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

In Ora10g use SQL Tuning analyser to tune up or
Create a trace and then run tkprof.
This is what I do:
You could also just pull the session+id for another session and then to run the statements from my trigger for it.
1. creat table to log the user... and to check if your trigger actualy works.
2. create logon trigger to insert into this table and to alter the current session
3. start process you want to trace with the user login you have into the trigger.
4. check the trace file - usually like:
cd /oracle_home/admin/database_name/udump
5.from the same directory log into SQLPLUS with sys accout rights and run:
tkprof FILE_NAME.trc file_name_you want_for_your_report.txt explain=test/test table=sys.plan_table sys=no waits=yes
5. You'll have in the same directory your file to read.
the information you can get looks like that:
SELECT VERSION
FROM
SRD_PRODUCT_VERSIONS WHERE PRODUCT = 'RS'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 7 0 1

Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 67

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL SRD_PRODUCT_VERSIONS (cr=7 pr=0 pw=0 time=134 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************

Pay attention also to the bottom summary.
Be careful to have enough space in the folder or the 2 files will not be created and you will NOT receive an error message.

Hope this will help.

Report message to a moderator

Re: How to monitor query [message #128082 is a reply to message #128043]

Thu, 14 July 2005 22:20

Hina
Messages: 51
Registered: April 2004

Member

Thanks a lot.

Report message to a moderator

Previous Topic:	reversed indexes
Next Topic:	/+ RULE / hint in SQL Queries

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 09 00:17:50 CST 2025