-
Strange Performance Issue on 10gR2
Hi,
I have this strange performance issue which I have been troubleshooting for a week and is getting not very far.
My users complain to me that all queries on this newly setup database is running much slower than their dev DB.
New Production DB
- is on a Sun box Solaris 10, 32 cores CPU, 32GB RAM, Sun storage
- Oracle 10gR2 64 bit 10.2.0.1.0 Standard Edition
- SGA_TARGET = 1.5GB
- PGA_AGGREGATE_TARGET = 1GB
- optimizer_mode = ALL_ROWS
Dev DB
- a normal MS Windows XP notebook
- Oracle 10gR2 10.2.0.1.0 Standard Edition
- SGA_TARGET = 270Mb
- PGA_AGGREGATE_TARGET = 89Mb
- optimizer_mode = ALL_ROWS
Dataset for the testing is exactly the same on both system as they were from the same source.
The query run on Dev DB took only 1min 40secs, while the same query run took 5+mins on Prod DB.
Explain plan and statistics are the same on both systems.
Below is the results taken from SQLPLUS with "set autotrace on" and "set timing on"
Prod DB:
18 rows selected.
Elapsed: 00:05:22.72
Execution Plan
----------------------------------------------------------
Plan hash value: 820129149
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 137M| 17G| | 12M (2)| 43:04:06 |
| 1 | SORT UNIQUE | | 137M| 17G| 37G| 8634K (46)| 28:46:49 |
| 2 | UNION-ALL | | | | | | |
| 3 | HASH GROUP BY | | 75M| 9G| 20G| 4782K (2)| 15:56:29 |
| 4 | MERGE JOIN CARTESIAN | | 75M| 9G| | 35273 (6)| 00:07:04 |
|* 5 | HASH JOIN | | 1559 | 213K| | 316 (5)| 00:00:04 |
| 6 | NESTED LOOPS | | 82 | 10414 | | 71 (5)| 00:00:01 |
|* 7 | HASH JOIN | | 82 | 8364 | | 68 (5)| 00:00:01 |
|* 8 | HASH JOIN | | 148 | 11544 | | 62 (4)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | FLIGHT_SCHEDULE_PROCESING | 148 | 6808 | | 42 (3)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | PERMIT_LICENSE | 355 | 11360 | | 19 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | USER_DETAILS | 520 | 12480 | | 5 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| AIRLINE_MASTER | 1 | 25 | | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | SYS_C006428 | 1 | | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | OPERATING_FLIGHT_SCHEDULE | 89240 | 1132K| | 243 (4)| 00:00:03 |
| 15 | BUFFER SORT | | 48714 | | | 2408K (2)| 08:01:43 |
| 16 | INDEX FAST FULL SCAN | ACTION_ID_FK | 48714 | | | 22 (5)| 00:00:01 |
| 17 | HASH GROUP BY | | 61M| 8165M| 16G| 3851K (2)| 12:50:21 |
| 18 | MERGE JOIN CARTESIAN | | 61M| 8165M| | 28231 (6)| 00:05:39 |
|* 19 | HASH JOIN | | 1255 | 171K| | 90 (5)| 00:00:02 |
| 20 | NESTED LOOPS | | 82 | 10414 | | 71 (5)| 00:00:01 |
|* 21 | HASH JOIN | | 82 | 8364 | | 68 (5)| 00:00:01 |
|* 22 | HASH JOIN | | 148 | 11544 | | 62 (4)| 00:00:01 |
|* 23 | TABLE ACCESS FULL | FLIGHT_SCHEDULE_PROCESING | 148 | 6808 | | 42 (3)| 00:00:01 |
|* 24 | TABLE ACCESS FULL | PERMIT_LICENSE | 355 | 11360 | | 19 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | USER_DETAILS | 520 | 12480 | | 5 (0)| 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID| AIRLINE_MASTER | 1 | 25 | | 1 (0)| 00:00:01 |
|* 27 | INDEX UNIQUE SCAN | SYS_C006428 | 1 | | | 0 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | MARKETING_FLIGHT_SCHEDULE | 8264 | 104K| | 19 (6)| 00:00:01 |
| 29 | BUFFER SORT | | 48714 | | | 1939K (2)| 06:28:00 |
| 30 | INDEX FAST FULL SCAN | ACTION_ID_FK | 48714 | | | 22 (5)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OFS"."SCHEDULE_NO"="PL"."SCHEDULE_NO")
7 - access("UD"."USER_ID"="PL"."ATMAN_USERID")
8 - access("FSP"."SCHEDULE_NO"="PL"."SCHEDULE_NO")
9 - filter("FSP"."SEASON_CODE"='NS0909')
10 - filter("PL"."PERMIT_OR_LICENSE"='L' AND "PL"."DDGO"='1')
13 - access("AM"."AIRLINE_ID"="FSP"."AIRLINE_ID")
19 - access("OFS"."SCHEDULE_NO"="PL"."SCHEDULE_NO")
21 - access("UD"."USER_ID"="PL"."ATMAN_USERID")
22 - access("FSP"."SCHEDULE_NO"="PL"."SCHEDULE_NO")
23 - filter("FSP"."SEASON_CODE"='NS0909')
24 - filter("PL"."PERMIT_OR_LICENSE"='L' AND "PL"."DDGO"='1')
27 - access("AM"."AIRLINE_ID"="FSP"."AIRLINE_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2007 consistent gets
0 physical reads
0 redo size
3008 bytes sent via SQL*Net to client
480 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
18 rows processed
What's next after 10g?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|