Home » RDBMS Server » Performance Tuning » num_rows on user_indexes vs table_indexes (11.2 AIX 6.1)
num_rows on user_indexes vs table_indexes [message #574166] Mon, 07 January 2013 09:50 Go to next message
tom_watson
Messages: 8
Registered: January 2013
Junior Member
Hi,

Our stats are collected at the schema level as follows:

EXEC DBMS_STATS.gather_schema_stats('schema', estimate_percent => 15, degree => 8 )

However, when I compare the num_rows in user_indexes to the num_rows in user_tables, for some indexes I am getting zero rows but over 100k rows in the table.

I can see from the last_analyzed col on user_indexes that stats were gathered within seconds of the stats being collected on the tables.

Does anyone have any ideas as to this behaviour?

Thanks,
Tom.


Re: num_rows on user_indexes vs table_indexes [message #574167 is a reply to message #574166] Mon, 07 January 2013 09:53 Go to previous messageGo to next message
tom_watson
Messages: 8
Registered: January 2013
Junior Member
Sorry for the incorrect title - it should read:

num_rows on user_indexes vs user_tables


Re: num_rows on user_indexes vs table_indexes [message #574168 is a reply to message #574167] Mon, 07 January 2013 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 21945
Registered: January 2009
Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


do not tell us.
SHOW US!
Re: num_rows on user_indexes vs table_indexes [message #574172 is a reply to message #574168] Mon, 07 January 2013 10:28 Go to previous messageGo to next message
tom_watson
Messages: 8
Registered: January 2013
Junior Member
Hi,

Please find below the statement I've written along with my results. I have deliberately kept my table and index names hidden.

select
--a.table_name,
a.num_rows "Tab count",
a.last_analyzed "Tab Stats Gathered",
--b.index_name,
b.num_rows "Index count",
b.last_analyzed "Index Stats Gathered",
round(b.num_rows * 100 / a.num_rows) "Index pct of tab count"
from user_tables a, user_indexes b
where a.table_name = b.table_name
order by b.num_rows * 100 / a.num_rows
;


Tab count Tab Stats Gathered Index count Index Stats Gathered Index pct of tab count
---------------------- ------------------------- ---------------------- ------------------------- ----------------------
9 07-JAN-13 05:10:30 0 07-JAN-13 05:10:30 0
967 07-JAN-13 04:56:25 0 07-JAN-13 04:56:25 0
122573 07-JAN-13 04:45:17 0 07-JAN-13 04:45:21 0
122573 07-JAN-13 04:45:17 0 07-JAN-13 04:45:21 0
122573 07-JAN-13 04:45:17 0 07-JAN-13 04:45:21 0
7039 07-JAN-13 04:33:28 0 07-JAN-13 04:33:28 0
616447 07-JAN-13 04:34:46 60055 07-JAN-13 04:34:47 10


The last col displays the num_rows in user_indexes as a % of num_rows in user_tables. All tables = 0 except for the last where the num_rows in user_indexes is 10% of that in user_tables.
Thanks,
Tom.

[Updated on: Mon, 07 January 2013 10:31]

Report message to a moderator

Re: num_rows on user_indexes vs table_indexes [message #574176 is a reply to message #574172] Mon, 07 January 2013 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Check the default options on dbms_stats.
Gather for ALL rows.
Compare.

Regards
Michel
Re: num_rows on user_indexes vs table_indexes [message #574195 is a reply to message #574166] Mon, 07 January 2013 16:12 Go to previous message
LNossov
Messages: 283
Registered: July 2011
Location: Germany
Senior Member
NULLs?
Previous Topic: Need the Tuinng Tips for Tuning The package
Next Topic: cpu utilizaton
Goto Forum:
  


Current Time: Sat Apr 19 05:18:19 CDT 2014

Total time taken to generate the page: 0.10906 seconds