Hi Gurus,
I am using Oracle 11gR1 on AIX6.1.
The optimizer_feature_enblable is 11.1.0.7.
For some reason we change the environment and we are now testing.
Many explain plan have change but we keep similar or get better performance.
For some of the queries, the explain have changed and is getting much longer to execute that the one on the old environment.
We can get this one for an example.
I am then trying to optimize the following query, which became longer.
I am not changing the value of the field 4 --> 4 to get no modifications on the environment.
update
FORMAT FR set FR.REQUEST = 4
where FR.REQUEST = 4
and exists
(select 'X'
from acc4g a4g
where FR.ACCOUNT_NUM = a4g.account_num
and FR.SEQ = a4g._seq
and FR.VERSION = a4g.version
and a4g.log is not null
and a4g.log = 'OK');
On the new environment the explain plan is :
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 3 | 144 | 9 (12)| 00:00:01 |
| 1 | UPDATE | FORMAT | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 3 | 144 | 9 (12)| 00:00:01 |
| 4 | SORT UNIQUE | | 3 | 54 | 4 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | ACC4G | 3 | 54 | 4 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | FORMAT_PK | 3 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| FORMAT | 1 | 30 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Copy paste with consolas police to get a better view.
The only way for me to get back the old environment's explain plan is to use NL_SJ but I have seen in the documentation that this hint is deprecated since 10g.
update /*+ INDEX (FR FORMATTINGREQUEST_AK2) */
FORMATTINGREQUEST FR set FR.REQUEST_STATUS = 4
where FR.REQUEST_STATUS = 4
and exists
(select /*+ NL_SJ INDEX(A4G ACC4GOB_INDEX)*/ 'X'
from acc4gob a4g
where FR.ACCOUNT_NUM = a4g.account_num
and FR.BILL_SEQ = a4g.bill_seq
and FR.BILL_VERSION = a4g.bill_version
and a4g.logimp is not null
and a4g.logimp = 'OK');
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 48 | 1369 (58)| 00:00:17 |
| 1 | UPDATE | FORMAT | | | | |
| 2 | NESTED LOOPS SEMI | | 1 | 48 | 1369 (58)| 00:00:17 |
| 3 | TABLE ACCESS BY INDEX ROWID| FORMAT | 9833K| 281M| 575 (0)| 00:00:07 |
|* 4 | INDEX RANGE SCAN | FORMAT_AK2 | 9833K| | 5 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| ACC4G | 1 | 18 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | ACC4G_INDEX | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Copy paste with consolas police to get a better view.
As i want to force oracle to use the nested loop semi which seems to be really faster.
the first one take 5000 sec et the hinted one is 448 sec.
--> If I keep the index but remove the NL_SJ it go throught a hash join and takes 4300 sec
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 48 | 653 (12)| 00:00:08 |
| 1 | UPDATE | FORMAT | | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 1 | 48 | 653 (12)| 00:00:08 |
|* 3 | TABLE ACCESS BY INDEX ROWID| ACC4G | 3 | 54 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | ACC4G_INDEX | 3 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| FORMAT | 9833K| 281M| 575 (0)| 00:00:07 |
|* 6 | INDEX RANGE SCAN | FORMAT_AK2 | 9833K| | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Copy paste with consolas police to get a better view.
It is really the Nested loop semi join explanation I am looking for
- Do you know why this hint has been deprecated ?
- Do you know how can I get something similar whitout the hint (another hint) ?
- Do you have a link, a documentation to see the algorithm used by nested loop ?
Thank you very much.
Matthieu
Edited by: 875039 on Jul 26, 2011 5:19 AM
Edited by: BluShadow on 26-Jul-2011 13:25
edited to add {noformat}
{noformat} tags around code/data/output.