Home » RDBMS Server » Performance Tuning » gather table stats (11.2.0.3, sun sol 10)
gather table stats [message #620281] Wed, 30 July 2014 22:52 Go to next message
AviatoR
Messages: 43
Registered: February 2009
Member
I have found few tables in my database with statistics older than 9 months and the difference in the rows between num_rows and the actual row count is around 30000. As there is some indexes present in that table, still it uses full table scan to execute that query. Therefore, i have decided first to gather the statistics first for those tables to start with troubleshooting this performance issue.

I have tables and few indexes on the table.

Question:

1) does gather_table_stats is good enough or do i need to gather stats for indexes as well?
2)I have decided to use degree of parallelism, i don't how much I can use. Below is the snapshot of the CPU details from my server

$ echo "`psrinfo -p` socket(s)"
2 socket(s)

$ echo "`kstat -m cpu_info|grep -w core_id|uniq|wc -l` core(s) "
8 core(s)

$ echo "`psrinfo|wc -l` logical (virtual) processor(s)"
16 logical (virtual) processor(s)




If you have any suggestions on my approach to solve this issue please fire your views at me.

Thank you

Re: gather table stats [message #620282 is a reply to message #620281] Wed, 30 July 2014 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have found few tables in my database with statistics older than 9 months & the difference in the rows between num_rows and the actual row count is around 30000.

It matters if the 30000 difference is based upon total rows of 30000 or 300,000,000 rows.
News statistics are collected only when there is a "material" difference between old & new statistics.

>still it uses full table scan to execute that query.
In some cases FTS is the most efficient choice.

For Oracle close is good enough; otherwise Oracle would collect new statistics every day when any DML occurs against ANY table.

You have posted no SQL & results which would show that some performance problem actually exists.

It appears that you are bored, lack productive tasks & suffer from Compulsive Tuning Disorder.

[Updated on: Wed, 30 July 2014 23:21]

Report message to a moderator

Re: gather table stats [message #620286 is a reply to message #620282] Thu, 31 July 2014 00:01 Go to previous messageGo to next message
AviatoR
Messages: 43
Registered: February 2009
Member
Thanks for your reply blackswan, the total number of rows are 3,300,000 (approx) And I tried to force index usage on the query by using hint but still indexes were not used. Is it worrying factor? I totally understand FTS is not bad, as CBO chooses the best plan however it should use index when i force it using hint doesn't it?

compulsive tuning disorder lol.

SELECT
    SHAPE,
    ADD_HOUSE_NUMBER_1 || ADD_HOUSE_SUFFIX_1 AS HOUSE_NUMBER_1,
    ADD_HOUSE_SUFFIX_1 AS HOUSE_SUFFIX_1,
    ADD_HOUSE_NUMBER_2 AS HOUSE_NUMBER_2,
    ADD_HOUSE_SUFFIX_2 AS HOUSE_SUFFIX_2
  FROM VMPROP.V_PROPERTY_MP_ADDRESS
  WHERE (ADD_BLG_UNIT_ID_1 IS NULL
  OR ADD_BLG_UNIT_ID_1 = 1)
  AND PROP_STATUS = 'A';



it has got spatial indexes on shape column and normal indexes on other columns. When i forced to used indexes using hints it still goes to FTS
Re: gather table stats [message #620289 is a reply to message #620286] Thu, 31 July 2014 00:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>it has got spatial indexes on shape column and normal indexes on other columns. When i forced to used indexes using hints it still goes to FTS

any INDEX only useful on columns in WHERE clause
Re: gather table stats [message #620290 is a reply to message #620281] Thu, 31 July 2014 00:27 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
As BlackSwan wrote Quote:
any INDEX only useful on columns in WHERE clause

I would add: and when selecting small part of the table.

In you case
(ADD_BLG_UNIT_ID_1 IS NULL OR ADD_BLG_UNIT_ID_1 = 1)
is NOT indexable and
PROP_STATUS = 'A'
doesn't look selective either.

1. How many rows the query actually selects (out of 3.3M)?
2. How many distinct values do you have for PROP_STATUS and ADD_BLG_UNIT_ID_1 columns?
3. How many NULLS for ADD_BLG_UNIT_ID_1?
Re: gather table stats [message #620292 is a reply to message #620290] Thu, 31 July 2014 00:59 Go to previous messageGo to next message
AviatoR
Messages: 43
Registered: February 2009
Member
Thanks for your replies Micheal and Blackswan, it returns 2.7M rows. And ADD_BLG_UNIT_ID_1 is part of a column named address - object type.
Re: gather table stats [message #620313 is a reply to message #620292] Thu, 31 July 2014 02:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So the query returns 2.7M/3.3M = 82% of the records.
Oracle is never going to use an index for that, it would be hideously inefficient for it to do so.
The cut off percentage of rows above which oracle stops using an index depends on a lot of factors but it's generally down around 10% or lower.
Re: gather table stats [message #620394 is a reply to message #620313] Thu, 31 July 2014 19:26 Go to previous messageGo to next message
AviatoR
Messages: 43
Registered: February 2009
Member
I concede the point that oracle will never index in this case. but my question is even if we use hints won't it use index? because i forced it using index atleast for my consolation it should use it, but i found it is still goin for FTS. will there be any problem with statistics of the table?
Re: gather table stats [message #620397 is a reply to message #620394] Thu, 31 July 2014 19:31 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF005

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7038986332061

http://www.oracle.com/pls/db121/search?word=hint&partno=
Previous Topic: How to remove left join and ON JOIN from query and use better things in place of that
Next Topic: Queries "break" after automatic statistic gathering.
Goto Forum:
  


Current Time: Fri Mar 29 01:38:35 CDT 2024