Post-Install Database Tasks - Oracle Exadata Recipes: A Problem-Solution Approach - page 604

Figure 21-2. SQL monitoring with parallel statement queuing

The SQL monitor screen in Figure 21-2 shows us the following information:

• The left-most column in the top three SQL executions indicates that the parallel operations are

being queued by Auto DOP, as indicated by the clock graphic.

• The bottom two SQL statement executions were each allocated 96 parallel query servers.

•
Using the script in Listing 21-11 to display the statement's database time per session_state and/or event from
ASH, we still see that the queries spent the bulk of their time on cell smart table scan wait events, indicating Exadata
Smart Scan was being utilized. In this case, however, we also see 1,372 seconds of database time spent waiting on the
resmgr:pq queued wait event, an indication that parallel statement queuing was taking place.

None of the SQL executions suffered parallel server downgrade.

Listing 21-11. lst21-11-ash-bysqlid.sql
SQL> select sql_id,evt,count(*) dbt from
(select sql_id,sample_id,sample_time,session_state,
decode(session_state,'ON CPU','CPU + CPU Wait',event) evt
from gv$active_session_history
where sql_id = '&&sql_id'
and sample_time > sysdate-&&mins_ago/1440)
group by sql_id,evt
order by 3 desc
/
Enter value for sql_id: 6jvkghxmqkx4n

sql_id CPU or Event Time (sec)
--------------- ---------------------------- -----------
6jvkghxmqkx4n cell smart table scan 143010.00
6jvkghxmqkx4n CPU + CPU Wait 3241.00
6jvkghxmqkx4n resmgr:pq queued 1372.00
6jvkghxmqkx4n resmgr:cpu quantum 145.00