OraFAQ Forum: Performance Tuning » how to check whether index is rebuilt or not?

Home » RDBMS Server » Performance Tuning » how to check whether index is rebuilt or not?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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..

Report message to a moderator

Re: how to check whether index is rebuilt or not? [message #115490 is a reply to message #115302]

Mon, 11 April 2005 20:54

andrew again
Messages: 2577
Registered: March 2000

Senior Member

1.) look at the "created" timestamp in user_objects of that index.
2.) do an explain plan of the query.

Report message to a moderator

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?

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: how to check whether index is rebuilt or not? [message #115545 is a reply to message #115527]

Tue, 12 April 2005 10:32

andrew again
Messages: 2577
Registered: March 2000

Senior Member

Hmmm... I should have checked that. Thanks.

Report message to a moderator

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.

Report message to a moderator

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

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: how to check whether index is rebuilt or not? [message #115619 is a reply to message #115302]

Wed, 13 April 2005 01:37

Frank
Messages: 7901
Registered: March 2000

Senior Member

Andrew is right, the execution plan in a tkprof-ed tracefile is only determined at the time you run tkprof. If you want the REAL used execution plan, use statspack.

hth

Report message to a moderator

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.
-----------------------------------------------------

Report message to a moderator

Previous Topic:	Check list to Tune an Application ( SQL & PL/SQL)
Next Topic:	URGENT:-Tuning a query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 01 14:49:13 CDT 2025