|
|
|
|
| Re: Response very slow [message #559112 is a reply to message #556076] |
Thu, 28 June 2012 15:01   |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
The number one thing that I would do would to be to watch the queries in action by watching the event waits in another session with the following query.
select sid,serial#,machine,
to_char(s.logon_time,'DD-MON-RR HH24:MI') login,i.instance_name db,
s.seconds_in_wait sec_wait,s.username,s.event,s.status,
s.program,s.machine,s.module,s.terminal
from gv$session s, gv$instance i where i.inst_id=s.inst_id
and s.status='ACTIVE' and s.username is not null
order by seconds_in_wait;
Sorts can be done more in memory by increasing the sort memory;
alter system set pga_aggregate_target=20g scope=memory sid='*';
Disk reads can be reduced by caching important tables and indexes.
alter TABLE CDA_SHARED.PLAYER_STATISTICS storage(buffer_pool keep);
alter INDEX CDA_APPS.UNIQUE_CLUB_CODE storage(buffer_pool recycle);
Many times I have had to rebuild all indexes and run stats to influence the optimizer to select a better plan.
alter index SCOTT.PK_EMP rebuild nologging online noparallel;
execute dbms_stats.gather_index_stats('SCOTT','PK_EMP',estimate_percent=>100)
alter index SCOTT.PK_EMP logging;
On several occasions, by analyzing the columns in the where clause, I have been able to create composite indexes on more than one column to force the join to only read the index for faster processing.
On a few occasions I have created a few temporary tables to reduce the rows and the complexity of the join, then joined the result tables for a much faster result.
|
|
|
|
|
|
| Re: Response very slow [message #560521 is a reply to message #559124] |
Fri, 13 July 2012 17:45 |
|
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
Michel,
I have found that using Oracle's recomendations as to which index to rebuild was not as effective as rebuilding all indexes as in the following:
ECSCDAP1P > @dba_hist_seg_stat_AVERAGE_LOGICAL_READS_PER_MINUTE.sql
DATE AVG_GIG_PER_MIN
-------------------- ---------------
2012-06-14 Thursday 821.3
2012-06-15 Friday 754.4
2012-06-16 Saturday 683.0
2012-06-17 Sunday 755.7
2012-06-18 Monday 818.2
2012-06-19 Tuesday 779.9
2012-06-20 Wednesday 873.4 Indexes rebuilt and then gigabytes
2012-06-21 Thursday 369.3 processed from memroy falls the next day
2012-06-22 Friday 343.8 and remains low in following days.
2012-06-23 Saturday 322.7
2012-06-24 Sunday 321.7
2012-06-25 Monday 367.4
2012-06-26 Tuesday 356.3
2012-06-27 Wednesday 358.5
2012-06-28 Thursday 334.8
ECSCDAP1P > list
1 select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DATE",
2 sum(b.LOGICAL_READS_DELTA)*8192/1024/1024/1024/60/24 Avg_GIG_PER_min
3 from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
4 where a.object_id=b.OBJ#
5 and c.snap_id=b.snap_id
6 and c.begin_interval_time > trunc(sysdate-29)
7 and c.begin_interval_time < trunc(sysdate)
8 and b.instance_number=c.instance_number
9 group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
10* order by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
On some occastions, rebuilding an index (or creating a new index) broke a query. In that case I found that the large table was missing a composite index. When I created the composite index it fixed the bad performing query.
In the case above, no rows were deleted, only indexes rebuilt and the gigabytes of bytes processed per minute dropped in half and remained low in the following days.
|
|
|
|
|
|
|
|
|
|
| Re: Response very slow [message #560527 is a reply to message #560526] |
Fri, 13 July 2012 19:44 |
|
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
The index is smaller so less memory blocks are read in an index range scan and less blocks are read in a full index scan. The smaller index influences the optimizer to use the index instead of a full table scan.
When I have had a query go south and the load go high on a RAC instance, I rebuild the indexes and run stats for all the tables referenced in the query and more often than not the query goes back to normal. I 1) Rebuilt the associated indexes 2) Ran stats on the associated indexes and 3) flushed the shared pool in the instance where I had the problem and the problem goes away.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
