-- Thanks for the question regarding "Instances in xxx_INDEXES", version 10.2.0.4
Submitted on 20-Oct-2009 8:31 Central time zone
Last updated 30-Oct-2009 9:43
You Asked
Hello Tom ,
What is the significance of "INSTANCES" in user_indexes / all_indexes view ?
How does affect the performance ?
Thanks for your time.
and we said...
<quote src=documentation>
INSTANCES VARCHAR2(40) Number of instances across which the indexes to be scanned
</quote>
It has to do with parallel query and the method in which parallel query against the index would work.
If you use RAC (real application clusters), you can have queries not only run in parallel on a single machine (a single instance) but across instances as well (across machines in a cluster - a single database, many computers)
So, it would influence a parallel query plan in a RAC environment.
October 29, 2009 - 3pm Central time zone
Reviewer: A reader
thanks tom .
1) Please advise , how the values in the "instances" is populated .
The following was executed in a RAC database.
SQL> create table t as select * from ALL_OBJECTS;
Table created
SQL> create index t_idx on t(object_id , owner) parallel 8;
Index created
SQL> select degree , instances from user_indexes where index_name ='T_IDX';
DEGREE INSTANCES
---------------------------------------- ----------------------------------------
8 1
2) I attened your webinar on 27th . You were descrbing about automatic parallelism .
Does it mean that , the values in "degree" and "instances" / PARALLEL hint will be ignored.
Followup October 30, 2009 - 9am Central time zone:
1) you set the instances if you want - just like you set parallel degree *if you want*
2) you would optimally just say "parallel", no degree. They would not be ignored, they would be used as minimums.
|