OraFAQ Forum: Performance Tuning » Need your help to optimize the Query

Home » RDBMS Server » Performance Tuning » Need your help to optimize the Query (Oracle 10g and Windows 12)

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

Need your help to optimize the Query [message #657604]

Tue, 15 November 2016 12:43

mahatab
Messages: 98
Registered: January 2008
Location: Dhaka

Member

Dear Concerned:

Kindly guide me how to optimize the below query as elapsed time too long as per the tracing: (Please see below for more details)

********************************************************************************

SELECT NVL(SUM(REQ_QTY),0)FROM STOCK_TRANSFER WHERE ACCEPT_TIME IS NULL AND
GRANT_TIME IS NULL AND CANCEL_FLAG IS NULL AND PROD_ID=:B1

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 1 0.37 2.63 17794 17881 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.37 2.63 17794 17881 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65 (recursive depth: 2)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 1113 0.04 2.36
db file sequential read 63 0.02 0.04
********************************************************************************

SELECT NVL(SUM(SL_NO),0)
FROM
STOCK_TRANSFER WHERE ACCEPT_TIME IS NULL AND GRANT_TIME IS NULL AND
CANCEL_FLAG IS NULL

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 1 0.40 2.46 17414 17881 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.40 2.46 17414 17881 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65 (recursive depth: 2)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 127 0.03 0.07
db file scattered read 1086 0.13 2.17
********************************************************************************

Expecting your kind guidance and help on this regards. Thanks in advance.

Regards,
-Mahatab.

Attachment: trace file.txt
(Size: 2.62KB, Downloaded 2315 times)

Report message to a moderator

Re: Need your help to optimize the Query [message #657605 is a reply to message #657604]

Tue, 15 November 2016 12:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

post results from SQL below

SELECT COUNT(*) FROM STOCK_TRANSFER;

select sum(bytes) from user_segments where segment_name = 'STOCK_TRANSFER';

Report message to a moderator

Re: Need your help to optimize the Query [message #657606 is a reply to message #657605]

Tue, 15 November 2016 12:55

mahatab
Messages: 98
Registered: January 2008
Location: Dhaka

Member

Dear BlackSwan,
Thanks for the prompt reply. Please see the information below:

Number of Rows=1288064
Sum(Bytes)=150994944

Kindly advice me in this regards. Thanks in advance.

Regards,
-Mahatab.

Report message to a moderator

Re: Need your help to optimize the Query [message #657607 is a reply to message #657606]

Tue, 15 November 2016 13:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

mahatab wrote on Tue, 15 November 2016 10:55

Dear BlackSwan,
Thanks for the prompt reply. Please see the information below:

Number of Rows=1288064
Sum(Bytes)=150994944

Kindly advice me in this regards. Thanks in advance.

Regards,
-Mahatab.

In round numbers each posted query completed in about 2.5 seconds
In round numbers Oracle processed just over 500,000 rows per seconds.
If this is not fast enough for you, then you need faster hardware since Oracle is limited by the physical hardware limitations.

Report message to a moderator

Re: Need your help to optimize the Query [message #657608 is a reply to message #657607]

Tue, 15 November 2016 13:19

mahatab
Messages: 98
Registered: January 2008
Location: Dhaka

Member

Deleting some old records can help to reduce the query execution time? What do you suggest? and creating indexing on the where condition column?? Can you advice? Earlier today, the execution was less then the time elapsed.

Report message to a moderator

Re: Need your help to optimize the Query [message #657609 is a reply to message #657608]

Tue, 15 November 2016 13:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

mahatab wrote on Tue, 15 November 2016 11:19

Deleting some old records can help to reduce the query execution time? What do you suggest? and creating indexing on the where condition column?? Can you advice? Earlier today, the execution was less then the time elapsed.

>WHERE ACCEPT_TIME IS NULL AND GRANT_TIME IS NULL AND CANCEL_FLAG IS NULL
above dictates that a Full Table Scan (FTS) is required to produce desired result set.
FTS proceeds until hitting High Water Mark, even if EVERY row was DELETED.

> Earlier today, the execution was less then the time elapsed.
Something changed & it wasn't Oracle.

ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) ddl for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: Need your help to optimize the Query [message #659014 is a reply to message #657609]

Wed, 04 January 2017 03:29

wanear
Messages: 8
Registered: December 2016

Junior Member

create index xx_idx on STOCK_TRANSFER(ACCEPT_TIME,GRANT_TIME ,CANCEL_FLAG,PROD_ID,0 )

Report message to a moderator

Re: Need your help to optimize the Query [message #659016 is a reply to message #659014]

Wed, 04 January 2017 03:56

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why the 0?

Report message to a moderator

Re: Need your help to optimize the Query [message #659318 is a reply to message #659016]

Thu, 12 January 2017 10:53

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

I suppose it is to include the nulls too. By adding a constant to the index definition (0 in this case) it's a function based index that will include nulls too.
Perhaps a function based index on only those columns all null (which is a small subset of the total rows in table I guess), might be small and thus even faster.

Report message to a moderator

Previous Topic:	slow while inserting
Next Topic:	SGA AND PGA TUNING

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 16:45:17 CST 2025