Oracle Advisor \uad00\ub828 \uc624\ub77c\ud074 \uba54\ubaa8\ub9ac \uc870\ud68c SQL<\/p>\n
—\u00a0 PGA target Advsior \ubcf4\uae30<\/p>\n
— estd_overalloc_count \uac00 0\uc778 \uac00\uc7a5 \uc791\uc740 target_MB \uac00 \uc801\uc808\ud55c PGA_target \uc774 \ub41c\ub2e4.<\/p>\n
select ROUND(pga_target_for_estimate\/1024\/1024) TARGET_MB, estd_pga_cache_hit_percentage CACHE_HIT_PERC,estd_overalloc_count
\nfrom V$PGA_TARGET_ADVICE<\/p>\n
1 select ROUND(pga_target_for_estimate\/1024\/1024) TARGET_MB,\n 2 estd_pga_cache_hit_percentage CACHE_HIT_PERC,\n 3 estd_overalloc_count\n 4* from V$PGA_TARGET_ADVICE\nSQL> \/\n\n TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT\n---------- -------------- --------------------\n 28 94 223\n 55 97 107\n 110 99 0\n 165 100 0\n 220 100 0\n 264 100 0\n 308 100 0\n 352 100 0\n 396 100 0\n 440 100 0\n 660 100 0\n 880 100 0\n 1320 100 0\n 1760 100 0\n\n14 rows selected.\n<\/pre>\n— Tablespace\ubcc4 \uc0ac\uc6a9\ub7c9 \ubcf4\uae30<\/p>\n
select * from dba_tablespace_usage_metrics ;<\/p>\n
SQL> select * from dba_tablespace_usage_metrics ;\n\nTABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT\n------------------------------ ---------- --------------- ------------\nAMD_TXN_INDX_TS 128 4194302 .003051759\nAMD_TXN_TS 160 4194302 .003814699\nEXAMPLE 10080 4194302 .240326042\nSYSAUX 95432 4194302 2.27527727\nSYSTEM 89288 4194302 2.12879282\nTEMP 0 4194302 0\nUNDOTBS1 288 4194302 .006866458\nUSERS 528 4194302 .012588507\n\n8 rows selected.\n\nSQL>\n<\/pre>\n— Advisor \uc218\ud589 task\ubcf4\uae30<\/p>\n
select * from DBA_ADVISOR_TASKS order by execution_end desc ;<\/p>\n
select * from DBA_ADVISOR_TASKS order by execution_end desc ;<\/pre>\n— DB cache advisor \ubcf4\uae30<\/p>\n
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads<\/p>\n
FROM V$DB_CACHE_ADVICE<\/p>\n
WHERE name = ‘DEFAULT’<\/p>\n
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = ‘db_block_size’)<\/p>\n
AND advice_status = ‘ON’ ;<\/p>\n
1 SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads\n 2 FROM V$DB_CACHE_ADVICE\n 3 WHERE name = 'DEFAULT'\n 4 AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')\n 5* AND advice_status = 'ON'\nSQL> \/\n\nSIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS\n----------------- -------------------- ------------------------- -------------------\n 12 1473 3.9292 6446307\n 24 2946 2.4672 4047698\n 36 4419 1.8712 3069979\n 48 5892 1.5137 2483457\n 60 7365 1.3674 2243388\n 72 8838 1.2782 2097055\n 84 10311 1.2169 1996428\n 96 11784 1.1666 1913892\n 108 13257 1.1217 1840312\n 120 14730 1.076 1765383\n 132 16203 1.0347 1697640\n 144 17676 1 1640633\n 156 19149 .9623 1578808\n 168 20622 .9229 1514217\n 180 22095 .8915 1462560\n 192 23568 .8616 1413646\n 204 25041 .833 1366728\n 216 26514 .8061 1322461\n 228 27987 .7825 1283739\n 240 29460 .7415 1216529\n\n20 rows selected.\n<\/pre>\n— Redo advisor \ubcf4\uae30<\/p>\n
— Redo log advisor<\/p>\n
— init.ora\uc5d0 Fast_start_mttr_target\uc774 \uc9c0\uc815\ub418\uc5b4 \uc788\uc5b4\uc57c \ud55c\ub2e4.<\/p>\n
select target_mttr, estimated_mttr, writes_mttr, optimal_logfile_size from v$instance_recovery;<\/p>\n
SQL> select target_mttr, estimated_mttr, writes_mttr, optimal_logfile_size from v$instance_recovery;\n\nTARGET_MTTR ESTIMATED_MTTR WRITES_MTTR OPTIMAL_LOGFILE_SIZE\n----------- -------------- ----------- --------------------\n 0 14 0\n\n<\/pre>\n— Segment advisor \uc218\ud589 \ud754\uc801 \ubcf4\uae30<\/p>\n
SQL> select * from DBA_AUTO_SEGADV_SUMMARY order by start_time desc;\n\nAUTO_TASKID SNAPID SEGMENTS_SELECTED SEGMENTS_PROCESSED TABLESPACE_SELECTED TABLESPACE_PROCESSED RECOMMENDATIONS_COUNT START_TIME END_TIME\n----------- ---------- ----------------- ------------------ ------------------- -------------------- --------------------- --------------------------------------------------------------------------- ---------------------------------------------------------------------------\n 151 1951 0 6 0 0 0 09-APR-18 10.00.01.199458 PM 09-APR-18 10.00.09.011127 PM\n 150 1928 0 2 0 0 0 08-APR-18 10.08.08.235659 PM 08-APR-18 10.08.09.596020 PM\n 149 1924 0 4 0 0 0 08-APR-18 06.07.54.062494 PM 08-APR-18 06.07.55.448212 PM\n 148 1920 0 2 0 0 0 08-APR-18 02.07.40.162909 PM 08-APR-18 02.07.42.742713 PM\n 147 1916 0 0 0 0 0 08-APR-18 10.07.26.252358 AM 08-APR-18 10.07.28.196305 AM\n 146 1912 0 5 0 0 0 08-APR-18 06.00.02.534671 AM 08-APR-18 06.00.04.779501 AM\n 145 1904 0 4 0 0 0 07-APR-18 10.06.42.617316 PM 07-APR-18 10.06.44.426401 PM\n 144 1900 0 3 0 0 0 07-APR-18 06.06.28.142453 PM 07-APR-18 06.06.30.100061 PM\n 143 1896 0 2 1 1 0 07-APR-18 02.06.14.664473 PM 07-APR-18 02.06.22.068583 PM\n 142 1892 0 2 0 0 0 07-APR-18 10.06.00.846257 AM 07-APR-18 10.06.02.770400 AM\n 141 1887 0 4 0 0 0 07-APR-18 06.00.02.360853 AM 07-APR-18 06.00.05.444056 AM\n 140 1879 0 5 0 0 0 06-APR-18 10.00.02.069407 PM 06-APR-18 10.00.07.641728 PM\n 139 1855 0 5 0 0 0 05-APR-18 10.00.03.522520 PM 05-APR-18 10.00.07.597868 PM\n 138 1831 0 5 0 0 0 04-APR-18 10.00.03.682236 PM 04-APR-18 10.00.07.970287 PM\n 137 1807 0 5 0 0 0 03-APR-18 10.00.10.207422 PM 03-APR-18 10.00.28.156111 PM\n 136 1783 0 5 0 0 0 02-APR-18 10.00.03.339729 PM 02-APR-18 10.00.06.276546 PM\n\n16 rows selected.\n\nSQL>\n<\/pre>\n— Segment advisor \uc218\ud589 \uad8c\uace0 \ubcf4\uae30<\/p>\n
— reclaimable_space (tablespace level\ub85c \ud655\uc778 \ud558\uae30)<\/p>\n
SQL> select tablespace_name, allocated_space, reclaimable_space,\n trunc(reclaimable_space\/allocated_space,2)*100 reclaimable_pct\nfrom table(dbms_space.asa_recommendations('TRUE', 'TRUE', 'ALL'))\norder by reclaimable_pct desc;<\/pre>\n— Segment advisor \uc218\ud589\uad8c\uace0 \ubcf4\uae30 ( Table \ubcc4 )<\/p>\n
select trunc(reclaimable_space\/allocated_space,2) reclaimable_pct,<\/p>\n
trunc(reclaimable_space\/allocated_space,2) reclaim_raito,<\/p>\n
Segment_owner,segment_name,Segment_type,Partition_name, Allocated_space,<\/p>\n
Used_space,Reclaimable_space,Chain_rowexcess chian_ratio,substr(Recommendations,1,40) recommendations<\/p>\n
from table (dbms_space.asa_recommendations()) a<\/p>\n
order by trunc(reclaimable_space\/allocated_space,2) desc,to_number(reclaimable_space) desc ;<\/p>\n
SQL> l\n 1 select trunc(reclaimable_space\/allocated_space,2) reclaimable_pct,\n 2 trunc(reclaimable_space\/allocated_space,2) reclaim_raito,\n 3 Segment_owner,segment_name,Segment_type,Partition_name, Allocated_space,\n 4 Used_space,Reclaimable_space,Chain_rowexcess chian_ratio,substr(Recommendations,1,40) recommendations\n 5 from table (dbms_space.asa_recommendations()) a\n 6* order by trunc(reclaimable_space\/allocated_space,2) desc,to_number(reclaimable_space) desc<\/pre>\n— Auto sga\uc77c\uacbd\uc6b0, memory size \ubcc0\ub3d9 \uacb0\uacfc \ubcf4\uae30.<\/p>\n
select * from V$SGA_RESIZE_OPS;<\/p>\n
SQL> select * from V$SGA_RESIZE_OPS;\n\nCOMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME\n---------------------------------------------------------------- ------------- --------- -------------------------------------------------------------------------------- ------------ ----------- ---------- --------- ------------------ ------------------\nshared pool STATIC shared_pool_size 0 255852544 255852544 COMPLETE 05-MAR-18 05-MAR-18\nlarge pool STATIC large_pool_size 0 4194304 4194304 COMPLETE 05-MAR-18 05-MAR-18\njava pool STATIC java_pool_size 0 4194304 4194304 COMPLETE 05-MAR-18 05-MAR-18\nstreams pool STATIC streams_pool_size 0 8388608 8388608 COMPLETE 05-MAR-18 05-MAR-18\nDEFAULT buffer cache INITIALIZING db_cache_size 150994944 150994944 150994944 COMPLETE 05-MAR-18 05-MAR-18\nASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 05-MAR-18 05-MAR-18\nRECYCLE buffer cache STATIC db_recycle_cache_size 0 0 0 COMPLETE 05-MAR-18 05-MAR-18\nDEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 05-MAR-18 05-MAR-18\nDEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 05-MAR-18 05-MAR-18\nDEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 05-MAR-18 05-MAR-18\nDEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 05-MAR-18 05-MAR-18\nDEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 05-MAR-18 05-MAR-18\nKEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 05-MAR-18 05-MAR-18\nDEFAULT buffer cache STATIC db_cache_size 0 150994944 150994944 COMPLETE 05-MAR-18 05-MAR-18\n\n14 rows selected.\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Oracle Advisor \uad00\ub828 \uc624\ub77c\ud074 \uba54\ubaa8\ub9ac \uc870\ud68c SQL —\u00a0 PGA target Advsior \ubcf4\uae30 — estd_overalloc_count \uac00 0\uc778 \uac00\uc7a5 \uc791\uc740 target_MB \uac00 \uc801\uc808\ud55c PGA_target \uc774 \ub41c\ub2e4. select ROUND(pga_target_for_estimate\/1024\/1024) TARGET_MB, estd_pga_cache_hit_percentage CACHE_HIT_PERC,estd_overalloc_count from V$PGA_TARGET_ADVICE 1 select ROUND(pga_target_for_estimate\/1024\/1024) TARGET_MB, 2 estd_pga_cache_hit_percentage CACHE_HIT_PERC, 3 estd_overalloc_count 4* from V$PGA_TARGET_ADVICE SQL> \/ TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT ———- ————– ——————– 28 94 223 55 […]<\/p>\n","protected":false},"author":1,"featured_media":3402,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[1069,1070,1068],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/1-know-how-1.jpg?fit=937%2C450","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3763"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3763"}],"version-history":[{"count":6,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3763\/revisions"}],"predecessor-version":[{"id":4506,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3763\/revisions\/4506"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/3402"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3763"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3763"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3763"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}