Home » RDBMS Server » Performance Tuning » How to monitor query
How to monitor query [message #128006] Thu, 14 July 2005 09:44 Go to next message
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
Re: How to monitor query [message #128043 is a reply to message #128006] Thu, 14 July 2005 16:04 Go to previous messageGo to next message
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.
Re: How to monitor query [message #128082 is a reply to message #128043] Thu, 14 July 2005 22:20 Go to previous message
Hina
Messages: 51
Registered: April 2004
Member
Thanks a lot.
Previous Topic: reversed indexes
Next Topic: /*+ RULE */ hint in SQL Queries
Goto Forum:
  


Current Time: Mon Feb 06 13:37:11 CST 2023