{"id":154,"date":"2011-09-25T23:51:44","date_gmt":"2011-09-25T15:51:44","guid":{"rendered":"https:\/\/yangtingkun.net\/?p=154"},"modified":"2011-09-26T00:45:32","modified_gmt":"2011-09-25T16:45:32","slug":"%e8%bf%9e%e6%8e%a5%e7%94%a8%e6%88%b7%e8%8e%b7%e5%8f%96%e6%9d%83%e9%99%90%e4%bf%a1%e6%81%af","status":"publish","type":"post","link":"https:\/\/yangtingkun.net\/?p=154","title":{"rendered":"\u8fde\u63a5\u7528\u6237\u83b7\u53d6\u6743\u9650\u4fe1\u606f"},"content":{"rendered":"

\u6709\u5ba2\u6237\u5bf9AWR\u4e2d\u62a5\u544a\u4e2d\u51fa\u73b0\u7684\u4e00\u4e2a\u67e5\u8be2GLOBAL_NAME\u7684SQL\u5b58\u5728\u7591\u95ee\uff0c\u8be6\u7ec6\u5206\u6790\u540e\u53d1\u73b0\u662f\u5ba2\u6237\u7aef\u5efa\u7acb\u8fde\u63a5\u540e\u83b7\u53d6\u6743\u9650\u4fe1\u606f\u7684\u8bed\u53e5\u3002<\/p>\n

\u5ba2\u6237\u5b58\u5728\u7591\u95ee\u7684SQL\u662f\uff1a<\/p>\n

select value$ from props$ where\r\nname = 'GLOBAL_DB_NAME'<\/pre>\n
\u800c\u8fd9\u4e2aSQL\u51fa\u73b0\u5728AWR\u62a5\u544a\u4e2d\u6267\u884c\u6b21\u6570\u90e8\u5206\uff0c\u53ef\u4ee5\u770b\u5230\u8fd9\u4e2aSQL\u8fd0\u884c\u6b21\u6570\u63a5\u8fd126W\u6b21\u3002<\/p>\n\n\n\n\n\n\n\n\n
\n
Executions <\/strong><\/p>\n<\/td>\n
\n
Rows Processed<\/strong><\/p>\n<\/td>\n
\n
Rows per Exec<\/strong><\/p>\n<\/td>\n
\n
Elapsed Time (s)<\/strong><\/p>\n<\/td>\n
\n
%CPU<\/strong><\/p>\n<\/td>\n
\n
%IO<\/strong><\/p>\n<\/td>\n
\n
SQL Id<\/strong><\/p>\n<\/td>\n
\n
SQL Module<\/strong><\/p>\n<\/td>\n
\n
SQL Text<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
546,016<\/p>\n<\/td>\n
\n
8,942,073<\/p>\n<\/td>\n
\n
16.38<\/p>\n<\/td>\n
\n
170.26<\/p>\n<\/td>\n
\n
28.28<\/p>\n<\/td>\n
\n
0.00<\/p>\n<\/td>\n
\n
cm5vu20fhtnq1<\/span><\/p>\n<\/td>\n
<\/td>\n\n
select \/*+
\nconnect_by_filterin…<\/p>\n<\/td>\n<\/tr>\n
\n
270,653<\/p>\n<\/td>\n
\n
545,943<\/p>\n<\/td>\n
\n
2.02<\/p>\n<\/td>\n
\n
125.71<\/p>\n<\/td>\n
\n
16.34<\/p>\n<\/td>\n
\n
0.00<\/p>\n<\/td>\n
\n
0k8522rmdzg4k<\/a><\/p>\n<\/td>\n
<\/td>\n\n
select privilege#
\nfrom sysauth…<\/p>\n<\/td>\n<\/tr>\n
\n
259,736<\/p>\n<\/td>\n
\n
259,753<\/p>\n<\/td>\n
\n
1.00<\/p>\n<\/td>\n
\n
64.03<\/p>\n<\/td>\n
\n
45.20<\/p>\n<\/td>\n
\n
0.00<\/p>\n<\/td>\n
\n
459f3z9u4fb3u<\/a><\/p>\n<\/td>\n
<\/td>\n\n
select value$
\nfrom props$ wher…<\/p>\n<\/td>\n<\/tr>\n
\n
259,720<\/p>\n<\/td>\n
\n
259,716<\/p>\n<\/td>\n
\n
1.00<\/p>\n<\/td>\n
\n
35.67<\/p>\n<\/td>\n
\n
56.40<\/p>\n<\/td>\n
\n
0.00<\/p>\n<\/td>\n
\n
5ur69atw3vfhj<\/a><\/p>\n<\/td>\n
<\/td>\n\n
select
\ndecode(failover_method,…<\/p>\n<\/td>\n<\/tr>\n
\n
259,660<\/p>\n<\/td>\n
\n
259,715<\/p>\n<\/td>\n
\n
1.00<\/p>\n<\/td>\n
\n
121.55<\/p>\n<\/td>\n
\n
50.67<\/p>\n<\/td>\n
\n
0.00<\/p>\n<\/td>\n
\n
0ws7ahf1d78qa<\/a><\/p>\n<\/td>\n
<\/td>\n\n
select
\nSYS_CONTEXT(‘USERENV’, …<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u51fa\u73b0\u5728\u8fd9\u4e2a\u62a5\u544a\u4e2d\u524d\u51e0\u4f4d\u7684SQL\u9664\u4e86\u7b2c\u4e00\u4e2a\u4ee5\u5916\uff0c\u5269\u4e0b\u7684\u6267\u884c\u6b21\u6570\u57fa\u672c\u4e0a\u4e00\u81f4\uff0c\u800c\u7b2c\u4e00\u4e2a\u663e\u7136\u662f\u5176\u4ed6\u76842\u500d\uff0c\u90a3\u4e48\u8fd9\u663e\u7136\u4e0d\u662f\u4e00\u4e2a\u5de7\u5408\u3002<\/p>\n
\u6839\u636e\u62a5\u544a\u7684\u8fd0\u884c\u65f6\u95f4\uff1a<\/p>\n\n\n\n\n\n\n
<\/td>\n\n
Snap Id<\/strong><\/p>\n<\/td>\n
\n
Snap Time<\/strong><\/p>\n<\/td>\n
\n
Sessions<\/strong><\/p>\n<\/td>\n
\n
Cursors\/Session<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
Begin Snap:<\/p>\n<\/td>\n
\n
661<\/p>\n<\/td>\n
\n
14-Sep-11
\n13:00:13<\/p>\n<\/td>\n
\n
318<\/p>\n<\/td>\n
\n
1.6<\/p>\n<\/td>\n<\/tr>\n
\n
End Snap:<\/p>\n<\/td>\n
\n
666<\/p>\n<\/td>\n
\n
14-Sep-11
\n18:00:52<\/p>\n<\/td>\n
\n
375<\/p>\n<\/td>\n
\n
1.8<\/p>\n<\/td>\n<\/tr>\n
\n
Elapsed:<\/p>\n<\/td>\n
<\/td>\n\n
300.64 (mins)<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u4ee5\u53ca\u6bcf\u79d2\u767b\u5f55\u6570\u636e\u5e93\u7684\u6b21\u6570\uff1a<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
<\/td>\n\n
Per Second<\/strong><\/p>\n<\/td>\n
\n
Per Transaction<\/strong><\/p>\n<\/td>\n
\n
Per Exec<\/strong><\/p>\n<\/td>\n
\n
Per Call<\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
DB Time(s):<\/p>\n<\/td>\n
\n
3.1<\/p>\n<\/td>\n
\n
0.7<\/p>\n<\/td>\n
\n
0.01<\/p>\n<\/td>\n
\n
0.01<\/p>\n<\/td>\n<\/tr>\n
\n
DB CPU(s):<\/p>\n<\/td>\n
\n
2.4<\/p>\n<\/td>\n
\n
0.6<\/p>\n<\/td>\n
\n
0.01<\/p>\n<\/td>\n
\n
0.01<\/p>\n<\/td>\n<\/tr>\n
\n
Redo size:<\/p>\n<\/td>\n
\n
17,771.2<\/p>\n<\/td>\n
\n
4,136.4<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
Logical reads:<\/p>\n<\/td>\n
\n
70,747.7<\/p>\n<\/td>\n
\n
16,467.1<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
Block changes:<\/p>\n<\/td>\n
\n
103.0<\/p>\n<\/td>\n
\n
24.0<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
Physical reads:<\/p>\n<\/td>\n
\n
156.0<\/p>\n<\/td>\n
\n
36.3<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
Physical writes:<\/p>\n<\/td>\n
\n
49.0<\/p>\n<\/td>\n
\n
11.4<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
User calls:<\/p>\n<\/td>\n
\n
440.1<\/p>\n<\/td>\n
\n
102.4<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
Parses:<\/p>\n<\/td>\n
\n
264.6<\/p>\n<\/td>\n
\n
61.6<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
Hard parses:<\/p>\n<\/td>\n
\n
49.1<\/p>\n<\/td>\n
\n
11.4<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
W\/A MB processed:<\/p>\n<\/td>\n
\n
25.7<\/p>\n<\/td>\n
\n
6.0<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
Logons:<\/p>\n<\/td>\n
\n
15.2<\/p>\n<\/td>\n
\n
3.5<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
Executes:<\/p>\n<\/td>\n
\n
262.3<\/p>\n<\/td>\n
\n
61.0<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
Rollbacks:<\/p>\n<\/td>\n
\n
0.0<\/p>\n<\/td>\n
\n
0.0<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/tr>\n
\n
Transactions:<\/p>\n<\/td>\n
\n
4.3<\/p>\n<\/td>\n
<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u8ba1\u7b97\u6570\u636e\u5e93\u4e2d\u8fd9\u6bb5\u65f6\u95f4\u5185\u767b\u5f55\u603b\u6b21\u6570\uff1a<\/p>\n
SQL> select 300.64*60*15.2 from dual;\r\n\r\n300.64*60*15.2\r\n\r\n--------------\r\n\r\n274183.68<\/pre>\n
\u663e\u7136\u5305\u62ecSQL\uff1aselect value$ from props$ where name = ‘GLOBAL_DB_NAME’\u5728\u5185\u7684\u8fd9\u4e9b\u6267\u884c\u6b21\u6570\u6700\u591a\u7684SQL\uff0c\u57fa\u672c\u4e0a\u90fd\u662f\u6bcf\u767b\u5f55\u4e00\u6b21\u5c31\u8fd0\u884c\u4e00\u6b21\uff0c\u800c\u5bf9\u4e8eSQL_ID\u4e3acm5vu20fhtnq1\u7684\u8bed\u53e5\uff0c\u6bcf\u6b21\u767b\u5f55\u9700\u8981\u8fd0\u884c\u4e24\u6b21\u3002<\/p>\n
\u8fd9\u4e2aSQL\u5c31\u662fOracle\u5728\u767b\u5f55\u9636\u6bb5\u83b7\u53d6\u6743\u9650\u548c\u8fde\u63a5\u4fe1\u606f\u7684SQL\uff0c\u800c\u4e14\u6839\u636eSQL\u8bed\u53e5\u5728metalink\u4e0a\u6587\u6863ID
\n730066.1\u4e2d\u4e5f\u627e\u5230\u7684\u5b98\u65b9\u7684\u8bf4\u660e\uff1a<\/p>\n
select value$ from\r\nprops$ where name = 'GLOBAL_DB_NAME'\r\n\r\nselect\r\nprivilege#,level from sysauth$ connect by grantee#=prior privilege#\r\n\r\nand privilege#>0 start with grantee#=:1 and privilege#>0\r\n\r\nselect\r\nSYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'),\r\n\r\nSYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV',\r\n'SERVICE_NAME'),\r\n\r\nINSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN')\r\n\r\nfrom v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')\r\n\r\nselect privilege#\r\nfrom sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0\r\n\r\nALTER SESSION SET\r\nNLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$'\r\n\r\nNLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR=\r\n'GREGORIAN'\r\n\r\nNLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY'\r\nTIME_ZONE= '+02:00'\r\n\r\nNLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '$' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM'\r\nNLS_TIMESTAMP_FORMAT=\r\n\r\n'DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT= 'HH.MI.SSXFF AM TZR'\r\nNLS_TIMESTAMP_TZ_FORMAT=\r\n\r\n'DD-MON-RR HH.MI.SSXFF AM TZR'<\/pre>\n
\u8fd9\u662f10g\u73af\u5883\u4e2dOracle\u5728\u767b\u5f55\u9636\u6bb5\u4f1a\u6267\u884c\u7684SQL\u8bed\u53e5\uff0c\u53ef\u4ee5\u770b\u5230\u5728\u5f53\u524d11.2.0.2\u73af\u5883\u4e2d\uff0c\u8fd9\u4e9bSQL\u5927\u90e8\u5206\u6ca1\u6709\u6539\u53d8\u53ea\u662f\u53bb\u6389\u4e86ALTER SESSION\u8bed\u53e5\uff0c\u53d6\u4ee3\u5176\u7684\u662f\u4e00\u4e2a\u67e5\u8be2SERVICE\u7684\u8bed\u53e5\uff0c\u6b64\u5916\u5176\u4e2d\u4e00\u4e2a\u589e\u52a0\u4e86HINT\u3002\u5728AWR\u62a5\u544a\u4e2d\uff0c\u8fd9\u4e9bSQL\u5b8c\u6574\u4fe1\u606f\u4e3a\uff1a<\/p>\n\n\n\n\n\n\n\n
\n
cm5vu20fhtnq1<\/p>\n<\/td>\n
\n
select \/*+ connect_by_filtering *\/ privilege#, level
\nfrom sysauth$ connect by grantee#=prior privilege# and privilege#>0 start
\nwith grantee#=:1 and privilege#>0<\/p>\n<\/td>\n<\/tr>\n
\n
0k8522rmdzg4k<\/p>\n<\/td>\n
\n
select privilege# from sysauth$ where (grantee#=:1 or
\ngrantee#=1) and privilege#>0<\/p>\n<\/td>\n<\/tr>\n
\n
459f3z9u4fb3u<\/p>\n<\/td>\n
\n
select value$ from props$ where name = ‘GLOBAL_DB_NAME’<\/p>\n<\/td>\n<\/tr>\n
\n
5ur69atw3vfhj<\/p>\n<\/td>\n
\n
select decode(failover_method, NULL, 0 , ‘BASIC’, 1, ‘PRECONNECT’,
\n2 , ‘PREPARSE’, 4 , 0), decode(failover_type, NULL, 1 , ‘NONE’, 1 ,
\n‘SESSION’, 2, ‘SELECT’, 4, 1), failover_retries, failover_delay, flags from
\nservice$ where name = :1<\/p>\n<\/td>\n<\/tr>\n
\n
0ws7ahf1d78qa<\/p>\n<\/td>\n
\n
select SYS_CONTEXT(‘USERENV’, ‘SERVER_HOST’), SYS_CONTEXT(‘USERENV’,
\n‘DB_UNIQUE_NAME’), SYS_CONTEXT(‘USERENV’, ‘INSTANCE_NAME’),
\nSYS_CONTEXT(‘USERENV’, ‘SERVICE_NAME’), INSTANCE_NUMBER, STARTUP_TIME,
\nSYS_CONTEXT(‘USERENV’, ‘DB_DOMAIN’) from v$instance where
\nINSTANCE_NAME=SYS_CONTEXT(‘USERENV’, ‘INSTANCE_NAME’)<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u5bf9\u4e8e\u8fd9\u79cd\u5927\u6570\u636e\u91cf\u51fa\u73b0\u7684SQL\u8bed\u53e5\uff0c\u591a\u534a\u90fd\u548cOracle\u5185\u90e8\u7684\u9012\u5f52\u8c03\u7528\u6709\u5173\uff0c\u53ea\u8981\u4ed4\u7ec6\u5206\u6790\u5c31\u4e0d\u96be\u627e\u5230\u5176\u51fa\u5904\u3002<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
\u6709\u5ba2\u6237\u5bf9AWR\u4e2d\u62a5\u544a\u4e2d\u51fa\u73b0\u7684\u4e00\u4e2a\u67e5\u8be2GLOBAL_NAME\u7684SQL\u5b58\u5728\u7591\u95ee\uff0c\u8be6\u7ec6\u5206\u6790\u540e\u53d1\u73b0\u662f\u5ba2\u6237\u7aef\u5efa\u7acb\u8fde\u63a5\u540e\u83b7\u53d6\u6743\u9650\u4fe1\u606f\u7684\u8bed\u53e5\u3002 \u5ba2\u6237\u5b58\u5728\u7591\u95ee\u7684SQL\u662f\uff1a select value$ from props$ where name = ‘GLOBAL_DB_NAME’ \u800c\u8fd9\u4e2aSQL\u51fa\u73b0\u5728AWR\u62a5\u544a\u4e2d\u6267\u884c\u6b21\u6570\u90e8\u5206\uff0c\u53ef\u4ee5\u770b\u5230\u8fd9\u4e2aSQL\u8fd0\u884c\u6b21\u6570\u63a5\u8fd126W\u6b21\u3002 Executions Rows Processed Rows per Exec Elapsed Time (s) %CPU %IO SQL Id SQL Module SQL Text 546,016 8,942,073 16.38 170.26 28.28 0.00 cm5vu20fhtnq1 select \/*+ connect_by_filterin… 270,653 545,943 … 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":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3],"tags":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2yiY3-2u","_links":{"self":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/154"}],"collection":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=154"}],"version-history":[{"count":1,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/154\/revisions"}],"predecessor-version":[{"id":155,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/154\/revisions\/155"}],"wp:attachment":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=154"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}