查询视图v$access很慢

た入场券 2022-08-12 00:25 130阅读 0赞

<table> 
 <tbody> 
  <tr> 
   <td align="left"><span style="font-size:14px; text-align:right; padding:0px 6px 0px 0px; font-weight:bold">QUERY USING V$ACCESS IS RUNNING SLOW (文档 ID 549895.1)</span></td> 
   <td align="right">&nbsp;</td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

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

![t.gif][]

<table style="table-layout:fixed; overflow:hidden"> 
 <tbody> 
  <tr> 
   <td style="overflow:hidden"><span style="font-size:12px"></span> <p><strong>In this Document</strong><br> <span style="font-family:inherit!important">&nbsp;&nbsp;<a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=i5u6w1jbj_9&amp;_afrLoop=2675233836971495#SYMPTOM" rel="nofollow">Symptoms</a><br> &nbsp;&nbsp;<a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=i5u6w1jbj_9&amp;_afrLoop=2675233836971495#CAUSE" rel="nofollow">Cause</a><br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=i5u6w1jbj_9&amp;_afrLoop=2675233836971495#aref1" rel="nofollow">Bad plan</a><br> &nbsp;&nbsp;<a href="https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=i5u6w1jbj_9&amp;_afrLoop=2675233836971495#FIX" rel="nofollow">Solution</a><br> </span></p> 
    <hr> <p></p> <p></p> 
    <table> 
     <tbody> 
      <tr> 
       <td><span style="font-family:inherit!important"><em>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.</em></span></td> 
      </tr> 
     </tbody> 
    </table> <p></p> <p></p> <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> APPLIES TO:</h2> Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.3 - Release: 10.2 to 11.2<br> Information in this document applies to any platform.<br> <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>SYMPTOMS</h2> <p>Query using v$access is running slow.&nbsp;<br> </p> 
    <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241); width:738.1500244140625px"> 
     <code style="font-family:'Courier New',Courier,monospace; white-space:pre-wrap; word-wrap:break-word">select * from v$access where sid= &amp;sid_of_session; </code> 
    </div> <br> The above takes 3.5 secs normally but when using rule hint it takes .2 secs.&nbsp;<br> <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>CAUSE</h2> <h3 style="font-size:14px; color:rgb(51,51,51); padding-top:0.5em"><a rel="nofollow"></a>Bad plan</h3> 
    <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241); width:738.1500244140625px"> 
     <code style="font-family:'Courier New',Courier,monospace; white-space:pre-wrap; word-wrap:break-word">SQL&gt; select * from v$access where sid=38; <br> <br> no rows selected <br> <br> Elapsed: 00:00:04.14 <br> <br> Execution Plan <br> ---------------------------------------------------------- <br> Plan hash value: 1154447258 </code> 
     <br> 
     <pre>|  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 |       &lt;======
|*  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 | 
---------------------------------------------------------------------------------------- 
</pre> 
    </div> <br> <br> The optimizer is using merge join cartesian which decreases the performance in this case.. <h2 style="border-bottom-width:1px; border-bottom-style:solid; border-bottom-color:rgb(196,209,230); font-size:16px; background-color:rgb(119,119,115); color:rgb(255,255,255); text-transform:uppercase; padding-top:6px; padding-bottom:6px; padding-left:8px"> <a rel="nofollow"></a>SOLUTION</h2> <p>If statistics are inaccurate, then the choice of a cartesian product can severely affect performance.<br> To resolve the issue, accurate statistics should be gathered.<br> <br> See:<br> </p> 
    <div style="padding:10px; background-color:rgb(255,249,215); border:1px solid rgb(193,169,13)"> 
     <a href="https://support.oracle.com/epmos/faces/DocumentDisplay?id=1226841.1" rel="nofollow">Note:1226841.1</a>&nbsp;How To: Gather Statistics for the Cost Based Optimizer 
     <br> 
    </div> <br> <br> 
    <div style="padding:10px; background-color:rgb(255,249,215); border:1px solid rgb(193,169,13)">
      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. 
    </div> <br> <br> 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:-<br> <br> 
    <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241); width:738.1500244140625px"> 
     <code style="font-family:'Courier New',Courier,monospace; white-space:pre-wrap; word-wrap:break-word">_optimizer_cartesian_enabled=false;<br> </code> 
    </div> <br> For example:<br> <p>&nbsp;</p> 
    <div style="overflow:auto; border:1px solid rgb(196,209,230); padding:0.5em; margin:5px 0px 0px; font-family:'Courier New',Courier,monospace; font-size:12px; background-color:rgb(224,234,241); width:738.1500244140625px"> 
     <code style="font-family:'Courier New',Courier,monospace; white-space:pre-wrap; word-wrap:break-word"></code> 
     <p>SQL&gt; alter session set "_optimizer_cartesian_enabled"=false;&nbsp;<br> </p> 
     <p>SQL&gt; select * from v$access where sid=38;</p> 
     <p>no rows selected&nbsp;<br> <br> Elapsed: 00:00:00.02&nbsp;<br> <br> </p> 
     <pre>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 | 
------------------------------------------------------------------------------------------------

</pre> 
    </div> <br> <br> Now the query is running faster.</td> 
  </tr> 
 </tbody> 
</table>

[t.gif]: /images/20220810/a2a77a5a05be4adda7e6eb130de7011d.png