c# barcode generator SORTING AND MERGE JOINS in Java

Drawing Code 3/9 in Java SORTING AND MERGE JOINS

CHAPTER 13 SORTING AND MERGE JOINS
Making USS Code 39 In Java
Using Barcode generator for Java Control to generate, create USS Code 39 image in Java applications.
www.OnBarcode.com
Scanning ANSI/AIM Code 39 In Java
Using Barcode scanner for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
sort_area_retained_size
Drawing Matrix Barcode In Java
Using Barcode maker for Java Control to generate, create Matrix Barcode image in Java applications.
www.OnBarcode.com
GTIN - 13 Generator In Java
Using Barcode printer for Java Control to generate, create European Article Number 13 image in Java applications.
www.OnBarcode.com
The sort_area_retained_size can also be set to modify the behavior of sorting. The default value is 0, which means the sort_area_retained_size should dynamically adjust itself to match the current setting of the sort_area_size. Setting the sort_area_retained_size to something other than 0 has two effects. First, it changes the way Oracle acquires and releases memory, and, second, it affects the way that Oracle uses the temporary tablespace.
UCC-128 Generator In Java
Using Barcode creator for Java Control to generate, create GTIN - 128 image in Java applications.
www.OnBarcode.com
Code 128A Encoder In Java
Using Barcode printer for Java Control to generate, create ANSI/AIM Code 128 image in Java applications.
www.OnBarcode.com
SHARED SERVERS AND P_A_T
QR Code JIS X 0510 Maker In Java
Using Barcode generator for Java Control to generate, create QR Code 2d barcode image in Java applications.
www.OnBarcode.com
Drawing International Standard Serial Number In Java
Using Barcode generation for Java Control to generate, create International Standard Serial Number image in Java applications.
www.OnBarcode.com
If you are using Shared Servers (or Multithreaded Server MTS as it used to be called), then in 9i the memory for bulk-memory operations such as sorting, hash joins, and so on, still uses the parameters relevant to a manual workarea_size_policy rather than the limits dictated by the pga_aggregate_target, and any memory allocated in the user global area (UGA) for these operations is based in the shared or system global area (SGA), not in the process global area (PGA). This changes in 10g.
Creating Code 39 In VS .NET
Using Barcode printer for ASP.NET Control to generate, create Code 39 image in ASP.NET applications.
www.OnBarcode.com
Code 39 Reader In Java
Using Barcode decoder for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Go back to script sort_demo_01.sql and run two tests, restarting your SQL*Plus session for each test. For the first test, set the sort_area_size to 30MB and do not set the sort_area_ retained_size; for the second test, leave the value for the sort_area_size at 30MB and set the sort_area_retained_size to 8MB (this test is implemented as script sort_demo_01a.sql in the online code suite) After each test, check the tempfile I/O figures and the changes in session statistics relating to sorts and memory usage. Table 13-3 shows what I got under 9.2.0.6.
Code 39 Creator In None
Using Barcode generator for Font Control to generate, create Code-39 image in Font applications.
www.OnBarcode.com
Create PDF 417 In None
Using Barcode creator for Microsoft Excel Control to generate, create PDF-417 2d barcode image in Office Excel applications.
www.OnBarcode.com
Table 13-3. Impact of sort_area_retained_size on I/O
Paint ECC200 In Visual Basic .NET
Using Barcode maker for .NET Control to generate, create Data Matrix ECC200 image in .NET framework applications.
www.OnBarcode.com
Creating UCC.EAN - 128 In None
Using Barcode encoder for Online Control to generate, create UCC-128 image in Online applications.
www.OnBarcode.com
Statistic
Recognizing Data Matrix ECC200 In Java
Using Barcode scanner for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
ECC200 Reader In C#
Using Barcode decoder for .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
session UGA memory max Session PGA memory max Physical writes direct sorts (disk)
Encode UCC - 12 In None
Using Barcode drawer for Office Excel Control to generate, create UPC-A Supplement 5 image in Excel applications.
www.OnBarcode.com
Encoding UCC - 12 In None
Using Barcode creation for Online Control to generate, create Universal Product Code version A image in Online applications.
www.OnBarcode.com
sort_area_retained_size = 0
1D Barcode Creator In C#.NET
Using Barcode encoder for VS .NET Control to generate, create Linear Barcode image in .NET framework applications.
www.OnBarcode.com
Generate Code 3/9 In None
Using Barcode creator for Online Control to generate, create Code 3 of 9 image in Online applications.
www.OnBarcode.com
25.5MB 25.8MB 0 0
sort_area_retained_size = 8MB
8.3MB 28.5MB 1,547 0
The most significant thing about these statistics is the physical writes direct by specifying a non-zero sort_area_retained_size we have made Oracle dump the entire data set to disk before returning results to the client (which may also explain the extra PGA memory we need it for the I/O buffers, which we wouldn t otherwise be using).
CHAPTER 13 SORTING AND MERGE JOINS
I have included the number of sorts to disk reported by v$sesstat, just to point out that Oracle does not consider this to be a sort to disk the sorting was all over before the data was dumped to disk as a holding location. This is the way things happen when the sort_area_retained_size is set. The first allocation of memory is in the UGA, but when the allocation reaches the limit set by sort_area_retained_ size, memory is then allocated in the PGA. Even if a sort completes in memory, the entire sorted data set will be dumped to disk if that memory has been split between the UGA and the PGA.
SO WHEN IS MEMORY ALLOCATED
It is important to remember that the sort_area_size and sort_area_retained_size represent limits on memory allocation. You may still hear the suggestion from time to time that a session acquires the amount of memory defined by sort_area_size as it starts. This is not true. The memory is only allocated as needed for sorting, and even then it is only allocated a bit at a time, not all at once. The same error has also been made about the pga_aggregate_target. There have been a couple of articles stating that the whole of the pga_aggregate_target is actually acquired by the instance at instance startup. This is completely incorrect. First, the parameter is simply a number that is used for accounting purposes (and you can go over budget); secondly, the memory is never acquired by the instance, it is acquired and released by individual processes only as and when needed.
As I pointed out earlier in this chapter, if you are using Shared Servers (MTS), then the UGA is in the SGA, so memory up to the sort_area_retained_size will be allocated from the SGA typically the large pool, although if you have forgotten to specify a value for the parameter large_pool_size, it will come from the shared pool. The excess demand, up to (sort_area_size sort_area_retained_size) will be allocated in the PGA, which means it will appear in the memory of the Shared Server process (i.e., the processes that are called things like ora_{SID}_sNNN at which point it will be accounted by the code that handles the pga_ aggregate_target calculations). If the sort_area_retained_size is less than the sort_area_size, then the sorted data will be dumped to disk as the sort completes, the excess memory allocated in the PGA will be made available for other activity, and the sort_area_retained_size (possibly limited to 2MB according to some recent experiments) will be used to reload the dumped data for further processing.
Copyright © OnBarcode.com . All rights reserved.