OraFAQ Forum: Performance Tuning » Slow query with customer data

Home » RDBMS Server » Performance Tuning » Slow query with customer data (Oracle 10.2.0.3.0 on Solaris 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Slow query with customer data [message #334641]

Thu, 17 July 2008 09:03

elmu
Messages: 3
Registered: July 2008

Junior Member

Dear all,

we have an Oracle 10.2.0.3.0 Database installed on a Solaris 10 operating system. The hardware is a Sun Fire 480R 2x1050MHz US-III+ CPU and 4GB RAM 3x147GB SCSI disks.

The problem is the following:
We import customer data via impdp. The table contains ~1.5 million records and the size is ~ 1.2GB. The table is partitioned and after the import only the first partition is used. After the import we have gathered all statistics and rebuild all indexes.

After that one of our query needs ~40min to execute. However the same query execute ~20sec if we use own data (of course similar amount and characteristic). Besides this it was also fast on the customer machine before the export and import.

The query is generated by a ProC code and makes a join between 3 tables, but only the main table contains data, the other 2 are empty.

From the tkprof results (see below) I can see that in the slow case we have a very high disk IO. However I don't know why the big difference between the 2 cases.

Any ideas, tips how to proceed?

Thanks in advance!

The slow query:

Quote:

SELECT count(*) /*+ ordered use_nl(TAB1 TAB2 TAB3) index(TAB1 ALARMLOG_IDX_1) index(TAB2 LOG_STATECHANGEDEF_IDX) index(TAB3 LOG_MONITOREDALARM_IDX) */
FROM alarmLog TAB1, OMCDBSYS.LOG_stateChangeDef TAB2, OMCDBSYS.LOG_monitoredAlarm TAB3
WHERE tab1.logRecordId = tab2.stateChangeDefLogRecordId(+)
AND tab1.logRecordId = tab3.monitoredAlarmLogRecordId(+)
AND (((NOT (perceivedSeverity=:"SYS_B_0") AND managedObjectClass LIKE :"SYS_B_1" ESCAPE(CHR(:"SYS_B_2")) )
AND (eventTime>=TO_DATE(:"SYS_B_3", :"SYS_B_4")
AND eventTime<=TO_DATE(:"SYS_B_5", :"SYS_B_6"))))
ORDER BY eventTime DESC , eventTime DESC

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 94.27 2807.14 385336 1387196 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 94.27 2807.15 385336 1387196 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1387196 pr=385336 pw=0 time=2807150241 us)
374 FILTER (cr=1387196 pr=385336 pw=0 time=8272413 us)
374 NESTED LOOPS OUTER (cr=1387196 pr=385336 pw=0 time=8271970 us)
374 NESTED LOOPS OUTER (cr=1386448 pr=385324 pw=0 time=8262217 us)
374 NESTED LOOPS (cr=1385700 pr=385312 pw=0 time=8249795 us)
2 SORT UNIQUE (cr=3 pr=0 pw=0 time=570 us)
2 TABLE ACCESS FULL PARTITION_TABLE (cr=3 pr=0 pw=0 time=252 us)
374 PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=1385697 pr=385312 pw=0 time=8251938 us)
374 TABLE ACCESS BY LOCAL INDEX ROWID ALARMLOG PARTITION: KEY KEY (cr=1385697 pr=385312 pw=0 time=8251501 us)
1383458 INDEX RANGE SCAN PK_ALARMLOG PARTITION: KEY KEY (cr=3670 pr=3330 pw=0 time=5537332 us)(object id 13437)
0 PARTITION RANGE ALL PARTITION: 1 2 (cr=748 pr=12 pw=0 time=81208 us)
0 INDEX RANGE SCAN LOG_MONITOREDALARM_IDX PARTITION: 1 2 (cr=748 pr=12 pw=0 time=77616 us)(object id 13445)
0 PARTITION RANGE ALL PARTITION: 1 2 (cr=748 pr=12 pw=0 time=112506 us)
0 INDEX RANGE SCAN LOG_STATECHANGEDEF_IDX PARTITION: 1 2 (cr=748 pr=12 pw=0 time=109757 us)(object id 13449)

And the fast one:

Quote:

