??????????? ?? ????? ???????????????? (?????? ?? ???????) ??????????? ???????????????? ???????, ???????, ???????????? ????? ????? ??????????? ????. ?? ????? ???????????, ?????? ?????, ????????? ????? ???????????????? ???????, ????????, ??? ???????, ? ????????? ???. ?? ??????? ???????? ??????????, ???????? ?????? ????? ??????? ??????? ? ??????????? ???????.

????? ???? ?? ??????? ????????? ????????? ????? ???????????. ?????????????, ?????????? ???????????? ???????? ?????????????????? ?????? ?????????????, ????? ??????????? ? ???????? ??????????? ???????.

??????????? ?????? ???????, ? ???? ???????, ????? ? ????????????? ?????????? ?????? ?????????????. ????? ???????, ?????????? ????????? ???????????? ????? ??? ?? ???????? ?????????? ??????? ? ??????????? ???????? ? ????????? ?????. ?????????? ????????? ????: ?????? ??????? ?????????, ??????????? ? ????????????????? ?????, ?? ?????????? ?????????? ?????????? ????????????.

?????? ?????? ??????????, ??? ????????? ??????? ??? ??????? ??????? ???????, ? ???????????? ?????????? ?????????:

1. ??????? ??????????? ??????????? ?? ?? ????????? ??????;
2. ?????????? ???????? ???????????? ??????;
3. ?????????? ??????, ????????? ?????????? ???????? ?? ???????;
4. ?????????? ????? ?????????? ??????-??????????.

????????? ?????? ???????? ??????? ??? ???????? ??????? ? ??????????? ???????????: ??????????? ?? ??? ??????????? ?? ????????? ??????. ? ?????? ???????? ??????? ? ?????????? ??????????? ??????????? ?? ????????? ?????? ??? ???????? ???????? ??? ????????? ??????????? ???????????????? ??????. ?? ????????? ????????? ?????? ????? ??????????? ??????-???? ??????????? ??????? ? ?????????:

1. ????????????????? ? ??????? ?????: ?????? ??????? ??? ???????????;
2. ???????????? ??????????: ?????????? ?????????;
3. ????????? ?????.

1. ????????

??? ???????? ??????? ?? ????????? ?????-???? ?????????????? ??????? (??) ????????? ?????? ? ???, ?????????? ??????, ?????????? ????????? ???????? ????? ???? ???:

• ?? ?????????? ?????? ?? ? ????? ???
• ???? ?????????? ?????? ?? ??? ??? ???? ??????.

????????? ??????? Cary Millsap [1], ?????????? ????, ??? ?????????????? ?????????? ??????: ??????? ???????? ??????????? ? ????????????? ?? ?????. Mr. Millsap ????????, ??? ??????? ?????? ??? ??????????? ?????? ????????. ?? ????? ?????? ???????? ????? ???????????. ??-??????, ??????? ?? ?????? ????????????? — ??? ?????????? ??????, ? ???????? ?? ????? ??????????? ?????????????? ???????? ???????? (??????????? ??????). ??-??????, ?????? ??????? ????? ????????? ?????????? ?????, ? ????? ??????? ??????? ? ???????????? ???????????????? ???????? ???????. ? ???? ?????? ??????????? ?????? ?? ??????? ????? ????????? ??????? ??????, ??????? ? ??????? ????????????????.

???? ??? ?? ?????????? ?????? ?? ? ?????, ????????? ?????????? ??????? ????? ????? ?????? ??????? ???????. ? ????????? ?? ?????? ?????????? ????????????? ??????-?????????? ? ??????? ??????????. ? ???? ????? ??????????, ?? ???????????? ? ??????? ?????????? ????????????. ???? ?? ?????????? ??????????, ?? ??????????? ?????????? ??????? ??????????? ???????????? ????????????? (help desc). ???????? ? ????? ????????, ????? ??? ????????? ? ???????????? ?????????? ????? ? ??? ?? ??????? ?????? ?????????? ? ?????? help desc.

????? ???????, ? ?????? ??????? ?????????????? ??????? ??????? ???????? ??, ??????????? ??????????? ?? ?? ????????? ??????, ??????? ?????? ???????, ? ????????? ?? ??????? ?? ??. ? ????? ??? ???????? ?????????? ?????? ? ???????????? ?????????.

