Disk/CPU/Memory Usage

[sramdram]

Is there a way to see how a query performs with respect to IO calls made / Cache hits / Memory Used / Network Wait

I understand that the Web Admin Interface has a graphical component that does something similar. I am looking for an accurate profile of resource usage by a query

[JimKnicely]

Have you looked at profiling?

Code: Select all

dbadmin=> profile select * from dual;
NOTICE 4788:  Statement is being profiled
HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273716597 and statement_id=9;
NOTICE 3557:  Initiator memory for query: [on pool sysquery: 4103 KB, minimum: 4103 KB]
NOTICE 5077:  Total memory required by query: [4103 KB]
 dummy 
-------
 X
(1 row)

Code: Select all

dbadmin=>  Select distinct counter_name from v_monitor.execution_engine_profiles where transaction_id=45035996273716597 and statement_id=9 order by 1;
       counter_name       
--------------------------
 bytes read from cache
 bytes read from disk
 clock time (us)
 end time
 estimated rows produced
 execution time (us)
 file handles
 input queue wait (us)
 memory allocated (bytes)
 memory reserved (bytes)
 output queue wait (us)
 request wait (us)
 response wait (us)
 rle rows produced
 rows processed
 rows produced
 rows pruned by valindex
 rows to process
 start time
(19 rows)

The Admin. Guide discuses it in detail!