eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

July 12, 2017

Oracle SQL和PL/SQL中字符串单引号的处理

在 Oracle 的查询中,经常因为字符串要添加单引号,但是单引号的位置和个数往往不易把握。很多朋友都问过这样的问题。

请看以下查询,最外层的两个单引号作为引号操作符,第二个作为转义符号,随意第三个单引号是真实的数据输出,所以,以下查询输出的是第三个单引号:

SQL> select '''' from dual;

'
-
'

进一步的,如果我们希望在字符串中也留有单引号,那么会是这一个样子的:

SQL> select '''YUNHE''ENMO' from dual;

'''YUNHE''E
-----------
'YUNHE'ENMO

SQL> select '''YUNHE''''ENMO' from dual;

'''YUNHE''''
------------
'YUNHE''ENMO

SQL> select '''YUNHE''''ENMO''' from dual;

'''YUNHE''''E
-------------
'YUNHE''ENMO'

总之,只要理解转义符号的存在,处理单引号就不复杂了。

Posted by eygle at 6:01 PM | Permalink | FAQ (252)

July 7, 2017

Oracle Mutex 等待事件之: cursor mutex X

Cursor Mutex X 是当会话对某个 Cursor 请求排他模式锁,但是该Cursor 上已经有 S 或 X 模式的锁定,因而产生的等待。

The session requests the mutex for a cursor object in exclusive mode, and it must wait because the resource is busy. The mutex is busy because either the mutex is being held in exclusive mode by another session or the mutex is being held shared by one or more sessions. The existing mutex holder(s) must release the mutex before the mutex can be granted exclusively.

ParameterDescription

P1

Hash value of cursor

P2

Mutex value (top 2 bytes contain SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)

P3

Mutex where (an internal code locator) OR'd with Mutex Sleeps

需要持有 Cursor Mutex X 的操作主要有:

  • 在父游标下创建新的子游标,但是这类操作在11.2之后被 Build Lock 替代,不再需要;
  • 捕获SQL中的绑定变量;
  • 更新、生成 SQL 统计信息,主要是 V$SQLSTATS 的内容;
  • Cursor Mutex X 主要指对于父游标的操作;
  • Building a new cursor under a parent
    • Although this operation is cheaper, building many cursors under a parent cursor is not recommended.
  • Capture SQL bind data
  • Build or Update statistics blocks
  • Mutex is in the parent cursor.

以下是在生产环境中观察到的实际现象,注意在获取 cursor:mutex X 的过程中,调用的底层函数是kkscsAddChildNode 这是增加子游标的操作,需要进一步的获取排他Mutex ,使用的函数是 kgxExclusive :

SQL> select sid,event,p1,p2,p3 from v$session where username='EYGLE' and sid=135;
       SID EVENT				      P1	 P2	  P3
---------- ---------------------------------- ---------- ---------- ----------
       135 cursor: mutex X		       978779761 1.6063E+12 1.4603E+11

SQL> oradebug short_stack;
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1876<-sspuser()+112<-__sighandler()<-semtimedop()+10
<-skgpwwait()+160<-ksliwat()+1865<-kslwaitctx()+163<-ksfwaitctx()+14<-kgxWait()+650
<-kgxExclusive()+295<-kkscsAddChildNode()+337<-kkscscid_opt_eval()+1290<-kkscsCheckCriteria()+211
<-kkscsCheckCursor()+783<-kkscsSearchChildList()+1262<-kksfbc()+12105<-kkspsc0()+1173
<-kksParseCursor()+116<-opiosq0()+1588<-opipls()+11479<-opiodr()+916<-rpidrus()+211
<-skgmstack()+148<-rpiswu2()+638<-rpidrv()+1384<-psddr0()+473<-psdnal()+457<-pevm_EXIM()+308
<-pfrinstr_EXIM()+53<-pfrrun_no_tool()+63<-pfrrun()+627<-plsql_run()+649<-peicnt()+301
<-kkxexe()+525<-opiexe()+17785<-kpoal8()+2224<-opiodr()+916<-ttcpip()+2755<-opitsk()+1717
<-opiino()+966<-opiodr()+916<-opidrv()+570<-sou2o()+103<-opimai_real()+133
<-ssthrdmain()+252<-main()+201<-__libc_start_main()+253<-_start()+36

关于Mutex 的使用一直在变化之中。

Posted by eygle at 5:12 PM | Permalink | FAQ (252)

July 6, 2017

