


                             







                              ORACLE RDBMS

                         Performance Tuning Guide

                               Version 6.0



                               
                              

⥬ ࠢ ५樮묨   ()  6.0 - ४-
᭮ ࠨ .     ᯮ   ॣ㫨-
 ਫ,  ᠬ    樮 ⥬.  믮 ⠪
ॣ㫨஢ ⭮  ன. ࠢ쭠 ன  ᯥ
襥 ᯮ         ᯥ᪨ ਫ  
䨣樨  ।.

 㪮⢮ ।⠢ ᫥ ଠ:

-  蠣 ⮤ ன  ᨨ 6.0 - ᭥
  ᯮ ᪨ ।, ᯮ㥬  ன 
- ४樨    襣  ᯮ  
- ⥫쭠 ଠ  ⥪    ,  6.0,
  ⭮  ன
- ᯨ᮪ ࠬ஢ INIT.ORA,ᯮ㥬  ன 

 㪮⢮  㪮 ஥ ଠ, ᮤ-
  㪮⢥      ᨨ 6.0


                        ।祭
                       
 㪮⢮  ᯮ짮  㠫쭮 ⢥⢥ 
ࠢ, 㦨  믮  .    筮 㯮
     .    ଠ ⮣
㪮⢠     ஥஢騪 ਪ .
᪮  㪮⢮ ।祭  ᭮  ன ,
ਪ   樮 ⥬, ⥫   ⥭ 
   ⥩. ⥫   ⠪   㪮⢮
   ()  

                짮 ⨬ 㪮⢮
               
 1.  ன.

      蠣, 室  ன.   ᮮ
  믮 蠣, ᠭ  ᫥ ,    ஥



                                    -- 1 --



樨, 㦤   蠣.    ᫥  
ᮮ⢥  蠣 ஥筮 . ⠩    
ᠭ  蠣.

 2. ன SQL - ᮢ  ਫ.

     SQL  ⠪,⮡  믮﫨 
䥪⨢   .  ⠪     룮 
஥஢   ⮣,⮡  ਪ ᯮ.  ⮩
 ᬮ७ ᫥騥 ⥬:
- ஥஢ 
- -⨬
- 
- ࠢ ஢  ஢ 
- PL/SQL
-  ᫥⥫쭮
- 
- ࠡ⪠ ᨢ(Arrey Processing)
  㤥 ࠧ  ਫ,ᮡ  

 3. ன । .

    ।      䥪⨢
  :
-  ⥪⮢
-  ᫮ 
-  ஢

          4. ன /뢮 (I/O)

     㧪  /뢮,  
᭨ ந⥫쭮.   ⮩   :
- 㬥 䫨
- । ࠭⢮   
-  ᪮  ࠭⢠

 5. 襭 䫨 権

  㦤 ஡, 騥  䫨   
    ஡ .  ⮩    㬥
䫨 :
- ⪠ ᥣ⮢
- 饫  ୠ ॣ樨 

 6. ⥫ ஥ ᮮࠦ.

  㦤 ᯥ樠 ᯥ :
- ஢
- ᢮ ᯨ᪨
- ஫ 窨

 ᫥  ஥ 蠣, ।⠢    1  5 ।
믮 ⢨, ᠭ  ⮩ .

        7. 믮 ᪨ 楤.




                                    -- 2 --



  㦤 ᫥騥 ।⢠,  ᯮ 
- SQL ஢
- EP(EXPLAIN PLAN)
 ।⢠ ᯮ  ஥     뫠  
 2  6  㪮⢠.
 㪮⢮ ᮤন ⠪ 뫮 ਠ,ᯮ㥬  ன.
뫠  ᫥騬 ਫ  㪮⢠.

ਫ .  - ⠫樮 樨.
 ਫ 㦤 ⠫  䨣஢   
襣 ᯮ짮.

ਫ .  뢠  (DBWR).
DBWR- -  ᭮   , ⢥⢥  ࠢ
                ஢
ଠ  ⮬ ਫ ਬ    6.0.27  ᫥騬
᪠  ᨨ 6.0.

ਫ .  INIT.ORA ࠬ஢.
 ਫ 㦤 INIT .ORA ࠬ,㠫  ᯮ.
ࠨ  ,  ᯮ짮 䨪  ࠬ஢.

                   ⥫ ᢥ
                   
..   ⮩ 樨 饭 ⮤ ன, 
ᮡ   㦤  窨 ७ ன . 
⠫쭮 ᠭ ॠ樨  ᮡ⥩ ।⠢  㪮⢥
     N 3601  6.0.  ਠ
⮣ 㪮⢠ ०,祬  .  , 㠫 
ன, :
 4  -  ⠡  ᥣ
 5  - 짮⥫᪮ ।⠢ 
 7  -  
 8  -   
 10 - ࠡ⪠ SQL ᮢ
 12 - ᫥⥫쭮  ࠫ
 16 - ࠢ ࠭⢮
 19 - 襭 ᯮ짮 ਫ
 20 - ன  
ਫ  - ⥫ SQL*DBA
 D - ࠬ INIT.ORA

 㪮⢮ 㦤 ன   , 筮 
  .
  ଠ樨  ⮩ 樨 ⠪   ᯮ짮 
ᮢ⭮ ᯮ ᪮ ⥬      
⢥ 㯮.
ଠ  ᮢ⭮ ਬ ᪮ ⥬ 室   21 "
⨬ ᪮ ⥬  " 㪮⢠  
 .
 ᮡ ன, 㦤  ⮬ 㪮⢥, ࠧࠡ⠭ 
SQL-ࠬ. ⠪  SQL-஢ ।⠢  ᠭ
SQL-몠,  N 778  6.0.
ன    ᮡ, ⨬  ਪ ।⢠
,  ।⠢  ᫥ ᠭ:




                                    -- 3 --



