Home » RDBMS Server » Performance Tuning » Long running query (Oracle 11.2)
Long running query [message #570166] Tue, 06 November 2012 04:05 Go to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi All,
I have a query which is running for more than 2 mins in production and I'm unable to tune this query.
Table:
1. CREATE TABLE test(b_id NUMBER,
		  BAC VARCHAR2(1),
		  DPC VARCHAR2(1),
		  QDC VARCHAR2(1));

2. SELECT COUNT(*) FROM test;
9111307

3. SELECT DISTINCT dpc FROM test;
U
F

4. SELECT DISTINCT qdc FROM test;
R
P
Q
A
N

5. SELECT DISTINCT bac FROM test;
N
S

6. SELECT column_name,index_name FROM all_ind_columns WHERE table_name='TEST'
   AND column_name IN ('BAC','QDC','ED','DPC','B_ID')
COLUMN_NAME		INDEX_NAME
ED			X1ED
B_ID			PK_TEST


Query and Explain plan:
SELECT  b_id,b_nr   FROM test  WHERE b_id IN ( 
SELECT MAX(b_id) b_id FROM test WHERE DPC='F' AND BAC='S' AND QDC='A' 
AND TRUNC(ED) BETWEEN TO_DATE('1-OCT-2012' ,'DD-MON-YYYY') AND TO_DATE( '30-OCT-2012' ,'DD-MON-YYYY')
GROUP BY b_nr 
);

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |  3968 | 99200 | 73985  (13)| 00:03:32 |
|   1 |  NESTED LOOPS                |          |       |       |            |          |
|   2 |   NESTED LOOPS               |          |  3968 | 99200 | 73985  (13)| 00:03:32 |
|   3 |    VIEW                      | VW_NSO_1 |  3971 | 51623 | 66021  (14)| 00:03:09 |
|   4 |     HASH GROUP BY            |          |  3971 |   100K| 66021  (14)| 00:03:09 |
|*  5 |      TABLE ACCESS FULL       | TEST     |  3974 |   100K| 66019  (14)| 00:03:09 |
|*  6 |    INDEX UNIQUE SCAN         | PK_TEST  |     1 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    12 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter("QDC"='A' AND "BAC"='S' AND "DPC"='F' 
              AND TRUNC(INTERNAL_FUNCTION("ED"))>=TO_DATE(' 2012-10-01 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("ED"))<=TO_DATE(' 
              2012-10-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("D"."BD_ID"="BD_ID")


Need your help/suggestions to solve this issue.

Regards,
Lakshmi.

Report message to a moderator

Re: Long running query [message #570167 is a reply to message #570166] Tue, 06 November 2012 04:13 Go to previous messageGo to next message
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
Senior Member
Your CREATE TABLE command does not have enough clumns to run that query.

Report message to a moderator

Re: Long running query [message #570168 is a reply to message #570167] Tue, 06 November 2012 04:16 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi Watson,
Sorry, while simulating the actual table and missed one column. Please find the below DDL:

CREATE TABLE test(b_id NUMBER,
		  BAC	VARCHAR2(1),
		  DPC VARCHAR2(1),
		  QDC VARCHAR2(1),
		  ED DATE);


Regards,
Lakshmi.

Report message to a moderator

Re: Long running query [message #570170 is a reply to message #570168] Tue, 06 November 2012 04:26 Go to previous messageGo to next message
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
Senior Member
I give up.
orcl> CREATE TABLE test(b_id NUMBER,
  2               BAC   VARCHAR2(1),
  3               DPC VARCHAR2(1),
  4               QDC VARCHAR2(1),
  5               ED DATE);

Table created.

orcl> SELECT  b_id,b_nr   FROM test  WHERE b_id IN (
  2  SELECT MAX(b_id) b_id FROM test WHERE DPC='F' AND BAC='S' AND QDC='A'
  3  AND TRUNC(ED) BETWEEN TO_DATE('1-OCT-2012' ,'DD-MON-YYYY') AND TO_DATE( '30-OCT-2012' ,'DD-MON-YYYY')
  4  GROUP BY b_nr
  5  );
SELECT  b_id,b_nr   FROM test  WHERE b_id IN (
             *
ERROR at line 1:
ORA-00904: "B_NR": invalid identifier


orcl>

Report message to a moderator

Re: Long running query [message #570183 is a reply to message #570170] Tue, 06 November 2012 06:25 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
TRy rewriting it without the trunc.

Report message to a moderator

Re: Long running query [message #570195 is a reply to message #570183] Tue, 06 November 2012 11:35 Go to previous messageGo to next message
Flyby
Messages: 188
Registered: March 2011
Location: Belgium
Senior Member
as above. Convert between to >=to_date(mystart) and <to_date(myend)+1. Trunc (columns) disables the use of index unless you have a function index trunc(ed) on it.

Report message to a moderator

Re: Long running query [message #570196 is a reply to message #570195] Tue, 06 November 2012 11:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>2. SELECT COUNT(*) FROM test;
>9111307
Either Production DB does not have current statistics or posted PLAN was not produced on Production DB.
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |  3968 | 99200 | 73985  (13)| 00:03:32 |
|   1 |  NESTED LOOPS                |          |       |       |            |          |
|   2 |   NESTED LOOPS               |          |  3968 | 99200 | 73985  (13)| 00:03:32 |
|   3 |    VIEW                      | VW_NSO_1 |  3971 | 51623 | 66021  (14)| 00:03:09 |

To successfully tune SQL for Production DB, it should be done with the same data as Production DB.

Report message to a moderator

Re: Long running query [message #570227 is a reply to message #570196] Wed, 07 November 2012 00:29 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Dear cookiemonster,
we can not take out TRUNC function, because some dates have timestamp with them.

@Flyby,
Thanks for your suggestion. I'll check for performance with function based index.

@Blackswan,
Please find the below count and explain plan from production . The distinct values of the columns are same as above.
SELECT COUNT(*) FROM test;
17675679

Plan hash value: 3209421779
 
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |  7413 |   180K|   353K  (2)| 01:10:48 |
|   1 |  NESTED LOOPS                |          |       |       |            |          |
|   2 |   NESTED LOOPS               |          |  7413 |   180K|   353K  (2)| 01:10:48 |
|   3 |    VIEW                      | VW_NSO_1 |  7413 | 96369 |   339K  (2)| 01:07:50 |
|   4 |     HASH GROUP BY            |          |  7413 |   188K|   339K  (2)| 01:07:50 |
|*  5 |      TABLE ACCESS FULL       | TEST     |  7415 |   188K|   339K  (2)| 01:07:50 |
|*  6 |    INDEX UNIQUE SCAN         | PK_TEST  |     1 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    12 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

   Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - filter("QDC"='A' AND "BAC"='S' AND "DPC"='F' 
              AND TRUNC(INTERNAL_FUNCTION("ED"))>=TO_DATE(' 2012-10-01 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("ED"))<=TO_DATE(' 
              2012-10-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - access("D"."BD_ID"="BD_ID")


Regards,
Lakshmi.

Report message to a moderator

Re: Long running query [message #570279 is a reply to message #570227] Wed, 07 November 2012 08:17 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
lakshmis wrote on Wed, 07 November 2012 06:29
Dear cookiemonster,
we can not take out TRUNC function, because some dates have timestamp with them.

Yes you can, you just need to tweak the query a bit, which is basically what flyby was pointing out as well.

This:
TRUNC(ED) BETWEEN TO_DATE('1-OCT-2012' ,'DD-MON-YYYY') AND TO_DATE( '30-OCT-2012' ,'DD-MON-YYYY')

is logically equivalent to:
ED BETWEEN TO_DATE('1-OCT-2012 00:00:00' ,'DD-MON-YYYY HH24:MI:SS') AND TO_DATE( '30-OCT-2012 23:59:59' ,'DD-MON-YYYY HH24:MI:SS')

or, if you don't want to write out the time component:
ED BETWEEN TO_DATE('1-OCT-2012' ,'DD-MON-YYYY') AND (TO_DATE( '30-OCT-2012' ,'DD-MON-YYYY') + 1 - (1/24/60/60))



Both of those will give the same result as the original but allow oracle to use the index on ed.
You virtually never need to use trunc on a date column to do date comparisions. The fact that the dates have times is irrelevant.

Report message to a moderator

Re: Long running query [message #570538 is a reply to message #570279] Sun, 11 November 2012 23:50 Go to previous message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi Cookiemonster,
Thanks a ton for your suggestion. I could not think of writing the query in this way Sad

Regards,
Lakshmi.

Report message to a moderator

Previous Topic: Table rebuild should be done after removing most of its data?
Next Topic: how to examine the impact of a too long varchar2 field
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Feb 16 07:25:30 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.