Re: Oracle db optimizer

From: Jack <none_at_invalid.com>
Date: Mon, 19 May 2014 15:59:52 +0300
Message-ID: <cfnev.40785$SH2.11045_at_uutiset.elisa.fi>

"Jack" wrote in message news:sshev.40752$SH2.19490_at_uutiset.elisa.fi...

Issue: Query in production 9.2.08 b´atabase run for ever (>5min, perhaps 20min?), in devolepment 11.2 xe db it runs in 3sec.

It is complex view/function system.
9db not analyzed.

/*+ ALL_ROWS */ hint does not fix it this time.

Well. yes, I have proposed to updrade db, perhaps some day.

---

9.db very slow
19 SELECT STATEMENT
18 SORT [AGGREGATE]
17 NESTED LOOPS
15 NESTED LOOPS [OUTER]
13 NESTED LOOPS
10 NESTED LOOPS
7 NESTED LOOPS
4 NESTED LOOPS

2  B.S TABLE ACCESS [BY INDEX ROWID]
1  B.S_UK INDEX [RANGE SCAN]
3  B.P_PK INDEX [UNIQUE SCAN]
6  B.T TABLE ACCESS [BY INDEX ROWID]
5  B.T_PK INDEX [RANGE SCAN]
9  B.P TABLE ACCESS [BY INDEX ROWID]
8  B.P_UK INDEX [RANGE SCAN]
12  B.P TABLE ACCESS [BY INDEX ROWID]
11  B.PANE_UK INDEX [RANGE SCAN]

14 B.A_PK INDEX [UNIQUE SCAN]
16 B.T_PK INDEX [RANGE SCAN] All OK 11.2
15 SELECT STATEMENT
14 SORT [AGGREGATE]
13 NESTED LOOPS
11 NESTED LOOPS
9 NESTED LOOPS
7 NESTED LOOPS
5 HASH JOIN
3 NESTED LOOPS

1  B.S TABLE ACCESS [FULL]
2  B.P_PK INDEX [UNIQUE SCAN]
4  B.T_PK INDEX [RANGE SCAN]
6  B.P_PK INDEX [UNIQUE SCAN]
8  B.P_PK INDEX [UNIQUE SCAN]

10 B.T_UK INDEX [RANGE SCAN]
12 B.T TABLE ACCESS [BY INDEX ROWID]   Received on Mon May 19 2014 - 14:59:52 CEST