Home » RDBMS Server » Performance Tuning » Merge join Cartesian and Buffer Sort (Oracle 10.2.0.1, Solaris 10)
Merge join Cartesian and Buffer Sort [message #378528] Tue, 30 December 2008 20:50 Go to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!

I've got a statement, simple query, but it's Merge join Cartesian and buffer sort which took ~ 56(s) for elapse time.

ccs_admin@VNP> set autotrace traceonl
ccs_admin@VNP> set autotrace traceonly
ccs_admin@VNP> SELECT COUNT (1)
  2    FROM (SELECT a.somay, b.nguoi_cn, c.ten_dv
  3            FROM ccs_tgg.danhba_dds_pttb a,
  4                 ccs_tgg.khachhangs_pttb b,
  5                 ccs_tgg.donvi_qls c
  6           WHERE b.ma_nv IS NULL
  7             AND a.ma_kh = b.ma_kh
  8             AND b.donviql_id = c.donviql_id
  9             AND a.somay IN (SELECT e.so_tb
 10                               FROM ccs_common.thue_bao e
 11                              WHERE e.loai = '1' AND e.so_tb = a.somay))
 12  /


Execution Plan
----------------------------------------------------------
Plan hash value: 2068641072

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

| Id  | Operation                     | Name                    | Rows  | Bytes
| Cost (%CPU)| Time
    | Inst   |IN-OUT|

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

|   0 | SELECT STATEMENT              |                         |     1 |    90
|    88   (2)| 00:00
:02 |        |      |

|   1 |  SORT AGGREGATE               |                         |     1 |    90
|            |
    |        |      |

|   2 |   NESTED LOOPS                |                         |     1 |    90
|    88   (2)| 00:00
:02 |        |      |

|   3 |    NESTED LOOPS               |                         |     1 |    68
|    87   (2)| 00:00
:02 |        |      |

|   4 |     MERGE JOIN CARTESIAN      |                         |     1 |    39
|    85   (2)| 00:00
:02 |        |      |

|   5 |      INDEX FULL SCAN          | DVQLTB_PK               |     1 |    13
|     0   (0)| 00:00
:01 |        |      |

|   6 |      BUFFER SORT              |                         | 10181 |   258K
|    85   (2)| 00:00
:02 |        |      |

|   7 |       TABLE ACCESS FULL       | DANHBA_DDS_PTTB         | 10181 |   258K
|    85   (2)| 00:00
:02 |        |      |

|   8 |     REMOTE                    | THUE_BAO                |     1 |    29
|     2   (0)| 00:00
:01 | DB_2_~ | R->S |

|*  9 |    TABLE ACCESS BY INDEX ROWID| KHACHHANGS_PTTB         |     1 |    22
|     1   (0)| 00:00
:01 |        |      |

|* 10 |     INDEX UNIQUE SCAN         | PK_MA_KH_KHACHHANG_PTTB |     1 |
|     0   (0)| 00:00
:01 |        |      |

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



Predicate Information (identified by operation id):
---------------------------------------------------

   9 - filter("B"."MA_NV" IS NULL AND "B"."DONVIQL_ID"="C"."DONVIQL_ID")
  10 - access("A"."MA_KH"="B"."MA_KH")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   8 - SELECT "SO_TB","LOAI" FROM "SUBADMIN"."THUE_BAO" "A" WHERE "LOAI"='1' AND
 :1="SO_TB" (accessi
ng

       'DB_2_NSA.REGRESS.RDBMS.DEV.US.ORACLE.COM' )



Statistics
----------------------------------------------------------
          8  recursive calls
          1  db block gets
     139092  consistent gets
          0  physical reads
        380  redo size
        210  bytes sent via SQL*Net to client
        247  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

ccs_admin@VNP>


tkprof:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.12       0.14          0          0          1           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1     13.90      56.63          0     139090          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     14.02      56.77          0     139090          1           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 534  


Original trace file:
SELECT COUNT (1)
  FROM (SELECT a.somay, b.nguoi_cn, c.ten_dv
          FROM ccs_tgg.danhba_dds_pttb a,
               ccs_tgg.khachhangs_pttb b,
               ccs_tgg.donvi_qls c
         WHERE b.ma_nv IS NULL
           AND a.ma_kh = b.ma_kh
           AND b.donviql_id = c.donviql_id
           AND a.somay IN (SELECT e.so_tb
                             FROM ccs_common.thue_bao e
                            WHERE e.loai = '1' AND e.so_tb = a.somay))
END OF STMT
PARSE #2:c=0,e=1669,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1552676408955
BINDS #2:
EXEC #2:c=0,e=107,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1552676409232
*** 2008-12-31 08:39:51.383
FETCH #2:c=13900000,e=56630432,p=0,cr=139090,cu=0,mis=0,r=1,dep=0,og=1,tim=1552733050154
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=139090 pr=0 pw=0 time=56630420 us)'
STAT #2 id=2 cnt=1648 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=139090 pr=0 pw=0 time=18066102 us)'
STAT #2 id=3 cnt=69354 pid=2 pos=1 obj=0 op='NESTED LOOPS  (cr=380 pr=0 pw=0 time=54318838 us)'
STAT #2 id=4 cnt=94986 pid=3 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=380 pr=0 pw=0 time=200607 us)'
STAT #2 id=5 cnt=9 pid=4 pos=1 obj=390872 op='INDEX FULL SCAN DVQLTB_PK (cr=1 pr=0 pw=0 time=62 us)'
STAT #2 id=6 cnt=94986 pid=4 pos=2 obj=0 op='BUFFER SORT (cr=379 pr=0 pw=0 time=105613 us)'
STAT #2 id=7 cnt=10554 pid=6 pos=1 obj=390846 op='TABLE ACCESS FULL DANHBA_DDS_PTTB (cr=379 pr=0 pw=0 time=58 us)'
STAT #2 id=8 cnt=69354 pid=3 pos=2 obj=0 op='REMOTE  THUE_BAO (cr=0 pr=0 pw=0 time=55297623 us)'
STAT #2 id=9 cnt=1648 pid=2 pos=2 obj=390939 op='TABLE ACCESS BY INDEX ROWID KHACHHANGS_PTTB (cr=138710 pr=0 pw=0 time=1007962 us)'
STAT #2 id=10 cnt=69354 pid=9 pos=1 obj=390940 op='INDEX UNIQUE SCAN PK_MA_KH_KHACHHANG_PTTB (cr=69356 pr=0 pw=0 time=506550 us)'
=====================
PARSING IN CURSOR #3 len=34 dep=0 uid=534 oct=42 lid=534 tim=1552882940326 hv=4237823014 ad='c91edb58'
alter session set sql_trace=false
END OF STMT
PARSE #3:c=0,e=2637,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1552882940321
BINDS #3:
EXEC #3:c=0,e=540,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1552882940945
$ ls -l


