| how to check whether index is rebuilt or not? [message #115302] |
Fri, 08 April 2005 20:51  |
confident
Messages: 28
Registered: March 2005
|
Junior Member |
|
|
i faced the interview for junior dba position, the interview ask me this quesiton
1.how to know the whether particular index is rebuilt or not and size of index. and how to know to what type of index it was..
2. user running a sql queries, by his queries, how to know, whether his queries using index or not...and what type index is using.... i replied to this answer, through explain plan we can know... is it right? if it right, please tell me, how to know from explain plain..
if u have any performance tunning interview question please forward to this mail id confident119@yahoo.com.sg...i will be very grate ful to him..
|
|
|
|
|
|
| Re: how to check whether index is rebuilt or not? [message #115527 is a reply to message #115302] |
Tue, 12 April 2005 08:48  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
created shows when the object was created, not rebuilt?
MYDBA@ORCL > create table t (a number);
Table created.
MYDBA@ORCL > create index tidx on t(a);
Index created.
MYDBA@ORCL > select created from user_objects where object_name = 'TIDX';
CREATED
--------------------
12-APR-2005 08:44:56
1 row selected.
MYDBA@ORCL > alter index tidx rebuild;
Index altered.
MYDBA@ORCL > select created from user_objects where object_name = 'TIDX';
CREATED
--------------------
12-APR-2005 08:44:56
1 row selected.
|
|
|
|
|
|
| Re: how to check whether index is rebuilt or not? [message #115558 is a reply to message #115545] |
Tue, 12 April 2005 11:45 |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
dba_objects.last_ddl_time tell you when your last ddl is run against your index. It is typically the rebuild.
dba_segments.blocks gives the size of index. if you do analyze compute statistics, you will see other statistics info in dba_indexes, which also gives you type of index.
Explain plan will tell you what index POSSIBLY the query will use before execution. The right answer to see if the indexe is used should be do sql trace dump and use tkprof to look at execution plan, which is the plan that the query REALLY used for execution.
|
|
|
|
| Re: how to check whether index is rebuilt or not? [message #115597 is a reply to message #115558] |
Tue, 12 April 2005 17:23 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
create table x1 (col1 number);
create index x1_idx on x1(col1);
select object_name, created, last_ddl_time from user_objects where object_name = 'X1_IDX';
OBJECT_NAME CREATED LAST_DDL_TIME
X1_IDX 4/12/2005 9:19:47 AM 4/12/2005 9:19:47 AM
-- tuncate changes last DDL time
truncate table x1;
select object_name, created, last_ddl_time from user_objects where object_name = 'X1_IDX';
OBJECT_NAME CREATED LAST_DDL_TIME
X1_IDX 4/12/2005 9:19:47 AM 4/12/2005 9:20:56 AM
I stand to be corrected, but I'm pretty sure the explain plan only gets determined when you run tkprof with explain=user/pass@db. It doesn't get stored in the trace file. Important to remember when running tkprof to use the correct connect string and not to change stats, indexes etc. It's easy to see if you drop an index and re-run tkprof. Some recursive SQL does seem to have execution plan info in the raw .trc trace file
|
|
|
|
|
|
| Re: how to check whether index is rebuilt or not? [message #115661 is a reply to message #115619] |
Wed, 13 April 2005 09:06 |
pscjhe
Messages: 38
Registered: April 2005
|
Member |
|
|
Doesn't "truncate table" truncate indexes as well ? Look at this.
tch@TCHTST2> create index t_ndx on t(object_id);
Index created.
tch@TCHTST2> select blocks from dba_segments where segment_name='T_NDX';
BLOCKS
----------
65
tch@TCHTST2> truncate table t;
Table truncated.
tch@TCHTST2> select blocks from dba_segments where segment_name='T_NDX';
BLOCKS
----------
13
dba_objects is only a synonym to all_objects. If you look at the source code of all_objects ( too long to include here), you will see last_ddl_time is from sys.obj$.mtime. So it seems to mean "modification" time. So it is not exactly when "alter index rebuild" is run. it is close enough. If you really have to trace when this DDL is run, you have to audit or use event trigger.
As to tkprof, I am sorry that I didn't make it clear enough to mean "Row Source Operation", which is the history of what was used to execute the sql and INCLUDED in the trace file. However, explain plan, autotrace, tkprof explain=u/p, no matter when they are executed, they are subject to any change in between execution and do this explain plan. Hence not 100% accurate. But in reality they are almost always same as tkprof's Row source.
In 9i, v$sql_plan also includes the plan that is the history that comes with sql execution. There is detailed method using this in Tom Kyte's "Effective Oracle By Design" Book p91.
Look at this tkprof
------------------------------------------------------------
select *
from
t where value='aa'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 6447 (TCH)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=3 r=0 w=0 time=40 us)
1 INDEX RANGE SCAN T_NDX (cr=2 r=0 w=0 time=27 us)(object id 43075)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 TABLE ACCESS (BY INDEX ROWID) OF 'T'
1 INDEX (RANGE SCAN) OF 'T_NDX' (NON-UNIQUE)
....
-----------------------------------------------------
The first one is Row source Operation and second one is explain plan. Normally you NEVER use explain plan in tkprof at all.
-----------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
