Home » RDBMS Server » Performance Tuning » Slow running query -Explain plan attached-pls help! (ORacle,11g,windows server2003)
| Slow running query -Explain plan attached-pls help! [message #531531] |
Wed, 16 November 2011 10:42  |
aviana
Messages: 106
Registered: July 2007
|
Senior Member |
|
|
Hi
I am creating a table from a view.
CREATE TABLE GEO_TB_Localities
TABLESPACE &table_ts
STORAGE(INITIAL 3M NEXT 500K PCTINCREASE 0)
AS
SELECT *
FROM GEO_V_Localities;
Th create table statement taking around 3and 1/2 hours roughly.
Select statement for view:
CREATE OR REPLACE VIEW GEO_V_Localities
(
locality_id, post_town_id, county_id,
county_name, post_town_name, locality_name, changed_date,
delivery_office_id, delivery_office_name,
route_id, route_name,
locality_default_code,
commercial_delivery_points,
residential_delivery_points
)
AS
SELECT loc.locality_id, dof.post_town_id, cou.county_id,
cou.name, pto.name, loc.name, loc.changed_date,
DECODE
(
-- if all buildings in locality are served by the same
-- delivery office, get this delivery office id,
-- else null as no default code can be defined
MIN (dof.outward_code),
MAX (dof.outward_code),
MIN (dof.delivery_office_id),
NULL
),
DECODE
(
-- if all buildings in locality are served by the same
-- delivery office, get this delivery office name,
-- else null as no default code can be defined
MIN (dof.outward_code),
MAX (dof.outward_code),
MIN (dof.name),
NULL
),
DECODE
(
MIN (rou.route_id),
MAX (rou.route_id),
MIN (rou.route_id),
NULL
),
DECODE
(
MIN (rou.route_id),
MAX (rou.route_id),
MIN (NVL(rou.automation_route_code, rou.name)),
NULL
),
DECODE
(
-- if all buildings in locality are served by the same route,
-- return delivery office outward code and route_id as sortcode
-- else if all buildings are served by routes of the same
-- delivery office,
-- return delivery office outward code as sortcode
-- else no default code can be defined
MIN (rou.route_id),
MAX (rou.route_id),
MIN (LPAD(dof.outward_code,3,'0') ||
LPAD (rou.route_id, 5, '0') || '2' || '0000'),
DECODE
(
MIN (dof.outward_code),
MAX (dof.outward_code),
MIN (LPAD(dof.outward_code,3,'0') ||
'00000' || '2' || '0000'),
NULL
)
),
SUM (NVL(bld.commercial_delivery_points, 0)),
SUM (NVL(bld.residential_delivery_points, 0))
FROM T_Counties cou,
T_Post_Towns pto,
T_Localities loc,
T_Buildings bld,
T_Thoroughfares str,
T_Routes rou,
T_Delivery_Offices dof
-- References to T_Thoroughfares(str) added by F. Dibley Version 1 Software April 11th 2002
WHERE cou.county_id = pto.county_id
AND pto.post_town_id = dof.post_town_id
AND str.thorfare_id = bld.thorfare_id
AND
(
loc.locality_id = bld.locality_id
OR
loc.locality_id = bld.secondary_locality_id
OR
loc.locality_id = str.locality_id
)
AND bld.route_id = rou.route_id
AND rou.delivery_office_id = dof.delivery_office_id
AND NVL(bld.invalid, 'N') = 'N'
AND NOT EXISTS
(
SELECT 1
FROM T_Delivery_offices dof_in,
T_Routes rou_in,
T_Buildings bld_in
WHERE dof_in.post_town_id = dof.post_town_id
AND dof_in.delivery_office_id = rou_in.delivery_office_id
AND rou_in.route_id = bld_in.route_id
AND DECODE
(
bld_in.secondary_locality_id,
NULL,
loc.secondary_locality_id,
bld_in.secondary_locality_id
)
IS NOT NULL
AND bld_in.locality_id = loc.locality_id
AND NVL(bld_in.invalid, 'N') = 'N'
)
GROUP BY loc.locality_id, dof.post_town_id, cou.county_id,
cou.name, pto.name, loc.name, loc.changed_date;
By going through the explain plan attached, can someone please tell where exactly it is going too slow?
Plan
CREATE TABLE STATEMENT ALL_ROWSCost: 996,292,096,796
46 LOAD AS SELECT
45 HASH GROUP BY Bytes: 143,338,734 Cardinality: 930,771
44 CONCATENATION
28 FILTER
18 NESTED LOOPS
16 NESTED LOOPS Cost: 4 Bytes: 154 Cardinality: 1
14 NESTED LOOPS Cost: 4 Bytes: 122 Cardinality: 1
11 NESTED LOOPS Cost: 3 Bytes: 111 Cardinality: 1
8 NESTED LOOPS Cost: 2 Bytes: 94 Cardinality: 1
5 NESTED LOOPS Cost: 1 Bytes: 72 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_BUILDINGS Cost: 0 Bytes: 31 Cardinality: 1
1 INDEX FULL SCAN INDEX AUTOMATION.BUILDING_2LOCALITY_I Cost: 0 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_ROUTES Cost: 1 Bytes: 41 Cardinality: 1
3 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.ROUTE_PK Cost: 0 Cardinality: 1
7 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_DELIVERY_OFFICES Cost: 1 Bytes: 22 Cardinality: 1
6 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.DELIVERY_OFFICE_PK Cost: 0 Cardinality: 1
10 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_POST_TOWNS Cost: 1 Bytes: 17 Cardinality: 1
9 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.POST_TOWN_PK Cost: 0 Cardinality: 1
13 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_COUNTIES Cost: 1 Bytes: 11 Cardinality: 1
12 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.COUNTY_PK Cost: 0 Cardinality: 1
15 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.LOCAL_PK Cost: 0 Cardinality: 1
17 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_LOCALITIES Cost: 0 Bytes: 32 Cardinality: 1
27 NESTED LOOPS
25 NESTED LOOPS Cost: 17 Bytes: 41 Cardinality: 1
23 NESTED LOOPS Cost: 16 Bytes: 33 Cardinality: 1
20 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_BUILDINGS Cost: 15 Bytes: 25 Cardinality: 1
19 INDEX RANGE SCAN INDEX AUTOMATION.BUILDING_LOCALITY_FK_I Cost: 3 Cardinality: 33
22 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_ROUTES Cost: 1 Bytes: 8 Cardinality: 1
21 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.ROUTE_PK Cost: 0 Cardinality: 1
24 INDEX UNIQUE SCAN INDEX (UNIQUE) AUTOMATION.DELIVERY_OFFICE_PK Cost: 0 Cardinality: 1
26 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_DELIVERY_OFFICES Cost: 1 Bytes: 8 Cardinality: 1
43 FILTER
41 HASH JOIN Cost: 325,360 Bytes: 8,588,281,331,476 Cardinality: 55,768,060,594
29 TABLE ACCESS FULL TABLE AUTOMATION.T_LOCALITIES Cost: 241 Bytes: 1,917,312 Cardinality: 59,916
40 HASH JOIN Cost: 16,057 Bytes: 115,019,160 Cardinality: 942,780
38 HASH JOIN Cost: 56 Bytes: 391,027 Cardinality: 4,297
36 HASH JOIN Cost: 38 Bytes: 23,350 Cardinality: 467
34 MERGE JOIN Cost: 19 Bytes: 3,556 Cardinality: 127
31 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_POST_TOWNS Cost: 2 Bytes: 2,176 Cardinality: 128
30 INDEX FULL SCAN INDEX AUTOMATION.POST_TOWN_COUNTY_FK_I Cost: 1 Cardinality: 127
33 SORT JOIN Cost: 17 Bytes: 286 Cardinality: 26
32 TABLE ACCESS FULL TABLE AUTOMATION.T_COUNTIES Cost: 16 Bytes: 286 Cardinality: 26
35 TABLE ACCESS FULL TABLE AUTOMATION.T_DELIVERY_OFFICES Cost: 18 Bytes: 10,362 Cardinality: 471
37 TABLE ACCESS FULL TABLE AUTOMATION.T_ROUTES Cost: 18 Bytes: 177,571 Cardinality: 4,331
39 TABLE ACCESS FULL TABLE AUTOMATION.T_BUILDINGS Cost: 15,995 Bytes: 29,456,727 Cardinality: 950,217
42 NESTED LOOPS
Pls let me know if any more information needed.
Thanks
[Updated on: Wed, 16 November 2011 10:59] Report message to a moderator |
|
|
|
|
|
| Re: Slow running query -Explain plan attached-pls help! [message #531552 is a reply to message #531531] |
Wed, 16 November 2011 13:54  |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
Try working on this part of he query:
-- Etc --
43 FILTER
41 HASH JOIN Cost: 325,360 Bytes: 8,588,281,331,476 Cardinality: 55,768,060,594
29 TABLE ACCESS FULL TABLE AUTOMATION.T_LOCALITIES Cost: 241 Bytes: 1,917,312 Cardinality: 59,916
40 HASH JOIN Cost: 16,057 Bytes: 115,019,160 Cardinality: 942,780
38 HASH JOIN Cost: 56 Bytes: 391,027 Cardinality: 4,297
36 HASH JOIN Cost: 38 Bytes: 23,350 Cardinality: 467
34 MERGE JOIN Cost: 19 Bytes: 3,556 Cardinality: 127
31 TABLE ACCESS BY INDEX ROWID TABLE AUTOMATION.T_POST_TOWNS Cost: 2 Bytes: 2,176 Cardinality: 128
30 INDEX FULL SCAN INDEX AUTOMATION.POST_TOWN_COUNTY_FK_I Cost: 1 Cardinality: 127
33 SORT JOIN Cost: 17 Bytes: 286 Cardinality: 26
32 TABLE ACCESS FULL TABLE AUTOMATION.T_COUNTIES Cost: 16 Bytes: 286 Cardinality: 26
35 TABLE ACCESS FULL TABLE AUTOMATION.T_DELIVERY_OFFICES Cost: 18 Bytes: 10,362 Cardinality: 471
37 TABLE ACCESS FULL TABLE AUTOMATION.T_ROUTES Cost: 18 Bytes: 177,571 Cardinality: 4,331
39 TABLE ACCESS FULL TABLE AUTOMATION.T_BUILDINGS Cost: 15,995 Bytes: 29,456,727 Cardinality: 950,217
42 NESTED LOOPS
[Updated on: Wed, 16 November 2011 13:55] by Moderator Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sun Nov 24 12:26:18 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
