


       3.       .
                   

  㦤 ன 蠣 ஥筮 ,ன ।
.    㦤 믮 ன ᫥  :
-  ⥪ ⥩
-   ᫮ 
-   ஢

믮     襭 ࠢ । ࠧ஢
 .   ⮩    ।  
, ⮡  襣 ᯮ  .

       .
     
   ଠ   :
-   
-   ᪥

᪮     ॥,祬  ,⥫쭮  ᮢ
    ,   .   襣 믮 룮
      ,祬  ᪥. ,
 樮 ⥬() ࠭祭. ன । 
砥 । 㯭     .
 ᫥ ࠨ ।  ᫥ ᫥ ४権,
।⠢   2"ன SQL-஢  ਫ" ᪮
ॡ       ਫ.    ᫥
ந ।   ᬮ७ ଠ樨   4"ன
/뢮". ।  ᫠ ⢮ /뢮,
室  ࠡ .       । ,
⮡ 믮    /뢮,   4   
믮 /뢮  䥪⨢.

  .


 ࠧ ।⠢ ᫥騥   , 
ன:
- ⥪ 
- ⥬ 쭠  (SGA)
- 

  易  饩 室  .  祭 ࠧ஢
    ⥭樠쭮   믮.   
 㦤 ⠫쭮  ⮩ .

⥪ ().
------------------------
 -    ,஥ ᮤন ଠ,室  믮
SQL-஢.  SQL- 믮 ᠬ   . 
ਪ ।⢠    ஫஢    
SQL-ࠬ  襬 ਫ. ᯮ  ஫,  ᭨
६ ᯮ  SQL-஢.

⥬ 쭠  (SGA).
-----------------------------------
SGA -  ᯥ樠쭠   , ᮤন , 
ᯮ㥬 -ᠬ. ᪮  SGA    ⠥

                                    -- 34 --



ᥬ -ᠬ,SGA ᯮ  饩 .    
  뤥  SGA     ७ ,ᯮ
SQL*DBA-:

      SQL*DBA> SHOW SGA

뢮 ⮩   룫拉 ⠪:

Total System Global Area          431384 
             Fixed Size            18236 
          Variable Size           339420 
       Database Buffers            65536 
           Redo Buffers             8197 


      .
      -----
  SGA ࠭  .  -   ,  ᮤন
 ,   ᪥. ,   ,   
 ࠧ ॥,祬  ᪠.  SGA ᮤন ᫥騥 ,  
ᮤন ࠧ ⨯ :
-  ᫮ 
-  ஢

 짮⥫  砥 ,  ,   
 ⠭ ।⢥  . 뢠 "cache hit". 
짮⥫ ॡ ,   , ந室 ஢ 
 䠩     । 㯮  .  뢠 "cache miss".
   ।⢮ "cache hit" 祢 ॥,祬 ।⢮
"cache miss".
   䥪⨢,᫨ 訢    .
᫨  筮 让,  ⭮,  ᮤন ,
訢. 稢 ࠧ ,   㢥 業 ᮢ
 ⨯ "cache hit".

             .
         

 ࠧ 뢠  ன । .  襣
䥪   ᫥ ⨬ 蠣  浪 .  蠣 ᠭ
  ⮩ .

ன 襩 樮 ⥬().
-----------------------------------------
,    ᯮ   䥪⨢,  ⢥म
᭮  ।   .   蠣   ⠪
ଠ 㬬୮ 稭  襩 , 㯭  .

ன ᨭ⠪᪮  (ᨭ)  ⥪ ⥩.
--------------------------------------------------------------------
ᯮ짮 ⥪ ⥩   ᨭ   ।
। 訬 ਫﬨ. ᨭ ।  㯠  ᫮
. ⮬  ᫥ ந ⥪   ன 
᫮ .

ன  ᫮ .
------------------------------
ன  ᫮  -  ⮩,  蠣  

                                    -- 35 --



। .  ᫮  ॡ  ⢮  
ࠢ   ஢. ,   ᫮  室
஦,祬   ஢. ⮬ 筮 ⢮  
 뤥, ० ᥣ,  ᫮ .

ன  ஢.
------------------------
᫥ ன ⠪ ⥩   ᫮    뤥
⠢ 㯭   ஢.   室
 蠣 ।  ᫥ ஡ ண .
᫥騥 室   ᤥ ⫠  ࠭ 蠣, 
᭮   ᫥ 蠣.  ਬ, ᫨  㢥稢 ࠧ
 ஢,, , ਤ 뤥   ,⮡
 ᢮   (swaping & paging).


   .

 ᫥  ன ।   ன 襩
樮 ⥬  ⠪ 楫ﬨ:
- 㬥   ᢮
- ਢ  ᮮ⢥⢨ SGA  ᭮ 
- 뤥 筮  ⤥ 짮⥫

 楫  ᭮묨  設⢥ . ,⠫ ன 襩 
  ᠬ .  ஡ﬨ ன   襩  頩
 㪮 짮⥫  ⠫樨 ⥬.

襭   ᢮.
--------------------------------
    ଠ    ᫥ :
- ॠ쭠 
- 㠫쭠 
- ७ 
- 

   ⠪ ६ ଠ     .  
ᨬ  襩   । 뢠   ᢮. 
 ᯮ   ᢮, ⮡ ࠧ 襥 ⢮
ଠ樨, ன  ⠥  ॠ쭮 .  ,   ᢮
ॡ ६. १   ᢮  ᭨
ந⥫쭮 襩 .

    ࠢ塞 ⨫⠬ . 譨   ᢮
 ⮬ 砥 ᨣ,   ଠ  ६頥  .
 ⮬ 砥 㬬ୠ  襩    筮 让, ⮡
ᮤঠ ,  祣  뤥 .  ᫥  㢥 
襩 ,  㬥 ࠭ । .

ன ⥬ 쭮  (SGA).
---------------------------------------------
᪮ 楫 SGA       ⮣ 㯠,
SGA ᥣ  室  ᭮ . ᫨ SGA 㦠  ,
    .  ᫥ 㡥,  SGA 
室     থ ᢮  .




                                    -- 36 --



।  짮⥫.
----------------------------------
     ஫஢ 稭 䨧᪮ ,
뤥塞  짮⥫. ,   짮⥫ 뤥
筮   ࠧ饭 ᮢ, 室  믮 
ਫ  .  ᨬ  襩     :

-  ᯮ 
- SGA
- ਪ ।⢠ 
- 樠 ਪ 

   ணࠬ ।⢠    ⠭ ⠪, 
  ᯮאַ ࠧ  짮⥫ﬨ  
ᯮ । 짮⥫,   ᭨ 㬬 , ॡ㥬
  짮⥫.

   (ᨭ).

 ⮬ ࠧ    ᪫ 㦭 ᨭ SQL-஢.
஫஢ ᨭ ॡ ।   ⥪
⥩.  ࠧ 㦤:
-  ⠪ ᨭ
-  易 ᨭ  ⥪ 
-  ᯮ 㦭 ᨭ
-  ।   ⥪ ⥩

 ⠪ ᨭ?
------------------
 蠣 믮 SQL- ⥬  .  SQL-,
믮塞   ࣭ ᨭ । 
믮,.. .  SQL- :

-   ஢   ࠢ쭮 ᨭ⠪  ᥬ⨪
-   ।   , 뤠訩 , ࠧ襭  
    ꥪ⠬  ,   뫠 
-  -⨬ 롨ࠥ  믮  

᫨ SQL-   ஠஢,  믮  
୮ . ᪮ ᨭ ஦ 믮 ᠬ ,
⥫쭮     ०.

⥪   .
------------------------------
믮 SQL-஢ ॡ ࠭⢠  . ᫥ 
(ᨭ) , ଠ,室   믮, 㦠 
⥪ . ⥪⭠  -  ࠡ祥 ࠭⢮  ,
ᯮ㥬  믮 SQL-஢.  -  , ।饥
⥪ .  ।   ⥪ ⥩
। ६ INIT.ORA ࠬࠬ:

OPEN_CURSORS     ࠬ । 㬬୮ ⢮ ⥪
                ⥩,      ६.
                祭  㬮砭 =50.

CONTEXT_AREA     ࠬ । ࠧ ⥪⭮ .
                祭  㬮砭 = 4096 .


                                    -- 37 --



CONTEXT_INCR     ࠬ । 㢥祭 ࠭⢠ , -
                塞  ⥪⭮ , ᫨ ଠ 
                ॢ蠥 祭,।塞 ࠬ஬ CONTEXT_AREA. 
                CONTEXT_INCR   ,    ⠭ 筮
                让,⮡   ଠ . 祭 ⮣
                ࠬ  㬮砭 = 4096 .

᫥ ᨭ     믮 ᭮   -ଠ-
 ⠥  ⥪⭮ . ,᫨ 㣨 SQL-
㦠  ⥪ ,।騥 SQL-  
࠭᫨஢  ७祭   ⥪   ⮣,⮡
 믮묨 ᭮.

। 㦭 ᨭ.
-------------------------------
  ᯮ짮 SQL-஢,⮡ ।   ࠧ 
SQL- .   ਫ  SQL஢ 
஠ ⨪.  稪 ᮮ  ᪮쪮 ࠧ 
   롮થ  ᯮ.  ଠ樨 
SQL-஢    7 "믮 ⮢ ணࠬ".

襭 ᨭ.
--------------------
᫨ -   襬 ਫ  ୮, ࠩ
㬥 ᨭ.   ஫஢  ᨭ  襬
ਫ.  ⮣ ஫ ᯥ筠   ਪ
।⢠ .

襭 ᨭ   ४.
-------------------------------------------
  ४  ஫ ⥪   ᨭ
᫥騬 ﬨ:
-  HOLD_CURSOR
-  RELEASE_CURSOR
_  MAXOPENCURSORS

 樨     ᯮᮡ:
-   ப ४
-  ४饩 ணࠬ

⨬ ﬨ   ⢮ ࠧ ᯮᮡ ࠢ ⥪⭮
  祭 믮 ணࠬ.  ஡ ଠ樨  
᫥騬 㪮⢠:
- 㪮⢮ ணࠬ   ४
- 㪮⢮ 짮⥫  PRO*Ada ४

襭 ᨭ  䥩 맮 (OCIs).
------------------------------------------------------
(ORACLE Call Interfaces)

 OCIs   믮 ஫  ᨭ  ⥪묨 ﬨ 
 ᫥ OCI 맮:
OOPEN      맮 ᮧ   ⥪ 
OSQL3      맮 믮 ⨭    ⥪ 

 ⥫쭮 ଠ樨   ᫥騬 㪮⢠:
-  㪮⢮ ணࠬ  OCI
-  㪮⢮ 짮⥫  PRO*Ada ORACLE Call Interface

                                    -- 38 --




襭 ᨭ  SQL*Forms.
--------------------------------
 SQL*Forms  ⠪  ஫஢ ᯮ   ਫ
⥪  ୮.  ஫  믮   ஢:
-  ਣ୮ ஢
-  form-஢
-   ᯮ

  ଠ樨   樨 ࠧࠡ稪 SQL*Forms.

। ⥪ ⥩.
-----------------------------------
୮ ᯮ짮 ⥪ ⥩  襬 ਫ ।
 ⢮ ᨭ  ⥪ ⥩ ॡ  ਫ.
ਬ, ਫ,஥ ୮ ᯮ ⥪  ,祬
  SQL-,  ॡ ⠪  ⥪ ⥩, 
ਫ, ஥  ᯮ  ୮. , ਫ,
୮ ᯮ ⥪ ,  ୮ ஢
SQL-, ᫨ १ ।     ࠭.
,祬 ०   ஢ , ⥬  ⥪ ⥩
 ॡ.

 㢥७,    筮 ⢮ ⥪ ⥩,
⮡ ࠧ   SQL-. ᫨  㬥蠥 ᨭ, ,
,   ⥪ ⥩.   ⠪ ॡ
㢥 ।쭮 ⢮ ⥪ ⥩, ࠧ襭  
짮⥫᪮ .   㢥  ।, 㢥稢
祭 INIT.ORA ࠬ OPEN_CURSORS. 祭 ⮣ ࠬ  
  5    255,  㬮砭 50.   ஦, ⮡  ⨬-
஢   ᢮, 뤥 ᫨誮    ⥫
⥪ ⥩.  २⢠ ⢨  SQL- 
 筮  - ⥭ ⥪ ⥩  ᪠,  
- 室   . , ,  ᯥਬ஢, -
  ⥪ ⥩  뤥     ᫮- 
    ஢.   㦤   ⮩ .


   .

 ⮬ ࠧ   ࠨ  ᫮ .  ⮬ ࠧ
ᬠਢ ᫥騥 :
-  ⠪ ᫮ 
-  ⠪  ᫮ 
-  ࠢ ࠡ⮩  ᫮ 
-   ࠡ  ᫮ 

 ⠪ ᫮ ?
-------------------------
  뫠  ᮤন ⠡    ᮤন ଠ
  ,  ,  짮⥫. ,   ᫮
 ᫥騥:
-   ⠡    ࠧ(views)
-   ⨯  ⠡  
- ࠢ  ਢ  짮⥫ 




                                    -- 39 --



 ଠ   ࠢ筠    ,
४஢騪 ਫ,  ⠪ 짮⥫.  ᫮  ⠪
 頥 ᠬ   ६ ᨭ SQL-஢.  
室  ࠡ .

 ⠪  ᫮ .
-----------------------------
᪮ ᫮  祭  訢 ,  ᮤঠ ᫮
 ।祭 ᯥ樠쭠   SGA,  뢠  ᫮
. ⮩      짮⥫.  ᫮
 ⮨  ᪮쪨 .  ᮤন ଠ  ।
