Home » RDBMS Server » Performance Tuning » Need your help to optimize the Query (Oracle 10g and Windows 12)
Need your help to optimize the Query [message #657604] Tue, 15 November 2016 12:43 Go to next message
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.
Re: Need your help to optimize the Query [message #657605 is a reply to message #657604] Tue, 15 November 2016 12:48 Go to previous messageGo to next message
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';
Re: Need your help to optimize the Query [message #657606 is a reply to message #657605] Tue, 15 November 2016 12:55 Go to previous messageGo to next message
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.
Re: Need your help to optimize the Query [message #657607 is a reply to message #657606] Tue, 15 November 2016 13:15 Go to previous messageGo to next message
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.
Re: Need your help to optimize the Query [message #657608 is a reply to message #657607] Tue, 15 November 2016 13:19 Go to previous messageGo to next message
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.
Re: Need your help to optimize the Query [message #657609 is a reply to message #657608] Tue, 15 November 2016 13:29 Go to previous messageGo to next message
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


Re: Need your help to optimize the Query [message #659014 is a reply to message #657609] Wed, 04 January 2017 03:29 Go to previous messageGo to next message
wanear
Messages: 8
Registered: December 2016
Junior Member

create index xx_idx on STOCK_TRANSFER(ACCEPT_TIME,GRANT_TIME ,CANCEL_FLAG,PROD_ID,0 )
Re: Need your help to optimize the Query [message #659016 is a reply to message #659014] Wed, 04 January 2017 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why the 0?
Re: Need your help to optimize the Query [message #659318 is a reply to message #659016] Thu, 12 January 2017 10:53 Go to previous message
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.
Previous Topic: slow while inserting
Next Topic: SGA AND PGA TUNING
Goto Forum:
  


Current Time: Thu Mar 28 06:50:48 CDT 2024