方法一.  

select /*+ rule*/ * from v$access where Object= 'TTS079';

方法二.参考MOS文档ID 549895.1

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

APPLIES TO:

http://www.cndba.cn/Expect-le/article/114

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.3 - Release: 10.2 to 11.2http://www.cndba.cn/Expect-le/article/114

Information in this document applies to any platform.

SYMPTOMS

http://www.cndba.cn/Expect-le/article/114

Query using v$access is running slow. 

select * from v$access where sid= &sid_of_session; 

The above takes 3.5 secs normally but when using rule hint it takes .2 secs. 

CAUSEhttp://www.cndba.cn/Expect-le/article/114

Bad plan

SQL> select * from v$access where sid=38; 
no rows selected 
Elapsed: 00:00:04.14 
Execution Plan 
---------------------------------------------------------- 
Plan hash value: 1154447258 
|  Id | Operation              | Name            |Rows | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT       |                 | 105 | 60480 |     1 (100)| 00:00:01 | 
|*  1 | VIEW                   | GV$ACCESS       | 105 | 60480 |     1 (100)| 00:00:01 | 
|   2 | HASH UNIQUE            |                 | 105 | 70980 |     1 (100)| 00:00:01 | 
|   3 | NESTED LOOPS           |                 | 105 | 70980 |     0 (0)  | 00:00:01 | 
|   4 | NESTED LOOPS           |                 |  10 |  1080 |     0 (0)  | 00:00:01 | 
|   5 | MERGE JOIN CARTESIAN   |                 | 100 |  8300 |     0 (0)  | 00:00:01 |       <======
|*  6 | FIXED TABLE FULL       | X$KSUSE         |   1 |    45 |     0 (0)  | 00:00:01 | 
|   7 | BUFFER SORT            |                 | 100 |  3800 |     0 (0)  | 00:00:01 | 
|   8 | FIXED TABLE FULL       | X$KGLDP         | 100 |  3800 |     0 (0)  | 00:00:01 | 
|*  9 | FIXED TABLE FIXED INDEX| X$KGLLK (ind:1) |   1 |    25 |     0 (0)  | 00:00:01 | 
|* 10 | FIXED TABLE FIXED INDEX| X$KGLOB (ind:1) |  10 |  5680 |     0 (0)  | 00:00:01 | 
----------------------------------------------------------------------------------------

The optimizer is using merge join cartesian which decreases the performance in this case..

SOLUTIONhttp://www.cndba.cn/Expect-le/article/114

If statistics are inaccurate, then the choice of a cartesian product can severely affect performance.http://www.cndba.cn/Expect-le/article/114

To resolve the issue, accurate statistics should be gathered.

See:

Note:1226841.1 How To: Gather Statistics for the Cost Based Optimizer

NOTE: There is nothing inherently 'wrong' with a plan using a Cartesian. If one of the sides of the query returns a single row then it is a highly efficient operation. Problems can occur when the optimizer thinks there is 1 row when there isn't.http://www.cndba.cn/Expect-le/article/114

If this is not possible in the short term, then you can disable the cartesian functionality to avoid these sorts of plans which might help with the problem if the choice of the cartesian is the cause. You can set the following parameter to disable the cartesian join:-

_optimizer_cartesian_enabled=false;

For example:

 

SQL> alter session set "_optimizer_cartesian_enabled"=false; 
SQL> select * from v$access where sid=38;
no rows selected 
Elapsed: 00:00:00.02 
Execution Plan 
---------------------------------------------------------- 
Plan hash value: 81585914 
------------------------------------------------------------------------------------------------ 
| Id | Operation                       | Name            |Rows | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------------------------ 
|  0 | SELECT STATEMENT                |                 | 105 | 60480 |     2 (100)| 00:00:01 | 
|* 1 |  VIEW                           | GV$ACCESS       | 105 | 60480 |     2 (100)| 00:00:01 | 
|  2 |   HASH UNIQUE                   |                 | 105 | 70980 |     2 (100)| 00:00:01 | 
|  3 |    NESTED LOOPS                 |                 | 105 | 70980 |     1 (100)| 00:00:01 | 
|  4 |     NESTED LOOPS                |                 |  10 |  1080 |     1 (100)| 00:00:01 | 
|* 5 |      HASH JOIN                  |                 |   1 |    70 |     1 (100)| 00:00:01 | 
|* 6 |       FIXED TABLE FULL          | X$KSUSE         |   1 |    45 |       0 (0)| 00:00:01 | 
|  7 |       FIXED TABLE FULL          | X$KGLLK         | 100 |  2500 |       0 (0)| 00:00:01 | 
|* 8 |      FIXED TABLE FIXED INDEX    | X$KGLDP (ind:1) |  10 |   380 |       0 (0)| 00:00:01 | 
|* 9 |     FIXED TABLE FIXED INDEX     | X$KGLOB (ind:1) |  10 |  5680 |       0 (0)| 00:00:01 | 
-----------------------------------------------------------------------------------------------

版权声明：本文为博主原创文章，未经博主允许不得转载。