Chinese

今天数据库做了如下调整，修改了两个节点的local_listener参数
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.244.132)(PORT=1521))))' scope=both sid='orcl2'
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.244.131)(PORT=1521))))' scope=both sid='orcl1'

今天上午8:30分，节点一的cpu达到100%，发现下面的Sql的执行计划出现问题。

sql语句：
select *
from (select content0_.CONTENT_ID as CONTENT1_30_0_,
coontentblo1_.BLOB_ID as BLOB1_31_1_,
content0_.FOLDER_ID as FOLDER 2_30_0_,
content0_.TEMPLATE_ID as TEMPLATE3_30_0_,
content0_.CONTENT_PATH as CONTENT4_30_0_,
content0_.CONTENT_PATH_CODE as CONTEN T5_30_0_,
content0_.CONTENT_ORDER as CONTENT6_30_0_,
content0_.CONTENT_TITLE as CONTENT7_30_0_,
content0_.CONTENT_AUTHOR as CONTEN T8_30_0_,
content0_.CONTENT_WRITE_DATE as CONTENT9_30_0_,
content0_.CONTENT_TIMER_STATUS as CONTENT10_30_0_,
content0_.CONTENT_TIMER as CONTENT11_30_0_,
content0_.CONTENT_KEYWORDS as CONTENT12_30 _0_,
content0_.CONTENT_SOURCE as CONTENT13_30_0_,
content0_.CONTENT_SUMMARY as CONTENT14_30_0_,
content0_.CONTENT_STATE as CONTENT 15_30_0_,
content0_.CREATER as CREATER30_0_,
content0_.CREATION_DATE as CREATION17_30_0_,
content0_.MENDER as MENDER30_0_,
content0_.MODIFIED_DATE as MODIFIED19_30_0_,
content0_.CONTENT_VERIFY as CONTENT20_30_0_,
content0_.CONTENT_EXPIRE as CONTENT21_30_0_,
content0_.ORDER_DATE as ORDER22_30_0_,
content0_.ORDER_NUMBER as OR DER23_30_0_,
content0_.CONTENT_TYPE as CONTENT24_30_0_,
content0_ .UPDATE_DATE as UPDATE25_30_0_,
content0_.START_DATE as START26_3 0_0_,
content0_.OPENTYPE as OPENTYPE30_0_,
content0_.STATICURL as STATICURL30_0_,
content0_.AFFIX as AFFIX30_0_,
content0_.IMP_CONTENT_ID as IMP30_30_0_,
content0_.SCORE_TYPE_ID as SCORE31_30_0_,
content0_.COMMENT_TYPE_ID as COMMENT32_30_0_,
content0_.PARENT_CONTENT_ID as PARENT33_30_0_,
content0_.COMMENT_TYPE as COMMENT34_ 30_0_,
content0_.SCHEDULE_TIME as SCHEDULE35_30_0_,
content0_.ICO N_TIME as ICON36_30_0_,
content0_.CONTENT_TIME as CONTENT37_30_0_,
content0_.MESSAGE_FLAG as MESSAGE38_30_0_,
content0_.RELATED as RELATED30_0_,
content0_.RELATED_CONTENT_ID as RELATED40_30_0_,
content0_.CONTENT_HYPERLINK as CONTENT41_30_0_,
contentblo1_.CONTENT_PROPERTY as CONTENT2_31_1_,
contentblo1_.SECURITY_POLICY as SE CURITY3_31_1_,
contentblo1_.SUBSCRIPTION_POLICY as SUBSCRIP4_31_1 _
from VEGA_WCM_CONTENT_MAIN content0_,
VEGA_WCM_CONTENT_BLOB contentblo1_
where content0_.CONTENT_ID = contentblo1_.BLOB_ID
and content0_.CONTENT_STATE = :1
and content0_.CONTENT_PATH_CODE = :2
and (content0_.CREATER = 'guest' or exists
(select viewpolicy2_.POLICY_ID
from VEGA_WCM_VIEW_POLICY viewpolicy2_
where content0_.CONTENT_ID = viewpolicy2_.CONTENT_ID
and (viewpolicy2_.POLICY_TYPE || viewpolicy2_.POLICY_NAME in
('com.coolwen.core.security.impl.UserPrincipalImplguest', 'com.coolwen.core.security.impl.UserPrincipalImplgu
est'))))
order by content0_.CONTENT_ORDER DESC,
content0_.ORDER_DATE DESC,
content0_.ORDER_NUMBER DESC)
where rownum <= :3


当前执行计划：
这个sql在8:30分的时候，使用的一直都是错误的执行计划。
错误的执行计划：
Execution Plan
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 13605 (100)| |
| 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 1028 | 13M| | 13605 (1)| 00:02:44 |
| 3 | SORT ORDER BY STOPKEY | | 1028 | 717K| 12M| 13605 (1)| 00:02:44 |
| 4 | FILTER | | | | | | |
| 5 | HASH JOIN | | 17880 | 12M| 7568K| 10908 (1)| 00:02:11 |
| 6 | TABLE ACCESS FULL | VEGA_WCM_CONTENT_MAIN | 17880 | 7351K| | 1681 (1)| 00:00:21 |
| 7 | TABLE ACCESS FULL | VEGA_WCM_CONTENT_BLOB | 101K| 28M| | 7383 (1)| 00:01:29 |
| 8 | TABLE ACCESS BY INDEX ROWID| VEGA_WCM_VIEW_POLICY | 1 | 89 | | 4 (0)| 00:00:01 |
| 9 | INDEX RANGE SCAN | POLICY_CONTENT_ID | 1 | | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------

以前的执行计划：
Execution Plan
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30 (100)| |
| 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 13713 | 30 (4)| 00:00:01 |
| 3 | SORT ORDER BY STOPKEY | | 1 | 715 | 30 (4)| 00:00:01 |
| 4 | FILTER | | | | | |
| 5 | NESTED LOOPS | | 11 | 7865 | 29 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| VEGA_WCM_CONTENT_MAIN | 11 | 4631 | 7 (0)| 00:00:01 |
| 7 | INDEX RANGE SCAN | CONTENT_PATH_CODE | 12 | | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| VEGA_WCM_CONTENT_BLOB | 1 | 294 | 2 (0)| 00:00:01 |
| 9 | INDEX UNIQUE SCAN | SYS_C007292 | 1 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | VEGA_WCM_VIEW_POLICY | 1 | 89 | 4 (0)| 00:00:01 |
| 11 | INDEX RANGE SCAN | POLICY_CONTENT_ID | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
出现两种执行计划，推断原因，可能首次给传给绑定变量不理想的值，导致出现了全表扫描的执行计划。


请帮助查找原因