Re: wierd performance problem
Date: Thu, 10 Jul 2008 16:11:05 -0700 (PDT)
Message-ID: <817915.47002.qm@web56603.mail.re3.yahoo.com>
Hi,
Well, I see couple of mails lying in my inbox on on this subject (I did not read them yet), so I guess by now you might have arrived at the solution. Anyways, here are my 2 cents.
Probably, blocks lying at the left-hand side (low water mark) are empty. So, to hit the first record, Oracle has to traverse all the empty blocks until it reaches to a block containing record. This I believe is consuming time in your case.
I performed a small test with 10,000 records:
SQL> create table t as select level sno from dual connect by level <= 10000;
Table created.
SQL> exec dbms_stats.gather_table_stats(user, 't');
PL/SQL procedure successfully completed.
SQL> set autotrace on
SQL> select * from t where rownum < 2;
������ SNO
�������� 1
Execution Plan
Plan hash value: 508354683
| Id� | Operation��������� | Name | Rows� | Bytes | Cost (%CPU)| Time���� |
|�� 0 | SELECT STATEMENT�� |����� |���� 1 |���� 3 |���� 2�� (0)| 00:00:01 | |*� 1 |� COUNT STOPKEY���� |����� |������ |������ |����������� |��������� | |�� 2 |�� TABLE ACCESS FULL| T��� |���� 1 |���� 3 |���� 2�� (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
�� 1 - filter(ROWNUM<2)
Statistics
��������� 1� recursive calls ��������� 0� db block gets ��������� 4� consistent gets ��������� 0� physical reads ��������� 0� redo size ������� 406� bytes sent via SQL*Net to client ������� 399� bytes received via SQL*Net from client ��������� 2� SQL*Net roundtrips to/from client ��������� 0� sorts (memory) ��������� 0� sorts (disk) ��������� 1� rows processed
SQL> set autotrace off
Notice, it required only "4" consistent gets, now let us delete almost all records from this table and repeat the test:
SQL> delete from t where sno <=9990;
9990 rows deleted.
SQL> commit;
Commit complete.
SQL> set autotrace on
SQL> select * from t where rownum < 2;
������ SNO
����� 9991
Execution Plan
Plan hash value: 508354683
| Id� | Operation��������� | Name | Rows� | Bytes | Cost (%CPU)| Time���� |
|�� 0 | SELECT STATEMENT�� |����� |���� 1 |���� 3 |���� 2�� (0)| 00:00:01 | |*� 1 |� COUNT STOPKEY���� |����� |������ |������ |����������� |��������� | |�� 2 |�� TABLE ACCESS FULL| T��� |���� 1 |���� 3 |���� 2�� (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
�� 1 - filter(ROWNUM<2)
Statistics
��������� 0� recursive calls ��������� 0� db block gets �������� 19� consistent gets ��������� 0� physical reads ��������� 0� redo size ������� 407� bytes sent via SQL*Net to client ������� 399� bytes received via SQL*Net from client ��������� 2� SQL*Net roundtrips to/from client ��������� 0� sorts (memory) ��������� 0� sorts (disk) ��������� 1� rows processed
SQL> set autotrace off
Well, consistent gets have climbed up to 19.
This could be the reason in your case.
Regards
Asif Momen
http://momendba.blogspot.com
- On Thu, 7/10/08, Dba DBA <oracledbaquestions_at_gmail.com> wrote: From: Dba DBA <oracledbaquestions_at_gmail.com> Subject: wierd performance problem To: oracle-l_at_freelists.org Date: Thursday, July 10, 2008, 9:10 AM
This only occurs with one table. The table is partitioned by day. It has about 160 gb.
I do the following:
select count(*)
from mytable
where rownum < 2;
No indexes. Takes 30 seconds. Same thing on other tables the same size take 2 seconds.
I ran a 10046 trace and found that all my wait time is db file scattered read. dug deeper. Looked at the raw trace file. I noticed I am doing ALOT of db file scattered read calls. My individual wait time on each call does not appear to be considerable.
why would I do so many scattered reads to just get the first record? I am having alot of performance problems with scanning this table and adding indexes.
Nothing else is going on. I am on a SAN. Oracle 10.2.0.3
Redhat 4.5
As I said there is not any other activity.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 10 2008 - 18:11:05 CDT