li.angshan 的博客

关注数据计算领域

日志

关于我

梁山

北漂族

文章分类

oradebug issue

2009-10-29 11:51:22| 分类： oracle 优化 | 标签： |举报 |字号大中小订阅

下载LOFTER 我的照片书 |

ORADEBUG
ORADEBUG is an undocumented debugging utility supplied with Oracle
For more general information see ORADEBUG introduction
In Oracle 9.2 commands include
HELP
SETMYPID
SETORAPID
SETOSPID
TRACEFILE_NAME
UNLIMIT
FLUSH
CLOSE_TRACE
SUSPEND
RESUME
WAKEUP
DUMPLIST
DUMP
EVENT
SESSION_EVENT
DUMPSGA
DUMPVAR
PEEK
POKE
IPC
Dumping the SGA

HELP command
The ORADEBUG HELP command lists the commands available within ORADEBUG

These vary by release and platform. Commands appearing in this help do not necessarily work for the release/platform on which the database is running

For example in Oracle 9.2.0.1 (Windows 2000) the command

ORADEBUG HELP

SETMYPID command
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process

Once a process has been selected, this will be used as the ORADEBUG process until another process is selected
The SETMYPID command selects the current process as the ORADEBUG process

For example

ORADEBUG SETMYPID
ORADEBUG SETMYPID can be used to select the current process to run systemwide commands such as dumps

Do not use ORADEBUG SETMYPID if you intend to use the ORADEBUG SUSPEND command

SETORAPID command
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process

Once a process has been selected, this will be used as the ORADEBUG process until another process is selected

The SETORAPID command selects another process using the Oracle PID as the ORADEBUG process

The syntax is

ORADEBUG SETORAPID pid

where pid is the Oracle process ID of the target process For example
ORADEBUG SETORAPID 9
The Oracle process id for a process can be found in V$PROCESS.PID

To obtain the Oracle process ID for a foreground process use

SELECT pid FROM v$process WHERE addr = ( SELECT paddr FROM v$session WHERE sid = DBMS_SUPPORT.MYSID );
Alternatively, if the DBMS_SUPPORT package is not available use
SELECT pid FROM v$process WHERE addr = ( SELECT paddr FROM v$session WHERE sid = ( SELECT sid FROM v$mystat WHERE ROWNUM = 1 ) );
To obtain the process ID for a background process e.g. SMON use
SELECT pid FROM v$process WHERE addr = ( SELECT paddr FROM v$bgprocess WHERE name = 'SMON' );
To obtain the process ID for a dispatcher process e.g. D000 use
SELECT pid FROM v$process WHERE addr = ( SELECT paddr FROM v$dispatcher WHERE name = 'D000' );
To obtain the process ID for a shared server process e.g. S000 use
SELECT pid FROM v$process WHERE addr = ( SELECT paddr FROM v$shared_server WHERE name = 'S000' );
To obtain the process ID for a job queue process e.g. job 21 use
SELECT pid FROM v$process WHERE addr = ( SELECT paddr FROM v$session WHERE sid = ( SELECT sid FROM dba_jobs_running WHERE job = 21 ) );
To obtain the process ID for a parallel execution slave e.g. P000 use
SELECT pid FROM v$px_process WHERE server_name = 'P000';
SETOSPID command
Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process

Once a process has been selected, this will be used as the ORADEBUG process until another process is selected

The SETOSPID command selects the another process using the operating system PID as the ORADEBUG process

The syntax is

ORADEBUG SETOSPID pid

where pid is the operating system process ID of the target process For example
ORADEBUG SETOSPID 34345
The operating system process ID is the PID on Unix systems and the thread number on Windows NT/2000 systems

On Unix the PID of interest may have been identified using a top or ps command

TRACEFILE_NAME command
This command prints the name of the current trace file e.g.

ORADEBUG TRACEFILE_NAME
For example

/export/home/admin/SS92003/udump/ss92003_ora_14917.trc
This command does not work on Windows 2000 (Oracle 9.2)

UNLIMIT command
In Oracle 8.1.5 and below the maximum size of the trace file is restricted by default. This means that large dumps (LIBRARY_CACHE, BUFFERS) may fail.

To remove the limitation on the size of the trace file use

ORADEBUG UNLIMIT
In Oracle 8.1.6 and above the maximum size of the trace file defaults to UNLIMITED

FLUSH command
To flush the current contents of the trace buffer to the trace file use

ORADEBUG FLUSH
CLOSE_TRACE command
To close the current trace file use

ORADEBUG CLOSE_TRACE
SUSPEND command
This command suspends the current process

First select a process using SETORAPID or SETOSPID

Do not use SETMYPID as the current ORADEBUG process will hang and cannot be resumed even from another ORADEBUG process

For example the command

ORADEBUG SUSPEND
suspends the current process
The command

ORADEBUG RESUME
resumes the current process
While the process is suspended ORADEBUG can be used to take dumps of the current process state e.g. global area, heap, subheaps etc.

This example demonstrates how to take a heap dump during a large (sorting) query

This example requires two sessions, session 1 logged on SYS AS SYSDBA and session 2 which executes the query. In session 2 identify the PID using

