iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Performance

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 |

 

Lesson 08

"A university is what a college becomes when the faculty loses interest in students." John Ciardi (1916 - 1986)

 

Read first then play the video:

   PRF-VIDEO -Optimizing Sort Operations
 
 

Optimize Sort Operations

 

Introduction

As a DBA, you are responsible to optimize sort operations of your organization"s database in case of performance problems. Your job"s responsibilities dictate that you should be aware of the following basic fundamental subjects:

 

Monitoring a sort statement operation

Optimizing a sort statement operation

Using the V$SYSSTAT view

SORTS (DISK)

SORTS (MEMORY)

Calculating the sort Ratio

SORT_AREA_SIZE

PGA_AGGREGATE_TARGET

Increasing the SORT_AREA_SIZE parameter

 

Hands-on

In this exercise, you will learn how to monitor and optimize sort operations.

Now, connect to SQLPlus as the SYSTEM/MANAGER user.
SQL> CONNECT system/manager AS SYSDBA


Sorting process
The Oracle Server Processes will sort as much as they can in the memory sort area before using any disk sort space.


Sort performed destination (Memory vs. Disk)
Now, query the V$SYSSTAT view to track the number of in-memory and to-disk sorts, as well as the total number of rows sorted.
SQL> SELECT name, value
               FROM v$sysstat
               WHERE name like 'sorts%'
/
Notice that the sorts (disk) number must be very low, and the sorts (memory) number can be as high as possible.

The "sorts (memory)" value is a number of times that Oracle tables were sorted in the memory. The "sorts (disk)" value is a number of times that Oracle tables were sorted on the disk using the TEMPORARY tablespace.

 

Sort HIT Ratio (Memory vs. Disk)

Now, determine the sort ratio of the in-memory vs. to-disk sorts.
SQL> SELECT 100*(a.value-b.value)/(a.value) AS "Sort Ratio"
               FROM v$sysstat a, v$sysstat b
               WHERE a.name = 'sorts (memory)'
                     AND b.name ='sorts (disk)'
/
Notice that the sort ratio should be greater than 95%. If you are not using the automatic PGA memory and the number is less than 95 percent, you should greatly consider increasing the value of the SORT_AREA_SIZE parameter. If you are using the automatic PGA memory and the number is less than 95 percent, you should greatly consider increasing the value of the PGA_AGGREGATE_TARGET parameter.


Increase sort area in memory

Try to increase the SORT_AREA_SIZE parameter to 819200 bytes.
SQL> ALTER SYSTEM SET sort_area_size=819200 SCOPE=spfile
/
In order for the change to take effect, the database needs to be restarted.

Shutdown and startup the database in order to see the changes.
SQL> SHUTDOWN IMMEDIATE;

SQL> CONNECT system/manager AS SYSDBA

SQL> STARTUP

 

"Success didn't spoil me, I've always been insufferable." Fran Lebowitz (1950 )

 

Questions:

Q: How do you optimize a sort operation in the Oracle SGA memory?

Q: How do you monitor a sort operation?

Q: How do you use the V$SYSSTAT view to check a sort usage in the SGA memory?

Q: Describe the "sorts (disk)" value in the V$SYSSTAT view.

Q: Describe the "sorts (memory)" value in the V$SYSSTAT view.

Q: How do you calculate the sort ratio value in the SGA sort area?

Q: How do you optimize the SORT_AREA_SIZE memory?

Q: what does the following SQL statement?

SQL> SELECT 100*(a.value-b.value)/(a.value) AS "Sort Ratio"
FROM v$sysstat a, v$sysstat b
WHERE a.name = 'sorts (memory)'
AND b.name ='sorts (disk)'
/

Q: What is an acceptable range for the sort ratio in the SGA sort area?

Q: When should you consider increasing your SORT_AREA_SIZE parameter?

Q: When should you consider increasing your PGA_AGGREGATE_TARGET parameter?