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

parallel-enabled, full-scan operations running concurrently, use SQL monitoring to measure the impact of parallel
statement queuing, and compare the test case execution times with and without Auto DOP and parallel statement
queuing.
First, we will construct a test case that runs a full scan against a table with over 27 billion rows and a parallel
degree of DEFAULT :
SQL> select degree,num_rows from dba_tables
where owner='D14' and table_name='DWB_RTL_SLS_RETRN_LINE_ITEM';
DEGREE NUM_ROWS
------------------- ----------------
DEFAULT 27,193,740,703
SQL>
For this test, we will start with Auto DOP disabled and our parallel_degree_limit initialization parameter set to
CPU, which on an Exadata Quarter Rack means that our parallel degree limit will be capped at 96. The test performed
was executed using the following shell script and the lst21-09-autodoptest.sql script in Listing 21-9:
#!/bin/sh
for i in 1 2 3 4 5
do
nohup sqlplus d14/d14 @lst21-09-autodoptest.sql $i &
done
Listing 21-9. lst21-08-autodoptest.sql
set serveroutput on size 20000
variable n number
exec :n := dbms_utility.get_time;
spool autodop_&1..lst
select /* queue test 0 */ count(*) from DWB_RTL_SLS_RETRN_LINE_ITEM;
begin
dbms_output.put_line
( (round((dbms_utility.get_time - :n)/100,2)) || ' seconds' );
end;
/
spool off
exit
While executing five concurrent executions of our SQL statement, a SQL monitor report will be shown,
as depicted in Figure 21-1 .