ITPub博客

首页 > 数据库 > Oracle > [20151208]隐含参数_trace_pin_time.txt

[20151208]隐含参数_trace_pin_time.txt

原创 Oracle 作者:lfree 时间:2015-12-08 14:58:44 0 删除 编辑

[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;
System altered.

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;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

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开头的信息:
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
pin kdswh11: kdst_fetch dba 0x1000097:1 time 1788454581

-- 说明: 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
    RFILE#     BLOCK# TEXT
---------- ---------- ----------------------------------------
         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
END OF STMT
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'
END OF STMT
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
=====================

--过滤pin开头的信息:

pin qeilwhrp: qeilbk dba 0x100852b:1 time 772629825
pin kdswh05: kdsgrp dba 0x1000097:1 time 772629858
pin kdiwh16: kdifxs dba 0x100852b:1 time 772630517

SCOTT@book> @ &r/dfb16 0x1000097
    RFILE#     BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
         4        151 alter system dump datafile 4 block 151 ;

SCOTT@book> @ &r/dfb16 0x100852b
    RFILE#     BLOCK# TEXT
---------- ---------- ------------------------------------------------------------
         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';
HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS
----------- ------------ ---------- ----------
          4        34090      65536          8

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

SCOTT@book> @ &r/which_obj 4 151
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

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'
END OF STMT
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";
System altered.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1868292/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6293182