Error Parameter Sort_area_size
Contents |
updated: November 05, 2012 - 9:44 am UTC Category: Database – Version: 8.1.6 Latest Followup You Asked Hi Tom, The value specified by SORT_AREA_SIZE is assigned on a per user basis. Therefore, if it is set to
How To Find Sort_area_size In Oracle
1M, and 25 users connect to the database, each user's Server Process will consume alter system set sort_area_size scope=both 1M (25M in total). The questions are: 1) The memory consumed for the sort, is it within the SGA memory or
Oracle Pga_aggregate_target
the Server memory (outside SGA)? 2) What will be the case if 25 physical users (different sessions) are connected using the same database user? 3) Let's assume, SORT_AREA_SIZE is set to 1M and temporary how to calculate sort_area_size tablespace is locally managed with uniform size of 1M. If SORT_AREA_RETAINED_SIZE is set to a higher value other than 1M, what will be the impact on performance? Thanks, 'Abdur-Razzaaq and we said... Well, your basic assumption is wrong. sort_area_size is an upper bound on the memory we dynamically allocate for a sort. A session may have more then one sort going at the same time (more then one oracle alter session sort_area_size sort area size) sort_area_sizes are not allocated until they are needed (and GROW to be sort_area_size bytes in size -- they don't start there) and are freed the second the sort is over. So, if it is 1m and there are 25 users connected to the database you will have somewhere between 0 and many megs of sort space at any point in time (if all 25 do a single sort AT THE SAME exact instant -- you'll have 25m. If they are not sorting, you'll have 0 meg) 1) sort_area_size space is allocated in the PGA, outside of the SGA (retained can be in the UGA with MTS) 2) it is done by the SESSION not by name and a single session can have more then one going at any time 3) SORT_AREA_RETAINED_SIZE is bounded by sort_area_size, hence this hypothetical situation won't happen. See http://www.ixora.com.au/newsletter/2000_11.htm for a good discussion on this. followup to comment one It is definitely an inaccurate statement on their part (eg: its *wrong* plain and simple) See http://www.sybex.com/erratatracking.nsf/weberrataform?OpenForm&ISBN=2684 there is an errata sheet for this book -- you might want to drop sybex a line and have them add this to the sheet. followup to comment two Nope
PGA_AGGREGATE_TARGET Set? 20 12 2009 December 20, 2009 (Forward to the Follow-Up Post) A couple of years ago
Workarea_size_policy
I was under the impression that increasing the SORT_AREA_SIZE parameter oracle hash_area_size could reduce the number of sorts to disk in Oracle 10g R2 with the PGA_AGGREGATE_TARGET
Sort_area_retained_size
parameter set, using dedicated server processes, and with the WORKAREA_SIZE_POLICY set to AUTO (yes, confirmed to be set in the session). Why? Because I https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1176223915789 saw it happen a couple of times when troubleshooting performance problems. But, the sorts to disk still happened on occasion when there seemed to be no reason why Oracle would not be able to perform an optimal, in-memory workarea execution. Thankfully, someone questioned my earlier testing that produced results https://hoopercharles.wordpress.com/2009/12/20/sort_area_size-affecting-sorts-to-disk-with-pga_aggregate_target-set/ which were inconsistent with the documentation. I then attempted to produce a test case to prove myself wrong. I set out to trigger a query to perform a sort to disk, and then by changing only the SORT_AREA_SIZE parameter, have Oracle switch to perform an in-memory workarea execution for the query. Once the test case produced that result, I attempted to identify the trigger that caused Oracle to switch. The test case appears in the following Usenet thread: http://groups.google.com/group/comp.databases.oracle.server/msg/e0b8bea300e45ae4 Line wrapping is a problem in the above thread, so I have reproduced the test case below: I was able to produce a test case on the base patch of Oracle 10.2.0.2 on Win 32. The following required roughly 10 hours of testing and analysis. I built a new database instance with the following pfile: ############################################ aq_tm_processes=1 background_core_dump=partial cluster_database=FALSE compatible=10.2.0.1.0 control_files=("C:\oracle\OraData\LT\ctlLT01.ctl", "C:\oracle\flash_recovery_area\LT\ctlLT02.ctl") control_file_record_keep_time=7 cursor_sharing=FORCE cursor_space_for_time=true db_block_size=8192 db_cache_advice=on db_block_checking=false db_block_checksum=typical
Oracle SQL Server PRODUCTSDiscussion & Resources Benchmark Factory Code Tester for Oracle SharePlex SQL Navigator SQL Optimizer Spotlight Stat Toad Intelligence Central Toad Data Modeler Toad Data Point Toad Extension for http://www.toadworld.com/platforms/oracle/w/wiki/1340.tuning-sorts-to-reduce-io Eclipse Toad for Hadoop Toad for IBM DB2 Toad for Oracle Toad for MySQL Toad for SQL Server Toad for SAP Solutions Toad MAC Edition Home » Platforms » Oracle » Oracle Wiki » Tuning Sorts to Reduce I/O Tuning Sorts to Reduce I/O Oracle Community Join Overview Forum Blog Wiki Members Contents Oracle Wiki Oracle - Wiki Agile how to Development / DevOps Amazon APEX Big Data SQL Data Replication Database Administration Database Tuning 12c Adaptive Query Optimization - Join Methods 12c Adaptive Query Optimization - Parallel Distribution Methods Adaptive Parallel Distribution Client-Server Tuning Common Management Infrastructure (CMI) Generic Methodology 5 Step Tuning Methodology - Overview Best Practices Exploring Oracle database in-memory management Improving throughput in Oracle 11g STEP 1: Optimize error parameter sort_area_size the Application Workload STEP 2: Reduce Contention STEP 3: Minimize Physical Disk IO Archiving Performance Identifying Full Table Scans Optimizing Initialization Parameters to Reduce I/O Reducing Shared Pool Contention Row Chaining and Migration Tuning Sorts to Reduce I/O Tuning the Database Buffer Cache Tuning the Database for Optimal Physical I/O STEP 4: Optimize Physical Disk IO How Exadata cell server deals with 'Consistent Read' requests ? Information Lifecycle Management (ILM) with Oracle 12c Monitoring Wait Statistics in Oracle database. Part 1 Monitoring Wait Statistics in Oracle database. Part 2 Optimizing Memory Optimizing the Oracle 11g Result Cache Oracle Database In-Memory Option Parallel Query Option (OTOC134) Parallel Server Tuning Performance tuning of Oracle instances. Part I Performance tuning of Oracle instances. Part II Real Time ADDM - 12c SPD (SQL Plan Directives) in 12c - part I SPD (SQL Plan Directives) in 12c - Part II SQL performance issue in Exadata SQL Tuning Statspack to monitor Oracle database performance Table partitioning in Oracle database Tuning a disjunctive subquery Tuning Long-running jobs Tuning the PGA in Oracle Database System TUNING VIEW'S PERFORM