Home » RDBMS Server » Performance Tuning » start with and connect by (Oracle 11g R2 , RHEL4)
| start with and connect by [message #517314] |
Mon, 25 July 2011 06:33  |
 |
liliek
Messages: 13
Registered: July 2011
Location: Indonesia
|
Junior Member |
|
|
Hi guys,
i face this problem, at oracle 10g r2 i have a query start with.. connect by.. n this query running well in 10g, but after i migrating to oracle 11g r2 this query seems not running like in 10g.
in 10g this my query elapsed just 2-3 seconds, but in 11g it takes 6-7 minutes.
can any one help me?
is there any setting after this migrating that i should turning on ?
Thx
|
|
|
|
|
|
|
|
|
|
|
|
| Re: start with and connect by [message #517449 is a reply to message #517421] |
Tue, 26 July 2011 02:53  |
|
liliek
Messages: 13
Registered: July 2011
Location: Indonesia
|
Junior Member |
|
|
QUERY IN V10G AND 11G :
SET AUTOTRACE ON
1 SELECT BA.ACCOUNTNUMBER, BA.ACCOUNTNAME, BA.PARENTACCOUNTNUMBER, BA.ACCOUNTMUTATIONTYPE, BA.TYPEACCOUNTCODE
2 FROM BMS_ACCOUNT BA
3 START WITH BA.ACCOUNTNUMBER IN
4 (
5 SELECT NO_ACC ACCOUNTNUMBER
6 FROM SALDO_ACC SA, MST_ACCOUNTINGPERIOD AP
7 WHERE TO_DATE ('01/06/2011','DD/MM/YYYY') >= AP.START_DATE
8 AND TO_DATE ('01/06/2011','DD/MM/YYYY') <= AP.END_DATE
9 AND SA.TGL_SL >= AP.START_DATE
10 AND SA.TGL_SL <= TO_DATE ('01/06/2011','DD/MM/YYYY')
11 AND SA.CABANG = '031'
12 AND SA.AK_SL <> 0
13 GROUP BY NO_ACC
14 )
15 CONNECT BY PRIOR BA.PARENTACCOUNTNUMBER = BA.ACCOUNTNUMBER
16 GROUP BY BA.ACCOUNTNUMBER, BA.ACCOUNTNAME, BA.PARENTACCOUNTNUMBER, BA.ACCOUNTMUTATIONTYPE, BA.TYPEACCOUNTCODE;
47 rows selected.
--in 11g
Execution Plan
----------------------------------------------------------
Plan hash value: 3020414404
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2442 | 145K| 14 (8)| 00:00:01 |
| 1 | HASH GROUP BY | | 2442 | 145K| 14 (8)| 00:00:01 |
|* 2 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 3 | TABLE ACCESS FULL | BMS_ACCOUNT | 2442 | 145K| 13 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | HASH GROUP BY | | 1 | 45 | 567 (1)| 00:00:07 |
| 6 | MERGE JOIN | | 759 | 34155 | 566 (1)| 00:00:07 |
| 7 | SORT JOIN | | 113 | 3277 | 562 (0)| 00:00:07 |
|* 8 | TABLE ACCESS BY INDEX ROWID | SALDO_ACC | 113 | 3277 | 562 (0)| 00:00:07 |
|* 9 | INDEX RANGE SCAN | IDX_SA2 | 2394 | | 23 (0)| 00:00:01 |
|* 10 | SORT JOIN | | 7 | 112 | 4 (25)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | MST_ACCOUNTINGPERIOD | 7 | 112 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BA"."ACCOUNTNUMBER"=PRIOR "BA"."PARENTACCOUNTNUMBER")
filter( EXISTS (SELECT 0 FROM "MST_ACCOUNTINGPERIOD" "AP","SALDO_ACC" "SA" WHERE
"SA"."TGL_SL"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SA"."CABANG"='031' AND
"SA"."AK_SL"<>0 AND INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE") AND
"AP"."END_DATE">=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') GROUP BY "NO_ACC" HAVING
"NO_ACC"=:B1))
4 - filter("NO_ACC"=:B1)
8 - filter("SA"."AK_SL"<>0)
9 - access("SA"."CABANG"='031' AND "SA"."TGL_SL"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - access(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
filter(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
11 - filter("AP"."END_DATE">=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4439611 consistent gets
1384 physical reads
0 redo size
3924 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
4886 sorts (memory)
0 sorts (disk)
47 rows processed
--and in oracle 10g
Execution Plan
----------------------------------------------------------
Plan hash value: 3533202435
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 61 | 3 (34)| 00:00:01 |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | MAT_VIEW ACCESS BY INDEX ROWID | BMS_ACCOUNT | | | | |
|* 4 | HASH JOIN | | 23 | 713 | 1439 (1)| 00:00:18 |
| 5 | VIEW | VW_NSO_1 | 23 | 391 | 1434 (1)| 00:00:18 |
| 6 | HASH GROUP BY | | 23 | 1035 | 1434 (1)| 00:00:18 |
| 7 | MERGE JOIN | | 23 | 1035 | 1433 (1)| 00:00:18 |
| 8 | SORT JOIN | | 66 | 1914 | 1429 (1)| 00:00:18 |
|* 9 | MAT_VIEW ACCESS BY INDEX ROWID| SALDO_ACC | 66 | 1914 | 1429 (1)| 00:00:18 |
|* 10 | INDEX RANGE SCAN | IDX_SA2 | 7907 | | 51 (0)| 00:00:01 |
|* 11 | SORT JOIN | | 7 | 112 | 4 (25)| 00:00:01 |
|* 12 | MAT_VIEW ACCESS FULL | MST_ACCOUNTINGPERIOD | 7 | 112 | 3 (0)| 00:00:01 |
| 13 | INDEX FAST FULL SCAN | BMS_ACCOUNT_IX01 | 2347 |32858 | 4 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | | | | |
| 15 | BUFFER SORT | | | | | |
| 16 | CONNECT BY PUMP | | | | | |
| 17 | MAT_VIEW ACCESS BY INDEX ROWID | BMS_ACCOUNT | 1 | 61 | 2 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | BMS_ACCOUNT_IX01 | 1 | | 1 (0)| 00:00:01 |
|* 19 | MAT_VIEW ACCESS FULL | BMS_ACCOUNT | 1 | 61 | 2 (0)| 00:00:01 |
|* 20 | FILTER | | | | | |
| 21 | HASH GROUP BY | | 1 | 45 | 1434 (1)| 00:00:18 |
| 22 | MERGE JOIN | | 23 | 1035 | 1433 (1)| 00:00:18 |
| 23 | SORT JOIN | | 66 | 1914 | 1429 (1)| 00:00:18 |
|* 24 | MAT_VIEW ACCESS BY INDEX ROWID | SALDO_ACC | 66 | 1914 | 1429 (1)| 00:00:18 |
|* 25 | INDEX RANGE SCAN | IDX_SA2 | 7907 | | 51 (0)| 00:00:01 |
|* 26 | SORT JOIN | | 7 | 112 | 4 (25)| 00:00:01 |
|* 27 | MAT_VIEW ACCESS FULL | MST_ACCOUNTINGPERIOD | 7 | 112 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "MST_ACCOUNTINGPERIOD" "AP","SALDO_ACC" "SA" WHERE
"SA"."TGL_SL"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "SA"."CABANG"='031' AND
"SA"."AK_SL"<>0 AND INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE") AND
"AP"."END_DATE">=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') GROUP BY "NO_ACC" HAVING "NO_ACC"=:B1))
4 - access("BA"."ACCOUNTNUMBER"="ACCOUNTNUMBER")
9 - filter("SA"."AK_SL"<>0)
10 - access("SA"."CABANG"='031' AND "SA"."TGL_SL"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
11 - access(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
filter(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
12 - filter("AP"."END_DATE">=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
18 - access("BA"."ACCOUNTNUMBER"=NULL)
19 - access("BA"."ACCOUNTNUMBER"=NULL)
20 - filter("NO_ACC"=:B1)
24 - filter("SA"."AK_SL"<>0)
25 - access("SA"."CABANG"='031' AND "SA"."TGL_SL"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
26 - access(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
filter(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
27 - filter("AP"."END_DATE">=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
217 recursive calls
0 db block gets
2488 consistent gets
0 physical reads
0 redo size
3886 bytes sent via SQL*Net to client
502 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
48 rows processed
this i provide you the explain plan in 10g and 11g
in 11g already make it just in 64 seconds --> from 6-7 minutes with turning on ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL' and my query takes just 64seconds, but it still can be tune like 10g isn't?
|
|
|
|
|
|
| Re: start with and connect by [message #517476 is a reply to message #517452] |
Tue, 26 July 2011 04:53 |
|
liliek
Messages: 13
Registered: July 2011
Location: Indonesia
|
Junior Member |
|
|
SET AUTOTRACE ON
SELECT BA.ACCOUNTNUMBER, BA.ACCOUNTNAME, BA.PARENTACCOUNTNUMBER,
BA.ACCOUNTMUTATIONTYPE, BA.TYPEACCOUNTCODE
FROM BMS_ACCOUNT BA
START WITH BA.ACCOUNTNUMBER IN
(
SELECT NO_ACC ACCOUNTNUMBER
FROM SALDO_ACC SA, MST_ACCOUNTINGPERIOD AP
WHERE TO_DATE ('01/06/2011','DD/MM/YYYY') >= AP.START_DATE
AND TO_DATE ('01/06/2011','DD/MM/YYYY') <= AP.END_DATE
AND SA.TGL_SL >= AP.START_DATE
AND SA.TGL_SL <= TO_DATE ('01/06/2011','DD/MM/YYYY')
AND SA.CABANG = '031'
AND SA.AK_SL <> 0
GROUP BY NO_ACC
)
CONNECT BY PRIOR BA.PARENTACCOUNTNUMBER = BA.ACCOUNTNUMBER
GROUP BY BA.ACCOUNTNUMBER, BA.ACCOUNTNAME, BA.PARENTACCOUNTNUMBER,
BA.ACCOUNTMUTATIONTYPE, BA.TYPEACCOUNTCODE;
now oracle 11.2.0.1.0 previously oracle 10.2.0.1.0
thx
|
|
|
|
|
|
| Re: start with and connect by [message #517500 is a reply to message #517482] |
Tue, 26 July 2011 05:44 |
|
liliek
Messages: 13
Registered: July 2011
Location: Indonesia
|
Junior Member |
|
|
The query i run in oracle 10.2.0.1.0 and 11.2.0.1.0
SET AUTOTRACE ON
SELECT BA.ACCOUNTNUMBER, BA.ACCOUNTNAME, BA.PARENTACCOUNTNUMBER,
BA.ACCOUNTMUTATIONTYPE, BA.TYPEACCOUNTCODE
FROM BMS_ACCOUNT BA
START WITH BA.ACCOUNTNUMBER IN
(
SELECT NO_ACC ACCOUNTNUMBER
FROM SALDO_ACC SA, MST_ACCOUNTINGPERIOD AP
WHERE TO_DATE ('01/06/2011','DD/MM/YYYY') >= AP.START_DATE
AND TO_DATE ('01/06/2011','DD/MM/YYYY') <= AP.END_DATE
AND SA.TGL_SL >= AP.START_DATE
AND SA.TGL_SL <= TO_DATE ('01/06/2011','DD/MM/YYYY')
AND SA.CABANG = '031'
AND SA.AK_SL <> 0
GROUP BY NO_ACC
)
CONNECT BY PRIOR BA.PARENTACCOUNTNUMBER = BA.ACCOUNTNUMBER
GROUP BY BA.ACCOUNTNUMBER, BA.ACCOUNTNAME, BA.PARENTACCOUNTNUMBER,
BA.ACCOUNTMUTATIONTYPE, BA.TYPEACCOUNTCODE;
--in 11g
Execution Plan
----------------------------------------------------------
Plan hash value: 3020414404
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2442 | 145K| 14 (8)| 00:00:01 |
| 1 | HASH GROUP BY | | 2442 | 145K| 14 (8)| 00:00:01 |
|* 2 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 3 | TABLE ACCESS FULL | BMS_ACCOUNT | 2442 | 145K| 13 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | HASH GROUP BY | | 1 | 45 | 567 (1)| 00:00:07 |
| 6 | MERGE JOIN | | 759 | 34155 | 566 (1)| 00:00:07 |
| 7 | SORT JOIN | | 113 | 3277 | 562 (0)| 00:00:07 |
|* 8 | TABLE ACCESS BY INDEX ROWID | SALDO_ACC | 113 | 3277 | 562 (0)| 00:00:07 |
|* 9 | INDEX RANGE SCAN | IDX_SA2 | 2394 | | 23 (0)| 00:00:01 |
|* 10 | SORT JOIN | | 7 | 112 | 4 (25)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | MST_ACCOUNTINGPERIOD | 7 | 112 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BA"."ACCOUNTNUMBER"=PRIOR "BA"."PARENTACCOUNTNUMBER")
filter( EXISTS (SELECT 0 FROM "MST_ACCOUNTINGPERIOD" "AP","SALDO_ACC" "SA" WHERE
"SA"."TGL_SL"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SA"."CABANG"='031' AND
"SA"."AK_SL"<>0 AND INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE") AND
"AP"."END_DATE">=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') GROUP BY "NO_ACC" HAVING
"NO_ACC"=:B1))
4 - filter("NO_ACC"=:B1)
8 - filter("SA"."AK_SL"<>0)
9 - access("SA"."CABANG"='031' AND "SA"."TGL_SL"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
10 - access(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
filter(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
11 - filter("AP"."END_DATE">=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4439611 consistent gets
1384 physical reads
0 redo size
3924 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
4886 sorts (memory)
0 sorts (disk)
47 rows processed
--and in oracle 10g
Execution Plan
----------------------------------------------------------
Plan hash value: 3533202435
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 61 | 3 (34)| 00:00:01 |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | MAT_VIEW ACCESS BY INDEX ROWID | BMS_ACCOUNT | | | | |
|* 4 | HASH JOIN | | 23 | 713 | 1439 (1)| 00:00:18 |
| 5 | VIEW | VW_NSO_1 | 23 | 391 | 1434 (1)| 00:00:18 |
| 6 | HASH GROUP BY | | 23 | 1035 | 1434 (1)| 00:00:18 |
| 7 | MERGE JOIN | | 23 | 1035 | 1433 (1)| 00:00:18 |
| 8 | SORT JOIN | | 66 | 1914 | 1429 (1)| 00:00:18 |
|* 9 | MAT_VIEW ACCESS BY INDEX ROWID| SALDO_ACC | 66 | 1914 | 1429 (1)| 00:00:18 |
|* 10 | INDEX RANGE SCAN | IDX_SA2 | 7907 | | 51 (0)| 00:00:01 |
|* 11 | SORT JOIN | | 7 | 112 | 4 (25)| 00:00:01 |
|* 12 | MAT_VIEW ACCESS FULL | MST_ACCOUNTINGPERIOD | 7 | 112 | 3 (0)| 00:00:01 |
| 13 | INDEX FAST FULL SCAN | BMS_ACCOUNT_IX01 | 2347 |32858 | 4 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | | | | |
| 15 | BUFFER SORT | | | | | |
| 16 | CONNECT BY PUMP | | | | | |
| 17 | MAT_VIEW ACCESS BY INDEX ROWID | BMS_ACCOUNT | 1 | 61 | 2 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | BMS_ACCOUNT_IX01 | 1 | | 1 (0)| 00:00:01 |
|* 19 | MAT_VIEW ACCESS FULL | BMS_ACCOUNT | 1 | 61 | 2 (0)| 00:00:01 |
|* 20 | FILTER | | | | | |
| 21 | HASH GROUP BY | | 1 | 45 | 1434 (1)| 00:00:18 |
| 22 | MERGE JOIN | | 23 | 1035 | 1433 (1)| 00:00:18 |
| 23 | SORT JOIN | | 66 | 1914 | 1429 (1)| 00:00:18 |
|* 24 | MAT_VIEW ACCESS BY INDEX ROWID | SALDO_ACC | 66 | 1914 | 1429 (1)| 00:00:18 |
|* 25 | INDEX RANGE SCAN | IDX_SA2 | 7907 | | 51 (0)| 00:00:01 |
|* 26 | SORT JOIN | | 7 | 112 | 4 (25)| 00:00:01 |
|* 27 | MAT_VIEW ACCESS FULL | MST_ACCOUNTINGPERIOD | 7 | 112 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "MST_ACCOUNTINGPERIOD" "AP","SALDO_ACC" "SA" WHERE
"SA"."TGL_SL"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "SA"."CABANG"='031' AND
"SA"."AK_SL"<>0 AND INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE") AND
"AP"."END_DATE">=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') GROUP BY "NO_ACC" HAVING "NO_ACC"=:B1))
4 - access("BA"."ACCOUNTNUMBER"="ACCOUNTNUMBER")
9 - filter("SA"."AK_SL"<>0)
10 - access("SA"."CABANG"='031' AND "SA"."TGL_SL"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
11 - access(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
filter(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
12 - filter("AP"."END_DATE">=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
18 - access("BA"."ACCOUNTNUMBER"=NULL)
19 - access("BA"."ACCOUNTNUMBER"=NULL)
20 - filter("NO_ACC"=:B1)
24 - filter("SA"."AK_SL"<>0)
25 - access("SA"."CABANG"='031' AND "SA"."TGL_SL"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
26 - access(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
filter(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
27 - filter("AP"."END_DATE">=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
217 recursive calls
0 db block gets
2488 consistent gets
0 physical reads
0 redo size
3886 bytes sent via SQL*Net to client
502 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
48 rows processed
|
|
|
|
|
|
|
|
|
|
| Re: start with and connect by [message #517530 is a reply to message #517514] |
Tue, 26 July 2011 08:07 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
In other words it should look more like this:
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
That's readable.
|
|
|
|
| Re: start with and connect by [message #517655 is a reply to message #517530] |
Wed, 27 July 2011 02:36 |
|
liliek
Messages: 13
Registered: July 2011
Location: Indonesia
|
Junior Member |
|
|
--in 11.2.0.1.0
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3020414404
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2442 | 145K| 14 (8)| 00:00:01 |
| 1 | HASH GROUP BY | | 2442 | 145K| 14 (8)| 00:00:01 |
|* 2 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 3 | TABLE ACCESS FULL | BMS_ACCOUNT | 2442 | 145K| 13 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | FILTER | | | | | |
| 5 | HASH GROUP BY | | 1 | 45 | 189 (2)| 00:00:03 |
| 6 | MERGE JOIN | | 223 | 10035 | 188 (1)| 00:00:03 |
| 7 | SORT JOIN | | 33
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 957 | 184 (0)| 00:00:03 |
|* 8 | TABLE ACCESS BY INDEX ROWID | SALDO_ACC | 33 | 957 | 184 (0)| 00:00:03 |
|* 9 | INDEX RANGE SCAN | IDX_SA2 | 702 | | 9 (0)| 00:00:01 |
|* 10 | SORT JOIN | | 7 | 112 | 4 (25)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 11 | TABLE ACCESS FULL | MST_ACCOUNTINGPERIOD | 7 | 112 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BA"."ACCOUNTNUMBER"=PRIOR "BA"."PARENTACCOUNTNUMBER")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
filter( EXISTS (SELECT 0 FROM "MST_ACCOUNTINGPERIOD" "AP","SALDO_ACC" "SA" WHERE
"SA"."TGL_SL"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SA"."CABANG"='002' AND
"SA"."AK_SL"<>0 AND INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE") AND
"AP"."END_DATE">=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"AP"."START_DATE"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') GROUP BY "NO_ACC" HAVING
"NO_ACC"=:B1))
4 - filter("NO_ACC"=:B1)
8 - filter("SA"."AK_SL"<>0)
9 - access("SA"."CABANG"='002' AND "SA"."TGL_SL"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
10 - access(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
filter(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
11 - filter("AP"."END_DATE">=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE(' 2011-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--in 10.2.0.1.0
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3533202435
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 3 (34)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 61 | 3 (34)| 00:00:01 |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | MAT_VIEW ACCESS BY INDEX ROWID | BMS_ACCOUNT | | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 4 | HASH JOIN | | 5 | 155 | 301 (1)| 00:00:04 |
| 5 | VIEW | VW_NSO_1 | 5 | 85 | 296 (1)| 00:00:04 |
| 6 | HASH GROUP BY | | 5 | 225 | 296 (1)| 00:00:04 |
| 7 | MERGE JOIN | | 5 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
225 | 295 (1)| 00:00:04 |
| 8 | SORT JOIN | | 13 | 377 | 291 (0)| 00:00:04 |
|* 9 | MAT_VIEW ACCESS BY INDEX ROWID| SALDO_ACC | 13 | 377 | 291 (0)| 00:00:04 |
|* 10 | INDEX RANGE SCAN | IDX_SA2 | 1599 | | 12 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 11 | SORT JOIN | | 7 | 112 | 4 (25)| 00:00:01 |
|* 12 | MAT_VIEW ACCESS FULL | MST_ACCOUNTINGPERIOD | 7 | 112 | 3 (0)| 00:00:01 |
| 13 | INDEX FAST FULL SCAN | BMS_ACCOUNT_IX01 | 2347 |32858 | 4 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 15 | BUFFER SORT | | | | | |
| 16 | CONNECT BY PUMP | | | | | |
| 17 | MAT_VIEW ACCESS BY INDEX ROWID | BMS_ACCOUNT | 1 | 61 | 2 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | BMS_ACCOUNT_IX01 | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 (0)| 00:00:01 |
|* 19 | MAT_VIEW ACCESS FULL | BMS_ACCOUNT | 1 | 61 | 2 (0)| 00:00:01 |
|* 20 | FILTER | | | | | |
| 21 | HASH GROUP BY | | 1 | 45 | 296 (1)| 00:00:04 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 22 | MERGE JOIN | | 5 | 225 | 295 (1)| 00:00:04 |
| 23 | SORT JOIN | | 13 | 377 | 291 (0)| 00:00:04 |
|* 24 | MAT_VIEW ACCESS BY INDEX ROWID | SALDO_ACC | 13 | 377 | 291 (0)| 00:00:04 |
|* 25 | INDEX RANGE SCAN | IDX_SA2 | 1599 | | 12 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 26 | SORT JOIN | | 7 | 112 | 4 (25)| 00:00:01 |
|* 27 | MAT_VIEW ACCESS FULL | MST_ACCOUNTINGPERIOD | 7 | 112 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "MST_ACCOUNTINGPERIOD" "AP","SALDO_ACC" "SA" WHERE
"SA"."TGL_SL"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "SA"."CABANG"='002' AND
"SA"."AK_SL"<>0 AND INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE") AND
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"AP"."END_DATE">=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') GROUP BY "NO_ACC" HAVING
"NO_ACC"=:B1))
4 - access("BA"."ACCOUNTNUMBER"="ACCOUNTNUMBER")
9 - filter("SA"."AK_SL"<>0)
10 - access("SA"."CABANG"='002' AND "SA"."TGL_SL"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
hh24:mi:ss'))
11 - access(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
filter(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
12 - filter("AP"."END_DATE">=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"AP"."START_DATE"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
18 - access("BA"."ACCOUNTNUMBER"=NULL)
19 - access("BA"."ACCOUNTNUMBER"=NULL)
20 - filter("NO_ACC"=:B1)
24 - filter("SA"."AK_SL"<>0)
25 - access("SA"."CABANG"='002' AND "SA"."TGL_SL"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
hh24:mi:ss'))
26 - access(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
filter(INTERNAL_FUNCTION("SA"."TGL_SL")>=INTERNAL_FUNCTION("AP"."START_DATE"))
27 - filter("AP"."END_DATE">=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"AP"."START_DATE"<=TO_DATE('2011-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
66 rows selected.
by the way , the result looks same isn't?
and i just copied from the ORACLE.
Thx all n sorry if this code not readable
CM: added missing [/code] tag
[Updated on: Wed, 27 July 2011 03:45] by Moderator Report message to a moderator |
|
|
|
| Re: start with and connect by [message #517750 is a reply to message #517655] |
Wed, 27 July 2011 22:46 |
|
liliek
Messages: 13
Registered: July 2011
Location: Indonesia
|
Junior Member |
|
|
guys this is the trace output.
TKPROF: Release 11.2.0.2.0 - Development on Thu Jul 28 10:12:03 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: emf_ora_16253024.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
The following statements encountered a error during parse:
select ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType,
ba.typeAccountCode
from bms_accont ba
START WITH ba.accountnumber in
(
select no_acc accountnumber
from saldo_acc sa, mst_accountingPeriod ap
where to_date ('01/06/2011','DD/MM/YYYY') >= ap.start_Date and
to_date ('01/06/2011','DD/MM/YYYY') <= ap.end_Date and
sa.tgl_sl >= ap.start_date and
sa.tgl_sl <= to_date ('01/06/2011','DD/MM/YYYY') and
sa.cabang = '031' and sa.ak_sl <> 0
group by no_acc
)
CONNECT BY prior ba.PARENTACCOUNTNUMBER = ba.ACCOUNTNUMBER
group by ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType,ba.typeAccountCode
Error encountered: ORA-00942
--------------------------------------------------------------------------------
select ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType,ba.typeAccountCode
from bms_account ba
START WITH ba.accountnumber in
(
select no_acc accountnumber
from saldo_acc sa, mst_accountingPeriod ap
where to_date ('01/06/2011','DD/MM/YYYY') >= ap.start_Date and
to_date ('01/06/2011','DD/MM/YYYY') <= ap.end_Date and
sa.tgl_sl >= ap.start_date and
sa.tgl_sl <= to_date ('01/06/2011','DD/MM/YYYY') and
sa.cabang = '031' and sa.ak_sl <> 0
group by no_acc
)
CONNECT BY prior ba.PARENTACCOUNTNUMBER = ba.ACCOUNTNUMBER
group by ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType,ba.typeAccountCode
Error encountered: ORA-00911
********************************************************************************
SQL ID: 9dhpqsf1wjzq7 Plan Hash: 0
ALTER SESSION SET SQL_TRACE=TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 86
********************************************************************************
SQL ID: 47r1y8yn34jmj Plan Hash: 3213098354
select default$
from
col$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY USER ROWID COL$ (cr=1 pr=0 pw=0 time=22 us cost=1 size=15 card=1)
********************************************************************************
SQL ID: ftj9uawt4wwzb Plan Hash: 1615075166
select condition
from
cdef$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY USER ROWID CDEF$ (cr=1 pr=0 pw=0 time=16 us cost=1 size=15 card=1)
********************************************************************************
SQL ID: 9fsc73pz7gnha Plan Hash: 1022690463
select *
from
emf_contract where rownum < 3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 86
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2 2 2 COUNT STOPKEY (cr=4 pr=0 pw=0 time=74 us)
2 2 2 TABLE ACCESS FULL EMF_CONTRACT (cr=4 pr=0 pw=0 time=70 us cost=2 size=364 card=2)
********************************************************************************
SQL ID: 3nkd3g3ju5ph1 Plan Hash: 853875749
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 9 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 9 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=24 us cost=4 size=86 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=20 us cost=3 size=0 card=1)(object id 37)
********************************************************************************
SQL ID: 96g93hntrzjtr Plan Hash: 841937906
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 7 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 0 1 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=0 pw=0 time=28 us)
1 0 1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=23 us)(object id 427)
********************************************************************************
declare
m_stmt varchar2(512);
begin
m_stmt:='delete from sdo_geor_ddl__table$$';
EXECUTE IMMEDIATE m_stmt;
EXCEPTION
WHEN OTHERS THEN
NULL;
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 2
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 57 (recursive depth: 1)
********************************************************************************
SQL ID: 1gu8t96d0bdmu Plan Hash: 17605035
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,
nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,
t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,
t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),
nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),
nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,
ts.logicalread
from
tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 1 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 1 4 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 MERGE JOIN OUTER (cr=4 pr=1 pw=0 time=3701 us cost=2 size=193 card=1)
1 1 1 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=1 pw=0 time=3679 us cost=2 size=141 card=1)
1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=1 pw=0 time=3663 us cost=1 size=0 card=1)(object id 3)
0 0 0 BUFFER SORT (cr=1 pr=0 pw=0 time=16 us cost=0 size=52 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=1 pr=0 pw=0 time=5 us cost=0 size=52 card=1)
0 0 0 INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=1 pr=0 pw=0 time=3 us cost=0 size=0 card=1)(object id 431)
********************************************************************************
SQL ID: 7ng34ruy5awxq Plan Hash: 3984801583
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
ist.logicalread
from
ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 SORT ORDER BY (cr=3 pr=0 pw=0 time=150 us cost=7 size=376 card=2)
0 0 0 HASH JOIN OUTER (cr=3 pr=0 pw=0 time=135 us cost=6 size=376 card=2)
0 0 0 NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=48 us cost=2 size=290 card=2)
0 0 0 TABLE ACCESS CLUSTER IND$ (cr=3 pr=0 pw=0 time=46 us cost=2 size=186 card=2)
1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=21 us cost=1 size=0 card=1)(object id 3)
0 0 0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=52 card=1)
0 0 0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 433)
0 0 0 VIEW (cr=0 pr=0 pw=0 time=0 us cost=3 size=43 card=1)
0 0 0 SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us cost=3 size=15 card=1)
0 0 0 TABLE ACCESS CLUSTER CDEF$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
0 0 0 INDEX UNIQUE SCAN I_COBJ# (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 30)
********************************************************************************
SQL ID: 83taa7kaw59c1 Plan Hash: 2783779297
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from
col$ where obj#=:1 order by intcol#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT ORDER BY (cr=3 pr=0 pw=0 time=60 us cost=3 size=720 card=12)
1 1 1 TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=36 us cost=2 size=720 card=12)
1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=15 us cost=1 size=0 card=1)(object id 3)
********************************************************************************
SQL ID: 3w4qs0tbpmxr6 Plan Hash: 3924106966
select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3
from
cdef$ where robj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=2 pr=0 pw=0 time=26 us cost=3 size=120 card=4)
0 0 0 INDEX RANGE SCAN I_CDEF3 (cr=2 pr=0 pw=0 time=23 us cost=1 size=0 card=4)(object id 55)
********************************************************************************
SQL ID: gx4mv66pvj3xz Plan Hash: 3886069984
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3
from
cdef$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.00 1 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 1 2 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS CLUSTER CDEF$ (cr=2 pr=1 pw=0 time=4180 us cost=2 size=245 card=5)
0 0 0 INDEX UNIQUE SCAN I_COBJ# (cr=2 pr=1 pw=0 time=4176 us cost=1 size=0 card=1)(object id 30)
********************************************************************************
SQL ID: a9u0s3g93f47z Plan Hash: 2094022552
select decode(u.type#, 2, u.ext_username, u.name), o.name, t.update$,
t.insert$, t.delete$, t.enabled, decode(bitand(t.property, 8192),
8192, 1, 0), decode(bitand(t.property, 65536), 65536, 1, 0),
decode(bitand(t.property, 131072), 131072, 1, 0), (select o.name
from
obj$ o where o.obj# = u.spare2 and o.type# =57) from sys.obj$ o,
sys.user$ u, sys.trigger$ t, sys.obj$ bo where t.baseobject=bo.obj# and
bo.name = :1 and bo.spare3 = :2 and bo.namespace = 1 and t.obj#=o.obj#
and o.owner#=u.user# and o.type# = 12 and bitand(property,16)=0 and
bitand(property,8)=0 order by o.obj#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 1 5 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 1 5 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=3 size=34 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 36)
0 0 0 SORT ORDER BY (cr=5 pr=1 pw=0 time=1894 us cost=9 size=205 card=1)
0 0 0 NESTED LOOPS (cr=5 pr=1 pw=0 time=1880 us cost=8 size=205 card=1)
0 0 0 NESTED LOOPS (cr=5 pr=1 pw=0 time=1877 us cost=7 size=99 card=1)
0 0 0 NESTED LOOPS (cr=5 pr=1 pw=0 time=1875 us cost=5 size=62 card=1)
1 1 1 INDEX RANGE SCAN I_OBJ5 (cr=3 pr=1 pw=0 time=1851 us cost=3 size=36 card=1)(object id 40)
0 0 0 TABLE ACCESS BY INDEX ROWID TRIGGER$ (cr=2 pr=0 pw=0 time=23 us cost=2 size=26 card=1)
0 0 0 INDEX RANGE SCAN I_TRIGGER1 (cr=2 pr=0 pw=0 time=11 us cost=1 size=0 card=1)(object id 162)
0 0 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=37 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 36)
0 0 0 TABLE ACCESS CLUSTER USER$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=106 card=1)
0 0 0 INDEX UNIQUE SCAN I_USER# (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 11)
********************************************************************************
SQL ID: 6aq34nj2zb2n7 Plan Hash: 3286988581
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 1 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 1 2 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 SORT GROUP BY (cr=2 pr=1 pw=0 time=3917 us cost=4 size=15 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=1 pw=0 time=3901 us cost=3 size=15 card=1)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=1 pw=0 time=3899 us cost=2 size=0 card=1)(object id 62)
********************************************************************************
SQL ID: 2q93zsrvbdw48 Plan Hash: 3286988581
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 SORT GROUP BY (cr=2 pr=0 pw=0 time=30 us cost=4 size=15 card=1)
0 0 0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=18 us cost=3 size=15 card=1)
0 0 0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=16 us cost=2 size=0 card=1)(object id 62)
********************************************************************************
SQL ID: 3972rvxu3knn3 Plan Hash: 912612854
delete from sdo_geor_ddl__table$$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 57 (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=6 us)
0 0 0 TABLE ACCESS FULL SDO_GEOR_DDL__TABLE$$ (cr=0 pr=0 pw=0 time=3 us cost=2 size=0 card=1)
********************************************************************************
SQL ID: gqut8cstnqrw5 Plan Hash: 2603802766
select *
from
bms_account where rownum <2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 86
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 COUNT STOPKEY (cr=3 pr=0 pw=0 time=67 us)
1 1 1 TABLE ACCESS FULL BMS_ACCOUNT (cr=3 pr=0 pw=0 time=60 us cost=2 size=116 card=1)
********************************************************************************
select ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType, ba.typeAccountCode
from bms_account ba
START WITH ba.accountnumber in
(
select no_acc accountnumber
from saldo_acc sa, mst_accountingPeriod ap
where to_date ('01/06/2011','DD/MM/YYYY') >= ap.start_Date and
to_date ('01/06/2011','DD/MM/YYYY') <= ap.end_Date and
sa.tgl_sl >= ap.start_date and
sa.tgl_sl <= to_date ('01/06/2011','DD/MM/YYYY') and
sa.cabang = '031' and sa.ak_sl <> 0
group by no_acc
)
CONNECT BY prior ba.PARENTACCOUNTNUMBER = ba.ACCOUNTNUMBER
group by ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType,ba.typeAccountCode
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 53.92 87.31 410 4439611 0 47
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 53.92 87.32 410 4439611 0 47
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 86
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
47 47 47 HASH GROUP BY (cr=4439611 pr=410 pw=0 time=87314266 us cost=14 size=148962 card=2442)
54 54 54 CONNECT BY NO FILTERING WITH SW (UNIQUE) (cr=4439611 pr=410 pw=0 time=87314292 us)
2442 2442 2442 TABLE ACCESS FULL BMS_ACCOUNT (cr=45 pr=0 pw=0 time=4324 us cost=13 size=148962 card=2442)
23 23 23 FILTER (cr=4439566 pr=410 pw=0 time=87257664 us)
55913 55913 55913 HASH GROUP BY (cr=4439566 pr=410 pw=0 time=87046026 us cost=508 size=45 card=1)
388278 388278 388278 MERGE JOIN (cr=4439566 pr=410 pw=0 time=85567412 us cost=507 size=32580 card=724)
1369561 1369561 1369561 SORT JOIN (cr=4424914 pr=410 pw=0 time=84577632 us cost=503 size=3132 card=108)
1369962 1369962 1369962 TABLE ACCESS BY INDEX ROWID SALDO_ACC (cr=4424914 pr=410 pw=0 time=70761429 us cost=503 size=3132 card=108)
106900992 106900992 106900992 INDEX RANGE SCAN IDX_SA2 (cr=996346 pr=410 pw=0 time=45370077 us cost=20 size=0 card=2108)(object id 83261)
388278 388278 388278 SORT JOIN (cr=14652 pr=0 pw=0 time=1178350 us cost=4 size=112 card=7)
2442 2442 2442 TABLE ACCESS FULL MST_ACCOUNTINGPERIOD (cr=14652 pr=0 pw=0 time=154134 us cost=3 size=112 card=7)
********************************************************************************
SQL ID: 7cfz5wy9caaf4 Plan Hash: 245223518
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,
'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,
DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
FROM
V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY
NAME_COL_PLUS_SHOW_PARAM,ROWNUM
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 86
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT ORDER BY (cr=0 pr=0 pw=0 time=5688 us cost=2 size=2128 card=1)
1 1 1 COUNT (cr=0 pr=0 pw=0 time=5665 us)
1 1 1 HASH JOIN (cr=0 pr=0 pw=0 time=5663 us cost=1 size=2128 card=1)
1 1 1 FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=2944 us cost=0 size=94 card=1)
2655 2655 2655 FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=2661 us cost=0 size=203400 card=100)
********************************************************************************
SQL ID: grwydz59pu6mc Plan Hash: 1028580536
select text
from
view$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.00 1 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 1 2 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=1 pw=0 time=3670 us cost=1 size=15 card=1)
********************************************************************************
SQL ID: f711myt0q6cma Plan Hash: 0
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,
userhost,terminal,action#,returncode, logoff$lread,logoff$pread,
logoff$lwrite,logoff$dead, logoff$time,comment$text,spare1,clientid,
sessioncpu,proxy$sid,user$guid, instance#,process#,auditid,dbid)
values
(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,:6,:7,:8, :9,:10,:11,
:12, cast(SYS_EXTRACT_UTC(systimestamp) as date),:13,:14,:15,:16,:17,
:18, :19,:20,:21,:22)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 2 1
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.01 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 11 53.92 87.32 410 4439618 0 51
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 53.92 87.33 410 4439618 0 51
Misses in library cache during parse: 4
Misses in library cache during execute: 2
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 20 0.00 0.00 0 0 0 0
Execute 22 0.00 0.02 0 1 2 3
Fetch 18 0.02 0.01 5 45 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 60 0.02 0.04 5 46 2 9
Misses in library cache during parse: 14
Misses in library cache during execute: 14
7 user SQL statements in session.
16 internal SQL statements in session.
23 SQL statements in session.
********************************************************************************
Trace file: emf_ora_16253024.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
7 user SQL statements in trace file.
16 internal SQL statements in trace file.
23 SQL statements in trace file.
21 unique SQL statements in trace file.
377 lines in trace file.
2385 elapsed seconds in trace file.
i really appreciate for your guide. (because i'm novice at this forum)
this case is annnoying me , because in oracle ver 10, this query running well.
Thx
|
|
|
|
| Re: start with and connect by [message #517753 is a reply to message #517750] |
Wed, 27 July 2011 23:43 |
|
liliek
Messages: 13
Registered: July 2011
Location: Indonesia
|
Junior Member |
|
|
sorry i post if part by part.
this is the sqltrace in 10.2.0.1.0
TKPROF: Release 10.2.0.1.0 - Production on Thu Jul 28 11:10:08 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Trace file: emf_ora_20456.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
The following statement encountered a error during parse:
select ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType, ba.typeAccountCode
from bms_account ba
START WITH ba.accountnumber in
(
select no_acc accountnumber
from saldo_acc sa, mst_accountingPeriod ap
where to_date ('01/06/2011','DD/MM/YYYY') >= ap.start_Date and
to_date ('01/06/2011','DD/MM/YYYY') <= ap.end_Date and
sa.tgl_sl >= ap.start_date and
sa.tgl_sl <= to_date ('01/06/2011','DD/MM/YYYY') andsa.cabang = '031' and sa.ak_sl <> 0
group by no_acc
)
CONNECT BY prior ba.PARENTACCOUNTNUMBER = ba.ACCOUNTNUMBER
group by ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType,ba.typeAccountCode
Error encountered: ORA-00907
********************************************************************************
ALTER SESSION SET SQL_TRACE=TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
********************************************************************************
select ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType, ba.typeAccountCode
from bms_account ba
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 158 0.00 0.00 0 203 0 2347
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 160 0.01 0.01 0 203 0 2347
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
2347 MAT_VIEW ACCESS FULL BMS_ACCOUNT (cr=203 pr=0 pw=0 time=2476 us)
********************************************************************************
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,
i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
ist.logicalread
from
ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 11 0.01 0.01 0 0 0 0
Fetch 29 0.01 0.00 0 102 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 42 0.02 0.02 0 102 0 18
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 SORT ORDER BY (cr=11 pr=0 pw=0 time=1201 us)
2 HASH JOIN OUTER (cr=11 pr=0 pw=0 time=1100 us)
2 NESTED LOOPS OUTER (cr=8 pr=0 pw=0 time=180 us)
2 TABLE ACCESS CLUSTER IND$ (cr=6 pr=0 pw=0 time=144 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=41 us)(object id 3)
0 TABLE ACCESS BY INDEX ROWID IND_STATS$ (cr=2 pr=0 pw=0 time=27 us)
0 INDEX UNIQUE SCAN I_IND_STATS$_OBJ# (cr=2 pr=0 pw=0 time=22 us)(object id 711)
0 VIEW (cr=3 pr=0 pw=0 time=170 us)
0 SORT GROUP BY (cr=3 pr=0 pw=0 time=168 us)
0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=3 pr=0 pw=0 time=105 us)
2 INDEX RANGE SCAN I_CDEF2 (cr=2 pr=0 pw=0 time=53 us)(object id 51)
********************************************************************************
select pos#,intcol#,col#,spare1,bo#,spare2
from
icol$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 18 0.00 0.00 0 0 0 0
Fetch 50 0.00 0.00 0 100 0 32
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.01 0 100 0 32
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID ICOL$ (cr=4 pr=0 pw=0 time=107 us)
1 INDEX RANGE SCAN I_ICOL1 (cr=3 pr=0 pw=0 time=81 us)(object id 40)
********************************************************************************
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,
nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,
scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,
rowid,col#,property, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,
nvl(spare3,0)
from
col$ where obj#=:1 order by intcol#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 12 0.00 0.00 0 0 0 0
Fetch 117 0.00 0.00 0 39 0 105
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 131 0.01 0.01 0 39 0 105
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
7 SORT ORDER BY (cr=4 pr=0 pw=0 time=131 us)
7 TABLE ACCESS CLUSTER COL$ (cr=4 pr=0 pw=0 time=79 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=24 us)(object id 3)
********************************************************************************
select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 and col# is not null group by privilege#, col#,
grantee# order by col#, grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.01 0.01 0 4 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT GROUP BY (cr=2 pr=0 pw=0 time=109 us)
0 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=2 pr=0 pw=0 time=65 us)
0 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=58 us)(object id 103)
********************************************************************************
select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))
from
objauth$ where obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by
grantee#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 10 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.01 0.01 0 10 0 6
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT GROUP BY (cr=3 pr=0 pw=0 time=157 us)
1 TABLE ACCESS BY INDEX ROWID OBJAUTH$ (cr=3 pr=0 pw=0 time=81 us)
1 INDEX RANGE SCAN I_OBJAUTH1 (cr=2 pr=0 pw=0 time=37 us)(object id 103)
********************************************************************************
select con#,obj#,rcon#,enabled,nvl(defer,0)
from
cdef$ where robj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 1 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=1 pr=0 pw=0 time=36 us)
0 INDEX RANGE SCAN I_CDEF3 (cr=1 pr=0 pw=0 time=29 us)(object id 52)
********************************************************************************
select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),
rowid,cols,nvl(defer,0),mtime,nvl(spare1,0)
from
cdef$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.01 0.01 0 6 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
2 TABLE ACCESS BY INDEX ROWID CDEF$ (cr=6 pr=0 pw=0 time=57 us)
2 INDEX RANGE SCAN I_CDEF2 (cr=4 pr=0 pw=0 time=48 us)(object id 51)
********************************************************************************
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#,
sample_size, minimum, maximum, distcnt, lowval, hival, density, col#,
spare1, spare2, avgcln
from
hist_head$ where obj#=:1 and intcol#=:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 10 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 0.01 0.01 0 10 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=3 pr=0 pw=0 time=123 us)
1 INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=68 us)(object id 257)
********************************************************************************
select /*+ rule */ bucket, endpoint, col#, epvalue
from
histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 3 0 9
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
9 SORT ORDER BY (cr=3 pr=0 pw=0 time=193 us)
9 TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=0 pw=0 time=136 us)
1 INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=72 us)(object id 252)
********************************************************************************
select intcol#,nvl(pos#,0),col#,nvl(spare1,0)
from
ccol$ where con#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.01 0.01 0 8 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType, ba.typeAccountCode
from bms_account ba
START WITH ba.accountnumber in
(
select no_acc accountnumber
from saldo_acc sa, mst_accountingPeriod ap
where to_date ('01/06/2011','DD/MM/YYYY') >= ap.start_Date and
to_date ('01/06/2011','DD/MM/YYYY') <= ap.end_Date and
sa.tgl_sl >= ap.start_date and
sa.tgl_sl <= to_date ('01/06/2011','DD/MM/YYYY') and
sa.cabang = '031' and sa.ak_sl <> 0
group by no_acc
)
CONNECT BY prior ba.PARENTACCOUNTNUMBER = ba.ACCOUNTNUMBER
group by ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType,ba.typeAccountCode
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.12 0.12 0 2437 0 48
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.14 0.14 0 2437 0 48
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
48 HASH GROUP BY (cr=2437 pr=0 pw=0 time=125575 us)
55 CONNECT BY WITH FILTERING (cr=2437 pr=0 pw=0 time=124697 us)
24 MAT_VIEW ACCESS BY INDEX ROWID BMS_ACCOUNT (cr=2375 pr=0 pw=0 time=121596 us)
24 HASH JOIN (cr=2362 pr=0 pw=0 time=121970 us)
24 VIEW VW_NSO_1 (cr=2345 pr=0 pw=0 time=119369 us)
24 HASH GROUP BY (cr=2345 pr=0 pw=0 time=119318 us)
166 MERGE JOIN (cr=2345 pr=0 pw=0 time=117840 us)
167 SORT JOIN (cr=2338 pr=0 pw=0 time=116490 us)
589 MAT_VIEW ACCESS BY INDEX ROWID SALDO_ACC (cr=2338 pr=0 pw=0 time=26740 us)
89072 INDEX RANGE SCAN IDX_SA2 (cr=463 pr=0 pw=0 time=89709 us)(object id 86420)
166 SORT JOIN (cr=7 pr=0 pw=0 time=664 us)
1 MAT_VIEW ACCESS FULL MST_ACCOUNTINGPERIOD (cr=7 pr=0 pw=0 time=154 us)
2347 INDEX FAST FULL SCAN BMS_ACCOUNT_IX01 (cr=17 pr=0 pw=0 time=128 us)(object id 56287)
30 NESTED LOOPS (cr=62 pr=0 pw=0 time=226 us)
48 BUFFER SORT (cr=0 pr=0 pw=0 time=159 us)
48 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=36 us)
30 MAT_VIEW ACCESS BY INDEX ROWID BMS_ACCOUNT (cr=62 pr=0 pw=0 time=597 us)
30 INDEX RANGE SCAN BMS_ACCOUNT_IX01 (cr=32 pr=0 pw=0 time=326 us)(object id 56287)
0 MAT_VIEW ACCESS FULL BMS_ACCOUNT (cr=0 pr=0 pw=0 time=0 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us)
0 MERGE JOIN (cr=0 pr=0 pw=0 time=0 us)
0 SORT JOIN (cr=0 pr=0 pw=0 time=0 us)
0 MAT_VIEW ACCESS BY INDEX ROWID SALDO_ACC (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN IDX_SA2 (cr=0 pr=0 pw=0 time=0 us)(object id 86420)
0 SORT JOIN (cr=0 pr=0 pw=0 time=0 us)
0 MAT_VIEW ACCESS FULL MST_ACCOUNTINGPERIOD (cr=0 pr=0 pw=0 time=0 us)
********************************************************************************
select obj#,type#,ctime,mtime,stime,status,dataobj#,flags,oid$, spare1,
spare2
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 8 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.01 0.01 0 8 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=2 pr=0 pw=0 time=66 us)
0 INDEX RANGE SCAN I_OBJ2 (cr=2 pr=0 pw=0 time=60 us)(object id 37)
********************************************************************************
select node,owner,name
from
syn$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 3 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID SYN$ (cr=3 pr=0 pw=0 time=63 us)
1 INDEX UNIQUE SCAN I_SYN1 (cr=2 pr=0 pw=0 time=43 us)(object id 101)
********************************************************************************
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,
nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,
t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,
t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),
nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),
nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,
ts.logicalread
from
tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 6 0.00 0.00 0 24 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.01 0.01 0 24 0 6
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS OUTER (cr=4 pr=0 pw=0 time=96 us)
1 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=66 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=25 us)(object id 3)
0 TABLE ACCESS BY INDEX ROWID TAB_STATS$ (cr=1 pr=0 pw=0 time=23 us)
0 INDEX UNIQUE SCAN I_TAB_STATS$_OBJ# (cr=1 pr=0 pw=0 time=19 us)(object id 709)
********************************************************************************
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#,
nvl(property,0),subname,d_attrs
from
dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 13 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.01 0.01 0 13 0 2
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=7 pr=0 pw=0 time=220 us)
1 NESTED LOOPS OUTER (cr=7 pr=0 pw=0 time=171 us)
1 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=4 pr=0 pw=0 time=114 us)
1 INDEX RANGE SCAN I_DEPENDENCY1 (cr=3 pr=0 pw=0 time=58 us)(object id 122)
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=46 us)
1 INDEX UNIQUE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=18 us)(object id 36)
********************************************************************************
select order#,columns,types
from
access$ where d_obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.01 0.01 0 6 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID ACCESS$ (cr=2 pr=0 pw=0 time=48 us)
0 INDEX RANGE SCAN I_ACCESS1 (cr=2 pr=0 pw=0 time=37 us)(object id 124)
********************************************************************************
select cols,audit$,textlength,intcols,property,flags,rowid
from
view$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 3 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID VIEW$ (cr=3 pr=0 pw=0 time=92 us)
1 INDEX UNIQUE SCAN I_VIEW1 (cr=2 pr=0 pw=0 time=58 us)(object id 99)
********************************************************************************
select text
from
view$ where rowid=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 0 2 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=27 us)
********************************************************************************
select timestamp, flags
from
fixed_obj$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 4 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID FIXED_OBJ$ (cr=2 pr=0 pw=0 time=56 us)
0 INDEX UNIQUE SCAN I_FIXED_OBJ$_OBJ# (cr=2 pr=0 pw=0 time=49 us)(object id 707)
********************************************************************************
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,
decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),
decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',
3,'IMMEDIATE','FALSE'), decode(bitand(ksppiflg,4),4,
'FALSE', decode(bitand(ksppiflg/65536,3)
, 0, 'FALSE', 'TRUE')), decode(bitand(ksppstvf,7),1,'MODIFIED',4,
'SYSTEM_MOD','FALSE'), decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),
decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), ksppdesc,
ksppstcmnt, ksppihash
from
x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ((translate(ksppinm,'_',
'#') not like '##%') and ((translate(ksppinm,'_','#') not like '#%')
or (ksppstdf = 'FALSE') or (bitand(ksppstvf,5) > 0)))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
********************************************************************************
select NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT ,
ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED ,
ISADJUSTED , ISDEPRECATED, DESCRIPTION, UPDATE_COMMENT, HASH
from
GV$PARAMETER where inst_id = USERENV('Instance')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#,
nvl(typidcol#, 0)
from
coltype$ where obj#=:1 order by intcol# desc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 3 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=3 pr=0 pw=0 time=87 us)
0 TABLE ACCESS CLUSTER COLTYPE$ (cr=3 pr=0 pw=0 time=55 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=32 us)(object id 3)
********************************************************************************
select intcol#, toid, version#, intcols, intcol#s, flags, synobj#
from
subcoltype$ where obj#=:1 order by intcol# asc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 1 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID SUBCOLTYPE$ (cr=1 pr=0 pw=0 time=46 us)
0 INDEX RANGE SCAN I_SUBCOLTYPE1 (cr=1 pr=0 pw=0 time=34 us)(object id 172)
********************************************************************************
select col#,intcol#,ntab#
from
ntab$ where obj#=:1 order by intcol# asc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 1 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID NTAB$ (cr=1 pr=0 pw=0 time=37 us)
0 INDEX RANGE SCAN I_NTAB2 (cr=1 pr=0 pw=0 time=31 us)(object id 200)
********************************************************************************
select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk,
l.pctversion$, l.flags, l.property, l.retention, l.freepools
from
lob$ l where l.obj# = :1 order by l.intcol# asc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 3 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 SORT ORDER BY (cr=3 pr=0 pw=0 time=76 us)
0 TABLE ACCESS CLUSTER LOB$ (cr=3 pr=0 pw=0 time=48 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=25 us)(object id 3)
********************************************************************************
select col#,intcol#,reftyp,stabid,expctoid
from
refcon$ where obj#=:1 order by intcol# asc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 1 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID REFCON$ (cr=1 pr=0 pw=0 time=40 us)
0 INDEX RANGE SCAN I_REFCON2 (cr=1 pr=0 pw=0 time=31 us)(object id 204)
********************************************************************************
select col#,intcol#,charsetid,charsetform
from
col$ where obj#=:1 order by intcol# asc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 16 0.00 0.00 0 3 0 15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.01 0.01 0 3 0 15
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
15 SORT ORDER BY (cr=3 pr=0 pw=0 time=122 us)
15 TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=87 us)
1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=30 us)(object id 3)
********************************************************************************
select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum
from
opqtype$ where obj# = :1 order by intcol# asc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.01 0 1 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID OPQTYPE$ (cr=1 pr=0 pw=0 time=38 us)
0 INDEX RANGE SCAN I_OPQTYPE1 (cr=1 pr=0 pw=0 time=30 us)(object id 206)
********************************************************************************
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,
'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,
DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
FROM
V$PARAMETER WHERE UPPER(NAME) LIKE UPPER('%user_dump_dest%') ORDER BY
NAME_COL_PLUS_SHOW_PARAM,ROWNUM
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.01 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 0 0 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.02 0.02 0 0 0 2
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY (cr=0 pr=0 pw=0 time=3633 us)
1 COUNT (cr=0 pr=0 pw=0 time=3595 us)
1 HASH JOIN (cr=0 pr=0 pw=0 time=3589 us)
1 FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=1776 us)
1385 FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=1395 us)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.04 0.04 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 167 0.13 0.13 0 2640 0 2397
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 176 0.18 0.18 0 2640 0 2397
Misses in library cache during parse: 3
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 36 0.15 0.15 0 0 0 0
Execute 79 0.16 0.16 0 0 0 0
Fetch 262 0.01 0.01 0 359 0 205
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 377 0.33 0.33 0 359 0 205
Misses in library cache during parse: 27
Misses in library cache during execute: 25
5 user SQL statements in session.
81 internal SQL statements in session.
86 SQL statements in session.
********************************************************************************
Trace file: emf_ora_20456.trc
Trace file compatibility: 10.01.00
Sort options: default
1 session in tracefile.
5 user SQL statements in trace file.
81 internal SQL statements in trace file.
86 SQL statements in trace file.
31 unique SQL statements in trace file.
1285 lines in trace file.
1842 elapsed seconds in trace file.
|
|
|
|
| Re: start with and connect by [message #517780 is a reply to message #517753] |
Thu, 28 July 2011 03:22 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Next time you run a tkprof I suggest you add
To the command line. That removes all the oracle internal statements.
The relevant bits of the trace are:
11g:
select ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType, ba.typeAccountCode
from bms_account ba
START WITH ba.accountnumber in
(
select no_acc accountnumber
from saldo_acc sa, mst_accountingPeriod ap
where to_date ('01/06/2011','DD/MM/YYYY') >= ap.start_Date and
to_date ('01/06/2011','DD/MM/YYYY') <= ap.end_Date and
sa.tgl_sl >= ap.start_date and
sa.tgl_sl <= to_date ('01/06/2011','DD/MM/YYYY') and
sa.cabang = '031' and sa.ak_sl <> 0
group by no_acc
)
CONNECT BY prior ba.PARENTACCOUNTNUMBER = ba.ACCOUNTNUMBER
group by ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType,ba.typeAccountCode
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 53.92 87.31 410 4439611 0 47
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 53.92 87.32 410 4439611 0 47
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 86
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
47 47 47 HASH GROUP BY (cr=4439611 pr=410 pw=0 time=87314266 us cost=14 size=148962 card=2442)
54 54 54 CONNECT BY NO FILTERING WITH SW (UNIQUE) (cr=4439611 pr=410 pw=0 time=87314292 us)
2442 2442 2442 TABLE ACCESS FULL BMS_ACCOUNT (cr=45 pr=0 pw=0 time=4324 us cost=13 size=148962 card=2442)
23 23 23 FILTER (cr=4439566 pr=410 pw=0 time=87257664 us)
55913 55913 55913 HASH GROUP BY (cr=4439566 pr=410 pw=0 time=87046026 us cost=508 size=45 card=1)
388278 388278 388278 MERGE JOIN (cr=4439566 pr=410 pw=0 time=85567412 us cost=507 size=32580 card=724)
1369561 1369561 1369561 SORT JOIN (cr=4424914 pr=410 pw=0 time=84577632 us cost=503 size=3132 card=108)
1369962 1369962 1369962 TABLE ACCESS BY INDEX ROWID SALDO_ACC (cr=4424914 pr=410 pw=0 time=70761429 us cost=503 size=3132 card=108)
106900992 106900992 106900992 INDEX RANGE SCAN IDX_SA2 (cr=996346 pr=410 pw=0 time=45370077 us cost=20 size=0 card=2108)(object id 83261)
388278 388278 388278 SORT JOIN (cr=14652 pr=0 pw=0 time=1178350 us cost=4 size=112 card=7)
2442 2442 2442 TABLE ACCESS FULL MST_ACCOUNTINGPERIOD (cr=14652 pr=0 pw=0 time=154134 us cost=3 size=112 card=7)
10g:
select ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType, ba.typeAccountCode
from bms_account ba
START WITH ba.accountnumber in
(
select no_acc accountnumber
from saldo_acc sa, mst_accountingPeriod ap
where to_date ('01/06/2011','DD/MM/YYYY') >= ap.start_Date and
to_date ('01/06/2011','DD/MM/YYYY') <= ap.end_Date and
sa.tgl_sl >= ap.start_date and
sa.tgl_sl <= to_date ('01/06/2011','DD/MM/YYYY') and
sa.cabang = '031' and sa.ak_sl <> 0
group by no_acc
)
CONNECT BY prior ba.PARENTACCOUNTNUMBER = ba.ACCOUNTNUMBER
group by ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType,ba.typeAccountCode
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.12 0.12 0 2437 0 48
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.14 0.14 0 2437 0 48
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54
Rows Row Source Operation
------- ---------------------------------------------------
48 HASH GROUP BY (cr=2437 pr=0 pw=0 time=125575 us)
55 CONNECT BY WITH FILTERING (cr=2437 pr=0 pw=0 time=124697 us)
24 MAT_VIEW ACCESS BY INDEX ROWID BMS_ACCOUNT (cr=2375 pr=0 pw=0 time=121596 us)
24 HASH JOIN (cr=2362 pr=0 pw=0 time=121970 us)
24 VIEW VW_NSO_1 (cr=2345 pr=0 pw=0 time=119369 us)
24 HASH GROUP BY (cr=2345 pr=0 pw=0 time=119318 us)
166 MERGE JOIN (cr=2345 pr=0 pw=0 time=117840 us)
167 SORT JOIN (cr=2338 pr=0 pw=0 time=116490 us)
589 MAT_VIEW ACCESS BY INDEX ROWID SALDO_ACC (cr=2338 pr=0 pw=0 time=26740 us)
89072 INDEX RANGE SCAN IDX_SA2 (cr=463 pr=0 pw=0 time=89709 us)(object id 86420)
166 SORT JOIN (cr=7 pr=0 pw=0 time=664 us)
1 MAT_VIEW ACCESS FULL MST_ACCOUNTINGPERIOD (cr=7 pr=0 pw=0 time=154 us)
2347 INDEX FAST FULL SCAN BMS_ACCOUNT_IX01 (cr=17 pr=0 pw=0 time=128 us)(object id 56287)
30 NESTED LOOPS (cr=62 pr=0 pw=0 time=226 us)
48 BUFFER SORT (cr=0 pr=0 pw=0 time=159 us)
48 CONNECT BY PUMP (cr=0 pr=0 pw=0 time=36 us)
30 MAT_VIEW ACCESS BY INDEX ROWID BMS_ACCOUNT (cr=62 pr=0 pw=0 time=597 us)
30 INDEX RANGE SCAN BMS_ACCOUNT_IX01 (cr=32 pr=0 pw=0 time=326 us)(object id 56287)
0 MAT_VIEW ACCESS FULL BMS_ACCOUNT (cr=0 pr=0 pw=0 time=0 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us)
0 MERGE JOIN (cr=0 pr=0 pw=0 time=0 us)
0 SORT JOIN (cr=0 pr=0 pw=0 time=0 us)
0 MAT_VIEW ACCESS BY INDEX ROWID SALDO_ACC (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN IDX_SA2 (cr=0 pr=0 pw=0 time=0 us)(object id 86420)
0 SORT JOIN (cr=0 pr=0 pw=0 time=0 us)
0 MAT_VIEW ACCESS FULL MST_ACCOUNTINGPERIOD (cr=0 pr=0 pw=0 time=0 us)
|
|
|
|
|
|
|
|
|
|
| Re: start with and connect by [message #517891 is a reply to message #517867] |
Fri, 29 July 2011 03:52 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You knew one DB had materialized views and the other didn't and you didn't think this was an important bit of information to tell us?
At a glance the 11g query is reading a lot more data than the 10g hence it's taking longer.
Try creating the materialized views on the 11g.
|
|
|
|
|
|
|
|
|
|
| Re: start with and connect by [message #518512 is a reply to message #517314] |
Thu, 04 August 2011 04:36 |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
Hello,
if you want, I could try to tune your sql. To do that, I need a testcase, which you can prepare with the following plsql code:
declare
ccc clob;
sss clob := 'select ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType, ba.typeAccountCode
from bms_account ba
START WITH ba.accountnumber in
(
select no_acc accountnumber
from saldo_acc sa, mst_accountingPeriod ap
where to_date (''01/06/2011'',''DD/MM/YYYY'') >= ap.start_Date and
to_date (''01/06/2011'',''DD/MM/YYYY'') <= ap.end_Date and
sa.tgl_sl >= ap.start_date and
sa.tgl_sl <= to_date (''01/06/2011'',''DD/MM/YYYY'') and
sa.cabang = ''031'' and sa.ak_sl <> 0
group by no_acc
)
CONNECT BY prior ba.PARENTACCOUNTNUMBER = ba.ACCOUNTNUMBER
group by ba.accountNumber, ba.accountName, ba.parentAccountNumber, ba.accountMutationType,ba.typeAccountCode';
begin
dbms_sqldiag.export_sql_testcase(directory=>'DATA_PUMP_DIR',sql_text=>sss, user_name=>'<YOUR USER NAME>', testcase_name =>'LEO', testcase => ccc);
end;
/
Please don't forget to replace <YOUR USER NAME> with your real user name. You need some privileges for execution, so you could do that as dba or sysdba user.
The result you will find in the DATA_PUMP_DIRECTORY (for the path look please into dba_directories). All the testcase files should have LEO as a prefix in their names. You can use tar to get them into one file, then compress this file and upload the result or send directly to lnossov@yahoo.de.
Don't worry about your data, I'll receive only metadata.
Regards
Leonid
|
|
|
|
|
|
|
|
| Re: start with and connect by [message #519578 is a reply to message #518512] |
Mon, 15 August 2011 01:39 |
|
liliek
Messages: 13
Registered: July 2011
Location: Indonesia
|
Junior Member |
|
|
i'm sorry LNossov, to get to this forum by now. actually last week i've been busy set up new server n drc machine.
I'gll give it try the testcase.
Thx
@blackswan : yes it's supposed to be have a similar data. it just db main n replica. may be in some case. ( may be it because the date parameter )
regards
Liliek
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 01:55:07 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
