Transcript Document

Tuning PGA and Temporary Space
Copyright © 2006, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
• Diagnose PGA memory issues
• Size the PGA memory
• Diagnose temporary space issues
• Specify temporary tablespace parameters for
efficient operation
13-2
Copyright © 2006, Oracle. All rights reserved.
SQL Memory Usage
•
Memory-intensive SQL operators:
– Sort-based (sort, group-by, rollup, window, ...)
– Hash-join
– Bitmap operators (merge and inversion)
•
Concept of work area:
– Memory allocated by a memory-intensive operator
to process its input data
•
Performance impact of memory:
– Optimal: Input data fits into the work area (cache).
– One-pass: Perform one extra pass over input data.
– Multi-pass: Perform several extra passes over input
data.
13-3
Copyright © 2006, Oracle. All rights reserved.
Performance Impact
Response Time
M1-pass
Moptimal
.
Sort
.
Memory
M-pass
1-pass
optimal
Response Time
M1-pass
Moptimal
.
Hash-Join
.
Memory
M-pass
13-4
1-pass
optimal
Copyright © 2006, Oracle. All rights reserved.
Automatic PGA Memory
•
Dynamically adapts the SQL memory allocation based on:
– PGA memory available
– SQL operator needs
– System workload
•
Improves manageability:
– No need to set *_AREA_SIZE parameters
– DBA sets a memory target: PGA_AGGREGATE_TARGET
•
Improves performance:
– PGA memory is really returned to the OS.
– Memory is allocated to the operation to maximize
throughput.
– Maximize overall memory utilization by dynamically
adapting memory with workload variation.
– Operation adapts its memory usage during the execution.
13-5
Copyright © 2006, Oracle. All rights reserved.
SQL Memory Manager
V$SQL_WORKAREA_ACTIVE
V$SQL_WORKAREA
WP1
HJ
WP2
HJ
WP3
Local SMM
GB
Server-1
...
WP4
HJ
WP5
Server-n
13-6
WP3
WP2
WP4
PGA Memory
Usage
Statistics
PGA_AGGREGATE_TARGET
WP5
Local SMM
GB
WP1
V$PROCESS
V$PROCESS_MEMORY
V$PGASTAT
every 3s
Auto-Target
Computation
Aggregate
PGA
auto-target
Memory Bound
Computation
Global
memory
bound
SGA
V$PGASTAT
Copyright © 2006, Oracle. All rights reserved.
Global SMM (CKPT)
Configuring Automatic PGA Memory
•
PGA_AGGREGATE_TARGET:
– Specifies the target aggregate amount of PGA
memory available to the instance
– Can be dynamically modified at the instance level
– Examples: 100,000 KB; 2,500 MB; 50 GB
– Default value: 10 MB or 20% of the size of the SGA,
whichever is greater
•
WORKAREA_SIZE_POLICY:
– Optional
– Can be dynamically modified at the instance or
session level
– Allows you to fall back to static SQL memory
management for a particular session
13-8
Copyright © 2006, Oracle. All rights reserved.
Setting PGA_AGGREGATE_TARGET Initially
•
•
•
Leave 20% of the available memory to other
applications.
Leave 80% of memory to the Oracle instance.
For OLTP:
PGA_AGGREGATE_TARGET=(total_mem*80%)*20%
•
For DSS:
PGA_AGGREGATE_TARGET=(total_mem*80%)*50%
13-9
Copyright © 2006, Oracle. All rights reserved.
Monitoring SQL Memory Usage
V$SQL_WORKAREA
V$SQL
V$PGASTAT
V$SQL_PLAN
V$PROCESS_MEMORY
V$SQL_WORKAREA_ACTIVE
V$TEMPSEG_USAGE
V$SYSSTAT
V$SQL_WORKAREA_HISTOGRAMS
13-10
Copyright © 2006, Oracle. All rights reserved.
Monitoring SQL Memory Usage: Examples
SELECT sql_text,
1
sum(onepass_executions) onepass_cnt,
sum(multipasses_executions) mpass_cnt
FROM V$SQL s, V$SQL_WORKAREA wa
WHERE s.address = wa.address
GROUP BY sql_text
HAVING sum(onepass_executions+multipasses_executions)>0;
SELECT TO_NUMBER(DECODE(sid, 65535, NULL, sid)) sid,
operation_type
OPERATION,
TRUNC(expected_size/1024)
ESIZE,
TRUNC(actual_mem_used/1024)
MEM,
TRUNC(max_mem_used/1024)
MAXMEM,
number_passes
PASS,
TRUNC(tempseg_size/1024)
TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;
13-12
Copyright © 2006, Oracle. All rights reserved.
2
Tuning SQL Memory Usage
•
Determine the best PGA_AGGREGATE_TARGET value
by using:
– V$PGA_TARGET_ADVICE
– V$PGA_TARGET_ADVICE_HISTOGRAM
•
Monitor AWR reports/Statspack reports for:
– direct path read temp
– direct path write temp
13-13
Copyright © 2006, Oracle. All rights reserved.
PGA Target Advice Statistics
ESTD_PGA_CACHE_HIT_PERCENTAGE
•
V$PGA_TARGET_ADVICE predicts how cache hit
percentages shown in V$PGASTAT evolve.
STATISTICS_LEVEL must be set to at least TYPICAL.
13-14
V$PGA_TARGET_ADVICE
100
90
80
70
60
50
40
30
20
10
0
Over allocation
zone
•
Good
value
Current
value
ESTD_OVERALLOC_COUNT
PGA_TARGET_FACTOR
Copyright © 2006, Oracle. All rights reserved.
PGA Target Advice Histograms
•
•
13-15
V$PGA_TARGET_ADVICE_HISTOGRAM predicts how
histograms shown in V$SQL_WORKAREA_HISTOGRAM
evolve.
STATISTICS_LEVEL must be set to at least TYPICAL.
Copyright © 2006, Oracle. All rights reserved.
Automatic PGA and Enterprise Manager
13-16
Copyright © 2006, Oracle. All rights reserved.
Automatic PGA and AWR Reports
13-17
Copyright © 2006, Oracle. All rights reserved.
Temporary Tablespace Management:
Overview
•
Temporary data generated by a database include:
–
–
–
–
–
–
•
•
•
13-18
Bitmap merges
Hash-join
Bitmap index creation
Sort
Temporary LOBs
Global temporary tables
Data persists for the duration of a transaction or
session.
High concurrency of the space management
operation is critical.
Media and instance recovery is not required.
Copyright © 2006, Oracle. All rights reserved.
Temporary Tablespace: Best Practice
Using locally managed temporary tablespace:
• Allows high-concurrency space management
– At steady state, all space metadata is cached in
SGA.
– Operations are serialized by the SGA latch.
•
•
13-19
Allows faster writes to temp files. Redo generated
on temporary blocks is not written to disk.
Makes READ ONLY databases possible
Copyright © 2006, Oracle. All rights reserved.
Configuring Temporary Tablespace
•
•
Locally managed temporary tablespaces are
uniform-extent tablespaces.
1 MB to 10 MB extent size:
– For DSS, OLAP applications involving huge work
areas
– Large temporary LOBs are predominant.
•
64 KB or multiple less than 1 MB:
– Small global temporary tables are predominant.
– OLTP
•
13-20
Use V$TEMPSEG_USAGE to monitor space usage
and workload distribution.
Copyright © 2006, Oracle. All rights reserved.
Temporary Tablespace Group: Overview
•
•
Groups multiple temporary tablespaces
Characteristics:
–
–
–
–
At least one temporary tablespace
Same namespace as tablespaces
Created implicitly on first assignment
No explicit deletion
Default tablespace
EXAMPLE
13-22
Default temporary
tablespace group TEMP
Tablespace
TEMP1
…
Copyright © 2006, Oracle. All rights reserved.
Tablespace
TEMPn
Temporary Tablespace Group: Benefits
Enables a user to use multiple temporary tablespaces:
• Same user in multiple sessions
• One particular parallel operation
HR
Serial
Parallel
HR
Tablespace
TEMP1
Tablespace
TEMP2
Tablespace
TEMP3
Temporary tablespace group TEMP
13-23
Copyright © 2006, Oracle. All rights reserved.
Creating Temporary Tablespace Groups
13-24
Copyright © 2006, Oracle. All rights reserved.
Maintaining Temporary
Tablespace Groups
13-25
Copyright © 2006, Oracle. All rights reserved.
Data Dictionary Changes
SELECT group_name, tablespace_name
FROM DBA_TABLESPACE_GROUPS;
13-26
Copyright © 2006, Oracle. All rights reserved.
Monitoring Temporary Tablespace
•
Use V$TEMPSEG_USAGE to monitor space usage
and workload distribution:
SELECT session_num, username, segtype, blocks, tablespace
FROM
V$TEMPSEG_USAGE;
•
•
Use V$SORT_SEGMENT to determine space usage
percentage:
SELECT (s.tot_used_blocks/f.total_blocks)*100 as pctused
FROM (SELECT SUM(used_blocks) tot_used_blocks
FROM
V$SORT_SEGMENT
WHERE tablespace_name='TEMP') s,
(SELECT SUM(blocks) total_blocks
FROM
DBA_TEMP_FILES
WHERE tablespace_name='TEMP') f;
13-27
Copyright © 2006, Oracle. All rights reserved.
Practice Overview:
Tune PGA Memory
This practice covers the following topics:
• Enable Automatic PGA Memory
• Tune PGA_AGGREGATE_TARGET
•
13-28
Tune Temporary Tablespace Performance
Copyright © 2006, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Diagnose PGA memory issues
• Size the PGA memory
• Diagnose temporary space issues
• Specify temporary tablespace parameters for
efficient operation
13-29
Copyright © 2006, Oracle. All rights reserved.