- ⥫  ࠧࠡ⮪ SQL,  N 3304  3.0
  (SQL*Forms Desiner's Reference)
- 짮⥫᪠   ⠫,  N 800  1.0
  (PL/SQL USER's Guide and Reference)
-  ணࠬ  ४樨  , N 5135
   1.3 (Programmer's Guide to the ORACLE Precompilers)
- 㪨 ணࠬ  맮 䥩 ,  N 5411
   6.0 (Programmer'Guide to the ORACLE CALL Interfaces)
-  짮⥫  ⠫  SQL*PLUS, N 5142.3.0
  (SQL*Plus User's Guide and Reference)
-  짮⥫  ⨫⠬  , N3602 6.0
  (ORACLE RDBMS Utilities User's Guide)

᪠ ன  襩 樮 ⥬   樨
짮⥫.

           ᫮, ᯮ㥬  ⮬ 㪮⢥

SQL-     -  ⠪  ⮬, . SELECT EMPNO,ENAME FROM EMP

SQL*PLUS   -  ⠪  ⮬,   䨪 SQL*PLUS,
                    .   SQL*Plus> SHOW ARRAYSIZE

SQL*DBA    -  ⠪  ⮬,   -
                    䨪 SQL*DBA, .
                    SQL*DBA> MONITOR LATCH

INIT.ORA ࠬ-  INIT.ORA ࠬ஢  -
                    묨 㪢, .
                    DB_BLOCK_BUFFERS_LOG_SMALL_ENTRY_MAX_SIZE

襭         - 饭   ᫥饬 ,
                    ਬ ४ᨢ 맮:
                    ॡ ६   

 䠩      -  묨 㪢, .
                    INIT.ORA

,ਫ  -  ⥩ 㪮⢠  ,.
 ࠧ           "믮 ⮢ ணࠬ"

                                
 1
        ன
       ⮢  ன
        ⮤ ன

        1.ன SQL-ࠬ஢  ਫ
        2.ன 뤥 
        3.ன /뢮
        4.襭 䫨⮢
       ⥫쭠 ன

 2
       ன SQL-஢  ਫ
       ன -SQL ஢  ਫ
       祭 襣 ਫ
          ࠧࠡ稪 ਫ  짮⥫

                                    -- 4 --



       ᯮ짮 ⮢ ணࠬ 
         ।⢠ ஢
          EXPLAIN PLAN

         ਫ,騥 SQL
         ঠ ࠧࠡ⪨ 
         ঠ  ᮡ⥩ ᯮ
         ঠ ᯥ᪨ ᮡ⥩ ன
       ࠡ⪠ 
         롮  ࠧࠡ⪨ 
       -⨬
         ਬ ⨬樨
       
          
            ᯮ
          ᯮ짮 
          ⠡ 
           뢠 -
         롮   樨
         ⥭ ᮢ
            ⥭஢   믮
           롮   ⥭஢ ᮢ
           冷稢  ⥭஢ 
         ਬ ஢ 

       ࠢ ஢  ஢ 
          ஢  ஢  蠥 ᯮ
         ᯮ짮 २⢠ ஢  ஢ 
         ணࠬ ࠢ ஢
       PL/SQL
          PL/SQL
         ८ࠧ⥫ PL/SQL (engine)
          PL/SQL 蠥 ᯮ
           ८ࠧ⥫ PL/SQL   
           ८ࠧ⥫ PL/SQL  ਪ ணࠬ 
         ᯮ짮 PL/SQL   ਫ

        ᫥⥫쭮
         믮 ᫥⥫ ८ࠧ
           ਬ ᫥⥫쭮
         饭  - ᫥⥫쭮 ᥫ
         ⢮ ⮢  SEQUENCE-
         ⢮ 祭   SEQUENCE- 

       
            ࠡ
         롮 ஢
         冷稢 ⠡  
         ࠢ  ஢
         ਬ  ஢

       ࠡ⪠ ᨢ(Arrey Processing-AP)
          AP 蠥 ࠡ
         롮 ࠧ஢ ᨢ
         ᯮ짮 AP   ਫ
         ᯮ짮 AP  맮 ஢  
         ᯮ짮 AP  맮 䥩ᮢ   (OCIs)


                                    -- 5 --



         ᯮ짮 AP ᮢ⭮ c SQL*Plus
         ᯮ짮 AP ᮢ⭮  SQL*LOader
         ᯮ짮 AP  㧪/㧪 ⨫

 3
       ன 뤥 
        뤥 
         
         ⥪ 
         ⥬ SGA
         -
       冷 ன 뤥 
         ன 襩 樮 ⥬
         ன ᨭ  ⥪ ⥩
         ன  ᫮ 
         ன  ஢
       ன 襩 樮 ⥬
         襭   ᢮
         ன ⥬ SGA
         뤥  짮⥫
       ࠭ 㦭 ᨭ
          ⠪ ᨭ?
         ⥪   
          㦭 ᨭ
         襭 ᨭ
           襭 ᨭ  ४
           
           襭 ᨭ  ORACLE Call
           䥩 (OCIs)
           ८ࠧ ᨭ⠪ SQL 
         뤥 ⥪ ⥩

       ன  ᫮ 
          ⠪ ᫮ ?
          ⠪  ᫮ ?
         業 ࠡ  ᫮ 
           業 ४ᨢ 맮
            V$ROWCACHE
            ⠡ V$ROWCACHE
            ⠡ V$ROWCACHE
         襭 ⪮  ᫮ 
         襭 ᯮ㥬 ⮢  ᫮ 
       ன  ஢
          ⠪  ஢?
         룮 ᯮ짮  ஢
         業 ࠡ  ஢
         襭 ⪮  ஢
            X$KCBRBH
           襭 ⠡ X$KCBRBH
            ⠡ X$KCBRBH
           㯯஢ ᥩ  ⠡ X$KCBRBH
          㦭 ஢
            X$KCBCBH
           ஢ ⠡ X$KCBCBH
            ⠡ X$KCBCBH
           㯯஢ ᥩ  ⠡ X$KCBCBH
       । 


                                    -- 6 --



 4
       ன /뢮
        ன /뢮
           (DBWR)
       襭 ᪮ 䫨⮢
          ⠪ ᪮ 䫨?
             ࠡ⪮ ᪮ ᮢ
             ࠡ⪮ -ᮢ ᪠
             ᠬ ᪠ 樮 ⥬
         । /뢮
            䠩    䠩 ୠ ॣ樨 
           "" ⠡ 
            ⠡  ᮢ
           ⮦  ᪮ /뢮

       । ࠭⢠   
         ଠ  
         ࠢ   
           筮-易 
            ᯨ᪨
              ᢮ ᯨ᪠
           ਬ ࠢ   
         롮 ࠭祭 ᢮ ࠭
           롮 PCTFREE
           롮 PCTUSED

       ᪫祭 ᪮ ࠢ ࠭⢮
           ⥭
         ७ ᥣ
         । ᪮ ७
         뤥 ⥭⮢

 5
       ன 㯨 権 (䫨⮢)
       襭 䫨⮢  ⪠ ᥣ⮢
          ⠪ ⪠ ᥣ?
         㦥 䫨 ⪠⭮ ᥣ
           業 䫨 ஢
           ᪮ 믮 ⠡ V$WAITSTA
            V$WAITSTAT
           襭 䫨⮢ ஢
          ⪠ ᥣ⮢
         롮 ࠧ஢  ⪠ ᥣ⮢
             ᮢ   ࠭権
            OLTP ਫ

       襭 䫨⮢  饫  ୠ ॣ権 
        ୠ ॣ権 
              ୠ (LGWR)
           ࠭⢮   ୠ ॣ樨 
         饫  ୠ ॣ樨
           ।⥫쭠 饫
           饫 ୠ ஢
         ஢ઠ 㭪樮஢ ୠ
         ॣ樨 
         襭 䫨 饫



                                    -- 7 --



           襭 䫨 ।⥫쭮 饫
           襭 䫨⮢  饫 ୠ ஢
       祭 ਮ⮢    ᮢ

 6
       ⥫ ஥ ᯥ
       ன ஢
          ஢
         ஢ ⥩
         뤥   ⥩ ஢
           ᯮ  ஢
           祭 ࠧ  ஢
           ᯮ짮 २  ⥩ ஢
            ਧ⥫쭮   ⥩ ஢
           ஢
            NOSORT
            ᯮ짮 NOSORT 
       襭 䫨⮢ ᢮ ᯨ᪮
          ᯨ᪨
         ᯮ 䫨⮢ ᢮ ᯨ᪮
           ஢ઠ ୮ 䫨
            V$WAITSTAT
           訢 V$WAITSTAT ⠡
           襭 ୮ 䫨
          ᢮ ᯨ᪮

       ன ஫ 祪
          ஫ 祪
            ६
            㦨 ୠ ॣ樨 
         ᯮ짮 ஫ 祪
           ࠡ뢠 ஫ 窨
          ஫ 窨   ᯮ
           ⫠ ०
            ०
          ⢠ ஫ 祪
            - ஫ 祪

 7
       믮 ⮢ ணࠬ
        SQL ஢
           ⠭ INIT.ORA ࠬ஢  SQL ஢
           襭 ஢  ᥠ ࠡ
           襭 ஢  
         SQL ஢ 䠩  ᨩ 䠩
         믮 TKPROF
           ਬ TKPROF
           뢮 TKPROF
           ᪨  ஢ SQ
           ᨢ 맮

       EP 
          室 ⠡ EP
         ⠪  EP
          ⠡ EP
         ਬ 뢮 EP
           ଠ ⠡ 뢮 EP
            ଠ 뢮 EP

                                    -- 8 --



ਫ 
         믮 砫쭮 ⠫樨 
        祭 INIT.ORA ࠬ஢
       뤥 ⪠ ᥣ⮢
         롮 ࠧ஢ ⪠ ᥣ⮢
             ᮢ  
           ࠭権
            OLTP ਫ
       । /뢮

ਫ 
               (DBWR)
             ஢
             ᪨  ஢
           ⥭    ஢
              䠩  
           ਪ DBWR  
           ணࠬ ࠢ DBWR
           ன DBWR

ਫ C
            INIT.ORA ࠬ஢
           CONTEXT_AREA
           CONTEXT_INCR
           CPU_COUNT
           -ࠬ ᫮ 
           DB_BLOCK_BUFFERS
           DB_BLOCK_LRU_EXTENDED_STATISTICS
           DB_BLOCK_LRU_STATISTICS
           DB_BLOCK_MAX_SCAN_CNT
           DB_BLOCK_SIZE
           DB_BLOCK_WRITE_BATCH
           DB_FILE_MULTIBLOCK_READ_COUNT
           FREE_LIST_INST
           FREE_LIST_PROC
           LOG_ALLOCATION
           LOG_BUFFER
           LOG_CHECKPOINT_INTERVAL
           LOG_ENTRY_PREBUILD_THRESHOLD
           LOG_SIMULTANEOUS_COPIES
           LOG_SMALL_ENTRY_MAX_SIZE
           VAX_DUMP_FILE_SIZE
           OPEN_CURSORS
           PROCESSES
           ROLLBACK_SEGMENTS
           ROW_LOCKING
           SEQUENCE_CACHE_ENTRIES
           SERIALIZABLE
           SORT_AREA_SIZE
           SORT_SPACEMAR_SIZE
           SQL_TRACE
           TIMED_STATISTICS
           USER_DUMP_DEST


      1
     
                      .

                                    -- 9 --



                     
  -    ⫨筮 ࠨ ணࠬ த. 
    訥 ॣ㫨஢,    믮
 . ࠨ   ,  ᬮ ᯮᮡ   
㦤.

 ⮩   ᭨ 楫 ஥筮 . ᫥ ᭥ 楫 
ᬮ    ஥ ⢨  ⮬ 㪮⢥, 
  .

  ⠪ ।⠢  ஥筮 ⮤, ४
.  ⮤ ᮤন  蠣  ன  
6.0.   㦤 ,     蠣 ⮤.

᫨   ࠨ   6.0 ࠭,   㦭 ᫥ 蠣
⮤  浪.  ⮤   ᢮ ன  த
।.  ⠪    믮 ⮢ ணࠬ, ।
.

᫨  㦥   ன  ᨨ 6.0  ⠥, 
  襩  㦭 㤥 ,     
ᯥ ଠ,  ன  㦤.

  ᬠਢ ⠪     ,
騥   ன .    ᢮ 
ன.

          .
        
 ⮣   筥 ன 襩    ᫥ । 楫
ன.  楫  ⥪  稭 ன.   ன 
 ᫥騬:
-  믮 ᯥ樠 SQL஢
-  믮 ᯥ樠 ਫ  
-  ६ 믮  짮⥫᪨   ਫ
   

    楫 ᬠਢ  ஥筮 ⢨, ᠭ 
⮬ 㪮⢥.   ਬ ᯥ樠 ⢨,  ᫥
業    ᢥ  楫.
 ⠪  ,   楫   ⨢稢묨. ਬ, ⮡
 襣 믮 ᯥ樠 SQLࠬ஢, , ਤ
⢮ 믮 㣨 SQLࠬ஢, 믮塞 ६ 襩
.

          .
        
ன ⮨   蠣.  蠣 ᠭ   
᫥ :

-  1:    2.ன SQL஢  ਫ.
-  2:    3.ன । .
-  3:    4.ன /뢮.
-  4:    5.ன 䫨⮢.

 蠣 ࠧࠡ⠭    ᨬ ᪮쪮 .
ਬ, २⢠ ன /뢮  蠣 3  易 
२⢠ 襭 䫨⮢  蠣 4.

                                    -- 10 --



 襭, ஥  ਭ   蠣,  
᫥騥 蠣. ਬ,  蠣 1   ९   
SQL ஢.  SQL    祭,- ࠦ饥 
ᨭ⠪   樨 ᫥饣 蠣.

  /뢮,  ࠨ  蠣 3   ࠧ஢
 ஢,  ࠨ  蠣 2.   ஥筮 ,
ࢥ  襥 ﭨ  㣨 蠣,   ஥筮
   ।. ⮬  ᫥ 믮 蠣  浪.

         1. ன SQL ஢  ਫ.
        ---------------------------------------------
। ன ᠬ   ᫥  㢥७, ਫ
  २⢠  SQL몠  ᮡ ࠧࠡ⪨ , ⮡
᪮ 믮 ਫ.   楫 1蠣.

 蠣 ਢ ਪ ।⢠,   믮
SQL஢  ਫ.  ᫥ ᯮ짮  ᮡ,
᭮  ॡ  ਫ:
- ஥஢ 
- -⨬
- 
- ࠢ ஢  ஢ 
- PL/SQL
-  ᫥⥫쭮
- 
- ARREY PROCESSING(ࠡ⪠ ᨢ)

         2. ன । .
        --------------------------------------
ࠢ쭮 ।        讥
⢨  䥪⨢ 믮 ணࠬ.  蠣   ।
᪮쪮  뤥  ᫥ :
-  ⥪⮢
-  ᫮ 
-  ஢

ࠢ쭮 । ᮢ   ਭ ᫥騥 룮:
-  -믮
- 㬥  SQL ஢
- 㬥 ᢮  

         3. ன /뢮.
        ------------------------------
᪮ /뢮 筮 ᭨ ந⥫쭮  ணࠬ
ਫ.    ஥஢ ⠪ ࠧ,  
㭪樮஢ ॡ, ⮡  뫮 থ  ஭ /뢮.
ன /뢮 砥 ᫥騥 楤:

- । /뢮,⮡  ᪮ 䫨⮢
- ।      襣 㯠
- ᮧ ⥭⮢ 筮    

         4. ன 䫨⮢.
        ----------------------------
६ 믮   짮⥫ﬨ   䫨
 । .   ⠢  ,  ।⢠ ⠭


                                    -- 11 --



㯭묨.  ⮬ 蠣  㬥 䫨 :
-⪠ ᥣ⮢
-饫  ୠ ॣ樨 

᫥ 믮 ⢥⮣ 蠣 ᭮ 業 ࠡ    
  室 쭥 ன.  ᫨  襭,
祭  ᫥ 蠣 㪠뢠   襭  ࠭
蠣,     ஥ 楤.

           .
        
  ஥筮 , ਢ   㪮b⢥,
 ,祬 㣨. 騩 ᯨ᮪ 砥   
ன.  ᯨ᮪, 类 ᮬ, 뢠騬 ᮡ࠭
ᥣ ਠ,ᮤঠ饣   㪮⢥. ᮡ  
뢮,祭   ᯨ᮪.  ᫨  ࠭   ன
 ᨨ 6.0,  ᯨ᮪   ।⠢  ⭮⥫쭮
 ⥩ ஥筮 . ᫨    ன 
ᨨ 6.0, ᯨ᮪ ࠢ   ⮩  㪮⢠, 㦤
 .

1. 祭  ⠡. ࠢ쭮 ᯮ짮 ᮢ  
   冷  믮 SQL ஢.   ଠ樨 
   ᠬ    2 "ன SQL ஢  ਫ".

2. ᯮ짮 २ ᮡ⥩  ᨨ 6.0.  騩
     ࠢ ஢  ஢  ᠭ   2
   "ன SQL ஢  ਫ".

3.   ६ 믮  SQL ஢ ।⢮ ࠭
 ᫮    ࠭  ᯮ㥬 ஢  ᫥ 
믮.  ⥫쭮 ଠ樨  ⮬    
3 "ன । ".

4. ᫨ ந室 ९  /뢮 , 㢥 ࠧ
 ஢  ⥬  SGA.  祭 ଠ樨  -
       3 "ன ।
".

5.  ⥬  㠫쭮 , 㡥,  ⥬ 
   SGA ᮮ⢥ ॠ쭮 , ᯥ稢    -.
    祭 ⥫쭮 ଠ樨  ।   
    3 "ன । ".

6. । /뢮 ⠪ ࠧ,⮡    ᪮ ன,
                ᮤঠ  䠩    䠩 ୠ ॣ樨 
                        ࠭  㧪.  ⥫쭮 ଠ樨 
                        "ன /뢮".

7.  䫨  饫, ᫨ 業 ᮢ,  뢠
     ⥫쭮 ॢ蠥 10%  15%.  ⥫쭮 ଠ樨
      5 "ன 䫨⮢".

8. ,  -   ਮ  樮
   ⥬.  ஡  , ᫨  樮 ⥬
   㦠 ਮ ,  ६    
   ᪫⥫   .


                                    -- 12 --



᮪ਮ  ⮣   ᪮筮,  ਮ-
  㤥 砭 ⭮.  ⥫쭮 ଠ樨  
 5 "ன 䫨⮢".

           2.                    SQL-  .
                                       

  ।⠢  蠣 ஥筮 : ன SQL--
஢  ਫ.  ⮩    ᯮ짮 ᮡ 
   ਫ  襣 믮  ਫ. 
ᮡ :
- ஥஢ 
- -⨬
- 
- ࠢ ஢  ஢ 
- PL/SQL
-  ᫥⥫쭮
- 
- ࠡ⪠ ᨢ (Array Processing)

   ਠ  ன   19 "襭 믮
ਫ"  , 㪮⢠   . 
 ⨬ ਠ ०, 祬  .

         SQL-  .
        

ன 襣 ਫ -   襣 믮 襩 
.  ᫥ ந  ਫ  ன ᠬ  
᫥騬 稭:

- ஥஢ ਫ ।⠢  ᠬ אַ ஫  SQL
  ࠬ  ࠡ⪮   . ⥫ ஥ ਫ 
   ⨬஢ ࠡ .

- ,᫨     ࠡ⮩  ,  ⥫쭮 
  ࠡ 襣 ਫ ன, ᭮   SQL.

- ᫨  ਫ  ஥,  㤥  믮  
   ஥ .

          .
        

। ॠ樥 ᮡ⥩ ,।⠢  ⮩ ,  ᫥
ࠢ २⢠  ᮡ  㦤 襣 ਫ. ⮡
ᤥ  ࠢ,      訬 ਫ, SQL
ࠬ   묨.

 ࠧࠡ稪 ਫ  짮⥫.

 ,⢥⢥  ࠧࠡ ਫ. ᭨ 
 ਫ:
-  SQL  ᯮ ਫ
-   ࠡ뢠 ਫ
-  樨 ਫ ந  ⨬ 묨



                                    -- 13 --



㤨 믮 ਫ  짮⥫ﬨ.   뤥 
 ਫ,   㦭  믮.  ।
   ⤥ SQL ,᫨  .  
ᮭ,⢥⢥  ࠡ 襩 樮 ⥬  ᯥ䨪
ன 樮 ⥬.

            .
        
 ।⠢ ᪮쪮 ⮢ ணࠬ  ஢ન ࠡ.   
 ᮡ   檥 ਫ:
- ।⢮ ஢ SQL
- EP(EXPLAIN PLAN)

        ।⢮ ஢.
        ---------------------
  ਫ  ࠧ襭 SQL ஢. ।⢮ SQL
஢ ࠡ뢠 ⨪   SQL , 믮
.  ⨪ ࠦ:

- ६, ஥  SQL  , 믮  ।
- ६, ஥ 室, ⮡ 믮  
-   ,  樨   SQL ஬
- ⢮ ப, ஥ 믮  SQL 

 ⮩ ⨪ ।  SQL  ॡ 襣
६.  襥  ன  ஢.

        EP .
        ------------
ᯮ EP   ᪮ 裡  ।⢮ SQLப. EP
 뢠  믮    襬 ਫ. EP
 뢠 ⢨,ந , ⮡ 믮 SQL
.   믮,    䥪⨢
  襬 ਫ. ᬮ ⠪ ன  ஢.

 ணࠬ     ᮡ SQL ,  
  ਫ.  ⥫쭮 ଠ樨  ᯮ짮 
ணࠬ    뢮 頩  7 " ⮢ ணࠬ".

         SQL-  .
        

믮 ன ਫ  襩 ⥯   ᠬ
ਫ.  ᮡ    ⥭樠쭮 
ࠡ  ਫ.   㦤    ᮡ⥩.
 ᮡ 蠥 ࠡ -᢮. 롥  ᮡ
ᯮ짮, ᭮뢠   २⢠  ॡ  ਫ.

        㦤 ஥஢ .
        ---------------------------------
஥஢    蠣   ண ਫ 
. ।  ⠡,  ᮤঠ ਫ  - 
  ஥஢ ᠬ ਫ.襥 ஥஢ 
ᯥ稢:

-    䨪樨 ਫ
- ᭮  ⨬쭮 믮 ਫ


                                    -- 14 --



஥஢   ᭮뢠    ॡ.  
᫥騩 ࠧ  ᯥ樠 ᮢ⮢  ஥஢ .

㦤 ᭮ ᮡ⥩ ᯮ.
--------------------------------------------
 ᮡ    ᯮ   ਫ.
  ᮡ⥩    ⨬ SQL  
ਫ. ,  訬 ࠧ ᯮ  ᮡ:
- -⨬
- 
- ࠢ ஢  ஢ 

        㦤 ᯥ᪨ ᮡ⥩ ᯮ.
        -------------------------------------------------
㣨 ᮡ   ᯮ ᯥ᪨ 権 
.  ᮡ   ᯮ 襣 ਫ 
ᨬ  権,   믮:

- PL/SQL
-  ᫥⥫쭮
- 
- ࠡ⪠ ᨢ(Array Processing)

         .
        
 蠣  ஥஢ ਫ    ।
 ⠡, ᮤঠ .      
ࠧ묨 ᯮᮡ, 롮  ५樮 ஥஢  
२,  ᫥     ࠡ  . 
ࠧ ᪠ :
-  ⠪ 襥 ஥஢ 
-  २⢠  ⪨ ࠧ ஥⮢
-  । 訩 ४   

 ࠧࠡ⪠ ਫ     ।  룮 
 २ ५樮 ⥮ਨ  ଠ樨.      
楯権  ᬠਢ  ⮬ 㪮⢥,    
ᠭ    ᯥ樠쭮   ५樮  .

롮 ஥ .
---------------------
樮 ४,     , 
 室騬  設⢠ ਫ. 樮 ஥ ।⠢
      襬 믮 設⢠ SQL
஢.

, ᫨ 䥪⨢ ᯮ  襩  楫,  
㦨,   ५樮 ४ ᯥ稢 襥 믮 ਫ-
.  ⮬ 砥  ᫥ ᬮ ࠭樨  襬 ਫ.
    ஥,  ⨬ ࠭樨 襣
ਫ,믮騥  .   ,,  
஥, ⨬    ६ ࠭樨. ᫨ 
㢨,  ᯮ 襣 ਫ ࠭祭 ன ⠡ ,
  ᬮ   .    
  ⥫ 䨪 襣 ਫ,  ᫥ ,
  த㬠 ஥     襩 ᭮ 
, ਫ  ⮩稢  訡 믮  .


                                    -- 15 --




         - .
        
믮  SQL      ஢७ ᭠砫
-⨬஬. -⨬ 롨ࠥ  믮  SQL
.  믮 -  ᫥⥫쭮 蠣  権 
 ,   믮  믮 SQL .
-⨬ 롨ࠥ  ᯮ  ᭮ ᫥ ਥ:
- ᨭ⠪ SQL 
- ᪮ ᫮  ᪮ ﭨ WHERE-ࠧ SQL 
-   । ꥪ⮢  ,  SQL 
-  ᮢ  

ࠢ,   -⨬ 롨ࠥ  蠣,ᠭ  
19"襭 믮 ਫ  " 㪮⢠  
.   ⨬ ࠢ   㪮  ᠭ SQL
஢,  믮     .

᪮ SQL   몮,   ⮦ ਫ  믮
ࠧ묨 SQL ࠬ,   SQL   ਢ   
⮬  १,   믮  ࠧ ॥,祬 㣮. 
⮬ 砥  ᫥ EP    SQL ஢, ⮡ ࠢ
믮   ᭥,   ஢  䥪⨢.

        ਬ ⨬樨.
        -------------------
 ਬ 뢠  ᨭ⠪ SQL ,   롨ࠥ
   ᯮ.  ਬ 뢠  믮 
SQL ஢,  믮     㭪.  SQL 
ॡ EMP  DEPT ⠡.     ⤥  DEPT ⠡,
   ᯮ⥫  EMP ⠡.   ᬠਢ EMP
⠡  ᮮ⢥⢨  ᮬ. ᬮਬ  DEPTNO_INDEX,
ᮧ  ਡ DEPTNO ⠡ EMP.

      믮, 祭 EP ஬:

SELECT DNAME, DEPTNO
    FROM DEPT
    WHERE DEPTNO NOT IN
           (SELECT DEPTNO
                  FROM EMP)

OPERATION             OPTIONS             OBJECT_NAME

FILTER
TABLE ACCESS          FULL                DEPT
TABLE ACCESS          FULL                EMP

 ப  믮 뢠  ᪠஢ EMP ⠡.
 ᪠஢ ॡ 讣 ६. EMP ⠡ ᪠ 
ᯮ짮   DEPTNO ਡ.

    ᯮ, ⮬    EMP ⠡  
।  WHERE.  設⢥ 砥 SQL    ᯮ짮
, ᫨  ਡ    ।  WHERE.
 ᫥騩 SQl  롨ࠥ   ᠬ  ᯮ :



                                    -- 16 --



SELECT DNAME, DEPTNO
    FROM DEPT
    WHERE NOT EXISTS
           (SELECT DEPTNO
                   FROM EMP
                   WHERE DEPT.DEPTNO = EMP.DEPTNO)

OPERATION             OPTIONS             OBLECT_NAME

FILTER
TABLE ACCESS          FUll                DEPT
INDEX                 RANGE SCAN          DEPTNO_INDEX

।  WHERE  ᮤন  DEPTNO EMP ⠡, ⮬
 ᯮ. ᯮ짮  ࠦ  쥩 ப EP
. ஢   DEPTNO_INDEX ॡ ࠧ 
६, 祬  ᪠஢ EMP ⠡  ।饬 . 
⮣  ࢮ ਬ ॡ  ᪠஢ EMP ⠡  
祭 DEPTNO  DEPT⠡.  ⮬ ன SQL  ॥,祬
.

        .
        
 -    , 訥 䥪⨢ 믮
ᮢ.  ࠧ :
-   
-  ࠧ   믮
-  ᫥ ᯮ짮 
-  롨 

 ᯮ   樨 ਡ⮢ ⠡.  ⠢ 
ᮮ⢥⢨  祭 ਡ   ⠡, ᮤঠ饩 
祭. ਡ,  ᮤন , 뢠 祢.   
ᮧ ஬ CREATE INDEX.  ଠ  ⮬  ᮤন 
㪮⢥   SQL.

         .
        -------------------
    ᯮ짮  ᨫ 㭨쭮 祭 
.   뢠 㭨묨 UNIQUE INDEXES ᫨ ᮧ
㭨       ᮤঠ  祭 
⮬ .

         ࠧ   ᯮ.
        ------------------------------------------
 ᯮ  ⨬樨 ᮢ. ,  ᮤন -
஢   ࠧ WHERE  ᯮ짮 .   
ᯮ ,     祭, ஥ ਭ
ࠦ, ᯥ஢ ࠧ WHERE. ᫨  롨ࠥ ⮫쪮
᭮ ,    祭 ⮣  ।⢥  
, 祬  ⠡.    祭  ⠪ । 
    ⠡, 饩  祭. ᫨  롨ࠥ 
   祭  ,  室   ⠡, ᭮뢠
  .     ᠬ  ᯮᮡ   
 .




                                    -- 17 --



 ᯮ짮 .
---------------------------
  ᯮ ᮢ,  롨 让
業 ᥩ  ⠡.  饬 砥  ᫥ ᯮ짮
  ᮢ,  ᮤঠ  10-15%  饣
⢠ ᥩ ⠡.

         ᪠஢ ⠡.
        ----------------------------
᫨   ᯮ    믮  ᪠஢
⠡,⮡ 믮 .  ᪠஢ ⠡ ந ⥭
 ᥩ ⠡ ᫥⥫쭮.  ஢  ,⮡
। 㤮⢮    ਩, ᮤঠ騩  WHERE ࠧ.

宦 筮    ஢  ࠧ 
஥,祬 宦    ᪠஢.
 , 롨ࠥ ,祬 10-15% ᥩ  饣 ⢠
  믮 ᪮॥   ᪠஢, 祬  樨.

 믮  ᪠஢  ⠥    ⠡. 
    ⠥  ,ᥭ   .  ⠥
   ࠧ. 믮 ஢ , ⠥  
浪, ஬     ᨬ  ⮣   
 ᮤঠ. ᫨  ᮤন ,祬  롨ࠥ ,
     ࠧ.     ᪠஢
 ॡ  権 /뢮,祬 ஢ .

        筮 뢠.
        ------------------------
  ᯮ짮 筮 ⥭,⮡  ᪮쪮 ᫥-
⥫ ᪮   ࠧ. ᪮  ᪠஢ ⠡ ⢫-
 ।⢮ ᫥⥫쭮 ⥭ ,   믮 
᪠஢ ᯮ 筮 ⥭. ,᪮, ஢
 筮 , ᯮ 砩   ,   
믮 ஢    ⥭.   
  ⭮. ⢮ ,뢠  筮 ⥭,
। INIT.ORA ࠬ஬ DB_FILE_MULTIBLOCK_READ _COUNT. ᫨ 
ਫ 믮   ᪠஢  ⠡,  
 믮 㢥祭 稭 ⮣ ࠬ. 㢥稢
⢮ ,⠥   筮 ⥭   ᭨
⢮  ⥭, 室  ⮣,⮡  
⠡. 筮 ⥭   ᮡ  ᨨ
6.0. 筮 ⥭  ⠡ ᪠஢ 
⢫ ॥,祬  ।  .  믮
ਫ  ᨨ 6.0  ᫥ ᬮ ⤥ ஢
  襬 ਫ, ⮡ ।     ⠡
᪠஢   ॥.

        롮   .
        ------------------------
 ᫥ ᮢ⠬  롮   :

-  ,  ᯮ  WHERE ࠧ. , 
  뫠  ஢   WHERE ࠧ  ᯮ짮 .

-  , ᨬ   祭 롨 .
  , 롨 ஢ 祭 SQL 㯯 㭪権 MAX
   MIN  ᯮ짮 .

                                    -- 18 --



-  ,  ᯮ  ꥤ ⠡  SQL
  .  ⥫쭮 ଠ樨 ꥤ 뫠  ࠧ
  ,   ⮩ .

-    ᮪ ᥫ⨢. ⨢ ᮪, ᫨ 
  ᥩ   祭  祢 ,  㭨 
    ᥫ⨢묨  䥪⨢묨  믮
  ⨬஢ ᮢ.

-     ᪮쪨 ࠪ묨 祭ﬨ. 
     ᥫ⨢. ਬ, ,ᮤঠ饬 ࠢ-
   ⢮ ""  "",   ᯮ.
-     쪨 ⠡. ᫨ ⠡ ᮤন  
   ,  ᪠஢ ⠡  롨 
  ॥, 祬 ஢ .   ।   
   ⠡ ᯮ 業 ࠬ ROWIDs ⠡, ਬ, 
   頥 ⢮ , ᯮ㥬  ⠡ ACOUNTS:

  SELECT COUNT(DISTINCT(SUBSTR(ROWID,1,8) SUBSTR(ROWID,15,4)))
  BLOCK FROM ACOUNTS

-   ,  . UPDATE, 
   ஢   INSERT  DELETE,  
  ஢ ⠡,ॡ  ६, 祬  ⢨
  樨.    ஢     
    ⠡.

롨 ᮧ  ,। ⮨  먣  ᪮ 믮
 ஢ ந  믮 ஢ INSERT  DELETE.  ,
,  ᯥਬ஢  ஢ ६ 믮 
SQL ஢  ᠬ   .    ६  
।⢠ SQL ஢.   ⥫쭮 ଠ樨  SQL ஢
   7 "믮 ⮢ ணࠬ".

        ⥭஢ .

   ᮧ ,祬   .  
뢠 ⥭஢묨 ᠬ().

         ⥭஢   믮.
⥭஢   믮, ।⠢:
- ᮪ ᥫ⨢
- ࠭ ⥫ 

   ᯥ祭 ᥫ⨢.      ᥫ⨢-
  ꥤ   ⥭樨  ᯥ稢 ᮪
ᥫ⨢.   ⠪ ᯮ짮  ࠭ ⥫
  , 롨  .  ᫨  롨ࠥ
,஥    ,   ROWID   
⥬  ᮮ⢥饥   ⠡. ᫨  롨ࠥ 
祭  ,    㤮⢮७ ᪫⥫쭮 ᪮ 
    ᥩ ⠡.

        롮   ⥭஢ ᮢ.
,  ᯮ  ⥭樨 뢠 砬 ⥭-
樨. ᯮ   SQL  । ﬨ, ᮤঠ騬 
WHERE-ࠧ SQL-  浪    CREATE INDEX. 
 ᯮ짮 ,᫨  ᮤন 砫    WHERE-ࠧ.

                                    -- 19 --



砫쭮    ࢮ   , ᯥ஢ 
 CREATE INDEX.  騩  ᮧ    JOB,SAL 
DEPTNO  ⠡ EMP:

 CREATE INDEX JOB_SAL_DEPTNO ON EMP(JOB, SAL, DEPTNO)

    ᯮ짮  ᫥ :
   SELECT
      FROM EMP
      WHERE JOB = 'CLERK'
      AND SAL = 800
      AND DEPTNO = 20
   SELECT
      FROM EMP
      WHERE SAL = 1250
      AND JOB = 'SALESMAN'
   SELECT JOB, SAL
      FROM EMP
      WHERE JOB = 'MANAGER'

   ᯮ 砫   JOB_SAL_DEPTNO.  ।
 WHERE-ࠧ  易 ᮤঠ   ⮬ 浪,  ஬ 
ᯮ   CREATE INDEX. ,  ⨩   
楫 㤮⢮७  㯮. ᪮  ,ᯮ㥬 
 (  SELECT ᯨ᪠,⠪   WHERE ࠧ   ,
  ॡ 㯠  EMP-⠡.

騩 , ,   ᯮ짮 ,⮡   
EMP-⠡ ⮬,    ᮤন ࢮ    ᢮
WHERE-ࠧ:

SELECT JOB
   FROM EMP
   WHERE SAL = 5000


        冷稢  ⥭஢ .

 ⨬ ᮢ⠬  㯮冷稢 祢 ,ᮧ :
- ᫨ ⮫쪮    ᯮ    WHERE-ࠧ,
       CREATE INDEX.

- ᫨ ,祬   ᯮ   WHERE-ࠧ, 
   ᥫ⨢     CREATE INDEX.

        ਬ ஢ .
        --------------------------------
 ਬ     믮 ᮢ.  
SQL-, 믮  䥪⨢  ᯮ짮 ᮢ.
 ஢筮 ⨪   ᯮ, ।⠢塞 EP
஬,  룮 ᯮ, ᢮⢥ . 
⥫쭮 ଠ樨  SQL ஢    7 "믮
⮢ ணࠬ".   室  SQL ஢  ᮡ  
⨪   :

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

                                    -- 20 --



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

phys   稭  ⮬   ⢮  , 뢠
               䠩    ᪥   蠣.

SQL   ⮬ ਬ 訢 ⠡ ᪨ ⮢.
 롨ࠥ   筮 :

SELECT BAKANCE
   FROM ACOUNTS
   WHERE ACC_NUM = 49999

:
ACOUNTS    -     ⠡ ᪨ ⮢
ACC_NUM    -    ,ᮤঠ饥     ᪮ 
BALANCE    -    ,ᮤঠ饥    

᫨     ACC_NUM SQL ஢ 뤠 ᫥ ⨪ 
 믮  ६ 믮 :

                count    cpu    elap    phys    cr    cur    rows

Parse:              1     43      54       3     0      0
Execute:            1      1       2       1     0      2       0
Fetch:              1   6176    7934    5883  5883      0       1

Execution plan:

TABLE ACCESS(FULL) OF 'ACCOUNTS'

 ᯮ 뢠, SQL    
᪠஢ ⠡ ACCOUNTS. ᯮ    襭 
樨   ACC_NUM.    ᮧ SQL ஬:

CREATE UNIQUE INDEX ACC_INDEX ON ACCOUNTS(ACC_NUM)

᫥ ᮧ    ᠬ    믮 
॥. ⨪   ᯮ ਢ  ஢ :

                count    cpu    elap    phys    cr    cur     rows

Parse:              1     44      66       3    17      0
Execute:            1      1       0       0     0      0        0
Fetch:              1      2      22       4     4      0        1

Execution plan:

TABLE ACCESS(BY ROWID) OF 'ACCOUNTS'
   INDEX (UNIQUE SCAN) OF 'ACC_INDEX' (UNIQUE)

  ᯮ 뢠,  砥   ACCOUNTS ⠡
  ROWID ᫥ ᪠஢  ACC_INDEX.   ᯮ
॥, 祬  ᪠஢ ⠡.
⨪, ஢ SQL-஢, ࠦ 룮 SQL ᯮ,
᢮⢥ . ࠢ 祭  phys  ᮢ,  
,     ᪠஢ ॡ  襥 /뢮,
祬 ஢ .  蠣 롮ન  ᪠஢ ⠡

                                    -- 21 --



뢠 5883 ,  ⮫쪮  .   ᪠஢
⠡ ⠥   ⠡  ᪠.  ஢  ⠥
,  ⥬ ⮫쪮 ᮮ⢥   ⠡.   ᮮ⢥-
  ॡ   ,祬 ,⠥  
᪠஢. ᪮ ஢  ॡ   /
뢮,  ந室  ॥  ᪠஢ ⠡.   
祭  cpu  elap   㢨,  蠣 롮ન ஢
 ॡ   ६,祬  蠣 롮ન  
᪠஢ ⠡.

            .
        
   6.0   ᯮ ࠭権 ᮤন ࠢ
஢  ஢ .  ࠧ 뢠:
-  ࠢ ஢  ஢  蠥 ᯮ
-  ᯮ짮 २⢠ ஢  ஢ 
-  ணࠬ ࠢ ஢

         ஢  ஢  蠥 ᯮ.
        ----------------------------------------------------
   6.0   믮 ࠭権   
஢  , 祬  ஢ ⠡. ஢  ஢  ࠧ-
蠥  짮⥫ ६   ࠧ    ⮩
 ⠡  ஢ ᮤন ⠡. 騩 먣  믮-
  ⢥.   먣  訬 襭
 ᨨ 6.0     ᨥ 5.1,  ⠪  訬 २-
   ࠢ  㣨 . ਫ,  먣뢠 
ᥣ  ,  ᯮ OLTP(online transaction processing).

OLTP ਫ ࠪਧ 訬 ⢮ 짮⥫, ६
 ࠧ    ⮩  ⠡.   ⠪ 
 짮⥫ ६        .
騩   饣 騩   饣. ,  
䫨, ⢮ ᮢ        
६.

        룮  ஢  ஢ .
        --------------------------------------
஢  ஢   ॡ - ᯥ樠 ࠧࠡ⮪ 
 ਪ ணࠬ. ,   ॡ ஢ 
஢  ஢  ஢ ⠡.  筠 ஢ ᭨
룮   믮 ࠭権.
஢  ஢ ⠡   믮 ⠪ SQL- ஢:

LOCK TABLE EMP IN EXCLUSIVE MODE
LOCK TABLE DEPT IN SHARE MODE

᫨   ਫ   ᨨ 6.0 ।⢮ 
믮 ࠭権, ᫥  ⠪   襣 ਫ.
᫨  ਫ ᭮  ஢  ஢ ⠡, 
⠪, ⮡  룮  ஢  ஢ . , ᫨ 
஥  ਫ,    襣 믮,ᯮ
஢  ஢   ஢  ஢ ⠡.  ஢
 㬮砭 ஫ ।⢮ INIT.ORA ࠬ஢:

SERIALIZABLE  ROW_LOCKING.    ᨨ 6.0  
믮 ࠭権 祭  㬮砭  ࠬ
SERIALIZABLE  "",  祭  㬮砭 ࠬ

                                    -- 22 --



ROW_LOCKING  "ᥣ".
᫨      믮 ࠭権, 㡥, 
 ࠬ  祭  㬮砭.

ࠢ ࠡ⮩ ஢.
------------------------------
   ⥫쭮 ஢ ।⢮ SQL*DBA MONITOR LOCK.
  뤠 SQL*DBA-:

SQL*DBA MONITOR LOCK

 ᯫ   㢨 ⤥ 䫨.    
  ᯮ 㧪  ஢, ᮡ, ᫨   
 믮 ࠭権. ⥫쭮 ଠ樨  SQL*DBA MONITOR
LOCK ⮡ࠦ 頩  ਫ   㪮⢥  
.

        PL/SQL.
        
PL/SQL -  楤  ,塞  SQL.ᯮ짮 PL/SQL 
 ਫ   ᯮ.  ࠧ  :
-  ᮤন PL/SQL-
-  PL/SQL 믮
-  ᯮ짮 PL/SQL   ਫ

  ଠ樨  PL/SQL   樨 짮⥫ 
ࠢ筮 㪮  PL/SQL.

         PL/SQL.
        -------------
PL/SQL   ᬥ訢 SQL-  楤묨 ﬨ.
騩 PL/SQL  뢠 . PL/SQL   ᮤঠ  
 ஢:

-  몠 㫨஢ 묨 (DML) (INSERT,UPDATE,DELETE  SELECT)
-  ࠢ ஬ (DECLARE,CURSOR,OPEN,FETCH  CLOSE)
-  믮 ࠭権 (COMMIT,ROLLBACK  SAVEPOINT)

PL/SQL   ⠪ ᮤঠ    楤 権:

-   浪 믮 ணࠬ:
  - IF...THEN...ELSE
  - GOTO
  - EXIT

-  横:
  - FOR...LOOP
  - WHILE...LOOP

-  祭:
  - X := Y+Z
  - BONUS := CURRENT_SALARY*BONUS_MULTIPLIER

        PL/SQL ८ࠧ⥫(engine).
        -------------------------------
ਫ ய᪠ PL/SQL  १ ८ࠧ⥫ PL/SQL.
८ࠧ⥫ ࠡ뢠  ८ࠧ PL/SQL . ⮡ ८ࠧ


                                    -- 23 --



, PL/SQL ८ࠧ⥫ 믮 楤   ய᪠
SQL-   .
PL/SQL ८ࠧ⥫ 砥  ᫥騥  த:

  -    6.0  樥 믮 ࠭権.
  - SQL*Forms  3.0

᫨      6.0   믮 ࠭権, 
 ᯮ짮 PL/SQL   ਫ,  ᯮ ᫥騥
ਪ  ।⢠:
  - ORACLE Precompilers
  - ORACLE Call Interfaces (OCIs)
  - SQL*Plus
  - SQL*DBA

᫨    SQL*Forms  3.0   ᯮ짮 PL/SQL  
  SQL*Forms ਫ,   ⥬    
 믮 ࠭権.

         PL/SQL 蠥 믮.
        -------------------------------
PL/SQL 蠥 ᯮ, 믮 맮    ਫ.
८ࠧ 맮 ᮡ   ⥢ ।,  맮 
  ॣ㧪. PL/SQL ८ࠧ 맮 ᫥騬 ᯮᮡ:

- ᫨   ᮤন PL/SQL ८ࠧ⥫, ਫ
   ய ⢮ SQL-஢   ६.

- ᫨   SQL*Forms  3.0, ᮤন PL/SQL ८ࠧ⥫,
   SQL*Forms ਫ  믮 楤   뢠
  .

        PL/SQL ८ࠧ⥫   .
        ------------------------------------
PL/SQL  ਫ ய᪠  SQL-஢ ६.
PL/SQL ,ᮤঠ騩  SQL-஢   ய饭    
맮.  PL/SQL ८ࠧ⥫ ਫ  ய᪠ SQL-
 ,    ࠧ쭮 맮. .2.1 뢠  PL/SQL
८⥫ ८ࠧ ⮪  蠥 ᯮ.

        .2.1 襭 ᯮ   PL/SQL.
   Ŀ
                          Ŀ                      
     Ŀ           SQL            Ŀ  
                         IF...THEN                     
                            SQL                   
       ĳ    ELSE   >  PL/SQL   
                            SQL                        
                            ENDIF                      
                SQL              
                                                
     Ŀ       Ŀ                Ŀ  
                 ĳSQL>            
                            Ŀ                   
                 ĴSQL>      
              Ŀ                 
                 ĳSQL>      
                            Ŀ                   

                                    -- 24 --



                 ĴSQL>            
                              
   
        PL/SQL ८ࠧ⥫  ਪ ।⢠ .
        -----------------------------------------------------
PL/SQL  ਫ 믮 楤 㭪樨,   㣮
砥 ॡ 맮 . ᪮ PL/SQL  ᮤঠ 楤
, ਫ  ᯮ짮 PL/SQl ८ࠧ⥫,祭
 ਪ ।⢮ ,  ⮣,⮡ 믮 ᮮ⢥騩
. ਬ, SQL*Forms ਫ  ᯮ SQL- 
믮 ᫨⥫  権  묨. 믮 ⠪ SQL-஢
ॡ 맮 .  SQL*Forms  3.0 ⠪    믮
  楤 ஢ PL/SQL ८ࠧ⥫.  ⮬ 砥 PL/SQL
   맮 ᥩ .

        ᯮ짮 PL/SQL   ਫ.
        -----------------------------------------
 쭥訬 㪠  ᯮ짮 PL/SQL   ᯥ樠묨
ਪ묨 ।⢠   襭 ᯮ  ਫ:

ORACLE        - ய᪠ ⢮ ஢  ORACLE
Precompilers    Precompiler ணࠬ    PL/SQL
                  ஥ SQL.

SQL*Plus      -  쪨 楤   PL/SQL  SQL*Plus
                                                                        ।
                                                                               P

SQL*Forms     - 頥 ⢮ ਣ 蠣  SQL*Forms ਫ
                                                                               

          (SG)SEQUENCE Generator.
        
 ᫥⥫쭮 -   ᮡ   ᨨ 6.0. 
  ᯮ짮  祭 ᫥⥫ 祭.  ࠧ
 :

-     ᫥⥫쭮
-   ⢫ ८ࠧ  ᫥⥫쭮 ᥫ
-      ᫥⥫쭮 ᥫ

 ਫ   ᫥⥫쭮  㦨  楫, ⠪
:
- 㭨쭮 ᯮ ᥫ
- ६ ⪨

᫨  ਫ ᯮ 㭨  ᫥⥫ 稭 
- 楫, ᫥ ᬮ    
᫥⥫쭮.

        믮 ᫥⥫ ८ࠧ  SEQUENCE ᥫ.
        --------------------------------------------------------------
  ᨨ 6.0 ᫥⥫ 稭   祭 ⮫쪮
ணࠬ.  祭 ࢨ筮    㥭 㢥祭
᫥ 祭.  ⮤ ॡ ஢  祭 ࠭樨 
⠢  짮⥫   祭 ࢨ筮 . 
 ⭮  "८ࠧ  ᫥⥫ ". ᫨ 


                                    -- 25 --



 ਫ  ᨨ 6.0  ᫥   
맮 SG.  ᪫砥 ᫥⥫쭮 ८ࠧ  蠥
ࠫ ਫ.

 । ᫥⥫쭮   㯭   짮-
⥫. ᫥⥫쭮   㯭   
짮⥫ﬨ  . SG   ࠭,  﫠
᫥⥫쭮   襭,  ⮬ ᫥⥫쭮 
 㢥祭 ᭮.

ਬ ᫥⥫쭮.
--------------------------
 ਬ 뢠   짮⥫    
᫥⥫쭮  ८ࠧ (serialization).
짮⥫   ᮧ ᫥⥫쭮 SEQ1  
SQL-:

CREATE SEQUENCE SEQ1
    INCREMENT BY 10
    START WITH 50

ࢠ 稭 SEQ1 ᫥⥫쭮 50. ᫥騥 祭 㢥稢
 10.
  ࠧ   SEQ1  㣨 짮⥫ ।⢮
SQL-:

GRANT SELECT
    ON SEQ1
    TO CHARLES, DIANA

  । ⥪ 稭 SEQ1,ᯮ ᥢ CURRVAL 
᫥饬 :

SELECT SEQ1.CURRVAL
    FROM DUAL

  㢥稢 SEQ1,ᯮ ᥢ NEXTVAL.  SQL-
⠢    ⠡ DEPT,ᯮ ᫥饥 祭 SEQ1:

INSERT INTO DEPT(DEOTNO, DNAME, LOG)
   VALUES (SEQ1.NEXTVAL, 'CONSULTING', 'LONDON')

  ⠪ 㢥稢 SEQ1,ᯮ ᥢ NEXTVAL:

INSERT INTO DEPT(DEPTNO, DNAME, LOG)
   VALUES (SEQ1.NEXTVAL, 'MARKETING', 'MANCHESTER')

  믮   ,    ⥬ ᮢ訫 
  ࠭. , ᫨  ᮢ訫 ⥬ ⪠ ᢮
࠭樨, ᫥⥫ ,ᯮ짮     㯥
᭮.   ଠ樨   㪮  SQL .

饭  - ᫥⥫쭮 ᥫ (SN-SEQUENCE numbers).
---------
SEQUENCE ᫠   ࠭  SEQUENCE  (SC)  ⥬ -
  (SGA). SN   㯭 ॥, 祬    ᪮.



                                    -- 26 --



- ᫥⥫쭮 (SC - SEQUENCE cache) ⮨  ⮢,
   ᮤঠ  SN   ᫥⥫쭮.
ᯮ  ४樨  ண 㯠  ᥬ SN:

- , SC  ᮤঠ  ᫥⥫쭮, ᯮ㥬
  ६  襬 ਫ.
- 稢 ⢮ 祭   ᫥⥫쭮,
  ᮤঠ饩  SC.

        ⢮ ⮢  SC.
        --------------------------
 ਫ 頥  SC, ᫥⥫   .
, ᫨ ਫ 頥  ᫥⥫쭮, ன   ,
᫥⥫쭮   ⠭   । 饭  . ᫨ 
ਫ ᯮ ६  ᫥⥫쭮⥩,  
 筮 訬, ⮡ ᮤঠ  ᫥⥫쭮.  
⮬ 砥   ᫥⥫ ᫠  ॡ  ᮢ
 .  ண 㯠  ᥬ ᫥⥫쭮 㡥,  
 筮 ⮢,⮡ ᮤঠ  ᫥⥫쭮,
६ ᯮ㥬 訬 ਫ.

⢮ ⮢  SC । INIT.ORA ࠬ஬:
SEQUENCE_CACHE_ENTRIES.  㬮砭  稭 ࠢ 10. ᫨  
஢  , ᫥ ࠧ  -  ᫥⥫-
, ᯮ㥬   ᥠ ஢ન.  ଠ樨  ஢થ
   17" ଠ樨  ᠭ樮஢ 㯠. 
  " 㪮⢠   .

⢮ 祭    SC.
-----------------------------------------
 ᫥⥫쭮 ⠥  SC,稭 ᫥⥫쭮 
   -.  祭    祭. -
⢮ 祭 ᫥⥫쭮,  ,। ।⢮
CACHE-ࠬ   CREATE SEQUENCE.  㬮砭  稭 ࠢ
20. CREATE SEQUENCE  ᮧ SEQ2 ᫥⥫쭮 ⠪, 50
祭 ᫥⥫쭮   SC:

CREATE SEQUENCE SEQ2
     CACHE 50

 50 祭 SEQ2   ⠭  .  訢 51
祭,᫥騥 50 祭  ⠭  ᪠.

롮 孥 祭        襬
 ஢ ᫥⥫쭮  訬 ⥭  ᪠  SC.  
砥 ਨ   ᫥⥫ 稭  .  
ᯮ짮  NOCACHE  CREATE SEQUENCE ,⮡  
祭 ᯥ樠쭮 ᫥⥫쭮  SC.  ⮬ 砥  饭 
⮩ ᫥⥫쭮 ॡ ⥭  ᪠.  ⥭  ᪠ -
   ᫥⥫쭮. 騩 CREATE SEQUENCE 
ᮧ SEQ3 ᫥⥫쭮, 祭     :

  CREATE SEQUENCE SEQ3
      NOCACHE





                                    -- 27 --



        .
        
    ,  ᯮ SQL-஢,
ꥤ    ⠡.   ࠧ  :
-   
-    ᯮ
-  ᯮ짮 
-    

 ᮧ  㯯 ⠡,       .
饥   騥  ࠧ  .    
஢.  뢠  ᮬ.   ᮧ
 ஬ CREATE CLUSTER.   ଠ樨 頩 
ࠢ筮 㪮   SQL.

   ᯮ.
---------------------------------
஢    "pre-joined" ଥ.   -
஢ ⠡,騥  祭 ୮   
    . ꥤ c ⠡, ᭮ 
祭 ୮ , ॡ ⥭ 襣 ⢠   ᪠,
祬 ꥤ ⠡,  -⤥쭮.

롮 ஢.
----------------
᪮쪮 䠪஢ ।  ࠧ   ᯮ
ࠧ SQL-஢.  ᫥ ᬮ 룮 ᯮ ஢,
  蠥,஢  ⠡:

-   ᯮ SQL-஢, ꥤ 
  ⠡  ୮ .   ॡ 
  /뢮, 祬 ⠡, 㠫쭮.

-  ⠪  믮 SQL-஢, ॡ ⠡,
  ᭮  祭 ୮ . ᪮    
  祭 ୮       ,⠪  ॡ
   /뢮,祬,᫨   ⠡ 뫨  
  -⤥쭮.  ⠪   룮  ⮩ ᮡ 
  ,饣   ⠡.

- SQL-, 믮  ᪠஢ ⮫쪮  ⠡, 
    ॡ  ६,祬,᫨   ⠡ 뫠
   ⤥쭮.  ᪠஢ ஢ ⠡ ॡ
  㯠  ᥬ      筮 ᮤন 
  ,祬  뫮   ⠡,  ⤥쭮.
- SQL-, ⠢   ஢ ⠡ 
   祭  祩, 믮 , 祬
     筮 ⠡.   ॡ
  ⥭ 㦨   祩.

롨 ஢  ⠡,ᬮ ⮨⢠  ⪨
஢  ᢥ ॡ⥩  ਫ.ਬ,   
஢  ⠡,  ᥣ 㯭   ਫ 
 ꥤ.  ,⭮,  ᫥ ⮢
⠡, ᫨  ਫ ꥤ  ।,  祭 
祢  . , ,  ᯥਬ஢  ࠢ
६ 믮  ஢  ࠧ ⠡.


                                    -- 28 --



冷稢 ⠡  .
----------------------------------
冷, ஬ ஢ ⠡ ᮧ,㦠   ஬
  뫠  SQL-,   믮.   ⨬
४  㯮冷稢 ஢ ⠡:
-  ஢ ⠡  浪 㢥祭  ࠧ. -
   ⠡  ⠡    ⢮ ᥩ   ᮧ
  묨. 訥 ⠡  ⠡  訬 ⢮ ᥩ  
  祭 ୮    ᮧ .
- 㦠 ஢ ⠡  浪 㢥祭  ࠧ
-  SQL-,ꥤ ஢ ⠡,뫠
   ⠡  ࠦ FROM  浪 ⠭  ࠧ஢.

ࠢ ࠭⢮ .
-----------------------------------
 蠥   ࠭⢠   뤥   祭
୮ ,᭮뢠  祭 SIZE-ࠬ   CREATE
CLUSTER. 롨 稭 SIZE-ࠬ,  ,  ᫥ 業
㬬୮ ࠭⢮, 室   祭 ୮  
 ᥩ,ᮤঠ  祭 . 祭 ⮣ ࠬ
ࠦ  .

ਬ,᫨     ।  ᥩ   祭
୮ ,   ।  100 ,  ᫥
ᯮ짮 SIZE-ࠬ  祭 600.  業  
, 祭 ୮   ⥫ ,  
 .

㬬୮ ࠭⢮,뤥塞  祭  ୮ 
।  ⮬   祭 ୮   
 . ᫨   SIZE-ࠬ  CREATE CLUSTER, 
㤥  祭  ୮   . 롨ࠩ 祭
୮  ஦.

᫨  業 ᫨誮 ᮪,  뤥 ᫨誮  ࠭⢠ 
 祭 ୮ .  ⮬ 砥 ࠭⢮   
 ﭮ ᭮. ᫨  業 㤥 ᫨誮 ,  
ᬮ 뤥 筮 ࠭⢠   祭 ୮
. ॡ     ᥩ   祭
୮ .   ⮬ 砥 ਤ 뢠   
ᮢ,   祭 ୮ .

ਬ.
-------
 ਬ ࠢ ᯮ SQL , ꥤ饣  ⤥
⠡  ᯮ ⮣  SQL ,ꥤ饣    ⠡,
  . ਬ 뢠    
ᯮ ꥤ. SQL  ꥤ ⠡ ACCOUNTS  BRANCH 
 ACC_NUM:

SELECT
   FROM ACCOUNTS, BRANCH
   WHERE ACCOUNTS.ACC_NUM = BRANCH.ACC_NUM

 IACC_NUM ᮧ   ACC_NUM ⠡ ACCOUNTS.  ਢ SQL
஢筠 ⨪   믮,祭 EP ஬ 
, ⠡  ⤥쭮:


                                    -- 29 --



                 count    cpu    elap    phys     cr    cur    rows

Parse:               1     82     100       4     36      0
Execute:             1      1       6       1      0      2       0
Fetch:            5000  26416   42198    6666 200040      0    9999

Execution plan:

NESTED LOOPS
    TABLE ACCESS (FULL) OF 'BRANCH'
    TABLE ACCESS (BY ROWID) OF 'ACCOUNTS'
        INDEX (RANGE SCAN) OF 'IACC_NUM' (NON-UNIQUE)

⨪   믮  , ⠡ ஢
  ACC_NUM:

                 count    cpu    elap    phys     cr    cur    rows

Parse:               1     96     130       5     45      0
Execute:             1      1       4       1      0      2       0
Fetch:            5000  10729   34486    5010  17969     20    9999

Execution plan:

NESTED LOOPS
    TABLE AXXESS (FULL) OF 'BRANCH'
    TABLE ACCESS (CLUSTER) OF 'ACCOUNT'

 ਬ஢ ,  믮   ஢ ⠡
 䥪⨢. 룮 믮 ஢   
ࠧ稨 稭  phys   fetch 蠣.   ⨪ ࠦ
⢮  ,   ⠭  蠣 롮ન.
, ஢ ꥤ ⠥   ,祬
஢. ஢ ꥤ 믮 ⠪ 
॥,   稭   cpu  elap. , 蠣 롮ન
 ஢ ꥤ ॡ   ६,祬 
஢.

  (AP-ARREY PROCESSING).


AP   믮 ⥬ 㬥襭 ⢠ 맮   
ਫ. AP  襬 ਫ 믮  SQL  
ࠧ ⮫쪮  ⨢  .  ࠧ ᬠਢ:
-  ⠪ AP
-  AP 蠥 믮
- ᪮쪮 訬  ᨢ
-  ᯮ짮 AP   ਫ

 AP 蠥 ᯮ.
---------------------------
 SQL ,뤠 ਫ,ய᪠ १ .  
᪠ SQL   ,ਫ  믮 㭪樮 맮.
 맮   ᮡ ॣ㧪,  ᭨ 믮,
ᮡ, ᫨ ਫ  १ .  AP ਫ 
뤠 SQL   ࠧ  맮 . ਬ,  AP 
ਫ  믮  맮  ⮣,⮡ 믮 SQL 


                                    -- 30 --



100 ࠧ.  AP  ਫ  뫮  맢  100 ࠧ,  ࠧ
  믮.  । ॣ㧪   믮, AP
蠥 믮,ᮡ  .

롮 ࠧ஢ ᨢ.
-----------------------
룮 믮 AP   ࠧ஢ ᨢ. 稢 ࠧ ᨢ,
    ᭨ ⢮ 맮 . , 㢥祭 ࠧ
ᨢ  । ।  ਭ ⮦ 먣  믮.
 ᯮ짮 ࠧ ᨢ 100, 襣 ᯮ.
ਬ,।, ਫ 믮 SQL  10000 ࠧ. 
ࠧ ᨢ = 100 ਫ  맢  100 ࠧ,  ࠧ 
 100 믮. ,㢥祭 ࠧ ᨢ  100  
⥫쭮 믮.  ᨢ = 1000 ᭨ ᫮ 맮  10, 
 먣   ࠢ  ந襬  㢥祭 ࠧ஢ ᨢ 
ᮮ⭮  1:100.

ਬ AP   ਫ.
----------------------------------
 ਪ ।⢠    룮  AP.  ,
ਬ SQL*Forms ᯮ AP ⮬᪨.  㣨 । 
 ૣ ஫  AP. ।⢠ :

-  ४ (OPs - ORACLE Precompilers)
-  䥩 맮 (OCIs - ORACLE Call Interfaces)
- SQL*Plus
- SQL*Loader
- ⨫ ᯮ  

ᯮ짮 AP   ४஬.
-----------------------------------------
  ४஬   ᯮ짮 AP  믮
஥ SQL ஢  ࠧ. ᬮਬ  ஥ SQL
  Pro*C Precompiler ணࠬ:

EXEC SOL INSERT INTO ACCOUNTS (ACC_NUM) VALUES (:acc_no);

᫨ ६ acc_no 楫,  SQL  ய᪠   
믮  ࠧ.  ⠢    ⠡ ACCOUNT.   ⮩
  ACC_NUM  祭 acc_no.

,᫨ acc_no  ᨢ 楫 ६  100, SQL
 室    믮 100 ࠧ.  ⠢ 100 
ᥩ  ⠡ ACCOUNTS.     ACC_NUM 㤥  祭
  ⮢ ᨢ acc_no.   ⠪ ᯮ짮 AP  
४  祭  ᥩ    FETCH .
 ⥫쭮 ଠ樨      㪮:

- 㪮⢮ ணࠬ   ४
- 㪮⢮ 짮⥫ Pro*Ada ४

ᯮ짮 AP c 䥩ᮬ 맮  (OCIs).
----------------------------------------------------
ᯮ OCIs, ॠ AP  ᫥騬 OCI 맮:

OEXN - 맮 믮 SQL-஬  ࠧ.  㬥⮢
              ⮣ 맮 । ⢮ ࠧ 믮 SQL .


                                    -- 31 --



OFEN -  맮 頥 ⢮ ᥩ,祭  SQL-. 
                                                         㬥⮢ ⮣ 맮

  ଠ樨  ᯮ짮 AP  OCIs   ᫥騬
㪮⢠:
- 㪮⢮ ணࠬ  䥩 맮 .
- 㪮⢮ 짮⥫  䥩 맮 PRO*Ada ORACLE

ᯮ짮 AP  SQL*Plus.
----------------------------
SQL*Plus ᯮ AP ⮬᪨,⮡   ᥩ  
६.    ஫஢ ⢮ ᥩ  
६. SQL*Plus-⥬ ६ ARRAYSIZE ।  ⢮
ᥩ   ⭮ 맮 . 祭  㬮砭 = 20.
 ⮣,⮡ 㢨 ⥪饥 祭 ARRAYSIZE  SQL*Plus-:

SQL> SHOW ARRAYSIZE

  㢥 ⢮ ᥩ, 頥   ,
㢥稢 稭 ARRAYSIZE. ਬ,⮡ ⠭ 祭 = 100, 
SQL*Plus :

SQL> SET ARRAYSIZE 100

ARRAYSIZE ⠪ । ⢮ ᥩ,㥬   , 
 SQL*Plus COPY-.  ⥫쭮 ଠ樨 
SQL*Plus- 頩  㪮  樨  SQL*Plus.

ᯮ짮 AP  SQL*Loader.
------------------------------
SQL*Loader ᯮ AP ⮬᪨,⮡ ⠢  ᥩ   
   ࠧ. ,᫥ ஫஢ ⢮ ᥩ, ⠢塞
  ࠧ. SQL*Loader-ࠬ  ப ROWS । ⢮
ᥩ, ⠢塞   ࠧ.  祭  㬮砭 = 64.   ⮣,
⮡ ⠢  ᥩ  ࠧ,  祭 ROWS. ਬ,
⮡ ⠢ ᨢ  100 ᥩ, ᫥ 맢 SQL*Loader :

SQLLOAD USEID=BAM/BIM, CONTROL=C1.CTL, DISCARD=D1.DIS, ROWS=100

  ଠ樨  SQL*Loader 頩  㪮 짮⥫ 
⨫⠬  .

ᯮ짮 AP  /ᯮ ⨫⠬.
--------------------------------------------
  ᯮ ⨫ ᯮ AP ⮬᪨,⮡   
       . ,  ஫஢
⢮ , ।   ࠧ.

᫨  ᯮ ᯮ    ⮤  ப,  
஫஢ ࠧ ᨢ, । 祭 祢 ᫮ BUFFER. 
᫮ । ࠧ   ,ᯮ-   । .
祭 ⮣ ࠬ  㬮砭 । 襩 樮 ⥬.
 롮 稭 ࠬ BUFFER । ࠧ 襩   ,
⥬ 롥 ⢮ ᥩ,஥   ।  ࠧ.
ᯮ ࠭ 祭  । 稭 BUFFER.  ਬ,᫨
  ।   4     ⮬ 100 ᥩ  ࠧ,
। ࠬ BUFFER = 409.600 .    ⨢஢ 
᫥饩 :

                                    -- 32 --



IMP USERID=BAM/BIM IGNORE=Y FULL=N TOUSER=BEM BUFFER=409600

᫨  ᯮ   ᯮ  ࠪ⨢ ०, ⨫ 
  ࠧ . ,  ,   筮
訬,⮡   .  ଠ樨  
㪮 짮⥫  ⨫⠬  .





















































                                    -- 33 --
