查询视图v$access很慢

QUERY USING V$ACCESS IS RUNNING SLOW (文档 ID 549895.1)

---

In this Document   Symptoms   Cause      Bad plan   Solution 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: Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.3 - Release: 10.2 to 11.2 Information in this document applies to any platform. SYMPTOMS 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.  CAUSE 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.. SOLUTION If statistics are inaccurate, then the choice of a cartesian product can severely affect performance. 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. 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 | ------------------------------------------------------------------------------------------------ Now the query is running faster.