9
Copyright © Oracle Corporation, 2002. All rights reserved.
Optimizing Sort Operations
9-2
Copyright © Oracle Corporation, 2002. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
•
List the operations that use temporary space
•
Create and monitor temporary tablespaces
•
Identify actions that use the temporary tablespace
•
Describe the use of disk and memory for sorting
•
Identify the SQL operations that require sorts
•
Differentiate between disk and memory sorts
•
List ways to reduce total sorts and disk sorts
•
Determine the number of memory sorts performed
•
Set parameters to optimize sorts
9-3
Copyright © Oracle Corporation, 2002. All rights reserved.
Automatic Sort Area Management
•
Parameters for automatic sort area management:

–
PGA_AGGREGATE_TARGET
(Ranges from 10 MB to 4000 GB)
–
WORKAREA_SIZE_POLICY
–
AUTO | MANUAL
•
Replaces all *_AREA_SIZE parameters
9-4
Copyright © Oracle Corporation, 2002. All rights reserved.
PGA Management Resources
Statistics to manage the PGA_AGGREGATE_TARGET
initialization parameter
•
Views for monitoring the PGA work area include:
–
v$sql_workarea_histogram
–
v$pgastat
–
v$sql_workarea_active
–
v$sql_workarea
–
v$tempseg_usage
•
Views to assist in sizing the PGA work area are:
–
v$pga_target_advice

–
v$pga_target_advice_histogram
9-5
Copyright © Oracle Corporation, 2002. All rights reserved.
Work Area Groups and PGA Cache
Hit Percentages
… 64-128KB
Percent
executions
100%
0% 0%
128-256KB …
94%
6%
0%
97%
3%0%
64-128MB
Optimal size
One-pass size
Multi-pass size
9-6
Copyright © Oracle Corporation, 2002. All rights reserved.
Determining PGA Workload
SQL> SELECT low_optimal_size/1024 AS low_kb,
2 (high_optimal_size+1)/1024 AS high_kb,
3 ROUND(100*optimal_executions
4 /total_executions) AS optimal,
5 ROUND(100*onepass_executions
6 /total_executions) AS onepass,

7 ROUND(100*multipasses_executions
8 /total_executions) AS multipass
9 FROM v$sql_workarea_histogram
10 WHERE total_executions != 0
11 ORDER BY low_kb;
9-7
Copyright © Oracle Corporation, 2002. All rights reserved.
Other Views for Work Areas
SQL> SELECT *
2 FROM v$pgastat
3 WHERE name = 'cache hit percentage';
NAME VALUE UNIT

cache hit percentage 93 percent
9-8
Copyright © Oracle Corporation, 2002. All rights reserved.
SQL> SELECT ROUND(pga_target_for_estimate
2 /1024/1024) AS target_mb,
3 estd_pga_cache_hit_percentage AS
4 cache_hit_percent, estd_overalloc_count
5 FROM v$pga_target_advice
6 ORDER BY target_mb;
Querying v$pga_target_advice
9-9
Copyright © Oracle Corporation, 2002. All rights reserved.
Understanding v$pga_target_advice
TARGET_MB CACHE_HIT_PERCENT ESTD_OVERALLOC_COUNT

63 23 367
125 24 30

250 30 3
375 39 1
500 58 0
600 59 0
700 59 0
800 60 0
900 60 0
1000 61 0
1500 67 0
2000 76 0
3000 83 0
4000 85 0
9-10
Copyright © Oracle Corporation, 2002. All rights reserved.
PGA Sizing Advisor Output
in Oracle Enterprise Manager
9-11
Copyright © Oracle Corporation, 2002. All rights reserved.
Overview
The automatic sort area management feature is:
•
Easier to set up and size than the
*_SORT_AREA parameters
•
Easier to monitor using the advisory view
9-12
Copyright © Oracle Corporation, 2002. All rights reserved.
The Sorting Process
If sort space requirement is greater than
SORT_AREA_SIZE:

Segments hold
data while the
server works on
another sort run
Server
process

Sort run 1

Sort run 2
TEMPORARY tablespace

Sort run 2

Temporary segment
9-13
Copyright © Oracle Corporation, 2002. All rights reserved.
Sort Area and Parameters
The sort space is in:
•
The PGA for a dedicated server connection
•
The shared pool for Oracle Shared Server
connection

Shared pool

PGA

Stack


space

User
session
data

Cursor
state

UGA

Sort
area

Stack

space

PGA

User
session
data

Cursor
state

UGA


Sort

area
9-15
Copyright © Oracle Corporation, 2002. All rights reserved.
Sort Area and Parameters
•
An execution plan can contain multiple sorts
•
A single server performing a sort needs:
–
An area of SORT_AREA_SIZE, in bytes
–
At least one area of SORT_AREA_RETAINED_SIZE
for a join sort
•
Each parallel query server needs
SORT_AREA_SIZE
•
Two sets of servers can be writing at once, so:
–
Calculate SORT_AREA_SIZE × 2 × degree of
parallelism
–
Add SORT_AREA_RETAINED_SIZE × degree of
parallelism × number of sorts above two
9-17
Copyright © Oracle Corporation, 2002. All rights reserved.
Tuning Sorts
•

