


             7-1     ன  

 7.        .
              

 ।⢮   믮    SQL*DBA. ࠭
SQL*DBA MONITOR ᠭ  ਫ  "砭  SQL*DBA" 㪮⢠
   .

⥫ ⮢ ।⢠ ᠭ  ⮩ .  ।⢠  ࠭
SQL*DBA MONITOR      ࠨ ਫ, 
뢠 .  ⮢ ।⢠, ᠭ  ⮩ :

- SQL ஢
- EXPLAIN PLAN (EP) 

뢮 EP     ⨬. ᪮쪮  ⨬
 ꥪ⮬, 騬  ᨨ  ᨨ  , 뢮 EP
 㤥 ࠧ.    뢮  ⢥
⫨  ⥪饩 ᨨ. 筮 ⠪ SQL ஢  TKPROF-ணࠬ
 ꥪ⠬ ⥫쭮 騬    . 
  ࠦ  㬥  짮⥫.

        SQL .
        
SQL ஢ ।⠢ ଠ  믮 ⤥ SQL ஢.
SQL ஢ ࠡ뢠 ᫥ ⨪   :
- 稪 , 믮  롮ન
- ६ ࠡ CPU  ६ 믮 
- 䨧᪨  ᪨ ⥭
- ⢮ ࠡ뢠 ᥩ

  ࠧ SQL ஢   ᥠ   
.  SQL ஢ ࠧ襭, ᡮ ⨪   SQL
஢ 믮  ᥠ 짮⥫   , ᯮ 
஢筮 䠩. ⮡ ॢ ஢ 䠩  , 㤮 
⥭,    TKPROF-ணࠬ.

 ਠ, TKPROF ⠪ 뢠  믮 .   
   㬥 EXPLAIN   ப TKPROF. ⠪
TKPROF-  ᠭ  ࠧ "믮 TKPROF"  ⮩ .  
⥫쭮 ଠ樨   EP   ࠧ "EP "  ⮩
.  ᪮ ࠡ SQL ஢ 㢥稢 ॣ㧪 ⥬, 
᫥ ࠧ   ६ ᫥  SQL ஢  ,
   稫.
 ᯮ짮 SQL஢ 믮 ᫥騥 蠣:

1.⠭ INIT.ORA ࠬ, ⮡ ⮢   ᯮ짮 SQL
  ஢.
2.믮 ਫ  ࠧ襭 ஢  ᥠ  .
3.ᯮ TKPROF  祭 ⨪  ଥ, 㤮  ⥭.

   蠣 㦤 ⠫쭮 .

        ⠭ INIT.ORA ࠬ஢  SQL ஢.
        --------------------------------------------------
। 믮 ਫ  ࠧ襭 SQL ஢
   ᫥騬 INIT.ORA ࠬࠬ:

                                    -- 77 --



- TIMED_STATISTICS = TRUE
   ࠧ蠥 ६ , ।⠢塞 SQL ஢, ⠪ 
  ६ ࠡ CPU  ६ 믮 . 襭 TIMED_STATISTICS
  ਢ  ⥫ ६ 맮 権  ஢. 
  ࠬ ⠪  ᡮ ᮮ⢥饩 ⨪  SQL*DBA MONITOR
  ࠭.

- MAX_DUMP_FILE_SIZE = n
   ᫥  ᨬ쭮 ⢮  樮 ⥬ 
  ࠧ饭 뢮 ஢. ᫨  㦨,   뢮 祭,
  㢥 ⢮ , । ⨬ ࠬ஬  
  ஢ ᭮.

