Home » RDBMS Server » Performance Tuning » Response very slow (oracle database 10g)
icon5.gif  Response very slow [message #556076] Thu, 31 May 2012 03:11 Go to next message
hielh
Messages: 1
Registered: May 2012
Location: morroco
Junior Member

Hi everyone,
I'm using table "order" to extract reporting information. this table in request join 21 other table. so to extract file with 20000 the request take 12-15 min. i wont to minimaze the time.

envirennement:
PHP 5.1.6
oracle 10g
have you any idea?


Best regard
Re: Response very slow [message #556077 is a reply to message #556076] Thu, 31 May 2012 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 58948
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Response very slow [message #559112 is a reply to message #556076] Thu, 28 June 2012 15:01 Go to previous messageGo to next message
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 #559124 is a reply to message #559112] Thu, 28 June 2012 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58948
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And does it globally improve performances? Not just for these queries just after you rebulld the indexes.

Regards
Michel
Re: Response very slow [message #560521 is a reply to message #559124] Fri, 13 July 2012 17:45 Go to previous messageGo to next message
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 #560522 is a reply to message #560521] Fri, 13 July 2012 18:06 Go to previous messageGo to next message
BlackSwan
Messages: 22722
Registered: January 2009
Senior Member
>I have found that using Oracle's recomendations as to which index to rebuild
please cite URL where Oracle's recommendations reside.
Re: Response very slow [message #560524 is a reply to message #560522] Fri, 13 July 2012 19:16 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
In the following manual it states that the blevel is used by the optimizer in its cost decision. When I took the performance tuning class I was told that indexes with blevel's 4 and greater should be rebuilt. I found few of my indexes with blevel's of 4 but I did find that if I rebuilt all indexes, that my gigabytes of memory processed per minute would fall (A stat that we use at the NFL to determine if developer application tuning helped or not).

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-stats-concepts-110711-1354477.pdf

Index Statistics
Index statistics provide information on the number of distinct values in the index (distinct keys), the depth of the index (blevel), the number of leaf blocks in the index (leaf_blocks), and the clustering factor1. The Optimizer uses this information in conjunction with other statistics to determine the cost of an index access. For example the Optimizer will use b-level, leaf_blocks and the table statistics num_rows to determine the cost of an index range scan (when all predicates are on the leading edge of the index).
Re: Response very slow [message #560526 is a reply to message #560524] Fri, 13 July 2012 19:35 Go to previous messageGo to next message
BlackSwan
Messages: 22722
Registered: January 2009
Senior Member
The alternative to a Logical Read is a Physical Read.
When a session needs a block of data, it will be satisfied either by a Logical
Read or a Physical Read. I can see where you can reduce the Logical Reads total,
but end up increasing the Physical Reads which are more expensive & slower.

In plain English, please explain how or why rebuilding INDEX directly results in
fewer Logical Read to satisfy the same collection of SQL within application.
Re: Response very slow [message #560527 is a reply to message #560526] Fri, 13 July 2012 19:44 Go to previous message
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.
Previous Topic: Big database migration
Next Topic: How to Tune my SQL (5 merged)
Goto Forum:
  


Current Time: Mon Sep 01 07:05:46 CDT 2014

Total time taken to generate the page: 0.15282 seconds