March 16, 2010
You might be aware that the number of consistent gets performed during the execution of a SQL statement will vary as the execution plan for a SQL statement changes. You might be aware that the number of consistent gets for a SQL statement will vary depending on the block size used by the database. You might be aware that the number of consistent gets will vary depending on the clustering factor of the indexes used by the SQL statement change (assuming that the table rows are actually accessed). You might be aware that moving a table and rebuilding its indexes could cause the number of consistent gets for a SQL statement to change. But were you aware that there are other reasons for the number of consistent gets to change? Let’s set up a simple test table in the database that has 1,000,000 rows and an index on the primary key column:
CREATE TABLE T5 (
C1 VARCHAR2(10),
C2 VARCHAR2(100),
PRIMARY KEY (C1));
INSERT INTO T5 NOLOGGING
SELECT
'A'||TO_CHAR(ROWNUM,'0000000'),
RPAD('A',100,'A')
FROM
DUAL
CONNECT BY
LEVEL<=1000000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T5',CASCADE=>TRUE)
In Oracle Database, for instance release 11.1.0.6 on 64 bit Linux, we craft a silly SQL statement that uses the test table. The silly SQL statement’s goal is just to drive up the number of consistent gets to allow us to peg the server’s CPU. This is the silly SQL statement that essentially joins table T5 to itself multiple times:
SELECT /*+ USE_NL(T51 T52 T53 T54 T55 T56 T57 T58) */ T51.C1, T51.C2, T52.C1, T52.C2, T53.C1, T53.C2, T54.C1, T54.C2, T55.C1, T55.C2, T56.C1, T56.C2, T57.C1, T57.C2, T58.C1, T58.C2 FROM T5 T51, T5 T52, T5 T53, T5 T54, T5 T55, T5 T56, T5 T57, T5 T58 WHERE T51.C1=T52.C1 AND T51.C1=T53.C1 AND T51.C1=T54.C1 AND T51.C1=T55.C1 AND T51.C1=T56.C1 AND T51.C1=T57.C1 AND T51.C1=T58.C1 AND T51.C1 BETWEEN 'A 0000000' AND 'A 1000000';
In the above SQL statement I have forced through a hint a nested loop join between the various aliases for the table T5 to force the use of the index, rather than letting the optimizer use full table scans and hash joins. Let’s see how the array fetch size affects the number of consistent gets for the above SQL statement. Articles on other blogs have shown demonstrations of the same effect of changing the array fetch size, but stay with me, there is a twist. The script:
SPOOL consistent_gets.txt SET AUTOTRACE TRACEONLY STATISTICS SET TIMING ON SET ARRAYSIZE 1 SELECT /*+ USE_NL(T51 T52 T53 T54 T55 T56 T57 T58) */ T51.C1, T51.C2, T52.C1, T52.C2, T53.C1, T53.C2, T54.C1, T54.C2, T55.C1, T55.C2, T56.C1, T56.C2, T57.C1, T57.C2, T58.C1, T58.C2 FROM T5 T51, T5 T52, T5 T53, T5 T54, T5 T55, T5 T56, T5 T57, T5 T58 WHERE T51.C1=T52.C1 AND T51.C1=T53.C1 AND T51.C1=T54.C1 AND T51.C1=T55.C1 AND T51.C1=T56.C1 AND T51.C1=T57.C1 AND T51.C1=T58.C1 AND T51.C1 BETWEEN 'A 0000000' AND 'A 1000000'; SET ARRAYSIZE 1 / SET ARRAYSIZE 10 / SET ARRAYSIZE 50 / SET ARRAYSIZE 100 / SET ARRAYSIZE 200 / SET ARRAYSIZE 500 / SET ARRAYSIZE 1000 / SET ARRAYSIZE 2000 / SET ARRAYSIZE 5000 / SPOOL OFF
We will throw out the first execution with the array fetch size set at 1 (the client computer is using the 11.1.0.7 client) so that the recursive call does not throw off the timing. The summarized output of the above script follows:
Fetch Array: 1 10 50 100 200 500 1000 2000 5000 Elapsed 00:05:36.83 00:02:30.86 00:01:47.15 00:01:38.21 00:01:29.87 00:01:24.80 00:01:23.08 00:01:22.25 00:01:22.49 recursive calls 0 0 0 0 0 0 0 0 0 db block gets 0 0 0 0 0 0 0 0 0 consistent gets 12,554,387 9,453,633 8,822,789 8,696,435 8,625,467 8,582,840 8,568,603 8,561,488 8,559,626 physical reads 0 0 0 0 0 0 0 0 0 redo size 0 0 0 0 0 0 0 0 0 bytes sent via SQL 129,001,789 94,601,789 87,721,789 86,861,789 86,431,789 86,173,789 86,087,789 86,044,789 86,018,989 bytes received via 5,500,349 1,100,349 220,349 110,349 55,349 22,349 11,349 5,849 2,549 SQL*Net roundtrips 500,001 100,001 20,001 10,001 5,001 2,001 1,001 501 201 sorts (memory) 0 0 0 0 0 0 0 0 0 sorts (disk) 0 0 0 0 0 0 0 0 0 rows processed 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000 1,000,000
As you can see from the above, when the fetch array size was set to 1, SQL*Plus actually operated as if the fetch array size was set to 2. As the fetch array size increased, the execution time for the query decreased until the fetch array size of 5,000 was attempted. Accompanying the decrease in the execution time is a decrease in the number of consistent gets, bytes sent across the network, and the number of round trips. Nice, so where is the twist, is it just that the time increased when the fetch array size was increased to 5,000?
Maybe we should repeat the test on Oracle Database 11.2.0.1, which also used the default 8KB block size and runs on 64 bit Linux. These are the summarized results, excluding the output that did not change significantly from the test run on Oracle 11.1.0.6:
Fetch Array: 1 10 50 100 200 500 1000 2000 5000 Elapsed 00:05:39.97 00:02:16.71 00:01:36.36 00:01:27.57 00:01:24.48 00:01:22.77 00:01:22.15 00:01:21.96 00:01:22.24 consistent gets 516,378 116,378 36,378 26,378 21,378 18,378 17,378 16,878 16,578
When the fetch array size was specified as 1, the number of consistent gets dropped from 12,554,387 to 516,378 when run on Oracle 11.2.0.1, yet the execution time is almost identical to that achieved on 11.1.0.6. When the fetch array size was specified as 5,000, the number of consistent gets dropped from 8,559,626 to 16,578, yet the execution time was almost identical to that achieved on 11.1.0.6. Quite a substantial decrease in the number of consistent gets from one release to another.
Anyone want to take an educated guess as to what caused the decrease in the number of consistent gets?
Take a guess before scrolling down.
…
Here is the DBMS_XPLAN output for Oracle 11.1.0.6 for the SQL statement:
SQL_ID 7v7q3k01y4r0z, child number 0
-------------------------------------
SELECT /*+ USE_NL(T51 T52 T53 T54 T55 T56 T57 T58) */ T51.C1,
T51.C2, T52.C1, T52.C2, T53.C1, T53.C2, T54.C1,
T54.C2, T55.C1, T55.C2, T56.C1, T56.C2, T57.C1,
T57.C2, T58.C1, T58.C2 FROM T5 T51, T5 T52, T5 T53,
T5 T54, T5 T55, T5 T56, T5 T57, T5 T58 WHERE
T51.C1=T52.C1 AND T51.C1=T53.C1 AND T51.C1=T54.C1 AND
T51.C1=T55.C1 AND T51.C1=T56.C1 AND T51.C1=T57.C1 AND
T51.C1=T58.C1 AND T51.C1 BETWEEN 'A 0000000' AND 'A 1000000'
Plan hash value: 2422939766
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | | 1000K|00:00:37.09 | 8696K|
| 2 | NESTED LOOPS | | 1 | 1000K| 1000K|00:00:34.08 | 7696K|
| 3 | NESTED LOOPS | | 1 | 1000K| 1000K|00:00:31.07 | 7457K|
| 4 | NESTED LOOPS | | 1 | 1000K| 1000K|00:00:26.06 | 6218K|
| 5 | NESTED LOOPS | | 1 | 1000K| 1000K|00:00:21.05 | 4980K|
| 6 | NESTED LOOPS | | 1 | 1000K| 1000K|00:00:16.04 | 3741K|
| 7 | NESTED LOOPS | | 1 | 1000K| 1000K|00:00:11.03 | 2503K|
| 8 | NESTED LOOPS | | 1 | 1000K| 1000K|00:00:06.02 | 1264K|
|* 9 | TABLE ACCESS FULL | T5 | 1 | 1000K| 1000K|00:00:00.01 | 25906 |
| 10 | TABLE ACCESS BY INDEX ROWID| T5 | 1000K| 1 | 1000K|00:00:04.28 | 1238K|
|* 11 | INDEX UNIQUE SCAN | SYS_C0015042 | 1000K| 1 | 1000K|00:00:01.96 | 238K|
| 12 | TABLE ACCESS BY INDEX ROWID | T5 | 1000K| 1 | 1000K|00:00:03.80 | 1238K|
|* 13 | INDEX UNIQUE SCAN | SYS_C0015042 | 1000K| 1 | 1000K|00:00:01.63 | 238K|
| 14 | TABLE ACCESS BY INDEX ROWID | T5 | 1000K| 1 | 1000K|00:00:03.79 | 1238K|
|* 15 | INDEX UNIQUE SCAN | SYS_C0015042 | 1000K| 1 | 1000K|00:00:01.62 | 238K|
| 16 | TABLE ACCESS BY INDEX ROWID | T5 | 1000K| 1 | 1000K|00:00:03.78 | 1238K|
|* 17 | INDEX UNIQUE SCAN | SYS_C0015042 | 1000K| 1 | 1000K|00:00:01.60 | 238K|
| 18 | TABLE ACCESS BY INDEX ROWID | T5 | 1000K| 1 | 1000K|00:00:03.79 | 1238K|
|* 19 | INDEX UNIQUE SCAN | SYS_C0015042 | 1000K| 1 | 1000K|00:00:01.60 | 238K|
| 20 | TABLE ACCESS BY INDEX ROWID | T5 | 1000K| 1 | 1000K|00:00:03.79 | 1238K|
|* 21 | INDEX UNIQUE SCAN | SYS_C0015042 | 1000K| 1 | 1000K|00:00:01.62 | 238K|
|* 22 | INDEX UNIQUE SCAN | SYS_C0015042 | 1000K| 1 | 1000K|00:00:01.61 | 238K|
| 23 | TABLE ACCESS BY INDEX ROWID | T5 | 1000K| 1 | 1000K|00:00:01.54 | 1000K|
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter(("T51"."C1">='A 0000000' AND "T51"."C1"<='A 1000000'))
11 - access("T51"."C1"="T52"."C1")
filter(("T52"."C1">='A 0000000' AND "T52"."C1"<='A 1000000'))
13 - access("T51"."C1"="T53"."C1")
filter(("T53"."C1">='A 0000000' AND "T53"."C1"<='A 1000000'))
15 - access("T51"."C1"="T54"."C1")
filter(("T54"."C1">='A 0000000' AND "T54"."C1"<='A 1000000'))
17 - access("T51"."C1"="T55"."C1")
filter(("T55"."C1">='A 0000000' AND "T55"."C1"<='A 1000000'))
19 - access("T51"."C1"="T56"."C1")
filter(("T56"."C1">='A 0000000' AND "T56"."C1"<='A 1000000'))
21 - access("T51"."C1"="T57"."C1")
filter(("T57"."C1">='A 0000000' AND "T57"."C1"<='A 1000000'))
22 - access("T51"."C1"="T58"."C1")
filter(("T58"."C1">='A 0000000' AND "T58"."C1"<='A 1000000'))
Here is the DBMS_XPLAN output for Oracle 11.2.0.1 for the SQL statement:
SQL_ID abk386qc1xwkg, child number 0
-------------------------------------
SELECT /*+ USE_NL(T51 T52 T53 T54 T55 T56 T57 T58) */ T51.C1,
T51.C2, T52.C1, T52.C2, T53.C1, T53.C2, T54.C1,
T54.C2, T55.C1, T55.C2, T56.C1, T56.C2, T57.C1,
T57.C2, T58.C1, T58.C2 FROM T5 T51, T5 T52, T5 T53,
T5 T54, T5 T55, T5 T56, T5 T57, T5 T58 WHERE
T51.C1=T52.C1 AND T51.C1=T53.C1 AND T51.C1=T54.C1 AND
T51.C1=T55.C1 AND T51.C1=T56.C1 AND T51.C1=T57.C1 AND
T51.C1=T58.C1 AND T51.C1 BETWEEN 'A 0000000' AND 'A 1000000'
Plan hash value: 2002323537
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1000K|00:00:00.50 | 26055 |
|* 1 | TABLE ACCESS FULL| T5 | 1 | 1000K| 1000K|00:00:00.50 | 26055 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("T51"."C1">='A 0000000' AND "T51"."C1"<='A 1000000'))
Oracle ignored my hint – darn bugs. Is that the twist? 
Recent Comments