Home » RDBMS Server » Performance Tuning » Need help on Performance tuning (Oracle 10.1.0.2, Windows 2003)
Need help on Performance tuning [message #425183] Wed, 07 October 2009 23:30 Go to next message
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 #425188 is a reply to message #425183] Wed, 07 October 2009 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
which have you tried & what were the results
Re: Need help on Performance tuning [message #425342 is a reply to message #425183] Thu, 08 October 2009 12:25 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
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
Re: Need help on Performance tuning [message #425514 is a reply to message #425342] Fri, 09 October 2009 06:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
+1

Good advice there Kevin
Re: Need help on Performance tuning [message #425584 is a reply to message #425514] Fri, 09 October 2009 16:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Awesome script. Copied it. Tweaked it (I like the SQL_FULLTEXT). Used it. Tuned the #1 SQL already.

Legend!
Re: Need help on Performance tuning [message #425586 is a reply to message #425183] Fri, 09 October 2009 16:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks.

I can't seem to download it though. Does the download still work?

Kevin
Re: Need help on Performance tuning [message #425590 is a reply to message #425586] Fri, 09 October 2009 16:39 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yep. Just downloaded it again now
Previous Topic: Slow queries, configuration problem ?
Next Topic: Oracle Performance Manager
Goto Forum:
  


Current Time: Mon Sep 26 00:48:56 CDT 2016

Total time taken to generate the page: 0.05242 seconds