This is a quick note-to-self to make sure a database account has all the necessary privileges to execute dbms_xplan.display_cursor. These are documented in the PL/SQL Packages and Types reference, summarised here for your convenience. Well, mine, actually 😀
WARNING: before you start granting the following privileges, make sure you have a change request recorded, and your approval board (the security team in particular!) has signed them off. Selecting from these V-Dollar views offers great power, and as you know, with great power comes great responsibility. Also make sure you are auditing these grants!
grant select on SYS.V_$SQL_PLAN to soe;
grant select on SYS.V_$SQL_PLAN_STATISTICS_ALL to soe;
grant select on SYS.V_$SESSION to soe;
grant select on SYS.V_$SQL to soe;That’s it! Different grants are needed for display_awr() and related functions, you find them in the aforementioned link to the documentation. With the grants provided, the user (MARTIN) can generate execution plans from the cursor cache. Getting plans from the cursor cache is preferable, since explain plan for can lie to you (the Internet is full of examples, I’ll leave it as an exercise to the reader to find out).
SQL> select * from table(dbms_xplan.display_cursor('6u9u3qjadnry4','0', 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
________________________________________________________________________________________________________________________________
SQL_ID 6u9u3qjadnry4, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ id, data_skew,
random1 from t1 where data_skew = 'A' and
random1 = 671
Plan hash value: 3617692013
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3480 (100)| | 48 |00:00:00.06 | 12666 | 12663 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 50 | 550 | 3480 (1)| 00:00:01 | 48 |00:00:00.06 | 12666 | 12663 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "T1"@"SEL$1"
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('23.1.0')
DB_VERSION('23.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("RANDOM1"=671 AND "DATA_SKEW"='A'))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID"[NUMBER,22], "DATA_SKEW"[CHARACTER,1], "RANDOM1"[NUMBER,22]
Query Block Registry:
---------------------
SEL$1 (PARSER) [FINAL] If you don’t want to grant individual roles, you might find SELECT_CATALOG_ROLE appealing, but that’s even broader, opening a wholly different can of worms.
Happy troubleshooting!
Martins Blog 