Validation of Sort Sizes in an Oracle10g Database


Appendix B: Test Results form Serial Sort Tests



Download 3.53 Mb.
Page5/17
Date28.05.2018
Size3.53 Mb.
#50899
1   2   3   4   5   6   7   8   9   ...   17

Appendix B: Test Results form Serial Sort Tests

1 Gigabyte PGA_AGGREGATE_TARGET, Default _pga_max_size


SQL> 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
NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target big integer 1G
SQL> @get_undocs
Parameter Description Value

---------------------------------------- ------------------------- ----------

_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

of the PGA advice

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

SGA for shared server

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> /
4547752 rows selected.


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

0 redo size

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
203 rows selected.
SQL> spool off


Download 3.53 Mb.

Share with your friends:
1   2   3   4   5   6   7   8   9   ...   17




The database is protected by copyright ©ininet.org 2022
send message

    Main page