V$sql_monitor for Oracle

Hi All,

Oracle 11 g has this awesome feature called Sql monitor. What it does it , It allows you to see stepwise execution of explain plan . You can check currently which statement in your explain plan is running. and what is the % of completion of that step. Amount of CPU used. What are the waits on .

The issue is access to this table is not there by default and you need special access to get this. Currently i do not have access to this in my office. We can use v$session, Active_session_history, V$sql_plan, Autotrace to get all the required information. But this view is kind of fun . You can do perfomrance tuning without this view . But this view makes it very very easy.

Below is the script for generating activ monitor 

Open sqlplus and type

@C:/Users/ADMIN/Desktop/sql_script.sql

set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool C:\Users\ADMIN\Desktop\sqlmon_active.html
select dbms_sqltune.report_sql_monitor(type=>'active') from dual;
spool off

How Sql statement is executed in Oracle

1. Shared pool contains 2 important things ( Locks and Result cache) . So when a user request for data first the result cache is checked to see if the data already exist. A simple case is previous user also requested for same data
2. If its not found then oracle tried to construct the resultset ( answer of your sql) . First it checks if the required table( data blocks) for creating the output exist in the buffer cache. Also it wont be able to find all the blocks in database buffer cache it has to read some  tables (data blocks) from the database
3. If  they exist it check the redo log to see if they are the current data blocks and no changes have been made. ( this what makes database  in sync with ACID properties)

4. If no changes have been made recently then it reads those blocks form Database buffer cache

Point to be noted -- When we are inserting some data and we say commit the data is inserted from the Redo log buffer

so you run some sql, this sql is going to do some operation like

o hashing

o sorting (order by, analytics, sort distinct, group by sort, etc...)

o etc - anything that needs "memory"

Oracle will allocate a workarea (a sort area, a hash area, whatever). The size of this workarea will be determined by Oracle based on the current workload on the system.

If this workarea (in your pga) fills up - and we are not done with the operation (the sort, the hash, whatever) we will swap the contents of that out to disk - a write to temp. Later, we'll read it back in. Temp works like "virtual memory" in a sense - we page your pga out to it and page it back in (it is not true virtual memory, that is just an analogy)

All the operations you mentioned above - group by, hash joins, analytic functions occur in the pga - but could definitely be swapped out to temp.

As for the large pool, we use that in parallel query to send messages back and forth, the parallel execute servers are in different processes - they cannot talk directly to eachother since they each have their own address space. Hence, one process will put some data into the large pool (a piece of SHARED memory that everyone can access) and another will read it from there. It is just a shared memory structure that everyone - every oracle process - can read/write.