Home » RDBMS Server » Performance Tuning » performance lag of the quey (11g)
performance lag of the quey [message #617537] Tue, 01 July 2014 07:34 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 11088
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 #617542 is a reply to message #617537] Tue, 01 July 2014 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59499
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do you really think what you posted is readable?
Take some time and make effort to nicely post your question if you want answers.
Just my opinion and advice.

Re: performance lag of the quey [message #617544 is a reply to message #617537] Tue, 01 July 2014 07:48 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2456
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 #617552 is a reply to message #617542] Tue, 01 July 2014 08:23 Go to previous messageGo to next message
andrewscharles89
Messages: 25
Registered: July 2014
Junior Member
cookie monster
in qa
stats collected on 6/15/2014
no of rows 1735709
no triggers in the database


in test
stats collected on 6/17/2014
no of rows 1618436
no triggers in the database

columns are covered by IDXU_MOD_RP_TCHFCST_BO are SCENARIOS_KEY,PROFITCENTER_KEY,ACCOUNT_KEY,PRODUCT_KEY
TIMEPERIOD_KEY,GEOGRAPHY_KEY,LEGALENTITY_KEY,TRADECHANNEL_KEY

how many rows it is suppoded to delete is zero.

how do say there could be stats problem.how do you calculate 720 times
Re: performance lag of the quey [message #617553 is a reply to message #617552] Tue, 01 July 2014 08:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2456
Registered: May 2013
Location: World Wide on the Web
Senior Member
andrewscharles89 wrote on Tue, 01 July 2014 18:53
how 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 #617554 is a reply to message #617553] Tue, 01 July 2014 08:30 Go to previous messageGo to next message
andrewscharles89
Messages: 25
Registered: July 2014
Junior Member
@lalit

how do say there could be serious stats problem
Re: performance lag of the quey [message #617555 is a reply to message #617554] Tue, 01 July 2014 08:32 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2456
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 #617557 is a reply to message #617554] Tue, 01 July 2014 08:36 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
andrewscharles89 wrote on Tue, 01 July 2014 06:30
@lalit

how do say there could be serious stats problem


you report more than 1 million rows in tables & ROW column in EXPLAIN PLANS are always less than 10.
Re: performance lag of the quey [message #617559 is a reply to message #617557] Tue, 01 July 2014 09:11 Go to previous messageGo to next message
andrewscharles89
Messages: 25
Registered: July 2014
Junior Member
what can be done next
Re: performance lag of the quey [message #617560 is a reply to message #617559] Tue, 01 July 2014 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
Senior Member
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_stats.htm#ARPLS68577
Re: performance lag of the quey [message #617594 is a reply to message #617560] Wed, 02 July 2014 02:41 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
How is the table partitioned?
Re: performance lag of the quey [message #617595 is a reply to message #617537] Wed, 02 July 2014 02:52 Go to previous messageGo to next message
John Watson
Messages: 4709
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 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2456
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 #617640 is a reply to message #617596] Wed, 02 July 2014 07:58 Go to previous messageGo to next message
andrewscharles89
Messages: 25
Registered: July 2014
Junior Member
table is partioned by range on scenarios key

on test -the statistics has changed when compared to previous one

still the last analyzed column for this table remains the same date as mentioned earlier


Elapsed: 00:12:08.59

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
----------------------------------------------------------
         63  recursive calls
         11  db block gets
          3  consistent gets
          0  physical reads
       2440  redo size
        477  bytes sent via SQL*Net to client
      29847  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed
          
          
          

the STATE column in v$session during the execution was in WAITING_KNOWN state ,after EXECUTION IS COMPLETE the WAIT_TIME was 0 and state is in WAITING

@lalit
parameters different how do i compare the paramters,i mean how do i get what parameter was chosen in qa and test for gathering stats.any dictionary views for this.

how in qa execution plan is doing a full table scan and in test a index range scan?index's are enabled in both the environments





Re: performance lag of the quey [message #617641 is a reply to message #617640] Wed, 02 July 2014 08:09 Go to previous messageGo to next message
John Watson
Messages: 4709
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.
Re: performance lag of the quey [message #617719 is a reply to message #617537] Thu, 03 July 2014 00:28 Go to previous messageGo to next message
michael_bialik
Messages: 604
Registered: July 2006
Senior Member
1. How many distinct values exist for each of indexed columns?
2. How many value do you supply in IN clauses of your query for each of referenced columns?
3. Is distribution of these values is homogenic?
Re: performance lag of the quey [message #619705 is a reply to message #617537] Thu, 24 July 2014 11:36 Go to previous message
LNossov
Messages: 289
Registered: July 2011
Location: Germany
Senior Member
Your runtime statistics don't correspond to the runtime. Can we exclude locks?
Previous Topic: Performance Tuning
Next Topic: A question about SPM
Goto Forum:
  


Current Time: Thu Oct 30 18:04:15 CDT 2014

Total time taken to generate the page: 0.07625 seconds