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

时间： 2024-09-20 15:07:31

[20151208]隐含参数_trace_pin_time.txt的相关文章

[20171109]查看隐含参数脚本.txt

[20171109]查看隐含参数脚本.txt --//查看隐含参数的脚本,今天没事修改一下增加查询description字段的内容.也有网友要求提供这个脚本,实际上这些脚本都是自己工作 --//中不断收集整理.网上许多地方都能找到. $ cat hide.sql col name format a40 col description format a66 col session_value format a22 col default_value format a22 col system_va

[20131116]12c的EXTENDED VARCHAR2与隐含参数_scalar_type_lob_storage_threshold.txt

[20131116]12c的EXTENDED VARCHAR2与隐含参数_scalar_type_lob_storage_threshold.txt 参考链接:http://space.itpub.net/267265/viewspace-776806/ google查询了一些资料: 发现:SYS@test01p> @hide _scalar_type_lob_storage_threshold;NAME                                     DESCRIPTI

[20170502]11G查询隐含参数视图.txt

[20170502]11G查询隐含参数视图GV$SYSTEM_PARAMETER3.txt --//oracle 存在许多隐含参数,一直以为oracle没有提供正常的视图查询该内容,实际上oracle 11G已经提供这方面的功能,只不过oracle并不公开. --//自己也是偶然发现: 1.环境: SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER -------------------------

[20140318]隐含参数_db_block_max_cr_dba

[20140318]隐含参数_db_block_max_cr_dba .txt 许多人都知道隐含参数_db_block_max_cr_dba缺省6,可以做一些简单的测试: 1.建立测试环境: SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11

[20120106]使用bash shell显示隐含参数.txt

我个人喜欢使用putty打开两个窗口,一个执行sql语句,另外一个查看目录user_dump_dest下的trc文件.如果要查询隐含参数,往往要切换sys用户,执行一个脚本: $ cat hide.sqlcol name format a36col description format a66col session_value format a22col default_value format a22col system_value format a22 select   a.ksppinm 

[20140318]隐含参数_db_block_max_cr_dba 2

[20140318]隐含参数_db_block_max_cr_dba 2.txt 许多人都知道隐含参数_db_block_max_cr_dba缺省6,如果多个会话修改同一块呢?会发生什么情况: 参考链接做一次测试: 1.建立测试环境: SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterpr

[20131111]参数optimizer_features_enable.txt

[20131111]参数optimizer_features_enable.txt optimizer_features_enable可以使系统升级后保持原来的执行计划.我自己很少做这种升级操作,昨天在给别人做优化时遇到一个问题, 自己做一个记录,用户改动了参数optimizer_features_enable,不知道什么原因要改这个参数,导致一些执行计划很慢! 举一个例子: 1.建立测试环境: SCOTT@test> @ver BANNER --------------------------

Oracle 隐含参数

Oracle 隐含参数 点击(此处)折叠或打开 set pagesize 9999 set line 9999 col NAME format a40 col KSPPDESC format a50 col KSPPSTVL format a20 SELECT a.INDX,        a.KSPPINM NAME,        a.KSPPDESC,        b.KSPPSTVL FROM x$ksppi a,        x$ksppcv b WHERE a.INDX = b.

关于oracle隐含参数 总结

1.回顾 之前整理了两篇关于x$表的介绍,连接如下: http://blog.csdn.net/cymm_liu/article/details/7611675 http://blog.csdn.net/cymm_liu/article/details/7611758 数据字典系统表主要由4部分组成:1.内部RDBMS表:x$--2.数据字典表:--$(比如user$)3.动态性能视图:gv$--,v$--4.数据字典视图:user_--,all_--,dba_--数据库启动时,动态创建x$,在