In above statistic, I think of buffer sort maybe a problem, -- buffer sort usually appears with Merge join Cartesian. But, with the original trace file, I realized Nested Loop caused the problem. With 139092 consistent get, the statement must be rewrite. How will I rewrite it for the best result?

May you clarify more?

Thank you!
Re: Merge join Cartesian and Buffer Sort [message #378553 is a reply to message #378528] Tue, 30 December 2008 23:25 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Oracle thinks that ccs_tgg.donvi_qls contains only one row, so it thinks it is harmless to perform a cartesian join to another table. According to your trace, there are 9 rows so the cartesian join is not all that harmless. You should re-calculate statistics on the table - that should stop the cartesian join.

The more likely culprit is the REMOTE table ccs_common.thue_bao.

Oracle is performing a return trip to the remote database 69354 times - that's a fair bit.

How many rows are there in ccs_common.thue_bao?

If there are less than (say) 1 million, you should be able to structure it so that it goes over only ONCE and gets every row. Try rewriting the sub-query thus:
AND a.somay IN (
    SELECT e.so_tb
    FROM ccs_common.thue_bao e
    WHERE e.loai = '1')

What you are looking for is for the NESTED LOOPS join to the REMOTE table to be replaced with a HASH JOIN.

Ross Leishman
Re: Merge join Cartesian and Buffer Sort [message #378605 is a reply to message #378553] Wed, 31 December 2008 01:36 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you for your reply!

Very useful to improve this statement. Yes, I though of CCS_COMMON.THUE_BAO a lot of time today. This is a view, retrieve data from another database, table THUE_BAO which contains >20 million rows

sys@RATING> select count(1) from subadmin.thue_bao
  2  ;

  COUNT(1)
----------
  23756379

sys@RATING>


In the morning, I tried to replace IN by EXISTS operator, but it's seems that nothing improvement. ~54(s) vs 51(s). Of course, the master table is already gathered statistics.

And I am thinking more..

[Updated on: Wed, 31 December 2008 01:38]

Report message to a moderator

Re: Merge join Cartesian and Buffer Sort [message #378764 is a reply to message #378605] Thu, 01 January 2009 06:30 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
With such a large table on a remote database, you have a big problem.

You could try the DRIVING_SITE hint (do a search), although you may have trouble getting it to work since the remote table is referenced by a view.

Your best chance would be to replicate that remote table locally; ie. keep a local copy up to date using a Materialised View or maybe Oracle Streams.

Ross Leishman
Previous Topic: Sql query performance
Next Topic: SQL/PL/SQL Tunning Hints
Goto Forum:
  


Current Time: Sat Dec 10 05:19:36 CST 2016

Total time taken to generate the page: 0.28762 seconds