Home » RDBMS Server » Performance Tuning » slow query with MERGE JOIN CARTESIAN and LATCH cache buffer chains (10g)
| slow query with MERGE JOIN CARTESIAN and LATCH cache buffer chains [message #407776] |
Thu, 11 June 2009 09:00  |
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
|
Member |
|
|
Hi,
I'm working for a software editor.
One of our clients (in 10G R2) had a session blocked on the following query:
SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM VCHKCL X WHERE X.COMAR IN ('ICE') AND TRUNC(X.DATOP,'DD')<=TO_DATE('09062009','DDMMYYYY') AND TRUNC(X.DANEG,'DD')> TO_DATE('09062009','DDMMYYYY'))
VCHKCL is a view:
CREATE OR REPLACE VIEW VCHKCL
(COMAR,NCOCL,NFICL,NUCON,NUFDP,DANEG,COINF,COINC,
CNACT,CMECH,CAECH,CSOPT,MTSNA,QTCLO,COTSJ,DATOP,NUCPT,NUBIX,NUBCL)
AS
SELECT
M.COMAR,D.NUCON,D.NUFDP,M.NUCON,M.NUFDP,NVL(C.DAEOD,C.DATOP),N.COINF,D.COINC,
N.CNACT,N.CMECH,N.CAECH,N.CSOPT,N.MTSNA,M.QTCLO,A.COTSJ,NVL(N.DAEOD,N.DATOP),D.NUCPT,M.NUBIX,M.NUBCL
FROM
MATCLO M,
FICDEP D,
FICNEG N,
FICNEG C,
NATACF A
WHERE
D.NUBIX=M.NUBCL AND
D.NUFDP=M.NFICL AND
N.NUBIX=M.NUBCL AND
C.NUBIX=M.NUBIX AND
A.CNACT=N.CNACT;
The tables MATCLO, FICDEP et FICNEG are partionned by range on COMAR column.
The table NATACF is not partitionned.
When I looked into v$session_wait, I saw the session was blocked on a Latch event (LATCH cache buffer chains).
The explain plan is below:
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 339 (100)| | | |
| 1 | FILTER | | | | | | | |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 146 | 337 (0)| 00:00:02 | | |
| 4 | NESTED LOOPS | | 1 | 115 | 336 (0)| 00:00:02 | | |
| 5 | NESTED LOOPS | | 1 | 72 | 334 (1)| 00:00:02 | | |
| 6 | MERGE JOIN CARTESIAN | | 1 | 35 | 329 (0)| 00:00:02 | | |
| 7 | INDEX FULL SCAN | FICDEP1 | 1 | 26 | 315 (0)| 00:00:02 | | |
| 8 | BUFFER SORT | | 45493 | 399K| 14 (0)| 00:00:01 | | |
| 9 | INDEX FULL SCAN | NATACF1 | 45493 | 399K| 14 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE ALL | | 13 | 481 | 4 (0)| 00:00:01 | 1 | 45 |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID| FICNEG | 13 | 481 | 4 (0)| 00:00:01 | 1 | 45 |
| 12 | INDEX RANGE SCAN | FICNEG2 | 1 | | 4 (0)| 00:00:01 | 1 | 45 |
| 13 | PARTITION RANGE SINGLE | | 1 | 43 | 2 (0)| 00:00:01 | KEY | KEY |
| 14 | TABLE ACCESS BY LOCAL INDEX ROWID | MATCLO | 1 | 43 | 2 (0)| 00:00:01 | KEY | KEY |
| 15 | INDEX RANGE SCAN | MATCLO2 | 899 | | 1 (0)| 00:00:01 | KEY | KEY |
| 16 | TABLE ACCESS BY GLOBAL INDEX ROWID | FICNEG | 1 | 31 | 1 (0)| 00:00:01 | ROW L | ROW L |
| 17 | INDEX UNIQUE SCAN | FICNEG1 | 1 | | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------
The query is blocked endless.
The are no statistics collected on tables MATCLO, FICDEP and FICNEG. However the OPTIMZER_DYNAMIC_SAMPLING parameter is set to 2.
Statisctics are collected for NATACF table.
this query is issued by our software. I don't have the same problem when I execute the query with sqlplus on the same database. When the client kill the session and restart the process the session keep still blocked on the same query and with the same wait event.
If I modifiy the view description by adding the partition criterion, the query runs quickly and I obtain the following explain plan:
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| | | |
| 1 | FILTER | | | | | | | |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 92 | 6 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 78 | 5 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 1 | 49 | 4 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | | 1 | 28 | 3 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE SINGLE | | 20 | 220 | 1 (0)| 00:00:01 | KEY | KEY |
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID| FICDEP | 20 | 220 | 1 (0)| 00:00:01 | KEY | KEY |
| 9 | INDEX RANGE SCAN | FICDEP4 | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 10 | PARTITION RANGE SINGLE | | 1 | 17 | 1 (0)| 00:00:01 | KEY | KEY |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID| MATCLO | 1 | 17 | 1 (0)| 00:00:01 | KEY | KEY |
| 12 | INDEX RANGE SCAN | MATCLO2 | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 13 | TABLE ACCESS BY GLOBAL INDEX ROWID | FICNEG | 1 | 21 | 1 (0)| 00:00:01 | ROW L | ROW L |
| 14 | INDEX UNIQUE SCAN | FICNEG1 | 1 | | 1 (0)| 00:00:01 | | |
| 15 | TABLE ACCESS BY GLOBAL INDEX ROWID | FICNEG | 1 | 29 | 1 (0)| 00:00:01 | ROW L | ROW L |
| 16 | INDEX UNIQUE SCAN | FICNEG1 | 1 | | 1 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS BY INDEX ROWID | NATACF | 1 | 14 | 1 (0)| 00:00:01 | | |
| 18 | INDEX UNIQUE SCAN | NATACF1 | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------
Why I got this issue? Why Did the CBO make a cartesian product in the first explain plan ? Why the session was blocked on the Latch (cache buffer chains) wait event ? Is this due to wrong statistics ? What about the dynamic samplic degree?
I have to provide an explanation to our client and I still haven't succeed.
Thanks for helping.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Nov 25 16:28:45 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
