Validation of Sort Sizes in an Oracle10g Database
Appendix B: Test Results form Serial Sort Tests
Download 3.53 Mb.
|
Appendix B: Test Results form Serial Sort Tests1 Gigabyte PGA_AGGREGATE_TARGET, Default _pga_max_sizeSQL> show parameter sga NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 1G sga_target big integer 1G SQL> show parameter pga
------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 1G
---------------------------------------- ------------------------- ---------- _pga_large_extent_size PGA large extent size 1048576 _pga_max_size Maximum size of the PGA 209715200 memory for one process _smm_advice_enabled if TRUE, enable TRUE v$pga_advice _smm_advice_log_size overwrites default size 0 workarea history log _smm_auto_cost_enabled if TRUE, use the AUTO TRUE size policy cost functions _smm_auto_max_io_size Maximum IO size (in KB) 248 used by sort/hash-join in auto mode _smm_auto_min_io_size Minimum IO size (in KB) 56 used by sort/hash-join in auto mode _smm_bound overwrites memory manager 0 automatically computed bound
_smm_control provides controls on the 0 memory manager _smm_freeable_retain value in KB of the 5120 instance freeable PGA memory to retain _smm_max_size maximum work area size in 52428 auto mode (serial) _smm_min_size minimum work area size in 1024 auto mode _smm_px_max_size maximum work area size in 314571 auto mode (global) _smm_retain_size work area retain size in 0 sessions (0 for AUTO) _smm_trace Turn on/off tracing for 0 SQL memory manager 15 rows selected. SQL> set autotrace traceonly SQL> set timing on SQL> get test_serial 1 select * from test_pga 2* order by district_name,name SQL> /
Elapsed: 00:03:29.54 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=133048 Card=454775 2 Bytes=513895976) 1 0 SORT (ORDER BY) (Cost=133048 Card=4547752 Bytes=513895976) 2 1 TABLE ACCESS (FULL) OF 'TEST_PGA' (TABLE) (Cost=15683 Ca rd=4547752 Bytes=513895976) Statistics ---------------------------------------------------------- 609 recursive calls 49 db block gets 79730 consistent gets 89546 physical reads 253974127 bytes sent via SQL*Net to client 3335525 bytes received via SQL*Net from client 303185 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 4547752 rows processed SQL> spool off SQL> select * from test_results; SID WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED MAX_MEM_USED TEMPSEG_SIZE NOW ----- -------------- ------------- --------------- ------------ ------------ ------------ 623 47708160 6602752 47710208 47710208 4194304 050905105926 623 47708160 6602752 47710208 47710208 10485760 050905105927 623 47708160 6602752 47710208 47710208 16777216 050905105928 623 47708160 6602752 47710208 47710208 23068672 050905105930 623 47708160 6602752 47710208 47710208 28311552 050905105931 623 47708160 6602752 47710208 47710208 34603008 050905105932 623 47708160 6602752 47710208 47710208 40894464 050905105933 623 6602752 6602752 5587968 47710208 46137344 050905105934 623 6602752 4571136 4645888 47710208 51380224 050905105935 623 6602752 4571136 4047872 47710208 56623104 050905105936 623 6602752 4571136 4301824 47710208 62914560 050905105937 623 6094848 6094848 3064832 47710208 68157440 050905105938 623 6094848 6094848 5047296 47710208 73400320 050905105939 623 7618560 7618560 7316480 47710208 78643200 050905105940 623 7618560 7618560 7316480 47710208 84934656 050905105941 623 8634368 8634368 7382016 47710208 89128960 050905105942 623 8634368 8634368 8365056 47710208 95420416 050905105943 623 8634368 8634368 8111104 47710208 101711872 050905105944 623 8634368 8634368 8111104 47710208 106954752 050905105945 623 10158080 10158080 9364480 47710208 112197632 050905105946 623 10158080 10158080 9364480 47710208 118489088 050905105947 623 10158080 10158080 9618432 47710208 123731968 050905105948 623 10158080 10158080 9110528 47710208 128974848 050905105949 623 10158080 10158080 9110528 47710208 135266304 050905105950 623 11681792 11681792 10871808 47710208 140509184 050905105951 623 11681792 11681792 11379712 47710208 145752064 050905105952 623 11681792 11681792 11379712 47710208 152043520 050905105953 623 11681792 11681792 11125760 47710208 157286400 050905105954 623 11681792 11681792 11125760 47710208 163577856 050905105955 623 13205504 13205504 12444672 47710208 168820736 050905105956 623 13205504 13205504 12444672 47710208 175112192 050905105957 623 13205504 13205504 12444672 47710208 180355072 050905105958 623 13205504 13205504 12444672 47710208 186646528 050905105959 623 13205504 13205504 12444672 47710208 190840832 050905110000 623 13205504 13205504 12444672 47710208 197132288 050905110001 623 14729216 14729216 13952000 47710208 202375168 050905110002 623 14729216 14729216 13952000 47710208 208666624 050905110003 623 14729216 14729216 14459904 47710208 212860928 050905110005 623 14729216 14729216 14459904 47710208 219152384 050905110006 623 14729216 14729216 13698048 47710208 224395264 050905110007 623 14729216 14729216 13698048 47710208 230686720 050905110008 623 14729216 14729216 13698048 47710208 236978176 050905110009 623 16760832 16760832 16237568 47710208 241172480 050905110010 623 16760832 16760832 16237568 47710208 247463936 050905110011 623 16760832 16760832 16491520 47710208 252706816 050905110012 623 16760832 16760832 16491520 47710208 258998272 050905110013 623 16760832 16760832 16491520 47710208 265289728 050905110014 623 16760832 16760832 16491520 47710208 269484032 050905110015 623 16760832 16760832 16491520 47710208 275775488 050905110016 623 16760832 16760832 15729664 47710208 281018368 050905110017 623 16760832 16760832 15729664 47710208 286261248 050905110018 623 16760832 16760832 15729664 47710208 292552704 050905110019 623 18284544 18284544 17490944 47710208 296747008 050905110020 623 18284544 18284544 17490944 47710208 303038464 050905110021 623 18284544 18284544 17490944 47710208 309329920 050905110022 623 18284544 18284544 17490944 47710208 313524224 050905110023 623 18284544 18284544 17490944 47710208 319815680 050905110024 623 18284544 18284544 17490944 47710208 326107136 050905110025 623 18284544 18284544 17490944 47710208 330301440 050905110026 623 18284544 18284544 17490944 47710208 336592896 050905110027 623 18284544 18284544 17490944 47710208 341835776 050905110028 623 20824064 20824064 19792896 47710208 346030080 050905110029 623 20824064 20824064 19792896 47710208 352321536 050905110030 623 20824064 20824064 19792896 47710208 357564416 050905110031 623 20824064 20824064 17826816 47710208 360710144 050905110032 623 20824064 20824064 19792896 47710208 365953024 050905110033 623 20824064 20824064 19792896 47710208 372244480 050905110034 623 20824064 20824064 19792896 47710208 378535936 050905110035 623 20824064 20824064 20300800 47710208 382730240 050905110036 623 20824064 20824064 20300800 47710208 387973120 050905110037 623 20824064 20824064 20300800 47710208 394264576 050905110038 623 20824064 20824064 20300800 47710208 398458880 050905110039 623 20824064 20824064 20300800 47710208 404750336 050905110040 623 20824064 20824064 20300800 47710208 411041792 050905110041 623 22855680 22855680 20972544 47710208 415236096 050905110042 623 22855680 22855680 21824512 47710208 421527552 050905110044 623 22855680 22855680 21824512 47710208 426770432 050905110045 623 22855680 22855680 21824512 47710208 433061888 050905110046 623 22855680 22855680 21824512 47710208 437256192 050905110047 623 22855680 22855680 21824512 47710208 443547648 050905110048 623 22855680 22855680 21824512 47710208 449839104 050905110049 623 22855680 22855680 5964800 47710208 455081984 050905110050 623 22855680 22855680 21824512 47710208 460324864 050905110051 623 22855680 22855680 21824512 47710208 466616320 050905110052 623 22855680 22855680 21824512 47710208 471859200 050905110053 623 22855680 22855680 22586368 47710208 476053504 050905110054 623 22855680 22855680 22586368 47710208 482344960 050905110055 623 22855680 22855680 22586368 47710208 488636416 050905110056 623 22855680 22855680 22586368 47710208 494927872 050905110057 623 22855680 22855680 21824512 47710208 498073600 050905110058 623 22855680 22855680 21824512 47710208 504365056 050905110059 623 22855680 22855680 21824512 47710208 509607936 050905110100 623 22855680 22855680 21824512 47710208 514850816 050905110101 623 25395200 25395200 25142272 47710208 519045120 050905110102 623 25395200 25395200 25142272 47710208 525336576 050905110103 623 25395200 25395200 25142272 47710208 530579456 050905110104 623 25395200 25395200 25142272 47710208 536870912 050905110105 623 25395200 25395200 19055616 47710208 537919488 050905110106 623 25395200 25395200 24888320 47710208 542113792 050905110107 623 25395200 25395200 24888320 47710208 548405248 050905110108 623 25395200 25395200 24888320 47710208 554696704 050905110109 623 25395200 25395200 3605504 47710208 559939584 050905110110 623 25395200 25395200 24380416 47710208 562036736 050905110111 623 25395200 25395200 24380416 47710208 568328192 050905110112 623 25395200 25395200 24380416 47710208 573571072 050905110113 623 25395200 25395200 24380416 47710208 579862528 050905110114 623 25395200 25395200 11994112 47710208 583008256 050905110115 623 25395200 25395200 24380416 47710208 586153984 050905110116 623 25395200 25395200 24380416 47710208 592445440 050905110117 623 25395200 25395200 24380416 47710208 597688320 050905110118 623 25395200 25395200 24380416 47710208 603979776 050905110119 623 25395200 25395200 24888320 47710208 606076928 050905110120 623 25395200 25395200 24888320 47710208 611319808 050905110121 623 25395200 25395200 24888320 47710208 617611264 050905110123 623 25395200 25395200 24888320 47710208 623902720 050905110124 623 27934720 27934720 11584512 47710208 628097024 050905110125 623 27934720 27934720 12305408 47710208 633339904 050905110126 623 24379392 24379392 24641536 47710208 638582784 050905110127 623 24379392 24379392 24641536 47710208 638582784 050905110128 623 24379392 24379392 24641536 47710208 638582784 050905110129 623 24379392 24379392 24641536 47710208 638582784 050905110130 623 24379392 24379392 24641536 47710208 638582784 050905110131 623 24379392 24379392 24641536 47710208 638582784 050905110132 623 24379392 24379392 24641536 47710208 638582784 050905110133 623 24379392 24379392 24641536 47710208 638582784 050905110134 623 24379392 24379392 24641536 47710208 638582784 050905110135 623 24379392 24379392 24641536 47710208 638582784 050905110136 623 24379392 24379392 24641536 47710208 638582784 050905110137 623 24379392 24379392 24641536 47710208 638582784 050905110138 623 24379392 24379392 24641536 47710208 638582784 050905110139 623 24379392 24379392 24641536 47710208 638582784 050905110140 623 24379392 24379392 24641536 47710208 638582784 050905110141 623 24379392 24379392 24641536 47710208 638582784 050905110142 623 24379392 24379392 24641536 47710208 638582784 050905110143 623 24379392 24379392 24641536 47710208 638582784 050905110144 623 24379392 24379392 24641536 47710208 638582784 050905110145 623 24379392 24379392 24641536 47710208 638582784 050905110146 623 24379392 24379392 24641536 47710208 638582784 050905110147 623 24379392 24379392 24641536 47710208 638582784 050905110148 623 24379392 24379392 24641536 47710208 638582784 050905110149 623 24379392 24379392 24641536 47710208 638582784 050905110150 623 24379392 24379392 24641536 47710208 638582784 050905110151 623 24379392 24379392 24641536 47710208 638582784 050905110152 623 24379392 24379392 24641536 47710208 638582784 050905110153 623 24379392 24379392 24641536 47710208 638582784 050905110154 623 24379392 24379392 24641536 47710208 638582784 050905110155 623 24379392 24379392 24641536 47710208 638582784 050905110156 623 24379392 24379392 24641536 47710208 638582784 050905110157 623 24379392 24379392 24641536 47710208 638582784 050905110159 623 24379392 24379392 24641536 47710208 638582784 050905110200 623 24379392 24379392 24641536 47710208 638582784 050905110201 623 24379392 24379392 24641536 47710208 638582784 050905110202 623 24379392 24379392 24641536 47710208 638582784 050905110203 623 24379392 24379392 24641536 47710208 638582784 050905110204 623 24379392 24379392 24641536 47710208 638582784 050905110205 623 24379392 24379392 24641536 47710208 638582784 050905110206 623 24379392 24379392 24641536 47710208 638582784 050905110207 623 24379392 24379392 24641536 47710208 638582784 050905110208 623 24379392 24379392 24641536 47710208 638582784 050905110209 623 24379392 24379392 24641536 47710208 638582784 050905110210 623 24379392 24379392 24641536 47710208 638582784 050905110211 623 24379392 24379392 24641536 47710208 638582784 050905110212 623 24379392 24379392 24641536 47710208 638582784 050905110213 623 24379392 24379392 24641536 47710208 638582784 050905110214 623 24379392 24379392 24641536 47710208 638582784 050905110215 623 24379392 24379392 24641536 47710208 638582784 050905110216 623 24379392 24379392 24641536 47710208 638582784 050905110217 623 24379392 24379392 24641536 47710208 638582784 050905110218 623 24379392 24379392 24641536 47710208 638582784 050905110219 623 24379392 24379392 24641536 47710208 638582784 050905110220 623 24379392 24379392 24641536 47710208 638582784 050905110221 623 24379392 24379392 24641536 47710208 638582784 050905110222 623 24379392 24379392 24641536 47710208 638582784 050905110223 623 24379392 24379392 24641536 47710208 638582784 050905110224 623 24379392 24379392 24641536 47710208 638582784 050905110225 623 24379392 24379392 24641536 47710208 638582784 050905110226 623 24379392 24379392 24641536 47710208 638582784 050905110227 623 24379392 24379392 24641536 47710208 638582784 050905110228 623 24379392 24379392 24641536 47710208 638582784 050905110229 623 24379392 24379392 24641536 47710208 638582784 050905110230 623 24379392 24379392 24641536 47710208 638582784 050905110231 623 24379392 24379392 24641536 47710208 638582784 050905110232 623 24379392 24379392 24641536 47710208 638582784 050905110233 623 24379392 24379392 24641536 47710208 638582784 050905110234 623 24379392 24379392 24641536 47710208 638582784 050905110235 623 24379392 24379392 24641536 47710208 638582784 050905110236 623 24379392 24379392 24641536 47710208 638582784 050905110237 623 24379392 24379392 24641536 47710208 638582784 050905110239 623 24379392 24379392 24641536 47710208 638582784 050905110240 623 24379392 24379392 24641536 47710208 638582784 050905110241 623 24379392 24379392 24641536 47710208 638582784 050905110242 623 24379392 24379392 24641536 47710208 638582784 050905110243 623 24379392 24379392 24641536 47710208 638582784 050905110244 623 24379392 24379392 24641536 47710208 638582784 050905110245 623 24379392 24379392 24641536 47710208 638582784 050905110246 623 24379392 24379392 24641536 47710208 638582784 050905110247 623 24379392 24379392 24641536 47710208 638582784 050905110248 623 24379392 24379392 24641536 47710208 638582784 050905110249 623 24379392 24379392 24641536 47710208 638582784 050905110250 623 24379392 24379392 24641536 47710208 638582784 050905110251 623 24379392 24379392 24641536 47710208 638582784 050905110252 623 24379392 24379392 24641536 47710208 638582784 050905110253 623 24379392 24379392 24641536 47710208 638582784 050905110254
Download 3.53 Mb. Share with your friends:
|