[20151208]隐含參數_trace_pin_time.txt

[20151208]隐含參數_trace_pin_time.txt

--使用隐含參數可以trace how long a current pin is held,作為測試與學習了解oracle内部相關知識。

--自己測試看看。

1.測試環境：

SYS@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> @ &r/hide _trace_pin_time

NAME             DESCRIPTION                           DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE

---------------- ------------------------------------- ---------------------- ---------------------- ----------------------

_trace_pin_time  trace how long a current pin is held  TRUE                   0                      0

SCOTT@book> alter session set "_trace_pin_time"=1 ;

alter session set "_trace_pin_time"=1

                  *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

--缺點是要修改spfile檔案。重新開機才生效。而且執行sql語句就寫轉儲檔案。

--最好配合10046事件才行，不然根本不知道那條sql語句再執行。

SCOTT@book> alter system set "_trace_pin_time"=1 scope=spfile;

System altered.

2.重新開機開始測試：

SYS@book> startup

ORACLE instance started.

Total System Global Area  626327552 bytes

Fixed Size                  2255832 bytes

Variable Size             243270696 bytes

Database Buffers          373293056 bytes

Redo Buffers                7507968 bytes

Database mounted.

Database opened.

SCOTT@book> alter system flush buffer_cache;

SCOTT@book> alter session set events '10046 trace name context forever, level 12';

Session altered.

SCOTT@book> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30

      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30

      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20

      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30

      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30

      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10

      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20

      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10

      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30

      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20

      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30

      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20

      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.

SCOTT@book> select * from emp where empno=7369;

3.觀察轉儲：

=====================

PARSING IN CURSOR #139653689738120 len=17 dep=0 uid=83 oct=3 lid=83 tim=1449544660918810 hv=1745700775 ad='7c2d8518' sqlid='a2dk8bdn0ujx7'

select * from emp

END OF STMT

PARSE #139653689738120:c=1999,e=1812,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3956160932,tim=1449544660918808

EXEC #139653689738120:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=1449544660918969

WAIT #139653689738120: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=87108 tim=1449544660919076

pin ktewh26: kteinpscan dba 0x1000092:4 time 1788453759

pin kdswh11: kdst_fetch dba 0x1000093:1 time 1788453814

pin kdswh11: kdst_fetch dba 0x1000094:1 time 1788453841

pin kdswh11: kdst_fetch dba 0x1000095:1 time 1788453858

pin kdswh11: kdst_fetch dba 0x1000096:1 time 1788453875

pin kdswh11: kdst_fetch dba 0x1000097:1 time 1788453891

FETCH #139653689738120:c=0,e=198,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=1449544660919330

WAIT #139653689738120: nam='SQL*Net message from client' ela= 526 driver id=1650815232 #bytes=1 p3=0 obj#=87108 tim=1449544660919910

pin kdswh11: kdst_fetch dba 0x1000097:1 time 1788454581

WAIT #139653689738120: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=87108 tim=1449544660920020

FETCH #139653689738120:c=0,e=93,p=0,cr=1,cu=0,mis=0,r=13,dep=0,og=1,plh=3956160932,tim=1449544660920070

STAT #139653689738120 id=1 cnt=14 pid=0 pos=1 obj=87108 op='TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=184 us cost=3 size=532 card=14)'

*** 2015-12-08 11:17:42.758

WAIT #139653689738120: nam='SQL*Net message from client' ela= 1837795 driver id=1650815232 #bytes=1 p3=0 obj#=87108 tim=1449544662758022

CLOSE #139653689738120:c=0,e=27,dep=0,type=0,tim=1449544662758248

--過濾pin開頭的資訊：

-- 說明： dba 0x1000092 一定是段頭塊。

SCOTT@book> select header_file,header_block,bytes,blocks from dba_segments where owner=user and segment_name='EMP';

HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS

----------- ------------ ---------- ----------

          4          146      65536          8

SCOTT@book> column PARTITION_NAME noprint

SCOTT@book> select * from dba_extents where owner=user and segment_name='EMP';

OWNER  SEGMENT_NAME  SEGMENT_TYPE  TABLESPACE_NAME   EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO

------ ------------- ------------- ---------------- ---------- ---------- ---------- ---------- ---------- ------------

SCOTT  EMP           TABLE         USERS                     0          4        144      65536          8            4

SCOTT@book> set verify off

SCOTT@book> @ &r/dfb16 0x1000092

    RFILE#     BLOCK# TEXT

---------- ---------- ----------------------------------------

         4        146 alter system dump datafile 4 block 146 ;

SCOTT@book> @ &r/dfb16 0x1000097

         4        151 alter system dump datafile 4 block 151 ;

--144+8-1=151。

--而且可以看到最後一塊讀了2次。

4.再分析另外1條sql語句：

PARSING IN CURSOR #139951095649984 len=34 dep=0 uid=83 oct=3 lid=83 tim=1449546531367697 hv=85843297 ad='7c267d18' sqlid='9gs6uhh2jvrb1'

select * from emp where empno=7369

PARSE #139951095649984:c=2000,e=2268,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2949544139,tim=1449546531367695

EXEC #139951095649984:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2949544139,tim=1449546531367861

WAIT #139951095649984: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=87108 tim=1449546531367968

FETCH #139951095649984:c=0,e=53,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=2949544139,tim=1449546531368077

STAT #139951095649984 id=1 cnt=1 pid=0 pos=1 obj=87108 op='TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=45 us cost=1 size=38 card=1)'

STAT #139951095649984 id=2 cnt=1 pid=1 pos=1 obj=87109 op='INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=29 us cost=0 size=0 card=1)'

WAIT #139951095649984: nam='SQL*Net message from client' ela= 482 driver id=1650815232 #bytes=1 p3=0 obj#=87108 tim=1449546531368737

