Hi Gurus ,
I am using 10.2.0.4 .
Below is the query that is taking 9264.74 sec per execution.
SELECT MAX(started) FROM qosdev.rate_toprocess WHERE stattype = :b1
Expl plan :-
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1826 (100)| |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| RATE_TOPR | 2301 | 27612 | 1826 (1)| 00:00:21 |
| 3 | INDEX RANGE SCAN | UI_RATE_TOPR | 2301 | | 35 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / RATE_TOPR@SEL$1
3 - SEL$1 / RATE_TOPR@SEL$1
Size of table :- 2.51131821 GB
Fragmentation in this table:- 1.017 GB
Last_analyzed :- 23-AUG-14
Index :- i had this index rebuilt as the size of index was more then 3GB .
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
UI_RATE_TOPR PERIOD 4
UI_RATE_TOPR PROCESSED 3
UI_RATE_TOPR SOURCE_SYSTEM 2
UI_RATE_TOPR STATTYPE 1
Can you please help me identfy where the issue is . Should i rebuild the table as i can see 1GB of fragmentation .
Thanks for looking
Regards,
DBApps