{"id":3739,"date":"2021-03-17T17:46:57","date_gmt":"2021-03-17T17:46:57","guid":{"rendered":"https:\/\/nenadnoveljic.com\/blog\/?p=3739"},"modified":"2021-03-17T17:47:00","modified_gmt":"2021-03-17T17:47:00","slug":"three-ways-to-suspend-an-oracle-process","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/","title":{"rendered":"Three Ways to Suspend an Oracle Process"},"content":{"rendered":"

While analyzing complex problems and researching internals in Oracle database, I’ve often had to observe process interaction in slow motion. This requires temporarily suspending a process until another process finishes a relevant chunk of work, and then resume the suspended process.<\/p>\n

For example, if you suspend log writer for a while, user process doing commit will be hanging during this time. You can see that log file sync wait event time increased during this time.<\/p>\n

@ashtop.sql<\/a> event2 \"event='log file sync'\" &1min\n\n    Total                                                                                                      Distinct\n  Seconds     AAS %This   EVENT2                                     FIRST_SEEN          LAST_SEEN           Execs Seen\n--------- ------- ------- ------------------------------------------ ------------------- ------------------- ----------\n       76     1.3  100% | log file sync                              2021-03-17 17:54:08 2021-03-17 17:54:46          1<\/code><\/pre>\n
Of course, this is something you should do in lab environments only.<\/p>\n
There are three ways to suspend an Oracle process. First two, sending signals and debugger, are generic – you can use them with any process. The third method uses the sqlplus oradebug command. It’s main advantage is that it doesn’t require shell access to the database server. You must, though, connect as sysdba.<\/p>\n
Sending signals<\/h1>\n
With the Unix kill command you can send STOP and CONT signals to a process:<\/p>\n
kill -STOP PID\nkill -CONT PID<\/code><\/pre>\n
On Solaris you can alternatively use prun and pstop commands:<\/p>\n
pstop PID\nprun PID<\/code><\/pre>\n
Debugger<\/h1>\n
gdb oracle\nattach PID<\/code><\/pre>\n
The process will suspend after you attach to it with debugger:<\/p>\n
0x00007fffa617741a in _portfs () from \/lib\/64\/libc.so.1\n(gdb)<\/code><\/pre>\n
In prompt you can then type the command to take the call stack or any other internal information of interest, for example:<\/p>\n
(gdb) bt\n#0  0x00007fffa617741a in _portfs () from \/lib\/64\/libc.so.1\n#1  0x00007fffa608485a in port_get () from \/lib\/64\/libc.so.1\n#2  0x00000000060e19bd in sskgpwwait ()\n#3  0x00000000060e15a3 in skgpwwait ()\n#4  0x00000000062e35c4 in ksliwat ()\n#5  0x00000000062e27af in kslwaitctx ()\n#6  0x0000000007210d27 in ksarcv ()\n#7  0x000000000720fb29 in ksbabs ()\n#8  0x000000000720dd21 in ksbrdp ()\n#9  0x0000000006e14599 in opirip ()\n#10 0x000000000602cab0 in opidrv ()\n#11 0x000000000602c8c7 in sou2o ()\n#12 0x000000000602c694 in opimai_real ()\n#13 0x000000000602c1eb in ssthrdmain ()\n#14 0x000000000602bf59 in main ()<\/code><\/pre>\n
Once you’re finished with collecting data, you can resume the execution:<\/p>\n
continue<\/code><\/pre>\n
SQLPlus<\/h1>\n
After connecting as sysdba, first you specify the target process:<\/p>\n
@bg2.sql<\/a> lgwr\n\nNAME  DESCRIPTION                                                             SID       OPID SPID                     PADDR            SADDR\n----- ---------------------------------------------------------------- ---------- ---------- ------------------------ ---------------- ----------------\nLGWR  Redo etc.                                                               129         32 26695                    00000000A73D2C28 00000000A7706660<\/code><\/pre>\n
oradebug setospid 26695\nOracle pid: 32, Unix process pid: 26695, image: oracle@server (LGWR)<\/code><\/pre>\n
Then you can use suspend and resume commands.<\/p>\n
oradebug suspend\noredebug resume<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
While analyzing complex problems and researching internals in Oracle database, I’ve often had to observe process interaction in slow motion. This requires temporarily suspending a process until another process finishes a relevant chunk of work, and then resume the suspended… Continue Reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[27,5],"tags":[],"class_list":["post-3739","post","type-post","status-publish","format-standard","hentry","category-gdb","category-oracle"],"yoast_head":"\nThree Ways to Suspend an Oracle Process - All-round Database Topics<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Three Ways to Suspend an Oracle Process - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"While analyzing complex problems and researching internals in Oracle database, I’ve often had to observe process interaction in slow motion. This requires temporarily suspending a process until another process finishes a relevant chunk of work, and then resume the suspended… Continue Reading →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2021-03-17T17:46:57+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-17T17:47:00+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/three-ways-to-suspend-an-oracle-process\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/three-ways-to-suspend-an-oracle-process\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Three Ways to Suspend an Oracle Process\",\"datePublished\":\"2021-03-17T17:46:57+00:00\",\"dateModified\":\"2021-03-17T17:47:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/three-ways-to-suspend-an-oracle-process\\\/\"},\"wordCount\":242,\"commentCount\":0,\"articleSection\":[\"gdb\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/three-ways-to-suspend-an-oracle-process\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/three-ways-to-suspend-an-oracle-process\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/three-ways-to-suspend-an-oracle-process\\\/\",\"name\":\"Three Ways to Suspend an Oracle Process - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2021-03-17T17:46:57+00:00\",\"dateModified\":\"2021-03-17T17:47:00+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/three-ways-to-suspend-an-oracle-process\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/three-ways-to-suspend-an-oracle-process\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/three-ways-to-suspend-an-oracle-process\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Three Ways to Suspend an Oracle Process\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Three Ways to Suspend an Oracle Process - All-round Database Topics","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/","og_locale":"en_US","og_type":"article","og_title":"Three Ways to Suspend an Oracle Process - All-round Database Topics","og_description":"While analyzing complex problems and researching internals in Oracle database, I’ve often had to observe process interaction in slow motion. This requires temporarily suspending a process until another process finishes a relevant chunk of work, and then resume the suspended… Continue Reading →","og_url":"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/","og_site_name":"All-round Database Topics","article_published_time":"2021-03-17T17:46:57+00:00","article_modified_time":"2021-03-17T17:47:00+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Three Ways to Suspend an Oracle Process","datePublished":"2021-03-17T17:46:57+00:00","dateModified":"2021-03-17T17:47:00+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/"},"wordCount":242,"commentCount":0,"articleSection":["gdb","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/","url":"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/","name":"Three Ways to Suspend an Oracle Process - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2021-03-17T17:46:57+00:00","dateModified":"2021-03-17T17:47:00+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/three-ways-to-suspend-an-oracle-process\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Three Ways to Suspend an Oracle Process"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3739","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=3739"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3739\/revisions"}],"predecessor-version":[{"id":3743,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/3739\/revisions\/3743"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=3739"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=3739"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=3739"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}