{"id":8916,"date":"2023-01-19T18:25:35","date_gmt":"2023-01-19T21:25:35","guid":{"rendered":"https:\/\/swiv.com.br\/?p=8916"},"modified":"2023-01-19T18:25:35","modified_gmt":"2023-01-19T21:25:35","slug":"how-to-identify-cpu-intensive-processes-on-oracle-linux-and-database","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2023\/01\/19\/how-to-identify-cpu-intensive-processes-on-oracle-linux-and-database\/","title":{"rendered":"How to identify CPU Intensive Processes on Oracle Linux and Database"},"content":{"rendered":"\n

Artigo simples de apoio para aquelas ocasi\u00f5es onde identifcamos um alto consumo de CPU em nosso ambiente, e precisamos partir para a pr\u00f3xima fase da investiga\u00e7\u00e3o: poss\u00edveis processos ofensores, tanto no S.O quanto no database.<\/p>\n\n\n\n

O modo mais simples e direto \u00e9 utilizar o “top”, que por default j\u00e1 ordena a lista de processos por consumo de CPU, conforme exemplo abaixo:<\/p>\n\n\n

\n[oracle@oel7 ~]$ top\ntop - 19:08:51 up 10 min,  2 users,  load average: 1.24, 0.95, 0.58\nTasks: 354 total,   7 running, 261 sleeping,   0 stopped,   0 zombie\n%Cpu(s): 66.9 us,  0.7 sy,  0.0 ni, 32.3 id,  0.0 wa,  0.0 hi,  0.2 si,  0.0 st\nKiB Mem : 16150724 total, 10330188 free,  1223556 used,  4596980 buff\/cache\nKiB Swap:  1261564 total,  1261564 free,        0 used. 11574284 avail Mem\n\n  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND\n 7527 root      20   0    7324    100      0 R 100.0  0.0   0:12.13 stress\n 7525 root      20   0    7324    100      0 R  99.7  0.0   0:12.16 stress\n 7528 root      20   0    7324    100      0 R  98.3  0.0   0:11.98 stress\n 7526 root      20   0    7324    100      0 R  98.0  0.0   0:12.10 stress\n 3948 grid      -2   0 1554740  64444  61216 S   1.7  0.4   0:06.41 asm_vktm_+asm\n 4092 oracle    -2   0 2983028  61048  57784 S   1.3  0.4   0:06.21 ora_vktm_cortex\n 7576 oracle    20   0  163736   4688   3768 R   1.0  0.0   0:00.12 top\n 3600 grid      20   0 2143224 132860  89712 S   0.7  0.8   0:07.65 oraagent.bin\n 3074 grid      20   0 2574648 112892  68044 S   0.3  0.7   0:16.99 ohasd.bin\n<\/pre><\/div>\n\n\n
Tamb\u00e9m \u00e9 poss\u00edvel identicar uma lista de processos a partir do comando ps abaixo, onde a primeira coluna aponta a % de consumo, a segunda o PID do processo (que nos pode ser insumo para outras an\u00e1lises):<\/p>\n\n\n
\nps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head\n<\/pre><\/div>\n\n
\n[oracle@oel7 ~]$ ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head\n 103  9401 root     pts\/0    stress --cpu 4 --timeout 60s\n 103  9399 root     pts\/0    stress --cpu 4 --timeout 60s\n 103  9398 root     pts\/0    stress --cpu 4 --timeout 60s\n 101  9400 root     pts\/0    stress --cpu 4 --timeout 60s\n 2.3  3074 grid     ?        \/grid\/19.3.0\/product\/bin\/ohasd.bin reboot\n 1.2  3600 grid     ?        \/grid\/19.3.0\/product\/bin\/oraagent.bin\n 1.1  4092 oracle   ?        ora_vktm_cortex\n 1.1  3948 grid     ?        asm_vktm_+ASM\n 1.1  3173 gdm      ?        \/usr\/bin\/gnome-shell\n 0.9  2586 root     tty1     \/usr\/bin\/X :0 -background none -noreset -audit 4 -verbose -auth \/run\/gdm\/auth-for-gdm-XDgYhG\/database -seat seat0 -nolisten tcp vt1\n[oracle@oel7 ~]$\n<\/pre><\/div>\n\n\n
Caso o processo seja proveniente do banco de dados Oracle, atrav\u00e9s do PID e da consulta abaixo \u00e9 poss\u00edvel termos mais detalhes do mesmo. Nota: a query foi retirada da obra “Linux and Solaris Recipes for Oracle DBAs”, de Darl Kuln:<\/p>\n\n\n
\nSET LINES 200 PAGES 0 HEAD OFF LONG 100000\nCOL dummy_value NOPRINT\nSELECT 'dummy_value' dummy_value,\n'USERNAME : ' || s.username || CHR(10) ||\n'SCHEMA : ' || s.schemaname || CHR(10) ||\n'OSUSER : ' || s.osuser || CHR(10) ||\n'MODULE : ' || s.program || CHR(10) ||\n'ACTION : ' || s.schemaname || CHR(10) ||\n'CLIENT INFO : ' || s.osuser || CHR(10) ||\n'PROGRAM : ' || s.program || CHR(10) ||\n'SPID : ' || p.spid || CHR(10) ||\n'SID : ' || s.sid || CHR(10) ||\n'SERIAL# : ' || s.serial# || CHR(10) ||\n'KILL STRING : ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||\n'MACHINE : ' || s.machine || CHR(10) ||\n'TYPE : ' || s.type || CHR(10) ||\n'TERMINAL : ' || s.terminal || CHR(10) ||\n'CPU : ' || q.cpu_time\/1000000 || CHR(10) ||\n'ELAPSED_TIME: ' || q.elapsed_time\/1000000 || CHR(10) ||\n'BUFFER_GETS : ' || q.buffer_gets || CHR(10) ||\n'SQL_ID : ' || q.sql_id || CHR(10) ||\n'CHILD_NUM : ' || q.child_number || CHR(10) ||\n'START_TIME : ' || TO_CHAR(s.sql_exec_start,'dd-mon-yy hh24:mi') || CHR(10) ||\n'STATUS : ' || s.status || CHR(10) ||\n'SQL_TEXT : ' || q.sql_fulltext\nFROM v$session s\nJOIN v$process p ON (s.paddr = p.addr)\nLEFT OUTER JOIN v$sql q ON (s.sql_id = q.sql_id)\nWHERE s.username IS NOT NULL -- eliminates background procs\nAND NVL(q.sql_text,'x') NOT LIKE '%dummy_value%' -- eliminates this query from output\nAND p.spid = '&amp;PID_FROM_OS'\nORDER BY q.cpu_time;\n<\/pre><\/div>\n\n
\n[oracle@oel7 admin]$ sqlplus \/ as sysdba\n\nSQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 17 19:39:59 2023\nVersion 19.3.0.0.0\n\nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n\n\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n\nSQL> SET LINES 200 PAGES 0 HEAD OFF LONG 100000\nCOL dummy_value NOPRINT\nSELECT 'dummy_value' dummy_value,\n'USERNAME : ' || s.username || CHR(10) ||\n'SCHEMA : ' || s.schemaname || CHR(10) ||\nSQL> 'OSUSER : ' || s.osuser || CHR(10) ||\n'MODULE : ' || s.program || CHR(10) ||\n'ACTION : ' || s.schemaname || CHR(10) ||\n'CLIENT INFO : ' || s.osuser || CHR(10) ||\nSQL>   2    3    4    5  'PROGRAM : ' || s.program || CHR(10) ||\n  6    7    8    9  'SPID : ' || p.spid || CHR(10) ||\n'SID : ' || s.sid || CHR(10) ||\n 10   11  'SERIAL# : ' || s.serial# || CHR(10) ||\n 12  'KILL STRING : ' || '''' || s.sid || ',' || s.serial# || '''' || CHR(10) ||\n 13  'MACHINE : ' || s.machine || CHR(10) ||\n 14  'TYPE : ' || s.type || CHR(10) ||\n'TERMINAL : ' || s.terminal || CHR(10) ||\n 15   16  'CPU : ' || q.cpu_time\/1000000 || CHR(10) ||\n 17  'ELAPSED_TIME: ' || q.elapsed_time\/1000000 || CHR(10) ||\n'BUFFER_GETS : ' || q.buffer_gets || CHR(10) ||\n 18   19  'SQL_ID : ' || q.sql_id || CHR(10) ||\n 20  'CHILD_NUM : ' || q.child_number || CHR(10) ||\n'START_TIME : ' || TO_CHAR(s.sql_exec_start,'dd-mon-yy hh24:mi') || CHR(10) ||\n 21   22  'STATUS : ' || s.status || CHR(10) ||\n'SQL_TEXT : ' || q.sql_fulltext\n 23   24  FROM v$session s\n 25  JOIN v$process p ON (s.paddr = p.addr)\nLEFT OUTER JOIN v$sql q ON (s.sql_id = q.sql_id)\n 26   27  WHERE s.username IS NOT NULL -- eliminates background procs\n 28  AND NVL(q.sql_text,'x') NOT LIKE '%dummy_value%' -- eliminates this query from output\nAND p.spid = '&amp;PID_FROM_OS'\n 29   30  ORDER BY q.cpu_time;\nEnter value for pid_from_os: 26405\nold  29: AND p.spid = '&amp;PID_FROM_OS'\nnew  29: AND p.spid = '26405'\nUSERNAME : SYSTEM\nSCHEMA : SYSTEM\nOSUSER : SWIV\nMODULE : sqlplus.exe\nACTION : SYSTEM\nCLIENT INFO : SWIV\nPROGRAM : sqlplus.exe\nSPID : 26405\nSID : 93\nSERIAL# : 22672\nKILL STRING : '93,22672'\nMACHINE : WORKGROUP\\xxx\nTYPE : USER\nTERMINAL : xxx\nCPU : .237271\nELAPSED_TIME: .386385\nBUFFER_GETS : 5233\nSQL_ID : 7xr6jha03c1wx\nCHILD_NUM : 0\nSTART_TIME : 17-jan-23 19:38\nSTATUS : INACTIVE\nSQL_TEXT : SELECT * FROM DBA_OBJECTS\n\n\nSQL>\n<\/pre><\/div>\n\n\n
A partir daqui podemos avan\u00e7ar na investiga\u00e7\u00e3o dentro do banco de dados, por exemplo validando o plano de execu\u00e7\u00e3o:<\/p>\n\n\n
\nSELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&amp;sql_id',&amp;child_num));\n<\/pre><\/div>\n\n
\nSQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&amp;sql_id',&amp;child_num));\nEnter value for sql_id: 7xr6jha03c1wx\nEnter value for child_num: 0\nold   1: SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&amp;sql_id',&amp;child_num))\nnew   1: SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('7xr6jha03c1wx',0))\nSQL_ID  7xr6jha03c1wx, child number 0\n-------------------------------------\nSELECT * FROM DBA_OBJECTS\n\nPlan hash value: 2114560210\n\n------------------------------------------------------------------------------------------------------------\n| Id  | Operation                              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |\n------------------------------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                       |                   |       |       |   393 (100)|          |\n|   1 |  VIEW                                  | DBA_OBJECTS       | 66791 |    30M|   393   (3)| 00:00:01 |\n|   2 |   UNION-ALL                            |                   |       |       |            |          |\n|   3 |    TABLE ACCESS BY INDEX ROWID         | SUM$              |     1 |    26 |     0   (0)|          |\n|*  4 |     INDEX UNIQUE SCAN                  | I_SUM$_1          |     1 |       |     0   (0)|          |\n|*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$  |     1 |     6 |     2   (0)| 00:00:01 |\n|*  6 |     INDEX RANGE SCAN                   | I_USER_EDITIONING |     2 |       |     1   (0)| 00:00:01 |\n|   7 |     TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$              |     1 |    40 |     3   (0)| 00:00:01 |\n|*  8 |      INDEX RANGE SCAN                  | I_OBJ1            |     1 |       |     2   (0)| 00:00:01 |\n|*  9 |    TABLE ACCESS BY INDEX ROWID         | SUM$              |     1 |    26 |     0   (0)|          |\n|* 10 |     INDEX UNIQUE SCAN                  | I_SUM$_1          |     1 |       |     0   (0)|          |\n|* 11 |    FILTER                              |                   |       |       |            |          |\n|* 12 |     HASH JOIN                          |                   | 72419 |    10M|   332   (1)| 00:00:01 |\n|  13 |      TABLE ACCESS FULL                 | USER$             |   130 |  2340 |     4   (0)| 00:00:01 |\n|* 14 |      HASH JOIN                         |                   | 72419 |  9547K|   328   (1)| 00:00:01 |\n|  15 |       INDEX FULL SCAN                  | I_USER2           |   130 |  3250 |     1   (0)| 00:00:01 |\n|* 16 |       TABLE ACCESS FULL                | OBJ$              | 72419 |  7779K|   327   (1)| 00:00:01 |\n|  17 |     NESTED LOOPS                       |                   |     1 |    32 |     4   (0)| 00:00:01 |\n|  18 |      NESTED LOOPS                      |                   |     1 |    23 |     3   (0)| 00:00:01 |\n|  19 |       TABLE ACCESS BY INDEX ROWID      | IND$              |     1 |    10 |     2   (0)| 00:00:01 |\n|* 20 |        INDEX UNIQUE SCAN               | I_IND1            |     1 |       |     1   (0)| 00:00:01 |\n|* 21 |       TABLE ACCESS CLUSTER             | TAB$              |     1 |    13 |     1   (0)| 00:00:01 |\n|* 22 |      INDEX RANGE SCAN                  | I_OBJ1            |     1 |     9 |     1   (0)| 00:00:01 |\n|* 23 |     TABLE ACCESS CLUSTER               | TAB$              |     1 |    13 |     2   (0)| 00:00:01 |\n|* 24 |      INDEX UNIQUE SCAN                 | I_OBJ#            |     1 |       |     1   (0)| 00:00:01 |\n|* 25 |     TABLE ACCESS BY INDEX ROWID        | SEQ$              |     1 |     8 |     1   (0)| 00:00:01 |\n|* 26 |      INDEX UNIQUE SCAN                 | I_SEQ1            |     1 |       |     0   (0)|          |\n|* 27 |     TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$  |     1 |     6 |     2   (0)| 00:00:01 |\n|* 28 |      INDEX RANGE SCAN                  | I_USER_EDITIONING |     2 |       |     1   (0)| 00:00:01 |\n|* 29 |     TABLE ACCESS BY INDEX ROWID BATCHED| USER_EDITIONING$  |     1 |     6 |     2   (0)| 00:00:01 |\n|* 30 |      INDEX RANGE SCAN                  | I_USER_EDITIONING |     2 |       |     1   (0)| 00:00:01 |\n|  31 |     NESTED LOOPS SEMI                  |                   |     1 |    29 |     2   (0)| 00:00:01 |\n|* 32 |      INDEX SKIP SCAN                   | I_USER2           |     1 |    20 |     1   (0)| 00:00:01 |\n|* 33 |      INDEX RANGE SCAN                  | I_OBJ4            |     1 |     9 |     1   (0)| 00:00:01 |\n|  34 |    NESTED LOOPS                        |                   |     1 |    36 |     3   (0)| 00:00:01 |\n|  35 |     TABLE ACCESS FULL                  | LINK$             |     1 |    18 |     2   (0)| 00:00:01 |\n|  36 |     TABLE ACCESS CLUSTER               | USER$             |     1 |    18 |     1   (0)| 00:00:01 |\n|* 37 |      INDEX UNIQUE SCAN                 | I_USER#           |     1 |       |     0   (0)|          |\n------------------------------------------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n\n   4 - access("S"."OBJ#"=:B1)\n   5 - filter("UE"."TYPE#"=:B1)\n   6 - access("UE"."USER#"=:B1)\n   8 - access("EO"."OBJ#"=:B1)\n   9 - filter(BITAND("S"."XPFLAGS",34368126976)=0)\n  10 - access("S"."OBJ#"=:B1)\n  11 - filter(((("O"."TYPE#"&lt;>1 AND "O"."TYPE#"&lt;>2 AND "O"."TYPE#"&lt;>6) OR ("O"."TYPE#"=1 AND  IS\n              NULL) OR ("O"."TYPE#"=2 AND =1) OR ("O"."TYPE#"=6 AND =1)) AND (BITAND("U"."SPARE1",16)=0 OR\n              BITAND("O"."FLAGS",1048576)=1048576 OR ("O"."TYPE#"&lt;>88 AND  IS NULL) OR ( IS NOT NULL AND\n              ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"&lt;>2) OR ("U"."TYPE#"=2\n              AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))))\n  12 - access("O"."SPARE3"="U"."USER#")\n  14 - access("O"."OWNER#"="U"."USER#")\n  16 - filter(("O"."NAME"&lt;>'_NEXT_OBJECT' AND "O"."NAME"&lt;>'_default_auditing_options_' AND\n              "O"."TYPE#"&lt;>10 AND "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0))\n  20 - access("I"."OBJ#"=:B1)\n  21 - filter(("I"."BO#"="T"."OBJ#" AND BITAND("T"."PROPERTY",36893488147419103232)=3689348814741910\n              3232))\n  22 - access("IO"."OBJ#"="I"."BO#" AND "IO"."TYPE#"=2)\n       filter("IO"."TYPE#"=2)\n  23 - filter(BITAND("T"."PROPERTY",36893488147419103232)=0)\n  24 - access("T"."OBJ#"=:B1)\n  25 - filter((BITAND("S"."FLAGS",1024)=0 OR "S"."FLAGS" IS NULL))\n  26 - access("S"."OBJ#"=:B1)\n  27 - filter("TYPE#"=:B1)\n  28 - access("UE"."USER#"=:B1)\n  29 - filter("UE"."TYPE#"=:B1)\n  30 - access("UE"."USER#"=:B1)\n  32 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')\n              ))\n       filter(("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'\n              ))))\n  33 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")\n  37 - access("L"."OWNER#"="U"."USER#")\n\nNote\n-----\n   - this is an adaptive plan\n\n\n91 rows selected.\n\nSQL>\n<\/pre><\/div>\n\n\n
Validando por exemplo os waitings da query:<\/p>\n\n\n
\nSELECT sid,\nDECODE(state, 'WAITING','Waiting', 'Working') state,\nDECODE(state, 'WAITING', 'So far '||seconds_in_wait,\n'Last waited '|| wait_time\/100)|| ' seconds for '||event\nFROM v$session\nWHERE sid = '&amp;&amp;session_id'\nAND serial# = '&amp;&amp;serial';\n<\/pre><\/div>\n\n
\nSQL> SELECT sid,\nDECODE(state, 'WAITING','Waiting', 'Working') state,\nDECODE(state, 'WAITING', 'So far '||seconds_in_wait,\n'Last waited '|| wait_time\/100)|| ' seconds for '||event\nFROM v$session\nWHERE sid = '&amp;&amp;session_id'\nAND serial# = '&amp;&amp;serial';  2    3    4    5    6    7\nEnter value for session_id: 93\nold   6: WHERE sid = '&amp;&amp;session_id'\nnew   6: WHERE sid = '93'\nEnter value for serial: 22672\nold   7: AND serial# = '&amp;&amp;serial'\nnew   7: AND serial# = '22672'\n        93 Waiting So far 0 seconds for SQL*Net message from client\n\nSQL>\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"
Artigo simples de apoio para aquelas ocasi\u00f5es onde identifcamos um alto consumo de CPU em nosso ambiente, e precisamos partir para a pr\u00f3xima fase da investiga\u00e7\u00e3o: poss\u00edveis processos ofensores, tanto no S.O quanto no database. O modo mais simples e direto \u00e9 utilizar o “top”, que por default j\u00e1 ordena a lista de processos por […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,6],"tags":[],"class_list":["post-8916","post","type-post","status-publish","format-standard","hentry","category-administration","category-linux"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/8916","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=8916"}],"version-history":[{"count":0,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/8916\/revisions"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=8916"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=8916"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=8916"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}