2

I have read this post and this excellent article

My question is, how to get the acutual execution plan in Oracle 11g

without sys privilege and DBA privilege. (You can treat my case as a READ-ONLY user)

I'm able to get the explain plan using SYS.PLAN_TABLE$ or CTRL+E in TOAD

But my understanding is that explain plan = estimated execution plan only?

Found on Oracle's Document

Execution plans can differ due to the following:

  • Different Schemas

  • Different Costs

Please give me any help you can offer to clear my concepts.

Community
  • 1
  • 1
Larry
  • 2,764
  • 2
  • 25
  • 36

2 Answers2

4

If you want to use dbms_xplan.display_cursor, you'll need at least these grants:

grant select on sys.v_$sql_plan to larry;
grant select on sys.v_$session to larry;
grant select on sys.v_$sql_plan_statistics_all to larry;

And you'll probably also need V$SQL to find the SQL_ID:

grant select on sys.v_$sql to larry;
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Thanks for the tips, so I must get these permission on SYS table from DBA? – Larry Jan 30 '13 at 05:28
  • one more question. `dbms_xplan.display_cursor` 's result is the actual execution plan used in query? – Larry Jan 30 '13 at 05:42
  • Yes. It can display several "actual" columns, allowing you to compare the estimates with the actual values. – Jon Heller Jan 30 '13 at 05:44
  • I'll accept this answer if there's no other workaround in 2 days. – Larry Jan 30 '13 at 05:58
  • I'll go with this answer as seemingly there's no other workaround, and I'll like to be granted the minimum set of privilege. – Larry Jan 31 '13 at 10:18
2

If you're developer, maybe I can request SELECT_CATALOG_ROLE to be granted you. In 11g it is harmless to grant it. This will allow read-only access to most of the information DBAs have.

This will give you an access to v$sql_plan*, v$session_longops, V$lock, V$SQL_BIND_CAPTURE and other performance related data.

ibre5041
  • 4,903
  • 1
  • 20
  • 35
  • 2
    That would be more convenient than granting individual tables. But I don't think you can always call it harmless; it depends on the organization. For example, a lot of organizations would not want developers running `select password from dba_users;`, even if the password is encrypted. And even `V$SQL` may be problematic, if users are including sensitive information in their queries. – Jon Heller Jan 30 '13 at 19:09
  • Yep, you're right. By the "harmless" I meant that on 9i users who had this role could see plaintext password for DBLINKs. This is no more true. – ibre5041 Jan 30 '13 at 20:47