cpu 100% slow reads fast writes

[Derek Baxter_2]

My server is showing cpu utilization at 100%, I know that is not a bad thing, but it seems like when jobs are running that require reading to be done they take a very long time, but jobs that are writing run very fast. The disk utilization is averaging 30-40%. Memory is around 80%, and swap is constant at 46%. It is an oracle database that we are running no this server. Does anyone have any suggestions about what else I may want to look at.

[RAC_1]

What are your settings for dbc_max_pct and dbc_min_pct? What do you see in sar -b 5 5?

Is %rcache high? What is the RAM on machine?

How the file system is created? Post mount -p|grep "file_system_on_which_reads_are_slow"

Anil

[Fred Ruffet]

It is recommended for an Oracle Database to mount filesystems where datafiles are with "mincache=direct,convosync=direct" options. It bypasses the OS disk cache (Oracle does its own).

You should also have a look at dbc_min_pct and dbc_max_pct kernel parameters, which determines the size of the OS IO cache.

[Nicolas Dumeige]

If you want to know if there a problem with your database, searching it with system tools is just not a straightforward approach.

If the RBDMS is Oracle :
To get a general idea
set linesize 200
set pagesize 200
set time off
set timing off

COL SID FORMAT 99999
COL SEQ# FORMAT 9999999
COL EVENT FORMAT A28 TRUNC
COL P1TEXT FORMAT A20 TRUNC
COL P1 FORMAT 999999999999
COL P1RAW NOPRINT
COL P2TEXT FORMAT A20 TRUNC
COL P2 FORMAT 999999999999
COL P2RAW NOPRINT
COL P3TEXT FORMAT A20 TRUNC
COL P3 FORMAT 999999999999
COL P3RAW NOPRINT
COL WAIT_TIME FORMAT 9999999 NOPRINT
COL SECONDS_IN_WAIT FORMAT 9999999
COL STATE FORMAT A10 NOPRINT

select * from v$session_wait
/

select EVENT,count(*)
from v$session_wait
group by EVENT
order by count(*) desc
/

To analyze the results, look at wait events explaination in Oracle Performance Guide.

For a fast I/O surevillance :
set pagesize 200
set timing off
set time off

SELECT
ses.sid
, DECODE(ses.action,NULL,'online','batch') "User"
, MAX(DECODE(sta.statistic#,9,sta.value,0)) /greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
, MAX(DECODE(sta.statistic#,40,sta.value,0)) /greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
, round(60*24*(sysdate-ses.logon_time),0) "Minutes"
FROM
V$SESSION ses
,V$SESSTAT sta
WHERE
ses.status = 'ACTIVE'
AND
sta.sid = ses.sid
AND
sta.statistic# IN (9,40)
GROUP BY
ses.sid, ses.action, ses.logon_time
ORDER BY
SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
/


You could also install statpack, it will report a lot of usefull information.

[Hein van den Heuvel]

I like the reply from Nicolas best: If you like to understand what Oracle is doing, then just ask Oracle what it is doing! Often with a cursory glance at first, and a detailed statspack later.

What does the IO subsystem look like? Many (most?) systems these days have physical disks behind an intellegent and caching controller. The writes to such controller as often cached into its (battery backup protected) caches and are acknowledged instantaneously. The reads however need to go on to the disk (unless re-reading or read-ahead is workginf): do the seek, wait for the rotation to come around to the right place, start the transfer to the controller, then transfer to the host. So write are then fast (typically 1 milli second) while reads are slows (typically 5 ms, but 15 is not uncommon).
To add insult to injury, if the reads and writes for to the same (set of) disks then those write will go and 'disturb' the reads by causing more and largers seeks. And you can queue up many more writes (in the same time) as reads!
Here... try a mental experiemnt: Assume a trivial synchroneous programs. One issues a write, wait for teh result and issue the next. The Other issues a read, waits for results and issues the next. Well, in the time the reader gets teh first IO back (5 ms?), the writter will have done 5 writes, and those 5 writes will be queued up to the real disk and 'in the way' of the 2nd read request which will now take 5ms for a current write to finish + 5 ms for a queusd up write to finish and then finally 5 ms for the read to finish. By now the write has done 10 more IOs, all stashed in the cache, waiting to hurt the reader some more. It's a good thing that eventually even writer have to read what to do next! :-).

waddyathink, makes sense?

Hein

[Ingo Duschl · Accepted solution]

hello, it could also be that the database is very fragmentated we had that problem too, after full export dropping database and doing an import it was much better.
the database then is "new" have a look that the oracle file are big enough at beginning and the extends are big enough . look if you have some hotspots in your disks, if one disk is writing and reading much more than others.

[KapilRaj]

Do you have some stupid sqls running ? I found them many in my envs !! as k the application guys to re-write the codes !

When you have a bad I/O I would determine which table space is being used the most from the dba's , find out the file , and then the LV and stripe it to a number of disks if possible !

Here I think your node is running out of resources in terms of mem,copu and IO time to plan for an upgrade !!

Kaps

[Derek Baxter_2]

It does look like it might be something in the database. I appears that is only taking a long time reading from one table, which is the one we use the most. We are trying to work with that table to see if we can speed it up. Thanks for the help.

[Greg OBarr]

Install the Enterprise Manager and run the SQL analyzer. You can see what your top SQL statements are based on disk I/O, buffer gets, etc, and look at the explain plan for those statements. Chances are, you need an index or your database is not using the indexes that are there. If you're running 8.1.7 or above, are using the COST or CHOOSE optimozer mode, and have gathered statistics on your tables and indexes, you may need to adjust some things like OPTIMIZER_INDEX_COST_ADJ. By default, the optimizer in COST mode (there is no RULE optimizer mode in 9i) says that indexes are 100% as "expensive" as a full table scan. Set the OPTIMIZER_INDEX_COST_ADJ in the init.ora file to "5". This will tell the optimizer that an index is 95% lower cost than a full table scan. Improves performance greatly. This works, of course, provided that you have indexes on the table that the optimizer can use for the query you're running.

-greg

[Nicolas Dumeige]

For the fragmentation hypothsesis

If the table is updated / deleted with hight frequency, it can get fragmented. To check this out :
http://www.remote-dba.cc/oracle_tips_chained_rows.htm

If so you can export, drop, then import the table to get the best physical allocation.

If the table is very large, you can use partition and spread the IO on disks.

Indexes, materialized view ... Oracle give you a lot of option to get better performance.

[Brian Crabtree]

You could be seeing a large number of parsing of SQL statements as well.

Check your settings for session_cached_cursors and cursor_sharing.

Setting session_cached_cursors from 100-300 should be good. If you are using bind variables, cursor_sharing should be set to SIMILAR. If you are using literal statements, you can set it to FORCE. There is some performance hit for it, however this might help keep the parsing down on your SQL statements.

Thanks,

Brian

[Printaporn_1]

Hi,

For me I will find the top process that hightly utilize CPU , if this is Oracle Server process , we can check for SID and SQL that currently running and take huge CPU.
from OEM. Or v$session and v$sql like:

SELECT DISTINCT /*+ORDERED*/
sql.sql_text,
sql.address,
sql.hash_value,
0 piece,
sid,
serial#,
getusername(sid)username
FROM v$session s,
v$sql sql
WHERE sql.address=s.sql_address
AND sql.hash_value=s.sql_hash_value

---------
Then try to analyze table that related in the query using
dbms_stats package.

HTH

[Derek Baxter_2]

Thanks to everyone for the help. I rebuilt the table and it has fixed the problems.