{"id":295,"date":"2022-03-25T13:43:43","date_gmt":"2022-03-25T05:43:43","guid":{"rendered":"https:\/\/www.db2go.net\/?p=295"},"modified":"2022-03-25T13:44:40","modified_gmt":"2022-03-25T05:44:40","slug":"%e8%bf%99%e4%b8%a4%e5%a4%a9%e5%a4%a7%e6%95%b0%e6%8d%ae%e9%87%8f%e7%9a%84%e4%b8%80%e4%ba%9b%e6%80%bb%e7%bb%93","status":"publish","type":"post","link":"https:\/\/www.db2go.net\/?p=295","title":{"rendered":"\u8fd9\u4e24\u5929\u5927\u6570\u636e\u91cf\u7684\u4e00\u4e9b\u603b\u7ed3"},"content":{"rendered":"
    \n
  1. \u770bawr\u6765\u5206\u6790\u662f\u4ec0\u4e48\u95ee\u9898\u3002
    \n\"\"<\/a>
    \n    \"\"<\/a>
    \n    \"\"<\/a><\/li>\n<\/ol>\n

    \u521d\u6b65\u5206\u6790\u662f\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\u4e0d\u5bf9\uff0c\u9020\u6210\u4e86insert\u8fd9\u4e2a\u8868\u7684\u65f6\u5019\u7684\u6267\u884c\u8ba1\u5212\u52a0\u4e0a\u4e86\/*+ RESTRICT_ALL_REF_CONS *\/<\/code>\u7684hint\uff0c\u6240\u4ee5\u9700\u8981\u5148\u6536\u96c6\u4e0b\u8fd9\u4e2a\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\uff0c\u8ba9\u6267\u884c\u8ba1\u5212\u8d70\u6b63\u786e\u3002<\/p>\n

    2.\u4e5f\u5148\u4e0d\u8981\u7740\u6025\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\uff0c\u53ef\u4ee5\u5148\u770b\u4e0b\u5b9e\u9645\u72b6\u51b5\u518d\u8bf4\u3002
    \n\uff081\uff09\u64cd\u4f5c\u7cfb\u7edf\u7684iostat\uff0c\u5173\u6ce8\u4e0b\u9762\u4e00\u4e9b\uff1a<\/p>\n

    iostat -d -k 1 10         #\u67e5\u770bTPS\u548c\u541e\u5410\u91cf\u4fe1\u606f(\u78c1\u76d8\u8bfb\u5199\u901f\u5ea6\u5355\u4f4d\u4e3aKB)\niostat -d -m 2            #\u67e5\u770bTPS\u548c\u541e\u5410\u91cf\u4fe1\u606f(\u78c1\u76d8\u8bfb\u5199\u901f\u5ea6\u5355\u4f4d\u4e3aMB)\niostat -d -x -k 1 10      #\u67e5\u770b\u8bbe\u5907\u4f7f\u7528\u7387\uff08%util\uff09\u3001\u54cd\u5e94\u65f6\u95f4\uff08await\uff09 iostat -c 1 10 #\u67e5\u770bcpu\u72b6\u6001\n\nutil\u662f\u5426\u8fbe\u5230100\u3001await<5\n<\/code><\/pre>\n
    \uff082\uff09\u6570\u636e\u5e93\u8fd9\u5f20\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\u60c5\u51b5<\/p>\n
    select num_rows, blocks, last_analyzed from dba_tables where  owner_name='xxx' and table_name = 'xxx';\n<\/code><\/pre>\n
    3.\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\u6b65\u9aa4
    \n\uff081\uff09\u5173\u590d\u5236\u8fdb\u7a0b
    \n\uff082\uff09\u4f7f\u7528sql\u6536\u96c6\uff0c\u6536\u96c6\u7684\u65f6\u5019\u6ce8\u610f\u91c7\u6837\u7387\u3002\u5927\u8868\u91c730%\u5de6\u53f3\u5408\u9002\u3002\u4ee5\u53ca\u5e76\u884c\u5ea6\u3002<\/p>\n
    BEGIN\n  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'xxx',\n                                tabname          => 'xxx',\n                                estimate_percent => 30,\n                                method_opt       => 'for all columns size repeat',\n                                no_invalidate    => FALSE,\n                                degree           => 8,);\nEND;\n\n\/\/\u6ce8\u610f\u76f4\u89c2\u56fe\u9009\u9879\u3002\n<\/code><\/pre>\n
    \uff083\uff09\u5f00\u542f\u590d\u5236\u8fdb\u7a0b\uff0c\u53ef\u4ee5\u67e5\u770bogg\u7684err.log\u4e2d\u7684\u4fe1\u606f\u3002\u770b\u4e0brba\u7b49\u8ffd\u4e0a\u6765\u6ca1\u6709\u3002<\/p>\n
    4.\u4e2d\u9014\u53d1\u751f\u4e2adatapump\u5bfc\u6570\u636e\u7684\u95ee\u9898\uff0c\u53d1\u73b0\u5728\u5bfc\u51fa\u65f6\u5361\u5728\u5bfc\u7edf\u8ba1\u4fe1\u606f\u90a3\u4e00\u5757\u4e86\u3002<\/p>\n
    select s.EVENT,s.MODULE,s.PROGRAM from vsession s where s.MODULE='Data Pump Worker';\nselect program,sid, event,blocking_session from v<\/span>session  where program like '%DW%';\n\nSELECT \n       bs.username \"Blocking User\",  \n       bs.username \"DB User\",  \n       bs.SID \"SID\",  \n       bs.serial# \"Serial#\",  \n       bs.sql_address \"address\",  \n       bs.sql_hash_value \"Sql hash\",  \n       bs.program \"Blocking App\",     \n       bs.machine \"Blocking Machine\",   \n       bs.osuser \"Blocking OS User\",   \n       bs.serial# \"Serial#\",                        \n       ws.username \"Waiting User\",  \n       ws.SID \"WSID\",  \n       ws.program \"Waiting App\",  \n       ws.machine \"Waiting Machine\",  \n       ws.osuser \"Waiting OS User\",  \n       ws.serial# \"WSerial#\",  \n       wk.TYPE lock_type,  \n       hk.lmode mode_held,  \n       wk.request mode_requested,  \n       TO_CHAR(hk.id1) lock_id1,  \n       TO_CHAR(hk.id2) lock_id2,  \n       hk.BLOCK blocking_others  \n  FROM vlock hk, v<\/span>session bs, vlock wk, v<\/span>session ws  \n WHERE hk.BLOCK = 1  \n   AND hk.lmode != 0  \n   AND hk.lmode != 1  \n   AND wk.request != 0  \n   AND wk.TYPE(+) = hk.TYPE  \n   AND wk.id1(+) = hk.id1  \n   AND wk.id2(+) = hk.id2  \n   AND hk.SID = bs.SID(+)  \n   AND wk.SID = ws.SID(+)  \n   AND (bs.username IS NOT NULL)  \n   AND (bs.username <> 'SYSTEM')  \n   AND (bs.username <> 'SYS')  \n ORDER BY 1; \n<\/code><\/pre>\n
    5.\u6e05\u7406\u5206\u533a\u8868\u65f6\u4f7f\u7528truncate\uff0c\u8981\u5e26\u4e0aupdate index\uff0c\u5426\u5219\u5168\u5c40\u7d22\u5f15\u5931\u6548\u3002
    \ndrop\u5206\u533a\u4e5f\u4f1a\u9020\u6210\u5168\u5c40\u7d22\u5f15\u5931\u6548\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
    \u770bawr\u6765\u5206\u6790\u662f\u4ec0\u4e48\u95ee\u9898\u3002 \u521d\u6b65\u5206\u6790\u662f\u8868\u7684\u7edf\u8ba1\u4fe1\u606f\u4e0d\u5bf9\uff0c\u9020\u6210\u4e86insert\u8fd9\u4e2a\u8868\u7684\u65f6\u5019\u7684\u6267\u884c\u8ba1\u5212\u52a0\u4e0a\u4e86\/*+ …<\/p>\n Read More<\/span> \u8fd9\u4e24\u5929\u5927\u6570\u636e\u91cf\u7684\u4e00\u4e9b\u603b\u7ed3<\/span><\/a>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"Layout":"","footnotes":""},"categories":[4],"tags":[],"class_list":["entry","author-suredandan","post-295","post","type-post","status-publish","format-standard","category-oracle"],"views":1052,"_links":{"self":[{"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/posts\/295","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.db2go.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=295"}],"version-history":[{"count":3,"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/posts\/295\/revisions"}],"predecessor-version":[{"id":298,"href":"https:\/\/www.db2go.net\/index.php?rest_route=\/wp\/v2\/posts\/295\/revisions\/298"}],"wp:attachment":[{"href":"https:\/\/www.db2go.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=295"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.db2go.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=295"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.db2go.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=295"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}