ꥪ  .    ᫮  । ।-
 INIT.ORA ࠬ. INIT.ORA ࠬ,  । ࠧ 
᫮ ,  , 稭騥 "DC_".  ਬ, , 뢠騩
, ᮤন ଠ,  뢠  ⠡  .
⢮ ⮢  ⮬  । INIT.ORA ࠬ஬ DC_COLUMNS.
  ଠ樨   ᫮   INIT.ORA ࠬࠬ, 
।  ࠧ,   ਫ  " INIT.ORA
ࠬ஢".

 襣 믮 ᨭ SQL-஢  ᫮  
 筮 訬, ⮡ ᮤঠ    訢
 ᫮.

஢ઠ ࠡ  ᫮ .
------------------------------------
᫨ 짮⥫᪨ ,  믮 SQL- 砥 "cache
miss",   믮 ⥫ SQL-  ॡ
⠡    .   뢠 ४ᨢ 맮,
᪮  뢠 ᠬ ᥡ.  ᨢ 맮  ந 
 믮  ࠢ. 믮 ᫥騥 蠣, ⮡ ।
  "cache miss"  ࠡ 襣 :

1.஢ ४ᨢ 맮
2.।   ४ᨢ 맮  ⢥  "cache miss"

஢ઠ ४ᨢ 맮.
-----------------------------
⢮ ४ᨢ 맮 । ⨪ ४ᨢ 맮.
  ⨪  ᯫ   ⨫ SQL*DBA MONITOR
STATISTICS USER.  .3-1  ࠭ ᯫ.   ᬮ ࠭
믮 SQL*DBA :

SQLDBA> MONITOR STATISTICS USER

 TOT 뢠 ᮢ㯭 稭   ⨪  , 
 砫 ᬮ  ᯫ.  .3-1   ,  㬬ୠ
稭  ⨪ ४ᨢ 맮=0.

.3-1. ࠭, 砥  믮 SQL*DBA MONITOR STATISTICS
         USER.
Ŀ
                  ORACLE Statistics Monitor    June 22   14:22:55 
        ORACLE PID: 0  Session   0     User Name SYSTEM STATISTICS
 Statistic Name         CUR       AUG      MAX      MIN      TOT  
---------------------  -------   ------ --------  ------- --------
 logons                   2      null     null     null      626  
 current logons           1      null     null     null       88  

                                    -- 40 --



 cumulative opened cu    10      null     null     null     5820  
 current open cursors     9      null     null     null      945  
 user commits          0.71      0.71     0.71     0.71        5  
 user rollbacks        0.00      0.00     0.00     0.00        0  
 user calls           24.86     24.86    24.86    24.86      174  
 recursive calls       0.00      0.00     0.00     0.00        0  
 messages sent         0.71      0.71     0.71     0.71        5  
 messages received     0.71      0.71     0.71     0.71        5  
 background timeouts   0.29      0.29     0.29     0.29        2  

SQL-, 믮塞  ᫥ ᪠  , 
᫠ ४ᨢ 맮.  ४ᨢ 맮 ந室 -
⮣,   ᫮  砫쭮      ᫮
ਢ   .   ன ஢ ४ᨢ 맮
᫥  ⠭  .

᫨  ४頥  ४ᨢ 맮 ᫥ ᪠,  
᫮  ⭮   襣 ᫮ .   ⮬ 砥
஢ ࠡ ,ᯮ ⠡,ᠭ  ᫥饬 ࠧ.

V$ROWCACHE ⠡.
-------------------
⨪, ࠦ ࠡ ᫮  ࠭  ᪨
饩 ⠡ SYS.V$ROWCACHE.    ⮩ ⠡ ᮤন
⨪     ᫮ .   ⨪ ࠦ 
ࠡ ᫮   ᫥   . 騥   ⮩
⠡ ࠦ ᯮ짮-   䥪⨢  :

PARAMETER     ࠬ  ᯥ樠  ᫮ .
                祭  ⮬   
             INIT.ORA ࠬ,  । ⢮ ⮢ 
              ᫮ . ਬ,  ,  ᮤন
             ⨪   䠩 ਯ஢, 祭 
             ࠦ "dc_files"

GETS           뢠 㬬୮ ⢮ ᮢ  ଠ樨
              ᮮ⢥饬 ꥪ .  ਬ, , 
             ᮤন ⨪   䠩 ਯ஢,   
             㬬 稭 ᮢ  ᠭ 

GETMISSES      뢠 ⢮ ᮢ  , 騬
              

COUNT          ᮤন 㬬୮ ⢮ ⮢  ᯥ樠쭮
             . 祭 ⮬ -  稭 INIT.ORA ࠬ,
             ।   PARAMETER.  ਬ, ,
             ᮤন ⨪   ਯ஢ 䠩   ᮤন
             㬬୮ ⢮ ⮢   䠩 ਯ஢.
             稭 ⮬  -  祭 INIT.ORA ࠬ DC_FILES.

USAGE          뢠 ⢮ -⮢, ᮤঠ
             . ᫨    稭 - ⭠ 祭
              COUNT.

      ⠡ V$ROWCACHE.
     --------------------------
 ⠡ ⢫ SQL-஬:


                                    -- 41 --



SELECT PARAMETER, GETS, GETMISSES, COUNT, USAGE
  FROM SYS.V$ROWCACHE

 ⮣   룫拉 ᫥騬 ࠧ:

PARAMETER               GETS     GETMISSES      COUNT      USAGE

dc_free_extents         2497           127         50         50
dc_used_extents         2400           213         50         50
dc_segments             5370            31         50         30
dc_tablespaces          2430             3         25
dc_tablespaces          1222             3         25
dc_tablespace_quotas    2325             3         25
dc_ files                  0             0         25
dc_users               27481             6         50
dc_rollback_segments    2152            10         25         10
dc_objects            138267            90        175        130
dc_costraints             18             7        150
dc_objects_ids         21922           801         50
dc_tables             162269           874        150        150
dc_synonyms            12447             8         50
dc_sequences            5049             4         20
dc_usernames           25105            20          5         20
dc_columns            912753           861        600        520
dc_table_grants         7277            14         55
dc_column_grants           0             0        100
dc_indexes            108060            45         80         50
dc_costraint_defs          0             0        200
dc_sequence_grants         0             0         20
22 rows selected

 ⠡ V$ROWCACHE.
---------------------------------
 ࢮ   ⮩ ⠡  ᤥ ᫥騥 砭:

-  祭  PARAMETER "dc_extents". 稭 뢠, 
   ࢠ  ᮤন ⨪   "free extent des- criptions"

-  祭  GETS 뢠, 뫮 2497 ᮢ   "free extent
   descriptions"

-  祭  GETMISSES 뢠,  127 ᮢ   "free
   extent descriptions"     (cache miss)

-  祭  COUNT 뢠, "free extent descriptions"  ᮤন
   50 ⮢

-  祭  USAGE 뢠,  50 ⮢ ⮣  ᮤঠ
   

᫥騥  ᮤঠ ⨪  㣨  ᫮ . -
  PARPMETER    ।   ᫮ .
ਬ, 祭  PARAMETER  쥩  "dc_segments",
  ᮤন ⨪  "segment descriptions" . ⢮
⮢ ⮣  । INIT.ORA ࠬ஬ DC_SEGMENTS.
, 祭 "dc_tablespaces"     PARAMETER. 



                                    -- 42 --



   ࠧ  ⮬,  祭 INIT.ORA ࠬ
DC_TABLESPACES । ࠧ  ࠧ  ᫮ . 
   ࠦ  ⤥쭮  ⠡.

襭 㤮⢮७ ᮢ   ᫮ (cache misses).

஢ ࠡ ,  GETS  GETMISSES. "Cache misses" ᫥
業   .  ६     ᫮ 
 ᮤন . ⮬ 砥  饭 㤥  १⮬
"cache miss".    ⠭  , ⥬  稭 "cache
miss".   殢  ⨣ "⮩稢 ﭨ",  ஬ 
  室   ᯮ㥬 .  ⮬ 砥
稭 "cache misses" 㤥 쭮.   訢 
⭮襭 GETMISSES  GETS    10-15%.

᫨  ᮮ⭮襭 த 㢥稢 ᢥ ⮣ ண  ६
믮 襣 ਫ, ᫥ ᬮ  㢥祭
⢠ ⮢  ⮬ .INIT.ORA ࠬ, ।騩 ⢮
⮢   ᫮,  祭  PARAMETER.

襭 ⢠ ᯮ㥬 ⮢  ᫮ .
-------------------------------------------------------------------
᫥  COUNT  USAGE. ᫨ -   ⠡ V$ROWCACHE
 祭  USAGE ⥫쭮  祭  COUNT, 
ᢨ⥫  ⮬,    .  ⮬ 砥 
  㬥 ⢮ ⮢  . 訢 ࠧ 
    㣨  .


       .
     

 ⮬ ࠧ   ࠨ  ஢. ⮬ ࠧ
ᬠਢ ᫥騥 :
-  ⠪  ஢
-   ஢ 蠥 ᯮ
-   ࠡ  ஢
-   ࠡ  ஢

 ⠪  ஢?
-----------------------
 ஢ -    SGA,  ᮤন    .
  ᮤঠ ,   ᯮ  ᠬ. 
 ᮤঠ :
- ⠡
- ᮢ
- ⪠ ᥣ⮢
- ஢

     ᮤঠ   . ⢮ ஢ 
 । INIT.ORA ࠬ஬ DB_BLOCK_BUFFERS.  祭  㬮砭
 ⮣ ࠬ   襩     .   
 ஢ 㢥稢  㢥祭 ந⥫쭮.

  ஢ 蠥 ᯮ.
-------------------------------------
    ,  ஢ ᭨ ६, 室  㯠


                                    -- 43 --



 .  ஢   ᭮ २⢠:
-  짮⥫᪨    /뢮  ᪠, ᯮ-
   ,   ஢.

- ᪮  ஢ 室  ࠧ塞 ,   
     . ᫨   ⠥ ,  㣨 
      .

஢ઠ ࠡ  ஢.
------------------------------
 ᮡࠥ  ⨪, ࠦ   :

Logical Reads     祭 ⮩ ⨪  㬬୮ ⢮
                  ᮢ  .  稭 砥
                  , 㤮⢮७  㯥  ࠬ  .

Physical Reads    祭 ⮩ ⨪  㬬୮ ⢮
                  ᮢ  . 뢠 , 믮 
                  㯥  䠩    ᪥.

Hit Ratio         祭 ⮩ ⨪  業  饣
                  ⢠ ᮢ, 㤮⢮७ ᪫⥫쭮
                     .

 뢠 ⨪ Hit Ratio  㫥:

Hit Ratio = (Logical Reads - Physical Reads)/Logical Reads

 ⮩ ⨪,祭   SQL*DBA MONITOR I/O 
 .3-2. ⮡   ࠦ 믮 SQL*DBA 

SQLDBA> MONITOR IO

 ࠦ   :

Interval         ࠭ 뢠 Interval.  ⨪  ⮩
                ࠦ /뢮,  ந諨 ᫥
               ᫥  ࠭.

Cumulative     ࠢ  ࠭ 뢠 Cumulative ⨪  ⮩
                ࠭ ⮡ࠦ /뢮, ந諨 
               砫  .

   ,  稭 Logical Reads = 114, 稭
Physical Reads = 11  Hit Ratio = 90%.
  Cumulative 稭 Logical Reads = 3215, 稭
Physical Reads = 261  Hit Ratio = 92%.

.3-2.࠭ SQL*DBA MONITOR I/O
Ŀ
 Interval              Cumulative ĳ
%Logical %Physical %Logical            %Logical %Physical %Logical
 Reads    Reads     Urites              Reads    Reads     Urites 
0    100 0     100 0    100   PID(SNO) 0    100 0     100 0    100
        Ĵ
                                 2(1)                             
                                 3(1)                             
                                 4(1)                             

                                    -- 44 --



                                 5(1)                             
-        -         -             6(1)  =        =         =       
=        =         =             7(1)  =        =         =       
=                  =             8(1)  =        =         =       
-        =         -             9(1)  =        =         =       
=        =         =            10(1)  =        =         =       
-        -         -            11(1)  =        =         =       
-        -         -            12(1)  =        =         =       
=        =         =            13(1)  =        =         =       
                                14(1)                             
                Ĵ
     114        11       90   Totals       3215       261     2200
    0.90                      Hit Ratio                       0.92
                                                                  


襭 ⢠ 㤮⢮७ ᮢ(cache misses).
---------------------------------------------------------------
᫨  Hit Ratio ,浪 60-70%,  , ⭮,  㢥
⢮ ஢    襭 ࠡ. ⮡ ᤥ  ஢
, 㢥 祭 INIT.ORA ࠬ DB_BLOCK_BUFFERS.

  ᮡ ⨪, 業 먣 ந⥫쭮, 
  १ 㢥祭 ࠧ஢ 襣  ஢.   ⮩
⨪   業  - ⢮ ஢   .


      X$KCBRBH.
     -----------------
㠫쭠 ⠡ SYS.X$KCBRBH ᮤন ⨪,  業
믮  㢥祭 .    ⠡ ࠦ
⭮⥫쭮 襭 믮     . 
ᬠਢ  ⮩ ⠡:

INDX       祭 ⮣    ,祬 ⢮ ஢, 
               .

COUNT      祭 ⮣  -  ⢮ ⥫ "cache
           hit",    INDX+1 ஢  .

ਬ,  ࢮ  ⠡ 稭 INDX = 0  稭 COUNT ࠢ
 "cache hit",  祭   ࢮ
⥫쭮   . ன  祭 INDX = 1,  稭
COUNT -  ⢮ "cache hit"  ண ⥫쭮 .

