认真就输

SQL> select * from v$version;

BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bi

PL/SQL Release 10.2.0.1.0 – Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 – Production

NLSRTL Version 10.2.0.1.0 – Production

SQL> @find_sql.sql

Enter value for sql_text: from scott.emp

Enter value for sql_id:

SQL_ID         CHILD HASH_VALUE  PLAN_HASH      EXECS         ETIME     AVG_ETIME USERNAME

————- —— ———- ———- ———- ————- ————- ————-

SQLTEXT

————————————————————————————————

ggqns3c1jz86c      0   52404428 3956160932          1           .17           .17 SYS

select * from scott.emp

SQL> @10to16.sql

Enter value for number10: 52404428

TO_CHAR(‘52404428’,’XXXXXXXXXXXXXXXXXXXXXXXXX

———————————————

                                      31fa0cc

2000+0010+0020

a0cc2030                           

SQL> @16to10.sql

Enter value for number16: a0cc2030 

                    NUMBER10

—————————-

                  2697732144

SQL> @parameter_hide.sql

+————————————————————————+

| display hide parameter value                                       |

+————————————————————————+

Enter Search Parameter (i.e. max|all) : kks

PARAMETER                                SESSION_VALUE        INSTANCE_VALUE       DESCRIPTION

—————————————- ——————– ——————– ————————————————————

_kks_use_mutex_pin                       FALSE                FALSE                Turning on this will make KKS use mutex for cursor pins.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug event 10049 trace name context forever,level 2697732144;

Statement processed.

SQL> startup force pfile=’/tmp/123.ora’;

ORACLE instance started.

Total System Global Area  524288000 bytes

Fixed Size                  2022080 bytes

Variable Size             218105152 bytes

Database Buffers          301989888 bytes

Redo Buffers                2170880 bytes

Database mounted.

Database opened.

SQL>  oradebug setmypid

Statement processed.

SQL> oradebug event 10049 trace name context forever,level 2697732144;

Statement processed.

SQL> select * from scott.emp;

SQL> select * from scott.emp;

SQL> select * from scott.emp;

SQL> select * from scott.emp;

SQL> oradebug tracefile_name;

/oracle/app/oracle/admin/orcl1021/udump/orcl1021_ora_7566.trc

*** 2014-05-04 13:26:36.128

/******************************************************************************

KGLTRCLCK kglget     hd = 0x0x4408bb808  KGL Lock addr = 0x0x3c1932c00 mode = N

KGLTRCPIN kglpin     hd = 0x0x4408bb808  KGL Pin  addr = 0x0x3c190e4f8 mode = X

KGLTRCPIN kglpndl    hd = 0x0x4408bb808  KGL Pin  addr = 0x0x3c190e4f8 mode = X

KGLTRCLCK kglget     hd = 0x0x4408bb5e0  KGL Lock addr = 0x0x3c1909030 mode = N

KGLTRCPIN kglpin     hd = 0x0x4408bb5e0  KGL Pin  addr = 0x0x3c190e938 mode = X

KGLTRCPIN kglpndl    hd = 0x0x4408bb5e0  KGL Pin  addr = 0x0x3c190e938 mode = S

KGLTRCLCK kgllkdl    hd = 0x0x4408bb5e0  KGL Lock addr = 0x0x3c1909030 mode = N

KGLTRCLCK kgllkdl    hd = 0x0x4408bb808  KGL Lock addr = 0x0x3c1932c00 mode = N

第一次硬分析,这个我们可以看到在父游标上面先kglget NULL的锁，再获取PIN的锁，随后再

是子游标的lock/pin，最后释放父游标的NULL locK锁

*******************************************************************************/

/******************************************************************************

*** 2014-05-04 13:26:48.940

KGLTRCLCK kglget     hd = 0x0x4408bb808  KGL Lock addr = 0x0x3c1923f68 mode = N

KGLTRCLCK kglget     hd = 0x0x4408bb5e0  KGL Lock addr = 0x0x3c18fef90 mode = N

KGLTRCPIN kglpin     hd = 0x0x4408bb5e0  KGL Pin  addr = 0x0x3c193c840 mode = S

KGLTRCPIN kglpndl    hd = 0x0x4408bb5e0  KGL Pin  addr = 0x0x3c193c840 mode = S

KGLTRCLCK kgllkdl    hd = 0x0x4408bb5e0  KGL Lock addr = 0x0x3c18fef90 mode = N

KGLTRCLCK kgllkdl    hd = 0x0x4408bb808  KGL Lock addr = 0x0x3c1923f68 mode = N

这里是软分析，也是第二次分析，这里可以看到整个过程没有X的锁

*******************************************************************************/

KGLTRCLCK kglget     hd = 0x0x4408bb808  KGL Lock addr = 0x0x3c1932e28 mode = N

KGLTRCLCK kglget     hd = 0x0x4408bb5e0  KGL Lock addr = 0x0x3c1932ee0 mode = N

KGLTRCPIN kglpin     hd = 0x0x4408bb5e0  KGL Pin  addr = 0x0x3c190e3e8 mode = S

KGLTRCPIN kglpndl    hd = 0x0x4408bb5e0  KGL Pin  addr = 0x0x3c190e3e8 mode = S

KGLTRCPIN kglpin     hd = 0x0x4408bb5e0  KGL Pin  addr = 0x0x3c190e938 mode = S

KGLTRCPIN kglpndl    hd = 0x0x4408bb5e0  KGL Pin  addr = 0x0x3c190e938 mode = S

再执行一次

SQL> select * from scott.emp;

只增加了下面的2行

*** 2014-05-04 13:28:08.866

下面是多次运行后，软软分析的trace，这里我们可以只在子游标上面获取pin的锁

KGLTRCPIN kglpin     hd = 0x0x4408bb5e0  KGL Pin  addr = 0x0x3c18fff58 mode = S

KGLTRCPIN kglpndl    hd = 0x0x4408bb5e0  KGL Pin  addr = 0x0x3c18fff58 mode = S

SQL> @find_sql.sql

Enter value for sql_text: from scott.emp

Enter value for sql_id:

SQL_ID         CHILD HASH_VALUE  PLAN_HASH      EXECS         ETIME     AVG_ETIME USERNAME

————- —— ———- ———- ———- ————- ————- ————-

SQLTEXT

———-

ggqns3c1jz86c      0   52404428 3956160932          5           .01           .00 SYS

select * from scott.emp

SQL> select kglhdadr,kglhdpar,kglnaobj from x$kglob where kglhdadr in (‘00000004408BB5E0′,’00000004408BB808’);

KGLHDADR         KGLHDPAR         KGLNAOBJ

—————- —————- ———————————————————————-

00000004408BB5E0 00000004408BB808 select * from scott.emp

00000004408BB808 00000004408BB808 select * from scott.emp