| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: More selective means slower?
Hhm:
being logged in as sysadm (in a Peoplesoft system), an id which does
have access to all v$ views and x$ views built on the x$ "tables" (but
does not have the DBA role):
13:15:32 sasbx.sysadm> explain plan set statement_id = 'o_l' for 13:23:27 2 select 'alter index diamond'||segment_name||' rebuild tablespace tmp_d_indx;'
13:23:29 3 from dba_segments 13:23:29 4 where owner='DIAMOND' 13:23:29 5 and 13:23:29 6 segment_type='INDEX' 13:23:29 7 and 13:23:29 8 tablespace_name='DM_DIAMOND_INDEXES';
select plan_table_output from
table(dbms_xplan.display('plan_table','o_l','typical'))
PLAN_TABLE_OUTPUT
| Id | Operation | Name | Rows |Bytes | Cost |
| 0 | SELECT STATEMENT | | |
| |
| 1 | VIEW | SYS_DBA_SEGS | |
| |
| 2 | UNION-ALL | | |
| |
| 3 | NESTED LOOPS | | |
| |
| 4 | NESTED LOOPS | | |
| |
| 5 | NESTED LOOPS | | |
| |
| 6 | NESTED LOOPS | | |
| |
| 7 | NESTED LOOPS | | |
| |
| 8 | TABLE ACCESS BY INDEX ROWID| TS$ | |
| |
|* 9 | INDEX UNIQUE SCAN | I_TS1 | |
| |
| 10 | TABLE ACCESS BY INDEX ROWID| USER$ | |
| |
|* 11 | INDEX UNIQUE SCAN | I_USER1 | |
| |
| 12 | VIEW | SYS_OBJECTS | |
| |
| 13 | UNION-ALL | | |
| |
|* 14 | TABLE ACCESS FULL | TAB$ | |
| |
|* 15 | FILTER | | |
| |
there is nothing special about the dictionary objects, you just need an account that has access to them, just like for any other object. Jacques Kilchoer wrote:
> Some dba_ views use x$ objects, and for those views the explain plan > will only show you the plan if you are signed on as SYS. > > -----Original Message----- > From: oracle-l-bounce_at_freelists.org > [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling > ... > > c) Why wouldn't explain plan not work on dictionary objects? > > >
-- Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 20 2005 - 15:39:36 CST
 |
 |