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  |
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 #355766 is a reply to message #355736] |
Mon, 27 October 2008 08:20  |
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 #355795 is a reply to message #355791] |
Mon, 27 October 2008 10:18 |
 |
Mahesh Rajendran
Messages: 10708
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 |
 |
Kevin Meade
Messages: 2103
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 |
rleishman
Messages: 3728
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 |
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 |
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 |
rleishman
Messages: 3728
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 |
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 |
|
Kevin Meade
Messages: 2103
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
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 02:39:40 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