FETCH #139951095649984:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=2949544139,tim=1449546531368777

WAIT #139951095649984: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=87108 tim=1449546531368817

*** 2015-12-08 11:49:48.371

WAIT #139951095649984: nam='SQL*Net message from client' ela= 57002386 driver id=1650815232 #bytes=1 p3=0 obj#=87108 tim=1449546588371225

CLOSE #139951095649984:c=0,e=21,dep=0,type=0,tim=1449546588371405

--根本看不到pin，即使你重新整理alter system flush buffer_cache;結果也一樣。說明全部塊都是共享模式讀取。

5.建立一個ename的非唯一索引看看：

create index i_emp_ename on emp (ename);

--注意非唯一。

SCOTT@book> select rowid,emp.* from emp where ename = 'SCOTT' ;

ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

AAAVREAAEAAAACXAAH       7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20

SCOTT@book> @ &r/rowid AAAVREAAEAAAACXAAH

    OBJECT       FILE      BLOCK        ROW DBA                  TEXT

---------- ---------- ---------- ---------- -------------------- ----------------------------------------

     87108          4        151          7 4,151                alter system dump datafile 4 block 151 ;

PARSING IN CURSOR #140053318609456 len=51 dep=0 uid=83 oct=3 lid=83 tim=1449547940062195 hv=2659843 ad='850f8438' sqlid='4bhpp2h02j5h3'

select rowid,emp.* from emp where  ename = 'SCOTT'

PARSE #140053318609456:c=2000,e=2227,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1237151973,tim=1449547940062193

EXEC #140053318609456:c=1000,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1237151973,tim=1449547940062340

WAIT #140053318609456: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1449547940062442

pin qeilwhrp: qeilbk dba 0x100852b:1 time 772629825

pin kdswh05: kdsgrp dba 0x1000097:1 time 772629858

FETCH #140053318609456:c=0,e=100,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=1237151973,tim=1449547940062595

WAIT #140053318609456: nam='SQL*Net message from client' ela= 510 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1449547940063160

pin kdiwh16: kdifxs dba 0x100852b:1 time 772630517

FETCH #140053318609456:c=0,e=30,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=1237151973,tim=1449547940063242

STAT #140053318609456 id=1 cnt=1 pid=0 pos=1 obj=87108 op='TABLE ACCESS BY INDEX ROWID EMP (cr=3 pr=0 pw=0 time=88 us cost=2 size=38 card=1)'

STAT #140053318609456 id=2 cnt=1 pid=1 pos=1 obj=90321 op='INDEX RANGE SCAN I_EMP_ENAME (cr=2 pr=0 pw=0 time=77 us cost=1 size=0 card=1)'

WAIT #140053318609456: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1449547940063356

*** 2015-12-08 12:12:27.055

WAIT #140053318609456: nam='SQL*Net message from client' ela= 6992338 driver id=1650815232 #bytes=1 p3=0 obj#=87106 tim=1449547947055719

CLOSE #140053318609456:c=0,e=20,dep=0,type=0,tim=1449547947055907

---------- ---------- ------------------------------------------------------------

SCOTT@book> @ &r/dfb16 0x100852b

         4      34091 alter system dump datafile 4 block 34091 ;

SCOTT@book> @ &r/which_obj 4 34091

OWNER  SEGMENT_NAME          SEGMENT_TYPE       TABLESPACE_NAME   EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO

------ --------------------  ------------------ ---------------- ---------- ---------- ---------- ---------- ---------- ------------

SCOTT  I_EMP_ENAME           INDEX              USERS                     0          4      34088      65536          8            4

SCOTT@book> select header_file,header_block,bytes,blocks from dba_segments where owner=user and segment_name='I_EMP_ENAME';

          4        34090      65536          8

-- 可以确定是dba=4,34091是索引的root節點。

SCOTT@book> @ &r/which_obj 4 151

SCOTT  EMP                   TABLE              USERS                     0          4        144      65536          8            4

6.建立一個ename的唯一索引看看：

SCOTT@book> drop index i_emp_ename ;

Index dropped.

SCOTT@book> create unique index i_emp_ename on emp (ename);

Index created.

PARSING IN CURSOR #140053320064088 len=48 dep=0 uid=83 oct=3 lid=83 tim=1449556761489688 hv=2811958051 ad='7c0c8d70' sqlid='4h4pmrumtq4t3'

Select rowid,emp.* from emp where  ename='SCOTT'

PARSE #140053320064088:c=0,e=114,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1640456157,tim=1449556761489686

EXEC #140053320064088:c=1000,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1640456157,tim=1449556761489831

WAIT #140053320064088: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1449556761489940

FETCH #140053320064088:c=0,e=58,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=1640456157,tim=1449556761490050

STAT #140053320064088 id=1 cnt=1 pid=0 pos=1 obj=87108 op='TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=35 us cost=1 size=38 card=1)'

STAT #140053320064088 id=2 cnt=1 pid=1 pos=1 obj=90322 op='INDEX UNIQUE SCAN I_EMP_ENAME (cr=1 pr=0 pw=0 time=20 us cost=0 size=0 card=1)'

WAIT #140053320064088: nam='SQL*Net message from client' ela= 524 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1449556761490749

FETCH #140053320064088:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1640456157,tim=1449556761490821

WAIT #140053320064088: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1449556761490899

*** 2015-12-08 14:39:37.654

WAIT #140053320064088: nam='SQL*Net message from client' ela= 16163624 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1449556777654547

CLOSE #140053320064088:c=0,e=20,dep=0,type=0,tim=1449556777654725

--根本看不到pin。

最後：

--取消相關設定。

SCOTT@book> alter system reset "_trace_pin_time";