Use automatic sort area management.
•
Avoid sort operations whenever possible.
•
Reduce swapping and paging by making sure that
sorting is done in memory when possible.
•
Reduce space allocation calls by allocating
temporary space appropriately.
9-18
Copyright © Oracle Corporation, 2002. All rights reserved.
The Sorting Process and Temporary Space
Create a temporary tablespace by using:
Temporary tablespace
2M
One single sort segment
Permanen
t
Objects
2M 2M
temp01.dbf
temp02.dbf
temp04.dbf
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
'$HOME/ORADATA/u06/temp01.dbf' size 200M;
9-19
Copyright © Oracle Corporation, 2002. All rights reserved.
Temporary Space Segments
A temporary space segment:
•

Is created by the first sort
•
Extends as demands are made on it
•
Comprises extents, which can be used by
different sorts
•
Is described in the sort extent pool (SEP)
9-20
Copyright © Oracle Corporation, 2002. All rights reserved.
Operations Requiring Sorts
Some of the operations that may require sorts are:
•
Index creation
•
Parallel insert operations involving index
maintenance
•
ORDER BY or GROUP BY clauses
•
DISTINCT values selection
•
UNION, INTERSECT, or MINUS operators
•
Sort-merge joins
•
ANALYZE command execution
9-22
Copyright © Oracle Corporation, 2002. All rights reserved.
Avoiding Sorts

Avoid sort operations whenever possible:
•
Use NOSORT to create indexes.
•
Use UNION ALL instead of UNION.
•
Use index access for table joins.
•
Create indexes on columns referenced in the
ORDER BY clause.
•
Select the columns for analysis.
•
Use ESTIMATE rather than COMPUTE for
large objects.
9-24
Copyright © Oracle Corporation, 2002. All rights reserved.
Diagnostic Tools
v$sysstat
Sort area (UGA)
SORT_AREA_SIZE
SORT_AREA_RETAINED_SIZE
Server
process
TEMPORARY
tablespace
Sort on
disk
Sort in
memory

PCTINCREASE
INITIAL
NEXT
Statspack
v$sort_usage
v$sort_segment
9-26
Copyright © Oracle Corporation, 2002. All rights reserved.
SQL> SELECT d.value "Disk", m.value "Mem",
2 (d.value/m.value)*100 "Ratio"
3 FROM v$sysstat m, v$sysstat d
4 WHERE m.name = 'sorts (memory)'
5 AND d.name = 'sorts (disk)';
Disk Mem Ratio

23 206 11.165049
Diagnostics and Guidelines
•
In an OLTP system the ratio of disk sorts to
memory sorts should be less than 5%.
•
Increase the value of SORT_AREA_SIZE /
PGA_AGGREGATE_TARGET if the ratio is greater
than 5%.
9-27
Copyright © Oracle Corporation, 2002. All rights reserved.
Performance Manager: Sorts
9-28
Copyright © Oracle Corporation, 2002. All rights reserved.
SQL> SELECT tablespace_name, current_users, total_extents,

2 used_extents, extent_hits, max_used_blocks,
3 max_sort_blocks
4 FROM v$sort_segment;
TABLESPACE_NAME CURRENT_USERS TOTAL_EXTENTS USED_EXTENTS
EXTENT_HITS MAX_USED_BLOCKS MAX_SORT_BLOCKS


TEMP 2 4 3
20 200 200
Monitoring Temporary Tablespaces
•
Default storage parameters apply to sort segments.
•
Sort segments have unlimited extents.
9-29
Copyright © Oracle Corporation, 2002. All rights reserved.
Temporary Tablespace Configuration
•
Set appropriate storage values.
•
Set up different temporary tablespaces based on
sorting needs.
•
Stripe temporary tablespaces.
•
Use v$tempfile and dba_temp_files for
information on temporary files.
SQL> SELECT session_num, tablespace, extents, blocks
2 FROM v$sort_usage;
SESSION_NUM TABLESPACE EXTENTS BLOCKS


16 TEMP 4 200
9-31
Copyright © Oracle Corporation, 2002. All rights reserved.
Summary
In this lesson, you should have learned how to:
•
List the operations that use temporary space
•
Create and monitor temporary tablespaces
•
Identify actions that use the temporary tablespace
•
Describe the use of disk and memory for sorting
•
Identify the SQL operations that require sorts
•
Differentiate between disk and memory sorts
•
List ways to reduce total sorts and disk sorts
•
Determine the number of memory sorts performed
•
Set parameters to optimize sorts