{"id":412,"date":"2015-08-24T05:28:50","date_gmt":"2015-08-24T05:28:50","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=412"},"modified":"2015-08-24T05:28:50","modified_gmt":"2015-08-24T05:28:50","slug":"sql%ec%97%90%ec%84%9c-%ec%82%ac%ec%9a%a9%ed%95%98%eb%8a%94-bind-%eb%b3%80%ec%88%98%eb%a5%bc-%ec%95%8c%ec%95%84-%eb%82%b4%ea%b8%b0-%ec%9c%84%ed%95%9c-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=412","title":{"rendered":"SQL\uc5d0\uc11c \uc0ac\uc6a9\ud558\ub294 Bind \ubcc0\uc218\ub97c \uc54c\uc544 \ub0b4\uae30 \uc704\ud55c \ubc29\ubc95"},"content":{"rendered":"


<\/P>
\n

SQL\uc5d0\uc11c \uc0ac\uc6a9\ud558\ub294 Bind \ubcc0\uc218\ub97c \uc54c\uc544 \ub0b4\uae30 \uc704\ud55c \uba87\uac00\uc9c0 \ubc29\ubc95<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

 – Create table<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

create table big_table as select empno , ename , job , mgr , hiredate , sal , comm , to_char(deptno) deptno from scott.emp ;<\/SPAN><\/P>
\n


<\/P>
\n


<\/P>
\n

scott \uc720\uc800\uac00 \uae30\ubcf8\uc801\uc73c\ub85c \uc5c6\uc73c\uc2e0 \ubd84\uc740 $ORACLE_HOME\/rdbms\/admin\/utlsampl.sql \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc774\uc6a9\ud574\uc11c \uc0dd\uc131<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

– \ub370\uc774\ud130 \ubee5\ud280\uae30<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

Insert into big_table select * from big_table ; \uc774\ub7f0\uc2dd\uc73c\ub85c \ub370\uc774\ud130 \uac74\uc218\ub97c \uc880 \ub9cc\ub4e0\ub2e4. ( 7,340,032 \uc815\ub3c4 \uc0dd\uc131 \ud6c4 \ud14c\uc2a4\ud2b8 \ud568)<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

– Index \uc0dd\uc131<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

create index big_table_deptno_idx on big_table(deptno) ;<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

– Autotrace \ud65c\uc131 \ud654<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

$ORACLE_HOME\/sqlplus\/admin\/plustrce.sql \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc218\ud589 \uc2dc\ud0a4\uba74 plustrace \ub77c\ub294 \uad8c\ud55c\uc774 \uc0dd\uae30\ub294\ub370 \uc774 \uad8c\ud55c\uc744 \uc77c\ubc18 \uc720\uc800\uc5d0\uac8c \uc8fc\uba74 autotrace \uae30\ub2a5\uc744 \uc0ac\uc6a9 \uac00\ub2a5 \ud558\ub2e4.<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

– DBMS_XPLAN \ud328\ud0a4\uc9c0 \uc0ac\uc6a9<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\ud639\uc2dc\ub098 dbms_xplan \ud328\ud0a4\uc9c0\uac00 \uc0dd\uc131 \uc548\ub418\uc2e0 \ubd84\ub4e4\uc740 $ORACLE_HOME\/rdbms\/admin\/dbmsxpln.sql \uc0ac\uc6a9<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

– Plan table \uc0dd\uc131<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

@$ORACLE_HOME\/rdbms\/admin\/utlxplan.sql \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc774\uc6a9\ud558\uc5ec PLAN_TABLE \uc0dd\uc131<\/SPAN>
 <\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\uc5ec\uae30\uae4c\uc9c0 \uc900\ube44\uac00 \uc644\ub8cc \ub418\uc5c8\uc73c\uba74 \ubcf8\uaca9\uc801\uc73c\ub85c BInd \ubcc0\uc218 \ud655\uc778\ud558\ub294 \ubc29\ubc95\uc744 \uc54c\uc544 \ubcf4\ub3c4\ub85d \ud558\uc790<\/SPAN><\/P>
\n

 <\/P>
\n

1. Plan \ud655\uc778 \ubc29\ubc95<\/SPAN><\/FONT><\/P>
\n

 <\/P>
\n

Autotrace\uc640 dbms_xplan\uc744 \uc774\uc6a9\ud558\uc5ec Plan\uc744 \ud655\uc778<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

00:45:27 BSTAR 3048><\/SPAN>set autotrace trace exp<\/FONT><\/STRONG>
00:45:38 BSTAR 3048><\/SPAN>var b1 number ;    <– \ubcc0\uc218 \uc120\uc5b8<\/SPAN>
<\/STRONG>00:45:47 BSTAR 3048><\/SPAN>exec :b1 := 40 ;    <– \ubcc0\uc218 \uac12 \uc9c0\uc815<\/SPAN><\/STRONG><\/P>
\n

PL\/SQL \ucc98\ub9ac\uac00 \uc815\uc0c1\uc801\uc73c\ub85c \uc644\ub8cc\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/SPAN><\/P>
\n

\uacbd   \uacfc: 00:00:00.01<\/SPAN>
00:45:56 BSTAR 3048><\/SPAN>SELECT * FROM big_table WHERE deptno = :b1 ;<\/SPAN>
<\/STRONG>\uacbd   \uacfc: 00:00:00.00<\/SPAN><\/P>
\n

Execution Plan<\/SPAN>
———————————————————-<\/SPAN>
Plan hash value: 724613332<\/SPAN><\/P>
\n

————————————————————————————————–<\/SPAN>
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time   |<\/SPAN>
————————————————————————————————–<\/SPAN>
|   0 | SELECT STATEMENT            |                      |   140K|    12M| 10597   (1)| 00:02:08<\/SPAN> <\/SPAN><\/SPAN>|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE            |   140K|    12M| 10597   (1)| 00:02:08<\/SPAN> <\/SPAN><\/SPAN>|*  2 |   INDEX RANGE SCAN          | BIG_TABLE_DEPTNO_IDX | 56243 |       |  9555   (1)| 00:01:55<\/SPAN> <\/SPAN><\/SPAN>————————————————————————————————–<\/SPAN><\/P>
\n

Predicate Information (identified by operation id):<\/SPAN>
—————————————————<\/SPAN><\/P>
\n

   2 – access(“DEPTNO”=:B1)<\/SPAN><\/P>
\n

Note<\/SPAN>
—–<\/SPAN>
   – dynamic sampling used for this statement<\/SPAN><\/P>
\n

00:46:16 BSTAR 3048><\/SPAN>set autotrace off
<\/STRONG><\/FONT>00:46:26 BSTAR 3048>
00:46:27 BSTAR 3048>explain plan for SELECT * FROM big_table WHERE deptno = :b1 ;<\/FONT><\/STRONG><\/P>
\n

\ud574\uc11d\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/P>
\n

\uacbd   \uacfc: 00:00:00.01
00:47:01 BSTAR 3048>select * from table(dbms_xplan.display) ;<\/STRONG><\/FONT><\/P>
\n

PLAN_TABLE_OUTPUT<\/SPAN>
————————————————————————————————–<\/SPAN>
Plan hash value: 724613332<\/SPAN><\/P>
\n

————————————————————————————————–<\/SPAN>
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time   |<\/SPAN>
————————————————————————————————–<\/SPAN>
|   0 | SELECT STATEMENT            |                      |   140K|    12M| 10597   (1)| 00:02:08<\/SPAN> <\/SPAN><\/SPAN>|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE            |   140K|    12M| 10597   (1)| 00:02:08<\/SPAN> <\/SPAN><\/SPAN>|*  2 |   INDEX RANGE SCAN          | BIG_TABLE_DEPTNO_IDX | 56243 |       |  9555   (1)| 00:01:55<\/SPAN> <\/SPAN><\/SPAN>————————————————————————————————–<\/SPAN><\/P>
\n

Predicate Information (identified by operation id):<\/SPAN>
—————————————————<\/SPAN><\/P>
\n

   2 – access(“DEPTNO”=:B1)<\/SPAN><\/P>
\n

Note<\/SPAN>
—–<\/SPAN>
   – dynamic sampling used for this statement<\/SPAN><\/P>
\n

18 \uac1c\uc758 \ud589\uc774 \uc120\ud0dd\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/SPAN><\/P>
\n

\uacbd   \uacfc: 00:00:00.06<\/SPAN>
00:47:15 BSTAR 3048><\/SPAN><\/P>
\n

 <\/P>
\n

2. Bind \ubcc0\uc218 \uac12 \ud655\uc778 \ubc29\ubc95 #1 ( 10046 Trace \uc774\uc6a9 )<\/SPAN><\/FONT><\/P>
\n

 <\/P>
\n

\uc138\uc158\uc5d0\uc11c 10046 Event\ub97c \uc774\uc6a9\ud558\uc5ec BInd \ubcc0\uc218\ub97c \ud655\uc778\ud574 \ubcf4\uc790.<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

00:52:31 BSTAR 2028>var b1 number<\/SPAN>
00:52:42 BSTAR 2028>exec :b1 := 40 ;<\/SPAN><\/P>
\n

PL\/SQL \ucc98\ub9ac\uac00 \uc815\uc0c1\uc801\uc73c\ub85c \uc644\ub8cc\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/SPAN><\/P>
\n

\uacbd   \uacfc: 00:00:00.00<\/SPAN><\/P>
\n

00:05:45 BSTAR 4080>alter session set tracefile_identifier=’TEST’ ;<\/SPAN><\/P>
\n

\uc138\uc158\uc774 \ubcc0\uacbd\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/SPAN><\/P>
\n

\uacbd   \uacfc: 00:00:00.00<\/SPAN>
00:52:49 BSTAR 2028>alter session set events ‘10046 trace name context forever , level 4 ‘ ;<\/SPAN><\/P>
\n

\uc138\uc158\uc774 \ubcc0\uacbd\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/SPAN><\/P>
\n

\uacbd   \uacfc: 00:00:00.01<\/SPAN>
00:53:16 BSTAR 2028>SELECT * FROM big_table WHERE deptno = :b1 ;<\/SPAN><\/P>
\n

\uc120\ud0dd\ub41c \ub808\ucf54\ub4dc\uac00 \uc5c6\uc2b5\ub2c8\ub2e4.<\/SPAN><\/P>
\n

\uacbd   \uacfc: 00:00:19.11<\/SPAN>
00:53:47 BSTAR 2028>alter session set events ‘10046 trace name context off’ ;<\/SPAN><\/P>
\n

\uc138\uc158\uc774 \ubcc0\uacbd\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/SPAN>
<\/P>
\n

\uc774\uc81c udump\uc5d0 \uac00\uc11c \ud2b8\ub808\uc774\uc2a4 \ud30c\uc77c\uc774\ub984\uc5d0 TEST\uac00 \ubd99\uc740 \ud30c\uc77c\uc744 \uc5f4\uc5b4\ubcf4\uc790<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

=====================<\/SPAN>
PARSING IN CURSOR #2<\/FONT><\/STRONG> len=43 dep=0 uid=64 oct=3 lid=64 tim=2669424238 hv=536337181<\/STRONG> ad=’27e06380′<\/STRONG>
SELECT * FROM big_table where deptno = :b1 
<\/STRONG>END OF STMT
PARSE #2:c=0,e=68,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2669424234
BINDS #2<\/FONT><\/STRONG>:
kkscoacd<\/SPAN>
 <\/SPAN>Bind#0<\/SPAN><\/STRONG>
 <\/SPAN> <\/SPAN>oacdty=02<\/SPAN><\/STRONG> <\/SPAN>mxl=22(22) mxlc=00 mal=00 scl=00 pre=00<\/SPAN>
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0<\/SPAN>
  kxsbbbfp=0835c364  bln=22  avl=02  flg=05<\/SPAN>
 <\/SPAN> <\/SPAN>value=40<\/SPAN>
<\/STRONG>EXEC #2:c=15625,e=6031,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2669434735<\/SPAN>
*** 2008-04-11 00:00:57.703<\/SPAN>
FETCH #2:c=4062500,<\/SPAN>e=25353648<\/SPAN><\/STRONG>,p=85106,<\/SPAN>cr=86476<\/SPAN><\/STRONG>,cu=0,mis=0,r=0,dep=0,og=1,tim=2694789310<\/SPAN>
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=54789 op=<\/SPAN>‘TABLE ACCESS FULL BIG_TABLE<\/SPAN><\/STRONG> <\/SPAN>(cr=86476 pr=85106 pw=0 time=25353646 us)’<\/SPAN>
*** 2008-04-11 00:09:46.875<\/SPAN>
=====================<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

– \ucd08 \uac04\ub2e8 \uc124\uba85 –<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

hv=536337181<\/SPAN><\/STRONG> <\/SPAN>: v$sqlarea \uc601\uc5ed\uc548\uc758 hash_value\uac12\uacfc \uc77c\uce58<\/SPAN><\/P>
\n

ad=27e06380 :<\/SPAN><\/STRONG>  v$sqlarea \uc601\uc5ed\uc548\uc758 address \uac12\uacfc \uc77c\uce58<\/SPAN><\/P>
\n

oacdty=02   :<\/SPAN> <\/SPAN><\/STRONG>bind \ubcc0\uc218\uc758 \ub370\uc774\ud130 \ud0c0\uc785\uc744 \ub098\ud0c0\ub0c4   ( 01 : varchar2 \/ 02 : number \/ 96 : char )<\/SPAN><\/P>
\n

value=40  :<\/SPAN><\/STRONG>  \ubcc0\uc218 \uac12<\/SPAN><\/P>
\n

e=25353648 :<\/SPAN><\/STRONG> <\/SPAN>elapsed time<\/SPAN><\/P>
\n

cr=86476     :<\/SPAN><\/STRONG> <\/SPAN>Logical Read\ub7c9 (86476 blocks)<\/SPAN><\/P>
\n

TABLE ACCESS FULL BIG_TABLE :<\/SPAN><\/STRONG> <\/SPAN>Table full Scan\uc744 \uc218\ud589 \ud588\ub2e4\ub294 \ub73b ^^* <\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

 tkprof\uc218\ud589 \uacb0\uacfc\ub97c \ucd94\uac00\ud574 \ubcf4\uba74<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

call     count       cpu    elapsed       disk      query    current        rows<\/SPAN>
——- ——  ——– ———- ———- ———- ———-  ———-<\/SPAN>
Parse        1      0.00       0.00          0          0          0           0<\/SPAN>
Execute      1      0.01       0.00          0          0          0           0<\/SPAN>
Fetch        1      4.06      25.35      85106      86476          0           0<\/SPAN>
——- ——  ——– ———- ———- ———- ———-  ———-<\/SPAN>
total        3      4.07      25.35<\/STRONG>      <\/FONT>85106      86476<\/STRONG><\/FONT>         0          0<\/FONT><\/STRONG><\/SPAN><\/P>
\n

Misses in library cache during parse: 0
<\/P>
\n

Optimizer mode: ALL_ROWS
Parsing user id: 64 <\/P>
\n

Rows     Row Source Operation
——-  —————————————————
      0  TABLE ACCESS FULL BIG_TABLE<\/STRONG> <\/FONT>(cr=86476 pr=85106 pw=0 time=25353646 us)<\/SPAN><\/P>
\n

           <\/SPAN><\/P>
\n

\ucffc\ub9ac\ub97c \uc218\ud589\ud558\ub294\ub370 25.35\ucd08\uac00 \uc18c\uc694 \ub418\uc5c8\uc73c\uba70 \uc77d\uc740 \ube14\ub85d \uc218\ub294 86,476 \ube14\ub85d\uc774\uace0 Return\ub41c \ub370\uc774\ud130 \uac74\uc218\ub294 0 \uac74\uc774\ub2e4.<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\uadf8\ub7f0\ub370 \uc5ec\uae30\uc11c \uc774\uc0c1\ud55c \uc810\uc744 \ubc1c\uacac\ud560 \uc218 \uc788\ub2e4. \ubc1c\uacac\ud558\uc2e0 \ubd84\uc740 \ub300\ub2e8\ud558\uc2ed\ub2c8\ub2e4 ~~!!!<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\ubd84\uba85 1 \uc5d0\uc11c plan\uc744 \uc218\ud589\ud574 \ubcf4\uba74 index Scan\uc744 \ud558\ub3c4\ub85d \ud50c\ub79c\uc774 \ub098\uc654\ub294\ub370 \uc2e4\uc81c \uc218\ud589 \uacb0\uacfc\ub97c \ud655\uc778\ud574 \ubcf4\uba74 full table scan\uc744 \ud558\uace0 \uc788\ub2e4 .<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\uc989, \uac00\uc7a5 \ud754\ud55c runtime \uc2e4\ud589 \uacc4\ud68d\uacfc \uc218\ud589 \uc804 \uc2e4\ud589\uacc4\ud68d\uc774 \ud2c0\ub9b0 \uacbd\uc6b0\uc774\ub2e4. \uc6d0\uc778\uc740 \uc22b\uc790\ud615\uc774 \ubb38\uc790\ud615\ubcf4\ub2e4 \uc6b0\uc120(?)\uc2dc \ub418\uc5b4 \ubb38\uc790\uac00 \uc22b\uc790\ub85c \ubcc0\ud615\uc774 \uc774\ub8e8\uc5b4 \uc9c0\uae30 \ub54c\ubb38\uc774\ub2e4. \uc989 \uc778\ub371\uc2a4 \uceec\ub7fc\uc5d0 \ubcc0\ud615\uc774 \uac00\ud574\uc9c0\ub294 \uacb0\uacfc\uc5ec\uc11c \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9 \ubabb\ud558\uac8c \ub418\ub294 \uac83\uc774\ub2e4.<\/SPAN><\/P>
\n


<\/SPAN><\/P>
\n

\uadf8\ub7fc \uc218\ud589\uc911\uc778 \ub2e4\ub978 \uc138\uc158\uc758 SQL\uc5d0 \ub300\ud558\uc5ec BInd \ubcc0\uc218\ub97c \uc54c\uace0 \uc2f6\uc73c\uba74 <\/SPAN>\ub2e4\uc74c\uc758 \uba87\uac00\uc9c0 \ubc29\ubc95\uc744 \uc0ac\uc6a9<\/SPAN><\/P>
\n

 <\/P>
\n

3. \ub2e4\ub978 \uc138\uc158\uc5d0\uc11c \uc218\ud589\uc911\uc778 SQL\uc758 \ubc14\uc778\ub4dc \ud655\uc778(oradebug)<\/SPAN><\/FONT><\/P>
\n

 <\/P>
\n

\ubaa8\ub2c8\ud130\ub9c1\uc744 \ud558\ub2e4\uac00 \uc218\ud589\uc911\uc778 SQL\uc774 \uc544\ubb34\ub798\ub3c4 \uc758\uc2ec\uc2a4\ub7ec\uc6cc\uc11c Bind\ubcc0\uc218\uac12\uacfc Plan\uc744 \ud655\uc778\ud574 \ubcf4\uace0\uc790 \ud55c\ub2e4\uba74 \uac00\uc7a5 \uac04\ub2e8\ud558\uac8c oradebug \ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4.<\/SPAN><\/P>
\n

\uc5ec\uae30\uc120 OS Process ID\ub97c \uc0ac\uc6a9\ud574\ubcf4\ub3c4\ub85d \ud558\uc790.<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\ud14c\uc2a4\ud2b8 \uc2dc\ub098\ub9ac\uc624<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

– \uc138\uc1581 : \ubc14\uc778\ub4dc \ubcc0\uc218\ub97c \uc0ac\uc6a9\ud558\ub294 \ucffc\ub9ac\ub97c \uc218\ud589\uc911\uc784 ( O\/S Process ID \ub294 2092 \uc784)<\/SPAN><\/P>
\n

– \uc138\uc1582 : \ubaa8\ub2c8\ud130\ub9c1 \uc911 \uc138\uc1581 \ucffc\ub9ac\uac00 \uc624\ub798 \uc218\ud589\ub418\uc5b4\uc11c \ud50c\ub79c\uacfc bind \ubcc0\uc218\ub97c \ud655\uc778\ud558\uace0\uc790 10046  Trace\ub97c \uc218\ud589 \ud568.<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

– \uc138\uc1581<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

01:24:02 BSTAR 2092>var b1 number ;<\/SPAN>
01:24:10 BSTAR 2092>exec :b1 := 40 ;<\/SPAN><\/P>
\n

PL\/SQL \ucc98\ub9ac\uac00 \uc815\uc0c1\uc801\uc73c\ub85c \uc644\ub8cc\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/SPAN><\/P>
\n

\uacbd   \uacfc: 00:00:00.00<\/SPAN>
01:24:16<\/SPAN><\/STRONG> <\/SPAN>BSTAR 2092>SELECT empno , ename , sal , deptno FROM big_table WHERE deptno = :b1 ;<\/SPAN><\/P>
\n

\uc120\ud0dd\ub41c \ub808\ucf54\ub4dc\uac00 \uc5c6\uc2b5\ub2c8\ub2e4.<\/SPAN><\/P>
\n

\uacbd   \uacfc: 00:00:20.06<\/SPAN><\/P>
\n


– \uc138\uc1582<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

01:24:18 SYS 3672>oradebug setospid 2092<\/SPAN>
Oracle pid: 19, Windows thread id: 2092, image: ORACLE.EXE (SHAD)<\/SPAN>
01:24:22 SYS 3672>oradebug event 10046 trace name context forever , level 4<\/SPAN>
\uba85\ub839\ubb38\uc744 \ucc98\ub9ac\ud588\uc2b5\ub2c8\ub2e4.<\/SPAN>
01:24:52 SYS 3672>oradebug close_trace<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\uc124\uba85 \uc138\uc1581\uc5d0\uc11c \uc218\ud589\uc911\uc778 SQL\uc774 \uc624\ub798 \uac78\ub9ac\ub294 \uac83\uc744 \ud655\uc778\ud558\uace0 DBA\uc138\uc158\uc778 \uc138\uc1582\uc5d0\uc11c 10046 Trace\ub97c \uc218\ud589\ud568.<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\ud2b8\ub808\uc774\uc2a4 \ud30c\uc77c\uc744 \ud655\uc778\ud574 \ubcf4\uba74 \uc5b4\ub5a4 \uacb0\uacfc\uac00 \uc788\uc744\uae4c\uc694 ? \ud2b8\ub808\uc774\uc2a4 \ud30c\uc77c\uc744 \ud655\uc778\ud574 \ubcf4\uba74 \ub0b4\uac00 \uc6d0\ud558\ub294 \ucffc\ub9ac \ubc0f Bind\ubcc0\uc218\ub294 \uad6c\uacbd(?)\ub3c4 \ubabb\ud560 \uac83\uc774\ub2e4.<\/SPAN><\/P>
\n

\uc989 10046 Trace\uc5d0\uc11c Bind \ubcc0\uc218\ub97c \uc5bb\uc744 \uc218 \uc788\ub294 \ubc29\ubc95\uc740 \ud574\ub2f9 SQL\uc774 \ud30c\uc2f1\ub2e8\uacc4\ub97c \uac70\uce58\uae30 \uc804\uc5d0 \uc218\ud589 \ud558\uc5ec\uc57c\uc9c0\ub9cc SQL\uad6c\ubb38 \ubc0f Bind \ubcc0\uc218\ub97c \ud655\uc778 \ud560\uc218 \uc788\ub2e4.<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\uadf8\ub7fc oradebug\ub97c \uc774\uc6a9\ud574\uc11c \uc218\ud589 \uc911\uc778 SQL\uc758 Bind \ubcc0\uc218 \ud655\uc778\uc740 \ubd88\uac00\ub2a5 \ud55c \uac83\uc778\uac00 ?<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\uc774\ub7f4 \uacbd\uc6b0 processstate\ub97c \uc774\uc6a9\ud558\ub294 \ubc29\ubc95\uc774 \uc788\ub2e4. <\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

01:51:28 SYS 3672><\/SPAN>oradebug setospid 340<\/SPAN><\/STRONG>
Oracle pid: 19, Windows thread id: 340, image: ORACLE.EXE (SHAD)<\/SPAN>
01:53:16 SYS 3672><\/SPAN>oradebug dump processstate 10<\/SPAN>
<\/STRONG>\uba85\ub839\ubb38\uc744 \ucc98\ub9ac\ud588\uc2b5\ub2c8\ub2e4.<\/SPAN>
01:53:25 SYS 3672><\/SPAN>oradebug tracefile_name<\/SPAN><\/STRONG>
c:\\oracle\\product\\10.2.0\\admin\\bstar\\udump\\bstar_ora_340.trc<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\ud2b8\ub798\uc774\uc2a4 \ud30c\uc77c\uc744 \uc5f4\uc5b4 \ubcf4\uba74 \uc138\uc158\uc5d0\uc11c \uc0ac\uc6a9\ud55c \ucee4\uc11c \uc815\ubcf4\ub97c \ud655\uc778 \ud560 \uc218 \uc788\ub2e4.<\/SPAN><\/P>
\n

 <\/P>
\n


<\/P>
\n

 <\/P>
\n

4. \ub2e4\ub978 \uc138\uc158\uc758 \ubc14\uc778\ub4dc \ubcc0\uc218 \ud655\uc778 ( v$sql_bind_capture  \ubdf0 \uc774\uc6a9)<\/SPAN><\/FONT><\/P>
\n

 <\/P>
\n

\uc6b0\uc120 v$sqlare\ub098 \uae30\ud0c0 sql\uad00\ub828 \ubdf0\uc5d0\uc11c hash value AND address \ub610\ub294 sql_id\ub97c \ud655\uc778\ud558\uc790<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

02:18:56 SYS 3672>SELECT sql_id , sql_text , hash_value , address FROM v$sqlarea<\/SPAN>
02:21:03   2  WHERE sql_text like ‘SELECT * FROM big_table where deptno = :b1%’ ;<\/SPAN><\/P>
\n

SQL_ID                     SQL_TEXT                                                                      HASH_VALUE           ADDRESS<\/SPAN>
———— ———————————————— ——————— ——–<\/SPAN>
5t4387sgzgqsx    SELECT * FROM big_table where deptno = :b1                           536337181            27E06380<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

SELECT * FROM v$sql_bind_capture<\/SPAN>
WHERE sql_id = ‘5t4387sgzgqsx’<\/SPAN><\/P>
\n

OR<\/SPAN><\/P>
\n

SELECT * FROM v$sql_bind_capture<\/SPAN>
WHERE hash_value = ‘536337181’<\/SPAN>
AND   address = ’27E06380′  \uc218\ud589\ud574 \ubcf4\uc790<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

NAME         CHILD_NUMBER   POSITION  DATATYPE_STRING     WAS_CAPTURED     VALUE_STRING<\/SPAN>
——– ———— ———- —————- ————–   ————<\/SPAN>
:B1                      1                1              NUMBER                    YES                      40<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\uc704\uc640 \uac19\uc774 \uacb0\uacfc\ub97c \uc5bb\uc744 \uc218 \uc788\ub2e4.<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

child_number : \ud574\ub2f9 SQL\uc758 Child Number Count<\/SPAN><\/P>
\n

position : \ud558\ub098\uc758 SQL\uc5d0 \uc5ec\ub7ec\uac1c\uc758 Bind\ubcc0\uc218\ub97c \uc0ac\uc6a9\ud560 \uacbd\uc6b0 (1 ~ n )<\/SPAN><\/P>
\n

datatype_string : bind \ubcc0\uc218 \ub370\uc774\ud130 \ud0c0\uc785<\/SPAN><\/P>
\n

value_string : \ubc14\uc778\ub4dc \ubcc0\uc218 \uac12<\/SPAN><\/P>
\n

 <\/P>
\n

 <\/P>
\n

5. dbms_xplan \ud328\ud0a4\uc9c0 \uc0ac\uc6a9<\/SPAN><\/FONT><\/P>
\n

 <\/P>
\n

sql_id\ub97c \uc54c \uacbd\uc6b0 \uc27d\uac8c \ud655\uc778 \uac00\ub2a5<\/SPAN> <\/SPAN>\ub2e8, v$sql_plan \ubdf0\ub85c \uc870\ud68c\uac00 \ub418\uc9c0 \uc54a\ub294 \uacbd\uc6b0 \ud655\uc778 \ubd88\uac00 !!!<\/FONT><\/STRONG><\/P>
\n

 <\/P>
\n

v$sql_bind_captuer \ubdf0\uc5d0 \ub370\uc774\ud130\uac00 \uc788\uc5b4\ub3c4 v$sql_plan\ubdf0\uc5d0 \ud574\ub2f9 sql_id\ub098 hash_value\uc758 SQL\uc774 \uc874\uc7ac \ud558\uc9c0 \uc54a\uc73c\uba74 \uc0ac\uc6a9 \ubabb\ud568<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\uc704\uc640 \uac19\uc740 \uacbd\uc6b0\ub294 \ub2e4\uc74c\uacfc \uac19\uc740 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd \ud568<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

02:30:36 SYS 3672>select * from table(dbms_xplan.display_cursor(‘5t4387sgzgqsx’ ,1,’typical +peeked_binds’));<\/SPAN><\/P>
\n

PLAN_TABLE_OUTPUT<\/SPAN>
——————————————————————<\/SPAN>
SQL_ID  5t4387sgzgqsx, child number 1<\/SPAN><\/P>
\n

SELECT * FROM big_table where deptno = :b1<\/SPAN><\/P>
\n

NOTE: cannot fetch plan for SQL_ID: 5t4387sgzgqsx, CHILD_NUMBER: 1<\/SPAN>
      Please verify value of SQL_ID and CHILD_NUMBER;<\/SPAN>
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

 \uc815\uc0c1\uc801\uc778 \ud50c\ub79c \ubc0f \ubc14\uc778\ub4dc \ubcc0\uc218\uac00 \uc874\uc7ac \ud560 \uacbd\uc6b0<\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

dbms_xplan.display_cursor( ‘sql_id’ , child_number , ‘option’)<\/SPAN><\/STRONG><\/P>
\n

 <\/SPAN><\/P>
\n

 02:35:03 SYS 3672>select * from table(dbms_xplan.display_cursor(‘726g67b2tbpv6′ ,0,’typical +peeked_binds’)<\/FONT><\/STRONG>) ;<\/P>
\n

PLAN_TABLE_OUTPUT<\/SPAN><\/p>\n

————————————————–<\/SPAN>
SQL_ID  726g67b2tbpv6, child number 0<\/SPAN>
————————————-<\/SPAN>
SELECT empno , ename , sal , deptno FROM big_table WHERE deptno = :b1<\/SPAN><\/P>
\n

Plan hash value: 3993303771<\/SPAN><\/P>
\n

——————————————————————————-<\/SPAN>
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |<\/SPAN>
——————————————————————————-<\/SPAN>
|   0 | SELECT STATEMENT  |           |       |       | 24708 (100)|          |<\/SPAN>
|*  1 |  TABLE ACCESS FULL| BIG_TABLE |   956 | 52580 | 24708   (5)| 00:04:57 |<\/SPAN>
——————————————————————————-<\/SPAN><\/P>
\n

Peeked Binds (identified by position):<\/SPAN>
————————————–<\/SPAN><\/P>
\n

   1 – :B1 (NUMBER): 40<\/FONT><\/STRONG><\/P>
\n

Predicate Information (identified by operation id):<\/SPAN>
—————————————————<\/SPAN><\/P>
\n

   1 –<\/SPAN> <\/SPAN>filter(TO_NUMBER(“DEPTNO”)=:B1)   <— Full Scan \ud558\uac8c \ub41c \uc6d0\uc778<\/SPAN><\/STRONG><\/P>
\n

Note<\/SPAN>
—–<\/SPAN>
   – dynamic sampling used for this statement<\/SPAN><\/P>
\n


27 \uac1c\uc758 \ud589\uc774 \uc120\ud0dd\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/SPAN><\/P>
\n

\uacbd   \uacfc: 00:00:00.32<\/SPAN>
02:35:04 SYS 3672>select * from table(<\/SPAN>dbms_xplan.display_cursor(‘726g67b2tbpv6′ ,0,’all +peeked_binds’)<\/STRONG><\/FONT>) ;<\/P>
\n

PLAN_TABLE_OUTPUT<\/SPAN><\/p>\n

————————————————–<\/SPAN>
SQL_ID  726g67b2tbpv6, child number 0<\/SPAN>
————————————-<\/SPAN>
SELECT empno , ename , sal , deptno FROM big_table WHERE deptno = :b1<\/SPAN><\/P>
\n

Plan hash value: 3993303771<\/SPAN><\/P>
\n

——————————————————————————-<\/SPAN>
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |<\/SPAN>
——————————————————————————-<\/SPAN>
|   0 | SELECT STATEMENT  |           |       |       | 24708 (100)|          |<\/SPAN>
|*  1 |  TABLE ACCESS FULL| BIG_TABLE |   956 | 52580 | 24708   (5)| 00:04:57 |<\/SPAN>
——————————————————————————-<\/SPAN><\/P>
\n

Query Block Name \/ Object Alias (identified by operation id):<\/SPAN>
————————————————————-<\/SPAN><\/P>
\n

   1 – SEL$1 \/<\/SPAN> <\/SPAN>BIG_TABLE@SEL$1<\/SPAN><\/A><\/P>
\n

Peeked Binds (identified by position):<\/SPAN>
————————————–<\/SPAN><\/P>
\n

   1 – :B1 (NUMBER): 40<\/SPAN><\/P>
\n

Predicate Information (identified by operation id):<\/SPAN>
—————————————————<\/SPAN><\/P>
\n

   1 – filter(TO_NUMBER(“DEPTNO”)=:B1)          <–<\/SPAN> <\/SPAN>Full Scan \ud558\uac8c \ub41c \uc6d0\uc778<\/SPAN><\/STRONG><\/P>
\n

Column Projection Information (identified by operation id):<\/SPAN>
———————————————————–<\/SPAN><\/P>
\n

   1 – “EMPNO”[NUMBER,22], “ENAME”[VARCHAR2,10], “SAL”[NUMBER,22],<\/SPAN>
       “DEPTNO”[VARCHAR2,40]<\/SPAN><\/P>
\n

Note<\/SPAN>
—–<\/SPAN>
   – dynamic sampling used for this statement<\/SPAN><\/P>
\n


38 \uac1c\uc758 \ud589\uc774 \uc120\ud0dd\ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/SPAN><\/P>
\n

\uacbd   \uacfc: 00:00:00.12<\/SPAN>
02:38:28 SYS 3672><\/SPAN><\/P>
\n

 <\/SPAN><\/P>
\n

\uc704\uc640 \uac19\uc740 \ubc29\ubc95\uc744 \uc774\uc6a9\ud558\uc5ec SQL Plan \ubc0f Bind \ubcc0\uc218\ub97c \ud655\uc778 \ud560 \uc218 \uc788\ub2e4.<\/SPAN><\/P><\/p>\n","protected":false},"excerpt":{"rendered":"

SQL\uc5d0\uc11c \uc0ac\uc6a9\ud558\ub294 Bind \ubcc0\uc218\ub97c \uc54c\uc544 \ub0b4\uae30 \uc704\ud55c \uba87\uac00\uc9c0 \ubc29\ubc95      – Create table   create table big_table as select empno , ename , job , mgr , hiredate , sal , comm , to_char(deptno) deptno from scott.emp ; scott \uc720\uc800\uac00 \uae30\ubcf8\uc801\uc73c\ub85c \uc5c6\uc73c\uc2e0 \ubd84\uc740 $ORACLE_HOME\/rdbms\/admin\/utlsampl.sql \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc774\uc6a9\ud574\uc11c \uc0dd\uc131   – \ub370\uc774\ud130 \ubee5\ud280\uae30   Insert into […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[145,146,147],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/412"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=412"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/412\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=412"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=412"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=412"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}