?????????? ??????????? ?????? ??????? ?????, ???????? ??????? ??? ?????? ?????? ????????????. ??????? ?????? ???????? ??????? ????? ??????? ????????????, ? ????? ??????? ??????? ????? ?????????? ??????-????????.

??????????? ??????? ?????? ??????????:

• ????? ????????????????? ?????? ??????,
• ????? ???????? ??????,
• ????? ??????????,
• ????????????? ??????? ???????????? ? ??????? ????????,
• ? ????? ?????????? ??????.

?????????? ??????: ?????????? ????/?????, ?????????? ????/?????, ??????? ????/?????, ??????????? ????????

Session info : 
-------------- 
Os user / Oracle user / Machine :DSBOOK\dsvolk / DSVOLK / GPVK\DSBOOK 
Module / Program :SQL*Plus / sqlplusw.EXE 

Responce time: 
-------------- 
Total (s) / Work time (s) / Transactions : 34.00 / 23.40 / 1 
Service (s) / Wait (s) / Unaccounted time (s) : 1.25 / 21.40 / .75 
Service (%) / Wait (%) / Unaccounted time (%) : 5.34% / 91.45% / 3.21% 

Session stats: 
-------------- 
IO (Mb)/ Cache Memory (Mb)/ Net IO (Mb) : 10.20 / 6.34 / 1.92 

Total work : 
-------------- 
PGA Memory usage (Mb) / Total changes(Mb): 4.99 / .03

2. ????? ???????

??? ????????? ?????? ?????? ?????????? ???? ???????? ? ???????? ??????? ???????:

????? ??????? = ????? ???????????? + ????? ????????
(Response Time = Service Time + Wait Time)

?????? ??????? ???? ??????? ???????????? ? ?????? [2] (?The COE perfomance method?). ????? ???????????? ????? ???? ???????? ?? ???????????? ????????????? V$SYSSTAT ??? V$SESSTAT ??? ?????????? ?CPU used by this session?:

select a.value ?Total CPU time? 
from v a 
where a.name = ?CPU used by this session?;

????? ???????? ????? ???? ???????? ?? ???????????? ????????????? V$SYSTEM_EVENT ? V$SESSION_EVENT, ???????? ??? ??????? ????????, ?? ??????????? ????????? ?? ???:

select sum(time_waited) ?Total Wait Time? 

from v 
where event not in ();

?? ????? ????????? ???????? ???? ???????, ???????, ????????? ????????? Oracle ??????????? ???????. ??? ? «Oracle Database Reference 10g Release 2 (10.2)» ??? ?????????? CPU used by this session ???????: ???? ???????????????? ????? ?????????? ???????, ??? 10 ??, ?? ? ?????????? ????? ????????? 0 ??. ????? ????????, ??? ???????? ???????????????? ????? ??????????? ? ?????? ????????????? ????????? ??????????.

??? ??????? ???????? ????????? ????? ?? ??????: ???? ???????? ???? ????? 1 ??? ??? Oracle9i, Oracle10g ? ????? 1 ?? ??? ?????? Oracle8i ?? ? ?????????? ????? ??????? 0.

????? ???????, ??????? ????????:

????? ??????? = ????? ???????????? + ????? ???????? + ?????????? ?????
(Response Time = Service Time + Wait Time + Unaccounted Time)

Unaccounted Time ???????????? ????? ?????? ?????????. ???? ? ?????????? ???????, ???????? ??? Unaccounted Time ?????????? ???????????? ??????? ?? ??????? ????????, ??????? ?????????? ? ????????? ??????? ?????? ?????????.

2.1. ????? ??????? ?? ?????? ??????????

????? ??????? ?? ?????? ?????????? ??????? ????? ???????? ?? ?????? statspack ? ??????? ?????????? ???????:

select event, time, pctwtt from 
( 
              select 'Responce time'                                 event 
                  , (:tcpu*10000 + :twt)/1000000                     time 
                  , to_number('100')                                 pctwtt 
                from dual 
              Union 
              select 'Service time'                                  event 
                   , (:tcpu*10000)/1000000                           time 
                   , decode(:twt + :tcpu*10000, 0, 0, 
                             100 
                          * :tcpu*10000 
                          / (:twt + :tcpu*10000) 
                          )                                           pctwtt 
                   from dual 
               union 
                select 'Wait time'                                    event 
                     , (:twt)/1000000                                 time 
                     , decode(:twt + :tcpu*10000, 0, 0, 
                               100 
                             * :twt 
                             / (:twt + :tcpu*10000) 
                             )                                        pctwtt 
                 from dual 
            ) 
          order by pctwtt desc;

