Home » RDBMS Server » Performance Tuning » my production database is taking 50% of CPU consumption in main db server
| my production database is taking 50% of CPU consumption in main db server [message #236461] |
Thu, 10 May 2007 01:10  |
|
|
Dear Sir,
In our main db server, patricularly our production database ( uid: WMS ) is running very slow.
Pls see the current status of DB server... 50% of CPU is by WMS processes...!!
Name PID CPU% PgSp Owner
oracle 10158082 24.8 10.6 ora_oem <<- this is our production database
oracle 1278042 24.8 10.0 ora_oem <<--this also....
oracle 5894358 1.1 4.3 ora_oem
topas 9662608 0.7 12.2 finmon
oracle 9809932 0.5 4.2 ora_oem
oracle 9658384 0.2 5.8 ora_oem
acactmgr 1536016 0.1 7.1 root
tnslsnr 1077448 0.1 6.3 ora_oem
acrecord 1355864 0.1 43.4 root
maragent 1384554 0.1 11.3 root
oracle 1253450 0.1 5.7 ora_fnp
lrud 274566 0.0 0.1 root
oracle 8483064 0.0 10.1 ora_oem
oracle 10186826 0.0 10.1 ora_oem
Signal 2 received
$ ps -ef|grep 10158082
finmon 5820662 8314888 1 18:16:14 pts/1 0:00 grep 10158082
ora_oem 10158082 1 101 17:13:56 - 24:33 oraclePFNW (LOCAL=NO)
$ ps -ef|grep 1278042
ora_oem 1278042 1 120 16:05:47 - 8:33 oraclePFNW (LOCAL=NO)
finmon 9957444 8314888 0 18:16:28 pts/1 0:00 grep 1278042
Please provide some general idea that how to approach this issue to resolve ?
In statspack report, which area we can see regarding this issue ?
thank you
kesavan.
|
|
|
|
|
|
|
|
|
|
| Re: my production database is taking 50% of CPU consumption in main db server [message #236630 is a reply to message #236495] |
Thu, 10 May 2007 07:38  |
|
|
Dear Michael,
After checking in both sql ordered by gets and reads,
i found there isone process cosuming cpu much.... which is as follows.....please guide me to resolve and wait events also...
thank you,
SQL ordered by Gets for DB: PFNW Instance: PFNW Snaps: 5339 -5341
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
43,869,066 1 43,869,066.0 711.5 ######## 22473.95 3620967297
Module: SQL*Plus
SQL ordered by Reads for DB: PFNW Instance: PFNW Snaps: 5339 -5341
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
29,108 1 29,108.0 65.6 ######## 22473.95 3620967297
Module: SQL*Plus
begin --EOD Wms_Proc_Rip_Eod_Process(); dbms_output.put_line('RI
P PROCESS COMPLETED'); WMS_PROC_COMBINE_HOLDINGS(WMS_BUSINESS_D
ATE); dbms_output.put_line('COMBINED HOLDINGS COMPLETED'); WMS_
PROC_INSERT_HOLDINGS_DET(WMS_BUSINESS_DATE); dbms_output.put_lin
e('DATA WAREHOUSING COMPLETED'); Wms_Pro_Update_Investments_Det
Actually same "PROCESS" taking place at first in both
Actually, when i checked the complete sql_text in v$sqlarea for the patricular hash_value, iam not able to see the entire only... (above ), why ? is there any alternate to see the entire text of this process ?
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 22,005 98.87
SQL*Net message from dblink 71,860 222 1.00
db file sequential read 7,699 18 .08
db file scattered read 4,495 4 .02
control file parallel write 2,342 3 .02
-------------------------------------------------------------
this is my top event list.
let me list out my understandings first then you guide me :
WAIT EVENT-1:
-------------
1) CPU TIME :- my cpu_count = 4.
i understnd 98.87 % cpu time is used during this snapshot and i feel
this is the bottleneck now.
After seeing the sql ordered by GETS and READS, i understand from my user
that they are running one PROCESS which is calling 7 procedures.
The main purpose of this process is to update only one table ( name : WMS_HOLDINGS )
1. Total no.of rows in this table : 69114
2. index details.
select index_name, index_type, status, last_analyzed from dba_indexes
where TABLE_owner = 'WMSDBA' AND TABLE_NAME = 'WMS_HOLDINGS'
SQL> /
INDEX_NAME INDEX_TYPE STATUS LAST_ANAL
------------------------------ --------------------------- -------- ---------
PK_WMS_HOLDINGS NORMAL VALID 04-MAY-07
3. table details
SQL> select table_name, last_analyzed from dba_tables
2 where owner ='WMSDBA' and table_name ='WMS_HOLDINGS';
TABLE_NAME LAST_ANAL
------------------------------ ---------
WMS_HOLDINGS 04-MAY-07
4.i just checked in v$db_object_cache for the column : KEPT... to know whether any of these
procedures have been kept .... but none of them kept in cache.
HENCE, PLEASE ADV ME TO REDUCE THE CPU TIME..
SHALL I KEEP ALL THESE 7 PROCEUDURES... USING SQL>execute dbms_shared_Pool.keep('<procedurename>', 'p');
WAIT EVENT-2:
-------------
SQL*Net message from dblink 71,860 222 1.00
My understanding is, this gives information on the amount of network traffic between distributed Oracle servers.
FURTHER GUIDE ME HOW I CAN APPROACH TO REDUCE THIS... ?
WAIT EVENT-3:
-------------
db file sequential read 7,699 18 .08
my understanding is ,
1) there is a table access using INDEX or rowid...
2)or there will be wait while writing TEMPORARY space...for this shall i increase PGA_AGGREGATE_TARGET ?
because, as of now, we have 10MB only, as per Mr.Michael adv, i can increase to 100mb...
is it ok ?
WAIT EVENT-4:
-------------
db file scattered read 4,495 4 .02
my understanding is,
this wait event occur during FTS.
STATISTIC# NAME CLASS VALUE
---------- ---------------------------------------------------------------- ---------- ----------
187 table scans (long tables) 64 15564
and
db_file_multiblock_read_count integer 8
PLEASE GUIDE ME WHETHER I SHOULD INCREASE THIS VALUE FROM 8... IF SO, HOW MUCH I CAN INCREASE...
OR TELL ME IS THERE ANY OTHER ALTERNATE TO REDUCE THIS WAIT EVENT ?
WAIT EVENT-5
-------------
control file parallel write 2,342 3 .02
my understanding is,
my session has issued multiple I/O requests in parallel to write blocks to all control files, and is waiting for all of the writes to complete.
PLEASE GUIDE ME HOW I HAVE TO APPROACH TO REDUCE THIS WAIT EVENTS...
THANK YOU,
KESAVAN.
|
|
|
|
| Re: my production database is taking 50% of CPU consumption in main db server [message #236636 is a reply to message #236630] |
Thu, 10 May 2007 07:50 |
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
|
Member |
|
|
Hi can you upload the statspack report.
The wait events dbfile scattered read and db file sequential reads can be present in a healthy database as well.
The thing is you need to tune the queries with high buffer gets per execution and well as queries with high physical reads.
For reducing the network traffic through the dblink please refer oracle perfromance tuning documentation.
Yes increasing the PGA AGGRGATE TARGET should help.
Have you collected statistics for the database? , please dont play around with multiblock read count unless you have solid reason to do so , this requires further deep analysis of the database queries.
Thanks
|
|
|
|
| Re: my production database is taking 50% of CPU consumption in main db server [message #236637 is a reply to message #236636] |
Thu, 10 May 2007 07:58 |
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
|
Member |
|
|
Regarding the procedures , you need to keep the procedures in the shared pool.
Are the procedures using bulk collect feature for updates ?
If not then use the bulk collect feature with an array limit of 500.
You are not able to see any rows after you query the v$sqlarea as those statements are aged out from the shared pool.
Try to select based on sql_text and not on the hash value , this way if the statement is there in the shared pool with a different hash value , you will get the full text.
Thanks
|
|
|
|
| Re: my production database is taking 50% of CPU consumption in main db server [message #236838 is a reply to message #236636] |
Fri, 11 May 2007 02:13 |
|
|
Dear Sriram,
I noted your below 2 messages. Thank you.
As per your first mail,
1) i have attached my statspack report ( production ) along with this.
kindly share your valuable comments with me further.
2) after checking statspack, pls tell me regarding the wait events
dbfile scattered read and db file sequential reads, whether we can ignore this or
to be tuned ?
3) after checking in both SQL ORDERED BY GETS AND READS... ( COL : GETS PER EXEC )
the process ( hash value : 3620967297 ) only taking high consumption in both section.
hence, as per your adv, i will keep all the 7 procedures involved in that PROCESS.
4) your comment is :
quote:
Have you collected statistics for the database?
,please dont play around with multiblock read count
unless you have solid reason to do so ,
this requires further deep analysis of the database queries
unquote
as per your above comment, we are not chaning the multiblock read count..we keep as it is.
And please confirm, regarding database statistics collection...i did not collect yet.
Currently, STATISTICS_LEVEL = TYPICAL , shall i set to ALL... ?
Further to collect the statistics at database level, shall i use ..
sql>execute DBMS_STATS.GATHER_DATABASE_STATS
or
sql>EXEC DBMS_UTILITY.analyze_database('COMPUTE');
after issuing this ( anyone of this ),how to see the statistics further....
pls guide me...
FOR YOUR SECOND MESSAGE.....
1) As per your adv, i keep all those 7procedures in KEEP pool.
( as of now we dont' have keep pool, first let me increase from the current size(64m) to 128m and then
as per oracle, let me allot 10% of default pool size for KEEP pool )
2) your comment is :
<<<quote
Are the procedures using bulk collect feature for updates ?
If not then use the bulk collect feature with an array limit of 500.
unquote>>>
please guide me, iam not getting the exact meaning of above statements.
please tell in some other words...or give me some documents or URL something to read further
regarding bulk collect feature....
THANK YOUR FOR YOUR TIME WITH ME.
KESAVAN.
AND WE NEED TO INCREAE THE SGA_MAX_SIZE FOR OUR PRODUCTION DATABASE...
CURRENTLY WE HAVE 177.47 MB.... ACTUALLY IN DUBAI, WE HAVE MAIN SERVER AND IN THAT, WE HAVE KEPT MANY DATABASES...
OUT OF THAT "WMSDBA" IS OUR PRODUCTION DATABASE.... IN THAT SERVER, THE PHYSICAL RAM SIZE IS 3.5 GB...
SO, PLS TELL ME IS THERE ANY THUMB RULE THAT SGA_MAX_SIZE SHOULD BE THIS MUCH PERCENTAGE OF PHYSICAL RAM OF DB SERVER ?
IF SO, TELL ME HOW MUCH WE CAN INCREASE.... MY SENIOR SAYS, SGA_MAX_SIZE SHOULD BE 3/4 OF PHYSICAL RAM IN SERVER.
IS THIS CORRECT /
|
|
|
|
| Re: my production database is taking 50% of CPU consumption in main db server [message #236858 is a reply to message #236838] |
Fri, 11 May 2007 02:58 |
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
|
Member |
|
|
Hi ,
As of now the wait events dbfile sequential read and scattered are not the problem ones,
As said earlier please tune your queries , top buffer gets and physical reads.
The statistics_level is all about controlling major statistics collections or advisories in the database. The level of the setting affects the number of statistics and advisories that are enabled:
This has nothing to do with database statistics.
The current level will turn on the adivisories for what we are interested in ie buffer cache,pga advisory etc.
What i was asking all about the frequency of database statistics collection , if the data volume changes frequently then you may need to collect statistics frequently , but the better way would be to do a schema level stats ie gather_schema_stats.
Now coming to keeping the procedures , the procedures are kept in the shared pool , not in the keep pool.(Use DBMS_SHARED_POOL.KEEP Procedure)
You have FTS for short tables , you may keep these under the keep pool , identifying the candidate objects please follow the below link.
http://www.dba-oracle.com/t_plan9i_sql_full_table_scans.htm
As of now dont increase the size of the shared pool , increase the size of the buffer cache to 128m.
Please read the below for an excellent demo and explanation on BULK COLLECT
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2367352052686
Yes 3/4th of the Physical ram would be fine for the SGA-MAX_SIZE.
|
|
|
|
|
|
| Re: my production database is taking 50% of CPU consumption in main db server [message #237028 is a reply to message #236461] |
Fri, 11 May 2007 12:10 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi.
IMHO - your main problem is CPU consumption (all other wait contribute about 1.2% of total waits, so they may be ignored at that stage).
Check and tune following queries:
15,600,112 1 15,600,112.0 253.0 8083.62 7918.82 2547270891
Module: SQL*Plus
INSERT INTO WMS_LARGE_DEPOTMOVEMENT(INVEST_WMS_AC_NUMBER,CLIENT_
NAME,CURRENT_POSTION,DEPOT_DATE) SELECT A.INVEST_WMS_AC_NUMBER,A
C.CLIENT_NAME, SUM(NVL(WMS_FUN_CONVERT_CURRENCY(S.SECURITY_CCY,'
AED',1), 0) * WMS_FUN_SECURITY_ABS_PRICE(S.SECURITY_CODE,TO_DATE
(:B1,WMS_DATE_FORMAT) ,1) * NVL(A.HOLDINGS_BOOKPOSITION, 0)) CUR
3,784,134 5,153 734.4 61.4 3038.43 2977.18 3418595293
Module: SQL*Plus
SELECT MAX(SECURITY_PRICE_SRNO) FROM WMS_SECURITY_PRICE O WHERE
SECURITY_PRICE_PRICE_DATE = ( SELECT MAX(SECURITY_PRICE_PRICE_DA
TE) FROM WMS_SECURITY_PRICE I WHERE O.SECURITY_CODE = I.SECURITY
_CODE AND I.SECURITY_PRICE_PRICE_DATE <= :B2 AND I.RECORD_STATUS
NOT IN ('C','N') ) AND O.SECURITY_CODE = :B1 AND RECORD_STATUS
You have to post the full source of these statements and corresponding EXPLAIN's as well.
Consider increasing your buffer cache to 128M instead of existing 64M.
HTH.
Michael
|
|
|
|
| Re: my production database is taking 50% of CPU consumption in main db server [message #237304 is a reply to message #237028] |
Mon, 14 May 2007 02:24 |
|
|
Dear Mr.Sriram, Michael,
Thank for your messages.
We will increase db_cache_size accordingly.
We will check the reason for much ROLLBACKs.
And for those SQLs, we will check the EXPLAIN PLAN in detail.
Further please confirm the expansion of the below abbrevation
" IMHO "
bcos, you have mentioned in the below statment
<<< quote
IMHO - your main problem is CPU consumption (all other wait contribute about 1.2% of total waits, so they may be ignored at that stage).
unquote >>>
Thank you for your time.
Kesavan.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 16 05:18:27 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
