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