| performance issue, pls help [message #52590] |
Tue, 30 July 2002 21:41  |
Julia
Messages: 30
Registered: December 1999
|
Member |
|
|
Hi,
We have Oracle 8.1.6 for both dev and prod on Solaris 8. The prod server is much more powerful than the dev server. DB parameters are identical on both servers. both are cost-based. Tables and indexes are analyzed (compute statistics) daily by the application team on prod. The problem we are having is for a certain job, it takes 3 hrs to finish on dev but 12 hrs on prod, also most other jobs slowed down gradually during the last few month on prod as well. We checked the indexes on both servers, they are identical as well. On prod
the iowait is always around 30% - 60%, and the event in v$session_wait is always 'db file scattered read'.
Looks like the queries (query only for this job) are not using indexes at all. But why? why the queries are not using index? The only suspicious part for me at the momoment is user_tab_columns.NUM_DISTINCT <> user_tables.NUM_ROW for a major table, while the two values are identical on dev server on which table was last analyzed 1 month ago. Is this the cause of this performance degrade? What shall we do to tune the performance in this case. Any comments/advice will be much appreciated.
Julia
|
|
|
|
| Re: performance issue, pls help [message #52605 is a reply to message #52590] |
Wed, 31 July 2002 09:31  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
You could try cloning the production db to the dev one. By copying the datafiles and control files you ensure that the databases are identical including how data is scattered in blocks etc. If the problem still persists, you have something to go on. Export and import won't give the same effect. e.g. if you have a table which was at one stage held 100,000,000 rows and now only hold 1 row, the optimizer may chose not to use the index and rather do a full table scan. Now it has to scan through all the DB blocks originally held by the 100,000,000 rows to find just that one. This could explain a growing problem. If it was something like this, then rebuilding (import/export) or recreate the table could help. Sometimes indexes benefit rebuilding too. For those you should be able to use 'alter index abc rebuild tablespace same_ts;'.
Ensure that your indexes are valid (status col in user_indexes). NUM_DISTINCT should only be that same as NUM_ROWS for a primary key. Any difference you see is likely to due to the data in the tables alone (or the freshness of the stats).
select index_name, num_distinct_keys, num_rows from user_indexes;
Having ptod in archivelog mode and dev not could make a big difference. This should be consistent effect over time though.
|
|
|
|
| Re: performance issue, pls help [message #52626 is a reply to message #52590] |
Thu, 01 August 2002 10:58 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
You might try deleting the statistics then run the query. Another thing to try is delete the stats then rerun the compile. Use compute and make sure to do both indexes and tables. Don't use dbms_utility. Use something like this script:
set pagesize 0
set heading off
set feedback off
set echo off
set verify off
spool anal_tables.tmp
select
'analyze table ' || owner || '."' || table_name || '" compute statistics;'
from
dba_tables
where
owner not in ('SYS','SYSTEM','DBSNMP','OUTLN')
order by
owner,table_name
/
spool off
spool anal_indexes.tmp
select
'analyze index ' || owner || '."' || index_name || '" compute statistics;'
from
dba_indexes
where
owner not in ('SYS','SYSTEM','DBSNMP','OUTLN')
order by
owner,index_name
/
spool off
set echo on
spool analyze_all.log
@anal_tables.tmp
@anal_indexes.tmp
spool off
!rm anal_tables.tmp
!rm anal_indexes.tmp
If if still doesn't use the indexes use optimizer hints and force it.
|
|
|
|
| Re: performance issue, pls help [message #52653 is a reply to message #52590] |
Mon, 05 August 2002 03:00 |
Andrew Ballance
Messages: 3
Registered: August 2002
|
Junior Member |
|
|
Did you create histograms for the indexed columns? Even if you did, did you explicitly specify the number of buckets to create? The default number of buckets is 2, which is of no use whatsoever. So your analyze should be
analyze table any_Table compute statistics for all indexed columns size 75
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
