| DB in snail's pace or bad SQL query or bad DB design? [message #606229] |
Tue, 21 January 2014 05:34  |
 |
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
|
Senior Member |
|
|
Hi
Sometimes this query has an elapsed time of 8 minutes.
where is the bottleneck/problem?
SELECT COUNT (*)
|| ';'
|| RSS_NAME
|| ';'
|| TO_CHAR (SYSDATE, 'DD.MM.YYYY/HH24:MI:SS')
|| ';'
|| 'NOK'
FROM TRANS
WHERE TO_TIMESTAMP (TIME_CREATED, 'YYYY/MM/DD-HH24:MI:SS.FF') >
SYSDATE - INTERVAL '15' MINUTE
AND ACT_MSG LIKE
'%ESS***** R Transaction failed. Communication service timed out%'
GROUP BY RSS_NAME;
PLAN_TABLE
SQL> set linesize 132
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3238048461
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 94 | 31490 | 92446 (2)| 00:18:30 |
| 1 | HASH GROUP BY | | 94 | 31490 | 92446 (2)| 00:18:30 |
|* 2 | TABLE ACCESS FULL| TRANS | 5283 | 1728K| 92444 (2)| 00:18:30 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - filter("ACT_MSG" LIKE '%ESS***** R Transaction failed.
Communication service timed out%' AND
TO_TIMESTAMP("TIME_CREATED",'YYYY/MM/DD-HH24:MI:SS.FF')>SYSDATE@!-INTERV
AL'+00 00:15:00' DAY(2) TO SECOND(0))
17 rows selected.
count
SQL> select count(*) from TRANS;
COUNT(*)
----------
2423186
only 3 months archive. Older records will be archived in other table.
desc
SQL> desc TRANS;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
SERIAL NOT NULL NUMBER(11)
UPD_TIME NOT NULL DATE
MESSAGE NOT NULL VARCHAR2(255 CHAR)
ENTITY_TABLE NOT NULL VARCHAR2(32 CHAR)
ACTION NOT NULL VARCHAR2(12 CHAR)
STATUS NOT NULL NUMBER(11)
SERIAL_NEG NUMBER(11)
RET_CODE NUMBER(11)
PRE_MSG VARCHAR2(4000 CHAR)
ACT_MSG VARCHAR2(4000 CHAR)
POST_MSG VARCHAR2(4000 CHAR)
PLATFORM_NAME VARCHAR2(64 CHAR)
SHORT_NAME NOT NULL VARCHAR2(2 CHAR)
RSS_NAME VARCHAR2(32 CHAR)
RSS_TYPE VARCHAR2(12 CHAR)
MSG_TYPE VARCHAR2(1 CHAR)
REC_TYPE VARCHAR2(1 CHAR)
COMM_MSG CLOB
ADMIN VARCHAR2(255 CHAR)
ADMIN_GROUP VARCHAR2(255 CHAR)
FIND_KEYS VARCHAR2(700 CHAR)
ESS_USER NOT NULL VARCHAR2(20 CHAR)
SIID VARCHAR2(9 CHAR)
HOT_PATH VARCHAR2(1 CHAR)
ORIGIN VARCHAR2(32 CHAR)
SYNC_PW VARCHAR2(1 CHAR)
INITIAL_LOAD NUMBER(11)
DOWNLOAD_METHOD NUMBER(11)
USER_BY_NAME VARCHAR2(255 CHAR)
USER_BY_PREFIX VARCHAR2(255 CHAR)
USER_BY_UG VARCHAR2(255 CHAR)
OE_BY_NAME CLOB
UG_BY_NAME VARCHAR2(255 CHAR)
USER_ID VARCHAR2(20 CHAR)
RSS_USER_NAME VARCHAR2(511 CHAR)
UG_NAME VARCHAR2(255 CHAR)
RES_ID NUMBER(11)
ACE_ID NUMBER(11)
OE_FULL_NAME VARCHAR2(255 CHAR)
EXTERNAL_ID NUMBER(11)
REMARKS VARCHAR2(2000 CHAR)
ONLINE_MODE NOT NULL NUMBER(11)
PARENT_ID NUMBER(11)
PRIORITY NUMBER(11)
AGENT_RELEASE VARCHAR2(12 CHAR)
HD_SHOULD_CREATE_TICKET VARCHAR2(1 CHAR)
HD_TICKET_ID VARCHAR2(64 CHAR)
HD_TICKET_WAS_PROCESSED VARCHAR2(1 CHAR)
ORIGINAL_COMMAND VARCHAR2(32 CHAR)
TIME_CREATED VARCHAR2(23 CHAR)
TIME_SENT VARCHAR2(23 CHAR)
TIME_STARTED VARCHAR2(23 CHAR)
TIME_AGENT_RESPONDED VARCHAR2(23 CHAR)
TIME_ENDED VARCHAR2(23 CHAR)
|
|
|
|
| Re: DB in snail's pace or bad SQL query or bad DB design? [message #606234 is a reply to message #606229] |
Tue, 21 January 2014 05:48  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
One possibility would be to create an index on TIME_CREATED, and then to re-write your predicate so that instead of applying TO_TIMESTAMP to TIME_CREATED you apply TO_CHAR to the SYSDATE - INTERVAL. This is assuming that TIME_CREATED is a string, as implied by your code. If it is not, well, I shall leave that as an exercise for your edification.
|
|
|
|
| Re: DB in snail's pace or bad SQL query or bad DB design? [message #606274 is a reply to message #606234] |
Tue, 21 January 2014 22:17 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Good job on the details. Providing the table row count is significant information in tuning, as well as providing the predicate info along with the core query plan output. Most people come here and fail to provide said details with their questions so KUDOS to you.
There are potentially many reasons why this query might go slow some times and fast others. The most obvious examples are:
1. sometimes there are few rows in the table that match the query criteria and sometimes many. If so then when there are few rows the grouping would go fast and when there are many rows, the grouping would go slower. However this should be a 30 second query on most systems, maybe less because of the small number of rows both in the table (2 million is small by my standards) and the number of rows returned (plan step 2 says after filtering 5283 rows will remain to be grouped). So I don't think this is the problem.
2. there might be someone updating the table will you are reading it. If there has been a lot of block changes for un-committed transactions at the time you execute this query then this query will need to do a CONSISTENT GET for each block it needs and this can be expensive and could easily explain an 8 minute runtime even 5for such a small number of rows.
Can you provide some additional info:
1. is there a different query plan for a fast query vs. a slow one?
2. if you remove the group by clause and group columns and just to a simple count, do you get something close to 5283?
3. can you confirm if people are updating when your run times go long?
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
