Home » RDBMS Server » Performance Tuning » This query make much recursive call
This query make much recursive call [message #287704] Thu, 13 December 2007 03:55 Go to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!

Take a look to my query, it get much more time for desiring
SELECT 	a.ma_kh, 
	a.ten_tt, 
	a.ma_nv, 
	a.diachi_tt, 
	a.ms_thue, 
	a.so_gt, 
	b.ma_nv ||' - '|| b.ten_nv ten_nv, 
	a.sodaidien, 
	'' tt_chuyentuyen 
FROM 	CCS_BNH.khachhangs_112007 a, CCS_BNH.nhanvien_tcs b 
WHERE 	a.donviql_id = 1 
AND 	a.ma_nv = '01' 
AND 	a.ma_nv = b.ma_nv 
AND 	b.ma_bc = a.ma_bc 
And 	a.ma_kh not in (
		Select 	ma_kh 
		From CCS_BNH.lichsu_chuyentuyens) 
union 
SELECT 	a.ma_kh, 
	a.ten_tt, 
	a.ma_nv, 
	a.diachi_tt, 
	a.ms_thue, 
	a.so_gt, 
	b.ma_nv ||' - '|| b.ten_nv ten_nv, 
	a.sodaidien, 
	'Da chuyen tu '||ls_tuyen.ma_nv_cu||' toi '||ls_tuyen.ma_nv_moi tt_chuyentuyen 
FROM 	CCS_BNH.khachhangs_112007 a, CCS_BNH.nhanvien_tcs b,CCS_BNH.lichsu_chuyentuyens ls_tuyen 
WHERE 	a.donviql_id = 1 
AND 	ls_tuyen.ma_nv_moi = '01' 
AND 	a.ma_nv = b.ma_nv 
AND 	b.ma_bc = a.ma_bc 
And 	ls_tuyen.ma_kh=a.ma_kh
/


Traced it with statistic

Quote:


SQL> /

236 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1576584514

------------------------------------------------------------------------------
----------------------------------------------------------
| Id | Operation | Name | Rows | By
tes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 |
601 | 152 (7)| 00:00:02 | | | |
| 1 | SORT UNIQUE | | 5 |
601 | 152 (7)| 00:00:02 | | | |
| 2 | UNION-ALL | | |
| | | | | |
| 3 | NESTED LOOPS ANTI | | 4 |
476 | 145 (3)| 00:00:02 | | | |
|* 4 | HASH JOIN | | 4 |
436 | 144 (3)| 00:00:02 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | NHANVIEN_TCS | 8 |
176 | 3 (0)| 00:00:01 | | | |
|* 6 | INDEX RANGE SCAN | PK_NHANVIEN_TC | 8 |
| 1 (0)| 00:00:01 | | | |
|* 7 | TABLE ACCESS FULL | KHACHHANGS_112007 | 244 | 21
228 | 140 (2)| 00:00:02 | | | |
|* 8 | INDEX RANGE SCAN | IDX_LAN | 1 |
10 | 1 (0)| 00:00:01 | | | |
| 9 | PX COORDINATOR | | |
| | | | | |
| 10 | PX SEND QC (RANDOM) | :TQ10002 | 1 |
125 | 6 (17)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 11 | HASH JOIN | | 1 |
125 | 6 (17)| 00:00:01 | Q1,02 | PCWP | |
| 12 | PX RECEIVE | | 2 |
206 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 13 | PX SEND HASH | :TQ10001 | 2 |
206 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 14 | NESTED LOOPS | | 2 |
206 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 15 | PX BLOCK ITERATOR | | |
| | | Q1,01 | PCWC | |
|* 16 | TABLE ACCESS FULL | LICHSU_CHUYENTUYENS | 2 |
32 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 17 | TABLE ACCESS BY INDEX ROWID| KHACHHANGS_112007 | 1 |
87 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 18 | INDEX UNIQUE SCAN | PK_KH_112007 | 1 |
| 0 (0)| 00:00:01 | Q1,01 | PCWP | |
| 19 | BUFFER SORT | | |
| | | Q1,02 | PCWC | |
| 20 | PX RECEIVE | | 251 | 5
522 | 3 (0)| 00:00:01 | Q1,02 | PCWP | |
| 21 | PX SEND HASH | :TQ10000 | 251 | 5
522 | 3 (0)| 00:00:01 | | S->P | HASH |
| 22 | TABLE ACCESS FULL | NHANVIEN_TCS | 251 | 5
522 | 3 (0)| 00:00:01 | | | |
------------------------------------------------------------------------------
----------------------------------------------------------

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

4 - access("A"."MA_NV"="B"."MA_NV" AND "B"."MA_BC"="A"."MA_BC")
6 - access("B"."MA_NV"='01')
7 - filter("A"."MA_NV"='01' AND "A"."DONVIQL_ID"=1)
8 - access("A"."MA_KH"="MA_KH")
11 - access("A"."MA_NV"="B"."MA_NV" AND "B"."MA_BC"="A"."MA_BC")
16 - filter("LS_TUYEN"."MA_NV_MOI"='01')
17 - filter("A"."DONVIQL_ID"=1)
18 - access("LS_TUYEN"."MA_KH"="A"."MA_KH")


Statistics
----------------------------------------------------------
655 recursive calls
3 db block gets
1560 consistent gets
0 physical reads
664 redo size
21984 bytes sent via SQL*Net to client
549 bytes received via SQL*Net from client
17 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
236 rows processed




655 recursive calls, it's too much.

Report message to a moderator

Re: This query make much recursive call [message #287747 is a reply to message #287704] Thu, 13 December 2007 06:26 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
Just go through below link.
http://download.oracle.com/docs/cd/B14117_01/server.101/b10752/sqltrace.htm#1395

and again execute and check recursive calls .

Report message to a moderator

Re: This query make much recursive call [message #287773 is a reply to message #287704] Thu, 13 December 2007 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Performance Tuning Guide
Chapter 20 Using Application Tracing Tools
Section 20.4 Using the SQL Trace Facility and TKPROF
Subsection 20.4.4 Step 4: Interpreting TKPROF Output
Paragraph 20.4.4.5 Understanding Recursive Calls

Regards
Michel

[Doh! time finding the reference and writing all the stuff and Mohammad Taj already posted it]

[Updated on: Thu, 13 December 2007 07:19]

Report message to a moderator

Re: This query make much recursive call [message #287868 is a reply to message #287773] Thu, 13 December 2007 21:08 Go to previous message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you Mohammad and Michel!

Report message to a moderator

Previous Topic: Optimize large SQL query with multiple joins
Next Topic: Oracle Memory Leakage
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jun 28 11:16:22 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.