How to properly capture performance result in oracle?

Question

Oracle

Good day, the task is to take performance readings in Oracle 11.2.0 g when running the script, there is an Enterprise Manager.
There are two sql scripts, you need to run the first one and take readings: how did it affect CPU RAM etc. Run the second sql script and also take readings and compare with each other.
Through Shanshot in EM, it seems to me that I will not get the same result.
ASH report will give the correct data?

Answer 1

You can view query activity statistics in a beautiful way using the package: DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id => sql_id, session_id =>sid, type=>'ACTIVE')

Answer 2

There is an AWR report in Oracle. It's easy to get it in SQL/Developer.
view -DBA (to display the DBA panel)
DBA-perfomance-AWR
There is also a standalone Ashview program with which you can monitor online.

Answer 3

before each request, clear the buffer cache and shared pool
execute immediate 'alter system flush shared_pool';
execute immediate 'alter system flush buffer_cache';
then we execute a query
to retrieve session statistics, expand the result for clarity and save the result to memory or to a table
select
SESSION_LOGICAL_READS,CPU_USED,BLOCK_GETS,PHYSICAL_READS
from (
select
a.statistic# as stat,
b.value
from
v$statname a, and a .statistic# = b.statistic# ) pivot (
v$ mystat b
where
a.statistic# in (14, 19, 128, 141) -- oracle statistic codes
sum(value) for stat in (14 as session_logical_reads, 19 as cpu_used, 128 as block_gets, 141 as physical_reads)
);