- USER_DUMP_DEST = directory
   ஢ 뢠 ⠫  ⥬ ,  
  㬮砭. ᫨     祭,   㪮 
  ⠭  ᯮ짮 (Installation and user's Guide)  ࠢ쭮
   㣮 ⠫.

襭 ஢  ᥠ.
----------------------------------
 ࠧ襭 ஢  ⤥쭮 ᥠ 믮 SQL :

ALTER SESSION SET SQL_TRACE TRUE

⮡ ⪫ SQL ஢, 믮 SQL :

ALTER SESSION SET SQL_TRACE FALSE

SQL ஢ ⪫砥 ⮬᪨,   ਫ  易 
.
     ਫ, ⮡  ALTER SESSION
. ਬ, ⮡ 믮 ALTER SESION   SQL*Forms ᨨ
3.0, 맮 SQL*Forms,ᯮ s-  맮 SQL*Forms(Design),
ᯮ  ⨪.   ⥫쭮 ଠ樨  SQL*Forms
  ࠢ筨  ४஢ SQL*Forms (SQL*FOrms Designer's
Reference).

        襭 ஢  .
        --------------------------------------
⮡ ࠧ SQL ஢   짮⥫ , ⠭
稭 INIT.ORA ࠬ SQL_TRACE = TRUE.  ࠧ ᡮ ⨪ 
 ᥠᮢ 짮⥫.    ஢  
ᥠ, 믮  ⮬ ᥠ SQL :

ALTER SESSION SET SQL_TRACE FALCE

 SQL ஢ ࠧ襭  ᥣ ,  ᮧ
஢ 䠩  ࠧ,  짮⥫ 稭  稢
ࠡ. । ᪮ TKPROF , ,   
஢ 䠩  室 䠩.

ਬ砭:  ᪮쪮 ஢ 䠩 ᮧ ᫥  
  ᮤঠ ,  ࠦ ࠡ ⮢ ᮢ. 
ᮡ, ᮤঠ ⨪,  ࠦ 㬬 ࠡ I/O 
ࠦ  ࠧ  ࠧ   .  
᪮쪮 䠩  ஢.


                                    -- 78 --



        SQL ஢ 䠩  䠩 ᨩ.
        ----------------------------------------
  ࠧ蠥 ஢ ,  뢠 뢠
஢ 䠩  ࠧ,  짮⥫ 稭 ࠡ 
稢.   㪮  ⠭  ᯮ짮 
ଠ樨   ஢ 䠩  ࠡ    襩
樮 ⥬. ᫨  樮 ⥬ ࠭  ᨩ
䠩, 㡥,   ᨩ 祭  稭  饣 ᫠
஢ 䠩, 砥  SQL ஢. ⭮  ᮧ
 ஢ 䠩   ᫥    ࠧ  ⠫
"user dump".

         TKPROF.
        --------------
TKPROF ॢ ஢ 䠩, 祭  SQL ஢  ଠ
 ⥭. TKPROF  ⠪  ᯮ짮  祭 뢮 EP
. 맮 TKPROF ᫥騬 ࠧ:

TKPROF tracefile outputfile
  [SORT = {sortoption  (sortoption[,sortoption...])}] [PRINT = n]
  [EXPLAIN = username/password]

⨢  ᢥ稢, ᫨  뢠 TKPROF  㬥⮢
 ப.  ।⠢ 㬥,    ᯮ짮
 맮 TKPROF.   ࠧ "ਬ TKPROF", ⮡ 㧭 
  㬥.

tracefile    㬥   䠩,   㤥
            뢠 SQL ⨪ ᫥ ࠧ襭 ஢

outline      㬥   䠩,   TKPROF
            㤥 뢠 ᢮ 뢮.

SORT          ਢ  ⮬,  ⨪  室 䠩
            ।⠢  ஢ . ᫨ । , 祬
             ஢筠 , 室 㤥 ஢  室饬
            浪  㬬 稭, ।  ஢ .
            ਬ, ᫨  EXECPU  EXEELA, ⮣ SQL 
              㬬 ६ CPU  elaped time, 祭 
            믮. ஢ 樨 ⠪:

PRSCNT      쪮 ࠧ 믮 ᨭ⠪᪨ ࠧ (ᨭ).
PPSCPU      ६ , 祭  ᨭ.
PRSELA      ६ , 祭  ᨭ.
PRSPHR      ⢮ 䨧᪨ ⥭  ६ ᨭ
PRSCP         ᫥⥫쭮 ⨯, ⠥  ६ ᨭ.
PRSCU        ⥪饣 , ⠥  ६ ᨭ.

EXECNT      ⢮ 믮.
EXECPU      ६ , 祭  믮.
EXEELA      ६ , 祭  믮.
EXEPHR      ⢮ 䨧᪨ ⥭  ६ 믮.
EXECR         ᫥⥫쭮 ⨯, ⠥  믮.
EXECU        ⥪饣 , ⠥  믮.
EXEROW      ⢮ ᥩ, ࠡ⠭  ६ 믮.




                                    -- 79 --



FCHCNT      ⢮ 롮ப.
FCHCPU      ६ , 祭  롮.
FCHELA      ६ 襭, 祭  롮.
FCHPHR      ⢮ 䨧᪨ ⥭  ६ 롮ન.
FCHCR         ᫥⥫쭮 ⨯, ⠥  ६ 롮ન.
FCHCU        ⥪饣 , ⠥  ६ 롮ન.
FCHROW      ⢮ 롨ࠥ ᥩ.

PRINT = n    ⨯ 㬥 ਢ  ⮬,   n(楫)
            ஢ SQL ஢,  뫨 ஠஢ 
            ᥠ ஢   ,   室
            䠩.

EXPLAIN=     ⨯ 㬥 ⠢ TKPROF 믮 EP
username/      SQL ஢  䠩 ஢.
password    EP  뤠  믮 SQL ஢. EP ୠ, 
                ⮬ 㬥 ࠬ username/password,
            ᮧ ⠡ PLAN_TABLE   ,  稢
            믮.  ଠ樨  EP  ᬮ ࠧ "EP
            "  ⮩ . 짮⥫᪨   
            ਢ RESOURCE, ⮡ ᯮ짮 㬥 EXPLAIN  TKPROF.

        ਬ TKPROF.
        --------------
騩 ਬ       TKPROF  樮
⥬ VMS:

TKPROF SQLDBA_JRL_005.TRC R1.PRF SORT=(EXECPU,FCHCPU) EXPLAIN=SCOTT/ TIGER

 ⮬ ਬ 䠩 ஢, 뢠  SQL ஢, 뢠
SQLDBA_JRL_005.TRC. TKPROF.  ᮧ ⨪   
஢. ⨪   室 䠩 R1.PRF  㬬 ६
, 祭  믮  ६ , 祭 
롮 .   䠩 ஢   믮  
   R1.PRF.

뢮 TKPROF.
-------------
  뢮 TKPROF  SQL ,    室
䠩. TKPROF  맢 樥 EXPLAIN    ப:

SELECT * FROM EMP. DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO

          count    cpu    elap    phys    cr    cur    rows

Parse:              1     88      98       3    37      0
Execute:            1      5      12       2     2      2       0
Fetch:              1     10      18       2     2      2      14

Execution plan:
MERGE JOIN
    SORT JOIN
      TABLE ACCESS (FULL) OF 'DEPT'
    SORT JOIN
      TABLE ACCESS (FULL) OF 'EMP'




                                    -- 80 --



 : SELECT,  ஢,  
஢筠 ⨪,   믮, 맢  EXPLAIN. 
⥫쭮 ଠ樨   믮 ᬮ "ਬ EP 뢮"  ⮩
.

        SQL ஢筠 ⨪.
        ------------------------------
 뢮 SQL ஢ ⭮  ᫥騬 蠣 SQL :
parse(),execute(믮),fetch(롮ઠ).

Parse      蠣 ࠭᫨ SQL    믮.   蠣
          砥 ஢ ࠢ쭮 ᠭ  ஢  ⠡,
             㣨 뫮 ꥪ⮢.

Execute    蠣  饭   .
Fetch      蠣 롨ࠥ ,  㤮⢮ .

 ᫥  SQl ஢筮 뢮:
count     ⢮ ࠧ   믮  롮ન SQL ,
          ⮡ 믮 .   맮 롮ન  
          ⠭   .

cpu       祭  ⮬   ६ ,ॡ- 騬 
           蠣 ᯮ, ᯮ  롮ન.  稭
          ࠦ    ᥪ㭤.

elap      稭  ⮬   㬬 ६ 믮, ॡ
          騬   蠣.  稭 ࠦ   
          ᥪ㭤.

phys      稭  ⮬   ⢮  , 뢠
           䠩    ᪥   蠣.

cr        ⢮ ஢, 롨ࠥ  ᫥⥫쭮 .  
          롨ࠥ  ᫥⥫쭮 ,  室 ⥭
          ᫥⥫쭮 ᨨ .

cur       ⢮ ஢, 롨ࠥ  ⥪饬 .  롨ࠥ 
          ⥪饬 ,  室 ᠬ ᫥  . 㬬
          ᫥⥫  ⥪ ⥭ ࠢ 㬬୮  
          ,  뫨 襭.

rows      㬬୮ ⢮ ᥩ, ࠡ⠭ SQL ஬ ( 
          ,  뫨 ࠡ⠭ ᠬ SQL ஢). 
          ஢ SELECT ⢮ ᥩ   蠣 fetch. 
          ஢ UPDATE  DELETE ⢮ ࠡ뢠 ᥩ
            蠣 execute.

ਬ砭: ६ ⨪ ࠦ    ᥪ㭤.  
砥,   樨    ७ 筮.   ᫥ 
    ⨪.  ᮡ ⥫,
 १   ᮢ,  믮 祭
.

        ᨢ 맮.
        -------------------
 ⮣, ⮡ 믮 SQL , , , ॡ
⥫ SQL   .    

                                    -- 81 --



४ᨢ 맮. ਬ, ᫨  ⠥ ⠢   ⠡,
   筮 ࠭⢠,  믮 ४ᨢ 맮,
⮡ 뤥 ࠭⢮ ᪨. ᨢ 맮 믮 ⠪,
 ଠ樨 ᫮     ᫮     
⠭  ᪠.  ᫨ ४ᨢ 맮 ந室  SQL ஢
ࠧ襭, TKPROF ⮡ࠦ ⨪  ஢   
⨪ ஢ ᭮ ணࠬ. ᨢ ⨪ 砥 
४ᨢ SQL .

,  ६ CPU, ६ 믮 ணࠬ, 䨧᪨, 뢭 
⥪騥 ⥭  ४ᨢ SQL ஢   ⨪ SQL
,  맢 ४ᨢ 믮.  ⠪ ⨪ 
 SQL ஢  ஦, ⮡  㤢 祭 ⨪
- ४ᨢ 맮.

         EXPLAIN PLAN (EP).
        

EP  뢠  믮, ࠭ -⨬- ஬ 
஢ SELECT,UPDATE,INSERT  DELETE (DDL    砥). 
믮 -  ᫥⥫쭮  権  冷  権,
   믮  믮 SQL .  믮
   ।   SQL    ᯮ짮
ᮢ.

 ⨢ ଠ樨  EXPLAIN PLAN    䠩
EXPLAIN.DOC .

         室 ⠡ EXPLAIN PLAN.
        ---------------------------------------
। 맮 EP    ᮧ ⠡    ࠭
 뢮. ⮡   ⠡, SQL ⥪, 뢠 XPLAINPL.SQL,
ᮧ  襬 ਡ⨢ ⥫  ⮬  ⠫ CATALOG.SQL. 
祭 ⥫쭮 ଠ樨 樮 ⥬  ᯮ
CATALOG.SQL   㪮  ⠭  ᯮ짮. ⮡
믮 XPLAINPL.SQL,  ᫥饥 ࠦ   SQL*Plus  
SQL*DBA:

SQL> @XPLAINPL.SQL

  ⠪ ᮧ ᢮ ⠡, ᯮ   ᢮ .  
ࠢ 뢮  EP   ⠡, ᯮ ࠦ INTO, ᠭ
  ⮬ ࠧ.

 ⠡, ᯮ㥬  ࠭ 뢮 EP ,   
    ⨯ ,   PLAN_TABLE:

CREATE TABLE PLAN_TABLE  (

    STATEMENT_ID          CHAR(30),
    TIMESTAMP             DATE,
    REMARKS               CHAR(80),
    OPERATION             CHAR(30),
    OPTIONS               CHAR(30),
    OBJECT_NODE           CHAR(30),
    OBJECT_OWNER          CHAR(30),
    OBJECT_NAME           CHAR(30),
    OBJECT_INSTANCE       NUMERIC,

                                    -- 82 --



    OBJECT_TYPE           CHAR(30),
    SEARCH_COLUMNS        NUMERIC,
    ID                    NUMERIC,
    PARENT_ID             NUMERIC,
    POSITION              NUMERIC,
    OTHER                 LONG).

        ⠪ EP .
        -----------------------
⠪ EP  ᫥騩:

EXPLAIN PLAN [SET STATEMENT_ID = 'description'
  [INTO table name]
  FCR sal_statement

:
description     䨪 樨 (ॡ ப  30 ᨬ) 
                . ᫨   ⠭ STATEMENT_ID, ᠭ 㤥 =
                0. ⥫쭮 ᮧ 䨪  ⮣ ,
                ᪮ PLAN_TABLE  뢠  ᯮ짮 㣨
                짮⥫ﬨ.

table name      ࠦ-, ।饥  ⠡ 뢮,   
                  १  EP.  
                ᮤঠ   , ⨯  ,   ⠡
                PLAN_TABLE, ᠭ  ࠧ " ⠡ 뢮 
                EXPLAIN PLAN".  ᫨   饭,  EP
                ⠥ ⠢  ⠡ PLAN_TABLE 楬 ⥪饣
                짮⥫.

sgl_statement    ⥪ INSERT,DELETE  SELECT ,  ண
                   EXPLAIN PLAN.

         ⠡ EXPLAIN PLAN.
        --------------------------
 EXPLAIN PLAN ⮨  ᫥ :

STATEMENT_ID     䨪 樨,   । 
                  EP.

TIMESTAMP        ६    .
REMARKS           ਨ ( 1  80 ᨬ), 묨 
                  ᮯ஢  蠣 塞 . ᫨ 
                 室    ਩  -
                  ⠡PLAN_TABLE, ᯮ  UPDATE.

OPERATION         ७ , 믮塞  ⮬ 蠣. 
                 ⠡ 7-1.

OPTIONS           ⠫쭮 ᠭ , 믮塞  ⮬ 蠣.
                  ⠡ 7-2.
OBJECT NODE       㪠⥫  , ᯮ㥬   ꥪ
                 ( ⠡  ।⠢).

OBJECT OWNER      짮⥫, 宧鶴 ⠡.
OBJECT NAME       ⠡    .



                                    -- 83 --



OBJECT INSTANCE  ᫮, ᮮ⢥饥 筮 樨 ꥪ,  ன 
                 ᯮ  ਣ쭮 . 㬥 ந室
                 ᫥ ࠢ  ⥪ . ,  
                 ᯮ 㤥 祭  ।᪠㥬 ᫠.

OBJECT TYPE      䨪,  ।⠢ ⥫ ଠ 
                 ꥪ  , ਬ, NON-UNIQUE  ᮢ.

SEARCH COLUMNS    ᯮ  饩 ᨨ.
ID               ᫮, 砥  頣 믮.

PARENT_ID        ID ᫥饣 蠣 믮, ஥   뢮 ID
                 蠣. ⮡   ID  PARENT_ID 뢠 冷
                 믮  믮, ᬮ "ਬ 뢮 EXPLAIN
                 PLAN"  .7-1  ⮩ .

POSITION         冷 믮  蠣,      PARENT_ID.

OTHER            㣠 ଠ,  ᯥ筠  蠣 믮 
                  짮⥫   . ਬ, 
                 । ᮢ OTHER  ᮤঠ ⥪ SQL
                 , 뫠  㤠 㧫.

 7-1 ᮤন ७ ,   믮  
६ 믮 SQL :

 7-1.  権, ᯮ㥬 EXPLAIN PLAN.
-------------------------------------------------------

         ᠭ

AND-EQUAL       ᯮ  ࠧ WHEN, ᮤঠ饩
                 ࠢ  AND ,   ࠢ
                砥 㭨쭮 ᭮ .  ⮬ 砥
                   ࠢ  믮 祭.

CONNECT BY    롮, ᭮  ᪥  ॢ.  ᯮ 
                믮 ࠧ CONNECT BY   SELECT.

CONCATENATION 롮  㯯 ⠡. ᭮  UNION ALL.

COUNTING      ,  뢠 ⢮ ᥩ,
              ࠭  ⠡.

FILTER        ࠭祭  ᥩ, 롨ࠥ  ⠡.
FIRST ROW     롮 ⮫쪮 ࢮ  १ .
FOR UPDATE    롮, ࠧ頥   ࠭ .

INDEX         롮  .    樨.
               ⠡ 7-2.
INTERSECTION  롮 ᥩ,   ⠡.  砫
              .
MERGE JOIN    ꥤ, 믮塞  ᫨ﭨ  ஢
                ஢ ࠭.    樨,  
                .  ⠡ 7-2.

MINUS         롮 ᥩ  室 ⠡ 1, ᮤঠ  ⠡
                2.

                                    -- 84 --



NESTED LOOPS  ꥤ , 믮塞  
              稭묨 ﬨ.   ,
              砥  ࢮ 稭 樨,
              믮  稭 . 
                樨,   .
               ⠡ 7-2.
PROJECTION    롮ઠ ⢠   ⠡.

REMOTE        롮  㣮  , ⫨筮  ⥪饩.

SEQUENCE       맮  ᫥⥫쭮.

SORT          롮 ᥩ, ᯮ    
              .    樨,  
              .  ⠡ 7-2.
TABLE ACCESS  롮   ⠡.    樨,
                .  ⠡ 7-2.
UNION         롮 㭨 ᥩ   ⠡. 㡫
              .
VIEW          롮  㠫쭮 ⠡.

 7-2 뢠 樨, 騥 樨, 뢠騥 蠣
믮.

 7-2. , 騥 樨.
--------------------------------------
        樨         ᠭ

INDEX            UNIQUE SCAN     㭨 稭.
                 RABGE SCAN       祭,
                               ࠭   BETWEEN
                               ᪮ .

MERGE JOIN       OUTER         譥 ꥤ.

NESTED LOOPS     OUTER         譥 ꥤ.

SORT             UNIQUE        ஢  祭 㭨
                               祭.
                 GROUP BY      ஢  㯯 権.
                 JOIN          ஢  ꥤ.
                 ORDER BY      ஢  । 浪.

TABLE ACCESS     BY ROWID        ⠡  ROWID. ROWID
                               롨ࠥ  ,⥬ 
                                 ⠡.
                 FULL            ⠡  
                               ᪠஢.
                 CLUSTER         ⠡  ୮ 

ਬ 뢮 EXPLAIN PLAN.
---------------------------
騩 ਬ 뢠 SQL   ᮮ⢥騩 
믮, ᮧ  ᯮ짮 EP .
  롨ࠥ   易 ଠ  㦠, 
௫    ࢠ ⠡ FALGRADE:



                                    -- 85 --



SELECT ENAME, JOB, SAL, DNAME
    FROM EMP,DEPT
    WHERE EMP.DEPTNO = DEPT.DEPTNO
    AND NOT EXISTS
            (SELECT *
                   FROM SALGRADE
                   WHERE EMP.SAL BETWEEN LOSAL AND HISAL)

EP  ࠡ뢠  믮  頥 뢮  ⠡
PLAN_TABLE:

EXPLAIN PLAN
SET STATEMENT_ID = 'EMP_SAL'
FOR
SELECT ENAME, JOB, SAL, DNAME
    FROM EMP,DEPT
    WHERE EMP.DEPTNO = DEPT.DEPTNO
    AND NOT EXISTS
            (SELECT *
                   FROM SALGRADE
                   WHERE EMP.SAL BETWEEN LOSAL AND HISAL)

ଠ ⠡ 뢮 EP .
-----------------------------------
SELECT   ᫥騩 뢮:

SELECT OPERATION, OPTIONS, OBJECT_NAME, ID, PARENT_ID, POSITION
    FROM PLAN_TABLE
    WHERE STATEMENT_ID = 'EMP_SAL'
    ORDER BY ID

OPERATION     OPTIONS   OBJECT_NAME  ID  PARENT_ID  POSITION

FILTER                                1                    0
MERGE JOIN                            2          1         1
SORT          JOIN                    3          2         1
TABLE ACCESS  FULL      DEPT          4          3         1
SORT          JOIN                    5          2         2
TABLE ACCESS  FULL      EMP           6          5         1
TABLE ACCESS  FULL      SALGRADE      7          1         2

 ᯥ஢ ORDER BY ID,  ID ᯥ뢠 
᫥⥫쭮. ,    믮  浪,
ᠭ   ID. ᪮ PARENT_ID 砥 ଠ 
ID, ,  PARENT_ID 㤮⢮   蠣 ID.
ਬ, 蠣 2,merge join  蠣 7,table access,  ᮮ⢥
蠣 1.  㠫쭮 ।⠢ ᫥⥫쭮
믮   ࠧ " ଠ 뢮 EXPLAIN PLAN"

 ଠ 뢮 EXPLAIN PLAN.
-------------------------------------
 ⨯  SELECT ᮧ  ।⠢ 뢮,
஥  筮 ࠦ 冷 蠣, 믮塞 
믮 SQL . 冷 ।⠢ ᮡ ॢ
,   㭪 7-1.




                                    -- 86 --



LECT LPAD('',2*LEVEL)OPERATION''OPTIONS''OBJECT_NAME QUERY_PLAN
     FROM PLAN_TABLE WHERE_STATEMENT_ID = 'EMP_SAL'
     CONNECT BY PRIOP ID = PARENT_ID AND STATEMENT_ID = 'EMP_SAL'
     START WITH ID = 1

QUERY PLAN

  FILTER
    MERGE JOIN
      TABLE ACCESS FULL DEPT
     SORT JOIN
      TABLE ACCESS FULL EMP
    TABLE ACCESS FULL SALGRADE

.7-1. ॢ   믮.
------------------------------------------------
                                Ŀ
                                 1  FILTER
                                
                         Ŀ
                       Ŀ            Ŀ
                        2  MERGE JOIN  7  TABLE ACCESS
                                   
                Ŀ
              Ŀ              Ŀ
    SORT JOIN  5                3  SORT JOIN
                            
                                  
              Ŀ              Ŀ
 TABLE ACCESS  6                4  TABLE ACCESS
                            

 ॢ  뢠  樨 
ந室  祭 믮 SQL , 室  
樨  㣮.  蠣  믮 祭 ஬
(⠢騬  ID  ⠡ PLAN_TABLE)  ࠦ  㧥.
⮬ 믮  㧫  室  㧫-த⥫
  ॢ.

         .
        

              .
        

  ⮩ 樨 ࠧ㬥,   ࠨ  ,
 㦥 饭. ,  ⥫ ஥ ⢨  
  믮  ६ ⠫樨.  ਫ ᮤন ஥
४樨  ⠫樨  .  ⨬   ६
⠫樨,  ᬮ 㬥 室 ன   ६ 
ࠡ.  ਫ ᮤন ४樨  ᫥ ⠫樮
⢨:
-  稭 INIT.ORA ࠬ஢  祭  㬮砭
- । ⪠ ᥣ⮢
- । /뢮




                                    -- 87 --



          INIT.ORA .
        

 ࠧ ।⠢ INIT.ORA ࠬ, 祭  㦭  
祭  㬮砭   ந ⠫    6.0.

DB_BLOCK_BUFFERS    ࠬ । ⢮ ஢   ஢ 
                   SGA. ⢮ ஢   ࠡ .  襣
                   ࠧ ᭨ - ⢮ ᥩ  .
                   ,让   ॡ ᫨誮   
                   맢   ᢮.

                   業 ⢮   ,   
                   ਫ 頥  , ⠡,
                     ⪠ ᥣ.  業  㡮
                   業 쭮 ⢠ ஢, ஥ 
                   ᮤঠ .   ⥫쭮 ଠ樨  
                   ஢   ਫ  "  
                    (DBWR)".

        ࠬ  ᫮ .
        ------------------------------
21 ࠬ  ᫮  थ ࠧ  ᫮   SGA.
  ࠬ 稭  "DC_".  ࠬ । ⢮
⮢   ᫮ . ,,   㢥
稭  ࠬ஢   祭  㬮砭:

DC_COLUMNS   ⠭  ࠬ ᮮ⢥騬  , 
              뫠  ࠫ쭮 ࠡ騥 짮⥫. 
             設⢠ ਫ 祭  㬮砭, ࠢ 300, ᫨誮
             .

DC_TABLES    ⠭  ࠬ   ⠡,   뫠
               ६ ࠡ騥 짮⥫.  設⢠
             ਫ 祭  㬮砭, ࠢ 100, ᫨誮 .

 ⥫쭮 ଠ樨   ᫮     3
"ன । ".

FREE_LIST_PROC      ࠬ । ⢮ ᢮ ᯨ᪮ 
                    ⠡.  ᯮ ᢮ ᯨ᪨ 
                   ࠭ , ᯮᮡ ਭ  . ᫨ 
                   ᮢ ६ ⠢       
                   ⠡,  ᫥ 㢥 稭 ⮣ ࠬ.
                   ᫨    㯭   ,
                   ᨬ쭠 稭 ⮣ ࠬ = 32. 
                   ⥫쭮 ଠ樨  ᢮ ᯨ᪠  
                    6 "⥫ ஥ ४樨".

LOG_ALLOCATION      ࠬ । ⢮   䠩 ॣ
                   樨 ,⢮,⢥  , 
                    ॡ  ࠭⢠  䠩. ᫨  
                    㯭   , ⠭ 
                   ࠬ 稭 ,祬 ࠧ 襣 䠩 ॣ
                   樨 ,⮡ । 㦭 ।.




                                    -- 88 --



PROCESSES           ࠬ । ᨬ쭮 ⢮ ᮢ
                   樮 ⥬,   ᯮ짮 
                   ६. 稭 ⮣ ࠬ   5 
                   䮭 ᮢ   1    짮⥫.
                   ., ᫨    50 짮⥫ ६-,
                   ⠭ 祭 ⮣ ࠬ   55.

ROLLBACK_SEGMENTS   ࠬ ।⠢ ᯨ᮪ ⪠ ᥣ⮢,
                    ॡ    ⥬.  ᮪ 
                   ⪠ ᥣ⮢  祭 ⮣ ࠬ.
                   ⮡ । ᪮쪮  㦭 ⪠ ᥣ,
                     ࠧ "। ⪠ ᥣ⮢"
                     ⮬ ਫ. ᫥ ⠫樨 ⥬ 
                   ᫥ ᮧ    ⪠ ᥣ  ⠡筮
                   ࠭⢥ SYSTEM    ⪠⭮ ᥣ
                   ⥬  ⮣,   ᬮ ᮧ    ꥪ
                    .

SEQUENCE_CACHE_     ࠬ । ⢮ ᫥- ⥫쭮⥩,
                      ஢  SGA  ண 㯠.
                   祭  㬮砭 = 10.   祭 室 
                   設⢠ ਫ.  ᫨  ਫ ᯮ
                    10 ᫥⥫쭮⥩ ६, 㢥
                   祭 ⮣ ࠬ.

SORT_AREA_SIZE      ࠬ । ⢮ , 뤥
                     権 ஢.  祭  㬮砭
                   筮 筮   設⢠ ஢. ,
                   ᫨  ᮧ 祭 訥   믮 SQL
                     ࠦﬨ GROUP BY  OTHER BY, 
                   ॡ 祭  ⠡,  ᫥ 㢥 祭
                   ⮣ ࠬ.
                    ⥫쭮 ଠ樨  믮
                   ஢    6 "⥫
                   ஥ ४樨".


          .
        
ࠢ쭮 뤥 ⪠ ᥣ⮢ 室  ⨬쭮 㭪樮
஢  .   ⢮ ⪠ ᥣ⮢, ॡ㥬 
⨬쭮 㭪樮஢,   襣 ਫ.  ⥫쭮
ଠ樨    5 "ன 䫨⮢".
 ᫥ ᪮쪮 ४権, ᭮  ⢥ ६
믮塞 ࠭権  襩 ⥬.  ४樨 ᮮ⢥
設 ਫ:

⢮ ६     㥬 ⢮
࠭権                   ⪠ ᥣ⮢

 16                     4 ᥣ
 16  32                  8 ᥣ⮢
32                      n/4,   50

 ᮧ ⪠ ᥣ⮢ ᯮ SQL  CREATE ROLLBACK
SEGMENTS.  ⥫쭮 ଠ樨   㪮   SQL.



                                    -- 89 --



        롮 ࠧ஢ ⪠ ᥣ⮢.
        ----------------------------------
  ⪠ ᥣ⮢ ⠪   ࠡ ⥬.  ࠧ
। ࠭塞묨 ࠬࠬ   CREATE ROLLBACK SEGMENT. 
⪠ ᥣ   筮 訬, ⮡ ᮤঠ ⪠
   ࠭権.

          ᮢ   ࠭権.
        ------------------------------------------
 ਫ 믮    묨,  ६
  㣨 ࠭ﬨ.  ᠬ    
⪠ ᥣ⠬, ⮡ ⠭   ஢ .
 ⪠ ᥣ   筮 訬, ⮡ ᮤঠ 
⪠     믮 .

让 ⪠ ᥣ  ⠪  믮  ࠭樨,
 ८ࠧ 訥 ꥬ .  ࠭ ᮧ 訥
⪠ . ᫨ ⪠   室  ⪠ ᥣ, 
 ᥣ. ᪮ ७ ᭨ ந⥫쭮  
᫥ - .

,    ⪠ ᥣ  ࠧ.   
讣 ⪠⭮ ᥣ  ࠭ ⮣,  让 ⪠ ᥣ
 맮 ७, ᪮ ࠭樨   ⪠ ᥣ⠬
砩 ࠧ,      㢥७,   ࠭ 㤥
祭  让 ⪠ ᥣ,    쪨.

         OLTP ਫ.
        --------------------
 ਫ 믮 "on line transaction processing"  OLTP.
OLTP-ਫ ࠪਧ 묨  ६ 믮塞묨
࠭ﬨ,    ꥬ . ᫨  ਫ 믮
⮫쪮 OLTP   ᮢ   ࠭権,   
ࠡ, ᮧ  쪨 ⪠ ᥣ⮢.    
⠪ ⢮ ⪠ ᥣ⮢, ᪮쪮 ६ 믮塞
࠭権.  ⮬  ࠭ 頥  ⤥쭮 ⪠⭮
ᥣ  ᪠ 䫨.  쪨 ⪠ ᥣ ⥫쭮 ࠭ 
 ஢,      .  LOTP ⪠ ᥣ
  2 ⥭,  ࠧ஬ 浪 10 .


         / (I/O).
        

ࠢ쭮 । I/O  ⭮  ࠡ. I/O  
।  ६ ⠫樨 . । I/O  ६ ⠫樨
 㬥 室  । ⮬,  ६ ࠡ. 
冷 । I/O  ६ ⠫樨 :
- ࠧ饭 䠩 ୠ ॣ樨 
- ࠧ饭 䠩  
- ࠧ ⠡  ᮢ
- 롮 ⭮  (ᥩ  )

  ଠ樨   㭪    4
"ன /뢮".

        ਫ .           (DBWR-).
                            

                                    -- 90 --



DBWR-  ࠢ  ஢  SGA ⥫쭮  
ᨨ 6.027  .  ।   ᨨ
6.0 DBWR-  ᭮  ஥筮 .  
ᨨ 6.027  ᫥  DBWR-  㦭
㤥    ன.

 ਫ 뢠 ࠡ DBWR-   ᨨ
6.027  ᫥   ᨨ 6.0.  㦤
᫥騥 ⥬:

-   ஢
- ⥭    ஢
-     ஢
-    䠩  
-  DBWR  
-  DBWR
- ன DBWR

          .
        
   ஢ ᮤঠ    ,  
   .     ஢ 
ᮤঠ   . ⢮ ஢   ।
INIT.ORA ࠬ஬ DB_BLOCK_BUFFERS.

        ᪨  ஢.
        --------------------
   室   ᯨ᪠:
- ୮ ᯨ᮪
- LRU-ᯨ᮪

୮ ᯨ᮪ ᮤন ,  뫨 ᯮ짮   
ᠭ  .
LRU-ᯨ᮪ ᮤন ᢮ , -  ᯮ짮
,    ॢ  ୮ .  
-  ,    ᯮ짮. - - 
,      ࠡ.
 -    ,  頥  
 LRU-ᯨ᪠,  뢠 MRU-.   ⮣, 
 த ६  MRU- LRU-ᯨ᪠,
ᯮ짮  ⥯ 㯠  LRU- ⮣ 
ᯨ᪠.

            .
        
  짮⥫  㦭    ,
୮ ᠭ   ஢,  頥 ,
ᮤন    MRU- LRU-ᯨ᪠. ⥬  砥
  ⮬ .
  짮⥫ 㦭    , ண
    ஢,      䠩 
,  室  ᪥,   .  ⮣, 
 ⠥    ,   ᭠砫 
᢮ .  ᬠਢ LRU-ᯨ᮪, 稭  LRU-
 ⮣ ᯨ᪠.      ᢮  
    , । INIT.ORA ࠬ஬
DB_BLOCK_MAX_SCAN_CNT.


                                    -- 91 --



  ६    ,    ᯮ짮
  LRU-ᯨ᪥.   ६頥    ୮
ᯨ᮪  த ᪠.   室 ᢮ ,
 ⠥     ६頥   MRU- LRU-ᯨ᪠.
᫨  짮⥫  室 DB_BLOCK_MAX_SCAN_CNT
ࠬ   ᢮ ,   ⠭  
ᮮ頥 DBWR-,  㦭    ᯮ짮
஢  .  ࠧ " DBWR  "   ⮬
ਫ.

             .
        
  䠩     믮 DBWWR-ᮬ,
䮭 ᮬ, ⢥⢥  ࠢ  ஢.
 ࠡ ⮣  ࠭  묨.   ⮣
   "吝" ᠬ 짮⥫, ⢮
᢮ ஢ 㬥蠥. ᫨ ⢮ ᢮ ஢
⠭ ᫨誮 ,  짮⥫,  
   ᪠  ,  ᬮ  ᢮ .
ࠢ DBWR- ᢮  ⮬,  짮⥫᪨
 ᥣ   ᢮ .
LRU   ஢ 㦭, ⮡ 㬥 I/O. , 
 ᯮ, ..   㯮, 쪨 ⠡
  ࠭   ⠪,    㦭  ᭮ 
 ᪠.   । 㯮, ..   祭  ⠡
   祭  ᮢ,    ᫥ ⮣, 
 뫨 ஢, ⮡ ⠢    ஢ 
㣨 . 奬 LRU  ,   ॡ騥
㯠, ⠢   ஢ ⠪, ⮡,  
뢠  ,  ⭮,  ᮤঠ騥    
 ᪮ ᯮ짮. , ᫨ DBWR- ⠭
᫨誮 ⨢,    ,    ᭮
ᯮ짮  ᪮஬ ६,  .

         DBWR  .
        
DBWR- 砥 ᨣ   ᯮ짮 ஢ 
  ᫥ ᫮:

-   짮⥫ 頥   ୮ ᯨ᮪ 
  ,    ⨣ ண , 
  짮⥫   DBWR  . ண 
  ।   祭 DB_BLOCK_WRITE_BATCH  INIT.ORA
  ࠬ.

-   짮⥫  DB_BLOCK_MAX_SCAN_CNT , 
   ᢮ ,  짮⥫ ⠭  
  LRU-ᯨ  뤠  DBWR  .

- १ । ࢠ ६ (timeout), 3 ᥪ㭤
  DBWR- 뤠  ᠬ ᥡ.

-  砥 ஫쭠 窠,   ୠ
  ॣ樨 (LGWR-) 뤠  DBWR-.

    DBWR- 뢠  ୮ ᯨ᪠
   ᨣ multy-block write. ⢮ ,
뢠  ⮬ ᨣ, । INIT.ORA ࠬ஬

                                    -- 92 --



DB_BLOCK_WRITE_BATCH. ᫨ ୮ ᯨ᮪  ᮤন
DB_BLOCK_WRITE_BATCH ,  DBWR 砥 ᨣ, 
DBWR-  LRU-ᯨ᮪    ⥫ ᯮ짮
.
१  3 ᥪ㭤 ந室 timeout.  ⮬ 砥
DBWR-  । ⢮ ஢  LRU-ᯨ᪥ 
 ,  ,  . १ 3 ᥪ㭤 DBWR-
   ஢. ⢮ ஢,    
timeout -   祭 DB_BLOCK_WRITE_BATCH ࠬ.
᫨   ⠨, DBWR- ९뢠 
 ஢  .
 砥 ஫쭠 窠 LGWR- । ᯨ᮪
஢ ஢,    ९ᠭ  .
DBWR-     .

         DBWR.
        
 ⠢ ⨪ ⥫쭮 DBWR-:

dbwr free low     祭 ⮩ ⨪ -  ⢮ ࠧ,
                  ஥ DBWR- 砫 ᨣ   
                  砥, ᫨ ⢮ ஢  ୮ ᯨ᪥
                  뫮 ࠢ  祭 DB_BLOCK_WRITE_BATCH.

dbwr free needed  祭 ⮩ ⨪ -  ⢮ ࠧ,
                  ஥ DBWR- 砫 ᨣ  ,
                    짮⥫ ᪠ DB_BLOCK_MAX_
                  SCAN_CNT ,   ᢮ .

 ⨪    SQL*DBA MONITOR STATIATICS
CACHE ࠭.   ࠭    SQL*DBA :

        SQLDBA> MONITOR STATISTICS CACHE

ࠦ SQL*DBA MONITOR STATISTICS CACHE 砥  
࠭. .-1  -2 뢠  ࠭.   -2  
 ன ࠭, 뢠騩 ⨪ dbwr free low  dbwr
free needed.
ࠢ  ஢   ࣠ ⠪,  
짮⥫  㦭 ᪠ DB_BLOCK_MAX_SCAN_CNT ,  
᢮ .  ⮩ 稭 祭 dbwr free needed 
 ᥣ = 0.  .-2   ,  㬬୮ 祭
dbwr free needed ⨪ = 0.
᫨   祭 dbwr free needed ⨪ ⫨筮 
0,  ,  稭 DB_BLOCK_MAX_SCAN_CNT  뫠 ⠭
筮 . 祭 ⮣ ࠬ  ᫥  
祭  㬮砭 = 30 ஢.

        .-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 

                                    -- 93 --



 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 
 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 


        .-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 


         DBWR.
        
     ஢ ᯥਬ 
㢥祭 祭 INIT.ORA ࠬ DB_BLOCK_WRITE_BATCH 
祭  㬮砭. 祭 ⮣ ࠬ :

- ᭨ ,  ன  짮⥫  ᨣ DBWR
   .

- 㢥 ⢮ , ஥ 뢠 DBWR.

祭 DB_BLOCK_WRITE_BATCH   ᫥騥   DBWR:

- ᯮ짮  樮 ⥬ 뢠  
  ࠧ ᪨ ࠫ쭮.

-  ᥤ    /뢮.

      .      INIT.ORA .
                       

। INIT.ORA ࠬ   ࠡ 襩 ⥬. 
, 믮  ࠬ஢ ᠭ  ⮬ ਫ  ᫥饬
ꥬ:
-  ࠬ

                                    -- 94 --



- 㭪  ᠭ
- 祭  㬮砭    祭
-    ࠬ
- ⢨  ࠬ
- 뫪  㣨 INIT.ORA ࠬ  

⮡  INIT.ORA ࠬ,  ᫥   ᭮ ⮢ .
 INIT.ORA ࠬ,  ࠧ ᮢ ࠡ ,
㢥稢 ॣ㧪.  ⠪ ࠬ, : TIMED_STATISTICS_SQL_TRACE,
DB_BLOCK_LRU_STATISTICS, DB_BLOCK_LRU_EXTENDED_STATISTICS  ࠡ⪠
ᯥ䨪権.   室  㢥७,   ⪫稫  ࠬ,
   㦭.

   ⠭ 襩 ⥬ ⥪ ࠬ஢  
SQL*DBA :

        SQLDBA>SHOW PARAMETERS

  14 "⨥  ⨥  " 㪮⢠ 
 , ᫨   ᭥  SQL*DBA.

        CONTEXT_AREA
        
ᠭ          ⢮   ,஥ 뤥 ⥬ 
                  ⥪⭮ . ⥪⭠  ᯮ 
                    , 室  믮 SQL
                  .

祭        4096 
㬮砭

  ᨬ ࠧ    1024  131072
祭          .    襩 樮 ⥬.
                  ४騥 ணࠬ 짮⥫  OCI (ORACLE CALL
                  Interface)  ⠭ 㣮 ࠧ.

       ᫨  ਪ :
                  -    SQL 
                  - ⠡  訬 ⢮ 
                  - ⠡  묨 ﬨ
                   ࠬ 室  㢥祭 砫쭮 ࠧ
                  ⥪ ⥩.  ⥪⭠  뤥 
                    .  ⮡ 㢥 ࠧ ⥪⭮
                   ᯮ ࠬ CONTEXT_INCR.

⢨ -  祭 ⮣ ࠬ ਢ  ⮬, 
 ࠬ     짮⥫ ᯮ  .
                  ந⥫쭮 CPU  ⮬  ⥫쭮 
                  㬥蠥 ᪮ ७ ⥪⭮  
                   讣 SQL .

뫪             3 "ன । ", 㪮⢮
                  짮⥫  ⠫樨,  8 "⥪ "
                  㪮⢠   .

        CONTEXT_INCR
        


                                    -- 95 --



ᠭ          ⢮ ,  ஥ ⥪⭠  㢥稢
                   ࠧ饭 讣   , ॢ
                  室 砫쭮 ७.  ᫨   
                  뤥 ⥫  뤠 ᮮ饭  訡.

祭        4   設⢠ ⥬
㬮砭

   1024  32768 
祭

       ᫨ CONTEXT_INCR 祭 , 㦭  蠣, ⮡
                   筮     믮 SQL
                  ஢. ᫨ CONTEXT_INCR ᫨誮 让, 㤥 
                   .  ४騥 ணࠬ 짮⥫  OCI
                  (ORACLE CALL Interface)  । ࠧ- 
                  砫 ࠧ.

⢨ -  ࠬ 㢥祭,  㤥 뤥
 ࠬ       ⥪⭮ , -
                  ⥫쭮 ᭨ ந⥫쭮 CPU.

뫪             3 "ன । ", 㪮⢮
                  짮⥫  ⠫樨,  8 "⥪ "
                  㪮⢠   .

        CPU_COUNT
        

ᠭ           ⢮ CPU, 㯭  .  
                  ⠭ 祭 ⮣ ࠬ ⮬᪨. 
                  ୮   祭 ⠭ =
                  0.  祭 CPU_COUNT ।  㬮砭 - 
                  INIT.ORA ࠬ LOG_SIMULTANEOUS_COPIES.

祭          樮 ⥬.
㬮砭

        ⠭  祭 ⮬᪨.  
                  祭 ⮣ ࠬ.

⢨ - ᫨ ⢮ 饫 ஢ = 0,
 ࠬ       饫 ।. ᫨ CPU_COUNT
                  = 0, LOG_SIMULTANEOUS_COPIES ⮦ = 0, ⠢ 
                  짮⥫ ᥣ ஢  饫 ।. ᫨
                    ஢    饫 ।
                   ᫥ ⠭ ࠬ LOG_ SIMULTANEOUS_COPIES,
                     ࠬ.

뫪             "饫 ஢"   5, " 祭
                  INIT.ORA ࠬ஢"  ਫ ; LOG_SIMULTANEOUS_COPIES.

           .
        





                                    -- 96 --



ᠭ          INIT.ORA "DC_" ࠬ ஫ ࠧ  ᫮
                  . ஢   V$ROWCACHE ⠡,  
                  । ⭮  ⠭  ࠬ. 
                  ᠭ   . 㭪, 祭  㬮砭 
                    祭   .

뫪             3 "ன । ", " 祭
                  INIT.ORA ࠬ஢"  ਫ .

         ᫮ .
        --------------------

DC_COLUMNS_GRANTS  ⢮ ⮢   grant cache.  襭 1 
                   , 饣 grant.  祭  㬮砭 = 50.
                  祭    1  ࠭祭 祭.

DC_COLUMNS        ⢮ ⮢  , 뢠饬 .  祭
                   ࠢ   ᠬ 讬  
                    ⠡.  襩 ࠡ 祭  
                        ⠡,  
                  뫠  ६  ࠫ쭮 ࠡ騥
                  짮⥫. 祭  㬮砭 = 300.  
                  祭  150, 孥 ࠭ .

DC_CONSTRAINT_    ⢮ ⮢  ,ᮤঠ饬 
DEPS              ࠭祭 楫⭮ (constraint cache).  祭 
                  㬮砭 = 200,   祭  ࠭祭.

DC_CONSTRAINTS    ⢮ ⮢ ࠭祭 楫⭮  . 祭
                   㬮砭 = 150.  -  祭 
                  ࠭祭.

DC_FILES          ⢮ ⮢  䠩, 뢠饬 .  襭 1
                    䠩, 易  ࠭⢮ ⠡  ࠡ.
                  祭  㬮砭 = 25.    ࠭祭.

DC_FREE_EXTENTS   ⢮ ⮢  free extent description  ( 
                  । 㠫쭮 ७ ᢮ ࠭⢠
                   ᨬ  ࠧ). 祭  㬮砭 = 50.
                    祭  5  ࠭祭 祭.

DC_INDEXES        ⢮ ⮢   ᮢ. 祭  㬮砭 =
                  50.   祭  5  ࠭祭
                  祭.

DC_OBJECT_IDS     ⢮ ⮢  , 饬 ꥪ.
                  祭  㬮砭 = 50.   祭  1
                   ࠭祭 祭.

DC_OBJECTS        ⢮ ⮢  ꥪ⭮  ( , 뤥饬
                  ꥪ  ,  ਬ ⠡, , ,
                  ᫥⥫쭮, ᨭ  ).  襣
                  믮 祭   ⠪, ᪮쪮 ꥪ⮢
                  ᯮ ᥬ 짮⥫ﬨ      ६.
                  祭  㬮砭 = 100.   祭  50
                   ࠭祭 祭.



                                    -- 97 --



DC_ROLLBACK_      ⢮ ⮢  , 뢠饬 ⪠
SEGMENTS          ᥣ. 祭  㬮砭 = 25.  
                  祭  2    ࠭祭.

DC_SEGMENTS       ⢮ ⮢  , 뢠饬 ᥣ.  襭
                  1   ⠡, ,   ⪠⭮ ᥣ,
                  ᯮ㥬      ६. 祭  㬮砭 =
                  50.    50   ࠭祭 祭.

DC_SEQUENCE_      ⢮ grant  ᫥⥫쭮⥩, 
GRANTS              ஢. 祭  㬮砭 = 20.  
                   祭  2  .

DC_SEQUENCE       ⢮ ⮢  , 뢠饬 ᫥⥫쭮.
                  祭  㬮砭 = 20.   祭  2 
                  .

DC_SYNONYMS       ⢮ ⮢  ,ᮤঠ饬 ᨭ.  祭 
                  㬮砭 = 50.   祭  2  .

DC_TABLE_GRANTS   ⢮ ⮢  table grant .   
                   ⠡  grant. 祭  㬮砭 = 50. 
                   祭  2  .

DC_TABLES         ⢮ ⮢  , ᮤঠ饬 ⠡,  
                  . 祭  㬮砭 = 100   
                  祭  30  . , ⭮,  
                  㢥  ࠬ  ⢠ ࠧ ⠡, 
                   뫠 ࠧ 짮⥫, ࠡ騥   
                    ६.     祭 ⮣
                  ࠬ  祭  㬮砭, ⠭  ᨨ
                   6.0. ᠭ ⮣   ਫ .

DC_TABLESPACE_    ⢮ ⮢  , ।饬 
QUOTAS             ࠭⢠ ⠡. ࠭⢮ 뤥
                  쭮   짮⥫   ⠡.
                  祭  㬮砭 = 25. 室  1   
                    ⠡筮 ࠭⢠   ࠫ쭮
                  ࠡ饣 짮⥫.  ,  ࠭祭
                  ਢ RESOURCE   .

DC_TABLESPACES    ⢮ ⮢  , 뢠饬 ࠭⢮
                  ⠡. 祭  㬮砭 = 25   
                  祭  25  .  㦭 1  1 ⠡筮
                  ࠭⢮.

DC_USED_EXTENTS   ⢮ ⮢  , 뢠饬 ᯮ㥬
                  ७. 祭  㬮砭 = 50   
                  祭  50  .

DC_USER_NAMES      , ᮤঠ饣  , ।塞 짮
                  ⥫. , ⠪  SCOTT   ᮮ⢥騬
                   ࠬ. 祭  㬮砭 = 50   
                  祭  1  .  ࠬ   ⠭ 
                   ࠧ  짮⥫,   
                  뫪   ᥭ  ୠ ॣ樨 
                  ࠭.


                                    -- 98 --



DC_USERS           ,  ᮤন 䨪樮
                  ,ᠭ  DC_USERNAMES. 祭  㬮砭 = 50
                     祭  1  .

        DB_BLOCK_BUFFERS
        

ᠭ          । ⢮ ஢   ஢  SGA.
                   ᮤন    .   祭 ⥬
                  , 祬  ॡ I/O, - 㢥祭
                  ⭮,  ॡ㥬  ஢  . ,
                  ᫨ 祭 ᫨誮 , SGA  ࣠
                  ,  ⮬ ᭨ ந⥫쭮.

                      祭 ⮣ ࠬ 
                  祭  㬮砭, ⠭ ᨥ  6.0. 
                  ᠭ  ਫ .

祭        । ⨢ ⥬.
㬮砭

   4  65535
祭

        hit ratio  60%-70%, 㢥祭 ⢠ ஢
                    믮 ਫ. ਫ, 
                   ᯮ 騥 , ⮦ 먣  㢥祭.

⢨ -  稢 祭 DB_BLOCK_BUFFERS,  ⠪
 ࠬ    㢥稢:
                  - ࠧ  SGA,  ᮤন .
                  - ⢮ ,   ஢.
                  - ⭮,  ⠭  
                  - ᯮ㥬 
                  - ,᫨  ९
                  ᭮  -   믮,   
                  .  ।  㢥祭 ࠧ஢ ஢ - 
                  ,  ᯮ ந⥫쭮  㢥稢
                  ⭮    SGA  짮⥫᪮
                  .   㬥  ࠭
                  ᮮ⢥饩 ன 襩 樮 ⥬.

뫪            "᫥ ⥫쭮  ஢" ࠧ  3,
                  "  ஢" ࠧ ਫ ,"
                  祭 INIT.ORA ࠬ஢"  ਫ ,
                  짮⥫  ⠫樨.

        DB_BLOCK_LPU_EXTENDED_STATISTICS
        

ᠭ          蠥 ࠭ ⨪  ⠡ X$KCBRBH, 
                  㢥稢 ⢮ ஢   ஢  SGA. 
                   ࠧ襭,    ࠭ ⥫
                  DB_BLOCK_LRU_ EXTENDED_STATISTICS    ᪠,
                  㯭  室 ࠭  ⥫
                  ஢.  ஥ ணࠬ ᫥ ⪫  砥
                  ଠ쭮 ࠡ.


                                    -- 99 --



祭        0
㬮砭

   0    ࠭祭
祭

        ࠭᫨ ⨪  ⠡ X$KCBRBH, ⠭
                   ࠬ  ᨬ ࠧ, ᫨   
                  ᯮ짮  業  ஢.  ⨢ 砥
                  ⠭   0.

⢨ -  襭 ࠭᫨஢ ࠧ ⨪.
 ࠬ

뫪             "襭   ⠡ X$KCBRBH"   3.

DB_BLOCK_LRU_     蠥 ࠭ ⨪  ⠡ X$KCBCBH,
STATISTICS         㢥稢 ⢮ ஢    SGA. 
                  ஥ ணࠬ ᫥ ⪫  砥 ଠ쭮
                  ࠡ.

祭        FALCE
㬮砭

  FALCE/TRUE
祭

       ⠭  ࠬ  TRUE,   
                  ࠭᫨஢ ⨪  ⠡ X$KCBCBH.  ⨢
                  砥 ⠢ ⠭ FALCE.

⢨ -  ⨪  묨  ⠡ X$KCBCBH
 ࠬ

뫪             "襭   ⠡ X$KCBCBH"   3.

        DB_BLOCK_MAX_SCAN_CNT
        
ᠭ          ⢮ ஢, ஥  짮⥫ 㤥
                  ᪠஢,     LRU-ᯨ᪥,  맮
                   DBWR.

祭        30 ஢
㬮砭

   0  祭 ࠬ DB_BLOCK_BUFFERS.
祭

       権   祭 .

뫪             "  ஢"  ਫ .

        DB_BLOCK_SIZE
        
ᠭ          ⠭ ࠧ      .
                  祭 DB_BLOCK_SIZE । ࠧ   ᮧ
                    ஬ CREATE DATABASE;   㣨 


                                   -- 100 --



                  祭   ⠭ 㢫쭮.  
                     ᫥ ᮧ   ⮫쪮 
                  ᮧ   .

                   ࠬ ⠪ । ࠧ ஢   ஢
                   SGA, ⢮ , ᯮ㥬   ⠡
                  (७ INITIAL)     뤥  
                   (७ NEXT).

祭        筮  2048  4096 ,  ᨬ 
㬮砭         樮 ⥬.

  । 樮 ⥬.
祭

        ࠬ  ᯮ  ன.  祭
                  筮  ᫥ .

뫪           "। ࠭⢠   "   4.

DB_BLOCK_WRITE_BATCH

ᠭ            I/O ⢮    
                  (DBWR), ।   ࠧ  樮 ⥬
                   .

祭        8 
㬮砭

   1  128 . ᫨ 樮 ⥬ ন
                      ࠧ, 祭 ᫥ ⠭  2
                   8.

           ࠬ  㦭 .
                  ,   룮 㢥稢  
                  ⮣, ⮡:
                  - 㬥 ,  ன 짮⥫᪨
                     뢠 DBWR
                  - 㢥 ⢮ ,   頥 DBWR,
                      ᨣ  

⢨ -  ᮪ 祭 㢥稢  DBWR
 ࠬ   ᯮ짮 ᯮᮡ 樮 ⥬   
                  ࠧ ᪨ ࠫ쭮, ᯮ짮 २⢮
                  ᪮ ᪠ ⨬쭮 ⬠   
                  ᬥ    I/O. , ᫨  ஢
                  쪨,    窥 楯  ⮣  
                  .  ⠪, ᮪ 祭 㢥稢 ⭮
                   짮⥫᪨ ᮬ   ஢
                  ,    窥.   㢥 
                  ࠬ   ⨪ write complete waits  write
                  wait time increasing.

뫪             "ன DBWR"  ਫ .





                                   -- 101 --



        DB_FILE_MULTIBLOCK_READ_COUNT
        
ᠭ          ᯮ  筮 ⥭.  ᨬ쭮
                  ⢮ , ⠥  ࠧ,  믮
                  ᫥⥫쭮 ᪠஢.  祭  㬮砭
                  । DB_BLOCK_BUFFERS  PROCESSES.

祭        । 樮 ⥬.
㬮砭

  । 樮 ⥬.
祭

       ᫨  ਫ 믮   ᪠஢
                   ⠡, 㢥稢 祭 ⮣ ࠬ 
                  祭  㬮砭,   ᯮ.

⢨ -  㢥祭 ⮣ ࠬ 㢥稢
 ࠬ    ⢮ , ⠥   筮 ⥭. 
                  讬 祭    ⮦ ᠬ ⢮
                     襥 ⢮  ⥭.

뫪             "筮 ⥭"   2.

        FREE_LIST_INST
        
ᠭ          ⢮ ஢, 㦭  ᮧ ᢮
                  ᯨ᪮.  , 騥    
                  ᯮ짮  ᢮ ᯨ᮪. ⢮ 
                  ࠢ  ஢ ( 1   ).
                     믮  먣ࠥ  㢥祭 ⮣
                  祭.

祭        1
㬮砭

   1  ⢠ ஢
祭

        ࠬ ᫥ ,   ஢.
                  冷  ।   21 㪮⢠
                    .

뫪             " ᯨ᪨"   6,  "FREE_LIST_PROC"
                    21 㪮⢠   .

        FREE_LIST_PROC
        
ᠭ          ⢮ ᢮ ᯨ᪮   (FREE_ LIST_INST),
                  ᮧ  ⠡. ⢮, ஥ ᯮ,
                   ⠡ ᮧ, ﭭ  ⮩ ⠡.  
                     祭 ⮣ ࠬ  
                  祭  㬮砭  ६ ⠫樨  ᨨ 6.0.
                  ᠭ ⮣   ਫ .

祭        1 ᢮ ᯨ᮪.
㬮砭


                                   -- 102 --



   1  32    .
祭

       ᫨ ⠡,   㤥 ᮧ  祭 ᮪
                  INSERT ⥫쭮 ( 믮 ⠢), ᫨ 
                  ﭭ ᮡࠥ ⨪ buffer busy waits,  
                  ਫ ஢ ⠡ X$WAITSTAT, ᫨   
                  buffer busy waits ⨪    , 
                  ᫥   ࠬ.

⢨ -  㢥祭  ᢮ ᯨ᪨ 
 ࠬ    ⠡,   ᮧ.

뫪             " ᯨ᪨"   6, " 祭
                  INIT.ORA ࠬ஢"  ਫ , "᪮ 믮
                  ⠡ X$WAITSTAT"   5, FREE_LIST_INST.

        LOG_ALLOCATION
        
ᠭ          ⢮  ୠ ॣ樨 , ।⠢
                      ࠧ,  ॡ 
                 ࠭⢠  ⥪饣 䠩 ୠ ॣ樨 .
                   ⢮  , ࠦ  ⨪ redo
                  chunk allocations.      祭
                  ⮣ ࠬ   祭  㬮砭  ⠫樨
                   ᨨ 6.0.  ᠭ   ਫ .

祭        200 .
㬮砭

     ⠭  ࠧ
祭          ࠢ  , 祬 ࠧ ᠬ 讣 䠩 ୠ
                  ॣ樨 .

        ࠬ 㦭   ࠡ   ࠬ.
                  冷  ।   21 㪮⢠
                    .

⢨ -  ⥬   ஬ ⠭ 
 ࠬ    ࠬ , 祬 ⢮   䠩 ୠ
                  ॣ樨  , 祬 redo chunk allocations.

뫪             "LGWR "   5, " 
                  ࢮ砫쭮 ⠫樨 "  ਫ ,  21
                  㪮⢠   .

        LOG_BUFFER
        
ᠭ              ୠ ॣ樨. ᫨ ࠧ
                  ,  ⨪ redo log space   ࠢ 0.

祭        । 樮 ⥬.
㬮砭

   ࠩ   4 ࠧ  ࠧ
ࠬ         ᨬ쭮 .  ࠧ㬭  65536   .



                                   -- 103 --



        祭 redo log space requests  ࠢ 0. 
                  祭 뢠,   짮⥫  
                   .

⢨ - ᫨  䫨   㢥  祭,  
 ࠬ    㢥 ⢮ ஢ ୠ   .

뫪             "࠭⢮   ୠ ॣ樨 "
                    5.

        LOG_CHECKPOINT_INTERVAL
        
ᠭ          ⢮    䠩 ୠ ॣ樨
                   ⠭ ஫쭮 窨.  ᨬ  ⮣
                  祭 ஫쭠 窠 ᥣ 砥  
                  ४祭 ୠ.  ⢮ ஫ 祪  
                     ⨪ statistic dbwr checkpoints.
                  ᫨ LOG_CHECKPOINT_INTERVAL ⠭ ᫨誮 쪨,
                  ஫쭠 窠 砥 ᫨誮   ⠭
                   ந室 .

祭        । 樮 ⥨.

   20    ࠭祭.
祭

       ⮡ 㬥  ஫ 祪, ⮡ 
                  믮, ⠭ 祭 , 祬 ࠧ 䠩
                  ୠ ॣ樨.

⢨ - 稢 , ࠧ  㢥稢 ६
 ࠬ    ⠭,  蠥 믮.

뫪            "⥫ ஥ ᮮࠦ"  6.


        LOG_ENTRY_PREBUILD_THRESHOLD
        
ᠭ          ᨬ ࠧ redo-, ८ࠧ 
                  ஢   ୠ. ᫨  ࠧ襭,  
                  짮⥫ ८ࠧ redo- ଠ   饫
                  ஢.  ᫨ 祭 LOG_SIMULTANEOUS_COPIES = 0, 
                   ࠬ .

祭        0
㬮砭

   0  2048  4096   ᨬ  襩
祭          樮 ⥬.

         ⥬   룮 㢥
                   ࠬ.  ୮ ⥬ ᫥  
                  祭 =0.

⢨ -   ⥬,  䫨 饫, 
 ࠬ    㢥祭 祭 ⮣ ࠬ 㬥蠥 ६ 墠
                  饫 ஢.


                                   -- 104 --



뫪            LOG_SIMULTANEOUS_COPIES, ࠧ " 䫨⮢ 饫
                  ஢"   5.

        LOG_SIMULTANEOUS_COPIES
        
ᠭ          ᨬ쭮 ⢮ 饫 ஢, 㯭 
                   ⮢ ୠ ॣ樨 ६. 
                  襩 ࠡ    ⢮ 饫   ࠧ
                  , 祬 ⢮ CPU.  ୮ ⥬
                  ⠭  祭  0, ..   
                  饫 ।. ᫨  = 0,  ࠬ LOG_SMALL_
                  ENTRY_MAX_SIZE .

祭        ᫨  ᯥ樠쭮 ⠭,  祭 
㬮砭         㬮砭 ࠢ  CPU (CPU_COUNT).

   0    ࠭祭.
祭

         䫨⮢ 饫 ஢.   
                  ࠭ SQL*DBA MONITOR LATCH.

⢨ -  㢥祭 ⮣ ࠬ 㬥 䫨-
 ࠬ    .஢   ᪮쪨 饫 ࠧ.

뫪            "ன 䫨⮢"  5, LOG_SMALL_ENTRY_MAX_SIZE,
                                                  CPU_COUNT.


        LOG_SMALL_ENTRY_MAX_SIZE
        
ᠭ         ᨬ ࠧ      ୠ ॣ樨
                  饫 ।  祭 饫 ஢.
                 ᫨ 祭 ।饣 ࠬ = 0,   ࠬ
                  (      믮 
                 饫 ஢). ᫨ redo-   饫
                 ।,  짮⥫ ᢮ 饫 ᫥
                 ஢. ᫨  , 祬  ࠬ, 
                 짮⥫ ᢮ 饫 ᫥ ।
                 ࠭⢠    祭 饫 ஢.

祭        800  (VMS).
㬮砭

  । 樮 ⥬.
祭

       ⮡ ᭨ 䫨 饫 ।, 㬥 
                  祭.  㬥 ⢮ ⮢, 㥬 
                  饫 ।  ᨬ  ࠧ஢ 
                  ⮢.

⢨ -   祭 㬥襭,  ᭨
 ࠬ    ⢮ ⮢, 㥬  饫 । 
                  㢥稢 ⢮ ⮢, ॡ 饫
                  ஢.



                                   -- 105 --



뫪            LOG_SIMULTANEOUS_COPIES, ࠧ "饫 ।" 
                   5.


MAX_DUMP_FILE_SIZE

ᠭ           ࠬ ᯮ SQL ஢  㣨
                  㭪ﬨ ⥬, ⠪  ଠ ७ 訡,
                   뢠 ஢.   ࠬ 㦥 
                  ஫ ࠧ ஢ 䠩 ( 㭪 
                   ஢ 䠩). । । ࠧ,
                  ⮡   ᥣ ᪮ ࠭⢠
                  ஢묨 䠩. ᫨  ⨣  
                  ஢ 䠩,   ᮪頥.
                   ᪨ 㬥, ⮬ ᫥   
                   ஢,⫠ ..

祭        500  ⨢ ⥬.
㬮砭

   1    ࠭祭.
祭

            祭, ᫨:
                  -  믮 SQL ஢   ᥪ樨
                  -  ᯮ, ஢ 䠩
                     ᫨誮  

⢨ - 祭 ⮣ ࠬ ࠧ蠥 ࠭
 ࠬ     ஢ 䠩,  ᭨  ࠭
                  㣨 䠩.

뫪            "믮 ⮢ ணࠬ"  7.


        OPEN_CURSORS
        
ᠭ          । 㬬୮ ⢮  ஢
                  (⥪ ⥩), ஥   
                  ६. ࠬ  ஫ ᮡᥩ
                  ⥬,  ஫ ᨬ  ࠭⢠
                  , ᯮ㥬  ᮬ. ஫ ⥪
                  ⥩ ᯥ祭   ਫ.

祭        50 ஢.
㬮砭

   5  255.
祭

       ᫨   ᭨ ⢮ ᨭ, 믮塞
                  訬 ਫ, 㢥 ⢮ ஢. ⢮
                  ᨭ    믮 SQL஢.

⢨ - ᫨  㬥蠥 ᨭ SQL ஢, 
 ࠬ    ⢮ ஢  㬥 ⠪,  
                  室 㢥祭, .. 室 
                  ஢.襭   ࠧ㬭.

                                   -- 106 --



뫪             ணࠬ   , ࠢ筨
                  ࠧࠡ稪 SQL*FORMS, ࠧ "⥪  
                  "   3.

        PROCESSES
        
ᠭ          ᨬ쭮 ⢮ ᮢ 짮⥫ 樮
                  ⥬, ஥  ࠡ   ६. 
                    祭 ⮣ ࠬ  祭 
                  㬮砭.  ᠭ  ਫ .

祭        25 ᮢ.
㬮砭

  쭮 ⢮ = 5, ᨬ쭮 
祭           樮 ⥬. 祭 ⮣ ࠬ 砥 5
                  䮭 ᮢ      ࠫ
                  .

       稢  祭  㢥稢 ⢮
                  ࠫ 짮⥫.

⢨ - ᫨ 祭 ᫨誮 ,  樮
 ࠬ    ⥬  ᬮ ন ॡ㥬 ⢮
                  짮⥫.

뫪            "  砫쭮 ⠫樨 " ਫ .


        ROLLBACK_SEGMENTS
        
ᠭ     ࠬ  ᯨ᮪ ⪠ ᥣ⮢, 訢
          .       ⪠ ᥣ⮢
         ⮬ ࠬ.  ᥣ ॡ, ⮡  ᥣ 뫨
        ⮡ࠦ  ⮬ ࠬ,   ⮬ 砥, ᫨ ⢮
        ᥣ⮢ ॢ蠥 ᯨ᮪ 室   (ᯮ
        TRANSACTIONS/ TRANSACTIONS_PER_ROLLBACK_SEGMENT).    
        ᮧ      祢 ᫮ PUBLIC.   
        । ⪠ ᥣ SYSTEM  祭 ⮣ ࠬ.
        ᫥ ⠫樨   ᮧ    ⪠ ᥣ 
        ⠡筮 ࠭⢥ SYSTEM   ⪠⭮ ᥣ SYSTEM 
        ᮧ 㣨 ꥪ⮢  .    
        । 㦤 襣 ਫ,   ᯥ 
        ⪠ ᥣ  ६ ⠫樨.  ଠ樨  
        ਫ  "  砫쭮 ⠫樨 ".

祭        
㬮砭

    祭 ⮣ ࠬ
祭            襣 ਫ. ଠ樨  祭
                  ⢠  ࠧ஢ ⪠ ᥣ⮢ 頩  
                  5 "ன 䫨⮢".

         䫨 ⪠ ᥣ⮢  㦭  
                  ⪠ ᥣ⮢,       ⮬
                  ࠬ.


                                   -- 107 --



⢨ -    ⪠⭮ ᥣ ࠧ蠥 
                    ⪠ ᥣ  ᫥饬  .

뫪            ਫ "砫쭠 ⠫ ",INIT.ORA ࠬ
                  TRANSACTIONS  TRANSACTIONS_PER_ROLLBACK _SEGMENT 
                  㪮⢥    .


        ROW_LOCKING
        
ᠭ          ᯮ  ࠬ ᮢ⭮  INIT.ORA ࠬ஬
                  SERIALIZABLE,⠭  FALSE, ⮡ ⠭
                  ஢  ஢   祭  㬮砭.

                     ᨨ 6.0  樥  ࠭樨
                  ᯮ ALWAYS  ⮣ ࠬ, ⮡ 
                  ஢  ஢   ६  ⠡.

                     ᨨ 6.0  樨  ࠭樨
                  ᯮ INTENT  ⮣ ࠬ, ⮡ 
                  ஢  ஢   SELECT FOR UPDATE. 
                  ஢ ᯮ  㣨 .
                  ᫨    믮 ࠭権, DEFAULTE 砥
                  ALWAYS. ᫨    樨 믮 ࠭権, DEFAULTE
                  砥 INTENT.

祭        DEFAULTE
㬮砭

  DEFAULTE/ALWAYS/INTENT
祭

         㦭 ᯮ짮 ஢  ஢  
                   ஢ ⠡.

⢨ - ALWAYS - ஢  ஢ . INTENT -
 ࠬ    ஢  ஢ ⠡,  믮 UPDATE,INSERT
                   DELETE.

뫪          "ன SQL ஢  ਫ"  2, SERIALIZABLE.


        SEQUENCE_CACHE_ENTRIES
        
ᠭ    ⠭ ⢮ ⮢, । ᫥⥫쭮,
                SGA.      ᮤন 
            ᫥⥫쭮   㦭     
            ᫥⥫쭮 SEQUENCE, ࠡ饩 ࠫ쭮. SEQUENCE 
            祭  ࠧࠬ  ࠧ饭  ᫥⥫쭮⥩,
            ᯮ㥬 ६ 訬 ਫﬨ  .

            ᫨ 祭 SEQUENCE_CACHE  㤥 ந室 
            /뢮.

            , , ।騩 ᫥⥫쭮, ⫨砥 
            CACHE/NOCACHE 樨   CREATE SEQUENCE.  ࠬ
            ।   ᫥⥫쭮⥩   ஢.


                                   -- 108 --



            CREATE SEQUENCE ।   ஢ ᫥⥫쭮
            ⥩ 㤥 ᯮ짮  ୮ 饭  , ⮡
            । ᫥饥 ⢮.

            ᫥⥫쭮,  ᮧ ᯮ  CREATE
            SEQUENCE NOCACHE  ࠧ  , ।饬 ᫥⥫
            ,  ᫥⥫쭮,  뫨 ᠭ  ᫮ 
             ᯮ짮.     祭 ⮣ ࠬ
             祭  㬮砭, ⠭  ⠫樨  
            ᨨ 6.0.  ᠭ  ਫ .

祭        10 ᫥⥫쭮⥩.
㬮砭

   10  32000
祭

       ᫨ ਫ ᯮ  10 ᫥⥫쭮⥩.

⢨ -  ⠭ 祭, ࠢ ᠬ 讬 
                  ᫥⥫쭮⥩,   ᯮ짮
                  ६,  ᮪ ࠫ ࠡ.

뫪            " 祭 INIT.ORA ࠬ஢"  ਫ ,
                   5 㪮⢠    .


        SERIALIZABLE
        
ᠭ          ⭮  ࠬ஬ ROW_LOCKING  ࠬ 
                  ஢.  SERIALIZABLE = TRUE, 訢
                  ஢  ६ ⥭  ஢ ⠡, ।
                    믮 .   ।
                   ⥭   ࠭,    
                   ६    ⮦ 祭.

祭           ᨨ 6.0  樥 믮
㬮砭         ࠭権 祭  㬮砭 FALSE  ᨬ쭮
                  ࠫ.

  FALSE/TRUE
祭

        訢 ୮ ⥭,   祭
                   TRUE  ROW_LOCKING  INTENT.

⢨ -  ⠡    㯭
 ࠬ   ८ࠧ.

뫪         "ன SQL ஢  ਫ"  2, ROW_LOCKING.


        SORT_AREA_SIZE
        
ᠭ             ,  ।祭  ஢.
                   祭    祭  㬮砭, ஥
                  ⠭  ⠫樨   ᨨ 6.0. 
                  ᠭ  ਫ .

                                   -- 109 --



祭        । 樮 ⥬.
㬮砭

  । 樮 ⥬.
祭

       ᫨ 뫨 ᮧ 訥   筮 ⨢
                  , ⮡ 뤥  ஢ , ᫨ ॡ
                  ஢ 讥 ⢮ ᥩ (GROUP BY DISTINCT 
                  ORDER), ⮣ 㢥祭 ⮣ ࠬ  믮.

⢨ -  ⢮ ஬ ⢨,
 ࠬ     믮  ஢  㬥蠥 ⢮
                  I/O  ஢. ࠢ, 讥 祭 ⮣
                  ࠬ 䥪⨢ ਡ ஢.  ࠬ
                  । ࠧ ⥩ ஢   ᮢ, 
                     稭 १୮ 室 , ᫨
                  ⠭  ᫨誮 訬.

뫪             "ன ஢"   6,"砫쭠
                  ⠫ " ਫ .

SORT_SPACEMAP_SIZE

ᠭ             ஢筮 ࠭⢠  ⥪⭮
                  .  ࠬ    ࠢ
                  ࠭⢮  ஬ ஢.

祭        । 樮 ⥬.
㬮砭

          ࠬ.

뫪             "ன ஢"   6,
                  "砫쭠쭠 ⠫ "  ਫ .


        SQL_TRACE
        
ᠭ          蠥  ஢   ALTER SESSION.
                   ࠬ ।⠢ ଠ  ன, 
                    믮.

祭        FALSE
㬮砭

  FALSE/TRUE
祭

        믮 ஢.

⢨ -   ࠬ TRUE,  ந
ࠬ         ॣ㧪 樮 ⥬.  ࠧ,  ᫥
                   ࠬ ⠭  祭 TRUE ⮫쪮 
                  祭 ⨪.

뫪         TIMED_STATISTICS,MAX_DUMP_FILE_SIZE,USER_DUMP_DEST 
               "⠭ INIT.ORA ࠬ஢  SQL ஢"   7.

                                   -- 110 --




        TIMED_STATISTICS
        
ᠭ           । ६ 믮  㭪権 
                  ஢ ( ਬ CPU   ६)   ⮣ 
                  ࠭ SQL*DBA MONITOR.

祭        FALSE
㬮砭

  FALSE/TRUE
祭

           ६,   ⨪.

⢨ - TRUE 砥 ७ ६.   ६
 ࠬ    ⥬ ࠡ⠥  ॣ㧪.

뫪            SQL_TRACE, ࠧ "⠭ INIT.ORA ࠬ஢  SQL
                  ஢   7.


        USER_DUMP_DEST
        
ᠭ          । ४,  ன ᠭ 䠩 ஢
                   짮⥫᪨ ᮢ.  ࠬ 
                  ᯮ짮  ࠢ 뢮 SQL ஢筮
                  ⨪.

祭        । 樮 ⥬.
㬮砭

  । 樮 ⥬.
祭

            DUMP 䠩   ४.

⢨ - ࠢ DUMP 䠩  ᯥ樠 ४.
 ࠬ

뫪             짮⥫  ⠫樨, ࠧ "⠭
                  INIT.ORA ࠬ஢  SQL ஢"   7.

















                                   -- 111 --
