Q: V$SYS_TIME_MODEL/v_$sysstat in 12C (pluggable)
Date: Sun, 29 Dec 2013 13:01:35 +0000
Message-ID: <92855FF6D7087A47A1DFFAF535C41363575672_at_UKBMBXW01.prod.quest.corp>
Hi folks ,
I'm trying to understand how to do performance tuning in 12C multitenant architecture .
When I'm try to understand the cpu statistics from either v$sys_time_model or v$sysstat I get confusing data .
This is my test case :
Oracle 12.1.0.1 of windows , 4 pdbs.
2 sessions connected to one of the pdbs (id=4) running the following block:
declare a number;
begin
for i in 1..100000 loop
select count(*) into a from dba_objects where rownum<24000;
end loop;
end;
/
To measure deltas I've used a modified version of tom kyte "runstat" utility <http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701> , I've created a stats view like that :
create or replace view stats
as select 'STAT...' || stat_name||' ['||con_id||']' name, value
from (
select * from V$SYS_TIME_MODEL
union all
select * from V$con_SYS_TIME_MODEL) ;
Runing the runstat I got the following input :
Where I can see the numbers are near but not summed up . For example db time [0]= 3,991,739 <> 3,987,789+13,352+13,352+9,402 = 4,023,895
I wander why is the reason for that , maybe the are some overlapping data ?
Name Run1 Run2 Diff STAT...DB CPU [0] 7,063,925 3,676,443 -3,387,482 STAT...DB CPU [1] 13,997 15,999 2,002 STAT...DB CPU [2] 5,999 8,999 3,000 STAT...DB CPU [3] 5,999 8,999 3,000 STAT...DB CPU [4] 7,055,927 3,669,443 -3,386,484 STAT...DB time [0] 8,025,438 4,033,699 -3,991,739 STAT...DB time [1] 22,431 31,833 9,402 STAT...DB time [2] 11,983 25,335 13,352 STAT...DB time [3] 11,983 25,335 13,352 STAT...DB time [4] 8,014,990 4,027,201 -3,987,789 STAT...PL/SQL compilation elap 1,382 0 -1,382 STAT...PL/SQL compilation elap 1,382 0 -1,382 STAT...PL/SQL execution elapse 4,154 2,690 -1,464 STAT...PL/SQL execution elapse 246 344 98 STAT...PL/SQL execution elapse 3,908 2,346 -1,562 STAT...background cpu time [0] 12,999 8,998 -4,001 STAT...background cpu time [1] 12,999 8,998 -4,001 STAT...background elapsed time 49,110 24,267 -24,843 STAT...background elapsed time 49,110 24,267 -24,843 STAT...hard parse elapsed time 1,717 0 -1,717 STAT...hard parse elapsed time 1,717 0 -1,717 STAT...parse time elapsed [0] 5,030 858 -4,172 STAT...parse time elapsed [1] 5,030 858 -4,172 STAT...parse time elapsed [2] 3,104 741 -2,363 STAT...parse time elapsed [3] 3,104 741 -2,363 STAT...parse time elapsed [4] 3,104 741 -2,363 STAT...sql execute elapsed tim 8,013,342 4,009,013 -4,004,329 STAT...sql execute elapsed tim 10,335 7,147 -3,188 STAT...sql execute elapsed tim 2,249 3,069 820 STAT...sql execute elapsed tim 2,249 3,069 820STAT...sql execute elapsed tim 8,005,256 4,004,935 -4,000,321
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Dec 29 2013 - 14:01:35 CET