| 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
|
|
|
|
| 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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
