| gather table stats [message #620281] |
Wed, 30 July 2014 22:52  |
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  |
 |
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 |
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 #620290 is a reply to message #620281] |
Thu, 31 July 2014 00:27 |
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)
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 #620313 is a reply to message #620292] |
Thu, 31 July 2014 02:46 |
cookiemonster
Messages: 13962
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 |
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?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
