Home » RDBMS Server » Performance Tuning » DB in snail's pace or bad SQL query or bad DB design? (Oracle 10g)
DB in snail's pace or bad SQL query or bad DB design? [message #606229] Tue, 21 January 2014 05:34 Go to next message
Messages: 213
Registered: September 2013
Location: Baden-W├╝rttemberg
Senior Member

Sometimes this query has an elapsed time of 8 minutes.

where is the bottleneck/problem?

         || ';'
         || RSS_NAME
         || ';'
         || TO_CHAR (SYSDATE, 'DD.MM.YYYY/HH24:MI:SS')
         || ';'
         || 'NOK'
            SYSDATE - INTERVAL '15' MINUTE
                '%ESS***** R Transaction failed. Communication service timed out%'


SQL> set linesize 132
SQL> SELECT * FROM TABLE(dbms_xplan.display);

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):


   2 - filter("ACT_MSG" LIKE '%ESS***** R Transaction failed.
              Communication service timed out%' AND
              AL'+00 00:15:00' DAY(2) TO SECOND(0))

17 rows selected.


SQL> select count(*) from TRANS;


only 3 months archive. Older records will be archived in other table.


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 Go to previous messageGo to next message
John Watson
Messages: 8869
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 Go to previous message
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?

Previous Topic: Tuning for select query
Next Topic: More Hash Value
Goto Forum:

Current Time: Sat Sep 23 20:06:31 CDT 2023