VPD Cursor no shared

开发的同事发过来一个sql,说是在2个用户下执行同样的SQL语句的性能和执行计划不一样,要求给看一下。
自己运行了一下发现2个sql确实游标不能共享。
SQL> select * from table(dbms_xplan.display_cursor(null,null,’allstats last’));
游标0的执行计划

SQL_ID  8mm1juamb8cv4, child number 0
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                        |      1 |        |    900 |00:00:31.41 |      24M|       |       |          |
|   1 |  SORT AGGREGATE                  |                        |    900 |      1 |    900 |00:02:45.70 |      24M|       |       |          |
|*  2 |   TABLE ACCESS FULL              | T_BRM_PAYMENT_STATUS   |    900 |      3 |      0 |00:02:45.69 |      24M|       |       |          |
|   3 |  SORT AGGREGATE                  |                        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   4 |   TABLE ACCESS BY INDEX ROWID    | T_BRM_ORDER            |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  5 |    INDEX RANGE SCAN              | IDX_BRM_ORDER_VOE      |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   6 |  SORT ORDER BY                   |                        |      1 |   3668 |    900 |00:00:31.41 |      24M|   302K|   302K|  268K (0)|
|*  7 |   HASH JOIN RIGHT OUTER          |                        |      1 |   3668 |    900 |00:00:00.16 |    6105 |  1140K|  1140K| 1494K (0)|
|   8 |    TABLE ACCESS FULL             | T_PREPAID_CARD_DEALER  |      1 |   3969 |   3969 |00:00:00.01 |      37 |       |       |          |
|*  9 |    HASH JOIN                     |                        |      1 |   3668 |    900 |00:00:00.15 |    6068 |   810K|   810K|  918K (0)|
|* 10 |     HASH JOIN                    |                        |      1 |     11 |     11 |00:00:00.01 |      14 |   864K|   864K|  787K (0)|
|  11 |      NESTED LOOPS                |                        |      1 |     11 |     11 |00:00:00.01 |       8 |       |       |          |
|  12 |       TABLE ACCESS BY INDEX ROWID| T_PREPAID_CARD_STATUS  |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|* 13 |        INDEX UNIQUE SCAN         | PK_PREPAID_CARD_STATUS |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 14 |       TABLE ACCESS FULL          | T_PREPAID_CARD_GROUP   |      1 |     11 |     11 |00:00:00.01 |       6 |       |       |          |
|  15 |      TABLE ACCESS FULL           | T_PREPAID_CARD_TYPE    |      1 |     34 |     34 |00:00:00.01 |       6 |       |       |          |
|* 16 |     TABLE ACCESS FULL            | T_PREPAID_CARD_ITEM    |      1 |  41417 |  40000 |00:00:00.08 |    6054 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("PREPAID_CARD_NUMBER"=:B1)
   5 - access("VIN"=:B1 AND "ORDER_NUMBER"='ProcessEnrollVehicle' AND "EXECUTE_STATUS"='Completed')
   7 - access("DEALER_ID"="DEALER"."ID")
   9 - access("BATCH_ID"="T_PREPAID_CARD_GROUP"."ID")
  10 - access("T_PREPAID_CARD_GROUP"."TYPE_ID"="TYP"."ID")
  13 - access("STATUS"."ID"=1)
  14 - filter(("T_PREPAID_CARD_GROUP"."CREATE_AT">=TO_DATE(' 2016-03-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T_PREPAID_CARD_GROUP"."CREATE_AT"<=TO_DATE(' 2016-05-09 23:59:59', 'syyyy-mm-dd hh24:mi:ss')))
  16 - filter("STATUS_ID"=1)

游标1的执行计划

SQL_ID  8mm1juamb8cv4, child number 1
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                        |      1 |        |    900 |00:00:00.09 |    6106 |       |       |          |
|   1 |  SORT AGGREGATE                  |                        |    900 |      1 |    900 |00:00:00.01 |       0 |       |       |          |
|*  2 |   FILTER                         |                        |    900 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   3 |    TABLE ACCESS FULL             | T_BRM_PAYMENT_STATUS   |      0 |    625K|      0 |00:00:00.01 |       0 |       |       |          |
|   4 |  SORT AGGREGATE                  |                        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   5 |   TABLE ACCESS BY INDEX ROWID    | T_BRM_ORDER            |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  6 |    INDEX RANGE SCAN              | IDX_BRM_ORDER_VOE      |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|   7 |  SORT ORDER BY                   |                        |      1 |   3668 |    900 |00:00:00.09 |    6106 |   267K|   267K|  237K (0)|
|*  8 |   HASH JOIN RIGHT OUTER          |                        |      1 |   3668 |    900 |00:00:00.15 |    6106 |  1140K|  1140K| 1447K (0)|
|   9 |    TABLE ACCESS FULL             | T_PREPAID_CARD_DEALER  |      1 |   3969 |   3969 |00:00:00.01 |      37 |       |       |          |
|* 10 |    HASH JOIN                     |                        |      1 |   3668 |    900 |00:00:00.15 |    6069 |   810K|   810K|  918K (0)|
|* 11 |     HASH JOIN                    |                        |      1 |     11 |     11 |00:00:00.01 |      14 |   864K|   864K|  787K (0)|
|  12 |      NESTED LOOPS                |                        |      1 |     11 |     11 |00:00:00.01 |       8 |       |       |          |
|  13 |       TABLE ACCESS BY INDEX ROWID| T_PREPAID_CARD_STATUS  |      1 |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|* 14 |        INDEX UNIQUE SCAN         | PK_PREPAID_CARD_STATUS |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 15 |       TABLE ACCESS FULL          | T_PREPAID_CARD_GROUP   |      1 |     11 |     11 |00:00:00.01 |       6 |       |       |          |
|  16 |      TABLE ACCESS FULL           | T_PREPAID_CARD_TYPE    |      1 |     34 |     34 |00:00:00.01 |       6 |       |       |          |
|* 17 |     TABLE ACCESS FULL            | T_PREPAID_CARD_ITEM    |      1 |  41417 |  40000 |00:00:00.07 |    6055 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter(NULL IS NOT NULL)
   6 - access("VIN"=:B1 AND "ORDER_NUMBER"='ProcessEnrollVehicle' AND "EXECUTE_STATUS"='Completed')
   8 - access("DEALER_ID"="DEALER"."ID")
  10 - access("BATCH_ID"="T_PREPAID_CARD_GROUP"."ID")
  11 - access("T_PREPAID_CARD_GROUP"."TYPE_ID"="TYP"."ID")
  14 - access("STATUS"."ID"=1)
  15 - filter(("T_PREPAID_CARD_GROUP"."CREATE_AT">=TO_DATE(' 2016-03-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "T_PREPAID_CARD_GROUP"."CREATE_AT"<=TO_DATE(' 2016-05-09 23:59:59', 'syyyy-mm-dd hh24:mi:ss')))
  17 - filter("STATUS_ID"=1)

使用tanel poder的noshared.sql查看一下不能共享的原因

SQL_ID                        : 8mm1juamb8cv4
ADDRESS                       : 0000000343765F38
CHILD_ADDRESS                 : 00000003439F89F8
CHILD_NUMBER                  : 0
AUTH_CHECK_MISMATCH           : Y
REASON                        : 037Authorization Check failed(9)0x0 
                                Authorization Check failed(9) 
                                row_level_access_control_mismatch

 

 

SQL_ID                        : 8mm1juamb8cv4
ADDRESS                       : 0000000343765F38
CHILD_ADDRESS                 : 00000007D482DA78
CHILD_NUMBER                  : 1
REASON                        : 137Authorization Check failed(9)0x0
                                 Authorization Check failed(9)
                                 row_level_access_control_mismatch

 

 

不能共享的原因是vpd的原因。
查看一下数据库vpd的控制对象
select * from DBA_SEC_RELEVANT_COLS where object_owner = &ownname and object_name = &objname
确认问题的原因。

此条目发表在CBO分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s