SELECT pid FROM v$process WHERE addr IN ( SELECT paddr FROM v$session WHERE sid = dbms_support.mysid );
In this example the PID was 12

In session 1 set the Oracle PID using

ORADEBUG SETORAPID 12
In session 2 start the query

SELECT ... FROM t1 ORDER BY ....
In session 1 suspend session 2

ORADEBUG SUSPEND
The query in session 2 will be suspended

In session 1 run the heap dump

ORADEBUG DUMP HEAPDUMP 1
The heapdump will show the memory structures allocated for the sort. At this point further dumps e.g. subheap dumps can be taken.

In session 1 resume session 2

ORADEBUG RESUME
The query in session 2 will resume execution

RESUME command
This command resumes the current process

First select a process using SETORAPID or SETOSPID

Do not use SETMYPID as the current ORADEBUG process will hang and cannot be resumed even from another ORADEBUG process

For example the command

ORADEBUG SUSPEND
suspends the current process
The command

ORADEBUG RESUME
resumes the current process
While the process is suspended ORADEBUG can be used to take dumps of the current process state e.g. global area, heap, subheaps etc.

See SUSPEND for an example of use of the SUSPEND and RESUME commands

WAKEUP command
To wake up a process use

ORADEBUG WAKEUP pid

For example to wake up SMON, first obtain the PID using

SELECT pid FROM v$process WHERE addr = ( SELECT paddr FROM v$bgprocess WHERE name = 'SMON' );
If the PID is 6 then send a wakeup call using

ORADEBUG WAKEUP 6
DUMPLIST command
To list the dumps available in ORADEBUG use

ORADEBUG DUMPLIST pid

For example in Oracle 9.2 (Windows 2000) this command returns the following

Dump Name
EVENTS
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
HANGANALYZE
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
REFRESH_OS_STATS
CROSSIC
CONTEXTAREA
HEAPDUMP
HEAPDUMP_ADDR
POKE_ADDRESS
POKE_LENGTH
POKE_VALUE
POKE_VALUE0
GLOBAL_AREA
MEMORY_LOG
REALFREEDUMP
ERRORSTACK
HANGANALYZE_PROC
TEST_STACK_DUMP
BG_MESSAGES
ENQUEUES
SIMULATE_EOV
KSFQP_LIMIT
KSKDUMPTRACE
DBSCHEDULER
GRANULELIST
GRANULELISTCHK
SCOREBOARD
GES_STATE
ADJUST_SCN
NEXT_SCN_WRAP
CONTROLF
FULL_DUMPS
BUFFERS
RECOVERY
SET_TSN_P1
BUFFER
PIN_BUFFER
BC_SANITY_CHECK
FLUSH_CACHE
LOGHIST
ARCHIVE_ERROR
REDOHDR
LOGERROR
OPEN_FILES
DATA_ERR_ON
DATA_ERR_OFF
BLK0_FMTCHG
TR_SET_BLOCK
TR_SET_ALL_BLOCKS
TR_SET_SIDE
TR_CRASH_AFTER_WRITE
TR_READ_ONE_SIDE
TR_CORRUPT_ONE_SIDE
TR_RESET_NORMAL
TEST_DB_ROBUSTNESS
LOCKS
GC_ELEMENTS
FILE_HDRS
KRB_CORRUPT_INTERVAL
KRB_CORRUPT_SIZE
KRB_PIECE_FAIL
KRB_OPTIONS
KRB_SIMULATE_NODE_AFFINITY
KRB_TRACE
KRB_BSET_DAYS
DROP_SEGMENTS
TREEDUMP
LONGF_CREATE
ROW_CACHE
LIBRARY_CACHE
SHARED_SERVER_STATE
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE
WORKAREATAB_DUMP
OBJECT_CACHE
SAVEPOINTS
OLAP_DUMP

DUMP command
To perform a dump use

ORADEBUG DUMP dumpname level

For example for a level 4 dump of the library cache use

ORADEBUG SETMYPID ORADEBUG DUMP LIBRARY_CACHE 4

评论这张

转发至微博

阅读(228)| 评论(0)

历史上的今天

this.p={  m:2,
              b:2,
              loftPermalink:'',
              id:'fks_083066084083084069081086081095085085080064087084084075093',
              blogTitle:'oradebug issue',
              blogAbstract:'<font style=\"font-size: 12px;\" size=\"4\"\>ORADEBUG <br\> ORADEBUG is an undocumented debugging utility supplied with Oracle <br\> For more general information see ORADEBUG introduction <br\> In Oracle 9.2 commands include <br\> HELP <br\> SETMYPID <br\> SETORAPID <br\> SETOSPID <br\> TRACEFILE_NAME <br\> UNLIMIT <br\> FLUSH <br\> CLOSE_TRACE <br\> SUSPEND <br\> RESUME <br\> WAKEUP <br\> DUMPLIST <br\> DUMP <br\> EVENT </font\>',
              blogTag:'',
              blogUrl:'blog/static/1313322892009929115122400',
              isPublished:1,
              istop:false,
              type:0,
              modifyTime:1312525919487,
              publishTime:1256788282400,
              permalink:'blog/static/1313322892009929115122400',
              commentCount:0,
              mainCommentCount:0,
              recommendCount:0,
              bsrk:-100,
              publisherId:0,
              recomBlogHome:false,
              currentRecomBlog:false,
              attachmentsFileIds:[],
              vote:{},
              groupInfo:{},
              friendstatus:'none',
              followstatus:'unFollow',
              pubSucc:'',
              visitorProvince:'',
              visitorCity:'',
              visitorNewUser:false,
              postAddInfo:{},
              mset:'000',
              mcon:'',
              srk:-100,
              remindgoodnightblog:false,
              isBlackVisitor:false,
              isShowYodaoAd:false,
              hostIntro:'北漂族',
              hmcon:'0',
              selfRecomBlogCount:'0',
              lofter_single:'<iframe width="140" height="560" style="overflow:hidden;" src="http://www.lofter.com/mailEntry.do?blogad=1&blog" frameBorder="0"></iframe>'
            }

