OraFAQ Forum: Performance Tuning » Query takes 2.5 Hrs to run!!!!!!

Home » RDBMS Server » Performance Tuning » Query takes 2.5 Hrs to run!!!!!!

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query takes 2.5 Hrs to run!!!!!! [message #182300]

Fri, 14 July 2006 04:42

rp_in
Messages: 5
Registered: July 2006

Junior Member

The attached query does not return any data after running for 2.5 hours (query cancelled after 2.5 hours). The query relates to the Business Objects report.Please could someone investigate why the query runs for so long?

There are so many outer joins but we can't remove them coz our business does not approve us for doin so, neither we have any functional knowledge of the data flow.
Please suggest some other alternative.

When i tried to execute this query it is throwing following errors:

ERROR at line 1:
ORA-03113: end-of-file on communication channel

RROR at line 33:
ORA-01652: unable to extend temp segment by 1280 in tablespace SORT

------------------------------------------------------------------
SELECT
initcap(BPOWNER.REF_TBL_DLVY_PT.NM),
BPOWNER_GEN_TBL_TXT5.TXT_LINE,
BPOWNER_GEN_TBL_TXT5.TXT_DSCN,
BPOWNER.GEN_VIW_0000_ASSETS.ASSET_TYPE_ID,
BPOWNER_GEN_TBL_TXT20.TXT_DSCN,
BPOWNER.GEN_TBL_INSPECT.INSPECT_DATE,
BPOWNER.REF_TBL_DLVY_PT.DLVY_PT_ID,
(BPOWNER.GEN_VIW_ASSET_TESTS.TEST_INTERVAL * 365),
substr(BPOWNER.GEN_VIW_0000_ASSETS.ASSET_ID,3,255),
HISTORY_TEST_INTERVAL_TXT.TXT_LINE,
TEST_PLAN_TXT.TXT_DSCN,
BPOWNER.REF_TBL_TCQ.tcq_name,
BPOWNER.GEN_TBL_ADDR.ADDR_MNMC,
BPOWNER.GEN_TBL_ADDR.BLDG_NM,
initcap(BPOWNER.GEN_TBL_ADDR.ST_NM),
BPOWNER.GEN_TBL_ADDR.LINE4,
initcap(BPOWNER.GEN_TBL_ADDR.locy),
BPOWNER.GEN_TBL_ADDR.LINE6,
BPOWNER.GEN_TBL_ADDR.attr_01_val,
BPOWNER.GEN_TBL_ADDR.DSTCT_ID,
BPOWNER.GEN_VIW_0000_ASSETS.ASSET_ID,
BPOWNER.GEN_VIW_ASSET_TESTS.TEST_INTERVAL,
BPOWNER.GEN_VIW_0000_ASSETS.TEST_PRESSURE_BAR,
BPOWNER_GEN_TBL_TXT7.TXT_LINE,
BPOWNER_GEN_TBL_TXT7.TXT_DSCN,
to_char(BPOWNER.GEN_VIW_0000_ASSETS.DATE_MANU,'YYYY'),
BPOWNER.GEN_VIW_0000_ASSETS.DATE_MANU,
BPOWNER_GEN_TBL_TXT6.TXT_LINE,
BPOWNER_GEN_TBL_TXT6.TXT_DSCN,
BPOWNER.REF_TBL_DLVY_PT.MNMC
FROM
BPOWNER.REF_TBL_DLVY_PT,
BPOWNER.GEN_TBL_TXT BPOWNER_GEN_TBL_TXT5,
BPOWNER.REF_VIW_TEST_INTERVAL HISTORY_TEST_INTERVAL_TXT,
BPOWNER.REF_VIW_TEST_TYPE TEST_PLAN_TXT,
BPOWNER.REF_TBL_TCQ,
BPOWNER.GEN_TBL_ADDR,
BPOWNER.GEN_TBL_TXT BPOWNER_GEN_TBL_TXT7,
BPOWNER.GEN_TBL_TXT BPOWNER_GEN_TBL_TXT6,
BPOWNER.GEN_TBL_INSPECT,
BPOWNER.GEN_VIW_0000_ASSETS,
BPOWNER.GEN_TBL_TXT BPOWNER_GEN_TBL_TXT20,
BPOWNER.GEN_VIW_ASSET_TESTS
WHERE
( BPOWNER.REF_TBL_DLVY_PT.DLVY_PT_ID=BPOWNER.GEN_VIW_0000_ASSETS.DLVY_PT_ID )
AND ( BPOWNER.GEN_TBL_ADDR.ADDR_ID=BPOWNER.REF_TBL_DLVY_PT.ADDR_ID )
AND ( BPOWNER_GEN_TBL_TXT5.TXT_ID(+)=BPOWNER.GEN_VIW_0000_ASSETS.CAPACITY_ID )
AND ( BPOWNER_GEN_TBL_TXT6.TXT_ID(+)=BPOWNER.GEN_VIW_0000_ASSETS.INSTALL_ID )
AND ( BPOWNER_GEN_TBL_TXT7.TXT_ID(+)=BPOWNER.GEN_VIW_0000_ASSETS.MFGR_ID )
AND ( BPOWNER_GEN_TBL_TXT7.SYS_BUS_UNT_ID = 2001 )
AND ( BPOWNER.GEN_TBL_INSPECT.TEST_SCHEDULE_TYPE_ID=HISTORY_TEST_INTERVAL_TXT.TXT_ID(+) )
AND ( TEST_PLAN_TXT.TXT_ID(+)=BPOWNER.GEN_VIW_ASSET_TESTS.TEST_TYPE_ID )
AND ( BPOWNER_GEN_TBL_TXT5.SYS_BUS_UNT_ID(+) =0 )
AND ( BPOWNER_GEN_TBL_TXT6.SYS_BUS_UNT_ID = 2001 )
AND ( BPOWNER.REF_TBL_TCQ.DLVY_PT_ID(+)=BPOWNER.REF_TBL_DLVY_PT.DLVY_PT_ID )
AND ( BPOWNER.REF_TBL_TCQ.SYS_BUS_UNT_ID(+) = 2001 )
AND ( BPOWNER_GEN_TBL_TXT5.TXT_TYP_ID(+)=100000902 )
AND ( BPOWNER_GEN_TBL_TXT6.TXT_TYP_ID(+)=100000908 )
AND ( BPOWNER_GEN_TBL_TXT7.TXT_TYP_ID(+)=100000903 )
AND ( BPOWNER_GEN_TBL_TXT20.TXT_TYP_ID(+)=100000900 )
AND ( BPOWNER_GEN_TBL_TXT20.TXT_ID(+)=BPOWNER.GEN_VIW_0000_ASSETS.ASSET_TYPE_ID )
AND ( BPOWNER_GEN_TBL_TXT20.SYS_BUS_UNT_ID = 2001 )
AND ( BPOWNER.GEN_TBL_INSPECT.SYS_BUS_UNT_ID = 2001 )
AND ( BPOWNER.REF_TBL_DLVY_PT.SYS_BUS_UNT_ID = 2001 )
AND ( BPOWNER.GEN_TBL_ADDR.SYS_BUS_UNT_ID = 2001 )
AND ( BPOWNER.GEN_VIW_0000_ASSETS.SYS_BUS_UNT_ID = 2001 )
AND ( BPOWNER.GEN_VIW_0000_ASSETS.ASSET_ID=BPOWNER.GEN_VIW_ASSET_TESTS.ASSET_ID(+)
and BPOWNER.GEN_VIW_0000_ASSETS.ASSET_TYPE_ID=BPOWNER.GEN_VIW_ASSET_TESTS.ASSET_TYPE_ID(+)
and BPOWNER.GEN_VIW_0000_ASSETS.INSP_TYPE_ID=BPOWNER.GEN_VIW_ASSET_TESTS.TEST_TYPE_ID(+) )
AND ( BPOWNER.GEN_VIW_ASSET_TESTS.SYS_BUS_UNT_ID = 2001 )
AND ( BPOWNER.GEN_VIW_ASSET_TESTS.ASSET_ID=BPOWNER.GEN_TBL_INSPECT.ASSET_ID(+)
and BPOWNER.GEN_VIW_ASSET_TESTS.ASSET_TYPE_ID=BPOWNER.GEN_TBL_INSPECT.ASSET_TYPE_ID(+)
and BPOWNER.GEN_VIW_ASSET_TESTS.TEST_TYPE_SCHEDULE_ID=BPOWNER.GEN_TBL_INSPECT.TEST_SCHEDULE_TYPE_ID(+) )
AND ( TEST_PLAN_TXT.SYS_BUS_UNT_ID = 2001 )
AND ( HISTORY_TEST_INTERVAL_TXT.SYS_BUS_UNT_ID = 2001 )
AND (
BPOWNER.GEN_VIW_0000_ASSETS.ASSET_TYPE_ID = 10000090001
AND ( BPOWNER.GEN_TBL_INSPECT.INSPECT_DATE is null or BPOWNER.GEN_TBL_INSPECT.INSPECT_DATE in
(select max(sq.INSPECT_DATE)
from BPOWNER.GEN_TBL_INSPECT sq where sq.ASSET_ID=BPOWNER.GEN_TBL_INSPECT.ASSET_ID) )
)

------------------------------------------------------------------

Report message to a moderator

Re: Query takes 2.5 Hrs to run!!!!!! [message #182311 is a reply to message #182300]

Fri, 14 July 2006 05:05

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Well, you certainly can remove some of the outer joins, because they are DEFINITELY not needed.

The outer joins to BPOWNER_GEN_TBL_TXT6, BPOWNER_GEN_TBL_TXT7, BPOWNER_GEN_TBL_TXT20, BPOWNER.GEN_VIW_0000_ASSETS, BPOWNER.GEN_VIW_ASSET_TESTS and TEST_PLAN_TXT can all be replaced with normal joins.

I can say this with certainty because the join conditions for all of these tables have a line of the format

<Table name>.<column_name> = <value>

without having an outer join marker on the column name. As any row returned because of the outer join would have a value of NULL for this column, the outer join row is being discarded by this clause.

The Sub-query at the bottom uses an IN where it can usefully use an =, as the subquery will only ever return one row:

...BPOWNER.GEN_TBL_INSPECT.INSPECT_DATE =
(select max(sq.INSPECT_DATE)
 from   BPOWNER.GEN_TBL_INSPECT sq 
 where  sq.ASSET_ID=BPOWNER.GEN_TBL_INSPECT.ASSET_ID)

Also, as this subquery will be executed for every row returned, make sure that there is an index on Gen_Tbl_Inspect(Asset_id, inspect_date).

Other than that, you need to check that the columns referenced are indexed on any tables that are reasonably large.

You seem confused about what happens when you run the query: First you state that it ran for 2.5 hours and you had to kill it, but 4 lines further down you say it gave an
'Unable to extend temp segment' error.

Which is it?

Report message to a moderator

Re: Query takes 2.5 Hrs to run!!!!!! [message #182312 is a reply to message #182300]

Fri, 14 July 2006 05:14

rp_in
Messages: 5
Registered: July 2006

Junior Member

When the user tried to run this query it took almost 2.5 Hr(can't say with surity), but when i tried ti execute the same query it threw error afte 1.5 hour(approx)

Report message to a moderator

Re: Query takes 2.5 Hrs to run!!!!!! [message #182396 is a reply to message #182300]

Fri, 14 July 2006 10:05

rp_in
Messages: 5
Registered: July 2006

Junior Member

I tried to get the explain plan for this query but even that did not help.
Attached is the explain Plan.Please suggest some more options that could optimize the query.
I've tried to alter the session in sql by running following two commands:

alter session set optimizer_max_permutations=5000;

alter session set db_file_multiblock_read_count=64;
-----------------------------------------------------------------
Explain plan for query:-----------------------

Plan Table
--------------------------------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | 691 | 891 | | |
| FILTER | | | | | | |
| NESTED LOOPS | | 1 | 691 | 891 | | |
| NESTED LOOPS OUTER | | 1 | 617 | 888 | | |
| NESTED LOOPS | | 1 | 599 | 887 | | |
| HASH JOIN | | 1 | 546 | 884 | | |
| HASH JOIN OUTER | | 2 | 950 | 491 | | |
| HASH JOIN | | 2 | 836 | 481 | | |
| TABLE ACCESS FULL |GEN_TBL_TXT | 49 | 2K| 9 | | |
| HASH JOIN | | 95 | 33K| 471 | | |
| TABLE ACCESS FULL |GEN_TBL_TXT | 49 | 2K| 9 | | |
| HASH JOIN | | 5K| 1M| 461 | | |
| HASH JOIN | | 11 | 1K| 67 | | |
| HASH JOIN | | 11 | 1K| 57 | | |
| HASH JOIN | | 11 | 990 | 47 | | |
| MERGE JOIN CARTESIAN | | 3 | 189 | 45 | | |
| TABLE ACCESS FULL |GEN_TBL_TXT | 1 | 27 | 9 | | |
| SORT JOIN | | 3 | 108 | 36 | | |
| TABLE ACCESS FULL |GEN_TBL_INSPECT | 3 | 108 | 36 | | |
| TABLE ACCESS FULL |GEN_TBL_TEST_PLAN | 19 | 513 | 1 | | |
| TABLE ACCESS FULL |GEN_TBL_TXT | 141 | 6K| 9 | | |
| TABLE ACCESS FULL |GEN_TBL_TXT | 141 | 3K| 9 | | |
| VIEW |GEN_VIW_0000_ASSETS | 13K| 1M| 393 | | |
| SORT UNIQUE | | 13K| 2M| 393 | | |
| UNION-ALL | | | | | | |
| TABLE ACCESS FULL |GEN_TBL_TANK | 4K| 1M| 23 | | |
| FILTER | | | | | | |
| TABLE ACCESS FULL |GEN_TBL_NETWORK | 2K| 280K| 7 | | |
| FILTER | | | | | | |
| TABLE ACCESS FULL |GEN_TBL_INSTALLATION | 2K| 279K| 7 | | |
| FILTER | | | | | | |
| TABLE ACCESS FULL |GEN_TBL_STORAGE_PARK | 3K| 367K| 7 | | |
| TABLE ACCESS FULL |GEN_TBL_TXT | 49 | 2K| 9 | | |
| VIEW |GEN_VIW_0000_ASSETS | 13K| 914K| 393 | | |
| SORT UNIQUE | | 13K| 2M| 393 | | |
| UNION-ALL | | | | | | |
| TABLE ACCESS FULL |GEN_TBL_TANK | 4K| 1M| 23 | | |
| FILTER | | | | | | |
| TABLE ACCESS FULL |GEN_TBL_NETWORK | 2K| 280K| 7 | | |
| FILTER | | | | | | |
| TABLE ACCESS FULL |GEN_TBL_INSTALLATION | 2K| 279K| 7 | | |
| FILTER | | | | | | |
| TABLE ACCESS FULL |GEN_TBL_STORAGE_PARK | 3K| 367K| 7 | | |
| TABLE ACCESS BY INDEX ROWID |REF_TBL_DLVY_PT | 27K| 1M| 3 | | |
| INDEX RANGE SCAN |REF_TBL_DLVY_PT_PK | 27K| | 2 | | |
| TABLE ACCESS BY INDEX ROWID |REF_TBL_TCQ | 5K| 91K| 1 | | |
| INDEX UNIQUE SCAN |PK_REF_TBL_TCQ | 5K| | | | |
| TABLE ACCESS BY INDEX ROWID |GEN_TBL_ADDR | 27K| 1M| 3 | | |
| INDEX RANGE SCAN |GEN_TBL_ADDR_PK | 27K| | 2 | | |
| SORT AGGREGATE | | 1 | 19 | | | |
| TABLE ACCESS FULL |GEN_TBL_INSPECT | 2 | 38 | 36 | | |
--------------------------------------------------------------------------------------------------------------------------

Report message to a moderator

Re: Query takes 2.5 Hrs to run!!!!!! [message #182436 is a reply to message #182396]

Fri, 14 July 2006 18:30

wagnerch
Messages: 58
Registered: July 2006

Member

rp_in wrote on Fri, 14 July 2006 11:05

I've tried to alter the session in sql by running following two commands:

alter session set optimizer_max_permutations=5000;
alter session set db_file_multiblock_read_count=64;

What do you believe these statements do for you ? It certainley isn't going to improve the query execution time, but it may mislead the optimizer in choose a plan that is even worse.

I think the fundamental problem is your query, and sometimes you have to break it back down to the fundamentals and start over.

Also, please try repasting the execution plan (without playing with the session variables) inside of a "code" tag (it is the button to the left of the font size).

Have you collected statistics on these tables?

Report message to a moderator

Re: Query takes 2.5 Hrs to run!!!!!! [message #182587 is a reply to message #182396]

Mon, 17 July 2006 03:16

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Have you checked that you've got the indexes I suggested and made the changes I highlighted?

Report message to a moderator

Previous Topic:	"Advice" on shared_pool and buffer cache size
Next Topic:	Advice Needed-Free space in tablespace for performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 13:16:27 CST 2024