? ?????????, ????????? ???????????? ????? ???????? ??????????? ?? ?????????, ? ??????, ?????????? ????? ????????, ??????? ??? ????????? ?????? ????? ?????? ??????????. ??????? ?????????? ????????????? ??????? ??????? ??? ????????? ?????? ????? ???????.

2.2. ????? ??????? ?? ?????? ??????

????? ????????? ??????? ????????????? ??????? ??????? ??? ??????, ???????????????? ????????????? ??????????????? v$session_event ? v$mystat

?? ?????? ?????? ????? ????????? ???????? ?? ?????? ????????????? ??????? ???????, ?? ? ?????????? ???????? ?????????? ????????. ??????, ??????? ???-?? ??????? ???????? (????? 800 ? ?????? Oracle10g) ? ????????? (????? 300 ? Oracle10g) ?????? ?????????? ?????????? ??????. ??????? ??? ????????? ??????????? ??????? ????? ??????? ? ??????? ??????? ???????? ? ??????????.

???, ??????? apt_stat_class_t (p_statname varchar2 ) return varchar2 ?????????? ????? ??????????, ? ??????? apt_event_class_t (p_event varchar2 ) return varchar2 ?????????? ????? ????????. ???? ????????, ??? ?????? ??????? ???????? ?? ????????? ? ????????, ?????????? ? ?????? 10g, ? ??? ??????? ?????????.

????? ???????, ????? ???????? ?????????? ?? v$session_event :

? ????? ???????????? ?? v$mystat:

??? ??????? ???????????? ????????, ?? ????? ???? ?????????? ???????? ?? ?????? ?????????? ?? ????????????? CPU. ???????? ?? ????????????? ?????? ?????????? ????? ????????? ? ????????? ??????.

3. ???? ??????

3.1. ?? ?????? ??????????

??? ??? ???????????, ?? ?????? ?????????? ?????? ?????????? ??????????????? ????????? ???????????????? ??????? statspack. ?????????, ???? ?????? statspack ? ??? ?????? ????????????????? ?????????, ?? ??????????? ???????? ??????? ?? ?????? ??????????, ???????? ??? ??????? ????? ?????????? ?????? ?? ????? 5.

3.2. ?? ?????? ??????

?? ?????? ?????? ?????? ????? ?????? ????????? ???????. ? ????? [3], «Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning», ?????????? ?????? ???????? before logoff on database. ?????????????, ??? ?????????? ????????, ??????? ?????? ???????? ?? ?????????? ???????????????? ??????? ??????????, ? ?????????? ?????????? ???????? ?????. ??????, ?????????? ?????????? ? ?? ????? ????????? ????????? ?? ??????????????????. ??????? ?????????? ????? ????????? ?? ??????? ????????? ????? ? ??????? csv.

4. ?????? ??????

4.1 ?????? ???????????? ??????????? ?? ????????? ??????

??????????? ?????? statspack ????? ????????, ??? ????? ???????? ?? ?????? ?????????? ????? ????????? 40-50%. ? ??? ??????, ??? ???? ?? ? ??????? ???????? ???? ??? ?????????? ???????? ????? ???????? ? 2 ????, ???? ????????????, ? ???????, ??????? ??????? ?? ????? 20% -25%. ????????, ?????? ? ??????? ???????????????? ????????? ????, ??? ? ??????? ???????? ?? ? ?????????? ???????? ????? 20% ???????? ? ?????????????????? ????? ??????.

??????? ????????, ??? ?????? ? ?? ?????? ??????????. ???? ? ?????, ?? ??? ??? ??????????? ??????, ????? ????????? ???????????? ???????? ?????????? ?????????? (redo logs) ???????? ??? ???????. ????? ???????? ??? ????????? ???????? ????????????? ??????. ? ???? ?????? ?? ?? ?????? statspack ?????, ??? ????? ???????? ?????????? ???????????? ????? ?????? ??????? ??????. ????? ???????, ????? ??????? ??????? ?? ??????????? ??????? ????? ?????????, ??? ?? ????????? ?????? ??? ? ???????. ???? ??????????, ??????? ?????????? ???? ????????? ???????????, ?? ??????? ? ????????? ??????.

