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

sql_id CPU or Event Time (sec)
--------------- -------------------------- -----------
6jvkghxmqkx4n cell smart table scan 165126.00
6jvkghxmqkx4n CPU + CPU Wait 3104.00
6jvkghxmqkx4n resmgr:cpu quantum 49.00
6jvkghxmqkx4n DFS lock handle 48.00
6jvkghxmqkx4n latch free 24.00
6jvkghxmqkx4n os thread startup 11.00

During the test above, we captured the query completion times using dbms_utility.get_time and spooled the
output to a series of text files. If we examine these files, we see the following query completion time ranged from 431
seconds to 873 seconds:

[oracle@cm01dbm01 autodop]$ grep seconds *lst
autodop_1.lst:873.03 seconds
autodop_2.lst:853.1 seconds
autodop_3.lst:790.17 seconds
autodop_4.lst:431.05 seconds
autodop_5.lst:863.5 seconds
[oracle@cm01dbm01 autodop]$

Now we will implement Auto DOP so we can compare results. To implement Auto DOP, set
parallel_degree_policy to LIMITED and "_parallel_statement_queuing"=TRUE to enable both Auto DOP
functionality and parallel statement queuing.

■ setting parallel_degree_policy=AUTO will implement both of these features but also enable In-Memory
parallel execution, another 11gr2 parallel query optimization. on exadata, this feature can potentially disable smart
scans by bypassing direct read algorithms and performing buffered reads. to learn more about In-Memory parallel
execution on exadata, please see recipe 21-7.

Note

SYS @ edw1> alter system set parallel_degree_policy=LIMITED scope=both sid='*';
System altered.
SYS @ edw1> alter system set "_parallel_statement_queuing"=TRUE scope=both sid='*';
System altered.
SYS @ edw1>

After launching the same test case as above, Figure 21-2 displays a SQL monitoring window during the test
execution.