SELECT count(*) /*+ ordered use_nl(TAB1 TAB2 TAB3) index(TAB1 ALARMLOG_IDX_1) index(TAB2 LOG_STATECHANGEDEF_IDX) index(TAB3 LOG_MONITOREDALARM_IDX) */
FROM alarmLog TAB1, OMCDBSYS.LOG_stateChangeDef TAB2, OMCDBSYS.LOG_monitoredAlarm TAB3
WHERE tab1.logRecordId = tab2.stateChangeDefLogRecordId(+)
AND tab1.logRecordId = tab3.monitoredAlarmLogRecordId(+)
AND (((NOT (perceivedSeverity=:"SYS_B_0") AND managedObjectClass LIKE :"SYS_B_1" ESCAPE(CHR(:"SYS_B_2")) )
AND (eventTime>=TO_DATE(:"SYS_B_3", :"SYS_B_4")
AND eventTime<=TO_DATE(:"SYS_B_5", :"SYS_B_6"))))
ORDER BY eventTime DESC , eventTime DESC

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.23 5.14 0 35973 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.23 5.14 0 35973 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=35973 pr=0 pw=0 time=5140817 us)
0 FILTER (cr=35973 pr=0 pw=0 time=5140722 us)
0 HASH JOIN RIGHT SEMI (cr=35973 pr=0 pw=0 time=5140653 us)
1 TABLE ACCESS FULL PARTITION_TABLE (cr=3 pr=0 pw=0 time=385 us)
0 NESTED LOOPS OUTER (cr=35970 pr=0 pw=0 time=5138069 us)
0 NESTED LOOPS OUTER (cr=35970 pr=0 pw=0 time=5138060 us)
0 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=35970 pr=0 pw=0 time=5138054 us)
0 TABLE ACCESS BY LOCAL INDEX ROWID ALARMLOG PARTITION: 1 1 (cr=35970 pr=0 pw=0 time=5138028 us)
1500001 INDEX RANGE SCAN ALARMLOG_IDX_1 PARTITION: 1 1 (cr=7699 pr=0 pw=0 time=1500094 us)(object id 12001)
0 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN LOG_STATECHANGEDEF_IDX PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us)(object id 12009)
0 PARTITION RANGE SINGLE PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN LOG_MONITOREDALARM_IDX PARTITION: 1 1 (cr=0 pr=0 pw=0 time=0 us)(object id 12005)

Report message to a moderator