4.2. ?????? ???????????? ??????????? ?? ?????? ??????

??? ??????? ?????? ????? ????? ????????? ????????? ? ??????? ???????? on-logoff ?????? ? ????. ??????? ? ?????? Oracle9i ????? ??? ???????? ???????? ??????????????? ???????? ????????? (organization external):

4.2.1. ????? ???????????? ??????

??????? ?? statspack ??????? ?????? ??????? ???????? ?? ??????????, ?? ????? ???????????. ???? ?????? ??? ??? ? ??????? ? ???, ????? ????? ?? ??????, ? ??????? ??????? ???????? ?????????? ???????????? ??????? ??????? ???????.

??? ?????? ?????????? ??????, ????????????? ????????? ?????????: ????? ???????? ?????????? ????? 60% ?? ?????? ??????? ??????.

?? ???????? ????, ?? ?????, ??? ???? ??????, ? ??????? % ???????? ?????????? 66.62% ?? ??????? ??????.

??? ????????? ?????? ??????? ?????????? ????? ????????? ?????? ?????? ????????.

4.2.2 ????? ???????? ??????? ??????

??? ?????? ???????? ??????? ?????? ??????? ?????????? ??????????, ????? ?????????? (?????????, ????-?????, ??????, ????) ????? ?? ???????? ????? ??????. ?? ???????? ???? ?????????? ?????? ?????? ??????, ??????????? ?????????? ?????????? ??????.

?? ???????? ???? ?? ?????, ??? ?????? ???????????? DWH (warehouse) ????????? ?????????? ???-?? ?????????? ??????.

4.2.3 ??????????? ??????? ?????? ??????????

?????????? ?????????? ??????, ????? ? OLTP ??????? ???????? ????????? ?? ????? ??????. ???? ?????? ?????? ??????????? ????????? ???????? ??? ?????? (commit ??? rollback). ?????, ?? ?????????? ?????? ????? ?????????? ?????, ??????????? ?? ?????? ?????????? ?? ????????? ???????:

????? ?????????? = (????? ???????????? + ????? ???????? )/???-?? ??????????.

4.2.4 ???? ???????? ?????? ?? ????

?? ????? ???????? ?????? ??????? ??????????? ???? ?????????, ??????? ?????????? ??? ?????????? SQL*Net roundtrip:

???????? ????????, ??? ????????? from client ?????? ???????? 5 ??, ? ????????? to client — 2 ???, ??? ?????????? ?????????????? ??????? ?? ?????? ??????? ?? 1 roundtrip. ??????, ???????? ??????? ? ???, ??? ???? ?????? ???????????, ????????, ? ???????? ????? ?????? ?? ???????, ???????? SQL*Net message from client ????? ?????????????. ????? ???????, ?????? ???????? ???????? ???????? ?????? ?? ???????. ? ???????, ?????????? ?????????? SQL*Net roundtrips to/from client. ???? ???? ???????? ??????, ??? ?????????? ?????????????.

?????????????, ??? ??????????? ???????? ??????? ???????:

????? ??????? ?????? = ????? ????? ???????? SQL*Net message from client ? ???-?? roundtrip* ??????? ????? 1 rountrip.

??????? ????? 1 roundtrip ??? ?? ???????????? ?????????? ?? ?????? ??????? ?????????????? ??????. ??? ???? ????????? ??????? ??? ?????????? 5 ??.

????? ???????, ??????? ??? ??????????? ??????? ?????? ?????? ?????????????:

????? ??????? — ????? ??????? = ????? ???????????? + ????? ???????? + ?????????? ?????

?? ???????? ???? (r2.sql) ?????????? ?????? ??? ??????????? ????????:

??? ???? ?????? sqlplus, ? ??????? ? ???????? ??????? full scan ??????, ? ????? ???????????? ?????????? ??????, ??????? ?????? ???????? ???:

