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

Figure 21-1. SQL monitoring without parallel statement queuing

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

• Each of the five executions of the SQL statement is actively running and not queued.

• The bottom-most session is running with 96 parallel query slaves and without any parallel

degree downgrade.

• The first four queries were all downgraded by Oracle; in other words, Oracle determined that

there were not enough system-wide parallel query slaves available and adjusted the degree
of parallelism accordingly, and then it executed the SQL statements with this lower degree of
parallelism.

• The total number of parallel query slaves is 256, which is computed based on

( parallel_max_servers) * ( parallel_server_instances) . On an Exadata Quarter Rack,
parallel_max_servers is set to 128 by default.
With the sql_id listed in Figure 21-1 , use the script in Listing 21-10 to display the statement's database time per
session_state and/or event from Oracle's Active Session History (ASH). The script shows that the queries spent the
bulk of their time on cell smart table scan wait events, indicating Exadata Smart Scan was being utilized. Note that
there is no indication of parallel statement queuing:

Listing 21-10. lst21-10-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