March 29, 2010 (Updated April 6, 2010 with a test table)
I recently encountered a discussion thread (dbaforums.org/oracle/index.php?s=eacd9ff86b358b4a14ecd3fd7653a9fd&showtopic=19407) that pointed to a news article about the internals of Oracle sorting. The news article (dba-oracle.com/t_oracle_sorting.htm) has a date of October 15, 2007, so it is probably reasonable to assume that the article describes the behavior of Oracle Database 10g R2, and possibly Oracle Database 11g R1.
Please read the news article and see if you are able to answer the following true or false questions. State why you believe that the question is true, or why you believe that the question is false. Any answers that attempt to utilize logical fallacies will be scored as incorrect.
1. Sequencing of database output first started in the 1960s.
2. One of the very important components of Oracle tuning is Oracle sorting, yet that process is often overlooked.
3. SSD is a synonym for a super-fast RAM disk.
4. Oracle Database always performs an automatic sorting operation when a GROUP BY clause is used in a SQL statement, when an index is created, and when an ORDER BY clause is used in a SQL statement.
5. The cheapest method is always used by Oracle Database when ordering a resultset.
6. A hinted execution plan involving a single table, with a /*+ index */ hint, will always retrieve the rows in the sorted order of the index.
7. If a SQL statement requires a single sort operation that completes in memory, that SQL statement will not use any space in the TEMP tablespace when the rows are retrieved – with the assumption that a hash join did not spill to disk.
8. The CPU_COST parameter causes Oracle Database to favor the pre-sorted ordering of an index over a discrete sorting operation.
9. The value of the SORT_AREA_SIZE parameter or the PGA_AGGREGATE_TARGET parameter if used, influences Oracle Database’s decision to prefer the pre-sorted ordering of an index over a discrete sorting operation.
10. The clustering factor of an index influences Oracle Database’s decision to prefer the pre-sorted ordering of an index over a discrete sorting operation.
11. The default database block size in use by the database influences Oracle Database’s decision to prefer the pre-sorted ordering of an index over a discrete sorting operation.
12. A sort operation will only spill to disk when RAM is exhausted.
13. “At the time a session is established with Oracle, a private sort area is allocated in memory for use by the session for sorting, based on the value of the sort_area_size initialization parameter.” Supporting evidence:
14. For sort intensive tasks it is not possible to adjust the amount of memory allocated to those tasks by adjusting the SORT_AREA_SIZE parameter at the session level.
15. The entire database can be slowed down due to a disk sort in the TEMP tablespace because sorts to disk are I/O intensive.
16. A good general rule is that the SORT_AREA_SIZE parameter should be adjusted to eliminate sorts to disk caused by GROUP BY operations.
17. Buffer pool blocks are allocated to hold or manage the blocks that are in the TEMP tablespace.
18. An optimal workarea execution, completed entirely in memory, is always preferred over a one-pass or multi-pass workarea execution.
19. Free buffer waits can be caused by excessive sorts to disk, which cause data blocks needed by other sessions to be paged out of the buffer.
20. One percent is an acceptable ratio of disk sorts to the total number of sorts.
21. When the PGA_AGGREGATE_TARGET parameter is specified, the total work area size cannot exceed 200MB.
22. No task may use more than 10MB for sorting.
23. A DBA should modify two hidden (underscore) parameters to permit up to 50MB of memory to be used for an in-memory sort operation for a SQL statement.
Have you ever read an article in an attempt to find the answer to a very specific question, only to find that by the time the end of the article is reached, you now have a whole new set of questions? Try to answer the above questions using something beyond true or false – tell me why in detail it is true or why it is false. Are there any other questions that could be asked about the article?
Test table for question #10, added April 6, 2010:
CREATE TABLE T1 ( C1 NUMBER, C2 NUMBER, C3 VARCHAR2(100), PRIMARY KEY (C1)); INSERT INTO T1 SELECT ROWNUM, DECODE(MOD(ROWNUM,100),0,NULL,ROWNUM), RPAD('A',100,'A') FROM DUAL CONNECT BY LEVEL<=10000; COMMIT; ALTER TABLE T1 MODIFY (C2 NOT NULL); CREATE INDEX IND_T1_C2 ON T1(C2); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)