?? ????? ?????: ????? ????? ?????? 118 ???, ?? ??? ?????? ???? ????? 18 ??? ? ????? ??????, ? ????? 95% — ????? ???????? (???????? ?? ????).

????????, ??? ???????? ?????? ??????? ?? ???????? ??????????? ???????? ??????? ?? 1 roundtrip, ?? ??????? ??????? ? ?? ????.

5. ??????? ??????????

?????????? ? ?????? ?????? ????? ???????? ????? ???????????, ??????? ??????? ????????? ??? ??????????.

??? ??? ?????????? ???? ?????? ??????????? ? ???????? on-logoff, ?? ?????????? ????????????? ?????? ?????, ????? ?????? ????????? ?????????? ????? ?? ??. ???? ?????? ???? ?????????? ? ??????? ??????? kill, ?????????? ?? ???? ?????? ??????? ?? ?????. ??????? ????? ?????????, ??? ?? ????? ????????? ???????? ????? ???????????????? ?????? ????? ??? ????????, ? ????? ??? ????? ???????????? ??????, ??????? ?? ?????????? ? ??????? ?????????? ????????. ??? ????? ??????? ????? ?????????? ?????? ??????? ???????????? ?????????, ?????????? ??????????? ?????????? ?????? ????????????? ?? ?????????/?????????? ????????. ???, ????????, ???? ?????? ??? ???????? ?? ?????? ????????? ????????, ?? ?? ????????? ?? ?????? ?? ?? ?????????? ?????????? ??????? ????????, ??????????? ?? ?????? ?????? ????????.

???? ?????? ????????? ???????????? ????????, ??, ? ?????????, ?????? ?? ????????? ?????? ???????? ? ??????????? ?????????, ??????? ????????? ???????? ?????? ??????. ???? ?????? ?????????? ???????? ????:

?? ?????????? ??????, ?????????? ? ???-?? ??????????? ?????? ? ???????????? ???????????? ??????? ????????????? ? ????????? ????????. ???? ?? ??????????? ???????? ?? ???????? ? ??????? on-logoff, ??? ?????? ? ?? ?????????? ???????? ??????. ????????????? ???? ??????????? ??????? ????????? ??? ??????? ?????? ????????.

?? ?????? ???????????????? ?????? ??????????? ????????? ???????? ???????? ??? ?????? (commit ??? rollback). ?????? ????? ???????????? ????????????, ?????????????? ?????? ?????????? ?????? ??????. ? ???? ?????? ?? ?????????? ?????????? ????? ?? ??????-??????????, ???????? ?? ?????????? ???????? ? ?? ?????? ???. ??, ? ????????, ????? ??????? ? ??????? ???????? ??????? ?????????? ?????? ????????????????? ?????? (user calls). ???????, ?????? ???????? ??????? ????? ????????? ????????? ??????????.

6. ????????? ???????????

????, ????? ??????????? ???????????? ?????? ?????????? ????????? ?? ???????????. ??????? ????? ????????? ????? ??????????? ? ???????? on-logon. ?? ????? ????? ?????????? ????????? ?????????? ?????????? ? ??????? ?????????? ??????, ????? ????????????, ???????????? ?????????, ????? ????????? ???????, ??????? ???????? ????? ???????? ??????????? ??????.

7. ??????????

??????? on-logoff ????????????? ??? ??????? ??????????? ??? ?????? ???????????? ?????? ?? ?????????? ?????????. ??? ???? ???? ?????????? ?? ????????? ??????? ?? ?????? ???????? ?????????????. ????????? ??????????? ?????????????? ??????? ?????????? ? ??????? ????????, ????? ????? ????? ?? ?????????? ????????????. ? ???? ????? ????? ?????????? ?????????? ??????????? ??????, ??? ????? ??????????????? ?????? ???????????.

??????, ?????????? ? ??????? ????????? ??????????, ????? ????????? ??? ???????? ???????????? ????????? ?????? ?? ????? ?????????????, ? ????? ?????????? ? ??????????? ????????????? ???????????? ??????.

????????? ?????????? ?????? ???? ???????????????? ? ?????? ???? ?????????????? ???????? ??? ????????? ??????????? ???????????? ???????.

?c??????: http://dsvolk.msk.ru/oracle/tuning

Readers Comments (Комментариев нет)