{"id":1617,"date":"2024-05-08T15:41:00","date_gmt":"2024-05-08T07:41:00","guid":{"rendered":"http:\/\/oneai.eu.org\/?p=1617"},"modified":"2024-05-08T15:41:00","modified_gmt":"2024-05-08T07:41:00","slug":"%e6%80%a7%e8%83%bd%e6%b5%8b%e8%af%95%e4%b8%ad%e5%b8%b8%e7%94%a8%e6%95%b0%e6%8d%ae%e5%ba%93%e6%93%8d%e4%bd%9c%e5%8f%8a%e6%95%b0%e6%8d%ae%e5%ba%93%e7%9b%91%e6%8e%a7sql%e6%80%bb%e7%bb%93","status":"publish","type":"post","link":"https:\/\/oneai.eu.org\/?p=1617","title":{"rendered":"\u6027\u80fd\u6d4b\u8bd5\u4e2d\u5e38\u7528\u6570\u636e\u5e93\u64cd\u4f5c\u53ca\u6570\u636e\u5e93\u76d1\u63a7SQL\u603b\u7ed3"},"content":{"rendered":"
\u6027\u80fd\u6d4b\u8bd5\u4e2d\u5e38\u7528\u6570\u636e\u5e93\u64cd\u4f5c\u53ca\u6570\u636e\u5e93\u76d1\u63a7SQL\u603b\u7ed3\n                           --by newhackerman\n\u76ee\u5f55\n\n1.\u627e\u51fa\u957f\u65f6\u95f4\u6267\u884c\u7684SQL\u8bed\u53e5\uff1a        3\n2.\u627e\u51fa\u4f7f\u7528CPU\u591a\u7684SQL\u8bed\u53e5\uff1a        4\n3.\u67e5\u627e\u6ca1\u6709\u4fdd\u5b58\u5728library cache\u4e2d\u7684\u5927\u5bf9\u8c61\uff1a        4\n4.\u8c03\u6574PGA\u4f18\u5316\u6392\u5e8f\uff1a        4\n5.SQL\u7684\u547d\u4e2d\u7387\uff1a        5\n7.\u56de\u6eda\u6bb5\u7684\u4e89\u7528\u60c5\u51b5\uff1a        6\n\u76d1\u63a7\u6570\u636e\u5e93\u6027\u80fd\u7684\u8bed\u53e5        7\n1. \u76d1\u63a7\u4e8b\u4f8b\u7684\u7b49\u5f85        7\n2. \u56de\u6eda\u6bb5\u7684\u4e89\u7528\u60c5\u51b5        8\n3. \u76d1\u63a7\u8868\u7a7a\u95f4\u7684 I\/O \u6bd4\u4f8b        8\n4. \u76d1\u63a7\u6587\u4ef6\u7cfb\u7edf\u7684 I\/O \u6bd4\u4f8b        8\n5.\u5728\u67d0\u4e2a\u7528\u6237\u4e0b\u627e\u6240\u6709\u7684\u7d22\u5f15        8\n6. \u76d1\u63a7 SGA \u7684\u547d\u4e2d\u7387        9\n7. \u76d1\u63a7 SGA \u4e2d\u5b57\u5178\u7f13\u51b2\u533a\u7684\u547d\u4e2d\u7387        9\n8. \u76d1\u63a7 SGA \u4e2d\u5171\u4eab\u7f13\u5b58\u533a\u7684\u547d\u4e2d\u7387\uff0c\u5e94\u8be5\u5c0f\u4e8e1%        9\n9. \u663e\u793a\u6240\u6709\u6570\u636e\u5e93\u5bf9\u8c61\u7684\u7c7b\u522b\u548c\u5927\u5c0f        10\n11.  \u76d1\u63a7\u5185\u5b58\u548c\u786c\u76d8\u7684\u6392\u5e8f\u6bd4\u7387\uff0c\u6700\u597d\u4f7f\u5b83\u5c0f\u4e8e .10\uff0c\u589e\u52a0 sort_area_size        10\n12. \u76d1\u63a7\u5f53\u524d\u6570\u636e\u5e93\u8c01\u5728\u8fd0\u884c\u4ec0\u4e48SQL\u8bed\u53e5        11\n13. \u76d1\u63a7\u5b57\u5178\u7f13\u51b2\u533a        11\n14. \u627eORACLE\u5b57\u7b26\u96c6        11\n15. \u76d1\u63a7 MTS        11\n16. \u788e\u7247\u7a0b\u5ea6        12\n\u67e5\u770b\u788e\u7247\u7a0b\u5ea6\u9ad8\u7684\u8868        12\n17. \u8868\u3001\u7d22\u5f15\u7684\u5b58\u50a8\u60c5\u51b5\u68c0\u67e5        13\n18\u3001\u627e\u4f7f\u7528CPU\u591a\u7684\u7528\u6237session        13\n19.\u5bf9\u53ef\u7591\/\u6027\u80fd\u4e0d\u597d\u7684Server Process\u6765\u8fdb\u884cTrace.,\u53ef\u4ee5\u7528tkprof\u6765\u5206\u6790Trace\u7684\u7ed3\u679c.        14\n20.\u67e5\u770bLock        15\n\u8c03\u7528oracle\u987e\u95ee\uff0c\u5206\u6790SQL\u8bed\u53e5\uff0c\u8be5\u600e\u6837\u4f18\u5316        17\n\u67e5\u770b\u9501\u8868\u7684\u65b9\u6cd5        19\n\u67e5\u770bSGA\u533a\u5269\u4f59\u53ef\u7528\u5185\u5b58        19\n\u67e5\u770b\u8868\u7684\u7d22\u5f15\uff1a        23\n\u67e5\u770b\u5b58\u50a8\u8fc7\u7a0b\u53ca\u89e6\u53d1\u5668\u8fc7\u7a0b        23\n\u67e5\u770b\u6307\u5b9a\u8868\u7a7a\u95f4\u7684\u7a7a\u95f4\u5927\u5c0f,\u8868\u7a7a\u95f4\u603b\u5927\u5c0fGB        23\n\u67e5\u770b\u53ef\u7528\u8868\u7a7a\u95f4\u5927\u5c0f,\u8868\u7a7a\u95f4\u603b\u5927\u5c0fMB        23\n\u6839\u636e\u6761\u4ef6\u9ad8\u6548\u5220\u9664\u91cd\u590d\u7684\u8bb0\u5f55\uff08\u4e00\u6b21\u53ea\u5220\u9664\u4e00\u4efd\uff1a\u5982\u76f8\u540c\u7684\u8bb0\u5f55\u6709\u4e24\u6761\uff0c\u4e00\u6b21\u53ea\u5220\u9664\u4e00\u6761\uff0c\u4fdd\u7559\u4e00\u6761\uff09        24\n\u5907\u4efd\u4e00\u5f20\u8868\u7684\u6570\u636e\u5230\u4e00\u5f20\u65b0\u8868        24\n\u8f93\u51faoracle\u6027\u80fd\u62a5\u544a(AWRRPT.sql AWR\u62a5\u544a)        25\n\u542f\u505coracle\u5b9a\u65f6\u8c03\u5ea6\u4efb\u52a1        25\n\u67e5\u770b\u56de\u6eda\u76f8\u5173\u7cfb\u7edf\u53c2\u6570        26\n\u67e5\u770b\u54ea\u4e9bsession\u6b63\u5728\u4f7f\u7528\u54ea\u4e9b\u56de\u6eda\u6bb5        26\n\u901a\u8fc7\u8868\u5206\u6790\u5224\u65ad\u7d22\u5f15\u662f\u5426\u5931\u6548\u53ca\u89e3\u51b3\u65b9\u6cd5        27\n\u67e5\u770b\u6bcf\u5206\u949f\u5904\u7406\u8bb0\u5f55\u6570SQL        28\n\u67e5\u770b\u672c\u7528\u6237\u4e0b\u7684\u5404\u79cd\u5bf9\u8c61\u7684SQL\u811a\u672c        28\n\n\u627e\u51fa\u957f\u65f6\u95f4\u6267\u884c\u7684SQL\u8bed\u53e5\uff1a\nSELECT SE.SID, OPNAME, TRUNC(SOFAR \/ TOTALWORK * 100, 2) || '%' AS PCT_WORK, ELAPSED_SECONDS ELAPSED, ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) \/ SOFAR) REMAIN_TIME, SQL_TEXT\n FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE\n WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE\n AND SL.SID = SE.SID AND SOFAR != TOTALWORK ORDER BY START_TIME\uff1b\n-----------------------------------------------------------------------------------------\n\u627e\u51fa\u4f7f\u7528CPU\u591a\u7684SQL\u8bed\u53e5\uff1a\n\u67e5\u627e\u6307\u5b9aSPID\u6b63\u5728\u6267\u884c\u7684SQL\u8bed\u53e5\uff1a\n1\uff0eSELECT P.pid pid,S.sid sid,P.spid spid,S.username username,S.osuser osname,P.serial# S_#,P.terminal,P.program  program,P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80))  SQL\nFROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+)  AND P.spid LIKE 'SID';\n\n2\uff0e\u5728linux\u73af\u5883\u53ef\u4ee5\u901a\u8fc7ps\u67e5\u770b\u8fdb\u7a0b\u4fe1\u606f\u5305\u62ecpid,windows\u4e2d\u4efb\u52a1\u7ba1\u7406\u5668\u7684PID\u4e0ev$process\u4e2dpid\u4e0d\u80fd\u4e00\u4e00\u5bf9\u5e94\u3002 windows\u662f\u591a\u7ebf\u7a0b\u670d\u52a1\u5668,\u6bcf\u4e2a\u8fdb\u7a0b\u5305\u542b\u4e00\u7cfb\u5217\u7ebf\u7a0b\u3002\u8fd9\u70b9\u4e8eunix\u7b49\u4e0d\u540c\uff0cUnix\u6bcf\u4e2aOralce\u8fdb\u7a0b\u72ec\u7acb\u5b58\u5728\uff0c\u5728Nt\u4e0a\u6240\u6709\u7ebf\u7a0b\u7531Oralce \u8fdb\u7a0b\u884d\u751f\u3002\n\u6307\u5b9aSID\u67e5\u770b\u6b63\u5728\u6267\u884c\u7684SQL\u8bed\u53e5\uff1a\nSELECT P.pid pid,S.sid sid,P.spid spid,S.username username,S.osuser osname,P.serial# S_#,P.terminal,P.program  program,P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80))  SQL\nFROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+)  AND s.sid = 'PID';\n-----------------------------------------------------------------------------------------\n\u67e5\u627e\u6ca1\u6709\u4fdd\u5b58\u5728library cache\u4e2d\u7684\u5927\u5bf9\u8c61\uff1a\nSelect * from v$db_object_cache where sharable_mem>10000 and type in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and kept='NO';\n\u5c06\u8fd9\u4e9b\u5bf9\u8c61\u4fdd\u5b58\u5728library cache\u4e2d\uff1a\nExecute dbms_shared_pool.keep('package_name');\n\u5bf9\u5e94\u811a\u672c\uff1adbmspool.sql\n-----------------------------------------------------------------------------------------\n\u8c03\u6574PGA\u4f18\u5316\u6392\u5e8f\uff1a\n\u9996\u5148\u67e5\u770bOracle\u7684v$pga_target_advice\uff1a\nSELECT ROUND(pga_target_for_estimate\/1024\/1024) AS target_mb,\n estd_pga_cache_hit_percentage AS hit_ratio,\n estd_overalloc_count\nFROM v$pga_target_advice\nORDER BY target_mb;\n\u7136\u540e\u8c03\u6574PGA\nalter system set pga_aggregate_target=150M;\n\u5728OLTP\u7cfb\u7edf\u4e2d\uff0c\u5178\u578bPGA\u5185\u5b58\u8bbe\u7f6e\u5e94\u8be5\u662f\u603b\u5185\u5b58\u7684\u8f83\u5c0f\u90e8\u5206\uff08\u4f8b\u598220\uff05\uff09\uff0c\u5269\u4e0b80\uff05\u5206\u914d\u7ed9SGA\u3002\nOLTP\uff1aPGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%\n\u5728DSS\u7cfb\u7edf\u4e2d\uff0c\u7531\u4e8e\u4f1a\u8fd0\u884c\u4e00\u4e9b\u5f88\u5927\u7684\u67e5\u8be2\uff0c\u5178\u578b\u7684PGA\u5185\u5b58\u6700\u591a\u5206\u914d70\uff05\u7684\u5185\u5b58\u3002\nDSS\uff1aPGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%\n-----------------------------------------------------------------------------------------\nSQL\u7684\u547d\u4e2d\u7387\uff1a\nSELECT gethitratio FROM v$librarycache WHERE namespace = 'SQL AREA';\n\u67e5\u770b\u6307\u5b9a\u67d0\u6761SQL\u8bed\u53e5\u7684\u6267\u884c\u60c5\u51b5\uff08\u6267\u884c\u6b21\u6570\u3001\u52a0\u8f7d\u6b21\u6570\u7b49\uff09\uff1a\nSELECT sql_text , users_executing , executions , loads FROM v$sqlarea where sql_text like 'select * from hr.tt';\n----------------------------\nv$librarycache\u4e2dreloads\/pins\u7684\u6bd4\u7387\u5e94\u8be5\u5c0f\u4e8e1\uff05\uff0c\u5982\u679c\u5927\u4e8e1\uff05\uff0c\u5e94\u8be5\u589e\u52a0\u53c2\u6570shared_pool_size\u7684\u503c:\nSelect sum(pins) "executions", sum(reloads) "cache misses",sum(reloads)\/sum(pins)*100 from v$librarycache;\nreloads\/pins>1%\u6709\u4e24\u79cd\u53ef\u80fd:\n\u4e00\u79cd\u662flibrary cache\u7a7a\u95f4\u4e0d\u8db3\n\u4e00\u79cd\u662fsql\u4e2d\u5f15\u7528\u7684\u5bf9\u8c61\u4e0d\u5408\u6cd5\n\u67e5\u770b\u73b0shared_pool_size\u7684\u503c\nshow parameter shared_pool_size\n\u66f4\u6539shared_pool_size\u7684\u503c\nAlter System set shared_pool_size=2048M;\n-----------------------------------------------------------------------------------------\nSQL\u8bed\u53e5\u7684\u786c\u89e3\u91ca\nSQL\u8bed\u53e5\u8fd0\u884c\u8fc7\u7a0b\n1).\u4f7f\u7528hash\u7b97\u6cd5\u5f97\u5230sql\u8bed\u53e5\u7684hash_value\u503c\n2).\u5982\u679chash_value\u503c\u5728\u5185\u5b58\u4e2d\uff0c\u53eb\u505a\u547d\u4e2d\u6267\u884c\u8f6f\u89e3\u6790\n3).\u5982\u679chash_value\u503c\u4e0d\u5b58\u5728\uff0c\u6267\u884c\u786c\u89e3\u6790\n4).\u8bed\u6cd5\u89e3\u6790\uff0c\u67e5\u770b\u662f\u5426\u6709\u9519\u8bef\n5).\u8bed\u610f\u89e3\u6790\uff0c\u67e5\u770b\u6743\u9650\u662f\u5426\u7b26\u5408\n6).\u82e5\u6709\u89c6\u56fe\uff0c\u53d6\u51fa\u89c6\u56fe\u7684\u5b9a\u4e49\n7).\u8fdb\u884csql\u8bed\u53e5\u7684\u81ea\u52a8\u6539\u5199\uff0c\u5982\u5c06\u5b50\u67e5\u8be2\u6539\u5199\u4e3a\u8fde\u63a5\n8).\u9009\u62e9\u6700\u4f18\u7684\u6267\u884c\u8ba1\u5212\n9).\u53d8\u91cf\u7ed1\u5b9a\n10).\u8fd0\u884c\u6267\u884c\u8ba1\u5212\n11).\u8fd4\u56de\u7ed3\u679c\u7ed9\u7528\u6237\n\u56e0\u4e3a\u8f6f\u89e3\u6790\u662f\u4ece\u6b6411\u6b65\u9aa4\u4e2d\u7b2c9\u6b65\u5f00\u59cb\u7684\uff0c\u56e0\u6b64\u8f6f\u89e3\u6790\u6bd4\u786c\u89e3\u6790\u8282\u7ea6\u5927\u91cf\u7684\u7cfb\u7edf\u5f00\u9500\uff0c\u5e94\u8be5\u5c3d\u91cf\u964d\u4f4e\u786c\u89e3\u6790\u7684\u6b21\u6570\n\u68c0\u67e5v$librarycache\u4e2dsql area\u7684gethitratio\u662f\u5426\u8d85\u8fc790%\uff0c\u5982\u679c\u672a\u8d85\u8fc790%\uff0c\u5e94\u8be5\u68c0\u67e5\u5e94\u7528\u4ee3\u7801\uff0c\u63d0\u9ad8\u5e94\u7528\u4ee3\u7801\u7684\u6548\u7387\uff1a\nSelect gethitratio*100 from v$librarycache where namespace='SQL AREA';\n\uff08\u6b64\u503c\u662f\u6570\u636e\u5e93\u957f\u671f\u8fd0\u884c\u540e\uff0c\u624d\u80fd\u5224\u65ad\uff09\n\u67e5\u770b\u67d0\u4e2asession\u7684\u786c\u89e3\u91ca\u4e2a\u6570\uff1a\nselect a.sid,to_char(a.value) from v$sesstat a,v$session b ,v$statname c where a.sid=b.sid and a.statistic#=c.statistic# and a.sid = 6110 and c.name='parse count (hard)';\n                     SID|TO_CHAR(A.VALUE)\n------------------------|----------------------------------------\n                    6110|12\n-----------------------------------------------------------------------------------------\n\u56de\u6eda\u6bb5\u7684\u4e89\u7528\u60c5\u51b5\uff1a\nselect name,waits,gets,waits\/gets ratio from v$rollstat a,v$rollname b where a.usn=b.usn;\n\u5bf9\u542b\u6709\u56de\u6eda\u6bb5\u5757\u7684\u7f13\u51b2\u533a\u7684\u4e89\u7528\u4e5f\u4f1a\u5f71\u54cd\u5230\u5bf9\u56de\u6eda\u6bb5\u7684\u4e89\u7528\u3002\u8fd9\u53ef\u4ee5\u901a\u8fc7\u67e5\u8be2\u52a8\u6001\u6027\u80fd\u8868V$WAITSTAT\u6765\u68c0\u6d4b\u662f\u5426\u5b58\u5728\u5bf9\u56de\u6eda\u6bb5\u7684\u4e89\u7528\uff0c\u4f8b\u5982\uff1a\nSELECT class,count FROM V$WAITSTAT WHERE class IN('system undo header','system undo block','undo header','undo block');\n\u5176\u4e2d\u53c2\u6570\u542b\u4e49\u5982\u4e0b\uff1a\n\u25c6 system undo header\uff1a\u5bf9\u542b\u6709SYSTEM\u56de\u6eda\u6bb5\u6807\u9898\u5757\u7684\u7f13\u51b2\u533a\u7684\u7b49\u5f85\u6b21\u6570\u3002\n\u25c6 system undo block\uff1a\u5bf9\u542b\u6709SYSTEM\u56de\u6eda\u6bb5\u975e\u6807\u9898\u5757\u7684\u7f13\u51b2\u533a\u7684\u7b49\u5f85\u6b21\u6570\u3002\n\u25c6 undo header\uff1a\u5bf9\u542b\u6709\u975eSYSTEM\u56de\u6eda\u6bb5\u6807\u9898\u5757\u7684\u7f13\u51b2\u533a\u7684\u7b49\u5f85\u6b21\u6570\u3002\n\u25c6 undo block\uff1a\u5bf9\u542b\u6709\u975eSYSTEM\u56de\u6eda\u6bb5\u975e\u6807\u9898\u5757\u7684\u7f13\u51b2\u533a\u7684\u7b49\u5f85\u6b21\u6570\u3002\n\u5982\u679c\u4efb\u4f55\u7b49\u5f85\u6b21\u6570\u5927\u4e8e\u603b\u8bf7\u6c42\u6570\u76841%\uff0c\u5219\u5e94\u521b\u5efa\u66f4\u591a\u7684\u56de\u6eda\u6bb5\u6765\u51cf\u5c11\u7ade\u4e89\uff0c\u53ef\u4ee5\u5468\u671f\u6027\u5730\u68c0\u67e5\u8fd9\u4e9b\u7edf\u8ba1\u6570\u5b57\uff0c\u5e76\u5c06\u5b83\u4e0e\u603b\u7684\u8bf7\u6c42\u6570\u636e\u7684\u6b21\u6570\u4f5c\u6bd4\u8f83\u3002\n\u603b\u7684\u8bf7\u6c42\u6570\u636e\u6b21\u6570\u53ef\u7528\u5982\u4e0b\u8bed\u53e5\u6c42\u51fa:\nSELECT SUM(value) FROM V$SYSSTAT WHERE name IN('db block gets','consistent gets');\n-----------------------------------------------------------------------------------------\n\u76d1\u63a7\u6570\u636e\u5e93\u6027\u80fd\u7684\u8bed\u53e5\n\u4e0b\u9762\u662f\u4e00\u4e9b\u76d1\u63a7\u6570\u636e\u5e93\u6027\u80fd\u7684\u8bed\u53e5\uff1a \n\u76d1\u63a7\u6570\u636e\u5e93\u6027\u80fd\u7684SQL\u8bed\u53e5 \n1. \u76d1\u63a7\u4e8b\u4f8b\u7684\u7b49\u5f85 \n  select   event, \n    sum(decode(wait_Time,0,0,1)) "Prev",  \n    sum(decode(wait_Time,0,1,0)) "Curr", \n    count(*) "Tot"  \n       from  v$session_Wait  \n       group by event  \n       order by 4; \n\n2. \u56de\u6eda\u6bb5\u7684\u4e89\u7528\u60c5\u51b5 \n      select   name, waits, gets, waits\/gets "Ratio"  \n       from  v$rollstat a, v$rollname b  \n       where  a.usn = b.usn;  \n\n3. \u76d1\u63a7\u8868\u7a7a\u95f4\u7684 I\/O \u6bd4\u4f8b \n      select   df.tablespace_name name,df.file_name "file",f.phyrds pyr, \n        f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw \n       from  v$filestat f, dba_data_files df \n       where  f.file# = df.file_id \n       order by df.tablespace_name; \n\n4. \u76d1\u63a7\u6587\u4ef6\u7cfb\u7edf\u7684 I\/O \u6bd4\u4f8b \n      select   substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",  \n        a.status, a.bytes, b.phyrds, b.phywrts  \n       from  v$datafile a, v$filestat b  \n       where  a.file# = b.file#;  \n\n5.\u5728\u67d0\u4e2a\u7528\u6237\u4e0b\u627e\u6240\u6709\u7684\u7d22\u5f15 \n      select   user_indexes.table_name,  \n        user_indexes.index_name, \n        uniqueness,  \n        column_name \n       from  user_ind_columns, user_indexes \n       where  user_ind_columns.index_name = user_indexes.index_name and  \n        user_ind_columns.table_name = user_indexes.table_name  \n       order by user_indexes.table_type, user_indexes.table_name, \n        user_indexes.index_name, column_position; \n6. \u76d1\u63a7 SGA \u7684\u547d\u4e2d\u7387 \n      select   a.value + b.value "logical_reads",  \n        c.value "phys_reads", \n        round(100 * ((a.value+b.value)-c.value) \/ (a.value+b.value)) "BUFFER HIT RATIO"  \n       from  v$sysstat a, v$sysstat b, v$sysstat c \n       where  a.statistic# = 38 and b.statistic# = 39 and  \n        c.statistic# = 40;  \n\n7. \u76d1\u63a7 SGA \u4e2d\u5b57\u5178\u7f13\u51b2\u533a\u7684\u547d\u4e2d\u7387 \n      select   parameter, gets,Getmisses ,  \n        getmisses\/(gets+getmisses)*100 "miss ratio", \n        (1-(sum(getmisses)\/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" \n       from  v$rowcache  \n       where  gets+getmisses <>0 \n       group by parameter, gets, getmisses;  \n\n8. \u76d1\u63a7 SGA \u4e2d\u5171\u4eab\u7f13\u5b58\u533a\u7684\u547d\u4e2d\u7387\uff0c\u5e94\u8be5\u5c0f\u4e8e1% \n      select   sum(pins) "Total Pins", sum(reloads) "Total Reloads", \n        sum(reloads)\/sum(pins) *100 libcache \n       from  v$librarycache; \n      select   sum(pinhits-reloads)\/sum(pins) "hit radio", \n        sum(reloads)\/sum(pins) "reload percent"  \n       from  v$librarycache; \n\n9. \u663e\u793a\u6240\u6709\u6570\u636e\u5e93\u5bf9\u8c61\u7684\u7c7b\u522b\u548c\u5927\u5c0f \n      select   type, \n        count(name) num_instances, \n        sum(source_size) source_size, \n        sum(parsed_size) parsed_size, \n        sum(code_size) code_size, \n        sum(error_size) error_size, \n        sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required  \n       from  dba_object_size  \n       group by type  \n       order by 1; \n\n10. \u76d1\u63a7 SGA \u4e2d\u91cd\u505a\u65e5\u5fd7\u7f13\u5b58\u533a\u7684\u547d\u4e2d\u7387\uff0c\u5e94\u8be5\u5c0f\u4e8e1% \n      SELECT   name, gets, misses, immediate_gets, immediate_misses, \n        Decode(gets,0,0,misses\/gets*100) ratio1, \n        Decode(immediate_gets+immediate_misses,0,0, \n         immediate_misses\/(immediate_gets+immediate_misses)*100) ratio2 \n       FROM  v$latch  \n       WHERE  name IN ('redo allocation', 'redo copy');  \n\n11.  \u76d1\u63a7\u5185\u5b58\u548c\u786c\u76d8\u7684\u6392\u5e8f\u6bd4\u7387\uff0c\u6700\u597d\u4f7f\u5b83\u5c0f\u4e8e .10\uff0c\u589e\u52a0 sort_area_size  \n      SELECT   name, value  \n       FROM  v$sysstat  \n       WHERE  name IN ('sorts (memory)', 'sorts (disk)');  \n\n12. \u76d1\u63a7\u5f53\u524d\u6570\u636e\u5e93\u8c01\u5728\u8fd0\u884c\u4ec0\u4e48SQL\u8bed\u53e5 \n      SELECT   osuser, username, sql_text  \n       from  v$session a, v$sqltext b \n       where  a.sql_address =b.address order by address, piece; \n\n13. \u76d1\u63a7\u5b57\u5178\u7f13\u51b2\u533a \n      SELECT   (SUM(PINS - RELOADS)) \/ SUM(PINS) "LIB CACHE"  \n       FROM  V$LIBRARYCACHE; \n      SELECT   (SUM(GETS - GETMISSES - USAGE - FIXED)) \/ SUM(GETS) "ROW CACHE"  \n       FROM  V$ROWCACHE; \n      SELECT   SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"  \n       FROM  V$LIBRARYCACHE;  \n     \u540e\u8005\u9664\u4ee5\u524d\u8005,\u6b64\u6bd4\u7387\u5c0f\u4e8e1%,\u63a5\u8fd10%\u4e3a\u597d\u3002 \n      SELECT   SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" \n       FROM  V$ROWCACHE; \n\n14. \u627eORACLE\u5b57\u7b26\u96c6 \n      select * from sys.props$ where name='NLS_CHARACTERSET';  \n\n15. \u76d1\u63a7 MTS \n  select busy\/(busy+idle) "shared servers busy" from v$dispatcher; \n  \u6b64\u503c\u5927\u4e8e0.5\u65f6\uff0c\u53c2\u6570\u9700\u52a0\u5927 \n  select sum(wait)\/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher'; \n  select count(*) from v$dispatcher; \n  select servers_highwater from v$mts; \n  servers_highwater\u63a5\u8fd1mts_max_servers\u65f6\uff0c\u53c2\u6570\u9700\u52a0\u5927 \n\n16. \u788e\u7247\u7a0b\u5ea6 \n      select   tablespace_name,count(tablespace_name)  \n       from  dba_free_space  \n       group by tablespace_name  \n       having count(tablespace_name)>10; \n  alter tablespace name coalesce; \n  alter table name deallocate unused; \n      create or replace view ts_blocks_v as \n       select   tablespace_name,block_id,bytes,blocks, \n         segment_name  \n        from  dba_free_space \n       union all \n       select   tablespace_name,block_id,bytes,blocks, \n         segment_name  \n        from  dba_extents; \n      select * from ts_blocks_v; \n      select   tablespace_name,sum(bytes),max(bytes),count(block_id)  \n       from  dba_free_space  \n       group by tablespace_name; \n     \u67e5\u770b\u788e\u7247\u7a0b\u5ea6\u9ad8\u7684\u8868 \n      SELECT   segment_name table_name , COUNT(*) extents \n       FROM  dba_segments  \n       WHERE  owner NOT IN ('SYS', 'SYSTEM')  \n       GROUP BY segment_name \n       HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name); \n\n17. \u8868\u3001\u7d22\u5f15\u7684\u5b58\u50a8\u60c5\u51b5\u68c0\u67e5 \n      select   segment_name,sum(bytes),count(*) ext_quan  \n       from  dba_extents  \n       where   tablespace_name='&tablespace_name' and  \n        segment_type='TABLE'  \n       group by tablespace_name,segment_name; \n      select   segment_name,count(*)  \n       from  dba_extents  \n       where  segment_type='INDEX' and owner='&owner' \n       group by segment_name; \n\n18\u3001\u627e\u4f7f\u7528CPU\u591a\u7684\u7528\u6237session \n     12\u662fcpu used by this session \n      select   a.sid,spid,status,substr(a.program,1,40) prog, \n        a.terminal,osuser,to_char(value\/60\/100) value \n       from  v$session a,v$process b,v$sesstat c \n       where  c.statistic#=12 and  \n        c.sid=a.sid and  \n        a.paddr=b.addr   \n       order by value desc; \n\u8ddf\u636etopas\u6267\u884c\u7ed3\u679c\u5f97\u5230\u6d88\u8017cpu\u6216\u8005\u5185\u5b58\u8f83\u9ad8\u7684Oracle\u8fdb\u7a0bpid\uff0c\u7136\u540e\u6267\u884c\u4e0b\u9762\u7684sql\u8bed\u53e5\uff0c\u8fd9\u6837\u5c31\u80fd\u627e\u51fa\u6548\u7387\u4f4e\u4e0b\u7684sql\u8bed\u53e5\uff1a\nselect z.SQL_TEXT from v$process x, v$session y, v$sqltext z   where x.ADDR = y.PADDR                                                                                                    \nand y.SQL_ADDRESS = z.ADDRESS  and x.SPID = 'pid\u503c' ;\n\n19.\u5bf9\u53ef\u7591\/\u6027\u80fd\u4e0d\u597d\u7684Server Process\u6765\u8fdb\u884cTrace.,\u53ef\u4ee5\u7528tkprof\u6765\u5206\u6790Trace\u7684\u7ed3\u679c.  \n   (1) start_trc:  \n  #!\/bin\/ksh  \n  if (( $# != 1 ))  \n  then  \n  echo Usuage: start_trc pid  \n  fi  \n  sid_serial=$(print "  \n  connect \/ as sysdba;  \n  set heading off;  \n  set feedback off;  \n  select a.sid,a.serial# from v\\$session a,v\\$process b where a.paddr=b.addr and b.spid=$1;  \n  exit;  \n  " | sqlplus -s \/nolog | grep -v 'Connected' | sed -e 's\/\\([0-9]\\{1,\\}\\)\/\\1,\/' -e '\/^$\/d' )  \n  if [[ -z $sid_serial ]]  \n  then  \n  print "Seems that this process $1 is not an Oracle process!"  \n  exit 1  \n  fi  \n  print "  \n  connect \/ as sysdba;  \n  execute dbms_system.set_sql_trace_in_session($sid_serial,true);  \n  exit;  \n  " | sqlplus -s \/nolog  \n\n  (2) stop_trc:  \n  #!\/bin\/ksh  \n  if (( $# != 1 ))  \n  then  \n  echo Usuage: stop_trc pid  \n  fi  \n  sid_serial=$(print "  \n  connect \/ as sysdba;  \n  set heading off;  \n  set feedback off;  \n  select a.sid,a.serial# from v\\$session a,v\\$process b where a.paddr=b.addr and b.spid=$1;  \n  exit;  \n  " | sqlplus -s \/nolog | grep -v 'Connected' | sed -e 's\/\\([0-9]\\{1,\\}\\)\/\\1,\/' -e '\/^$\/d' )  \n  if [[ -z $sid_serial ]]  \n  then  \n  print "Seems that this process $1 is not an Oracle process!"  \n  exit 1  \n  fi  \n  print "  \n  connect \/ as sysdba;  \n  execute dbms_system.set_sql_trace_in_session($sid_serial,false);  \n  exit;  \n  " | sqlplus -s \/nolog \n\n20.\u67e5\u770bLock  \n  SELECT   sn.username, m.sid, m.type,  \n    DECODE(m.lmode, 0, 'None',  \n     1, 'Null',  \n     2, 'Row Share',  \n     3, 'Row Excl.',  \n     4, 'Share',  \n     5, 'S\/Row Excl.',  \n     6, 'Exclusive',  \n     lmode, ltrim(to_char(lmode,'990'))) lmode,  \n    DECODE(m.request,0, 'None',  \n     1, 'Null',  \n     2, 'Row Share',  \n     3, 'Row Excl.',  \n     4, 'Share',  \n     5, 'S\/Row Excl.',  \n     6, 'Exclusive',  \n     request, ltrim(to_char(m.request,'990'))) request,  \n    m.id1, m.id2  \n   FROM  v$session sn, v$lock m  \n   WHERE  (sn.sid = m.sid AND m.request != 0) OR  \n    ( sn.sid = m.sid AND  \n     m.request = 0 AND  \n     lmode != 4 AND  \n     (id1, id2) IN ( \n       SELECT   s.id1, s.id2  \n        FROM  v$lock s  \n        WHERE  request != 0 AND  \n         s.id1 = m.id1 AND  \n         s.id2 = m.id2 \n       )  \n    )  \n   ORDER BY id1, id2, m.request;  \n\n   select   l.sid,s.serial#,s.username,s.terminal,  \n     decode(l.type,'RW','RW - Row Wait Enqueue',  \n      'TM','TM - DML Enqueue',  \n      'TX','TX - Trans Enqueue',  \n      'UL','UL - User',l.type||'System') res,  \n     substr(t.name,1,10) tab,u.name owner,  \n     l.id1,l.id2,  \n     decode(l.lmode,1,'No Lock',  \n      2,'Row Share',  \n      3,'Row Exclusive',  \n      4,'Share',  \n      5,'Shr Row Excl',  \n      6,'Exclusive',null) lmode,  \n     decode(l.request,1,'No Lock',  \n      2,'Row Share',  \n      3,'Row Excl',  \n      4,'Share',  \n      5,'Shr Row Excl',  \n      6,'Exclusive',null) request  \n    from  v$lock l, v$session s,  \n     sys.user$ u,sys.obj$ t  \n    where  l.sid = s.sid and  \n     s.type != 'BACKGROUND' and  \n     t.obj# = l.id1 and  \n     u.user# = t.owner#;\n----------------------------------------------------------------------------------------------------\n\u8c03\u7528oracle\u987e\u95ee\uff0c\u5206\u6790SQL\u8bed\u53e5\uff0c\u8be5\u600e\u6837\u4f18\u5316\ndeclare\nl_task_id     varchar2(20);\nl_sql         varchar2(2000);\nbegin\nl_sql := 'SQL\u8bed\u53e5';\ndbms_sqltune.drop_tuning_task ('FOLIO_COUNT');\nl_task_id := dbms_sqltune.create_tuning_task (\nsql_text  => l_sql,\nuser_name  => 'ARUP',\nscope      => 'COMPREHENSIVE',\ntime_limit => 120,\ntask_name  => 'FOLIO_COUNT'\n   );\ndbms_sqltune.execute_tuning_task ('FOLIO_COUNT');\nend;\n\/\n\n\u8fd9\u4e2a\u5305\u521b\u5efa\u5e76\u6267\u884c\u4e86\u4e00\u4e2a\u540d\u4e3a FOLIO_COUNT \u7684\u8c03\u6574\u4efb\u52a1\u3002\u63a5\u4e0b\u6765\uff0c\u60a8\u5c06\u9700\u8981\u67e5\u770b\u4efb\u52a1\u6267\u884c\u7684\u7ed3\u679c\uff08\u4e5f\u5c31\u662f\u8bf4\uff0c\u67e5\u770b\u5efa\u8bae\uff09\u3002 \nset serveroutput on size 999999\nset long 999999\nselect dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;\n-----------------------------------------------------------------------------------------------------\n\u67e5\u770b\u6570\u636e\u5e93\u6b63\u5728\u8fd0\u884c\u7684SQL\uff1a\nset line 1000\n    set linesize 256\n    set pagesize 1000\n    set long 999999\n    set heading off\n\n\u65b9\u6cd51\u3002select user_name,sql_text\n\u3000\u3000 from v$open_cursor\n\u3000\u3000 where sid in (select sid from (select sid,serial#,username,program\n\u3000\u3000 from v$session\n\u3000\u3000 where status='ACTIVE'));\n\n\u65b9\u6cd52\u3002select z.sql_text from v$process x,v$session y,v$sqltext z where x.addr=y.paddr and y.sql_address=z.address and x.spid<>0 order by z.piece; \n\n-----------------------------------------------------------------------------------------------------\n\u67e5\u770b\u9501\u8868\u7684\u65b9\u6cd5                                                                             \n1\u3002select c.sid,c.serial#,c.program,c.username,b.owner,c.event,a.locked_mode,b.object_name from \nv$locked_object a,dba_objects b,v$session c where a.object_id=b.object_id and a.session_id=c.sid;\n---------\n2\u3002SELECT B.OWNER, B.OBJECT_NAME, A.SESSION_ID, A.LOCKED_MODE\n  FROM V$LOCKED_OBJECT A, DBA_OBJECTS B\n WHERE B.OBJECT_ID = A.OBJECT_ID;\n\u89e3\u9501\u65b9\u6cd5\uff1a\nalter system kill session 'sid,serial#';                                          \n-----------------------------------------------------------------------------------------------------\n\u67e5\u770bSGA\u533a\u5269\u4f59\u53ef\u7528\u5185\u5b58\ncol OBJECT_NAME format a20\ncol \u81ea\u7531\u7a7a\u95f4\u767e\u5206\u6bd4(%) format 90.99\u3000\u3000\u3000--\u683c\u5f0f\u5316\uff0c\u628a\u5c0f\u6570\u70b9\u5bf9\u9f50\u4e3a\u4e24\u4f4d\n\nselect name,\n\u3000\u3000\u3000\u3000\u3000\u3000sgasize\/1024\/1024\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000"Allocated(M)",\n\u3000\u3000\u3000\u3000\u3000\u3000bytes\/1024\/1024 \u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000 "\u81ea\u7531\u7a7a\u95f4(M)",\n\u3000\u3000\u3000\u3000\u3000\u3000round(bytes\/sgasize*100, 2)\u3000\u3000\u3000"\u81ea\u7531\u7a7a\u95f4\u767e\u5206\u6bd4(%)"\n\u3000\u3000 from   (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f\n\u3000\u3000 where  f.name = 'free memory';\n-----------------------------------------------------------------------------------------------------\n\u67e5\u770b\u5404\u4e2a\u8868\u7a7a\u95f4\u5360\u7528\u78c1\u76d8\u60c5\u51b5\uff1a\ncol tablespace format a20\nselect \n b.file_id                                 \u6587\u4ef6ID\u53f7,\n b.tablespace_name                         \u8868\u7a7a\u95f4\u540d,\n b.bytes\/1024\/1024                                 MB,\n (b.bytes-sum(nvl(a.bytes,0)))\/1024\/1024                 \u5df2\u4f7f\u7528MB,\n sum(nvl(a.bytes,0))\/1024\/1024                         \u5269\u4f59\u7a7a\u95f4MB,\n sum(nvl(a.bytes,0))\/(b.bytes)*100         \u5269\u4f59\u767e\u5206\u6bd4 \n from dba_free_space a,dba_data_files b \n where a.file_id=b.file_id \n group by b.tablespace_name,b.file_id,b.bytes \n order by b.file_id\n \/\n-----------------------------------------------------------------------------------------------------\n\u67e5\u770b\u6570\u636e\u5e93\u540e\u53f0\u8fdb\u7a0b\uff1a\ndesc v$bgprocess\n\u540d\u79f0                                      \u7a7a?      \u7c7b\u578b\n ----------------------------------------- -------- -----------------\n PADDR                                              RAW(4)                                --\u8fdb\u7a0b\u72b6\u6001\u5bf9\u8c61\u5730\u5740\n NAME                                               VARCHAR2(5)                --\u540e\u53f0\u8fdb\u7a0b\u540d\u79f0\n DESCRIPTION                                        VARCHAR2(64)        --\u540e\u53f0\u8fdb\u7a0b\u63cf\u8ff0\n ERROR                                              NUMBER                                --\u540e\u53f0\u8fdb\u7a0b\u8fd0\u884c\u4e2d\u6240\u9047\u5230\u7684\u9519\u8bef\u6570\n\ncol DESCRIPTION format a40\n\nselect * from v$bgprocess where  paddr<>'00';\nPADDR    NAME  DESCRIPTION                                                           ERROR\n-------- ----- ---------------------------------------------------------------- ----------\n66D90CE4 PMON  process cleanup                                                           0\n66D91038 DBW0  db writer process 0                                                       0\n66D9138C LGWR  Redo etc.                                                                 0\n66D916E0 CKPT  checkpoint                                                                0\n66D91A34 SMON  System Monitor Process                                                    0\n66D91D88 RECO  distributed recovery                                                      0\n---------------------------------------------\nSMON        \u7cfb\u7edf\u8fdb\u7a0b\nPMON        \u7528\u6237\u8fdb\u7a0b\nDBWR        \u6570\u636e\u5e93\u5199\u5165\u8fdb\u7a0b\nLGWR        \u65e5\u5fd7\u5199\u5165\u8fdb\u7a0b\nCKPT        \u68c0\u67e5\u70b9\u8fdb\u7a0b                \u7528\u6765\u51cf\u5c11\u5b9e\u4f8b\u6062\u590d\u6240\u9700\u65f6\u95f4\uff0c\n                        init.ora\u4e2d\uff0clog_checkpoint_interval = 10000\uff0c\u6b64\u53c2\u6570\u8bbe\u7f6e\u68c0\u67e5\u70b9\u51fa\u73b0\u7684\u9891\u5ea6\n\nARCH        \u5f52\u6863\u8fdb\u7a0b                \u5c06\u8054\u673a\u91cd\u505a\u65e5\u5fd7\u62f7\u8d1d\u5230\u78c1\u76d8\u6216\u78c1\u5e26\uff0c\u5373\u5c06\u8054\u673a\u91cd\u505a\u65e5\u5fd7\u5f52\u6863\nRECO        \u6062\u590d\u8fdb\u7a0b                \u7528\u4e8e\u5206\u5e03\u5f0f\u6570\u636e\u5e93\u4e2d\u7684\u5206\u5e03\u5f0f\u5904\u7406\uff0c\n                        init.ora\u4e2d\uff0cdistributed_transactions = 10\uff0c\u6b64\u53c2\u6570\u5927\u4e8e0\u65f6\u624d\u88ab\u5efa\u7acb\nSNPn        \u5feb\u7167\u8fdb\u7a0b                \u6570\u91cf\u53d6\u51b3\u4e8einit.ora\u4e2d\u53c2\u6570job_queue_processes = 4\nLCKn        \u9501\u8fdb\u7a0b                \u53ef\u9009\u9879\uff0c\u7528\u4e8e\u5e76\u884c\u670d\u52a1\u5668\nDnnn        \u8c03\u5ea6\u8fdb\u7a0b                \u53ef\u9009\u9879\uff0c\u4ec5\u7528\u4e8e\u591a\u7ebf\u7a0b\u670d\u52a1\u5668\n-----------------------------------------------------------------------------------------------------\n\u67e5\u770b\u4ec0\u4e48\u65f6\u95f4\u6709\u54ea\u4e9b\u6570\u636e\u5e93\u5bf9\u8c61\u7ed3\u6784\u88ab\u4fee\u6539\u8fc7:\ncol OBJECT_NAME format a20\nSELECT OBJECT_NAME,\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000                        \u3000--\u5bf9\u8c61\u540d\n\u3000\u3000\u3000\u3000\u3000\u3000OBJECT_TYPE,\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000--\u5bf9\u8c61\u7c7b\u578b\n\u3000\u3000\u3000\u3000\u3000\u3000TO_CHAR(CREATED,       'YYYY-Mon-DD HH24:MI') CREATE_TIME,\u3000--\u521b\u5efa\u65f6\u95f4\n\u3000\u3000\u3000\u3000\u3000\u3000TO_CHAR(LAST_DDL_TIME, 'YYYY-Mon-DD HH24:MI') MOD_TIME,\u3000\u3000 --\u4fee\u6539\u65f6\u95f4\n\u3000\u3000\u3000\u3000\u3000\u3000TIMESTAMP,\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000--\u65f6\u95f4\u6233\n\u3000\u3000\u3000\u3000\u3000\u3000STATUS\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000--\u72b6\u6001\n \u3000\u3000FROM   USER_OBJECTS\n\u3000 \u3000WHERE  to_char(LAST_DDL_TIME,'yyyymmdd')>'&\u65e5\u671f\u53d8\u91cf';\n-----------------------------------------------------------------------------------------------------\n \u67e5\u770b\u7528\u6237\u8868\u4e0a\u7684\u7ea6\u675f\u5185\u5bb9:\n select a.OWNER \u7528\u6237\u540d,\n            a.CONSTRAINT_NAME \u7ea6\u675f\u540d\u79f0,\n            a.CONSTRAINT_TYPE \u7ea6\u675f\u7c7b\u578b,\n            a.TABLE_NAME \u8868\u540d,\n            b.COLUMN_NAME \u5217\u540d,\n            a.SEARCH_CONDITION \u7ea6\u675f\u5185\u5bb9\n     from USER_CONSTRAINTS a,USER_CONS_COLUMNS b\n     where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME;\n-----------------------------------------------------------------------------------------------------\n\u589e\u52a0\u8868\u7a7a\u95f4\u7684\u5927\u5c0f:\n1\u3001\u4e3a\u8fd9\u4e2a\u8868\u7a7a\u95f4\u589e\u52a0\u4e00\u4e2a\u6570\u636e\u6587\u4ef6\nalter tablespace \u8868\u7a7a\u95f4\u540d add datafile '\/u1\/oradata\/userdata_002.ora' size 50m;\u3000\u3000--Unix\u4e2d\nalter tablespace \u8868\u7a7a\u95f4\u540d add datafile 'c:\\oradata\\userdata_002.ora' size 50m;\u3000\u3000 --Windows NT\u4e2d\n\n2\u3001\u91cd\u65b0\u8c03\u6574\u6570\u636e\u6587\u4ef6\u7684\u5927\u5c0f\nalter database datafile '\/u1\/oradata\/userdata_001.ora' resize 50M; \u3000--Unix\u4e2d\nalter database datafile 'c:\\oradata\\userdata_002.ora' resize 50M;\u3000\u3000--Windows NT\u4e2d\n-----------------------------------------------------------------------------------------------------\nsql\u6267\u884c\u8ba1\u5212\u53ca\u5206\u6790\uff1a\n1.set autotrace traceonly\nsql\u8bed\u53e5\nset autotrace  off\n2.explain plan for\nSQL\u8bed\u53e5\n select * from TABLE(dbms_xplan.display());\n\u67e5\u770bSQL\u8fd0\u884c\u65f6\u95f4\n3.set timing on\nsql\u8bed\u53e5\n-----------------------------------------------------------------------------------------------------\n\u67e5\u770b\u8868\u7684\u7d22\u5f15\uff1a\n 1.select owner,index_name,index_type,TABLE_OWNER,status,table_name,UNIQUENESS from dba_indexes where table_name='\u8868\u540d' and OWNER='\u7528\u6237\u540d';\n2.select index_owner \u7d22\u5f15\u62e5\u6709\u8005,INDEX_NAME \u7d22\u5f15\u540d,COLUMN_NAME \u7d22\u5f15\u5b57\u6bb5 ,COLUMN_POSITION \n    from all_ind_columns \n      where index_name in(select INDEX_NAME from all_indexes where upper(table_name) = upper('HSC_INF_GDECINFO') ) \n   order by index_owner,INDEX_NAME,COLUMN_POSITION;\n \u67e5\u770b\u5b58\u50a8\u8fc7\u7a0b\u53ca\u89e6\u53d1\u5668\u8fc7\u7a0b \nselect text from dba_source where type='PROCEDURE' and name='\u5b58\u50a8\u8fc7\u7a0b\u540d' and owner='JMYY';\n\u67e5\u770b\u8868\u6240\u5c5e\u8868\u7a7a\u95f4\uff1a\nselect tablespace_name from all_tables where table_name=upper('\u8868\u540d') and owner='\u7528\u6237';\n\u67e5\u770b\u6307\u5b9a\u8868\u7a7a\u95f4\u7684\u7a7a\u95f4\u5927\u5c0f,\u8868\u7a7a\u95f4\u603b\u5927\u5c0fGB\n1.select OWNER,SUM(BYTES)\/1024\/1024\/1024 G from dba_segments where tablespace_name='\u8868\u7a7a\u95f4\u540d' and owner in ('\u7528\u6237','\u7528\u6237') GROUP BY OWNER ; \n---------------------------------------------------\n\u67e5\u770b\u53ef\u7528\u8868\u7a7a\u95f4\u5927\u5c0f,\u8868\u7a7a\u95f4\u603b\u5927\u5c0fMB\n2. select (select  ROUND(SUM(BYTES) \/ (1024 * 1024), 2) "freeMB" from SYS.DBA_FREE_SPACE where tablespace_name='\u8868\u7a7a\u95f4\u540d' ) "FreeMB",(select  ROUND(SUM(BYTES) \/ (1024 * 1024), 2) "\u603b\u5927\u5c0f" from SYS.DBA_DATA_FILES where tablespace_name='\u8868\u7a7a\u95f4\u540d')  "TotalMB" from dual;\nSELECT UPPER(F.TABLESPACE_NAME) "\u8868\u7a7a\u95f4\u540d",\n       D.TOT_GROOTTE_MB "\u8868\u7a7a\u95f4\u5927\u5c0f(M)",\n       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "\u5df2\u4f7f\u7528\u7a7a\u95f4(M)",\n       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) \/ D.TOT_GROOTTE_MB * 100,\n                     2),\n               '990.99') "\u4f7f\u7528\u6bd4",\n       F.TOTAL_BYTES "\u7a7a\u95f2\u7a7a\u95f4(M)",\n       F.MAX_BYTES "\u6700\u5927\u5757(M)"\n  FROM (SELECT TABLESPACE_NAME,\n               ROUND(SUM(BYTES) \/ (1024 * 1024), 2) TOTAL_BYTES,\n               ROUND(MAX(BYTES) \/ (1024 * 1024), 2) MAX_BYTES\n          FROM SYS.DBA_FREE_SPACE\n         GROUP BY TABLESPACE_NAME) F,\n       (SELECT DD.TABLESPACE_NAME,\n               ROUND(SUM(DD.BYTES) \/ (1024 * 1024), 2) TOT_GROOTTE_MB\n          FROM SYS.DBA_DATA_FILES DD\n         GROUP BY DD.TABLESPACE_NAME) D\n WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME\n ORDER BY F.TABLESPACE_NAME;\n\n\u6839\u636e\u6761\u4ef6\u9ad8\u6548\u5220\u9664\u91cd\u590d\u7684\u8bb0\u5f55\n\uff08\u4e00\u6b21\u53ea\u5220\u9664\u4e00\u4efd\uff1a\u5982\u76f8\u540c\u7684\u8bb0\u5f55\u6709\u4e24\u6761\uff0c\u4e00\u6b21\u53ea\u5220\u9664\u4e00\u6761\uff0c\u4fdd\u7559\u4e00\u6761\uff09\ndelete from \u8868\u540d where rowid in(select max(rowid) from \u8868\u540d where \u6761\u4ef6 group by \u6709\u91cd\u8bb0\u5f55\u7684\u5b57\u6bb5\u540d  having count(\u6709\u91cd\u590d\u8bb0\u5f55\u7684\u5b57\u6bb5\u540d)>1 ) ;\n\u5907\u4efd\u4e00\u5f20\u8868\u7684\u6570\u636e\u5230\u4e00\u5f20\u65b0\u8868\ncreate table testa as select * from testb ;\n\u6ce8\u610f\uff0ctestb\u8868\u7684\u6570\u636e\u5168\u90e8\u653e\u5165testa\u4e2d\uff0c\u4f46\u6ca1\u6709\u7d22\u5f15* \n-----------------------------------------------------------------------------------------------------\n\u8f93\u51faoracle\u6027\u80fd\u62a5\u544a(AWRRPT.sql AWR\u62a5\u544a)\ncd $ORACLE_HOME\/rdbms\/admin\/\nexport ORACLE_SID='\u8981\u5bfc\u51fa\u62a5\u544a\u7684\u6570\u636e\u5e93\u5b9e\u4f8b\u540d\u79f0' \u5982testdb\necho $ORACLE_SID\ntestdb\nsqlplus \u7528\u6237\u540d\/\u5bc6\u7801@testdb\nSQL> @awrrpt.sql\n\u6839\u636e\u63d0\u793a\u5bfc\u51faAWRRPT\u62a5\u544a\n\n\u4fee\u6539\u6536\u96c6\u65f6\u95f4\u95f4\u9694\uff1a\nBEGIN\nDBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(\n   interval  =>  30,\n     retention =>  7*24*60);\n   END;\n   \/\n\u8fd9\u6837\u5c31\u53ef\u4ee5\u8c03\u6574\u6536\u96c6\u95f4\u9694\u4e3a30\u5206\u949f\n-----------------------------------------------------------------------------------------------------\n\u542f\u505coracle\u5b9a\u65f6\u8c03\u5ea6\u4efb\u52a1\ncol PROGRAM_NAME for a50 \/\/\u8bbe\u7f6e\u5b57\u6bb5\u957f\u5ea6\nselect OWNER,JOB_NAME,PROGRAM_NAME,STATE,enabled from DBA_SCHEDULER_JOBS; \/\/ \u67e5\u8be2\u8c03\u5ea6\u4efb\u52a1\n\n\u505c\u6b62\u8c03\u5ea6\nexec DBMS_SCHEDULER.DISABLE ('PURGE_LOG',true);\nexec DBMS_SCHEDULER.DISABLE ('GATHER_STATS_JOB',true);\nexec DBMS_SCHEDULER.DISABLE ('AUTO_SPACE_ADVISOR_JOB',true); \n\u542f\u52a8\u8c03\u5ea6\uff1a\nexec DBMS_SCHEDULER.INABLE ('AUTO_SPACE_ADVISOR_JOB',true);\n-----------------------------------------------------------------------------------------------------\n \u67e5\u770b\u56de\u6eda\u76f8\u5173\u7cfb\u7edf\u53c2\u6570\n show parameter undo \n\u67e5\u770b\u56de\u6eda\u6bb5\u53ef\u7528\u8868\u7a7a\u95f4\uff1a\n  select a.tablespace_name,total,free,total-free used from \n ( select tablespace_name,sum(bytes)\/1024\/1024 totalMB from dba_data_files\n    group by tablespace_name) a, \n ( select tablespace_name,sum(bytes)\/1024\/1024 freeMB from dba_free_space\n    group by tablespace_name) b\n where a.tablespace_name=b.tablespace_name and b.tablespace_name='\u56de\u6eda\u6bb5\u8868\u7a7a\u95f4';\n------------\n\u56de\u6eda\u6bb5\u7684\u7edf\u8ba1\u4fe1\u606f\uff1a\nSELECT n.name,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts,s.status\n\u3000\u3000\u3000\u3000FROM v$rollname n,v$rollstat s\n\u3000\u3000\u3000\u3000WHERE n.usn=s.usn;\n\u56de\u6eda\u6bb5\u7684\u5f53\u524d\u6d3b\u52a8\u4e8b\u52a1\uff1a\nSELECT s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk\n\u3000\u3000\u3000\u3000FROM v$session s,v$transaction t\n\u3000\u3000\u3000\u3000WHERE s.saddr=t.ses_addr;\n\u67e5\u770b\u54ea\u4e9bsession\u6b63\u5728\u4f7f\u7528\u54ea\u4e9b\u56de\u6eda\u6bb5\nSELECT  r.name \u56de\u6eda\u6bb5\u540d,\n        s.sid,\n        s.serial#,\n        s.username \u7528\u6237\u540d,\n        t.status,\n        t.cr_get,\n        t.phy_io,\n        t.used_ublk,\n        t.noundo,\n        substr(s.program, 1, 78) \u64cd\u4f5c\u7a0b\u5e8f\nFROM   sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r\nWHERE  t.addr = s.taddr and t.xidusn = r.usn\nORDER  BY t.cr_get,t.phy_io\n-----------------------------------------------------------------------------------------------------\n\u901a\u8fc7\u8868\u5206\u6790\u5224\u65ad\u7d22\u5f15\u662f\u5426\u5931\u6548\u53ca\u89e3\u51b3\u65b9\u6cd5\n\u67e5\u770b\u5e93\u8868\u4e2d\u672a\u505a\u8fc7\u8868\u5206\u6790\u6216\u8005\u8868\u5206\u6790\u7684\u65e5\u671f\u572810\u4e4b\u524d\u7684\u8bb0\u5f55\u603b\u6570\nSELECT T.TABLE_NAME, T.NUM_ROWS\n  FROM USER_TABLES T\n WHERE (T.LAST_ANALYZED IS NULL OR T.LAST_ANALYZED < SYSDATE - 10)\n   AND T.TABLE_NAME = '\u7269\u7406\u8868\u540d';\n\u67e5\u770b\u5e93\u8868\u4e2d\u73b0\u5728\u7684\u8bb0\u5f55\u6570\nSELECT COUNT(*) FROM CS_REC_ONEKEYLOG;\n\u5982\u679c\u4e24\u8005\u8bb0\u5f55\u6570\u76f8\u5dee\u8ddd\u5927\u5219\u9700\u8fdb\u884c\u8868\u5206\u6790\uff08\u66f4\u65b0\u8868\u7684\u72b6\u6001\uff09\uff1a\nanalyze table \u7528\u6237.\u7269\u7406\u8868\u540d delete system statistics;\n\u67e5\u770b\u8868\u7684\u522b\u540d\u53ca\u6240\u5c5e\u7528\u6237\nselect * from dba_synonyms where synonym_name='\u7269\u7406\u8868\u540d';\n\u67e5\u770b\u8868\u662f\u5426\u5b58\u5728\nselect object_name,object_type,owner from dba_objects where object_name='\u8868\u540d';\n-----------------------------------------------------------------------------------------------------\n\u6570\u636e\u5e93\u542f\u505c\u65b9\u6cd5\uff1a\n1\u3002\u67e5\u770b\u5f53\u524d\u7684\u7528\u6237\u8fdb\u7a0b\uff08PMON\uff09:\u5728\u6570\u636e\u5e93\u6240\u5728\u7684\u670d\u52a1\u5668\u6267\u884c\uff1aps -ef|grep oracle|grep pmon\n2\u3002\u4fee\u6539\/\u5b9a\u4e49ORACLE_SID :export ORACLE_SID=\u6570\u636e\u5e93\u5b9e\u4f8b\u540d\necho $ORACLE_SID\n3\u3002sqlplus '\/as sysdba'\n4\u3002\u505c\u6b62\u6570\u636e\u5e93\uff1ashutdown abort\n\u542f\u52a8\u6570\u636e\u5e93\n1\uff0c2\uff0c3\u6b65\u540c\u4e0a\uff1a\n4\u3002startup\n-----------------------------------------------------------------------------------------------------\n\u67e5\u770b\u6bcf\u5206\u949f\u5904\u7406\u8bb0\u5f55\u6570SQL\nselect to_char(\u65f6\u95f4\u5b57\u6bb5\u540d, 'yyyymmddhh24:mi'), count(*) \nfrom jmyy.reception \nwhere \u65f6\u95f4\u5b57\u6bb5\u540d > to_date('2010-12-13 19:40:00', 'yyyy-mm-dd hh24:mi:ss') \nand \u65f6\u95f4\u5b57\u6bb5\u540d < to_date('2010-12-13 20:20:00', 'yyyy-mm-dd hh24:mi:ss') \ngroup by to_char(\u65f6\u95f4\u5b57\u6bb5\u540d, 'yyyymmddhh24:mi') \norder by to_char(\u65f6\u95f4\u5b57\u6bb5\u540d, 'yyyymmddhh24:mi');\n-----------------------------------------------------------------------------------------------------\n\u83b7\u53d6\u968f\u673a\u6570\u8bb0\u5f55\nSELECT * FROM (SELECT * FROM \u8868\u540d WHERE ROWNUM<=50000 ORDER BY\ndbms_random.random) WHERE ROWNUM<=1\n\u5176\u4e2d\uff1a ROWNUM<=50000\u662f\u5148\u6307\u5b9a\u4e00\u4e2a\u8303\u56f4\uff0c\u53ef\u4ee5\u907f\u514d\u56e0\u4e3a\u8bb0\u5f55\u592a\u591a\u5bfc\u81f4\u6267\u884cSQL\u540e\u54cd\u5e94\u5f88\u6162\uff1b\u800cROWNUM<=1\u662f\u9650\u5236\u6700\u7ec8\u67e5\u8be2\u51fa\u6765\u7684\u968f\u673a\u6570\u6570\u91cf\uff0e\n-----------------------------------------------------------------------------------------------------\n\u67e5\u770b\u672c\u7528\u6237\u4e0b\u7684\u5404\u79cd\u5bf9\u8c61\u7684SQL\u811a\u672c\n\u8868\uff1a\n\u3000\u3000select * from cat;\n\u3000\u3000select * from tab;\n\u3000\u3000select table_name from user_tables;\n\u89c6\u56fe\uff1a\n\u3000\u3000select text from user_views where view_name=upper('&view_name');\n\u7d22\u5f15\uff1a\n\u3000\u3000select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name; \n\u89e6\u53d1\u5668\uff1a\n\u3000\u3000select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;\n\u5feb\u7167\uff1a\n\u3000\u3000select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;\n\u540c\u4e49\u8bcd\uff1a\n\u3000\u3000select * from syn;\n\u5e8f\u5217\uff1a\n\u3000\u3000select * from seq;\n\u6570\u636e\u5e93\u94fe\u8def\uff1a\n\u3000\u3000select * from user_db_links;\n\u7ea6\u675f\u9650\u5236\uff1a\n\u3000\u3000select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from user_constraints;\n\u672c\u7528\u6237\u8bfb\u53d6\u5176\u4ed6\u7528\u6237\u5bf9\u8c61\u7684\u6743\u9650\uff1a\n\u3000\u3000select * from user_tab_privs;\n\u672c\u7528\u6237\u6240\u62e5\u6709\u7684\u7cfb\u7edf\u6743\u9650\uff1a\n\u3000\u3000select * from user_sys_privs;\n\u7528\u6237\uff1a\n\u3000\u3000select * from all_users order by user_id;\n\u8868\u7a7a\u95f4\u5269\u4f59\u81ea\u7531\u7a7a\u95f4\u60c5\u51b5\uff1a\n\u3000\u3000select tablespace_name,sum(bytes) \u603b\u5b57\u8282\u6570,max(bytes),count(*) from dba_free_space group by tablespace_name\uff1b\n\u6570\u636e\u5b57\u5178\uff1a\n\u3000\u3000select table_name from dict order by table_name;\n\u9501\u53ca\u8d44\u6e90\u4fe1\u606f\uff1a\n\u3000\u3000select * from v$lock;\u4e0d\u5305\u62ecDDL\u9501\n\u6570\u636e\u5e93\u5b57\u7b26\u96c6\uff1a\n\u3000\u3000select name,value$ from props$ where name='NLS_CHARACTERSET';\ninin.ora\u53c2\u6570\uff1a\n\u3000\u3000select name,value from v$parameter order by name;\nSQL\u5171\u4eab\u6c60\uff1a\n\u3000\u3000select sql_text from v$sqlarea;\n\u6570\u636e\u5e93\uff1a\n\u3000\u3000select * from v$database\n\u63a7\u5236\u6587\u4ef6\uff1a\n\u3000\u3000select * from V$controlfile;\n\u91cd\u505a\u65e5\u5fd7\u6587\u4ef6\u4fe1\u606f\uff1a\n\u3000\u3000select * from V$logfile;\n\u6765\u81ea\u63a7\u5236\u6587\u4ef6\u4e2d\u7684\u65e5\u5fd7\u6587\u4ef6\u4fe1\u606f\uff1a\n\u3000\u3000select * from V$log;\n\u6765\u81ea\u63a7\u5236\u6587\u4ef6\u4e2d\u7684\u6570\u636e\u6587\u4ef6\u4fe1\u606f\uff1a\n\u3000\u3000select * from V$datafile;\nNLS\u53c2\u6570\u5f53\u524d\u503c\uff1a\n\u3000\u3000select * from V$nls_parameters;\nORACLE\u7248\u672c\u4fe1\u606f\uff1a\n\u3000\u3000select * from v$version;\n\u63cf\u8ff0\u540e\u53f0\u8fdb\u7a0b\uff1a\n\u3000\u3000select * from v$bgprocess;\n\u67e5\u770b\u7248\u672c\u4fe1\u606f\uff1a\n\u3000\u3000select * from product_component_version;\n-----------------------------------------------------------------------------------------------------\noracle\u6570\u636e\u5e93\u5173\u95ed\u4e0e\u91cd\u542f\uff1a\nexport ORACLE_SID=\u5b9e\u4f8b\u540d\u79f0\nsqlplus 'as \/sysdba'\nshutdown \u52a0\u5173\u95ed\u7c7b\u578b\n:\nSHUTDOWN NORMAL\uff1a\u4e0d\u5141\u8bb8\u65b0\u7684\u8fde\u63a5\u3001\u7b49\u5f85\u4f1a\u8bdd\u7ed3\u675f\u3001\u7b49\u5f85\u4e8b\u52a1\u7ed3\u675f\u3001\u505a\u4e00\u4e2a\u68c0\u67e5\u70b9\u5e76\u5173\u95ed\u6570\u636e\u6587\u4ef6\u3002\u542f\u52a8\u65f6\u4e0d\u9700\u8981\u5b9e\u4f8b\u6062\u590d\u3002\nSHUTDOWN TRANSACTIONAL\uff1a\u4e0d\u5141\u8bb8\u65b0\u7684\u8fde\u63a5\u3001\u4e0d\u7b49\u5f85\u4f1a\u8bdd\u7ed3\u675f\u3001\u7b49\u5f85\u4e8b\u52a1\u7ed3\u675f\u3001\u505a\u4e00\u4e2a\u68c0\u67e5\u70b9\u5e76\u5173\u95ed\u6570\u636e\u6587\u4ef6\u3002\u542f\u52a8\u65f6\u4e0d\u9700\u8981\u5b9e\u4f8b\u6062\u590d\u3002\nSHUTDOWN IMMEDIATE\uff1a\u4e0d\u5141\u8bb8\u65b0\u7684\u8fde\u63a5\u3001\u4e0d\u7b49\u5f85\u4f1a\u8bdd\u7ed3\u675f\u3001\u4e0d\u7b49\u5f85\u4e8b\u52a1\u7ed3\u675f\u3001\u505a\u4e00\u4e2a\u68c0\u67e5\u70b9\u5e76\u5173\u95ed\u6570\u636e\u6587\u4ef6\u3002\u6ca1\u6709\u7ed3\u675f\u7684\u4e8b\u52a1\u662f\u81ea\u52a8rollback\u7684\u3002\u542f\u52a8\u65f6\u4e0d\u9700\u8981\u5b9e\u4f8b\u6062\u590d\u3002\nSHUTDOWN ABORT\uff1a\u4e0d\u5141\u8bb8\u65b0\u7684\u8fde\u63a5\u3001\u4e0d\u7b49\u5f85\u4f1a\u8bdd\u7ed3\u675f\u3001\u4e0d\u7b49\u5f85\u4e8b\u52a1\u7ed3\u675f\u3001\u4e0d\u505a\u68c0\u67e5\u70b9\u4e14\u6ca1\u6709\u5173\u95ed\u6570\u636e\u6587\u4ef6\u3002\u542f\u52a8\u65f6\u81ea\u52a8\u8fdb\u884c\u5b9e\u4f8b\u6062\u590d\u3002\n----\n\u542f\u52a8\uff1a\nexport ORACLE_SID=\u5b9e\u4f8b\u540d\u79f0\nsqlplus 'as \/sysdba'\nstartup\n-------------------------------------\nSHUTDOWN NORMAL\uff1a\u4e0d\u5141\u8bb8\u65b0\u7684\u8fde\u63a5\u3001\u7b49\u5f85\u4f1a\u8bdd\u7ed3\u675f\u3001\u7b49\u5f85\u4e8b\u52a1\u7ed3\u675f\u3001\u505a\u4e00\u4e2a\u68c0\u67e5\u70b9\u5e76\u5173\u95ed\u6570\u636e\u6587\u4ef6\u3002\u542f\u52a8\u65f6\u4e0d\u9700\u8981\u5b9e\u4f8b\u6062\u590d\u3002\nSHUTDOWN TRANSACTIONAL\uff1a\u4e0d\u5141\u8bb8\u65b0\u7684\u8fde\u63a5\u3001\u4e0d\u7b49\u5f85\u4f1a\u8bdd\u7ed3\u675f\u3001\u7b49\u5f85\u4e8b\u52a1\u7ed3\u675f\u3001\u505a\u4e00\u4e2a\u68c0\u67e5\u70b9\u5e76\u5173\u95ed\u6570\u636e\u6587\u4ef6\u3002\u542f\u52a8\u65f6\u4e0d\u9700\u8981\u5b9e\u4f8b\u6062\u590d\u3002\nSHUTDOWN IMMEDIATE\uff1a\u4e0d\u5141\u8bb8\u65b0\u7684\u8fde\u63a5\u3001\u4e0d\u7b49\u5f85\u4f1a\u8bdd\u7ed3\u675f\u3001\u4e0d\u7b49\u5f85\u4e8b\u52a1\u7ed3\u675f\u3001\u505a\u4e00\u4e2a\u68c0\u67e5\u70b9\u5e76\u5173\u95ed\u6570\u636e\u6587\u4ef6\u3002\u6ca1\u6709\u7ed3\u675f\u7684\u4e8b\u52a1\u662f\u81ea\u52a8rollback\u7684\u3002\u542f\u52a8\u65f6\u4e0d\u9700\u8981\u5b9e\u4f8b\u6062\u590d\u3002\nSHUTDOWN ABORT\uff1a\u4e0d\u5141\u8bb8\u65b0\u7684\u8fde\u63a5\u3001\u4e0d\u7b49\u5f85\u4f1a\u8bdd\u7ed3\u675f\u3001\u4e0d\u7b49\u5f85\u4e8b\u52a1\u7ed3\u675f\u3001\u4e0d\u505a\u68c0\u67e5\u70b9\u4e14\u6ca1\u6709\u5173\u95ed\u6570\u636e\u6587\u4ef6\u3002\u542f\u52a8\u65f6\u81ea\u52a8\u8fdb\u884c\u5b9e\u4f8b\u6062\u590d\u3002\n----\n\u542f\u52a8\uff1a\nexport ORACLE_SID=\u5b9e\u4f8b\u540d\u79f0\nsqlplus 'as \/sysdba'\nstartup\n---------------------------------\n1\u3001--\u7edf\u8ba1\u6e38\u6807\u603b\u6570\nselect o.sid, osuser, PROCESS,PROGRAM, count(*) num_curs\nfrom v$open_cursor o, v$session s\nwhere osuser='lzm' and user_name = 'ZHZW' and o.sid=s.sid \ngroup by o.sid, osuser, PROCESS,PROGRAM\norder by  num_curs desc\n************************************************************************************* \n2\u3001--\u67e5\u770b\u67d0\u4e2a\u7a0b\u5e8f\u7684\u6240\u6709\u6e38\u6807\u8bed\u53e5\nselect SQL_TEXT\nfrom v$open_cursor o, v$session s\nwhere user_name = 'ZHZW' and o.sid=s.sid \nand PROCESS='2146392'\n************************************************************************************* \n3\u3001--\u77e5\u9053\u67d0\u4e00\u7279\u5b9asql\u67e5\u8be2\u6253\u5f00\u7684\u6e38\u6807\u6570\nselect a.sql_text, a.sql_id, b.count, to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from v$sqlarea a, \n(select sql_id, count(*) count from v$open_cursor group by sql_id) b \nwhere a.sql_text like 'select integralcyc%' and a.sql_id = b.sql_id;\n*************************************************************************************\n\n4\u3001\u6839\u636e\u7a0b\u5e8f\u540d\u67e5\u8be2\u6253\u5f00\u7684\u6e38\u6807\u6570\nselect p.SQL_TEXT,count(*) ,sysdate from v$session s ,V$OPEN_CURSOR p \nwhere s.SID=p.SID and s.program like'Date%' group by p.SQL_TEXT order by p.SQL_TEXT;\n*************************************************************************************\n5\u3001\u67e5\u8be2\u8868\u7684\u5206\u533a\uff1a\nselect *  from user_tab_partitions t  where t.table_name = 'HSC_REC_PRODUCT';\n*************************************************************************************\n6\u3001\u6dfb\u52a0\u5206\u533a\uff1a\nalter table HSC_REC_PRODUCT add PARTITION HSC_REC_PRODUCT16 values less than(TO_DATE('2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));\n*************************************************************************************\n\n7.\u6839\u636e\u8fdb\u7a0bPID\u53f7\u67e5\u627eSQL\uff1a\nselect sql_fulltext from V$sql a where a.sql_id=(select sql_id from V$session b where b.paddr=(select addr from V$process c where c.spid=\u8fdb\u7a0bID));\n\n\u83b7\u53d6\u5efa\u8868\u8bed\u53e5\uff1a\nselect to_char(dbms_metadata.get_ddl('TABLE','GDHSC_SUBS_PRODUCT')) from dual;\n\u83b7\u53d6\u5b58\u50a8\u8fc7\u7a0b\u5b9a\u4e49\uff1a\nselect text from user_source where type='PROCEDURE' and name='PROCEDURE_NAME';  \n\u89e6\u53d1\u5668\uff1a\nselect text from user_source where type='TRIGGER' and name='TRIGGER_NAME';  \n-- \u67e5\u770b\u89c6\u56fe\u7684\u5b9a\u4e49\u8bed\u53e5      \n --  select dbms_metadata.get_ddl('VIEW','V_CANSALETICKET') from dual\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
\u6027\u80fd\u6d4b\u8bd5\u4e2d\u5e38\u7528\u6570\u636e\u5e93\u64cd\u4f5c\u53ca\u6570\u636e\u5e93\u76d1\u63a7SQL\u603b\u7ed3 –by newhackerman \u76ee\u5f55 1.\u627e\u51fa\u957f\u65f6\u95f4\u6267\u884c\u7684SQL\u8bed\u53e5\uff1a 3 2.\u627e\u51fa\u4f7f\u7528CPU\u591a\u7684SQL\u8bed\u53e5\uff1a 4 3.\u67e5\u627e\u6ca1\u6709\u4fdd\u5b58\u5728library cache\u4e2d\u7684\u5927\u5bf9\u8c61\uff1a 4 4.\u8c03\u6574PGA\u4f18\u5316\u6392\u5e8f\uff1a 4 5.SQL\u7684\u547d\u4e2d\u7387\uff1a 5 7.\u56de\u6eda\u6bb5\u7684\u4e89\u7528\u60c5\u51b5\uff1a 6 \u76d1\u63a7\u6570\u636e\u5e93\u6027\u80fd\u7684\u8bed\u53e5 7 1. \u76d1\u63a7\u4e8b\u4f8b\u7684\u7b49\u5f85 7 2. \u56de\u6eda\u6bb5\u7684\u4e89\u7528\u60c5\u51b5 8 3. \u76d1\u63a7\u8868\u7a7a\u95f4\u7684 I\/O \u6bd4\u4f8b 8 4. \u76d1\u63a7\u6587\u4ef6\u7cfb\u7edf\u7684 I\/O \u6bd4\u4f8b 8 5.\u5728\u67d0\u4e2a\u7528\u6237\u4e0b\u627e\u6240\u6709\u7684\u7d22\u5f15 8 6. \u76d1\u63a7 SGA \u7684\u547d\u4e2d\u7387 9 7. \u76d1\u63a7 SGA \u4e2d\u5b57\u5178\u7f13\u51b2\u533a\u7684\u547d\u4e2d\u7387 9 8. \u76d1\u63a7 SGA \u4e2d\u5171\u4eab\u7f13\u5b58\u533a\u7684\u547d\u4e2d\u7387\uff0c\u5e94\u8be5\u5c0f\u4e8e1% 9 9. \u663e\u793a\u6240\u6709\u6570\u636e\u5e93\u5bf9\u8c61\u7684\u7c7b\u522b\u548c\u5927\u5c0f 10 11. \u76d1\u63a7\u5185\u5b58\u548c\u786c\u76d8\u7684\u6392\u5e8f\u6bd4\u7387\uff0c\u6700\u597d\u4f7f\u5b83\u5c0f\u4e8e .10\uff0c\u589e\u52a0 sort_area_size 10 12. \u76d1\u63a7\u5f53\u524d\u6570\u636e\u5e93\u8c01\u5728\u8fd0\u884c\u4ec0\u4e48SQL\u8bed\u53e5 11 13. \u76d1\u63a7\u5b57\u5178\u7f13\u51b2\u533a 11 14. \u627eORACLE\u5b57\u7b26\u96c6 11 15. \u76d1\u63a7 MTS 11 16. \u788e\u7247\u7a0b\u5ea6 12 \u67e5\u770b\u788e\u7247\u7a0b\u5ea6\u9ad8\u7684\u8868 12 17. \u8868\u3001\u7d22\u5f15\u7684\u5b58\u50a8\u60c5\u51b5\u68c0\u67e5 13 18\u3001\u627e\u4f7f\u7528CPU\u591a\u7684\u7528\u6237session 13 19.\u5bf9\u53ef\u7591\/\u6027\u80fd\u4e0d\u597d\u7684Server Process\u6765\u8fdb\u884cTrace.,\u53ef\u4ee5\u7528tkprof\u6765\u5206\u6790Trace\u7684\u7ed3\u679c. 14 20.\u67e5\u770bLock 15 \u8c03\u7528oracle\u987e\u95ee\uff0c\u5206\u6790SQL\u8bed\u53e5\uff0c\u8be5\u600e\u6837\u4f18\u5316 17 \u67e5\u770b\u9501\u8868\u7684\u65b9\u6cd5 19 \u67e5\u770bSGA\u533a\u5269\u4f59\u53ef\u7528\u5185\u5b58 19 \u67e5\u770b\u8868\u7684\u7d22\u5f15\uff1a 23 \u67e5\u770b\u5b58\u50a8\u8fc7\u7a0b\u53ca\u89e6\u53d1\u5668\u8fc7\u7a0b 23 \u67e5\u770b\u6307\u5b9a\u8868\u7a7a\u95f4\u7684\u7a7a\u95f4\u5927\u5c0f,\u8868\u7a7a\u95f4\u603b\u5927\u5c0fGB 23 \u67e5\u770b\u53ef\u7528\u8868\u7a7a\u95f4\u5927\u5c0f,\u8868\u7a7a\u95f4\u603b\u5927\u5c0fMB 23 \u6839\u636e\u6761\u4ef6\u9ad8\u6548\u5220\u9664\u91cd\u590d\u7684\u8bb0\u5f55\uff08\u4e00\u6b21\u53ea\u5220\u9664\u4e00\u4efd\uff1a\u5982\u76f8\u540c\u7684\u8bb0\u5f55\u6709\u4e24\u6761\uff0c\u4e00\u6b21\u53ea\u5220\u9664\u4e00\u6761\uff0c\u4fdd\u7559\u4e00\u6761\uff09 24 \u5907\u4efd\u4e00\u5f20\u8868\u7684\u6570\u636e\u5230\u4e00\u5f20\u65b0\u8868 24 \u8f93\u51faoracle\u6027\u80fd\u62a5\u544a(AWRRPT.sql AWR\u62a5\u544a) 25 \u542f\u505coracle\u5b9a\u65f6\u8c03\u5ea6\u4efb\u52a1 25 \u67e5\u770b\u56de\u6eda\u76f8\u5173\u7cfb\u7edf\u53c2\u6570 26 \u67e5\u770b\u54ea\u4e9bsession\u6b63\u5728\u4f7f\u7528\u54ea\u4e9b\u56de\u6eda\u6bb5 26 \u901a\u8fc7\u8868\u5206\u6790\u5224\u65ad\u7d22\u5f15\u662f\u5426\u5931\u6548\u53ca\u89e3\u51b3\u65b9\u6cd5 27 \u67e5\u770b\u6bcf\u5206\u949f\u5904\u7406\u8bb0\u5f55\u6570SQL 28 \u67e5\u770b\u672c\u7528\u6237\u4e0b\u7684\u5404\u79cd\u5bf9\u8c61\u7684SQL\u811a\u672c 28 \u627e\u51fa\u957f\u65f6\u95f4\u6267\u884c\u7684SQL\u8bed\u53e5\uff1a SELECT SE.SID, OPNAME, TRUNC(SOFAR \/ TOTALWORK * 100, 2) || '%' AS PCT_WORK, ELAPSED_SECONDS ELAPSED, ROUND(ELAPSED_SECONDS * (TOTALWORK – SOFAR) \/ SOFAR) REMAIN_TIME, SQL_TEXT FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE AND SL.SID = SE.SID AND SOFAR != TOTALWORK ORDER BY START_TIME\uff1b —————————————————————————————– \u627e\u51fa\u4f7f\u7528CPU\u591a\u7684SQL\u8bed\u53e5\uff1a \u67e5\u627e\u6307\u5b9aSPID\u6b63\u5728\u6267\u884c\u7684SQL\u8bed\u53e5\uff1a 1\uff0eSELECT P.pid pid,S.sid sid,P.spid spid,S.username username,S.osuser osname,P.serial# S_#,P.terminal,P.program program,P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+) AND P.spid LIKE 'SID'; 2\uff0e\u5728linux\u73af\u5883\u53ef\u4ee5\u901a\u8fc7ps\u67e5\u770b\u8fdb\u7a0b\u4fe1\u606f\u5305\u62ecpid,windows\u4e2d\u4efb\u52a1\u7ba1\u7406\u5668\u7684PID\u4e0ev$process\u4e2dpid\u4e0d\u80fd\u4e00\u4e00\u5bf9\u5e94\u3002 windows\u662f\u591a\u7ebf\u7a0b\u670d\u52a1\u5668,\u6bcf\u4e2a\u8fdb\u7a0b\u5305\u542b\u4e00\u7cfb\u5217\u7ebf\u7a0b\u3002\u8fd9\u70b9\u4e8eunix\u7b49\u4e0d\u540c\uff0cUnix\u6bcf\u4e2aOralce\u8fdb\u7a0b\u72ec\u7acb\u5b58\u5728\uff0c\u5728Nt\u4e0a\u6240\u6709\u7ebf\u7a0b\u7531Oralce \u8fdb\u7a0b\u884d\u751f\u3002 \u6307\u5b9aSID\u67e5\u770b\u6b63\u5728\u6267\u884c\u7684SQL\u8bed\u53e5\uff1a SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,S.osuser osname,P.serial#…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":"","footnotes":""},"categories":[88,82,101,80],"tags":[],"class_list":["post-1617","post","type-post","status-publish","format-standard","hentry","category-oracle","category-82","category-101","category-80"],"_links":{"self":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1617","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1617"}],"version-history":[{"count":1,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1617\/revisions"}],"predecessor-version":[{"id":1618,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1617\/revisions\/1618"}],"wp:attachment":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1617"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1617"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1617"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}