Home > Oracle, Performance > APPEND hint and CONSISTENT READs

APPEND hint and CONSISTENT READs


APPEND hint can positively influence the CONSISTENT GETs.  Suppose one session is inserting data to a table for day end data load while others are reading from the tables.  Since the user loading the data is not committed, other users should not get that data, but cannot avoid consistent gets while indirect path load.  Means, the session has to read and filter the uncommitted blocks.  Waste of time, more CPU due to CBC latches.  While loading the in DIRECT path load, data will be loaded above the HWM, hence will read the blocks below the HWM, to get their committed data.

Below test case shows this clearly, the constantly changing consistent gets though not committed.  I always get 191 rows..

SQL> select * from test where object_id < 200;

191 rows selected.
Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10113 | 918K| 2133 (3)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 10113 | 918K| 2133 (3)| 00:00:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_ID”<200)
Statistics
———————————————————-
2 recursive calls
1 db block gets
135 consistent gets
0 physical reads
4136 redo size
9490 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
191 rows processed

For the subsequent SELECT query returned 191 rows, but CONSISTENT gets continued to increase till the INSERT completed.

SQL> /

191 rows selected.
Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10113 | 918K| 2133 (3)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 10113 | 918K| 2133 (3)| 00:00:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_ID”<200)
Statistics
———————————————————-
1 recursive calls
1 db block gets
1539 consistent gets
657 physical reads
132 redo size
9490 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
191 rows processed

SQL> /

191 rows selected.
Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10113 | 918K| 2133 (3)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 10113 | 918K| 2133 (3)| 00:00:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_ID”<200)
Statistics
———————————————————-
1 recursive calls
1 db block gets
2464 consistent gets
647 physical reads
132 redo size
9490 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
191 rows processed

SQL> /

191 rows selected.
Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10113 | 918K| 2133 (3)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 10113 | 918K| 2133 (3)| 00:00:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_ID”<200)
Statistics
———————————————————-
1 recursive calls
1 db block gets
3083 consistent gets
799 physical reads
132 redo size
9490 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
191 rows processed

SQL> /

191 rows selected.
Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10113 | 918K| 2133 (3)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 10113 | 918K| 2133 (3)| 00:00:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_ID”<200)
Statistics
———————————————————-
1 recursive calls
1 db block gets
3497 consistent gets
912 physical reads
176 redo size
9490 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
191 rows processed

SQL> /

191 rows selected.
Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10113 | 918K| 2133 (3)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 10113 | 918K| 2133 (3)| 00:00:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_ID”<200)
Statistics
———————————————————-
1 recursive calls
1 db block gets
4167 consistent gets
1036 physical reads
132 redo size
9490 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
191 rows processed

SQL> /

191 rows selected.
Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10113 | 918K| 2133 (3)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 10113 | 918K| 2133 (3)| 00:00:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_ID”<200)
Statistics
———————————————————-
1 recursive calls
1 db block gets
7235 consistent gets
2315 physical reads
132 redo size
9490 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
191 rows processed

SQL> /

191 rows selected.
Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10113 | 918K| 2133 (3)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 10113 | 918K| 2133 (3)| 00:00:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_ID”<200)
Statistics
———————————————————-
1 recursive calls
1 db block gets
12248 consistent gets
4500 physical reads
132 redo size
9490 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
191 rows processed

SQL> /

191 rows selected.
Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10113 | 918K| 2133 (3)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 10113 | 918K| 2133 (3)| 00:00:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_ID”<200)
Statistics
———————————————————-
1 recursive calls
1 db block gets
14888 consistent gets
5692 physical reads
132 redo size
9490 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
191 rows processed

The last SELECT returns 191 records with 14888 consistent gets and 5692 physical reads while the first SELECT returned 191 rows  with 135 consistent gets only.  This is happening because I was loading data in traditional path load from another session.  That is forcing other sessions to read unwanted data blocks of not their fault!  To save others, you must load above the HWM, so that the users will read below the HWM avoiding the new blocks.

SQL> /

191 rows selected.
Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10113 | 918K| 2133 (3)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 10113 | 918K| 2133 (3)| 00:00:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_ID”<200)
Statistics
———————————————————-
2 recursive calls
1 db block gets
135 consistent gets
0 physical reads
4136 redo size
9490 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
191 rows processed

SQL> /

191 rows selected.
Execution Plan
———————————————————-
Plan hash value: 217508114

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 10113 | 918K| 2133 (3)| 00:00:26 |
|* 1 | TABLE ACCESS FULL| TEST | 10113 | 918K| 2133 (3)| 00:00:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_ID”<200)
Statistics
———————————————————-
0 recursive calls
0 db block gets
77 consistent gets
16 physical reads
0 redo size
9490 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
14 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
191 rows processed

Even after inserting 1054528 rows into the table, still I got the same consistent gets.

SQL> insert /*+ APPEND */ into test select * from test1;

1054528 rows created.

So, APPEND hint not only helping us to reduce the UNDO and faster processing, but also not forcing others session to read the uncommitted data and rollback.  Some times, these large use of UNDO can cause even ORA-01555 snapshot too old error.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: