SR 3-7298149111 : (HOLD) something seems un

Severity	1-Critical	Status	Closed
Escalation Status	Never Escalated	Opened	May 31, 2013 5:20 PM (3+ months ago)
Last Updated	Aug 1, 2013 10:19 PM (1+ month ago)
Bug Reference	No Related Bugs	Attachments	No Related Attachments
Related Articles	353670.1	Related SRs	No Related SRs
Support Identifier	18553230
Account Name	The Store Inc
Primary Contact	liu yang	Alternate Contact
System		Host	No Related Hosts
Product	Oracle Database - Enterprise Edition
		Product Version	11.2.0.3
Operating System	Linux x86-64	OS Version	Oracle Linux 5
Project		Project Milestone
Problem Description	seems met bug: first I using sqlplus and set auto trace exp and execute sql with bind value (sql plan not correct and seems that oracle not peeking user bind value) but When I actually run this sql via sqlplus We got correct SQL plan ( oracle peeking bind value and choose full table scan ) you can check test_detail for more information.

History

Close Requested By Customer

Oracle Support

- Jul 25, 2013 1:14 PM (2+ months ago)

done

Notes

Oracle Support

- Jul 25, 2013 9:45 AM (2+ months ago)

Dear customer,

Your last update is kindly acknowledged, kindly let me know if you have further queries.

Best Regards, Feng Gao

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 25, 2013 9:22 AM (2+ months ago)

nice job. thank you

ODM Answer

Oracle Support

- Jul 24, 2013 8:28 PM (2+ months ago)

尊敬的客户您好：

针对这个问题我做了如下测试：

1. 我可以在不同的版本重现这个问题

2. 使用各种set autot的语法，之后再执行这条SQL:
对于这条SQL会真正执行的参数(如，set AUTOT ON EXP STAT, set autot on exp，和set autot traceonly), SQL的Plan会bind peek
对于不真正执行SQL的参数(set autot trace exp), SQL的 Plan 不会bind peek

综上，我们可以得到下面的认识：在set autot的时候，如果执行SQL了，那么会bind peek；如果只是parse，那么不会bind peek。我认为这是一个正常的现象。因为根据bug 4026101的描述，bind peek看起来是在一个特殊的阶段去实施的，而set autot trace exp的操作不会触发bind peek；但是set AUTOT ON EXP STAT, set autot on exp，和set autot traceonly则会触发

Thank you, Feng Gao, Oracle Software Support
-----------------------------------
中文社区：https://communities.oracle.com/portal/server.pt/community/%E6%95%B0%E6%8D%AE%E5%BA%93/549

ODM Test Case

Oracle Support

- Jul 24, 2013 8:23 PM (2+ months ago)

Test Case 1:
---------------
我可以重现这个问题在以下的版本里：

a). 10.2.0.5
b). 10.2.0.2
c). 11.2.0.3

所以这个现象是通用的

Test Case 2:
---------------
我尝试了下面的测试：

a). 使用explain plan for <...SQL...>, 这时候v$sql里面只有explain plan for <...SQL...>的语句，而没有<...SQL...>，所以再次执行这条 SQL 时会bind peek(因为没有以存在的plan)
b). 使用 set autot trace exp, 这时候v$sql中有explain plan for <...SQL...>的语句，同时也有<...SQL...>的plan, 这时候下次再执行就不会bind peek了，因为不好的plan已经存在了 *** 注意，set autot trace exp并不会真正执行SQL
c). 使用 set AUTOT ON EXP STAT, 以及 set autot on exp，和set autot traceonly，这些时候我注意到plan是好的，bind peek发生了 *** 注意，这些情况下会真正的执行这条SQL

综上测试，我得到的结论是：

在执行完 set autot之后，如果下面的SQL会真正执行，那么会发生bind peek；如果SQL并没有真正执行，那么bind peek不会发生。

ODM Test Case

Oracle Support

- Jul 24, 2013 3:27 PM (2+ months ago)

TestCase:
--------------
Trying to reproduce the bad scenario in 10.2.0.5.

Notes

Oracle Support

- Jul 22, 2013 11:51 PM (2+ months ago)

Dear customer,

Sorry to keep you waiting, but I am still performing testing to investigate it further.

Currently I am performing the following testings:

1. Try the testcase in different RDBMS versions
2. Try the testcase with SQLT Xplore method to on/off various CBO parameters/fix controls

I am hoping to get back to you soon.

Best Regards, Feng Gao

Notes

Oracle Support

- Jul 22, 2013 9:43 AM (2+ months ago)

Dear customer,

I am still performing the testing, will get back to you by today.

Best Regards, Feng Gao

Notes

Oracle Support

- Jul 19, 2013 12:03 PM (2+ months ago)

Performing testcase...

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 18, 2013 5:38 PM (2+ months ago)

I have upload peek.dmp You can download it now :)

Notes

Oracle Support

- Jul 18, 2013 5:36 PM (2+ months ago)

Dear customer,

We received and will review your update! Thanks for your patience!

Thank you, Feng Gao, Oracle Software Support
-----------------------------------
中文社区：https://communities.oracle.com/portal/server.pt/community/%E6%95%B0%E6%8D%AE%E5%BA%93/549

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 18, 2013 5:33 PM (2+ months ago)

Upload to gtcr successful for the file peek.tar.gz.

Notes

Oracle Support

- Jul 18, 2013 5:12 PM (2+ months ago)

Dear customer,

Oracle FTP servers are retired, could you please send the file to this mail address? db.oracle.cn@gmail.com

Thank you, Feng Gao, Oracle Software Support
-----------------------------------
中文社区：https://communities.oracle.com/portal/server.pt/community/%E6%95%B0%E6%8D%AE%E5%BA%93/549

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 18, 2013 5:04 PM (2+ months ago)

Could you give me a ftp server ? Seems that I can't upload peek.dmp to this SR

ODM Action Plan

Oracle Support

- Jul 18, 2013 4:45 PM (2+ months ago)

Dear customer,

No, for now. You could compress it and then upload it to this SR

Thank you, Feng Gao, Oracle Software Support
-----------------------------------
中文社区：https://communities.oracle.com/portal/server.pt/community/%E6%95%B0%E6%8D%AE%E5%BA%93/549

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 18, 2013 4:42 PM (2+ months ago)

whether you got peek.dmp?

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 18, 2013 4:23 PM (2+ months ago)

call me now, we chat this problem.

ODM Action Plan

Oracle Support

- Jul 18, 2013 4:11 PM (2+ months ago)

Dear customer,

+ Since we don't have stats in the table, please kindly upload the table data to this SR as well, so that I could reproduce the same problem.

+ I also tried to reproduce this same issue with below steps but failed to get FTS plan:
create table peek as select * from dba_objects;
insert into table peek select * from peek;
...
..
SQL> select count(*) from peek;

COUNT(*)
----------
4942280

create index IDX_PEEK on PEEK (OBJECT_ID);

Thank you, Feng Gao, Oracle Software Support
-----------------------------------
中文社区：https://communities.oracle.com/portal/server.pt/community/%E6%95%B0%E6%8D%AE%E5%BA%93/549

ODM Data Collection

Oracle Support

- Jul 18, 2013 3:29 PM (2+ months ago)

Name
--------
=== ODM Data Collection ===

CUSTOMER STATEMENT
--------------------------------------
Copied from SR Text:
seems that peek table doesn't has statistics.

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 18, 2013 3:11 PM (2+ months ago)

seems that peek table doesn't has statistics.

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 18, 2013 3:11 PM (2+ months ago)

Upload to gtcr successful for the file stat.dmp.

ODM Action Plan

Oracle Support

- Jul 18, 2013 3:01 PM (2+ months ago)

Dear customer,

Unfortunately, SQLT didn't collect peek table statistics as expected.
Could you please kindly export the table statistics for this table manually?

1. create stats table and move the stats
SQL> connect username/password
SQL> exec dbms_stats.create_stat_table(NULL,'STAT');
SQL> exec dbms_stats.export_table_stats(NULL,'PEEK',NULL,'STAT');

2. export the stat table
exp system/manager tables=<OWNER>.STAT file=STAT.dmp log=STAT.log

3. upload the STAT.dmp file to this SR

Thank you, Feng Gao, Oracle Software Support
-----------------------------------
中文社区：https://communities.oracle.com/portal/server.pt/community/%E6%95%B0%E6%8D%AE%E5%BA%93/549

ODM Test Case

Oracle Support

- Jul 18, 2013 2:50 PM (2+ months ago)

TEST CASE:
------------------

1. Imported the testcase TC41045 & TC41044 to testing database
2. But seems peek table is not imported and review the SQLT report, the following error observed:

Review log and fix following errors:
sqlt$a: *** d:ORA-20000: TABLE "SYS"."PEEK" does not exist or insufficient privileges
sqlt$a: *** d:could not export stats for table = "PEEK", owner = "SYS"

| Stats from id "s41044_testdb_db42"
| have been restored into data dict
|
| METRIC IN STATTAB RESTORED OK
| ------------- ---------- -------- --
| STATS ROWS: 0 0 OK
| TABLES: 0 0 OK
| TABLE PART: 0 0 OK
| TABLE SUBPART: 0 0 OK
| INDEXES: 0 0 OK
| INDEX PART: 0 0 OK
| INDEX SUBPART: 0 0 OK
| COLUMNS: 0 0 OK
| COLUMN PART: 0 0 OK
| COLUMN SUBPART: 0 0 OK
| AVG AGE DAYS: 0 0 OK
|

Notes

Oracle Support

- Jul 18, 2013 11:45 AM (2+ months ago)

Dear customer,

In order to diagnose and find a proper solution to the problem, I am currently doing in-house test.
Please stand by for an update shortly and we thank you for your patience.

Thank you, Feng Gao, Oracle Software Support
-----------------------------------
中文社区：https://communities.oracle.com/portal/server.pt/community/%E6%95%B0%E6%8D%AE%E5%BA%93/549

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 18, 2013 11:43 AM (2+ months ago)

Upload to gtcr successful for the file louis_SR_3-7298149111_1.txt.

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 18, 2013 11:43 AM (2+ months ago)

Upload to gtcr successful for the file sqlt_s41045_xtract_cw82fa1q8w8s4.zip.

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 18, 2013 11:42 AM (2+ months ago)

Upload to gtcr successful for the file sqlt_s41044_xtract_cw82fa1q8w8s4.zip.

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 18, 2013 11:41 AM (2+ months ago)

upload detail .

sqlt_s41044_xtract_cw82fa1q8w8s4.zip ----set autotrace

sqlt_s41045_xtract_cw82fa1q8w8s4.zip ----actually run sql in sqlplus

ODM Action Plan

Oracle Support

- Jul 18, 2013 11:04 AM (2+ months ago)

Dear customer,

Please kindly upload SQLT output for the problematic SQL as following:

1. If SQLT is not installed, please kindly download SQLT and Chinese version instruction from note 1526574.1
2. Once installed please gather SQLT output like below:

a). If the cursor is already in shared pool, then use XTRACT method:

cd sqlt
SQL> start run/sqltxtract.sql <sql_id_of_problematic_SQL>

b). If the cursor is not in shared pool, then use XECUTE method:

create a sql file named eg.sql which contains the problematic SQL

eg:

$ cat eg.sql
select * from dual;

get the report using

SQL> START run/sqltxecute.sql eg.sql
Password: <password>

Kindly let me know if you have any doubt regarding the action plan

Thank you, Feng Gao, Oracle Software Support
-----------------------------------
中文社区：https://communities.oracle.com/portal/server.pt/community/%E6%95%B0%E6%8D%AE%E5%BA%93/549

ODM Issue Verification

Oracle Support

- Jul 18, 2013 10:53 AM (2+ months ago)

Verified the issue in the log file as noted below:

LOG FILE
-----------------------
Filename =Test_detail
See the following error:

SQL> ! ora params _user_binds

NAME VALUE IS_DEFAULT SES_MODIFIABLE SYS_MODIFIABLE DESCRIPTION
--------------------------------------------- --------------- --------------- --------------- --------------- --------------------------------------------------------------------------------
_optim_peek_user_binds TRUE FALSE true immediate enable peeking of user binds

SQL> variable n number;
SQL> execute :n :=2;

PL/SQL procedure successfully completed.

SQL> set autot trace exp;
SQL> select /*+test*/ * from peek where object_id=:n;

Execution Plan
----------------------------------------------------------
Plan hash value: 2024383757

--------------------------------------------------------------------------------
--------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |

--------------------------------------------------------------------------------
--------

| 0 | SELECT STATEMENT | | 65 | 6175 | 7 (0)| 00
:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| PEEK | 65 | 6175 | 7 (0)| 00
:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_PEEK | 65 | | 3 (0)| 00
:00:01 |

--------------------------------------------------------------------------------
--------

Predicate Information (identified by operation id):

---------------------------------------------------

SQL> @xm 1821254404 %
old 21: hash_value in (&1)
new 21: hash_value in (1821254404)
old 22: and to_char(child_number) like '&2' -- to_char is just used for convenient filtering using % for all children
new 22: and to_char(child_number) like '%' -- to_char is just used for convenient filtering using % for all children

Ch Pr Op Objcect Optimizer Optim rows Optim bytes Optimizer
ld ed ID Operation Name Cost from step from step Mode
-- -- ---- ------------------------------------------------------- ------------------------------ --------- ---------- ----------- ----------
0 0 SELECT STATEMENT 7 ALL_ROWS
1 TABLE ACCESS BY INDEX ROWID PEEK 7 65 6175
A 2 INDEX RANGE SCAN IDX_PEEK 3 65

ODM Issue Clarification

Oracle Support

- Jul 18, 2013 10:52 AM (2+ months ago)

On : 11.2.0.3 version, RDBMS

ACTUAL BEHAVIOR
---------------
If set autotrace on, then the SQL generated bad execution plan
If not set autotrace on, good plan will be generated

EXPECTED BEHAVIOR
-----------------------
no matter autotrace on or off, we need get one good execution plan

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
set autotrace on
select /*+test*/ * from peek where object_id=:n;

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, it impact the DB performance

Notes

Oracle Support

- Jul 18, 2013 9:50 AM (2+ months ago)

Reviewing attached file and previous updates...

First Contact

Oracle Support

- Jul 18, 2013 9:42 AM (2+ months ago)

First Phone Contact Comments
--------------------------------------------------------
Called customer and he said:

+ This problem impacts the production system, but not big.
+ He agreed to remove 24x7 flag for this SR

Notes

Oracle Support

- Jul 18, 2013 9:41 AM (2+ months ago)

Dear customer,

Thank you for using My Oracle Support.
We are currently reviewing/researching the situation and will update the Service Request as soon as we have relevant information.
Thank you for your patience.

Thank you, Feng Gao, Oracle Software Support

-------------------------------------------------------------------------------------------------
我们很荣幸地宣布My Oracle Support推出了专门面向中文用户的技术社区。
欢迎访问“https://communities.oracle.com/portal/server.pt/community/%E6%95%B0%E6%8D%AE%E5%BA%93/549”来参与到我们的第一个中文数据库社区。

谢谢！
Oracle全球客户支持

Severity 1 Activity

Oracle Support

- Jul 18, 2013 9:32 AM (2+ months ago)

At the request of liu yang the SR's Severity has been changed to Severity 1.

The Service Request will be worked 24x7. If you need to lower the Severity you can use the "Lower Severity" button in the Update region. The following 24x7 contact information was provided:

Severity 1 - 24x7 Manager
---------------------------------------------------
Name: liuyang
24x7 Telephone Number: +8618666668061
E-mail Address: ylouis83@gmail.com

Primary 24x7 Service Request Contact Information
----------------------------------------------------------------------
Name: liu yang
24x7 Telephone Number: +8618666668061
E-mail Address: ylouis83@gmail.com
Contact Method: Web

Severity 1 - Secondary 24x7 Contact
----------------------------------------------------------------------
Name: liu yang
24x7 Telephone Number: +8618666668061
Alternate 24x7 Telephone Number: +8618666668061
E-mail Address: ylouis83@gmail.com

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 17, 2013 4:56 PM (2+ months ago)

please escalate this SR to level-1

ODM Action Plan

Oracle Support

- Jul 17, 2013 4:54 PM (2+ months ago)

Hi liu
Apologize for the delay in response,
The issue does not reproduce on my machine.
is it possible to export the peek table and upload to the SR.

Regards
Maged

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 8, 2013 9:38 AM (2+ months ago)

I create this table like this:

create table peek as select * from dba_objects;
insert into table peek select * from peek;
...
..
SQL> select count(*) from peek;

COUNT(*)
----------
4942280

create index IDX_PEEK on PEEK (OBJECT_ID)
tablespace SYSTEM;

ODM Action Plan

Oracle Support

- Jul 5, 2013 10:12 PM (2+ months ago)

would you provide how the table peek is created? so we can reproduce the issue in house.

Regards
Maged

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 5, 2013 6:14 PM (2+ months ago)

upload autotrace.log

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 5, 2013 6:14 PM (2+ months ago)

Upload to gtcr successful for the file autotrace.log.

Call Back - Customer

Oracle Support

- Jul 5, 2013 5:22 PM (2+ months ago)

Call Back Customer
------------------------------------
Customer contact name : liu yang

Phone#: +8618666668061

Reason for Call Back? Customer called and requested engineer call back now

Action Taken? (completed after customer contact)

Call - Inbound

Oracle Support

- Jul 5, 2013 5:19 PM (2+ months ago)

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 5, 2013 5:12 PM (2+ months ago)

you can call me now

I run again this query

SQL> select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4',null,'advanced ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cw82fa1q8w8s4, child number 0
-------------------------------------
select /*+test*/ * from peek where object_id=:n

Plan hash value: 2024383757

--------------------------------------------------------------------------------
---------

| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E
-Time |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
---------

| 0 | SELECT STATEMENT | | | | 7 (100)|
|

| 1 | TABLE ACCESS BY INDEX ROWID| PEEK | 65 | 6175 | 7 (0)| 0
0:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_PEEK | 65 | | 3 (0)| 0

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0:00:01 |

--------------------------------------------------------------------------------
---------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / PEEK@SEL$1
2 - SEL$1 / PEEK@SEL$1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "PEEK"@"SEL$1" ("PEEK"."OBJECT_ID"))
END_OUTLINE_DATA
*/

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

2 - access("OBJECT_ID"=:N)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "PEEK"."OWNER"[VARCHAR2,30], "PEEK"."OBJECT_NAME"[VARCHAR2,128],
"PEEK"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"PEEK"."DATA_OBJECT_ID"[NUMBER,22], "PEEK"."OBJECT_TYPE"[VARCHAR2,19],
"PEEK"."CREATED"[DATE,7], "PEEK"."LAST_DDL_TIME"[DATE,7],
"PEEK"."TIMESTAMP"[VARCHAR2,19], "PEEK"."STATUS"[VARCHAR2,7],
"PEEK"."TEMPORARY"[VARCHAR2,1], "PEEK"."GENERATED"[VARCHAR2,1],
"PEEK"."SECONDARY"[VARCHAR2,1], "PEEK"."NAMESPACE"[NUMBER,22],

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"PEEK"."EDITION_NAME"[VARCHAR2,30]
2 - "PEEK".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

Note
-----
- Warning: basic plan statistics not available. These are only collected when
:

* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
l

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

61 rows selected.

Elapsed: 00:00:00.31

Notes

Oracle Support

- Jul 5, 2013 4:44 PM (2+ months ago)

ACTION PLAN
=============

spool <spool file>
set timing on
set autot trace exp;
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set statistics_level=ALL;
alter session set timed_statistics = true;

variable n number;
execute :n :=2;

select /*+test*/ * from peek where object_id=:n;

--wait until it finishes,

set long 40000
set lines 500
set longc 120
set pages 2000

select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4',null,'advanced ALLSTATS LAST'));

spool off

2)open a new session :

spool <spool file>
set timing on
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set statistics_level=ALL;
alter session set timed_statistics = true;

variable n number;
execute :n :=2;

select /*+test*/ * from peek where object_id=:n;

--wait until it finishes,

set long 40000
set lines 500
set longc 120
set pages 2000

select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4',null,'advanced ALLSTATS LAST'));

spool off

and upload the generated spool files

Best Regards
Maged

ODM Research

Oracle Support

- Jul 5, 2013 4:39 PM (2+ months ago)

here is the source of confusion:

auto_exp.log
===========

SQL> select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4',null,'advanced ALLSTATS LAST'));
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3713220770

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | 8168 | 16336 | 29 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

looks you run "select * from table(dbms_xplan.display_cursor(null,null,'advanced ALLSTATS LAST'));"
instead of "select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4',null,'advanced ALLSTATS LAST'));"
so you got :
the (Plan hash value: 3713220770 )
.
COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR

Notes

Oracle Support

- Jul 4, 2013 11:16 PM (2+ months ago)

Sorry for the delay
I will call you tomorrow morning.
Regards
Maged

Update from Customer

YLOUIS83@GMAIL.COM

- Jul 3, 2013 4:23 PM (2+ months ago)

if you don't have time to solve this problem

I'll escalate the SR to top level .

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 23, 2013 10:21 PM (3+ months ago)

I'm in china If you want to call me you can check our country time (AM 10:00 - PM 10:00 is ok)

Notes

Oracle Support

- Jun 21, 2013 10:12 PM (3+ months ago)

Apologize liu for missing you calls
Looks the time zone difference is responsible for missing your calls.
are you available now to call you.
Please let me know the time you prefere to call you on.

Best Regards
Maged

Call - Inbound

Oracle Support

- Jun 20, 2013 10:36 AM (3+ months ago)

Call - Inbound
----------------------------
Customer contact name: liu yang
Verify preferred method of contact: PHONE OR My Oracle Support customer portal

Phone#: +8618666668061
Email: ylouis83@gmail.com

Comments for this update: Customer called in requesting the engineer's call back when he is on shift.

ACTION PLAN
--------------------
Will inform the engineer.

Thank you.
Michelle
Oracle Global Customer Support

Call - Inbound

Oracle Support

- Jun 20, 2013 10:32 AM (3+ months ago)

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 14, 2013 2:24 PM (3+ months ago)

call me now

thanks

Call - Outbound

Oracle Support

- Jun 10, 2013 6:12 PM (3+ months ago)

Call - Outbound
------------------------------
called liu on +8618666668061 ,
he was busy ,

Please let me know your preffered time so I can call in

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 9, 2013 10:55 AM (3+ months ago)

you can call me ?

+86 18666668061

ODM Research

Oracle Support

- Jun 8, 2013 7:12 PM (3+ months ago)

Thanks liu
the uploaded files has some discrepancy ,for example normal.log has "4792513 rows selected." while the plan have only 631 rows returned.
also auto_exp.log "select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4..." get the plan for different sql.

we need to organize OWC session so we can see the same things .
Please let me know you preferred time.
Best Regards
Maged

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 8, 2013 10:30 AM (3+ months ago)

Upload to gtcr successful for the file normal.log.

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 8, 2013 10:30 AM (3+ months ago)

Upload to gtcr successful for the file auto_exp.log.

ODM Action Plan

Oracle Support

- Jun 7, 2013 11:15 PM (3+ months ago)

I can not file the spool file in the uploaded file test_detail.zip

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 7, 2013 4:15 PM (3+ months ago)

I have already run your scripts and the result is in test_detail.zip

ODM Action Plan

Oracle Support

- Jun 6, 2013 5:56 PM (3+ months ago)

Thanks Liu
yes I see it is not executed it look the auto trace cursor .

would you run my last action plan ,I repeat here for your conveniences ,
try to use
select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4',null,'advanced ALLSTATS LAST'));
instead of @xm in the Test_detail script .

spool <spool file>
set timing on
set autot trace exp;
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set statistics_level=ALL;
alter session set timed_statistics = true;

variable n number;
execute :n :=2;

select /*+test*/ * from peek where object_id=:n;

--wait until it finishes,

set long 40000
set lines 500
set longc 120
set pages 2000

select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4',null,'advanced ALLSTATS LAST'));

spool off

2)open a new session :

spool <spool file>
set timing on
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set statistics_level=ALL;
alter session set timed_statistics = true;

variable n number;
execute :n :=2;

select /*+test*/ * from peek where object_id=:n;

--wait until it finishes,

set long 40000
set lines 500
set longc 120
set pages 2000

select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4',null,'advanced ALLSTATS LAST'));

spool off

and upload the generated spool files

Best Regards
Maged

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 6, 2013 10:49 AM (3+ months ago)

Upload to gtcr successful for the file test_detail.zip.

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 6, 2013 10:46 AM (3+ months ago)

upload test_detail

You can see set_auto_exp.jpg for details (take care that ex=0 means execution=0 --- not actually run this sql but gather cursor)

ODM Action Plan

Oracle Support

- Jun 4, 2013 7:11 PM (3+ months ago)

I got your point , you mean the query take the same plan as the auto trace plan when using sqlplus autotrace
would you try to use

select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4',null,'advanced ALLSTATS LAST'));
instead of @xm in the Test_detail script .

spool <spool file>
set timing on
set autot trace exp;
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set statistics_level=ALL;
alter session set timed_statistics = true;

variable n number;
execute :n :=2;

select /*+test*/ * from peek where object_id=:n;

--wait until it finishes,

set long 40000
set lines 500
set longc 120
set pages 2000

select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4',null,'advanced ALLSTATS LAST'));

spool off

2)open a new session :

spool <spool file>
set timing on
alter system flush shared_pool;
alter system flush buffer_cache;
alter session set statistics_level=ALL;
alter session set timed_statistics = true;

variable n number;
execute :n :=2;

select /*+test*/ * from peek where object_id=:n;

--wait until it finishes,

set long 40000
set lines 500
set longc 120
set pages 2000

select * from table(dbms_xplan.display_cursor('cw82fa1q8w8s4',null,'advanced ALLSTATS LAST'));

spool off

and upload the generated spool files

Best Regards
Maged

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 4, 2013 10:50 AM (3+ months ago)

You may not understand my meaning

not why explain or set auto trace not show correct plan

but why the new cursor gathered by set auto trace is also incorrect ( actually in v$sql and v$sql_plan)

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 4, 2013 10:36 AM (3+ months ago)

as you said

"sqlplus auto trace". it does not represent the actual good plan used.

Confirm this is bug or oracle cares not for this behavior ( as set auto trace has already create a new cursor but incorrect plan)?

ODM Action Plan

Oracle Support

- Jun 4, 2013 2:46 AM (3+ months ago)

as expected "explain plan for " generate the same plan as "sqlplus auto trace".
as explain plan by definition does not use bind peeking .

the query actual runtime plan is different because it uses bind peeking .

ACTION PLAN
============
if the query actual runtime plan -with bind peeking- is good . you can ignore totally the plan generated by "sqlplus auto trace". it does not represent the actual good plan used.
if the query actual runtime plan -with bind peeking- is bad ,and you want the plan generated by "sqlplus auto trace"to be used when running the query .then you can disable bind peeking.
set _optim_peek_user_binds=false

Best Regards
Maged

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 3, 2013 11:26 PM (3+ months ago)

SQL> alter system flush shared_pool;

System altered.

SQL> variable n number;

SQL> variable n number;
SQL> execute :n :=2;

PL/SQL procedure successfully completed.

SQL> explain plan for select /*+test*/ * from peek where object_id=:n
2 ;

Explained.

SQL> set lines 2000
SQL> set head off
SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));

Plan hash value: 2024383757

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65 | 6175 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PEEK | 65 | 6175 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_PEEK | 65 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / PEEK@SEL$1
2 - SEL$1 / PEEK@SEL$1

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

2 - access("OBJECT_ID"=TO_NUMBER(:N))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

1 - "PEEK"."OWNER"[VARCHAR2,30], "PEEK"."OBJECT_NAME"[VARCHAR2,128],
"PEEK"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],

"PEEK"."DATA_OBJECT_ID"[NUMBER,22], "PEEK"."OBJECT_TYPE"[VARCHAR2,19],
"PEEK"."CREATED"[DATE,7], "PEEK"."LAST_DDL_TIME"[DATE,7],
"PEEK"."TIMESTAMP"[VARCHAR2,19], "PEEK"."STATUS"[VARCHAR2,7],
"PEEK"."TEMPORARY"[VARCHAR2,1], "PEEK"."GENERATED"[VARCHAR2,1],
"PEEK"."SECONDARY"[VARCHAR2,1], "PEEK"."NAMESPACE"[NUMBER,22],
"PEEK"."EDITION_NAME"[VARCHAR2,30]
2 - "PEEK".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

33 rows selected.

ODM Action Plan

Oracle Support

- Jun 3, 2013 6:01 PM (3+ months ago)

Hi
if you run explain plan for select /*+test*/ * from peek where object_id=:n
then

set lines 2000
set head off
select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));

i think it will return the same plan as sqlplus autotrace

please upload the output.
Best Regards
Maged

Update from Customer

YLOUIS83@GMAIL.COM

- Jun 3, 2013 10:17 AM (3+ months ago)

Hi Maged

I think you don't look over carefully . Pls see the file_detail I have uploaded carefully.

As you say "explain plan does not use bind peeking,the different plan appears" but the query actually already gather a new cursor (flush shared_pool) and when I query the cursor plan via v$sql_plan still got an error plan .

ODM Action Plan

Oracle Support

- May 31, 2013 9:16 PM (3+ months ago)

Hi liu

This is not a bug , it is expected behavior .
"sqlplus autotrace exp" runs expalin plan for the query after it is been executed ,
and as explain plan does not use bind peeking , the different plan appears
see
EXPLAIN PLAN and SQL*PLUS AUTOTRACE may not generate actual plans [ID 1268111.1]
Autotrace And Explain Plan Give Different Plans Than Row Source Trace [ID 353670.1]

Best Regards
Maged

ODM Question

Oracle Support

- May 31, 2013 9:10 PM (3+ months ago)

first I using sqlplus and set auto trace exp and execute sql with bind value
(sql plan not correct and seems that oracle not peeking user bind value)

but When I actually run this sql via sqlplus We got correct SQL plan ( oracle peeking bind value and choose full table scan )

Customer Problem Description

YLOUIS83@GMAIL.COM

- May 31, 2013 5:20 PM (3+ months ago)

Customer Problem Description
---------------------------------------------------

Problem Summary
---------------------------------------------------
something seems un

Problem Description
---------------------------------------------------
seems met bug:

first I using sqlplus and set auto trace exp and execute sql with bind value (sql plan not correct and seems that oracle not peeking user bind value)

but When I actually run this sql via sqlplus We got correct SQL plan ( oracle peeking bind value and choose full table scan )

you can check test_detail for more information.

Error Codes
---------------------------------------------------

Problem Category/Subcategory
---------------------------------------------------
Application Development (SQL, Wrong Query Results, PLSQL, XML Database, XDK, JVM, Pro*C, OCI, OCCI, XA)/Wrong or Incorrect Query Results

Uploaded Files
---------------------------------------------------
File: Test_detail

Template Question Responses
---------------------------------------------------
1) ###What is the Nature of Incorrect Results?###
Wrong SQL Plan

2) ###Has the query ever returned a correct result?###
seems that oracle can't peeking user bind value

3) Additional Information

4) ### Impact on Business ###

Update from Customer

YLOUIS83@GMAIL.COM

- May 31, 2013 5:18 PM (3+ months ago)

Upload to gtcr successful for the file Test_detail.