|Subject||Re: Detecting disk operations|
> Is there a way to detect throught mon$ tables or trace or other way disk operations caused by query?Engine collects some performance stats counters internally. Trace, monitoring, ISC API - all query engine's
performance data. Therefore there is no "special hidden ways" to ask engine for something that is not
collected by the engine itself.
Trace is the most precious way to know given query execution statistics.
> I see only page_reads and page_writes, page_marks, page_fetches but how to distinguish which one:Page read is always happens when page buffer is missed. Page fetch is always happens when page buffer> 1. Page is read because missed in cache
is accessed by the engine.
Sort IO is directed into separate temporary files and it is nor cached by the engine, nor paged.> 2. Page is read/write beacause of big sort operation
Unfortunately, engine doesn't collect Sort statistics.
You could try to use OS utlities to look at performance stats at the file level, btw
GTT's data go thru the common page cache as all other database operations.> 3. Page is read/write from temp object bigger then memory buffer e.g big global temporary table, others?
Hope it helps,