Re: High CPU usage
Date: Tue, 19 Jun 2012 18:07:13 +1000
Message-ID: <4FE03331.8040008_at_millerandbowman.com>
The query is :
SELECT 'COLUMN' type, owner, table_name object_name, column_name, column_id, data_type
FROM sys.all_tab_cols
WHERE hidden_column = 'NO'
and rownum <=50
and owner = :bind1
and table_name = :bind2
union all
select
case type#
when 7 then
'PROCEDURE'
when 8 then
'FUNCTION'
when 9 then
'PACKAGE'
end type, null owner, name object_name, null column_name, null column_id, null data_type
FROM sys.obj$ o
where name not like 'BIN%'
and type# in (7,8,9)
and rownum <=50
and name like :bind3;
and is always run with the same bind variables. I just executed 10 times for a total of 550 buffer gets , 500 rows and 0.92 seconds of CPU.
Another SQL statement had the following stats:
First run: Fetch 1680 301.91 1003.86 12860128 12864552
0 25164
Second run: Fetch 1680 1601.70 2230.58 12298368 12867647
0 25164
Also of interest is the time taken for scattered reads:
db file scattered read 804821 0.69
750.66 db file scattered read 768964 22.61
2014.15
This is not always the case.
During this period vmstat reports that CPU never went under 80% and 'ps
aux' showed that CPU usage for the session running the query was between 63
and 72 % ( which I assume is the percentage of 1 CPU)
Regards
Gerry
Jonathan Lewis wrote: ----- Original Message ----- From: "Gerry Miller" <gerry_at_millerandbowman.com>[1] To: <jonathan_at_jlcomp.demon.co.uk>[2] Cc: <oracle-l_at_freelists.org>[3] Sent: Tuesday, June 19, 2012 7:07 AM Subject: Re:High CPU usage | It is quite a lightweight query of sys.all_tab_cols and obj$ but the | reason I chose it was that it had exactly the same buffer getsin both | files and the same type and number of waits events. I was trying to | avoid any red herrings. The key question, though is whether the number of buffer gets seems reasonable for the query. If we can't trust the number of buffer gets reported then the difference in the CPU usage becomes asecondary problem. | | Am I correct in assuming that any such latch or mutexproblem activity | would manifest itself as a session wait event? | Not necessarily - latches spin before sleeping and mutexes spin - both of which take up CPU. Normally we would expect a relatively short spin followed by a sleep, of course, but your tkprof output is anomalous so we need to think about possible reasons for unexpected behaviour (i.e. bugs). On an O/S-related variant of the same theme - is your regression testing on a new (or upgraded) platform to the one you've otherwise been using. Is it possiblethat some sort of NUMA side effect is making memory access very slow and causing extreme CPU stalling when accessing latches or mutexes ? Can you compare the O/S CPU reporting with the Oracle CPU reporting for one of these anomalous tests. Regards Jonathan Lewis -- http://www.freelists.org/webpage/oracle-l[4]
- Links --- 1 mailto:gerry_at_millerandbowman.com 2 mailto:jonathan_at_jlcomp.demon.co.uk 3 mailto:oracle-l_at_freelists.org 4 http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l