Oracle Mutex 等待事件之: cursor: mutex S

Cursor: Mutex S 等待事件是指,一个会话以共享模式请求一个Mutex,而其他会话以排他模式正在持有Cursor 上的 Mutex。

文档说明可以看到,此处的Mutex是位于 Cursor 对象上的固有 Mutex,也就是针对 Parent Cursor 的。这个等待的第一个参数会披露出 SQL 的 Hash Value。

A session waits on this event when it is requesting a mutex in shared mode, when another session is currently holding a this mutex in exclusive mode on the same cursor object.

ParameterDescription

P1

Hash value of cursor

P2

Mutex value (top 2 bytes contain SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)

P3

Mutex where (an internal code locator) OR'd with Mutex Sleeps

Posted by eygle at 5:05 PM | Permalink | FAQ (252)

July 5, 2017

Oracle Mutex 等待事件之: cursor pin X

在 Oracle 官方文档,关于 cursor pin X 等待事件的说明是:

A session waits on this event when it is requesting an exclusive mutex pin for a cursor object and it must wait because the resource is busy. The mutex pin for a cursor object can be busy either because a session is already holding it exclusive, or there are one or more sessions which are holding shared mutex pin(s). The exclusive waiter must wait until all holders of the pin for that cursor object have released it, before it can be granted. Wait Time: Microseconds

翻译过来就是:

当一个会话对一个 cursor 对象请求 排他模式的 Mutex pin 时,因为该对象已有 X 或者 S 模式的 Mutex pin 存在,所以必须处于等待。直至所有 X 或 S 模式的 pin 被释放后,才能够获得 X pin。

cursor pin X 等待有三个参数,第一个参数就是 cursor 的 hash value。

ParameterDescription

P1

Hash value of cursor

P2

Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)

P3

Mutex where (an internal code locator) OR'd with Mutex Sleeps


需要对 Cursor 执行 Pin X 的操作包括:

  • 会话在某些串行化操作时需要获得 X 模式的 Pin,例如构建一个子游标;
  • 在Cursor 上执行排他操作,如 dbms_shared_pool.purge, keep/unkeep 等;
  • 一个或多个进程正在reference 该Mutex (shared mutex pin)

Posted by eygle at 10:29 PM | Permalink | FAQ (252)

Oracle Mutex 等待事件之: cursor pin S

cursor: pin S 等待事件说明,当一个会话(session)试图去更新一个共享的 Mutex Pin,同时已经有其他会话正在更新(updating)同一个 Shared Mutex Pin。注意,Pin S 的Mutex位于子游标上,是动态创建的,无法从等待事件中获得,但是可以在竞争出现的情况下,通过观察 x$mutex_sleep_history 来获得。

文档说明如下:

A session waits on this event when it wants to update a shared mutex pin and another session is currently in the process of updating a shared mutex pin for the same cursor object. This wait event should rarely be seen because a shared mutex pin update is very fast.

Wait Time: Microseconds

ParameterDescription

P1

Hash value of cursor

P2

Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)

P3

Mutex where (an internal code locator) OR'd with Mutex Sleeps

在实践中,当大量会话对某一个Cursor进行并发的软解析时,这一等待会被观察到。

此前的一篇文档说明在这里:AWR报告分析之三:cursor: pin S 的原理与案例分析

Posted by eygle at 4:52 PM | Permalink | FAQ (252)

近期发表

  • Oracle 11.2.0.4 和 12c 安装 INS-30131 临时位置权限解决 - July 3, 2017
  • Mac微信客户端的备份恢复功能和备份文件存储 - June 12, 2017
  • Oracle 11.2.0.4 最终版本发布以来的 Oracle PSU 列表 - June 8, 2017
  • 云和恩墨 Oracle Database 12.2 体系结构图海报发布 - May 19, 2017
  • 在Mac上通过Docker部署Oracle Database 12.2版本 - May 18, 2017
  • Oracle数据库中 MISSING 文件名称的出现和处理 - May 17, 2017
  • 警惕:Oracle中删除的分区不会进入回收站(Recyclebin) - April 25, 2017
  • 如何修改 VirtualBox 虚拟机的磁盘大小 - April 25, 2017
  • 【安全警告】Oracle 12c 多租户的SQL注入高危风险防范 - April 18, 2017
  • 不以规矩不成方圆:Digital Ocean也删除了他们的数据库 - April 18, 2017


  • CopyRight © 2004 ~ 2012 eygle.com, All rights reserved.