TKPROF

[ramasineParticipante]

Galera,

Vou postar aqui parte do TKPROF, que fiz com a sort_options= fchcpu.
Tem o Oracle Spatial neste banco de dados!

TKPROF: Release 10.1.0.5.0 – Production on Thu Aug 6 13:11:56 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Trace file: sigp_ora_2572382.trc
Sort options: fchcpu
********************************************************************************
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
********************************************************************************

SELECT GET_INTERSECTION_POLYGON(:B1 , MIP.MIP_POLIGONO)
FROM SIG_MIG_ITM_POL_CA2009 MIP WHERE PLA_ID = :B2 AND MIP_POLIGONO IS NOT NULL AND SDO_RELATE(MIP.MIP_POLIGONO, :B1 , ‘MASK=ANYINTERACT’) = ‘TRUE’

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 0 0.00 0.00 0 0 0 0
Execute 355 21.45 53.00 0 9620 710 0
Fetch 710 1191.90 2234.48 86 1149761 0 355
——- —— ——– ———- ———- ———- ———- ———-
total 1065 1213.35 2287.49 86 1159381 710 355

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 187 (PEX) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-06553: PLS-306: wrong number or types of arguments in call to ‘GET_INTERSECTION_POLYGON’

parse error offset: 75

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
—————————————- Waited ———- ————
latch: row cache objects 8 0.00 0.01
db file sequential read 48 0.00 0.05
latch: object queue header operation 1 0.00 0.00
latch: cache buffers chains 1 0.00 0.00
latch: library cache 1 0.00 0.00
********************************************************************************

O fato é que não consigo otimizar a query que vem logo em primeiro lugar, e com mais consumo de CPU:

SELECT GET_INTERSECTION_POLYGON(:B1 , MIP.MIP_POLIGONO)
FROM SIG_MIG_ITM_POL_CA2009 MIP WHERE PLA_ID = :B2 AND MIP_POLIGONO IS NOT NULL AND SDO_RELATE(MIP.MIP_POLIGONO, :B1 , ‘MASK=ANYINTERACT’) = ‘TRUE’

Tem um erro apontado…
tem como mudar a passagem dos argumentos, das variáveis..ou algo do tipo?

Qq ajuda é bem-vinda!