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  |
trantuananh24hg
Messages: 744
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  |
rleishman
Messages: 3728
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 |
trantuananh24hg
Messages: 744
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 |
rleishman
Messages: 3728
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
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 07:34:31 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
