Re: More selective means slower?
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Jacques Kilchoer <Jacques.Kilchoer@xxxxxxxxx>
- Date: Thu, 20 Jan 2005 13:31:36 -0700
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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
