performance lag of the quey [message #617537] |
Tue, 01 July 2014 07:34 |
|
andrewscharles89
Messages: 25 Registered: July 2014
|
Junior Member |
|
|
i have a query which takes 2 mins to exceute in qa environmnet
and the same query takes 10 mins in test environment.
in qa environmnet
Elapsed: 00:00:02.86
Execution Plan
----------------------------------------------------------
Plan hash value: 1402972727
--------------------------------------------------------------------------------
-----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------------------
| 0 | DELETE STATEMENT | | 5 | 230 | 18 (0
)| 00:00:01 | | |
| 1 | DELETE | MOD_RP_TCHFCST_BO | | |
| | | |
| 2 | PARTITION RANGE SINGLE| | 5 | 230 | 18 (0
)| 00:00:01 | 125 | 125 |
|* 3 | TABLE ACCESS FULL | MOD_RP_TCHFCST_BO | 5 | 230 | 18 (0
)| 00:00:01 | 125 | 125 |
--------------------------------------------------------------------------------
-----------------------------
Statistics
----------------------------------------------------------
63 recursive calls
9 db block gets
40 consistent gets
35 physical reads
2488 redo size
483 bytes sent via SQL*Net to client
29847 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
in test environment. the same query takes 10 mins to execute
Elapsed: 00:12:06.73
Execution Plan
----------------------------------------------------------
Plan hash value: 4154840340
--------------------------------------------------------------------------------
----------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------------
| 0 | DELETE STATEMENT | | 1 | 104 | 0
(0)| 00:00:01 | | |
| 1 | DELETE | MOD_RP_TCHFCST_BO | | |
| | | |
| 2 | PARTITION RANGE SINGLE| | 1 | 104 | 0
(0)| 00:00:01 | 125 | 125 |
| 3 | INLIST ITERATOR | | | |
| | | |
|* 4 | INDEX RANGE SCAN | IDXU_MOD_RP_TCHFCST_BO | 1 | 104 | 0
(0)| 00:00:01 | 125 | 125 |
--------------------------------------------------------------------------------
----------------------------------
Statistics
----------------------------------------------------------
485 recursive calls
12 db block gets
1685 consistent gets
48 physical reads
2392 redo size
479 bytes sent via SQL*Net to client
29847 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
0 rows processed
DELETE FROM Mod_RP_TCHFCST_BO WHERE SCENARIOS_Key = AND GEOGRAPHY_Key IN ( )
AND LEGALENTITY_Key IN ( ) AND PRODUCT_Key IN () AND PROFITCENTER_Key =
AND TRADECHANNEL_Key IN () AND ACCOUNT_Key = AND TIMEPERIOD_Key =
i have unique index on all the where conds
question is why the same query takes time in different environment
[Updated on: Tue, 01 July 2014 07:38] Report message to a moderator
|
|
|
Re: performance lag of the quey [message #617541 is a reply to message #617537] |
Tue, 01 July 2014 07:45 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Are the stats upto date?
The explain plan in both cases says the delete should take 1 second, so the fact that it's taking 720 times that in test suggests something seriously wrong with the stats.
Also the QA query appears to have taken 2 seconds not 2 mins.
How many rows in the table?
How many rows should the query delete?
Do you have any delete triggers on the tables?
What columns are covered by IDXU_MOD_RP_TCHFCST_BO?
|
|
|
|
Re: performance lag of the quey [message #617544 is a reply to message #617537] |
Tue, 01 July 2014 07:48 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
First of all, test environment should not be the platform for performance testing/tuning.
To answer your question, why the same query takes different execution times, to be short and precise, the environments are different, parameters defined different, different SGA, number of CPUs, system workload, differnet execution plans .....
|
|
|
|
Re: performance lag of the quey [message #617553 is a reply to message #617552] |
Tue, 01 July 2014 08:27 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
andrewscharles89 wrote on Tue, 01 July 2014 18:53how do you calculate 720 times
Because Elapsed: 00:12:06.73 means 12 minutes+, around 720 as compared to 1 sec per the time in execution plan. Isn't it 720+ times more than what the optimizer estimated?
|
|
|
|
Re: performance lag of the quey [message #617555 is a reply to message #617554] |
Tue, 01 July 2014 08:32 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
andrewscharles89 wrote on Tue, 01 July 2014 19:00@lalit
how do say there could be serious stats problem
Because that's what the actuals and esimations are pointing to.
Optimizer needs information known as stats, based on which it makes an execution plan. In your case, it estimated the query would complete in 1 second, however, it took 720 seconds+.
|
|
|
|
|
|
|
Re: performance lag of the quey [message #617595 is a reply to message #617537] |
Wed, 02 July 2014 02:52 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
The object statistics look correct to me. The query deletes zero rows, and the estimate is to select either 1 or 5 rows for deletion. Close enough.
The execution statistics are a different matter. The first query does 40 consistent gets in 2 minutes, the second does 1685 consistent gets in 12 minutes. That is ridiculous. I think that the queries are waiting on something, and you need to find out what. It could be something as simple as a row lock. Look at v$session.event and related columns while the statements are running.
|
|
|
Re: performance lag of the quey [message #617596 is a reply to message #617595] |
Wed, 02 July 2014 03:18 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
As John suggested to check for wait events, look out for status column in v$session. For wait events there are 4 particular status :
1. WAITING
2. WAITED KNOWN TIME
3. WAITED SHORT TIME
4. WAITED UNKNOWN TIME.
A session is actually waiting if STATE is WAITING. So check if your session has this state.
|
|
|
|
Re: performance lag of the quey [message #617641 is a reply to message #617640] |
Wed, 02 July 2014 08:09 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You are looking at the wrong columns. Have you looked up the definition of the view? EVENT and SECONDS_IN_WAIT and BLOCKING_SESSION and several others might be interesting.
|
|
|
|
|