Elapsed Time Calculation using the DBA_HIST_SQL_STAT
The query (Insert into select ) is using Parallelism in 3 node RAC and it is called inside a procedure .After the Execution when i query the DBA_HIST_SQLSTAT to view the Elapsed Time Delta , the value which i got for this single query is more than the Total procedure execution Time. How to calculate the exact elapsed time for the statement if the query is using parallelism across multiple nodes. The ROWS_PROCESSED_DELTA column sometime i am getting values and other time it is zero in DBA_HIST_SQLSTAT.
Also the CPU_TIME and Elapsed Time for the same query in v$sqlstats differs from the DBA_HIST_SQLSTAT data?
The db version is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit the server has total 32 CPUS .Its totally confusing.
Could any body please answer the below questions.
1. How to calculate elapsed time delta in DBA_HIST_SQLSTAT in case a query is using parallelism and running across multiple Instances.
2. Are the values in DBA_HIST_SQLSTAT is reliable ? (or there are any bugs in the version 10.2.0.4.0)
Thanks.