{list a as x}
    {if !!x}
    <div class="iblock nbw-fce nbw-f40">
      <a class="fc03 noul" target="_blank" hidefocus="true" href="http://blog.163.com/${x.visitorName}/">
      {if x.visitorName==visitor.userName}
      <img alt="${x.visitorNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.visitorName)}&r=${visitor.imageUpdateTime}"/>
      {else}
      <img alt="${x.visitorNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.visitorName)}"/>
      {/if}
      </a>
      <div class="cwd vname thide">
        {if x.moveFrom=='wap'}
          <a class="noul pnt" target="_blank" href="http://blog.163.com/services/wapblog.html?frompersonalbloghome"><span title="来自网易手机博客" class="iblock wapIcon"> </span></a>
        {elseif x.moveFrom=='iphone'}
          <a class="noul pnt" target="_blank"><span title="来自iPhone客户端" class="iblock iphoneIcon"> </span></a>
        {elseif x.moveFrom=='android'}
          <a class="noul pnt" target="_blank"><span title="来自Android客户端" class="iblock androidIcon"> </span></a>
        {elseif x.moveFrom=='mobile'}
          <a class="noul pnt" target="_blank" href="http://blog.163.com/services/emsblog.html?frompersonalbloghome"><span title="来自网易短信写博" class="iblock wapIcon"> </span></a>
        {/if}
        <a class="fc03 m2a"  target="_blank" hidefocus="true" href="http://blog.163.com/${x.visitorName}/">
          ${fn(x.visitorNickname,8)|escape}
        </a>
      </div>
    </div>
    {/if}
    {/list}

<#--最新日志，群博日志--> <#--推荐日志-->

<p class="fc06">推荐过这篇日志的人：</p>
    <div>
      {list a as x}
      {if !!x}
      <div class="iblock nbw-fce nbw-f40">
        <a class="fc03 noul" target="_blank" hidefocus="true" href="http://blog.163.com/${x.recommenderName}/">
        <img alt="${x.recommenderNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.recommenderName)}"/>
        </a>
        <div class="cwd thide">
          <a class="fc03 m2a" target="_blank" hidefocus="true" href="http://blog.163.com/${x.recommenderName}/">
            ${fn(x.recommenderNickname,6)|escape}
          </a>
        </div>
      </div>
      {/if}
      {/list}
    </div>
    {if !!b&&b.length>0}
    <p  class="fc06">他们还推荐了：</p>
    <ul>
    {list b as y}
      {if !!y}
        <li class="rrb"><span class="iblock">·</span><a class="fc03 m2a" target="_blank" href="http://blog.163.com/${y.recommendBlogPermalink}/?from=blog/static/1313322892009929115122400">${y.recommendBlogTitle|escape}</a></li>
      {/if}
    {/list}
    </ul>
    {/if}

<#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇，下一篇--> <#-- 热度 -->

{list a as x}
    {if !!x}
    <div class="hotItem iblock nbw-fce nbw-f40">
      <a class="fc03 noul" target="_blank" hidefocus="true" href="http://blog.163.com/${x.publisherUsername}/">
      {if x.publisherUsername==visitor.userName}
      <img alt="${x.publisherNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.publisherUsername)}&r=${visitor.imageUpdateTime}"/>
      {else}
      <img alt="${x.publisherNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.publisherUsername)}"/>
      {/if}
      </a>
      <div class="cwd vname thide">
        <a class="fc03 m2a"  target="_blank" hidefocus="true" href="http://blog.163.com/${x.publisherUsername}/">
          ${fn(x.publisherNickname,8)|escape}
        </a>
      </div>
      <a class="f-myLikeIcons hottype {if x.type==1} js-liketype{elseif x.type==2} js-reblogtype{elseif x.type==3} js-sharetype{else}{/if}" target="_blank" hidefocus="true" href="http://blog.163.com/${x.publisherUsername}/"> </a>
    </div>
    {/if}
    {/list}

<#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->

页脚

我的照片书 - 手机博客 - 下载LOFTER APP - 订阅此博客

li.angshan 的博客

导航

日志

oradebug issue

历史上的今天

最近读者

热度

评论

页脚