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 Go to next message
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.
Re: slow query with MERGE JOIN CARTESIAN and LATCH cache buffer chains [message #407781 is a reply to message #407776] Thu, 11 June 2009 09:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You know what the problem is:
The are no statistics collected on tables MATCLO, FICDEP and FICNEG.


The CBO reckons (based on it's default stats probably) that there's only going to be a single row returned, and therefore it will be file to do a cartesian join to it.
Re: slow query with MERGE JOIN CARTESIAN and LATCH cache buffer chains [message #407783 is a reply to message #407781] Thu, 11 June 2009 09:57 Go to previous message
farenheiit
Messages: 31
Registered: June 2009
Location: Paris
Member
If the statistics are skewed, is it due to the dynamic_sampling ?
And what about the wait event on Latch cache buffer chains ?
Previous Topic: Performance Tuning in Count(DISTINCT
Next Topic: Dynamic_sampling and partitioning
Goto Forum:
  


Current Time: Sun Dec 04 00:16:37 CST 2016

Total time taken to generate the page: 0.12403 seconds