襭  ⠡ X$KCBRBH.
---------------------------------------
⨪,ᮡ࠭  ⠡ X$KCBRBH, ஫ INIT.ORA ࠬ஬
DB_BLOCK_LRU_EXTENDED_STATISTICS. 祭 ⮣ ࠬ ।
⢮ ᥩ  ⮩ ⠡.  㬮砭 稭 ⮣ࠬ = 0,
 砥 " ᮡ ⨪".  ⮡ ࠧ ᡮ ⨪  
⠡, ⠭  稭 ⮣ ࠬ. ਬ,᫨ 
稭 = 100,  ᮡ 100 ᥩ ⨪,  砥
  .





                                    -- 45 --



 ⮩ ⨪   ᮡ  ॣ㧪.  ॣ㧪 
ࠡ ய樮쭠  ᥩ  ⠡.  ࠩ ⨪
⮫쪮  ६ ன  ஢.    ࠨ, 
ᡮ ⨪  襭 ந⥫쭮.


 ⠡ X$KCBRBH.
------------------------
 ଠ樨  ⮩ ⠡   ।᪠ ⥭樠
먣  㢥祭 ࠧ . ਬ, ⮣, ⮡ ।
⢮ ⥫ "cache hit",  ,᫨  
20 ஢  ,   ⠡ ।⢮ SQL-:

SELECT SUM(COUNT) ACH
   FROM SYS.X$KCBRBH
   WHERE INDX < 20

  ⠪ ।  ࠧ  ⥫ "cache hit"
 Hit Ratio. ᯮ ᫥    Hit Ratio,
᭮뢠  稭 ⨪ Logical Reads  Physical Reads  ⢥
⥫ "cache hit"(ACH), 祭  :

Hit Ratio = (Logical Reads - Physical Reads + ACH)/Logical Reads

㯯஢ ᥩ  ⠡ X$KCBRBH.
-----------------------------------------
㣨 ᯮᮡ ᫥ ⮩ ⠡  㯯஢
⥫ ஢  訥 ࢠ.    ⠡
X$KCBRBH ᫥騬 SQL-஬:

SELECT 250*TRUNC(INDX/250)+1'to'250*(TRUNC(INDX/250)+1) INTERVAL
SUM(COUNT) CACHE HITS
     FROM SYS.X$KCBRBH
     GROUP BY TRUNC(INDX/250)

 ⮣   룫拉 ⠪:

INTERVAL        CACHE HITS

1 to 250             16080
251 to 500           10950
501 to 750             710
751 to 1000          23140
:

INTERVAL            ࢠ ⥫ ஢   .

CACHE HITS          ⢮ ⥫ "cache hit",-
                   祭   ஢   INTERVAL.

᫥ 뢮 ⮣    ᫥騬 祭:

-  ᫨   㤥  250 ஢,  㤥 祭 먣  16080
   "cache hit".

-  ᫨ 㤥   250 ஢,..  㬬 500 ஢,  㤥 
    10950 "cache hit" ⥫쭮  ࠭  16080.


                                    -- 46 --



-  ᫨   250 ஢,.. ᥣ 750 ஢,  ⥫쭮
   ਡ  ⮫쪮 710 "cache hit",   㬬 27740 "cache hit".

-  ᫨   250 ஢  1000,  먣 㤥 23140 "cache hit",
     㬬 50880 ⥫ "cache hit".

᭮뢠  ⮬ ,  ᫥   ⢮ ஢
  .  ਭ ᫥饥 襭:

-  㬭  250  500 ஢  ᨬ   ᮢ
   .    ᯥ ⥫ 먣
   ந⥫쭮.

-  ࠧ㬭  750 ஢. 먣 ⥫  ࠢ 
   ।騬 ,  ⮬ ,뤥   , 
     ᯮ짮  㣨  .

-  㬭  1000 ஢,᫨   . 먣
   ⥫  ࠢ   250,500  750 ஢

 㦭 ஢.
---------------------------
᫨  Hit Ratio ᮪, , ⭮  ࠧ஢, ⮡ ᮤঠ
  訢 .  ⮬ 砥   㬥 ࠧ
  -⠪ ᯥ  ந⥫쭮. ⮡  ᤥ,
㦭 㬥 稭 INIT.ORA ࠬ DB_BLOCK_BUFFERS.  
 ࠧ   㣨  .   ᮡ
⨪,⮡ ।᪠ ந⥫쭮  ஢  訬
ࠧࠬ. ᫥ ⮩ ⨪    । 
訬      ⥫쭮 ᭨ ந⥫쭮.

 X$KCBCBH.
-----------------
㠫쭠 ⠡ SYS.X$KCBCBH ᮤন ⨪  業 ந⥫-
  㬥襭  ஢.  ⠡ 筠  
।饩.  ᮤন ᫥騥 :

INDX       祭 ⮣    ,祬  ⢮
           ஢ .

COUNT      祭 ⮣  -  ⢮ "cache hit",  ।塞
           ⢮ ஢ INDX+1.

⢮ ᥩ  襩 ⠡ ⭮  ஢  襬
.   ⠡ ࠦ ⢮ "cache hit" ᢮⢥
 . ਬ, ன  祭 INDX = 1, 祭 
COUNT -  ⢮ "cache hit"  ண .  쥩 
祭 INDX = 2, 祭  COUNT ࠢ  "cache hit"  3
. ࢠ  ⠡ ᮤন ᯥ樠 ଠ, 祭
INDX = 0  祭 COUNT ࠢ 㬬୮  , 饭 
   .

襭  ⠡ X$KCBCBH.
---------------------------------------
 ⨪  ⮩ ⠡ ஫ INIT.ORA ࠬ஬
DB_BLOCK_LRU_STATISTICS. 稭 ⮣ ࠬ । ᮡࠥ  
⨪. 祭  㬮砭 ⮣ ࠬ ࠢ FALSE,  砥,
 ⨪  ᮡࠥ.  ࠧ襭 ᡮ ⨪ ⠭ 

                                    -- 47 --



ࠬ  ﭨ TRUE.   ⨪ ਢ   殮
ࠡ. ࠩ ⨪,   ࠨ  ஢.    
ࠨ,   ᡮ ⨪  㢥祭 ந⥫쭮

饭  ⠡ X$KCBCBH.
-----------------------------
室  ଠ樨 ⮩ ⠡   ।᪠ ⢮ ⥫-
 "cache miss",   ,᫨ ⢮ ஢  
㤥 㬥襭. ᫨   ஢ ᮤন 100 ஢,  
㧭  ⢮ "cache miss" ,᫨  㤥 90.  ।
⥫ "cache miss"   ⠡ ᫥騬 ஬:

SELECT SUM(COUNT) ACM
    FROM SYS.X$KCBCBH
    WHERE INDX >= 90

  ⠪ । Hit RAtio, ᭮뢠  ⮬ ࠧ .
ᯮ ᫥    Hit Ratio,ᯮ  Logical
Reads  Physical Reads   ⢥ ⥫ "cache miss(ACM)",
祭  :

Hit Ratio = (Logical Reads - Physical Reads - ACM)/Logical Reads

㯯஢ ᥩ  ⠡ X$KCBCBH.
---------------------------------------
㣨 ᯮᮡ ᫥  ⠡  㯯஢ 
ࢠ. ਬ,᫨   ᮤন 100 ஢,  ࠧ 
 4  ࢠ.  ⠡  ⢨ ஬ :

SELECT 25*TRUNC(INDX/25)+1' to '25*(TRUNC(INDX/25)+1) INTERVAL
SUM(COUNT) CACHE HITS
     FROM SYS.X$KCBCBH
     WHERE INDX > 0
     GROUP BY TRUNC(INDX/25)

,  WHERE । ।頥  ⨪  ࢮ
.  ⮣   룫拉 ᫥騬 ࠧ:

INTERVAL         CACHE HITS

1 to 25                1900
26 to 50               1100
51 to 75               1360
76 to 100               230

:
INTERVAL      ࢠ ஢  

CACHE HITS   ⢮ "cache hit",ᮮ⢥ ࠬ   INTERVAL

᫥ १ ⮣   ᤥ ᫥騥 뢮:

-  ᫥ 25 ஢   ( 76  100) 뤥 230 "cache hit". ᫨
    㬥   25 ஢,   230 "cache hit".

-  ⨩  ࢠ  51  75 ਢ   1360 "cache hit",᫨
       .   ⥫쭮  230,.. 㬥襭
    50 ஢ ਢ  㬬୮  1590 "cache hit".

                                    -- 48 --



-  ன  ࢠ ( 26  50) ਢ   1100 "cache
   hit",᫨  ࢠ ஢   .  㬬  ।騬 
   㤥 2690 ﭭ "cache hit".

-   25 ஢ (c 2  25) ਢ   1900 "cache hit",᫨ 
   ࢠ ஢   .   ஢ ਢ  
    "cache hit".

᭮뢠  ⮬ ,   㬥   ࠧ .
  ᫥騥 襭:

-  ᫨  ࠭祭, ࠧ㬭  25 ஢  .    76
    100  ࠢ⥫쭮  "cache hit".    ஢ 
    ⥫쭮 ࠡ ,   ᢮   
   ᯮ짮 㣨 ।⢠ .

-  ࠧ㬭 㬥  ,祬  25 ஢. ਬ, 㬥襭 
   50 ஢ ਢ  ⥫쭮 襭 ࠡ , ..ﭭ
   "cache hit" ⠢ ⥫   饣 ⢠.

      .
     

᫥       ॡ㥬 ࠧ஢, 業
 ࠡ ⥪ ⥩,  ᫮    ஢. ᫨ 
㬥訫 室     ,    뤥
   㣨 . ਬ,᫨  㬥訫 ࠧ 
஢,    룮  ⥫쭮 ।⠢
  ⥪ . ன  樮 ⥬ ᭮.

  㦭 ࠧ஢     
ॡ   . ᮡ 㡥,  譥  
᢮. ਬ, ᫨ ࠧ  ᫮    ஢ 㢥稫
SGA     ᫨誮 让 ࠧ  ᭮ ,  ⮬
砥 SGA  ࣭ ᢮  .

  ।    㦨, ࠧ 
  ॡ  ,祬  ।⠢ 樮
⥬.  ⮬ 砥    ࠡ  ⮫쪮 
 襬 .

                  4-1     ன  

      4.            /.
                        

  ।⠢ ⨩ 頣 ஥筮 :
ன /뢮 (I/O).

      㧪  I/O,   
 ࠡ  ᨬ쭮 ந⥫쭮.

 ⮩    :
-  ᭨ ᪮ 䫨
-  । ࠭⢮   
-   ᪮ ࠢ ࠭⢮



                                    -- 49 --



          /.
        
믮  ணࠬ ਫ 筮 ࠭稢 ᪮ I/O.
 ࠡ 業ࠫ쭮    ঠ   
ࠡ I/O.  ਫ 뢠 "࠭祭 /뢮".

 6.0   ஥஢ ⠪ ࠧ, 믮  
 ࠭祭 I/O.  ந I/O ᫥ । ,
४   3.  3 뢠   । ,⮡
᭨  㬠 I/O. ᫥ ⨦ ⮣ 㬠 ᫥ 
⮩ ,⮡ 믮 室 I/O 䥪⨢ ᪮쪮 .

             (DBWR).
        
DBWR -  ᭮  ,⢥⢥  ࠢ  ஢.
DBWR  뢠 ஢     ஢,
室  SGA   䠩    ᪥. DBWR 믮 
 ᪮ I/O,室  ࠡ  .

 ᪠  ᨨ 6.0,।⢮ 6.027 ࠢ  ஢
뫮 ᭮ 楫 ன  .   ᨨ 6.027  DBWR.
DBWR ⥯ ࠢ  ஢  䥪⨢   ॡ ன.

 ⥫쭮 ଠ樨 ⭮⥫쭮 ஫ DBWR  ࠢ  ஢
  ਫ   樨.

          .
        
 ⮬ ࠧ   ᭨ ᪮ 䫨.  ⮬ ࠧ
㦤 ᫥騥 :
-   ⠪ ᪮ 䫨
-   ᫥  ࠡ⮩ ᪠
-   㬥 㧪 ᪠

         ⠪ ᪮ 䫨?
        ----------------------------
᪮ 䫨    ᮢ     ⮬
  ६.
設⢮ ᪮  ࠭祭  ⢮ 㯮  㬬୮
⢮ , ஥   ।  ᥪ㭤.   ⨣
 ࠭祭,  㦤  㯠  .

          ࠡ⮩ ᪠.
        ----------------------------
 ᪠ ࠦ :
-  ORACLEFILE I/O ⨪
-  ⨪ 樮 ⥬

   ORACLEFILE I/O ⨪,  ࠦ   䠩
.   ⠪  ࠭ ⨪  ᪮ ᮢ  ᥬ
䠩.

         ࠡ ᪠  .
        ----------------------------------
஢ ᪮ 饭  䠩    SQL*DBA MONITOR FILE
I/O. .4-2   ࠭. 祭 ⮣ ࠭ 믮 SQL*DBA
: SQLDBA > MONITOR FILE SQL*DBA MONITOR FILE I/O ⮡ࠦ ᯨ᮪ 


                                    -- 50 --



䠩  ,   頫  ,  ⨪,
ࠦ饩  I/O ⨢.   䠩   뤠
⨪:

Request Rate        । ⢮ ⥭   䠩
Read/s                ᥪ㭤

Request Rate        ⢮ ᥩ   䠩 
Write/s              ᥪ㭤

Batch Size          । ⢮  ,뢠
blks/W               䠩     

㬬୮ I/o ᮮ⭮襭   ᪠  㬬 Request Rate Read/s 
Request Rate Write/s   䠩  , ࠢ塞  
室  ⮬ ᪥. ।  稭     ᪮.