Re: Slow query with customer data [message #334651 is a reply to message #334641]

Thu, 17 July 2008 10:15

alanm
Messages: 284
Registered: March 2005

Senior Member

hi,
if you run the query with your own data a few times, do you seen any slow down in performance?

regards

Alan

[Updated on: Thu, 17 July 2008 11:14] by Moderator

Report message to a moderator

Re: Slow query with customer data [message #335082 is a reply to message #334651]

Sun, 20 July 2008 21:57

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Show us a description of the indexes ALARMLOG_IDX_1 and PK_ALARMLOG. It looks like the fast query is able to use the indexed columns in ALARMLOG_IDX_1 to filter non-matching rows (fast!), but the slow one has to lookup the table row 1.5M times to get the columns required to filter out rows (sloooooowwwwwww!!!!!!!)

Also, it seems from your plan that one of the objects - probably ALARMLOG - is a VIEW. Show the text of the View as well.

Ross Leishman

Report message to a moderator

Re: Slow query with customer data [message #335203 is a reply to message #334641]

Mon, 21 July 2008 07:30

elmu
Messages: 3
Registered: July 2008

Junior Member

Hello,

- The query will be not slower it is always ~40min.
- ALARMLOG is a table. Here are the index and table script:

Quote:

CREATE TABLE ALARMLOG
(
PART_ID NUMBER DEFAULT 0,
LOGRECORDID INTEGER NOT NULL,
LOGGINGTIME DATE DEFAULT SYS_EXTRACT_UTC( SYSTIMESTAMP ) NOT NULL,
SYMBOLICNAME VARCHAR2(300 BYTE) DEFAULT NULL,
MANAGEDOBJECTCLASS VARCHAR2(100 BYTE) NOT NULL,
MANAGEDOBJECTINSTA VARCHAR2(300 BYTE) NOT NULL,
EVENTTYPE VARCHAR2(200 BYTE) NOT NULL,
EVENTTIME DATE NOT NULL,
NOTIFICATIONIDENTI NUMBER(10) DEFAULT NULL,
CORRELATEDNOTIFICA VARCHAR2(1200 BYTE) DEFAULT NULL,
ADDITIONALTEXT VARCHAR2(255 BYTE) DEFAULT NULL,
ADDITIONALINFORMAT RAW(1000) DEFAULT NULL,
SBSORIGINATOR NUMBER(10) DEFAULT NULL,
SBSOTHERINFO VARCHAR2(200 BYTE) DEFAULT NULL,
SBSSWORFILEVERSION VARCHAR2(100 BYTE) DEFAULT NULL,
PROBABLECAUSE VARCHAR2(150 BYTE) NOT NULL,
PERCEIVEDSEVERITY NUMBER(5) NOT NULL,
SPECIFICPROBLEMS VARCHAR2(150 BYTE) DEFAULT NULL,
BACKEDUPSTATUS NUMBER(1) DEFAULT NULL,
BACKUPOBJECT VARCHAR2(100 BYTE) DEFAULT NULL,
TRENDINDICATION NUMBER(5) DEFAULT NULL,
PROPOSEDREPAIRACTI VARCHAR2(100 BYTE) DEFAULT NULL,
OPERATORADDITIONAL VARCHAR2(100 BYTE) DEFAULT NULL,
ACKNOWLEDGETIME DATE DEFAULT NULL,
ACKNOWLEDGEUSERNAM VARCHAR2(100 BYTE) DEFAULT NULL,
ALARMCATEGORYLEVEL VARCHAR2(100 BYTE) DEFAULT NULL,
TRIGGEREDTHRESHOLD VARCHAR2(100 BYTE) DEFAULT NULL,
THRESHOLDLEVEL VARCHAR2(100 BYTE) DEFAULT NULL,
OBSERVEDVALUE VARCHAR2(100 BYTE) DEFAULT NULL,
SWVERSION NUMBER(5) DEFAULT 0 NOT NULL,
SWSUBVERSION NUMBER(5) DEFAULT 0 NOT NULL,
TECHNOLOGY NUMBER(5) NOT NULL
)
TABLESPACE DATA01
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 659448K
NEXT 16K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
LOGGING
PARTITION BY RANGE (PART_ID)
(
PARTITION P1 VALUES LESS THAN (1)
NOLOGGING
NOCOMPRESS
TABLESPACE DATA01
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 40K
NEXT 491568K
MINEXTENTS 1
MAXEXTENTS 24
PCTINCREASE 50
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
),
PARTITION P2 VALUES LESS THAN (2)
LOGGING
NOCOMPRESS
TABLESPACE DATA01
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 659448K
NEXT 16K
MINEXTENTS 1
MAXEXTENTS 1
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

And the indexes:

Quote:

CREATE INDEX PK_ALARMLOG ON ALARMLOG
(PART_ID, LOGRECORDID)
TABLESPACE IDX
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 6592K
NEXT 6592K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
LOCAL (
PARTITION P1
LOGGING
NOCOMPRESS
TABLESPACE IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 6592K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P2
LOGGING
NOCOMPRESS
TABLESPACE IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 6592K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
)
NOPARALLEL;

Quote:

CREATE INDEX ALARMLOG_IDX_1 ON ALARMLOG
(EVENTTIME, MANAGEDOBJECTINSTA)
TABLESPACE IDX
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 6592K
NEXT 6592K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
)
LOGGING
LOCAL (
PARTITION P1
LOGGING
NOCOMPRESS
TABLESPACE IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 6592K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
),
PARTITION P2
LOGGING
NOCOMPRESS
TABLESPACE IDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 6592K
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
)
)
NOPARALLEL;

Report message to a moderator

