| Need help on Performance tuning [message #425183] |
Wed, 07 October 2009 23:30  |
socalguy09
Messages: 1
Registered: October 2009
|
Junior Member |
|
|
Hi,
I have a really bad performance on the database as more users got into the system (> 50 users) or after the bulk load or bulk import. I'm not a pure DBA, I'd need help on tuning the server (at least with bufer cache, shared pool size, and pga).
Below is my AWR Report
Thanks in Advance
Buffer Cache: 1,768M Std Block Size: 8K
Shared Pool Size: 352M Log Buffer: 5,120K
Load Profile
Per Second Per Transaction
Redo size: 1,131.67 3,025.06
Logical reads: 169.48 453.03
Block changes: 6.54 17.49
Physical reads: 0.18 0.49
Physical writes: 0.39 1.04
User calls: 9.65 25.79
Parses: 3.22 8.61
Hard parses: 0.17 0.45
Sorts: 1.01 2.71
Logons: 0.05 0.13
Executes: 6.09 16.28
Transactions: 0.37
% Blocks changed per Read: 3.86 Recursive Call %: 60.19
Rollback per transaction %: 18.47 Rows per Sort: 40.95
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.07 In-memory Sort %: 100.00
Library Hit %: 97.80 Soft Parse %: 94.82
Execute to Parse %: 47.09 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 95.10 % Non-Parse CPU: 91.53
Shared Pool Statistics
Begin End
Memory Usage %: 75.62 79.10
% SQL with executions>1: 95.39 93.05
% Memory for SQL w/exec>1: 93.56 91.78
Top 5 Timed Events
Event Waits Time(s) Percent Total DB Time Wait Class
class slave wait 122 624 3,490.44 Other
CPU time 11 64.05
control file parallel write 1,180 3 19.57 System I/O
control file sequential read 544 2 13.80 System I/O
log file parallel write 1,358 2 10.52 System I/O
SGA Memory Summary
SGA regions Size in Bytes
Database Buffers 1,853,882,368
Fixed Size 792,964
Redo Buffers 5,242,880
Variable Size 556,000,892
init.ora Parameters
Parameter Name Begin value End value (if different)
__db_cache_size 1853882368
__java_pool_size 16777216
__large_pool_size 58720256
__shared_pool_size 369098752
_shared_pool_reserved_pct 6
background_dump_dest C:\ORACLE\PRODUCT\10.1.0\ADMIN\V5
compatible 10.1.0.2.0
control_files C:\ORACLE\PRODUCT\10.1.0\V5\V5\CO
core_dump_dest C:\ORACLE\PRODUCT\10.1.0\ADMIN\V5
db_block_size 8192
db_cache_size 16777216
db_domain
db_file_multiblock_read_count 16
db_name V5
db_recovery_file_dest C:\oracle\product\10.1.0\flash_re
db_recovery_file_dest_size 21474836480
dispatchers (protocol=TCP)
fast_start_mttr_target 26
java_pool_size 16777216
job_queue_processes 20
large_pool_size 58720256
log_buffer 5242880
open_cursors 2000
pga_aggregate_target 896147200
processes 900
remote_login_passwordfile SHARED
resource_limit FALSE
session_cached_cursors 100
sessions 995
sga_max_size 2415919104
sga_target 2306867200
shared_pool_size 369098752
shared_servers 25
sort_area_size 65536
transactions 900
undo_management AUTO
undo_tablespace UNDOTBS1
user_dump_dest C:\ORACLE\PRODUCT\10.1.0\ADMIN\V5
|
|
|
|
|
|
| Re: Need help on Performance tuning [message #425342 is a reply to message #425183] |
Thu, 08 October 2009 12:25  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
For all the fine tuning advice out there, the reality is 9 times out of 10 a performance problem is related to bad SQL.
I never bought into the line that server tuning was the first thing you should. your first look at tuning should always be to find the most expensive sql and see if you can make is way less expensive. Twenty years ago an Oracle tuning expert stunned me with a fact (I was impressionable in those days).
1% of the sql on an Oracle database does 90% of the work.
Now it is 20 years later, and this rule still works. I can look at any Oracle database and find that 1% of the sql running on that database is doing 90% of the work. When I server tune, the first thing I do is make sure there is no piece of sql that is killing me. Usually there is and one day spent tuning it yeilds a totally new box. Imagine what happens when 90% of the work being done on a box is all of a sudden gone. This is very possible when tuning high cost SQL. Here is a script you can use to find potential tuning opportunities.
If after a day of tuning your worst SQL, your issue does not go away, then you can start looking at other problems. Here is a dump of stuff from one of my test systems that will illustrate this point:
1) we see that my test box has been up for 5 and 1/2 days.
2) we see a distribution of code based on cpu cost. Notice how only 15 SQL statemets are responsible for 60% of the workload.
3) we see the actual sql
SQL> @showtopcpu2
INSTANCE_NAME UP_DAYS
---------------- ----------
mytestd 5.5
1 row selected.
CPU_TIME_LOG10 SQL_STATEMENTS CPU_TIME_ROUNDED CPU_TIME PCT_TOTAL RUNNING_CPU_TIME
-------------- -------------- ---------------- ---------- --------- ----------------
-6 13 .000001 0 0 0
-3 28 .001 0 0 0
-2 427 .01 2 0 2
-1 935 .1 34 0 36
0 652 1 743 2 779
1 72 10 2242 5 3021
2 49 100 15292 34 18313
3 15 1000 27286 60 45599
--------------
sum 2191
8 rows selected.
CPU_SECONDS EPLAPSED_SECONDS HOURS_IN_CACHE EXECUTIONS SQL_TEXT
----------- ---------------- -------------- ---------- --------------------------------
100 119 6 12414 INSERT INTO WORK_AGCY_DASHBOARD_
102 1551 63 3 call dbms_space.auto_space_advis
105 342 25 1 SELECT /*+ parallel(pd,2) */
107 264 0 20316 INSERT INTO AGCY_DASHBOARD_FACT(
112 210 7 26055 INSERT INTO WORK_SALES_TM_PSTN_D
113 176 15 20475 INSERT INTO WORK_FLX_COMM_FACT_P
114 124 131 1571 /* OracleOEM */ SELEC
115 1347 5 1 /* Formatted on 2009/07/14 12:43
117 829 0 1 /* Formatted on 2009/07/14 13:43
135 343 27 2 SELECT /*+ parallel(mttl,2) use
136 1051 0 1 /* Formatted on 2009/07/14 10:25
137 170 7 21418 INSERT INTO WORK_MSAP_BKFPOL_AIF
143 1409 0 1 /* Formatted on 2009/07/14 10:21
159 1158 6 1 /* Formatted on 2009/07/14 11:43
178 192 27 218990 SELECT 'select bus_seg_id from b
179 2060 6 1 /* Formatted on 2009/09/24 08:43
181 278 15 5091 INSERT INTO FLX_INSURED_NAME_DIM
181 318 24 51067 INSERT INTO WORK_MSAP_IMPACT_TRA
185 700 6 1 /* Formatted on 2009/09/10 07:07
186 1437 0 1 /* Formatted on 2009/09/10 07:16
188 212 121 2714092 UPDATE lmp_cust04_stage.PL_FLOW
207 313 6 1 /* Formatted on 2009/02/12 10:06
210 252 106 5248 SELECT TOWNER, TNAME, NAME, LENG
227 865 15 1 /* Formatted on 2007/06/12 11:06
230 439 0 75963 INSERT INTO SALES_ICON_POLICY_FA
243 253 24 42662 INSERT INTO WORK_AGCY_DSHBRD_PRM
263 287 27 218990 BEGIN LMP_SEGMENT_BUS_SEG_ID
267 332 122 1723925 INSERT INTO lmp_cust04_stage.PL
270 609 0 1 /* Formatted on 2008/02/11 07:09
323 339 24 39012 INSERT INTO WORK_BASE_AGCY_BKF_M
332 712 25 54325 INSERT INTO WORK_MSAP_PPR_PLCY_C
342 661 25 60848 INSERT INTO WORK_MSAP_DAILY_PPR_
346 2931 7 1 /* Formatted on 2009/08/24 13:00
358 611 7 1 /* Formatted on 2009/08/13 06:54
365 676 7 53253 INSERT INTO WORK_AGCY_DASHBOARD_
405 572 24 70640 INSERT INTO WORK_MSAP_IMPACT_TRA
405 823 7 126861 INSERT INTO WORK_BKF_ICON_POLICY
428 549 6 1 SELECT MAX (SLS_TEAM_PSTN_REP_ID
452 2000 15 1 SELECT /*+ FULL(Fc) PA
496 573 16 102185 INSERT INTO WORK_MSAP_FLEX_TRANS
527 751 15 90921 INSERT INTO WORK_FLX_COMM_FACT_C
536 681 26 53514 INSERT INTO WORK_AGCY_DSHBRD_PRM
576 1242 0 1 /* Formatted on 2009/04/29 08:38
585 755 30 112640 INSERT INTO WORK_BKF_GROWTH(ENHA
660 669 6 3978598 UPDATE WORK_SALES_TM_PSTN_DSHBRD
793 842 14 263277 INSERT INTO WORK_FLX_COMM_FACT(T
798 1055 131 132 insert into wrh$_sga_target_advi
817 8468 0 1 /* Formatted on 2009/04/02 09:53
834 2805 15 1 SELECT /*+ full(Fc) P
1006 5279 0 1 /* Formatted on 2008/04/16 13:39
1071 5938 15 1 /* Formatted on 2007/06/12 12:16
1133 1373 6 172250 INSERT INTO WORK_SALES_TM_PSTN_D
1248 1574 6 163267 INSERT INTO WORK_BASE_AGCY_BKF_M
1283 21407 7 1 /* Formatted on 2009/09/24 08:42
1326 1481 123 17215779 DELETE FROM lmp_cust04_stage.PL
1354 22405 7 1 /* Formatted on 2009/09/24 08:43
1637 5092 14 1 SELECT /*+ parallel(cl,3) */
1681 5190 16 1 /* Formatted on 2007/06/12 10:54
2030 7654 17 1 /* Formatted on 2009/08/13 08:40
2189 15108 15 1 /* Formatted on 2007/06/12 12:00
2702 9627 7 1 /* Formatted on 2009/01/13 08:06
2753 10567 0 1 /* Formatted on 2009/03/17 12:55
2902 9267 24 1 /* Formatted on 2008/04/14 10:19
2965 6799 7 1 /* Formatted on 2008/04/23 08:12
-----------
42548
64 rows selected.
Obviously this technique is not 100%. I am only looking at SQL currently in my cursor cache. But this works for me. If I wanted to tune this box, I would take a look at the 15 statments and then the 49 above it. Ideally I would make some great headway.
Actually this box has already been tuned. Originally there were 10 SQL statements responsbile for 98% of CPU consumed. I did a quick tuning and their cost went to almost zero.
I have attached the file for you use. This may not be your issue, but it is worth looking at.
Kevin
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
