 on consumption of resources [message #109966] |
Wed, 02 March 2005 07:32  |
Tads
Messages: 10
Registered: February 2005
|
Junior Member |
|
|
Hi all,
I´m trying tunning a query and I would like to know your opinions about the following results of the generated trace:
Before:
call count cpu elapsed disk query curr rows
------- ----- ---- ------- ----- ------ ---- ------
Parse 1 0.19 0.16 1 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 328 5.19 18.26 52380 138758 4 4905
------- ----- ---- ------- ----- ------ ---- ------
total 330 5.39 18.43 52381 138760 4 4905
After:
call count cpu elapsed disk query curr rows
------- ----- ---- ------- ----- ------ ---- ------
Parse 1 0.06 0.06 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 328 8.18 13.01 55067 212876 6 4905
------- ----- ---- ------- ----- ------ ---- ------
total 331 8.24 13.07 55067 212876 6 4905
As noted, only elapsed reduced.
Somebody has some tips for this?
Thanks!!!
[]´s
|
|
|
|
| Re: on consumption of resources [message #109969 is a reply to message #109966] |
Wed, 02 March 2005 07:49  |
Tads
Messages: 10
Registered: February 2005
|
Junior Member |
|
|
Sorry,
I forgot the explain plan:
Before:
IO_COST CPU_COST CARD Pstart Pstop Execution Plan
---------- ---------- ---------- ------------ ------------ ------------------------------------------------------------------------------
253 5 SELECT STATEMENT
253 5 SORT ORDER BY
249 5 NESTED LOOPS OUTER
1 INDEX UNIQUE SCAN PK_STATUS_ARS_HSBC
249 5 NESTED LOOPS OUTER
1 1 TABLE ACCESS BY INDEX ROWID ARS_RESPONSAVEL_HSBC
1 INDEX UNIQUE SCAN PK_ARS_RESP_HSBC
247 5 NESTED LOOPS OUTER
1 1 TABLE ACCESS BY INDEX ROWID ARS_CATEGORIA_HSBC
1 INDEX UNIQUE SCAN PK_ARS_CATEGORIA_HSBC
245 5 NESTED LOOPS OUTER
2 1 TABLE ACCESS BY INDEX ROWID SPECIFIC_ERROR
1 1 INDEX RANGE SCAN IND_SPE_ERROR_PK_ALT
242 5 NESTED LOOPS OUTER
3 1 ROW LOCATION ROW LOCATION TABLE ACCESS BY GLOBAL INDEX ROWID DEVICE_HISTORY
2 1 INDEX RANGE SCAN INDEX_FK_DEV_HIST_VS_H_HIST
238 5 NESTED LOOPS OUTER
234 5 NESTED LOOPS
232 5 NESTED LOOPS
232 5 NESTED LOOPS
230 5 NESTED LOOPS OUTER
228 5 NESTED LOOPS
33 5 NESTED LOOPS
27 6 HASH JOIN
14 36 HASH JOIN
7 6 NESTED LOOPS
2 1 TABLE ACCESS BY INDEX ROWID UNIDADE_HSBC
1 1451 INDEX UNIQUE SCAN PK_UNIDADE_HSBC
5 1 TABLE ACCESS BY INDEX ROWID UNIDADE_HSBC
1 6 INDEX RANGE SCAN IND_FK_UNIDADE_VS_PAI
6 1451 TABLE ACCESS FULL UNIDADE_HSBC
12 256 TABLE ACCESS FULL ATM_HSBC
1 20 TABLE ACCESS BY INDEX ROWID ATM
27 INDEX UNIQUE SCAN PK_ATM
39 1 ROW LOCATION ROW LOCATION TABLE ACCESS BY GLOBAL INDEX ROWID HISTORY
2 751 INDEX RANGE SCAN INDEX_FK_HISTORY_VS_ATM
1 1 TABLE ACCESS BY INDEX ROWID ARS_HSBC
1 INDEX RANGE SCAN IND_ARS_HSBC_NR
1 1 TABLE ACCESS BY INDEX ROWID ERROR_CODE
1 INDEX UNIQUE SCAN PK_ERROR_CODE
IO_COST CPU_COST CARD Pstart Pstop Execution Plan
---------- ---------- ---------- ------------ ------------ ------------------------------------------------------------------------------
1 INDEX UNIQUE SCAN PK_SITE_HSBC
1 9 TABLE ACCESS BY INDEX ROWID MODEL
1 INDEX UNIQUE SCAN PK_MODEL
3 1 TABLE ACCESS BY INDEX ROWID HARDWARE_HISTORY
2 1 INDEX RANGE SCAN INDEX_HARD_HISTORY_EVENT
After:
IO_COST CARD Pstart Pstop Execution Plan
---------- ---------- ------------ ------------ ------------------------------------------------------------------------------
29 1 SELECT STATEMENT
29 1 SORT ORDER BY
25 1 NESTED LOOPS
24 1 NESTED LOOPS OUTER
23 1 NESTED LOOPS OUTER
23 1 NESTED LOOPS OUTER
22 1 NESTED LOOPS
21 1 NESTED LOOPS OUTER
20 1 NESTED LOOPS
19 1 NESTED LOOPS
18 1 NESTED LOOPS OUTER
15 1 NESTED LOOPS OUTER
12 1 NESTED LOOPS
12 1 NESTED LOOPS
11 1 NESTED LOOPS
7 4 HASH JOIN OUTER
4 4 NESTED LOOPS
2 1 TABLE ACCESS BY INDEX ROWID UNIDADE_HSBC
1 1451 INDEX UNIQUE SCAN PK_UNIDADE_HSBC
2 4 ROW LOCATION ROW LOCATION TABLE ACCESS BY GLOBAL INDEX ROWID HISTORY
1 8978 INDEX RANGE SCAN IND_HIS_FG_ERR_MONIT
2 1 TABLE ACCESS FULL ARS_HSBC
1 1 TABLE ACCESS BY INDEX ROWID ATM
27 INDEX UNIQUE SCAN PK_ATM
1 1 TABLE ACCESS BY INDEX ROWID ATM_HSBC
19 INDEX UNIQUE SCAN PK_ATM_HSBC
1 INDEX UNIQUE SCAN PK_SITE_HSBC
3 1 TABLE ACCESS BY INDEX ROWID HARDWARE_HISTORY
2 1 INDEX RANGE SCAN INDEX_HARD_HISTORY_EVENT
3 1 ROW LOCATION ROW LOCATION TABLE ACCESS BY GLOBAL INDEX ROWID DEVICE_HISTORY
2 1 INDEX RANGE SCAN INDEX_FK_DEV_HIST_VS_H_HIST
1 6 TABLE ACCESS BY INDEX ROWID UNIDADE_HSBC
1 INDEX UNIQUE SCAN PK_UNIDADE_HSBC
1 6 TABLE ACCESS BY INDEX ROWID UNIDADE_HSBC
242 INDEX UNIQUE SCAN PK_UNIDADE_HSBC
2 1 TABLE ACCESS BY INDEX ROWID SPECIFIC_ERROR
1 1 INDEX RANGE SCAN IND_SPE_ERROR_PK_ALT
1 1 TABLE ACCESS BY INDEX ROWID ERROR_CODE
1 INDEX UNIQUE SCAN PK_ERROR_CODE
1 1 TABLE ACCESS BY INDEX ROWID ARS_CATEGORIA_HSBC
IO_COST CARD Pstart Pstop Execution Plan
---------- ---------- ------------ ------------ ------------------------------------------------------------------------------
1 INDEX UNIQUE SCAN PK_ARS_CATEGORIA_HSBC
1 INDEX UNIQUE SCAN PK_STATUS_ARS_HSBC
1 1 TABLE ACCESS BY INDEX ROWID ARS_RESPONSAVEL_HSBC
1 INDEX UNIQUE SCAN PK_ARS_RESP_HSBC
1 1 TABLE ACCESS BY INDEX ROWID MODEL
1 INDEX UNIQUE SCAN PK_MODEL
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