Re: Slow query with customer data [message #335253 is a reply to message #334641]

Mon, 21 July 2008 11:22

Kriptas
Messages: 118
Registered: June 2008

Senior Member

do not use indexes
use full scan and hash joins

SELECT count(*)
FROM alarmLog TAB1,
LEFT JOIN OMCDBSYS.LOG_stateChangeDef TAB2 ON tab1.logRecordId = tab2.stateChangeDefLogRecordId
LEFT JOIN OMCDBSYS.LOG_monitoredAlarm TAB3 ON tab1.logRecordId = tab3.monitoredAlarmLogRecordId
WHERE eventTime>=TO_DATE(:"SYS_B_3", :"SYS_B_4") AND eventTime<=TO_DATE(:"SYS_B_5", :"SYS_B_6")
AND (((NOT (perceivedSeverity=:"SYS_B_0") AND managedObjectClass LIKE :"SYS_B_1" ESCAPE(CHR(:"SYS_B_2")) )

oracle works terrible with indexes.

please run scripts

select clustering_factor from user_indexes where index_name='ALARMLOG_IDX_1'
select clustering_factor from user_indexes where index_name='LOG_STATECHANGEDEF_IDX'
select clustering_factor from user_indexes where index_name='LOG_MONITOREDALARM_IDX'

if columns are clustered, then query using idexes is slower zilion times comparing to ful table scan

[Updated on: Mon, 21 July 2008 11:22]

Report message to a moderator

Re: Slow query with customer data [message #335344 is a reply to message #335253]

Mon, 21 July 2008 22:36

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

There's something you are not telling us.

How many rows are dated between TO_DATE(:"SYS_B_3", :"SYS_B_4") AND TO_DATE(:"SYS_B_5", :"SYS_B_6")?

The fast SQL says that it scanned 1.5M rows from the index, but did not perform a SINGLE successful table access. This implies that there are 1.5M rows in the date range, but that one of the other clauses was used to filter out non-matching rows. This all happened in 5 seconds; it is INCONCEIVABLE that Oracle could perform 1.5M ROWID lookups in 5 seconds, to that filtering must have occurred on an indexed column.

So what other clauses do we have?

       AND NOT (perceivedSeverity=:"SYS_B_0")
       AND managedObjectClass LIKE :"SYS_B_1" ESCAPE(CHR(:"SYS_B_2"))

So that means - on the FAST database - either perceivedSeverity or managedObjectClass (or both) must be part of the ALARMLOG_IDX_1 index.

I think the two databases are indexed differently.

Ross Leishman

Report message to a moderator

Re: Slow query with customer data [message #335437 is a reply to message #335344]

Tue, 22 July 2008 03:35

MartinMM
Messages: 10
Registered: July 2008

Junior Member

Please provide a more advanced trace. The trace normally can provide information about the part of the where clause being represented by the plan part.

Would give something like:

ACCESS FULL SCAN (costs: 3000, Cardinality: 15, Bytes: 250) for tableX.VALUE < 300

I don't know where to retrieve these information without using a tool. I use SQLDeveloper's autotrace feature and I believe you need access privileges for the V$STATS table.

So I guess just provide this kind of enhanced trace giving more hints what kind of condition requires what kind of access.

Cheers,

Martin (Kersten)

[Updated on: Tue, 22 July 2008 03:37]

Report message to a moderator

Re: Slow query with customer data [message #335515 is a reply to message #334641]

Tue, 22 July 2008 07:07

elmu
Messages: 3
Registered: July 2008

Junior Member

Thanks for the responses.

Sometimes the query speed up, but I don't know the main reason.
Below you can see 2 execution plans. The first (the slow) one was executed yesterday afternoon, the second one (the fast) today morning. During the night GATHER_STATS_JOB and AUTO_SPACE_ADVISOR_JOB jobs were executed.

However if I run this jobs manually then no performance improvement at all. Besides this the query is slow since days so, why today become it fast?

Btw.:
- All records are in the timeframe of the query.
- Clustering factors:
ALARMLOG_IDX: 1333895
LOG_STATECHANGEDEF_IDX: 0
LOG_MONITOREDALARM_IDX: 1
- Unfortunately I can not change the SQL as it is generated by a C program.

The new execution plans:

Quote:

SQL> SELECT count(*) /*+ ordered use_nl(TAB1 TAB2 TAB3) index(TAB1 ALARMLOG_IDX_1) index(TAB2 LOG_STATECHANGEDEF_IDX) index(TAB3 LOG_MONITOREDALARM_IDX) */
FROM alarmLog TAB1, OMCDBSYS.LOG_stateChangeDef TAB2, OMCDBSYS.LOG_monitoredAlarm TAB3
WHERE tab1.logRecordId = tab2.stateChangeDefLogRecordId(+)
AND tab1.logRecordId = tab3.monitoredAlarmLogRecordId(+)
2 AND (((NOT (perceivedSeverity=5) AND managedObjectClass LIKE '%ES%' ESCAPE(CHR(0)) )
AND (eventTime>=TO_DATE('20071231220000', 'YYYYMMDDHH24MISS')
AND eventTime<=TO_DATE('20080824220000', 'YYYYMMDDHH24MISS'))))
ORDER BY eventTime DESC , eventTime DESC; 3 4 5 6 7 8

COUNT(*)/*+ORDEREDUSE_NL(TAB1TAB2TAB3)INDEX(TAB1ALARMLOG_IDX_1)INDEX(TAB2LOG_STATECHANGEDEF_IDX)INDEX(TAB3LOG_MONITOREDALARM_IDX)*/
-----------------------------------------------------------------------------------------------------------------------------------
374

Elapsed: 00:52:30.66

Execution Plan
----------------------------------------------------------
Plan hash value: 2579350973

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 68 | 10996 (2)| 00:02:12 | | |
| 1 | SORT AGGREGATE | | 1 | 68 | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 41289 | 2741K| 10996 (2)| 00:02:12 | | |
| 3 | PARTITION RANGE ALL | | 49 | 294 | 1 (0)| 00:00:01 | 1 | 2 |
| 4 | INDEX FULL SCAN | LOG_MONITOREDALARM_IDX | 49 | 294 | 1 (0)| 00:00:01 | 1 | 2 |
|* 5 | HASH JOIN RIGHT OUTER | | 41289 | 2499K| 10994 (2)| 00:02:12 | | |
| 6 | PARTITION RANGE ALL | | 1 | 13 | 1 (0)| 00:00:01 | 1 | 2 |
| 7 | INDEX FULL SCAN | LOG_STATECHANGEDEF_IDX | 1 | 13 | 1 (0)| 00:00:01 | 1 | 2 |
| 8 | NESTED LOOPS | | 41289 | 1975K| 10992 (2)| 00:02:12 | | |
| 9 | SORT UNIQUE | | 1 | 24 | 2 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | PARTITION_TABLE | 1 | 24 | 2 (0)| 00:00:01 | | |
| 11 | PARTITION RANGE ITERATOR| | 58566 | 1429K| 10989 (2)| 00:02:12 | KEY | KEY |
|* 12 | TABLE ACCESS FULL | ALARMLOG | 58566 | 1429K| 10989 (2)| 00:02:12 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("LOGRECORDID"="TAB3"."MONITOREDALARMLOGRECORDID"(+))
5 - access("LOGRECORDID"="TAB2"."STATECHANGEDEFLOGRECORDID"(+))
10 - filter(UPPER("LOGRECORDTABLENAME")='ALARMLOG' AND "ACTIVE"=1)
12 - filter("MANAGEDOBJECTCLASS" LIKE '%ES%' ESCAPE ' ' AND "PERCEIVEDSEVERITY"<>5 AND
"EVENTTIME">=TO_DATE('2007-12-31 22:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "EVENTTIME"<=TO_DATE('2008-08-24
22:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "PART_ID"="PARTITIONID")

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1389453 consistent gets
443118 physical reads
0 redo size
639 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
SQL> SELECT count(*) /*+ ordered use_nl(TAB1 TAB2 TAB3) index(TAB1 ALARMLOG_IDX_1) index(TAB2 LOG_STATECHANGEDEF_IDX) index(TAB3 LOG_MONITOREDALARM_IDX) */
FROM alarmLog TAB1, OMCDBSYS.LOG_stateChangeDef TAB2, OMCDBSYS.LOG_monitoredAlarm TAB3
WHERE tab1.logRecordId = tab2.stateChangeDefLogRecordId(+)
2 AND tab1.logRecordId = tab3.monitoredAlarmLogRecordId(+)
3 4 AND (((NOT (perceivedSeverity=5) AND managedObjectClass LIKE '%ES%' ESCAPE(CHR(0)) )
AND (eventTime>=TO_DATE('20071231220000', 'YYYYMMDDHH24MISS')
AND eventTime<=TO_DATE('20080824220000', 'YYYYMMDDHH24MISS'))))
ORDER BY eventTime DESC , eventTime DESC; 5 6 7 8

COUNT(*)/*+ORDEREDUSE_NL(TAB1TAB2TAB3)INDEX(TAB1ALARMLOG_IDX_1)INDEX(TAB2LOG_STATECHANGEDEF_IDX)INDEX(TAB3LOG_MONITOREDALARM_IDX)*/
-----------------------------------------------------------------------------------------------------------------------------------
374

Elapsed: 00:00:21.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2579350973

-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 68 | 10996 (2)| 00:02:12 | | |
| 1 | SORT AGGREGATE | | 1 | 68 | | | | |
|* 2 | HASH JOIN RIGHT OUTER | | 41289 | 2741K| 10996 (2)| 00:02:12 | | |
| 3 | PARTITION RANGE ALL | | 49 | 294 | 1 (0)| 00:00:01 | 1 | 2 |
| 4 | INDEX FULL SCAN | LOG_MONITOREDALARM_IDX | 49 | 294 | 1 (0)| 00:00:01 | 1 | 2 |
|* 5 | HASH JOIN RIGHT OUTER | | 41289 | 2499K| 10994 (2)| 00:02:12 | | |
| 6 | PARTITION RANGE ALL | | 1 | 13 | 1 (0)| 00:00:01 | 1 | 2 |
| 7 | INDEX FULL SCAN | LOG_STATECHANGEDEF_IDX | 1 | 13 | 1 (0)| 00:00:01 | 1 | 2 |
| 8 | NESTED LOOPS | | 41289 | 1975K| 10992 (2)| 00:02:12 | | |
| 9 | SORT UNIQUE | | 1 | 24 | 2 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | PARTITION_TABLE | 1 | 24 | 2 (0)| 00:00:01 | | |
| 11 | PARTITION RANGE ITERATOR| | 58566 | 1429K| 10989 (2)| 00:02:12 | KEY | KEY |
|* 12 | TABLE ACCESS FULL | ALARMLOG | 58566 | 1429K| 10989 (2)| 00:02:12 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("LOGRECORDID"="TAB3"."MONITOREDALARMLOGRECORDID"(+))
5 - access("LOGRECORDID"="TAB2"."STATECHANGEDEFLOGRECORDID"(+))
10 - filter(UPPER("LOGRECORDTABLENAME")='ALARMLOG' AND "ACTIVE"=1)
12 - filter("MANAGEDOBJECTCLASS" LIKE '%ES%' ESCAPE ' ' AND "PERCEIVEDSEVERITY"<>5 AND
"EVENTTIME">=TO_DATE('2007-12-31 22:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "EVENTTIME"<=TO_DATE('2008-08-24
22:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "PART_ID"="PARTITIONID")

Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
99358 consistent gets
69993 physical reads
0 redo size
639 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>

Report message to a moderator

Re: Slow query with customer data [message #335567 is a reply to message #335515]

Tue, 22 July 2008 13:19

Kriptas
Messages: 118
Registered: June 2008

Senior Member

I can tell only one thing right now
ALARMLOG_IDX clustering fctor is very high.

try to reload data to that partition usig order by clause, and order record by columns which are in ALARMLOG_IDX index.
then reindex partition
and try the query again.

I think that when you put your data to table, you put it very nicely, and clustering factor in your table is very small, so that query with your data runs fast.

I have the same problem, i have 260 000 000 records table
and select 1 500 000 rows from that table using index takes 3hours.
after inserting ordered data to that table and indexing sorted column, query runs 25s.
(but in my case other queries performance got poor after that, because oter queries using other columns and indexes and they become culstered more then before)

Report message to a moderator

Previous Topic:	Better way to handle this query?
Next Topic:	performance issues in insert stmt

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 22:46:13 CST 2024