Lutz Haake | Freelance Oracle Consultant

In analysis of SQL stats,  execution times are cumulated for all parallel servers. This makes it difficult to derive the correct execution times from the AWR.

Create some test data:

The test script:

 

Output:

 

Though the SQL runtime was 1,27s, the accumu­lated elapsed time in v$sqlstats is 17,07s, due to the fact that all parallel query slaves are cumulated.

Therefore, to get good estimates from AWR retro­s­pec­tively, one needs to know the parallel degree with which the SQL was executed. Unfort­u­nately, px_servers_executions gives us 32 instead of 16. (It can be even more misleading when hard parses are high.)

 

How to obtain the correct degree? One way is to look at v$sql_monitor.

There problem here is that v$sql_monitor does not contain much histo­rical data, so forensic analysis may be impossible.

 

Another way is to look at v$sql_plan’s other_xml column.

The other_xml column contains the info:

The attribute of interest here is “dop”. It can be extracted in this way:

so that the SQL can be written as

and for histo­rical SQL

which will give us the correct value:

 

Both mehods can only be estimates, because neither considers parallel degree downgrades.

 

A third way is to analyze the ASH. Parallel query slaves share the same sql_id, sql_exec_id and qc_session_id with their parallel query coordi­nator, which can be identified by having qc_session_id=null. The problem here is that, due to the granu­larity in the time domain, it works reasonably well only for long running queries.