大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
Jetbrains全系列IDE稳定放心使用
【学习笔记】Oracle数据库10049用于分析SQL解析笔记案例
时间:2016-11-05 13:54 来源:Oracle研究中心 作者:HTZ 点击:
次
天萃荷净
Oracle研究中心学习笔记:分享一篇关于Oracle数据库关于SQL解析的详细文档,该文档详细介绍使用10049 event事件来分析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,查询SQL的HASH_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
Oracle oracleplus.netDatabase 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 | 认真就输
————————————–ORACLE-DBA—————————————-
最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库10049用于分析SQL解析笔记案例
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/187131.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...