। ⠪ 㬬୮ ⢮ , 뢠   
ᥪ㭤.   䠩 ⢮ , 뢠  ᥪ㭤,
। ࠬࠬ Request Rate Write/s  Batch Size blks/W.  㬬୮
⢮ , 뢠   ,  㬬 ,
뢠   䠩.

.4-1  ࠭  SQL*DBA MONITOR FILE I/O
Ŀ
                      ORACLE FILE I/O MONITOR                     
          Request Rate   Batch Size    Response Time  Total Blocks
File Name Read/s Write/s blks/R blks/W  Read  Write   Read Written
    Ĵ
DISKSMKT4:[HOMAIL]V60MAIL_SYS1.ORA                                
            0.40    1.00   1.00   1.00  0.00   0.00   4705    2803
DISKSMKT3:[HOMAIL]V60MAIL_TBL_A01.ORA                             
            3.60    1.20   1.00   1.00  0.00   0.00  20477    3255
DISKSMKT1:[HOMAIL]V60MAIL_TBL_B01.ORA                             
            8.20    1.20   2.00   1.00  0.00   0.00  32127    2884
DISKSMKT2:[HOMAIL]V60MAIL_TBL_B02.ORA                             
            4.20    2.80    1.00  1.00  0.00   0.00  20364    3208


          ࠡ⮩ ᪠  樮 ⥬.
        ---------------------------------------------------
  , ࠭騥  ᪥  䠩 ୠ ॣ樨 
ᮤঠ ᯮ⥫ 䠩,   ⭮  .饭 
⠪ 䠩      , 祬 SQL*DBA-ࠬ.
ᯮ ।⢠   ஢ન 㬬୮ I/O   ᪮.
ࠩ ᭨ ᫮   ᪠,ᮤঠ騬 䠩 .

        । /뢮(I/O).
        --------------------------------
஠ ⨪  ࠭ SQL*DBA MONITOR FILE I/O  祭
।⢠ .   㬥樨, 뢠饩  ।⢠,
। ।쭮   ᪮.  ᪨, ࠡ騥  ஢
 㧪 ⥭樠쭮  ⭥  䫨. ਬ, 40 
 I/O  ᥪ㭤  㦥 譥  設⢠ ᪮  VMS  UNIX
樮 ⥬.  ⮡ 㬥 ࠡ  ॣ㦥 ᪥,
६     ᯮ㥬 䠩  



                                    -- 51 --



⨢ .  ᯮ  ਭ樯     ᪮  
     㬬୮ ⢮ I/O.  뢠
। /뢮.

 ࠧ  ᮢ  । /뢮:
-   䠩    䠩 ୠ ॣ樨  ࠧ ᪠
-    "stripe" ⠡   ࠧ ᪨
-   ⠡    ࠧ ᪨
-   ᪮ I/O ⭮騥   

         䠩    䠩 ୠ ॣ樨.
        -----------------------------------------------------------
  ﭭ   䠩   䠩 ୠ
ॣ樨. ᫨  䠩 室   ᪠,   ਢ 
᪮ 䫨⠬.

ᯮ  䠩    ⤥쭮 ᪥.   ⥯
   ࠧ 䠩   ६  ᪮
䫨⮢.

ᯮ  䠩 ୠ ॣ樨  ⤥쭮 ᪥.  ୠ
ॣ樨 뢠 ।⢮ Log Writer Process (LGWR)  ࠭樨.
 LGWR 뢠 䠩 ୠ ॣ樨 ᮣᮢ,   
 ॥,᫨    ६ 㣨 饭  ⮬ .

뤥 ⤥쭮 ᪠  䠩 ୠ ॣ樨 筮 ᯥ稢
 믮 LGWR  室 쭥襩 ன.   
LGWR  ।.  ଠ樨  LGWR 頩   5 "ன
䫨⮢".

뤥 ⤥쭮 ᪠  䠩 ୠ ॣ樨  ⠪ 
।⢮ ।஦.   㢥७,  ६ 䠩 
  䠩 ୠ ॣ樨      ਨ.

        "Striping" ⠡ .
        ----------------------------
"Striping" -  ᯮᮡ ࠧ  ⠡   쪨 樨
   ࠧ 䠩    ࠧ ᪠.

  ࠧ ᠬ   ࠧ  ⠡
६  ᪮ 䫨⮢.  "Striping" ᮡ  
⨬஢ 砩 㯥  ⠡栬  訬 ⢮ ᥩ.
 ᮧ "striped" ⠡:

1. ࠭⢮ ⠡ ஬ CREATE TABLESPACE.   䠩
     ࠦ DATAFILE.  䠩    ⤥쭮 ᪥.

   CREATE TABLESPACE STRIPEDTABSPACE
           DATAFILE 'FILE_ON_DISK_1' SIZE 500K,
                    'FILE_ON_DISK_2' SIZE 500K,
                    'FILE_ON_DISK_3' SIZE 500K,
                    'FILE_ON_DISK_4' SIZE 500K,
                    'FILE_ON_DISK_5' SIZE 500K

2.⥬ ᮧ ⠡ ஬ CREATE TABLE.   ᮧ
  ⠡筮 ࠭⢮  ࠧ TABLESPACE.   ⠪ ࠧ ⠡
  ⥭⮢  ࠧ STORAGE.    ⥭  ⤥쭮 䠩


                                    -- 52 --



  .
   ⥭   ⥫쭮 , 祬 䠩
    ࠭⢥ ⠡,  ॣ㧪. ਬ:

   CREATE TABLE STRIPEDTAB
           ( COL_1 NUMBER(2),
             COL_2 CHAR(10)   )
           TABLESPACE STRIPEDTABSPACE
           STORAGE ( INITIAL 495K NEXT 495K
                     MINEXTENTS 5 RCTINCREASE 0 )

⮬  ⢨  ᮧ ⠡ STRIPEDTAB.  ⠡
ᮤন 5 ⥭⮢,  ࠧ஬ 495 .  ⥭  
 䠩,   ࠧ DATAFILE  CREATE TABLESPACE.  
䠩 室  ⤥ ᪠.  ⥭ । ,
᪮ MINEXTENTS ࠢ 5.  ⥫쭮 ଠ樨  MINEXTENTS 
㣨  ࠬࠬ   ࠧ "  ⥭" 
⮩ .

         ⠡  ᮢ.
        -----------------------------
頩  訢     ࠧ 䠩 
   ࠧ ᪠. ⮡  ᤥ,     
   ᯮ. ਬ, ࠧ  ᯮ㥬 ⠡
  ᮢ.  ࠧ । /뢮  ⠡  ᮢ
 ࠧ ᪠.   ࠧ ⠡   믮 ᫥騥 蠣:

1. ࠭⢮ ⠡ ஬ CREATE TABLESPACE.
   䠩    ࠧ DATAFILE:

  CREATE TABLESPACE TABSPACE_1
         DATAFILE 'FILE_ON DISK_1'

2. ⠡ ஬ CREATE TABLE.  ࠭⢮ ⠡ 
  ࠧ:

  CREATE TABLE TAB_1
          ( COL_1  NUMBER(2),
            COL_2  CHAR(10)  )
  TABLESPACE TABSPACE_1

3. 㣮 ࠭⢮ ⠡.  䠩    㣮
  ᪥:

  CREATE TABLESPACE TABSPACE_2
         DATAFILE 'FILE_ON_DISK_2'

4. .   ⠡筮 ࠭⢮:

  CREATE INDEX IND_1 ON TAB_1 (COL_1)
         TABLESPACE TABSPACE_2

 蠣 ਢ  ᮧ ⠡ TAB_1  䠩 FILE_ON_DISK_1  ᮧ
 IND_1  䠩 FILE_ON_DISK_2.

        ࠭ 㣨 ᪮ /뢮 (I/O).
        ------------------------------------------------
᫨ , ࠭ I/O  ⭮騥    ᪠, ᮤঠ

                                    -- 53 --



-䠩.     ⮫쪮 㬥 䫨 ⮣ ᪠,  
 ஫஢ ࠡ  ⠪ ᪥   SQL*DBA MONITOR FILE
I/O.

            .
        
     ࠭   .  ⮬ ࠧ 
 । ࠭⢮     
ந⥫쭮 ࠡ.  㦤 ᫥騥 :
-   ࠭   
-   ஫஢ ࠭ 
-   ࠭   䥪⨢   ਫ

        ଠ  .
        --------------------
   ᮤঠ ࠭⢮ :
-  饩 ଠ樨  
-  ଠ樨  ஢ ⠡  ୮ , ᫨  ࠭
   ஢ 
-  ଠ樨    
-   㠫쭮 

        INIT.ORA ࠬ஬
DB_BLOCK_SIZE.  祭 ண ६   , ⢥ 
 㠫쭮 ,   .  ࠭⢮ ⠥
᢮(free space).  ⥫쭮 ଠ樨  ଠ  
頩   5 "ꥪ 짮⥫  " 㪮⢠
  .

        ࠢ ࠭⢮  .
        --------------------------------------
SQL- INSERT,UPDATE  DELETE   㬬୮ ࠭⢮ 
 , ᮤঠ饬 .  ࠭⢮ ᯮ :
-   INSERT    ⠡,  ᯮ  
-   UPDATE 㢥稢 㬬 ,  㦥 ࠭  

࠭⢮ ᢮ :
-   DELETE 㤠 ,࠭騥  
-   UPDATE 㬥蠥 㬬   ,  ࠭  

        窠 .
        ---------------
᫨  UPDATE 㢥稢 㬬    ⠪,   㦥 
ᯥ稢 ࠧ饭  , 㣮    ᯮ짮 
࠭ ⠢ .  뢠 楯 ᥩ(chaining rows).
᪮ । , ᮡ 楯, ࠪ୮  ࠡ
 , ।⠢   ஫  ࠭⢮,
ᯮ㥬  .   ॣ㫨஢ ᯮ짮 ࠭⢠
⠪, ⮡ 뫮 筮 ᢮ ࠭⢠    
ࠧ襭 ७ ᥩ, ࠭  .

         ᯨ᪨.
        -----------------
 ᯨ᮪ -  ᯨ᮪  , ᮤঠ ᢮ ࠭⢮.
 ᯨ᪨ ᯮ  ࠭ ᫥⥫쭮 , 
筮 ᢮ ࠭⢠  ࠧ饭  ᥩ.  ⠡
     ᢮ ᯨ᮪.    ⠢
  ⠡,  ᭠砫      ᢮ -

                                    -- 54 --



࠭⢮  ࠭ .  ⠪ ,     ᢮
ᯨ᪮ ⠡. ⢮ ᢮ ᯨ᪮  ⠡ ।
稭 INIT.ORA ࠬ FREE_LIST_PROC  ६ ᮧ ⠡.

           ᢮ ᯨ᪠.
        ---------------------------------
騥  ࠬ ஫   㤠   
᢮ ᯨ᪮  ⠭ । 㬬୮ ᢮ ࠭⢠ 
 :

PCTFREE       業   , १ࢨ஢  ࠧ饭
              ᥩ. ᫨ 業   , ᮤঠ饣
             ᢮ ࠭⢮,   PCTFREE,  㤠 
             ᢮ ᯨ᪠         
             .  騥   .  祭
             ⮣ ࠬ  㬮砭 = 10%.

PCTUSED       㤥  ࠭  業   ,
             ᮤঠ騩 . ᫨  業   , ᮤঠ騩
             ,   PCTUSED,   頥  ᢮ ᯨ᮪
                     . 祭 
             㬮砭 = 40.

  । 稭  ࠬ஢ PCTFREE  PCTUSED  ⠡,
    ६  ᮧ.  ⠪  । 祭
 㬮砭  ६ ᮧ ࠭⢠ ⠡. 㬬  ࠬ஢ 
 ॢ 100%.

PCTFREE  PCTUSED   ।  Data Definition Language (DDL)
:
-  CREATE TABLE
-  ALTER TABLE
-  CREATE CLUSTER
-  ALTER CLUSTER

PCTFREE ࠬ   ⠪ ᯮ짮   CREATE INDEX 
। 稭 ᢮ ࠭⢠, 饣    
६ ᮧ .  ᢮ ࠭⢮   ᯮ짮 
 ࠧ饭   祭, ⠪  ७ 
 .

        ਬ ࠢ ࠭⢮  .
        ---------------------------------------------
 ਬ    ࠧ  頥    ᢮
ᯨ᮪  㤠  㤠, ᭮뢠  祭 ࠬ஢ PCTFREE 
PCTUSED. ᬮਬ SQL-,  ᮧ ⠡ DEPT:

CREATE TABLE DEPT
    (  DEPTNO NUMBER(2),
       DNAME  CHAR(14),
       LOG    CHAR(13) )
   PCTFREE 5 PCTUSED 75

ࠢ ࠭⢮   ⠡ DEPT । 稭 PCTFREE =
5  PCTUSED = 75.  १ࢨ 5%    ७ ᥩ  -
ࠥ ࠭ ,   75%    ਭ  ,
᫨   5%   ᢮. ᫨ -    ,
祬  95%,  㤠   ᯨ᪠ ᢮ . ⠢襥 ᢮

                                    -- 55 --



࠭⢮    㢥祭  㦥 ࠭ ᥩ.
᫨   㤠 筮 ⢮ ,  㤠 
ᥩ,  㬥襭 㬬    ⠪,  ᯮ 
75%  ,       ᯨ᮪ ᢮.  ⮬ 砥
  ਭ    ᢮ ࠭⢮  ⠭ 
5%.

        롮 ।  ᢮ ࠭⢠.
        -------------------------------------------
   ࠡ  権  , ⠭ 
ࠢ ࠭⢮    .  ⮣, ⮡  ᤥ,
     訬 ਫﬨ.  ᮡ,    
樨   믮       ⠡.

        롮 PCTFREE.
        --------------
