Distance checker

1
Distance checker

• Make sure you see these
• select a value which is visible from last rows at
  18
• This is a positive highlighted value
• This is a negative highlighted value
• select a value which is visible from last rows at
  14
• This is a positive highlighted value
• This is a negative highlighted value

2
Working with Automatic PGA
All graphs are taken from the document SQL
Memory Management in Oracle9i from Benoît
Dageville and Mohamed Zait from Oracle Corporation
Christo Kutrovsky The Pythian Group 2005 Feb
3
What is PGA ?

• Program Global Area
• i.e. Process Global Area
• Why is PGA different from SGA
• What goes into PGA
• Sort
• Hash
• PL/SQL variables and cursor memory

4
Oracle memory model
P1
P2
PGA
BIG SORT
P3
cursors pl/sql var.
PGA
SORT
P4
HASH
cursors pl/sql var.
5
Virtual memory mapping
32/64 bit addressing space
0 gb
1 gb
2 gb
3 gb
4 gb
P1
P2
6
The need for more memory

• To make things go faster !
• The fast true parameter
• Not really
• More memory has different effects on sorts and
  hash joins

7
Effects on Sorting

• Sorting has ONLY 3 modes
• optimal or cache (all in memory)
• 1 pass (the entire result set is written once to
  disk)
• multi-pass (the entire result set is written
  multiple times to disk)

8
Sorting response time
Response time
1 pass
Optimal
Memory used
9
Crossing optimal gt 1 pass

• SQLgt select count() from (select from
  TBLSESSION t where rownumlt 920000 order by
  ses_ip)
• Executed in 27.559 seconds
• SQLgt select count() from (select from
  TBLSESSION t where rownumlt 930000 order by
  ses_ip)
• Executed in 27.82 seconds
• SQLgt select count() from (select from
  TBLSESSION t where rownumlt 940000 order by
  ses_ip)
• Executed in 35.451 seconds
• SQLgt select count() from (select from
  TBLSESSION t where rownumlt 950000 order by
  ses_ip)
• Executed in 36.652 seconds

10
Crossing optimal gt 1 pass

• select substr(sql_text,57,15) as sql,
  operation_type as op, operation_id as id, policy,
• round(estimated_optimal_size/1024/1024,2) as
  e_opt, round(estimated_onepass_size/1024/1024,2)
  as e_one,
• round(last_memory_used/1024/1024,2) as l_mem,
  last_execution as last,
• total_executions as tot, optimal_executions as
  opt, onepass_executions as one,
  multipasses_executions as mult,
• round(active_time/1000000,2) as sec,
  round(max_tempseg_size/1024/1024,2) as tmp_m,
  round(last_tempseg_size/1024/1024,2) as tmp_L
• from vsql_workarea swa, vsql sq
• where swa.address sq.address and swa.hash_value
  sq.hash_value
• and sql_text like 'select count() from ( select
  from TBLSESSION order by sql
• SQL OP ID E_OPT E_ONE L_MEM LAST TOT
  OPT ONE MULT SEC TMP_M TMP_L
• --------- ---- -- ----- ----- ------ ------- ---
  --- --- ---- ------ ----- -----
• lt920000 SORT 3 96.5 3.28 85.78 OPTIMAL 1
  1 0 0 26.62
• lt930000 SORT 3 97.56 3.3 86.72 OPTIMAL 1
  1 0 0 26.91
• lt940000 SORT 3 87.28 3.13 87.27 1 PASS 1
  0 1 0 34.32 80 80
• lt950000 SORT 3 88.22 3.15 87.27 1 PASS 1
  0 1 0 35.53 80 80

11
Other nuances of SORTs

• When sorting, you are sorting the ENTIRE result
  set. Thus you need as much memory as ALL your
  select columns
• So yes, by adding an extra field (even a fixed
  string) you could just pass the limit and switch
  to one-pass sort
• select is even worse

12
Affecting sort memory needs

