当前位置: 首页 > BASIC > 正文

10049用于分析SQL解析

 

1,数据库版本

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

2,查询SQLHASH_VALUE

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

3,配置event

关于10049每一个level的意思与配置方法见:配置10049

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.

4,运行sql

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

本文固定链接: http://www.htz.pw/2015/03/28/10049%e7%94%a8%e4%ba%8e%e5%88%86%e6%9e%90sql%e8%a7%a3%e6%9e%90.html | 认真就输

该日志由 huangtingzhong 于2015年03月28日发表在 BASIC 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: 10049用于分析SQL解析 | 认真就输
关键字: