{"id":96,"date":"2022-12-08T14:05:23","date_gmt":"2022-12-08T17:05:23","guid":{"rendered":"https:\/\/kappasoft.com.br\/wiki\/?p=96"},"modified":"2022-12-08T14:05:23","modified_gmt":"2022-12-08T17:05:23","slug":"como-obter-estatisticas-de-execucao-e-historico-para-um-sql","status":"publish","type":"post","link":"https:\/\/kappasoft.com.br\/wiki\/2022\/12\/08\/como-obter-estatisticas-de-execucao-e-historico-para-um-sql\/","title":{"rendered":"Como obter estat\u00edsticas de execu\u00e7\u00e3o e hist\u00f3rico para um SQL"},"content":{"rendered":"\n<p>Da mem\u00f3ria<\/p>\n\n\n\n<div class=\"wp-block-codemirror-blocks-code-block code-block\"><pre class=\"CodeMirror\" data-setting=\"{&quot;showPanel&quot;:true,&quot;languageLabel&quot;:&quot;language&quot;,&quot;fullScreenButton&quot;:true,&quot;copyButton&quot;:true,&quot;mode&quot;:&quot;sql&quot;,&quot;mime&quot;:&quot;text\/x-plsql&quot;,&quot;theme&quot;:&quot;material&quot;,&quot;lineNumbers&quot;:false,&quot;styleActiveLine&quot;:false,&quot;lineWrapping&quot;:false,&quot;readOnly&quot;:true,&quot;fileName&quot;:&quot;&quot;,&quot;language&quot;:&quot;PLSQL&quot;,&quot;maxHeight&quot;:&quot;400px&quot;,&quot;modeName&quot;:&quot;plsql&quot;}\">set pages 1000 lines 200\ncol first_load_time for a20\ncol last_load_time for a20\ncol outline_category for a20\ncol sql_profile for a32\nselect sql_id, child_number, plan_hash_value, first_load_time, last_load_time,\noutline_category, sql_profile, executions,\ntrunc(decode(executions, 0, 0, rows_processed\/executions)) rows_avg,\ntrunc(decode(executions, 0, 0, fetches\/executions)) fetches_avg,\ntrunc(decode(executions, 0, 0, disk_reads\/executions)) disk_reads_avg,\ntrunc(decode(executions, 0, 0, buffer_gets\/executions)) buffer_gets_avg,\ntrunc(decode(executions, 0, 0, cpu_time\/executions)) cpu_time_avg,\ntrunc(decode(executions, 0, 0, elapsed_time\/executions)) elapsed_time_avg,\ntrunc(decode(executions, 0, 0, application_wait_time\/executions)) apwait_time_avg,\ntrunc(decode(executions, 0, 0, concurrency_wait_time\/executions)) cwait_time_avg,\ntrunc(decode(executions, 0, 0, cluster_wait_time\/executions)) clwait_time_avg,\ntrunc(decode(executions, 0, 0, user_io_wait_time\/executions)) iowait_time_avg,\ntrunc(decode(executions, 0, 0, plsql_exec_time\/executions)) plsexec_time_avg,\ntrunc(decode(executions, 0, 0, java_exec_time\/executions)) javexec_time_avg\nfrom v$sql\nwhere sql_id = '&amp;sql_id'\norder by sql_id, child_number;<\/pre><\/div>\n\n\n\n<p>Do AWR<\/p>\n\n\n\n<div class=\"wp-block-codemirror-blocks-code-block code-block\"><pre class=\"CodeMirror\" data-setting=\"{&quot;showPanel&quot;:true,&quot;languageLabel&quot;:&quot;language&quot;,&quot;fullScreenButton&quot;:true,&quot;copyButton&quot;:true,&quot;mode&quot;:&quot;sql&quot;,&quot;mime&quot;:&quot;text\/x-plsql&quot;,&quot;theme&quot;:&quot;material&quot;,&quot;lineNumbers&quot;:false,&quot;styleActiveLine&quot;:false,&quot;lineWrapping&quot;:false,&quot;readOnly&quot;:true,&quot;fileName&quot;:&quot;&quot;,&quot;language&quot;:&quot;PLSQL&quot;,&quot;maxHeight&quot;:&quot;400px&quot;,&quot;modeName&quot;:&quot;plsql&quot;}\">set pages 1000 lines 200\ncol sql_profile for a32\nselect sql_id, snap_id, plan_hash_value, sql_profile, executions_total,\ntrunc(decode(executions_total, 0, 0, rows_processed_total\/executions_total)) rows_avg,\ntrunc(decode(executions_total, 0, 0, fetches_total\/executions_total)) fetches_avg,\ntrunc(decode(executions_total, 0, 0, disk_reads_total\/executions_total)) disk_reads_avg,\ntrunc(decode(executions_total, 0, 0, buffer_gets_total\/executions_total)) buffer_gets_avg,\ntrunc(decode(executions_total, 0, 0, cpu_time_total\/executions_total)) cpu_time_avg,\ntrunc(decode(executions_total, 0, 0, elapsed_time_total\/executions_total)) elapsed_time_avg,\ntrunc(decode(executions_total, 0, 0, iowait_total\/executions_total)) iowait_time_avg,\ntrunc(decode(executions_total, 0, 0, clwait_total\/executions_total)) clwait_time_avg,\ntrunc(decode(executions_total, 0, 0, apwait_total\/executions_total)) apwait_time_avg,\ntrunc(decode(executions_total, 0, 0, ccwait_total\/executions_total)) ccwait_time_avg,\ntrunc(decode(executions_total, 0, 0, plsexec_time_total\/executions_total)) plsexec_time_avg,\ntrunc(decode(executions_total, 0, 0, javexec_time_total\/executions_total)) javexec_time_avg\nfrom dba_hist_sqlstat\nwhere sql_id = '&amp;sql_id'\norder by sql_id, snap_id;<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Da mem\u00f3ria Do AWR<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,2],"tags":[],"class_list":["post-96","post","type-post","status-publish","format-standard","hentry","category-banco-de-dados","category-oracle"],"_links":{"self":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts\/96","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/comments?post=96"}],"version-history":[{"count":1,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts\/96\/revisions"}],"predecessor-version":[{"id":97,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/posts\/96\/revisions\/97"}],"wp:attachment":[{"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/media?parent=96"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/categories?post=96"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kappasoft.com.br\/wiki\/wp-json\/wp\/v2\/tags?post=96"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}