• Adding a fixed string
• SQLgt select count() from (select t.,'extra'
  from TBLSESSION t where rownumlt920000 order by
  ses_ip)
• Executed in 33.398 seconds
• Narrowing the select list
• SQLgt select count() from (select ses_id,
  ses_start, ses_refkey from TBLSESSION t where
  rownumlt 950000 order by ses_ip)
• Executed in 26.868 seconds

13
Switching to multi-pass

• Multipass is a long way down the road, compared
  to optimal gt 1 pass
• TYPE POLICY SID A_SEC WSIZE EXP ACT MAX
  PASSES TEMP
• ---- ------ --- ------ ------ ----- ------ ------
  ------- ----
• SORT AUTO 299 813.77 77.98 77.98 78.01 87.27
  1 6750

14
Top N queries

• Remember top N queries?
• SQLgt select count() from (select from
  TBLSESSION t
• where rownumlt950000 order by ses_ip )
• where rownum lt1000
• Executed in 11.597 seconds
• TYPE POLICY SID A_SEC WSIZE EXP ACT MAX
  PASSES TEMP
• ---- ------ ---- ------ ----- ----- ---- -----
  ------ ----
• SORT AUTO 336 11.42 0.21 0.21 0.21 0.21
  0

15
Effect on HASH joins

• Hash joins have the same 3 modes optimal (cache,
  in memory), 1 pass and multi-pass
• Hash joins benefit from additional memory between
  optimal and one-pass execution
• That is, according to Oracle, in my testing there
  was no measurable benefit

16
(No Transcript)
17
Nuances HASH joins

• If the optimizer estimates it will go above the
  bound, it will use temp right away
• The estimated value is re-learned from the last
  execution
• Thus a 2nd execution, without any changes, may
  not use temp
• If the estimate is bypassed, then it will write
  to temp as needed, same way as a sort

18
Automatic PGA

• Now to the automatic word

19
(No Transcript)
20
(No Transcript)
21
Global memory manager

• Updates the global memory bound every 3 seconds
• Reacts to over allocation by lowering the bound
  further

22
Some facts

• Oracles SQL operators can adjust the memory
  consumption in mid-execution
• Sorts that switch from optimal to 1-pass will
  release the extra memory immediately (depending
  on disk speed)
• Background jobs processes will not free-up the
  virtual memory, if sufficient memory is available

23
(No Transcript)
24
Drift

• A drift of 10 is allowed
• When the limit is passed, a foreground bound
  recompute occurs

25
(No Transcript)
26
Monitoring PGA activity

• Overview
• Details
• Advisors

27
Monitoring - Overview

• SQLgt select from vpgastat
• NAME VALUE
  UNIT
• -------------------------------------- ----------
  --------
• aggregate PGA target parameter 2097152000
  bytes
• aggregate PGA auto target 1866138624
  bytes
• global memory bound 104857600
  bytes
• total PGA inuse 23664640
  bytes
• total PGA allocated 132822016
  bytes
• maximum PGA allocated 564828160
  bytes
• total freeable PGA memory 4194304
  bytes
• PGA memory freed back to OS 2165370847
  bytes
• total PGA used for auto workareas 0
  bytes
• maximum PGA used for auto workareas 111509504
  bytes
• total PGA used for manual workareas 0
  bytes
• maximum PGA used for manual workareas 529408
  bytes
• over allocation count 0
  
• bytes processed 5318923366
  bytes
• extra bytes read/written 2912501760
  bytes

28
Monitoring - Overview no bytes

• select name,round(value/1024/1024,1) as Mb from
  vpgastat where unit 'bytes'
• NAME MB
• -------------------------------------- ---------
• aggregate PGA target parameter 2000
• aggregate PGA auto target 1779.7
• global memory bound 100
• total PGA inuse 22.5
• total PGA allocated 126.7
• maximum PGA allocated 538.7
• total freeable PGA memory 4
• PGA memory freed back to OS 2065058.6
• total PGA used for auto workareas 0
• maximum PGA used for auto workareas 106.3
• total PGA used for manual workareas 0
• maximum PGA used for manual workareas 0.5
• bytes processed 507256.6
• extra bytes read/written 2777.6