稭 ࠬ   ࠢ ࠭⢮  , 
室  ᢮ ᯨ᪥.  稭 ࠬ PCTFREE 
믮  ᪠஢ ⠡.  ⠡ 㦠  
祭 ⮣ ࠬ, ⠡   㤮⢮ 訬
⢮ , ᪮  ⠢  ᢮ ࠭⢠ 
  .   ᪠஢ ⠡ ⮬   ⠡,
  襬  . 祭  祭 PCTFREE 
ᯮ짮  ⠡,  ⮫쪮 訢   .
  㢥稢 ⭮ 楯 ᥩ,  .
 稭 PCTFREE ᭨ ⭮ 楯 ᥩ.  ⠡
㦠  訬 祭 ⮣ ࠬ,   ⠢ 
᢮ ࠭⢠  ७ ᥩ.

  , ᫨  ᮤঠ  ६   
㫥묨 祭ﬨ.

⠢ ࠭⢮  ७ ᥩ,  ⭮ 楯
 ᥩ  㣨 .   讥 祭 ⮣
ࠬ 㢥稢 ⢮ ,   ⠭ 
 ᪠஢ ⠡

        롮 PCTUSED.
        --------------
稭 PCTUSED   ࠢ ࠭⢮  , 
㤠  ᯨ᪮ ᢮.

 祭 ࠬ ᭨ ⭮ 楯  ᥩ
   .  ந室 ⮬,     ⭮ 
᢮ ᯨ᪨,     ᢮ ࠭⢠.  砥,
   ᢮ ᯨ᪥    ᢮ ࠭⢠, ⮡
  .

 稭 ࠬ 㬥蠥 㬬୮ ᢮ ࠭⢮  ,
室  ᢮ ᯨ᪥. ந室 ⮬,    
⭮  ᢮ ᯨ᪨,     ᢮ ࠭⢠.
 稭 ࠬ  ࠭   䥪⨢, ᯮ
᢮ ࠭⢮, ,  㢥稢 ⭮ 楯 
ᥩ. ᮪ 祭 ⮣ ࠬ ⠪   㢥祭 ,
᪮ன   ⭮  ᢮ ᯨ᮪.  ⪮ ࠢ
᢮묨 ᯨ᪠ ॡ ६ 믮   ᭨ 䥪⨢
ࠡ .


                                    -- 56 --




         .
     
 ᮧ ꥪ  , ⠪  ⠡,   뤥
࠭⢮.  ࠭⢮ 뢠 ᥣ⮬. ᫨ ᫥騥 樨
    㢥祭   ࠭⢠ ⠭ 筮,
   ᥣ.  ᪮ ७  ᭨
ந⥫쭮.   ࠧ 㦤:
-   । ᥣ
-   ᥣ  ᪨
-   㦨 ᪮ ७
-   । ࠭⢮   , ⮡  ᪮
   ७

          ⥭.
        --------------------
 ࠭  ᥣ ᫥騥 :

     -    ࠭     ⠡ 
                      
 ᥣ -   ᥣ ࠭     
⪠ ᥣ  -  ⪠ ᥣ ࠭ ଠ, 室 
                      ⠭    

६ ᥣ -  ६ ᥣ ᯥ稢 ६ ࠭⢮
                       , ⮡ 믮 ᮮ⢥騥 樨 
                      , ਬ ⠪, ஢

㧮 ᥣ-  㧮 ᥣ ᮤন ᠭ ⠡ ᫮
                      ,  㦠    

 ࠧ  쪨 , 뢠 ⥭⠬.
⥭ ⮨  뢭  . ⢮  ࠧ ⥭⮢ 
ᥣ । ।⢮  ࠬ஢   ᮧ
ᥣ.  ࠬ ᫥騥:

INITIAL        ࠬ । ࠧ ࢮ  砫쭮 (initial)
              ⥭  ᥣ. 砫 ⥭ ᥣ 뤥 
              ᮧ ᥣ.  稭 ࠦ  , 祭 
              㬮砭 10240 .  稭 ⮣ ࠬ 㣫 
              襬 ࠧ  .   
              । INIT.ORA ࠬ஬ DB_BLOCK_SIZE.

NEXT           ࠬ । ࠧ ண   ᫥
              ⥭⮢  ᥣ. 祭 ⮦ ࠦ  . 祭
               㬮砭 10240 .  稭 ⮣ ࠬ 㣫 
              襬 ࠧ -.

MINEXTENTS     ࠬ । 쭮 ⢮ ⥭⮢ 
              ᥣ.  ⥭ 뤥  ६ ᮧ ᥣ.
              祭  㬮砭 = 1.

MAXEXTENTS     ࠬ । ᨬ쭮 ⢮ ⥭⮢ 
              ᥣ. 祭  㬮砭 = 99.





                                    -- 57 --



PCTINCREASE    ࠬ । 業 㢥祭 ᫥
              ⥭⮢. ᫥ ண ⥭  ⥭ 㢥稢 
              稭 ⮣ ࠬ.  祭  㬮砭 = 50.  ᫨ 
              ࠬ = 0,   ⥭ ᫥ ண  ࠧ NEXT.

   稭  ࠬ஢  ࠦ STORAGE
SQL-஢,  ᮧ   ꥪ  :
- ⠡
- 
- 
- ⪠ ᥣ

ਬ, ᬮ CREATE TABLE-:

CREATE TABLE ACCOUNTS
     ( ACC_NUM NUMBER,
       BALANCE NUMBER(11,2) )
     STORAGE ( INITIAL 100K  NEXT 50K
               MINEXTENT 1  MAXEXTENTS 50
               PCTINCREASE  5 )

  ᮧ ⠡ ACCOUNTS  砫 ⥭⮬  100 , ..
祭 ࠬ INITIAL ࠢ 100 .  ண ⥭  
50, ᫨ 室, .. 祭 ࠬ NEXT. ⮡  ࠧ
쥣 ⥭, 㢥 ࠧ ண ⥭  5%,稭
PCTINCREASE. ⠭ ࠧ  3 ⥭ = 52,5 . ⮡ 
⢨⥫ ࠧ 3 ⥭, 㣫  ࠧ  襣 ࠧ
 .

࠭塞 祭 ࠬ஢  ⠪  ।  ࠧ DEFAULT
STORAGE ᫥ DDL-஢:
-  CREATE TABLESPACE
-  ALTER TABLESPACE

ਬ, ᬮਬ  CREATE TABLESPACE:

CREATE TABLESPACE TABSPACE_2
    DATAFILE 'TABSPACE_FILE2.DAT' SIZE 20M
    DEFAULT STORAGE ( INITIAL 10K  NEXT 50K
                      MINEXTENTS 1  MAXEXTENTS 999
                      PCTINCREASE 10 )
    ONLINE

稭, ।  ࠧ DEFAULT STORAGE  । 
࠭塞 ࠬ஢ ᥣ⮢, ᮧ  ࠭⢥ ⠡. ᫨ 
ᮧ ⠡  ࠭⢥ TABSPACE_2  ᯮ짮 ࠧ STORAGE,
࠭塞 ࠬ ⠡筮 ᥣ  । 稭  ࠧ
DEFAULT STORAGE  CREATE TABLESPACE.

        ७ ᥣ.
        --------------------
 뤥 ᥣ  ⥭  ६ ᮧ ᮮ⢥ ꥪ⮢
 . ਬ,  믮  CREATE TABLE,  뤥
⠡ ᥣ   ⠪ 뤥 쭮 ⢮ ⥭⮢ 
ᥣ, ।塞 ࠬ஬ MINEXTENTS.   㦠   
⠡,    ⥭.



                                    -- 58 --



᫨ 㬬   ⠡ ॢ ࠧ ࢮ MINEXTENTS, 
᪨ ᮧ  ⥭, ᭮뢠  稭 ࠭塞
ࠬ஢.   뢠 ७.  ᪮ ७ 
᭨ ந⥫쭮. 室  ⮣ ᫥ ᪫ 譥
᪮ ७.

         ᪮ ७.
        -----------------------------------
᪮ ७ ⠢  믮 SQL-   
SQL-ࠬ, 믮塞 ᮬ 짮⥫.  SQL- 
 ४ᨢ 맮 ⮬,   뢠 ᠬ ᥡ.

ᨢ 맮   "cache miss" ᫮ .
⢮ ४ᨢ 맮, 믮塞 , ࠦ ⨪
४ᨢ 맮.   ⨪  ᯫ   
 SQL*DBA MONITOR STATISTICS USER.  .4-2 뢠  ࠭.
⮡  ࠭ 믮 SQL*DBA : SQLDBA> MONITOR STATISTICS USER
 .4-2   ,  ⢮ ४ᨢ 맮 = 0

.4-2
Ŀ
                                                                  
       ORACLE PID: 0  Session    0  User Name: SYSTEM STATISTICS  
 Statistic Name          CUR     AUG     MAX    MIN    TOT        
 --------------------  -----  ------  ------  -----  -----        
 logons                    2    null    null   null    020        
 current logons            1    null    null   null     80        
 cumulative opened cu     10    null    null   null   5020        
 current open cursors      9    null    null   null    945        
 user commits            0.71   0.71    0.71   0.71      5        
 user rollbacks          0.00   0.00    0.00   0.00      5        
 user calls             24.00  24.00   24.00  24.00    174        
 recursive calls         0.00   0.00    0.00   0.00      0        
 mesages sent            0.71   0.71    0.71   0.71      5        
 mesages recieved        0.71   0.71    0.71   0.71      5        
 background timeouts     0.29   0.29    0.29   0.29      2        


     ४ᨢ 맮 ᪮ ᫥  ⥬ -
  "cache miss" ᫮ . ⮬  ᫥  ४ᨢ 맮
  ⮫쪮 ᫥  襭  ⥬. ᫨  த 
  ४ᨢ 맮 ᫥ ,  㦭 㡥    
  ᫥⢨ "cache miss" ᫮ .   ଠ樨   ᫮
      3"ன । ".

  ᫨  ।,  譨 ४ᨢ 맮  맢 "cache miss"
  ᫮ ,   ४ᨢ 맮   ᫥⢨ ᪮
  ७.  ⮬ 砥 ᫥ 㬥 ᪮ ७, 뤥
   ⥭⮢.

        뤥 ⥭⮢.
          --------------------
  믮 ᫥騥 蠣, ⮡  ᪮ ७:

  1.। ᨬ ࠧ 襣 ꥪ  .  ।
     㫥 ᪮쪮 뤥 ࠭⢠  ⠡    16
    "ࠢ ࠭⢮" 㪮⢠    
    .

                                    -- 59 --



  2.롥 稭 ࠭塞 ࠬ஢ ⠪, ⮡  뤥
    筮 ⢮ ⥭⮢  ࠧ饭     ६
    ᮧ ꥪ  .  讥 ⢮ ⥭⮢ ਢ 
    먣  ந⥫쭮  ᫥騬 稭:
    -  ᪮    ⥭ ᯮ 뢭,  让
       ⥭  뢥, 祬  쪨 ⥭⮢.  
         让 ⥭  ᪠   筮 ⥭

    -  ⭮,  ᥣ  訬 ⥭⠬  .
       , ᪮ 訥 ⥭ ॡ  ᬥ , 
        㤮   筮 ⢮ ᬥ ᢮  
       ࠭ ଠ樨.

         5.         .
                        

  ।⠢ ⢥ 蠣 ஥筮 :  ன
䫨⮢. 䫨    ᮢ    
⦥  ६.  䫨 ⠢   㯠. 
।⠢  ⮤   䫨⮢.   ⮩   :

-  㦨 䫨,     ந⥫쭮
-  ᭨ 䫨

 , 㦤  ⮩ , :
- ⪠ ᥣ
- 饫  ୠ ॣ樨 

            .
        
 ⮬ ࠧ 㦤 ᫥騥 :
-  ⠪ ⪠ ᥣ
-  㦨 䫨  ⪠ ᥣ⮢
-  ᮧ ⪠ ᥣ
-   ࠧ ⪠ ᥣ⮢

 ⠪ ⪠ ᥣ?
---------------------------
⪠ ᥣ -  뤥 ࠭⢮   , ஥ ᮤন
ଠ, 室  ⠭   , 믮
࠭樥. ⪠ ᥣ ᮤন ⪠ .  ⪠ 
ᮤন ଠ, 室   , ᤥ ࠭樥.
⪠ ᥣ ᯮ :
-  ⪠ ࠭樨
-  ⥭ ᮤন
-  ⠭

 ࠭樨, 믮塞  , ᮮ⢥ ⪠ ,
뢠  ⪠ ᥣ.   , ᮤঠ騥 ⪠
ᥣ,  訢. ⮬ ⪠ ᥣ   ꥪ⮬
䫨.

        㦥 䫨 ⪠⭮ ᥣ.
        -----------------------------------------
䫨 ⪠ ᥣ⮢ ⮡ࠦ १ 䫨 ஢, ᮤঠ
 ⪠ ᥣ⮢. 믮 ᫥騥 ⢨  ⮣, ⮡ ।-
, ᭨  䫨 ⪠ ᥣ⮢ ந⥫쭮 襩 :


                                    -- 60 --



1.஢ 稥 䫨⮢   ஢  SGA.

2.।   䫨  ᫥⢨ 䫨 ⪠⭮
  ᥣ.

         䫨⮢ ஢.
        ---------------------------
 㦥 䫨 ⪠⭮ ᥣ  ᫥ ᭠砫 ஢
䫨   ஢  .  ᮡࠥ ⨪, ⮡ࠦ 
䫨:

consistent gets,      㬬   ⨪  饥
db block gets         ⢮ ᮢ  ஢.

buffer busy waits     祭 ⮩ ⨪ ࠢ  ᮢ 
                      ஢, १⮬  뫮 .

 ⨪    ᯫ   SQL*DBA MONITOR
