CURSOR SHARING=SIMILAR
From OraWiki
Contents |
Author
| (c) 2007 by Steven of Oz | ||
|---|---|---|
| Author | ||
| Name | Gerald Roehrbein | |
| http | www.OraForecast.com | |
| Gerald.Roehrbein@OraForecast.com | ||
| Release | 0.9 | |
| Created | 2009.06.12 | |
| Last update | 2009.06.12 | |
| Changes | Started article | |
| Estimated duration of installation | none | |
| License | Freeware | |
Purpose
Environment: Oracle 10.2.0.4, AIX 5.3, 3 Node RAC
Show a not documented Oracle Bug using CURSOR_SHARING=SIMILAR
Oracle already have a note and testcase for that created by me.
Installation
- None
Todo
- Nothing
Recommendations
- Do not use CURSOR_SHARING=SIMILAR as database default. Use it at SESSION Level.
Example(s)
Plan with CURSOR_SHARING=SIMILAR:
Elapsed: 00:01:01.27
Execution Plan
----------------------------------------------------------
Plan hash value: 1234567
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1312 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE | 1 | 1312 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | INDEX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(""='VALUE')
filter(""<>'VALUE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1417603 consistent gets
4363 physical reads
0 redo size
4549 bytes sent via SQL*Net to client
1969 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
Plan with CURSOR_SHARING=EXACT:
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1234567
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1312 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TABLE | 1 | 1312 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | INDEX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(""='VALUE')
filter(""<>'VALUE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
4549 bytes sent via SQL*Net to client
1969 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
If you compare both plans than you will see that SQL*Plus shows same QEP but different # of LIO's. A session trace for the slow query shows a different QEP as SQL*Plus. This is a heavy problem for performance experts because we can not trust one of our best friends:
SQL*Plus set autotrace trace
Security related issues
- None
How to make this feature as secure as possible
- None.
Source code of this extension
- See above
Planned enhancements
- Will follow
Discussions
This is an essential problem performance experts have to worry about!
History
- 2009.06.12 Start of documentation
Known bugs
- Reported Description of problem Fixed Release
- 2009.06.12 No issues known
Running example
- Not required