29
Monitoring getting the details 1

• When the query is runningvsql_workarea_active
• select operation_type as type, policy, sid,
  round(active_time/1000000,2) as a_sec,
• round(work_area_size/1024/1024,2) as wsize,
  round(expected_size/1024/1024,2) as exp,
• round(actual_mem_used/1024/1024,2) as
  act,round(max_mem_used/1024/1024,2) as max,
• number_passes as passes, round(tempseg_size/1024/1
  024,2) as temp from vsql_workarea_active
• TYPE POLICY SID A_SEC WSIZE EXP ACT MAX
  PASSES TEMP
• ---- ------ --- ----- ----- ----- ----- -----
  ------ ----
• SORT AUTO 299 284.5 54.73 54.73 53.75 87.27
  1 3015

30
Monitoring getting the details 2

• When it has finished (or for another
  query)vsql_workarea
• select operation_type as op, operation_id as id,
  policy,
• round(estimated_optimal_size/1024/1024,2) as
  e_opt, round(estimated_onepass_size/1024/1024,2)
  as e_one,
• round(last_memory_used/1024/1024,2) as l_mem,
  last_execution as last,
• total_executions as tot, optimal_executions as
  opt, onepass_executions as one,
  multipasses_executions as mult,
• round(active_time/1000000,2) as sec,
  round(max_tempseg_size/1024/1024,2) as tmp_m,
  round(last_tempseg_size/1024/1024,2) as tmp_L
• from vsql_workarea where address
  '0000000381E23CF0' and hash_value '1505362365'
• OP ID POLICY E_OPT E_ONE L_MEM LAST
  TOT OPT ONE MULT SEC TMP_M TMP_L
• ---------- -- ------ ----- ----- ----- -------
  ---- ---- --- ---- ---- ----- -----
• SORT 1 AUTO 0.02 0.02 0.02 OPTIMAL
  227 227 0 0 0.02
• HASH-JOIN 2 AUTO 0.63 0.63 0.34 OPTIMAL
  227 227 0 0 0.02
• HASH-JOIN 9 AUTO 0.88 0.88 1.14 OPTIMAL
  146 146 0 0 0.01

31
Monitoring getting the details 3

• You can link workareas to steps from the
  EX.PLAN vsql_plan
• select rpad(' ', depth3)operation'
  'optionsnvl2(object_name, ' -gt
  ','')object_namedecode(search_columns,0,NULL,'
  ('search_columns')') as OP,cost, cardinality
  as CARD, bytes, id as "id",access_predicates as
  "ACCESS", filter_predicates as filter,round(temp_s
  pace/1024/1024) as TMP_MB,partition_start
  nvl2(partition_start, ' - ', '')partition_stop
  as P, partition_id, other, other_tag, cpu_cost,
  io_cost, distribution, object_owner,
  parent_id,optimizer from ( select from
  VSQL_PLAN where address hextoraw('0000000381E23
  CF0') and hash_value '1505362365' and
  child_number 0) t connect by prior id
  parent_id start with id 0 order by id,
  position
• OP
  id ACCESS TMP_MB
• --------------------------------------------------
  -- -- --------- ------
• SELECT STATEMENT
  0
• SORT ORDER BY
  1
• HASH JOIN
  2 "VID""VID"
• NESTED LOOPS
  3
• TABLE ACCESS INDEX ROWID -gt TAG_SYM
  4
• INDEX RANGE SCAN -gt TS_VSID (1)
  5 "VSID"1
• TABLE ACCESS BY INDEX ROWID -gt
  VEN_TAG 6
• INDEX UNIQUE SCAN -gt VT_TAG_PK (1)
  7 "TAG""TAG"
• VIEW
  8
• HASH JOIN
  9 ROWIDROWID
• INDEX FAST FULL SCAN -gt VI_NAME_PK
  10
• INDEX FAST FULL SCAN -gt VI_VID_UK
  11

32
Monitoring getting the details 4

• Summaries overall per workarea
  size vsql_workarea_histogram
• SELECT LOW_OPTIMAL_SIZE/1024/1024
  low_mb,(HIGH_OPTIMAL_SIZE1)/1024/1024 high_mb,
  optimal_executions,onepass_executions,multipasses_
  executions
• FROM vsql_workarea_histogram
• WHERE total_executions ! 0
• and (low_optimal_size/1024/1024 gt 8 or
  total_executions gt optimal_executions)
• LOW_MB HIGH_MB OPTIMAL_EXECUTIONS
  ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS
• ------ ------- ------------------
  ------------------ ----------------------
• 2 4 3073
  0 0
• 4 8 2033
  0 0
• 8 16 402
  0 0
• 16 32 1501
  55 0
• 32 64 147
  2295 0
• 64 128 0
  374 0
• 128 256 0
  28 0
• 256 512 0
  3 0
• 512 1024 0
  16 0
• 1024 2048 0
  1 0

33
Monitoring getting the details 5

• Summaries finding the bad ones
• select operation_type as op, operation_id as id,
  policy,
• round(estimated_optimal_size/1024/1024,2) as
  e_opt, round(estimated_onepass_size/1024/1024,2)
  as e_one,
• round(last_memory_used/1024/1024,2) as l_mem,
  last_execution as last,
• total_executions as tot, optimal_executions as
  opt, onepass_executions as one,
  multipasses_executions as mult,
• round(active_time/1000000,2) as sec,
  round(max_tempseg_size/1024/1024,2) as tmp_m,
  round(last_tempseg_size/1024/1024,2) as tmp_L
• from vsql_workarea where max_tempseg_size is not
  null
• OP ID POLICY E_OPT E_ONE L_MEM LAST TOT
  OPT ONE MULT SEC TMP_M TMP_L
• --------- -- ------- ----- ----- ----- ------ ---
  --- --- ---- ----- ----- -----
• HASH-JOIN 4 AUTO 47.91 4.09 36.03 1 PASS 158
  0 158 0 90.99 45 40

34
The Advisors been really lazy

• Similar to vdb_cache_advice vpga_target_advice
  Shows estimate temp IO usage
• vpga_target_advice_histogram can be used to
  estimate opt/1pass/multi executions for different
  workarea sizes

35
vpga_target_advice

• select round(pga_target_for_estimate/1024/1024)
  as est_mb,pga_target_factor as factor,
• round(bytes_processed/1024/1024) as
  p_mb,round(estd_extra_bytes_rw/1024/1024) as
  extra_mb,
• estd_pga_cache_hit_percentage as
  hit_ratio,estd_overalloc_count as est_over from
  vpga_target_advice
• EST_MB FACTOR P_MB EXTRA_MB HIT_RATIO
  EST_OVER
• ------ ------ ------ ---------- ----------
  ----------
• 125 0.125 446362 768675 37
  9797
• 250 0.25 446362 293602 60
  2075
• 500 0.5 446362 181392 71
  0
• 750 0.75 446362 176313 72
  0
• 1000 1 446362 136979 77
  0
• 1200 1.2 446362 136391 77
  0
• 1400 1.4 446362 136391 77
  0
• 1600 1.6 446362 136391 77
  0
• 1800 1.8 446362 136391 77
  0
• 2000 2 446362 136391 77
  0

36
(No Transcript)
37
To make it work

• And now what you need to make it work

38
Documented parameters

• pga_aggregate_target
• thats it

39
Before we continue

• The following slides will show undocumented
  parameters
• Do not use them, unless instructed by Oracle
  support
• Bugs you should be aware off

40
Patches/Bugs that are required

• 2805580 - corruption with auto PGA, 9.2.0.4
• 3130972 - allow more then 1gb per workarea
  9.2.0.5
• 3586008 - the above 2 (as they conflict)
• Check with Oracle what is required for your patch
  level

41
Maximum workarea size

• The maximum PGA workarea is hard limited to 5 of
  pga_aggregate_target or a max of 100Mb
• Can be controlled with _smm_max_size
• Value is in KB !

42
Maximum PGA per process

• One process can have many workareas
• Max total size can be controlled
  with_pga_max_size
• Value is in bytes, default 200Mb
• CAUTION virtual address space limitation on 32
  bit platforms

43
So whats possible?

• Put really BIG sorts in memory !
• A single workarea is still limited at 4gb
• In addition due to steps-allocations, its
  spilling much earlier, around 3.5 gb
• A process however is not limited to 4gb, at least
  on 64 bit system

44
The system

• Solaris with 64 bit Oracle
• 9.2.0.5
• 32 Gb RAM
• 16 Gb SGA
• pga_aggregate_target 8000m
• _pga_max_size 8000000000
• _smm_max_size 5000000

45
Monitoring The adjusted system

• SQLgt select name,round(value/1024/1024,1) as Mb
  from vpgastat where unit 'bytes'
• NAME
  MB
• ---------------------------------------
  ----------
• aggregate PGA target parameter
  8000
• aggregate PGA auto target
  7139.4
• global memory bound
  4882.8
• total PGA inuse
  67.4
• total PGA allocated
  2940.5
• maximum PGA allocated
  9344.4
• total freeable PGA memory
  2783.5
• PGA memory freed back to OS
  2590908.6
• total PGA used for auto workareas
  0
• maximum PGA used for auto workareas
  6903.9
• total PGA used for manual workareas
  0
• maximum PGA used for manual workareas
  0.5
• bytes processed
  13542884
• extra bytes read/written
  1034611.9

46
A big sort 1 - running

• select operation_type, policy, sid,
  round(active_time/1000000,2) as a_sec,
• round(work_area_size/1024/1024,2) as wsize,
  round(expected_size/1024/1024,2) as exp,
  round(actual_mem_used/1024/1024,2) as act,
• round(max_mem_used/1024/1024,2) as max,
  number_passes as passes, round(tempseg_size/1024/1
  024,2) as temp
• from vsql_workarea_active
• OPE POLICY SID A_SEC WSIZE EXP
  ACT MAX PASSES TEMP
• --- ------ ---- ------- -------- --------
  -------- -------- ------- -----
• SOR AUTO 26 148.66 3589.75 3589.75
  3108.88 3108.88 0

47
A big sort 2 - spilling

• select operation_type, policy, sid,
  round(active_time/1000000,2) as a_sec,
• round(work_area_size/1024/1024,2) as wsize,
  round(expected_size/1024/1024,2) as exp,
  round(actual_mem_used/1024/1024,2) as act,
• round(max_mem_used/1024/1024,2) as max,
  number_passes as passes, round(tempseg_size/1024/1
  024,2) as temp
• from vsql_workarea_active
• OPER POLICY SID A_SEC WSIZE EXP ACT
  MAX PASSES TEMP
• ---- ------ --- ------ ------- ----- -------
  ------- ------ ----
• SORT AUTO 26 246.38 3589.75 46.41 3589.75
  3589.75 0 1100

48
A big sort 3 - spilled (first fetch)

• select operation_type, policy, sid,
  round(active_time/1000000,2) as a_sec,
• round(work_area_size/1024/1024,2) as wsize,
  round(expected_size/1024/1024,2) as exp,
  round(actual_mem_used/1024/1024,2) as act,
• round(max_mem_used/1024/1024,2) as max,
  number_passes as passes, round(tempseg_size/1024/1
  024,2) as temp
• from vsql_workarea_active
• OPER POLICY SID A_SEC WSIZE EXP ACT MAX
  PASSES TEMP
• ---- ------ --- ------ ----- ---- ---- -------
  ------ ----
• SORT AUTO 26 378.28 3.28 3.28 3.52 3589.75
  1 2800

49
Is it seriously using 3.5gb of ram?

• Oh yes, even more !
• select round(pga_alloc_mem/1024/1024,1) as
  alloc_mb, round(pga_used_mem/1024/1024,1) as
  used_mb, round(pga_max_mem/1024/1024,1) as
  max_mb, pid, spid, serial, program, traceid,
  background, pga_freeable_mem
• from vprocess order by alloc_mb desc
• ALLOC_MB USED_MB MAX_MB SPID PROGRAM
  PGA_FREEABLE_MEM
• -------- ------- ------ ------ -------------------
  ----- ------
• 4787.8 4780.1 4787.8 16291 oracle_at_test (TNS
  V1-V3) 0
• 10.9 0.2 10.9 4320 oracle_at_test (DBW0)
  0
• 5.3 5.1 5.3 19355 oracle_at_test (TNS
  V1-V3) 0
• 5.3 5.1 5.3 19318 oracle_at_test (TNS
  V1-V3) 0
• 4.2 4.1 4.2 19312 oracle_at_test (NSV0)
  0
• 1.4 1.3 1.4 16251 oracle_at_test (TNS
  V1-V3) 65536
• 1 0.4 1 16241 oracle_at_test (TNS
  V1-V3) 524288

50
Checking doubts on the OS side

• I dont believe that, thats just oracle telling
  us
• /home/oraclegt pmap -xs 16291head
  
• 16291 oracleprod (LOCALNO)
  
• Address Kbytes RSS Anon
  Locked Pgsz Mode Mapped File
• 0000000100000000 2272 2272 -
  - 8K r-x-- oracle
• 0000000100238000 104 96 -
  - - r-x-- oracle
• 
  
• /home/oraclegt pmap -xs 16291tail
  
• 7FFFFFFF7FE00000 176 176 -
  - 8K r-x-- ld.so.1
• 7FFFFFFF7FF2C000 16 16 16
  - 8K rwx-- ld.so.1
• FFFFFFFF7FFE0000 128 128 128
  - 8K rw--- stack
• ---------------- ---------- ---------- --------
  --------
• total Kb 21931752 21904656 4907016
  16961536
• The above values are in Kb. 16 gb sga 5 gb
  anonymous memory, which is PGA total 21 gb
  resident in use

51
Not used doesnt mean returned

• select round(pga_alloc_mem/1024/1024,1) as
  alloc_mb, round(pga_used_mem/1024/1024,1) as
  used_mb, round(pga_max_mem/1024/1024,1) as
  max_mb, pid, spid, username, serial, program,
  round(pga_freeable_mem/1024/1024,1) as
  freeable_mb
• from vprocess order by alloc_mb desc
• ALLOC_MB USED_MB MAX_MB PID SPID USERNAME
  SERIAL PROGRAM FREEABLE_MB
• -------- ------- ------ --- ----- --------
  ------- ------------- -----------
• 4100 3.7 4787.8 25 16291 oracle
  2 (TNS V1-V3) 4096
• 10.9 0.2 10.9 3 4320 oracle
  1 (DBW0) 0
• 5.3 5.1 5.3 18 19355 oracle
  10 (TNS V1-V3) 0

52
Wrap-up what can be achieved

• Use more memory for sorting/hashing
• Yet controlled automatically to avoid swapping
• Resulting in improved response time and less disk
  IO
• Resulting in more IO capacity available for other
  tasks
• Basically fast true

53
The End

• Thank you,
• Questions?
• kutrovsky_at_pythian.com

Christo Kutrovsky The Pythian Group 2005 Feb

• http//www.pythian.com/documents/Working_with_Auto
  matic_PGA.ppt