STATISTICS CACHE. ⮡   ⨪, 믮 :

SQLDBA> MONITOR STATISTICS CACHE

ଠ 뤠   ࠭ .5-1  5-2.  㭪 뢠 
 ࠭.  .5-1   ,   ࠭ ᮤন ⨪
consistent gets, db block gets  buffer busy waits.

᫥  ⨪  ६ 믮 襣 ਫ.  
⭮襭 稭 consistent gets  db buffer gets.  ⭮襭 ।⠢
業 ᮢ  , १⮬  뫮 .᫨   
10-15%, ந⥫쭮  ᭨.

᫨  ⭮襭 , 祬 10-1:%,   䫨   
ࠡ ⥬.  ⮬ 砥  ᫥ ।  ⨯  
ꥪ⮬ 䫨⮢, ᨢ ⠡ V$WAITSTAT,   ᠭ  ᫥饬
ࠧ.

.5-1 (࠭ 1).SQL*DBA MONITOR STATISTICS CACHE
Ŀ
                         ORACLE Statistics Moniyor                
       ORACLE PID: 0   Session  : 0   User Name: SYSTEM TATISTICS 
 Statistic Name           CUR      AUG      MAX      MIN      TOT 
 --------------------  ------  -------  -------  -------  ------- 
 db block gets          38.46    38.46    38.46    38.46      500 
 consistent gets        95.31    95.31    95.31    95.31     1239 
 physical reads         41.15    41.15    41.15    41.15      535 
 physical writes         9.15     9.15     9.15     9.15      119 
 db block changes       29.38    29.38    29.38    29.38      382 
 change write time       0.00     0.00     0.00     0.00        0 
 consistent changes      2.77     2.77     2.77     2.77       36 
 write complete waits    0.23     0.23     0.23     0.23        3 
 write wait time         0.00     0.00     0.00     0.00        0 
 buffer busy waits       0.00     0.00     0.00     0.00        0 
 busy wait time          0.00     0.00     0.00     0.00        0 
 redo synch writes       0.69     0.69     0.69     0.69        9 
 redo synch time         0.00     0.00     0.00     0.00        0 
 DEUR exchange waits     0.00     0.00     0.00     0.00        0 
 exchange deadlocks      0.00     0.00     0.00     0.00        0 
 free buffer requests   45.31    45.31    45.31    45.31      589 

                                    -- 61 --



 free buffer scans      45.31    45.31    45.31    45.31      589 
 free buffer inspecte  859.85   859.85   859.85   859.85    11178 
 free buffer waits       2.38     2.38     2.38     2.38       31 
 free wait time          0.00     0.00     0.00     0.00        0 


.5-2 (࠭ 2). SQL*DBA MONITOR STATISTICS CACHE
Ŀ
                        ORACLE Statistics Monitor                 
       ORACLE PID: 0   Session :  0  User Name: SYSTEM STATISTICS 
 Statistic Name           CUR      AUG      MAX      MIN      TOT 
 --------------------- ------  -------  -------  -------  ------- 
 ...continued (page 2)                                            
 dbwr timeouts           0.00      0.00    0.00     0.00        0 
 dbwr free needed        0.00      0.00    0.00     0.00        0 
 dbwr free lou           0.54      0.54    0.54     0.54        7 
 dbwr buffers scanned   10.77     10.77   10.77    10.77      140 
 dbwr checkpoints        0.00      0.00    0.00     0.00        0 
 consistent forceouts    0.00      0.00    0.00     0.00        0 


        ᪮ 믮 V$WAITSTAT ⠡.
        -------------------------------------------
⨪  䫨⮢ ࠭  ᪨ 믮塞 ⠡
SYS.V$WAITSTAT.  ⠡ ⮡ࠦ  䫨, 訥 ᫥
᫥   .  ⮩ ⠡ ᫥騥:

OPERATION                 । ⨯ 樨, 맢襩
                        䫨.  祭 ⮣  ᫥騥:

  buffer busy waits       ⨬ 祭  䫨 ஢

  consistent lock gets,   ⨬ 祭   ⮫쪮
  current lock gets      ୮ 

CLASS                     ।  䫨
                         ꥪ⮢
                        祭 ⮣  ᫥騥:

  undo segment header     ⨬ 祭  䫨  ஢,
                        ᮤঠ   ⪠⭮ ᥣ

  undo block              ⨬ 祭  䫨  ஢,
                        ᮤঠ   ⪠⭮ ᥣ

  data block              ⨬ 祭  䫨  ஢,
                        ᮤঠ  

  segment header,         ⨬ 祭ﬨ  ᯮ
  save undo header,      ன
  save undo block,
  sort block

RANGE                     ।  ६ . 祭
                        ⮣  ࠦ  ᥪ㭤.   :
                        0 - 30
                        30 - 100
                        100 - 500

                                    -- 62 --



                        500 - 1000
                        1000 - 2000
                        2000 - 4000
                        > 4000

COUNT                   祭 ⮣  ࠢ    ।
                         祭  OPERATION,  । 祭
                         CLASS   । 祭  RANGE

TIME                    祭 ⮣   㬬  ६  
                            OPERATION CLASS  RANGE

         ⠡ V$WAITSTAT.
        --------------------------
⮡     , 騥 ꥪ⮬ 䫨,
㯯  ⠡ V$WAITSTAT   CLASS.  । 㬬୮
⢮    CLASS   :

