Home » RDBMS Server » Performance Tuning » Performance issues between Databases (Oracle 10.2.0)
Performance issues between Databases [message #355736] Mon, 27 October 2008 04:52 Go to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
Hi@all,
i have the Problem that i have 2 Databases. One is the production an the other is the test database. Both are running on the same machine and the test database is mirrored every day with the data of the production database. So far so good.
But on the production database i have a really poor performance and on the test db not.

I have an statement for my testing and on the production db there is a disastrous explain plan but on the test, the explain plan is wonderful and the statement is very fast.

The Oracle Parameters are the same on both databases. I have gathered statistics on both databases. Can you give me an advice where to look for this performance hole?

Thanks in advance

regards

eigeneachse
Re: Performance issues between Databases [message #355758 is a reply to message #355736] Mon, 27 October 2008 07:12 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

compare execution plan of both db for the same query.

or compare statspack report of both db for the same period of time.
Re: Performance issues between Databases [message #355766 is a reply to message #355736] Mon, 27 October 2008 08:20 Go to previous messageGo to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
Hi Mohammad Taj,
this is exactly the problem. The execution plans are very different. I just don't know why they are.

Here is the production plan.
Plan
SELECT STATEMENT  ALL_ROWSCost: 68,073  Bytes: 3.857.649  Cardinality: 9,717  																		
	43 NESTED LOOPS OUTER  Cost: 68,073  Bytes: 3.857.649  Cardinality: 9,717  																	
		40 NESTED LOOPS  Cost: 48,699  Bytes: 3.725.645  Cardinality: 9,677  																
			38 NESTED LOOPS OUTER  Cost: 39,051  Bytes: 3.623.512  Cardinality: 9,637  															
				35 NESTED LOOPS OUTER  Cost: 39,039  Bytes: 3.517.505  Cardinality: 9,637  														
					32 NESTED LOOPS OUTER  Cost: 39,027  Bytes: 3.411.498  Cardinality: 9,637  													
						29 NESTED LOOPS OUTER  Cost: 39,015  Bytes: 3.286.217  Cardinality: 9,637  												
							26 NESTED LOOPS OUTER  Cost: 39,004  Bytes: 3.160.936  Cardinality: 9,637  											
								23 NESTED LOOPS OUTER  Cost: 29,355  Bytes: 2.707.997  Cardinality: 9,637  										
									20 NESTED LOOPS OUTER  Cost: 19,706  Bytes: 2.255.058  Cardinality: 9,637  									
										17 NESTED LOOPS OUTER  Cost: 19,65  Bytes: 2.043.044  Cardinality: 9,637  								
											14 HASH JOIN RIGHT OUTER  Cost: 10,087  Bytes: 1.862.835  Cardinality: 9,553  							
												1 TABLE ACCESS FULL TABLE CEDBA.DOK_TITEL Cost: 85  Bytes: 884,239  Cardinality: 30,491  						
												13 HASH JOIN OUTER  Cost: 9,861  Bytes: 1.585.798  Cardinality: 9,553  						
													11 HASH JOIN OUTER  Cost: 9,626  Bytes: 1.108.148  Cardinality: 9,553  					
														9 HASH JOIN OUTER  Cost: 9,553  Bytes: 842,83  Cardinality: 9,47  				
															7 HASH JOIN  Cost: 9,481  Bytes: 582,056  Cardinality: 9,388  			
																5 NESTED LOOPS  Cost: 9,413  Bytes: 413,072  Cardinality: 9,388  		
																	2 TABLE ACCESS FULL TABLE CEDBA.MRG_CONTENT_ATRIBUT Cost: 13  Bytes: 75,104  Cardinality: 9,388  	
																	4 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_PROGRAMM Cost: 1  Bytes: 36  Cardinality: 1  	
																		3 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.IX_PROGRAMM_MERGE Cost: 0  Cardinality: 1  
																6 TABLE ACCESS FULL TABLE CEDBA.REF_MANDANT_TITELFASSUNG Cost: 66  Bytes: 446,364  Cardinality: 24,798  		
															8 TABLE ACCESS FULL TABLE CEDBA.DOK_PROGRAMMFASSUNG Cost: 71  Bytes: 690,039  Cardinality: 25,557  			
														10 TABLE ACCESS FULL TABLE CEDBA.DOK_PROGRAMMFASSUNG Cost: 71  Bytes: 690,039  Cardinality: 25,557  				
													12 TABLE ACCESS FULL TABLE CEDBA.DOK_TITEL Cost: 85  Bytes: 1.524.550  Cardinality: 30,491  					
											16 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_PROGRAMMFASSUNG Cost: 1  Bytes: 17  Cardinality: 1  							
												15 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.PROGRAMMFASSUNG_PK Cost: 0  Cardinality: 1  						
										19 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_SERIEN Cost: 1  Bytes: 22  Cardinality: 1  								
											18 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.SERIEN_PK Cost: 0  Cardinality: 1  							
									22 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_TITEL Cost: 1  Bytes: 47  Cardinality: 1  									
										21 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.TITEL_PK Cost: 0  Cardinality: 1  								
								25 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_TITEL Cost: 1  Bytes: 47  Cardinality: 1  										
									24 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.TITEL_PK Cost: 0  Cardinality: 1  									
							28 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.PAR_PROGRAMMTYP Cost: 1  Bytes: 13  Cardinality: 1  											
								27 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.PROGRAMMTYP_PK Cost: 0  Cardinality: 1  										
						31 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.PAR_PRODUKTIONSFORMAT Cost: 1  Bytes: 13  Cardinality: 1  												
							30 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.PRODUKTIONSFORMAT_PK Cost: 0  Cardinality: 1  											
					34 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.PAR_SPRACHE Cost: 1  Bytes: 11  Cardinality: 1  													
						33 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.SPRACHE_PK Cost: 0  Cardinality: 1  												
				37 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.PAR_SPRACHE Cost: 1  Bytes: 11  Cardinality: 1  														
					36 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.SPRACHE_PK Cost: 0  Cardinality: 1  													
			39 INDEX RANGE SCAN INDEX CEDBA.IX_R_MAN_CONTENT_MERG_MAND Cost: 1  Bytes: 9  Cardinality: 1  															
		42 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.REF_CONTENT_MANDANT Cost: 2  Bytes: 12  Cardinality: 1  																
			41 INDEX RANGE SCAN INDEX CEDBA.IX_R_MAN_CONTENT_MERG_MAND Cost: 1  Cardinality: 1  															


and here ist the test plan
Plan
SELECT STATEMENT  ALL_ROWSCost: 2,166  Bytes: 298,231  Cardinality: 331  																		
	48 NESTED LOOPS OUTER  Cost: 2,166  Bytes: 298,231  Cardinality: 331  																	
		45 NESTED LOOPS  Cost: 2,131  Bytes: 152,574  Cardinality: 177  																
			43 NESTED LOOPS OUTER  Cost: 2,13  Bytes: 79,42  Cardinality: 95  															
				40 NESTED LOOPS OUTER  Cost: 2,111  Bytes: 73,245  Cardinality: 95  														
					37 NESTED LOOPS OUTER  Cost: 2,092  Bytes: 67,07  Cardinality: 95  													
						34 NESTED LOOPS OUTER  Cost: 2,073  Bytes: 60,895  Cardinality: 95  												
							31 NESTED LOOPS OUTER  Cost: 2,053  Bytes: 54,72  Cardinality: 95  											
								28 NESTED LOOPS OUTER  Cost: 2,034  Bytes: 50,445  Cardinality: 95  										
									25 NESTED LOOPS OUTER  Cost: 2,015  Bytes: 46,17  Cardinality: 95  									
										22 NESTED LOOPS OUTER  Cost: 1,996  Bytes: 40,28  Cardinality: 95  								
											19 NESTED LOOPS OUTER  Cost: 1,977  Bytes: 36,86  Cardinality: 95  							
												16 NESTED LOOPS OUTER  Cost: 1,958  Bytes: 34,2  Cardinality: 95  						
													13 NESTED LOOPS OUTER  Cost: 1,939  Bytes: 29,64  Cardinality: 95  					
														10 NESTED LOOPS OUTER  Cost: 1,92  Bytes: 24,985  Cardinality: 95  				
															7 HASH JOIN  Cost: 1,901  Bytes: 20,33  Cardinality: 95  			
																2 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.REF_MANDANT_TITELFASSUNG Cost: 10  Bytes: 10,816  Cardinality: 208  		
																	1 INDEX RANGE SCAN INDEX CEDBA.IX_MANDANT_TITELFASSUNG_MND Cost: 10  Cardinality: 83  	
																6 NESTED LOOPS  Cost: 1,891  Bytes: 1.520.856  Cardinality: 9,388  		
																	3 TABLE ACCESS FULL TABLE CEDBA.MRG_CONTENT_ATRIBUT Cost: 11  Bytes: 244,088  Cardinality: 9,388  	
																	5 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_PROGRAMM Cost: 1  Bytes: 136  Cardinality: 1  	
																		4 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.IX_PROGRAMM_MERGE Cost: 1  Cardinality: 1  
															9 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_PROGRAMMFASSUNG Cost: 1  Bytes: 49  Cardinality: 1  			
																8 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.PROGRAMMFASSUNG_PK Cost: 1  Cardinality: 1  		
														12 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_PROGRAMMFASSUNG Cost: 1  Bytes: 49  Cardinality: 1  				
															11 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.PROGRAMMFASSUNG_PK Cost: 1  Cardinality: 1  			
													15 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_TITEL Cost: 1  Bytes: 48  Cardinality: 1  					
														14 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.TITEL_PK Cost: 1  Cardinality: 1  				
												18 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_TITEL Cost: 1  Bytes: 28  Cardinality: 1  						
													17 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.TITEL_PK Cost: 1  Cardinality: 1  					
											21 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_PROGRAMMFASSUNG Cost: 1  Bytes: 36  Cardinality: 1  							
												20 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.PROGRAMMFASSUNG_PK Cost: 1  Cardinality: 1  						
										24 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_SERIEN Cost: 1  Bytes: 62  Cardinality: 1  								
											23 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.SERIEN_PK Cost: 1  Cardinality: 1  							
									27 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_TITEL Cost: 1  Bytes: 45  Cardinality: 1  									
										26 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.TITEL_PK Cost: 1  Cardinality: 1  								
								30 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.DOK_TITEL Cost: 1  Bytes: 45  Cardinality: 1  										
									29 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.TITEL_PK Cost: 1  Cardinality: 1  									
							33 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.PAR_PROGRAMMTYP Cost: 1  Bytes: 65  Cardinality: 1  											
								32 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.PROGRAMMTYP_PK Cost: 1  Cardinality: 1  										
						36 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.PAR_PRODUKTIONSFORMAT Cost: 1  Bytes: 65  Cardinality: 1  												
							35 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.PRODUKTIONSFORMAT_PK Cost: 1  Cardinality: 1  											
					39 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.PAR_SPRACHE Cost: 1  Bytes: 65  Cardinality: 1  													
						38 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.SPRACHE_PK Cost: 1  Cardinality: 1  												
				42 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.PAR_SPRACHE Cost: 1  Bytes: 65  Cardinality: 1  														
					41 INDEX UNIQUE SCAN INDEX (UNIQUE) CEDBA.SPRACHE_PK Cost: 1  Cardinality: 1  													
			44 INDEX RANGE SCAN INDEX CEDBA.IX_R_MAN_CONTENT_MERG_MAND Cost: 1  Bytes: 52  Cardinality: 2  															
		47 TABLE ACCESS BY INDEX ROWID TABLE CEDBA.REF_CONTENT_MANDANT Cost: 1  Bytes: 78  Cardinality: 2  																
			46 INDEX RANGE SCAN INDEX CEDBA.IX_R_MAN_CONTENT_MERG_MAND Cost: 1  Cardinality: 1  															


But why is there this heavy difference?

Thanks for your help.

regards

eigeneachse
Re: Performance issues between Databases [message #355788 is a reply to message #355736] Mon, 27 October 2008 09:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
How exactly are you "mirroring"?
Post the statements that used to collect statistics.
Post the OS versions.

[Updated on: Mon, 27 October 2008 09:30]

Report message to a moderator

Re: Performance issues between Databases [message #355791 is a reply to message #355788] Mon, 27 October 2008 09:53 Go to previous messageGo to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
Hi Mahesh Rajendran,
i am mirroring via exp and imp.
The OS ist Win2k3 R2.
Statistics were generated via
exec dbms_stats.gather_schema_stats('MYSCHEMA', options=>'GATHER AUTO');


regards
eigeneachse
Re: Performance issues between Databases [message #355795 is a reply to message #355791] Mon, 27 October 2008 10:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
That is a big difference.
Exp/Imp might be recreating the indexes.
Your statement will collect stats only on the tables and not indexes.
You can see that from the plan.
In prod CARD is high and in test CARD is low.
In prod try this.

exec dbms_stats.gather_schema_stats('MYSCHEMA',Method_opt=>'FOR ALL INDEXED COLUMNS SIZE 250',cascade=>true);

This is just a baseline.
If it works, change it back to AUTO and just include indexes by using CASCADE=>TRUE.

[Updated on: Mon, 27 October 2008 10:18]

Report message to a moderator

Re: Performance issues between Databases [message #355829 is a reply to message #355736] Mon, 27 October 2008 16:14 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I once read somewhere a very good piece of advice:

Quote:
99% of the time, when the cost based optimizer gets it wrong, it is because it incorrectly computed the expected cardinality of one or more steps. Get the cardinality right, and your plan will be right.

Of course getting the cardinality right can sometimes be difficult as often the optimizer does not have any real data to do on and must use default guesses. the rest of the discussion went on to illustrate many of these situations and defaults.

I would also say that:

Quote:
90% of the time when I have been presented with this exact problem, the issue has been that the presenter was not doing something teh same way, or was not telling us something because they figured would not make a difference. That, or they were out right doing something wrong.


Not that there was ever any intent to decieve.

Please do the following:

Quote:
1) compare all database parameters via v$parameter.
2) compare all indexes. Pay attention to column order and index uniqueness
3) collect statistics on both systems the same way, at the same time. You should have one script you are using on both systems.
4) take apart the sql statement. try to see where it falls apart. If you don't know what I mean by this, then post the sql and I will show you.

Particularly with #4, you should be able to find right away if there is a difference bewteen the databases.

Additionally, if you have access to a tool like TOAD (or something simliar), you should be able to do a schema compare via the tool and save yourself a lot of time.

Good luck, Kevin
Re: Performance issues between Databases [message #355854 is a reply to message #355829] Tue, 28 October 2008 01:18 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The only real differences appear to be a few full table scans on the inner-most tables.

- Check the indexes on those tables that are full scanning
- Compare the statistics on all indexes, especially LEAF_BLOCKS, CLUSTERING_FACTOR, and DISTINCT_KEYS.

When you say TEST is much faster, is it faster to return the FIRST row? Or all rows? The Indexed Nested Loops joins will return the first row fastest, but the full-scans might make up that time over the duration of the query.

Try adding a /*+FIRST_ROWS*/ hint to the PROD query. This will force it to use indexed nested loops if it can. If it is using indexed nested loops and is STILL slower, then I would bet your indexes are different, or possibly badly fragmented.

Ross Leishman
Re: Performance issues between Databases [message #355860 is a reply to message #355795] Tue, 28 October 2008 02:37 Go to previous messageGo to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
Mahesh Rajendran wrote on Mon, 27 October 2008 16:18
That is a big difference.
Exp/Imp might be recreating the indexes.
Your statement will collect stats only on the tables and not indexes.
You can see that from the plan.
In prod CARD is high and in test CARD is low.
In prod try this.

exec dbms_stats.gather_schema_stats('MYSCHEMA',Method_opt=>'FOR ALL INDEXED COLUMNS SIZE 250',cascade=>true);

This is just a baseline.
If it works, change it back to AUTO and just include indexes by using CASCADE=>TRUE.



Hi Mahesh,
after this action there is no difference in the explain plans.

regards

eigeneachse
Re: Performance issues between Databases [message #355868 is a reply to message #355854] Tue, 28 October 2008 03:42 Go to previous messageGo to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
rleishman wrote on Tue, 28 October 2008 07:18

- Check the indexes on those tables that are full scanning
- Compare the statistics on all indexes, especially LEAF_BLOCKS, CLUSTERING_FACTOR, and DISTINCT_KEYS.



Hi rleishman,
they are at the same values.

Further ideas?

regards

eigeneachse
Re: Performance issues between Databases [message #355903 is a reply to message #355868] Tue, 28 October 2008 07:10 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Run the following on BOTH databases and show BOTH sets of results.
select column_name, column_position
from   user_ind_columns
where  index_name = 'IX_MANDANT_TITELFASSUNG_MND'


select INDEX_NAME, UNIQUENESS, COMPRESSION, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR, DISTINCT_KEYS, STATUS, NUM_ROWS
from user_indexes
where index_name = 'IX_MANDANT_TITELFASSUNG_MND'


select *
from user_tab_histograms
where table_name = 'REF_MANDANT_TITELFASSUNG'


Ross Leishman
Re: Performance issues between Databases [message #356319 is a reply to message #355736] Thu, 30 October 2008 05:26 Go to previous messageGo to next message
eigeneachse
Messages: 37
Registered: July 2008
Member
Hi@all,
first i want to thank you all for your suggestions.
I have stopped the import from the prod database into the test db an now the explain plans are the same. I think because every table is now in sga an this is the best plan for the cbo.
But the execution time difference is still there.
In prod the querie takes 22 secs in test 2 secs.

Have you any ideas what else could be the difference.

I looked for the wait events in the prod db an the greatest wait event is "CPU Time". Any hints?


regards

eigeneachse
Re: Performance issues between Databases [message #356347 is a reply to message #356319] Thu, 30 October 2008 08:06 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Please post the query for comment. This is a pretty long thread to not have seen and "Problem Code" yet.

Also, I believe you now need to identify where in the query the cost is being expended. This will take you about one hour. Take your query and build it incrementally and run each piece one at a time noting how long each addition adds to your execution time, and how the plan changes in each case.

If you do not know what I mean, I will start you off once you post the query.

Kevin
Previous Topic: Thread 1 cannot allocate new log, sequence 8823--Checkpoint not complete
Next Topic: Insert Oddly Alters Explain Plan
Goto Forum:
  


Current Time: Thu Dec 08 12:45:43 CST 2016

Total time taken to generate the page: 0.17178 seconds