SELECT CLASS, SUM(COUNT)  TOTAL_WAITS
    FROM SYS.V$WAITSTAT
    WHERE OPERATION = 'buffer busy waits'
    AND CLASS IN ('undo segment header', 'undo block', 'data block'
    GROUP BY CLASS

 ⮣   룫拉 ⠪:

CLASS                   TOTAL WAITS

undo segment header            1235
undo block                      942
data block                        5

:    CLASS           䫨 

TOTAL WAITS    㬬୮ ६   ।  

 ⮣ ਬ  ᤥ ᫥騥 砭:

-  뫮 ᥣ 1235   ஢, ᮤঠ   ⪠
   ᥣ⮢
-  뫮 ᥣ 942   ஢, ᮤঠ   ⪠
   ᥣ⮢
-  뫮 ᥣ 5   ஢, ᮤঠ  

 ⮣ ਬ ,  ᠬ   䫨⮢ ⭮  ࠬ,
ᮤঠ騬  ⪠ ᥣ⮢. ⭮⥫쭮   䫨⮢
⭮  ࠬ, ᮤঠ騬  .

         䫨⮢  ஢.
        --------------------------------
⮡ ᭨ 䫨 ஢, ᫥ ᭨ 䫨  CLASS  
訬 祭 TOTAL WAITS:

-  ᪮ 訥 䫨   ⪠
   ᥣ⮢, ᭨ 䫨, ᮧ  ⪠ ᥣ⮢

-  ᫨ 訥 䫨   ஢, ᮤঠ ,
       6"⥫ 砭  ன".

                                    -- 63 --



         ⪠ ᥣ⮢.
        ----------------------------
 ᫥  騥 ४樨  롮 ⢠ ⪠
ᥣ⮢, ᭮뢠  ⢥ ࠭権, 믮塞 襩 
६.  ४樨 室  設⢠ ਪ ஢:

⢮ ६      㥬 ⢮ ⪠
࠭権                    ᥣ⮢
----------------------------------------------------------------
 16                      4 ᥣ
 16  32                   8 ᥣ⮢
 32                      n/4,    50

 ᮧ ⪠ ᥣ⮢ ᯮ  CREATE ROLLBACK SEGMENT.
 ⥫쭮 ଠ樨   ࠢ筮 㪮  SQL
.

        롮 ࠧ஢ ⪠ ᥣ⮢.
        ----------------------------------
  ⪠ ᥣ⮢    ந⥫쭮.  
⪠⭮ ᥣ । 묨   CREATE ROLLBACK
SEGMENT ࠬࠬ.

 ⪠ ᥣ   筮 訬, ⮡ ᮤঠ ⪠ 
襩 ࠭樨. ,    ⪠ ᥣ ⮣  ࠧ.
  讣 ⪠⭮ ᥣ  ࠭,    ॡ
७. ᪮ ࠭樨    ⪠ ᥣ 砩
ࠧ,     㢥७,  ।  ࠭ 㤥
祭  让 ⪠ ᥣ,    쪨.

          ᮢ   ࠭権.
        -------------------------------------------
 ਫ 믮    묨,  ६
 㣨 ࠭ﬨ.    ॡ  
⪠ ᥣ⠬  ⠭ ண 祭 ஢ .

 ⪠ ᥣ   筮 訬, ⮡  
⪠ ᥣ    ६ ᯮ .  訥 ⪠
ᥣ  ⠪  ࠡ  ࠭樨,  
讥 㬬୮ ⢮ .  ࠭樨 ॡ 訥 ⪠
ᥣ. ᫨ ⪠   室  ⪠ ᥣ,  
ᥣ. ᪮ ७ ᭨ ந⥫쭮   ᫥
,   .

         OLTP ਫ.
        --------------------
 ਫ 믮 "online transaction processing"  OLTP. 
ਫ 뢠 OLTP ਫ.  ࠪਧ 묨 ६
믮塞묨 ࠭ﬨ ⠪,       㬬
. ᫨  ਫ 믮 ⮫쪮 OLTP   ᮢ 
 ࠭権,    ࠡ, ᮧ  쪨 ⪠
ᥣ⮢.     ⠪  ⢮ ⪠ ᥣ⮢,
᪮쪮  ६ 믮塞 ࠭権.  ⮬  ࠭
㤥   ⤥쭮 ⪠⭮ ᥣ  ᪠ 䫨.

쪨 ⪠ ᥣ ⥫쭮 ⠢ ࠭   ஢, 
    .  OLTP ⪠ ᥣ   2
⥭,  ࠧ஬ 浪 10 .

                                    -- 64 --




               
        
䫨 㯠   ୠ ॣ樨  । ᭨
ந⥫쭮  . ,  ।⠢  ⮤ 
  ᭨  䫨⮢,  .  ࠧ
:
-  ⠪  ୠ ॣ樨
-  㭪樨 饫  ୠ ॣ樨 
-  㦨 䫨  饫
-  ᭨ 䫨  饫

         ୠ ॣ樨 .
        ------------------------------------
 ୠ ॣ樨   㣮   SGA,  ᮤন
ଠ  , ᤥ   .   ଠ ࠭ 
redo-. Redo- ᮤঠ ଠ  ४樨  -
⪨ , ந    ࠬ 몠 㫨-
 (DML-Data Manipulation Language) INSERT,UPDATE  DELETE  ࠬ
몠 ᠭ  (DDL) CREATE,ALTER,DROP.

Redo- ᯮ  ⠭  .
Redo-  -짮⥫᪨ ᠬ  ࠭⢠
 짮⥫   ୠ ॣ樨   SGA. Redo-
 뢭 ᫥⥫쭮 ࠭⢮  .

          ୠ (LGWR).
        ------------------------------
 ୠ ॣ樨  뢠  䠩 ୠ ॣ樨
 (redo log file)   ।⢮ LGWR  (Log Writer
process), .. ᭮ ᮬ , ⢥⢥  ࠢ ஬
ୠ ॣ樨 .  LGWR  뢠  redo-,
 뫨 ᪮஢      ᫥ ஢  䠩.
LGWR 뢠 뢭    . LGWR 뢠:
-    짮⥫ ᮢ蠥 ࠭
-    ୠ ॣ樨    
-   DBWR      

        ࠭⢮   ୠ ॣ樨 .
        ----------------------------------------------------
 LGWR 뢠 redo-   ୠ ॣ樨 
(redo log )  䠩 ୠ ॣ樨  (redo log 䠩), 
짮⥫  ⥬ ஢    ⮢, ᠭ 
. LDWR 筮  筮  ⠪, ⮡ ࠭⢮   뫮
㯭   ⮢,     ୠ 㤭.

⨪ ᮢ  ୠ ॣ樨  (redo log space requests)
ࠦ ६, ஥  짮⥫  㯠  redo log .
 ⨪    ࠭:  SQL*DBA MONITOR STATISTICS REDO.
.5-3   ⮣ ࠭. ࠦ  , 믮 :

SQLDBA> MONITOR STATISTICS REDO

稭 redo log space requests   ᥣ ࠢ .
㫥 祭 뢠,    ࠭⢮  . 
⮬ 砥 㢥 ࠧ redo log .  ࠧ ।
INIT.ORA ࠬ஬ LOG_BUFFER.  祭 ࠦ  .


                                    -- 65 --



.5-3.
Ŀ
                        ORACLE Statistics Monitor                 
       ORACLE PID: 0   Session :  0  User Name: SYSTEM STATISTICS 
 Statistic Name           CUR      AUG      MAX      MIN      TOT 
 --------------------- ------  -------  -------  -------  ------- 
 redo entries           11.33    11.33    11.33    11.33       68 
 redo size            2283.67  2283.67  2283.67  2283.67    13702 
 redo entries lineari    0.00     0.00     0.00     0.00        0 
 redo buffer allocati    0.00     0.00     0.00     0.00        0 
 redo small copies      11.17    11.17    11.17    11.17       67 
 redo wastage          155.17   155.17   155.17   155.17      931 
 redo writer latching    0.00     0.00     0.00     0.00        0 
 redo writers            0.50     0.50     0.50     0.50        3 
 redo block written      5.17     5.17     5.17     5.17       31 
 redo write time         0.00     0.00     0.00     0.00        0 
 redo log switch wait    0.00     0.00     0.00     0.00        0 
 redo chunk allocatio    0.00     0.00     0.00     0.00        0 
 redo log space reque    0.00     0.00     0.00     0.00        0 
 redo log space wait     0.00     0.00     0.00     0.00        0 
 redo log switch into    0.00     0.00     0.00     0.00        0 
                                                                  


        饫  ୠ ॣ樨 .
        ---------------------------------------------
  redo log  ॣ㫨 饫.  ⨯ 饫 ஫
  redo log :
-  饫 । 
-  饫 ஢ 

        饫 । .
        --------------------------------
饫 ।  ஫ । ࠭⢠ 
redo-⮢  redo log .⮡ । ࠭⢮  ,
짮⥫᪨     饫 । -
. ᪮   ⠪ 饫, ⮫쪮  짮⥫᪨ 
 । ࠭⢮      ६.  饫
। ন ᫥⥫ ࠪ ⮢  . ᫥
뤥 ࠭⢠  redo-,  짮⥫  ஢
   ⮫쪮    饫 । .  -
஢ ⭮  ஢  "饫 । ".  
 믮 ⠪ ஢, ᫨ redo -  ண -
. ᫥ ⠪ ஢  짮⥫ ᢮ 饫.
ᨬ ࠧ redo-   ஢  饫 -
।  INIT.ORA ࠬ஬ LOG_SMALL_ENTRY_MAX_SIZE. 祭
ࠬ ࠦ  .  祭  㬮砭   襩
樮 ⥬.

        饫 ஢.
        --------------------
᫨ redo- ᫨誮   ஢  饫 ।,
 짮⥫   饫 ஢렭 । ஢
  .    饫 ஢,  
redo-  뤥   ⠭⢮  ,  ⥬ ᢮
饫 ஢.



                                    -- 66 --



᫨     CPU,  redo log    
饫 ஢. 稥 ᪮쪨 饫 ஢ 
᪮쪨 ᠬ ஢   redo log  ६.
⢮ 饫 ஢ । INIT.ORA ࠬ஬
LOG_SIMULTANEOUS_COPIES. 祭   㬮砭 ࠢ 祭 INIT.ORA
ࠬ CPU_COUNT.

 ⮬᪨ ⠭ 祭 CPU_COUNT   CPU, -
㯭   ⮢.  ୮    
饫 ஢, ᪮ ⨢   ⮫쪮   ᮢ 
  ६.  ⮬ 砥 redo-   饫 -
। ᨬ  ࠧ஢.  ⠭ 祭 CPU_COUNT = 0.

        ஢ઠ ࠡ ୠ ॣ樨 .
        ----------------------------------------------
㤭 㯠   ୠ ॣ樨   ਢ 
䫨⠬  饫 .  䫨  ᭨ ந⥫쭮.
 ᮡࠥ ⨪  ࠡ   饫.  ⨪ 
  SQL*DBA MONITOR LATCH ࠭. ࠦ  , 믮
:
        SQLDBA> MONITOR LATCH.

 .5-4   ⮣ ࠭.
᫥ ⨪  饫 ।  ஢.
   ⨪  Willing to Wait Requests:

Total           祭 ⮩ ⨪ ࠢ 㬬୮ 稭 ⢠
                ᮢ  饫

Timeouts        稭 ⮩ ⨪ ࠢ  ⠩⮢  ६
                 饫.   १⮬ ᯥ譮
                  饫. ୮ ६   ⢮
                ᮢ  ⠩   襩 樮 ⥬.

᫨ ⭮襭 Timeouts  Total  । 饫 ॢ蠥 ண
祭  10-15%,  䫨 ⮩ 饫    ந⥫-
.  ⮬ 砥 ᫥ ᭨ 䫨  ⮩ 饫.  ண
䫨⮢ 室  設⢠ 樮 ⥬,  
   CPU,    䫨⮢  ᭨
ந⥫쭮.   "㪮  ⠭  ᯮ짮" 
⥫쭮 ଠ樨, ᯥ筮  襩 樮 ⥬.

.5-4.
Ŀ
                        ORACLE Latch Monitor                      
               Holder--Willing-to-Wait-Requests--No-Wait-Requests-
Latch Name      PID    Total Timeouts Immediates   Total Succerses
--------------------- ------ -------- ----------   ----- ---------
process allocation       659       48        646       0        0 
session allocation     23789       24      23767       0        0 
messages               53462      369      53110       0        0 
enqueues               61833      178      61665       0        0 
cache buffers chains 1000926      787    1000259  112506   112477 
cache buffers Iru ch   88730      773      88019  354631   552109 
cache buffer handles     348        1        347       0        0 
multiblock read ob je  10904        0      10984       0        0 
inter-instans buffe        0        0          0       0        0 
system commit number   56510       33      56470       0        0 

                                    -- 67 --



archive control            0        0          0       0        0 
redo allocation        55567      519      54660       0        0 
redo copy                  0        0          0   21547    21547 
dml/ddl allocation     19255       13      19243       0        0 
transaction allocati    6597        1       6596       0        0 
undo global data        6277        0       6277       0        0 
sequence cache          1585        4       1581       0        0 
row cache objects     128948      633     128641       0        0 


         䫨⮢  饫.
        --------------------------------
設⢮ ⠪ 䫨⮢ 砥,     ᮢ 
     饫.  䫨 ।  
 ,    ⮫쪮  ⨢ .

         䫨⮢  饫 ।.
        ----------------------------------------------
 ᭨ 䫨⮢  ⠪ 饫  ᫥ ஢ ६, 
祭 ண    饫.  ᭨ ⮣ ६
᫥ 㬥 ஢  饫 ।. 訢 稭
INIT.ORA ࠬ LOG_SMALL_ENTRY_ MAX_SIZE,  ᭨ ⢮  ࠧ
redo-⮢, 㥬  饫 ।.

         䫨⮢  饫 ஢.
        --------------------------------------------
   ᫥ 饫 ஢ 
᫥ ᠬ ஢   redo log  ६.
祭 INIT.ORA ࠬ LOG_SIMULTANEOUS _COPIES  㬮砭 
稥 INIT.ORA ࠬ CPU_COUNT.   ⮬᪨ ⠭
祭 CPU_COUNT ࠢ  CPU, 㯭 襬 . 
   ⠭ 祭 CPU_COUNT = 0.

᫨  㦨 䫨  饫 ஢,   饫. 
⮣ ᫥ 㢥 LOG_SIMULTANEOUS_COPIES.   ᮧ 
襣 ⢠ 饫 ஢  ⭮襭   CPU,
㯭 襬 .

㣨 ᯮᮡ ᭨ 䫨⮢  饫 ஢  ᭨
६,  祭 ண    饫.  ६ 
᭨ ன (pre-building) ⮢ । 祭 饫
஢. Redo-     ⥩. 筮  
 ⤥쭮   짮⥫᪮   redo log  
  饫 ஢. ஢ ⥩ -⤥쭮
ॡ  饭  . ,   ஥, 
ᮡࠥ      饫. Redo-   ⥬
᪮஢     饭  .

 ࠨ  ,   ண ࠧ.
 ண ࠧ । INIT.ORA ࠬ஬
LOG_ENTRY_PREBUILDING_THRESHOLD. 稭 ⮣ ࠬ ࠦ  .
祭  㬮砭 ࠢ 0.  砥,    ࠨ.
 ⨢樨  ன redo-⮢ 㢥
LOG_ENTRY_PREBUILDING_ THRESHOLD ࠬ.

       -  .
     


                                    -- 68 --



 ࠡ   祭  ᮢ.   ॡ ࠧ
ᮢ   SGA.

,   -   ਮ.  ६
⠫樨   䮭  稫 ਮ  㬮砭, 
 樮 ⥬.  ᫥   ਮ.  㡥,
  짮⥫᪨     ᠬ ਮ.

祭 ࠧ ਮ⮢ -ᠬ  맢 䫨. 
樮 ⥬   뤥 ६     
ਮ⮬, ᫨   ᮪ ਮ⮬ ॡ ६   ६.
᫨ ᮪ਮ  ॡ 㯠  ᠬ , 
ਮ ᮬ,     ᪮筮, 
ਮ   CPU  ᢮ .

      6.
     

        .
     
 ⮩  㦤 ⥫ ஥ , 
᫥ ।ਭ ᫥ 襭 蠣, ।⠢  
  ⮣ 㪮⢠.
   ந 祭 ᯥ᪨ 樨  .
஬ ⮣,     묨, ᫨  樨 ᭨
ந⥫쭮.   砥 ᫥騥 ⥬:
-  ஢
-   ᯨ᪨
-  ஫ 窨


 .

 ਫ ॡ, ⮡  ஢ . 
ࠧ ᪠ :
-     
-    ॡ  ஢
-   । , ⮡ ⨬஢  ஢
-    ஢, ᮧ 

稭 ஢.
-------------------
騥 SQL- ⠢  ஢ :
-  SQL-,  ᮧ 
-  SQL-,   ࠦ GROUP BY, ORDER BY
-  SQL-,  ᯮ  DISTINCT
-  SQL-,  믮 ꥤ
-  SQL-,  ᯮ UNION,INTERSECT  MINUS set-

 ஢.
-------------------
஢ ॡ ࠭⢠  . ⪨ ,   
 ,    ஢(sort areas).  
஢     짮⥫ ,  ॡ
஢. ꥬ   ஢ । INIT.ORA ࠬ஬
SORT_AREA_SIZE. 祭 ⮣ ࠬ ࠦ  . ᫨ 㬬
,  ,  㬥  뤥  ஢,
 ࠧ   , ᯮᮡ 㬥   . 

                                    -- 69 --



 ⥬  ⤥쭮. ⤥쭮 㥬  ⭠ 
"run".  ᫥ ஢  run  ꥤ , ⮡ 
⥫ १. ᫥ ஢ run     ६
ᥣ  ᪥   ᫥騩 run.   㬮砭 ६
ᥣ ᮧ  ࠭⢥ ⠡ SYSTEM. 㣨 ࠭⢠ ⠡
  ।  㬮砭   ࠧ TEMPORARY TABLESPACE
 ALTER USER.   ਬ:
 ALTER USER KING
     IDENTIFIED BY ARTHUR
     TEMPORARY TABLESPACE CAMELOT

INIT.ORA ࠬ SORT_SPACEMAP_SIZE ।   ࠢ ⮩
࠭⢠  ஬ run. ࠪ ⮬᪨ ⠭ 
ࠬ.  ᫥  .

।   ⥩ ஢.
---------------------------------------------
। ࠧ  ஢  ᮮ⢥⢮ ᮤন 
  設⢠ ஢. , ᫨  ਫ  믮
訥 ஢ ,   ᮮ⢥ ࠧ  ஢,
   㢥 ࠧ  ஢. 訥 ஢
   ᮧ  ᮢ   SQL- 
ࠦ GROUP BY,  ࠡ뢠 讥 ⢮ ᥩ.

㦥  ஢.
-------------------------------
  ।      ஢ ᫨誮 쪮.
ᯮ  ALTER USER  । ६ ⠡筮
࠭⢠  짮⥫᪮ , 믮饣 ஢. ⥬
 ᪮ I/O  ⮣ ⠡筮 ࠭⢠  祭 ஢.
᪮ I/O      SQL*DBA MONITOR FILE I/O ࠭.
 祭 ࠦ 믮 SQL*DBA :
SQLDBA> MONITOR FILE

祭 ࠧ  ஢.
--------------------------------------
᫨ /뢮  ६ ⠡筮 ࠭⢠ १  祭
஢,  ந⥫쭮 襣 ਫ  먣 
㢥祭 ࠧ஢  ஢.  ⮬ 砥 㢥 祭
INIT.ORA ࠬ SORT_AREA_SIZE.

襭 믮  㢥祭  ஢.
------------------------------------------------------
祭 ࠧ  ஢ 㢥稢 ࠧ  run  㬥
 ⢮ run.  㬥襭   믮  ﬨ:
-  㬬୮ ⢠ run 㬥蠥 ⢮ ꥤ, 
    믮, ⮡  㬬 ஢ १.

-  㬬୮ ⢠ run ᭨ ⢮ ᪮ I/O 
  ६ ᥣ⠬, 室  믮 ஢.

 ந⥫쭮   ஢ .
-------------------------------------------------------------
祭 ࠧ  ஢ ⠢   ,
믮騩 ஢, 뤥  .  㢥祭 㬥蠥
㬬 , 㯭  ⥪ ⥩.   ⠪



                                    -- 70 --



⢮  ।   樮 ⥬   맢
  ᢮. । 㢥祭 ࠧ  ஢ 㡥, 
筮 ᢮  㯭  襩 樮 ⥬.

  ஢.
------------------------
   ஢ -  ᮧ ᮢ.    ⠡
뢠 ஢  ᥩ  ⠡  ᭮ 祭 ᭮ 
 .    ᮧ   ஢. ᫨  
⠡ 㦠  饬 浪,   ᮧ  ॥
 ஢.

 NOSORT.
-------------
⮡ ᮧ   ஢, 㦠   ⠡  饬
浪 祭 ᭮ .  樮 ⥬  ।⠢
 ⨫ ஢  ஢ ᥩ ।  㧪.  
ᮧ , ᯮ  NOSORT   CREATE INDEX. ਬ,
CREATE INDEX  ᮧ  EMP_INDEX   ENAME ⠡ EMP 
஢ ᥩ:

CREATE INDEX EMP_INDEX ON EMP(ENAME) NOSORT

 ᯮ짮  NOSORT.
--------------------------------
஢    㧪   浪  ᥣ 
३訬 ᯮᮡ  㧪. ᫨    , 
  㦠  ॥, ᯮ  ஢
ࠫ쭮,   㦠 ࠧ 樨 . ⮡ 
  ࠫ쭮 믮, 㦠   ।⥫쭮
஢. ⥬ ᮧ   樨 NOSORT.  㣮 ஭ ᫨  
 , , -, ᫥ ஢ 
। 㧪. ⥬  ᫥ ᮧ   樥 NOSORT.

   .


䫨 ᢮ ᯨ᪮  ᭨ 믮  ਫ. 
ࠧ ᪠ :
-  ⠪ ᢮ ᯨ᪨
-  㦨 䫨 ᢮ ᯨ᪮
-  㢥 ⢮ ᢮ ᯨ᪮

 ᯨ᪨.
-----------------
 ᯨ᮪ -  ᯨ᮪  ,  ᮤঠ ᢮
࠭⢮.  ᯨ᪨ ᯮ  ࠭ ᮢ  ,
  筮 ᢮ ࠭⢠  ࠧ饭  ᥩ.

 ⠡     ᢮ ᯨ᪮. ⢮ ᢮
ᯨ᪮ । INIT.ORA ࠬ஬ FREE_LIST_PROC  ६ ᮧ
⠡. 祭 ⮣ ࠬ  㬮砭 = 4.    
⠢   ⠡,   ᭠砫     ᢮
࠭⢮. ⮡  ⠪ ,     ⠡ ᢮
ᯨ᪮.




                                    -- 71 --



䫨  ᢮ ᯨ᪮    ᪮쪮 
ᮢ ६ ⠢       ⠡.  
  ᢮ ᯨ᮪. ᫨ ᢮ ᯨ᪮ , 祬 ᮢ,
  ᮢ   ᢮ ᯨ᮪.

㦥 䫨⮢ ᢮ ᯨ᪮.
-----------------------------------------
䫨 ᢮ ᯨ᪮ ⮡ࠦ १ 䫨    
. 믮 ᫥騥 蠣, ⮡ । ᭨  䫨
᢮ ᯨ᪮ ந⥫쭮 .

1. ᫥ 䫨 ஢    SGA.
2. ।    䫨 ᫥⢨ 䫨⮢
   ᢮ ᯨ᪮.

  䫨⮢.
---------------------------
 㦥 䫨⮢ ᢮ ᯨ᪮  ᫥ 砫 ᫥
䫨    -.  ᮡࠥ ⠪ ⨪:

cosistent gets,        㬬   ⨪ ࠢ 饬 
db block gets          ᮢ   .

buffer busy waits      稭 ⮩ ⨪ ࠢ  ᮢ 
                       ࠬ,  稫 .

 ⨪     SQL*DBA MONITOR STATISTICS CACHE
࠭.  ⮣ 㦭 믮 SQL*DBA :

SQLDBA> MONITOR STATISTICS CACHE

 ⨪ 뤠  ࠭.  .6-1  6-2   
࠭.  .6-1 ,   ࠭ ᮤন ⨪:
consistent gets, db block gets, buffer busy waits.
  ⨪  ६ 믮 襣 ਫ.  
⭮襭 buffer busy waits  㬬 consistent gets  db block gets. 
⭮襭 ࠢ 業 ᮢ  ,  稫 . ᫨
 ⭮襭   10-15%,  䫨  ஢, ᮤঠ 
䫨   ᭨ ந⥫쭮. ᫨  ⭮襭  15%,
 䫨 㬥 ந⥫쭮  ᫥ ।  ⨯ 
 ꥪ⮬ 䫨⮢.

.6-1 (࠭ 1).SQL*DBA MONITOR STATISTICS CACHE
Ŀ
                         ORACLE Statistics Moniyor                
       ORACLE PID: 0   Session  : 0   User Name: SYSTEM TATISTICS 
 Statistic Name           CUR      AUG      MAX      MIN      TOT 
 --------------------  ------  -------  -------  -------  ------- 
 db block gets          38.46    38.46    38.46    38.46      500 
 consistent gets        95.31    95.31    95.31    95.31     1239 
 physical reads         41.15    41.15    41.15    41.15      535 
 physical writes         9.15     9.15     9.15     9.15      119 
 db block changes       29.38    29.38    29.38    29.38      382 
 change write time       0.00     0.00     0.00     0.00        0 
 consistent changes      2.77     2.77     2.77     2.77       36 
 write complete waits    0.23     0.23     0.23     0.23        3 
 write wait time         0.00     0.00     0.00     0.00        0 
 buffer busy waits       0.00     0.00     0.00     0.00        0 

                                    -- 72 --



 busy wait time          0.00     0.00     0.00     0.00        0 
 redo synch writes       0.69     0.69     0.69     0.69        9 
 redo synch time         0.00     0.00     0.00     0.00        0 
 DEUR exchange waits     0.00     0.00     0.00     0.00        0 
 exchange deadlocks      0.00     0.00     0.00     0.00        0 
 free buffer requests   45.31    45.31    45.31    45.31      589 
 free buffer scans      45.31    45.31    45.31    45.31      589 
 free buffer inspecte  859.85   859.85   859.85   859.85    11178 
 free buffer waits       2.38     2.38     2.38     2.38       31 
 free wait time          0.00     0.00     0.00     0.00        0 


.6-2 (࠭ 2). SQL*DBA MONITOR STATISTICS CACHE
Ŀ
                        ORACLE Statistics Monitor                 
       ORACLE PID: 0   Session :  0  User Name: SYSTEM STATISTICS 
 Statistic Name           CUR      AUG      MAX      MIN      TOT 
 --------------------- ------  -------  -------  -------  ------- 
 ...continued (page 2)                                            
 dbwr timeouts           0.00      0.00    0.00     0.00        0 
 dbwr free needed        0.00      0.00    0.00     0.00        0 
 dbwr free lou           0.54      0.54    0.54     0.54        7 
 dbwr buffers scanned   10.77     10.77   10.77    10.77      140 
 dbwr checkpoints        0.00      0.00    0.00     0.00        0 
 consistent forceouts    0.00      0.00    0.00     0.00        0 


 V$WAITSTAT.
-------------------
 ᮡࠥ ⨪   ⨯ , ꥪ⮢ 䫨⮢. 
⨪ ࠭  ᪨ 塞 ⠡ SYS.V$WAITSTAT.  ⮩
⠡ ᫥騥 :
OPERATION           । ⨯ 樨, 맢襩 䫨.

CLASS               ।  , ꥪ 䫨.

COUNT             稭 ⮣   ⢮   
                  ,    CLASS.  ⢮  
                    ⥬.

 ⠡ V$WAITSTAT.
--------------------------
⮡  㧭     ꥪ⮬  䫨⮢,
㯯   ⠡   CLASS.  㤥 । 㬬୮
⢮       :

SELECT CLASS, SUM(COUNT) TOTAL_WAITS
    FROM SYS.V$WATSTAT
    WHERE OPERATION = 'buffer bust waits'
    AND CLASS IN ('data block, 'undo segment header' 'undo block')
    GROUP BY CLASS

 ⮣   룫拉 ⠪:

CLASS                    TOTAL_WAITS




                                    -- 73 --



data block                       755
undo block                        10
undo segment header                8

:

CLASS    -      ꥪ⮢ 䫨⮢
TOTAL    -    饥 ᫮   ।  

 ⮣ ਬ  ᤥ ᫥騥 뢮:
- 뫮 ᥣ 755   ஢, ᮤঠ  
- 뫮 10   ஢, ᮤঠ   ⪠ ᥣ⮢
- 뫮 ᥣ 8   ஢, ᮤঠ   ⪠
  ᥣ⮢

 ⮬ ਬ ⭮⥫쭮   䫨⮢ ⭮  ࠬ,
ᮤঠ騬  .

  䫨⮢.
-----------------------------
 ᭨ 䫨⮢, ࠦ buffer busy waits ⨪, ᭨
䫨     訬 㬬 祭 TOTAL WAITS:

- ᫨ 設⢮ 䫨⮢  ஢, ᮤঠ  ,  
  뫮  ᬮ७ ਬ, ᭨ 䫨, 㢥稢 ᫮ ᢮
  ᯨ᪮.

-  ᫨ 設⢮ 䫨⮢ ⭮  ࠬ, ᮤঠ騬 - ⨯
   ⪠ ᥣ⮢,    5 "ன 䫨⮢".

祭 ᫠ ᢮ ᯨ᪮.
-----------------------------------
 㬥襭 䫨⮢ ᢮ ᯨ᪮ 믮 ᫥騥 蠣:

1. ४ ࠡ .
2.  祭 INIT.ORA ࠬ FREE_LIST_PROC  ⢠ 
   ᮢ,  ६    ,    32
3.   .
4.  ᭮ ⠡, ᢮ ᯨ᪨ ன 뫨 ꥪ⮬ 䫨⮢.
   ᮧ    㬥襭 .     
   ᮧ ⠡, 롨      ⠡, 㬥 
   ⠡      ⠡.  ⠡ 㤥  ⮫쪮
   ᢮ ᯨ᪮, ᪮쪮  INIT.ORA ࠬ஬ FREE_LIST_PROC.

  .


஫쭠 窠() -  , 믮塞   ⮬᪨. 
  ।  ᭨ ந⥫쭮.  ࠧ ᪠
:
- 祬  믮 ஫ 窨
-   믮 ஫ 窨
-   믮 ஫ 窨
-  ஫ 窨   믮
-    ஫ 祪
-    ஫ 祪



                                    -- 74 --



祭 ஫ 祪 ().
----------------------------------
 㦠 ᫥騬 楫:

- ⠭  ६,  ண  "ப⪠
  ।"  ⠭   砥 ਨ 
- ࠧ ୮ ᯮ짮  娢஢  䠩
  ୠ ॣ樨 

 ⠭.
-------------------
ਬ, ᫨  襬    ⠭, 設⢮  
   䠩    ᪥. , , ࣠訥
   ஢,     ᠭ  䠩   
ਨ. 襭      redo-⠬  redo
log 䠩  ᪥.   ⠭     ਨ,
ᠭ  redo-.   뢠 "ப⪠ ।"(rolling
forward).   㢥७,      ⠭,
.. ⠭ ,  ண    "ப ।".

㦨 ୠ ॣ樨 .
-------------------------------------------
 ⠪  娢஢  ᯮ짮 ୮ redo log 䠩. 
ॡ  ࠩ   䠩 ୠ ॣ樨  (redo log
䠩). LGWR    redo- ᫥⥫쭮   rdo log
䠩,    㤥 . ⥬ LGWR  த   
㣮 redo log 䠩.   ६,   䠩   
稭  㣮 䠩 , 뢠 ४⥫ ୠ (log switch).
 ᥣ     ४祭.   ࠭,   
, 饭 ।騬 redo log 䠩, ᠭ  䠩   
 redo-   㦭. ।騩 䠩  ⥬  ᯮ짮
,  娢஢.  ⥫쭮 ଠ樨  娢஢
   15 㪮⢠    .

 ⠢ ஫ 窨.
---------------------------------
஫ 窨 믮  ᫥ ⥫⢠:
-  redo log 䠩 
-  LGWR  ᠫ । ⢮   
  ।饩 

 ᥣ    ४祭,  ᠭ ࠭.   ⠪
믮  ४祭ﬨ.  믮  LGWR  ᠫ
। ⢮    ।饩 .

⢮ , ᠭ  , 뢠 checkpoint interval. 
 INIT.ORA ࠬ஬ LOG_CHECKPOINT_INTERVAL.  稭 ⮣ ࠬ
ࠦ   樮 ⥬.  ਬ, ᫨  redo log 䠩
 5000 ,  祭 log checkpoint interval = 2000 , 
믮     redo log 䠩. ࢠ  믮  LGWR
뢠 2000   䠩.   믮  LGWR 뢠
᫥騥 2000 .   믮  LGWR 뢠 1000 ,
 䠩  믮 ४祭 (log switch).

  믮 ஫ .
--------------------------------------
 믮 , LGWR ᮧ ᯨ᮪ ,  ࣠


                                    -- 75 --



   ।饩    ஢,   뫨 ᠭ  䠩
 .     (DBWR-) ⥬    
䠩  .  DBWR 稢 ,  襭.

 ஫ 窨   ந⥫쭮.
---------------------------------------------------
஫ 窨  :
- ६ 믮 ⠭
- ६ 믮 

६ 믮 ⠭.
--------------------------------
   ᭨ ६ ⠭ ᫥ ਨ. ᫨ ࢠ 
, ⮣  ࢠ     ᤥ ⭮⥫쭮 訥
  .  ⮬ 砥 ⭮⥫쭮 訥  
 "ப祭 ।"  ⠭.

६ 믮 .
------------------------
᪮  ᯮ DBWR  믮 I/O,   ᭨  ⪮
६ ந⥫쭮 ⥬. ॣ㧪, 易  ,  
᭨ ந⥫쭮 ⮫쪮  ६ 믮  ஫
祪.

  ஫ 祪.
------------------------------------
      襩 ,  ࢠ .  祭
ࢠ  ᭨  . ⮡  ࢠ ,  稭
INIT.ORA ࠬ LOG_CHECKPOINT_INTERVAL.
᪮   ४祭 (log switch) 室  ࠡ ୠ
ॣ樨  (redo log),    ᮢᥬ  . 
⢮    㬥襭  㬠 ⠭ 祭
LOG_CHECKPOINT_INTERVAL  稭 襣 ࠧ 襣 redo log
䠩.  ⠭ 㡨ࠥ  , ᪫ ,   
४⥫ ஢ redo log 䠩.
    㬥 ⢮ , ᭨  ४⥫
஢. ⮡ 㬥 ⢮ ४⥫, 㢥 ࠧ  redo
log 䠩, ⮡   﫨 ᫨誮 .

롮  ஫ 祪.
--------------------------------
 ᫥     襩 , ᭮뢠  ᮡ
襩 ࠡ. ᫨     ६ 襭 襣 ਫ, 祬
६ ⠭, 롥   .  ⨢ 砥 롥
᮪  .














                                    -- 76 --
