Home » SQL & PL/SQL » SQL & PL/SQL » Long query with WITH not responding (Ora 11.2.0.3.0)
Long query with WITH not responding [message #685023] |
Mon, 11 October 2021 06:50  |
 |
Reversi72
Messages: 10 Registered: October 2021
|
Junior Member |
|
|
I find the WITH very useful and I don't know if the problem I am about to describe is due to this clause.
I have a very long query that doesn't respond, or rather it repond but after a long time. The problem is not the query itself, but its parse. Even trying to just execute the execution plan (I'm rehearsing with Toad, not running the query, just running the execution plan.), I get responses after about 10 minutes. I also tried to remove all comments, all double spaces. I always have the same behavior. Can you tell me how I can solve it?
The query include only tables.
I asked if it was possible to change the _optimizer_max_permutations parameter, bringing it to 1000 or even less, but the DBA group denied me the request.
|
|
|
|
Re: Long query with WITH not responding [message #685025 is a reply to message #685023] |
Mon, 11 October 2021 07:51   |
John Watson
Messages: 8803 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:I asked if it was possible to change the _optimizer_max_permutations parameter, bringing it to 1000 or even less, but the DBA group denied me the request. To test your hypothesis that the parsing is the problem, you can adjust the parameter just for your session:orcl> connect scott/tiger
Connected.
orcl> alter session set "_optimizer_max_permutations"=1000;
Session altered.
orcl>
|
|
|
Re: Long query with WITH not responding [message #685028 is a reply to message #685025] |
Tue, 12 October 2021 07:51   |
 |
Reversi72
Messages: 10 Registered: October 2021
|
Junior Member |
|
|
CREATE TABLE DCS_MIFR_DFP_EOLICO
(
ID NUMBER(16),
LETTURA_ORIG DATE,
LETTURA DATE,
CODICE_UP NVARCHAR2(20),
CENSIMP NVARCHAR2(20),
ID_ANEMO NUMBER(10),
INTENSITA_VENTO NUMBER(5,2),
DIREZIONE_VENTO NUMBER(3),
TEMPERATURA_ARIA NUMBER(4,2),
UMIDITA_RELATIVA NUMBER(3),
PRESSIONE_ATM NUMBER(4),
FONTE NUMBER(2),
VERSIONE NUMBER(4),
VALIDO NUMBER(1),
DATAINSERIMENTO DATE,
DST NUMBER(1),
VALIDO_DAL DATE,
VALIDO_AL DATE
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_COD_ANAG_UP
(
ID_COD_ANAG NUMBER,
S_COD_IMPIANTO VARCHAR2(17 BYTE),
S_COD_CENSIMP VARCHAR2(17 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_MISURE_MENS
(
N_ID_MISURA NUMBER,
S_COD_DISTRIB VARCHAR2(3 BYTE),
N_ANNO_RIF NUMBER(4),
N_MESE_RIF NUMBER(2),
S_COD_IMPIANTO VARCHAR2(17 BYTE),
S_COD_CENSIMP VARCHAR2(17 BYTE),
S_CODICE_MISURA VARCHAR2(30 BYTE),
N_VERSIONE NUMBER(5),
N_RECENTE NUMBER(1),
GBL_ID RAW(16),
D_DATA_CREAZIONE DATE,
N_TOT_ENERGIA NUMBER,
D_DATA_ACQUIS DATE,
D_DATA_VALIDAZIONE DATE,
N_FLG_UTILIZZATA NUMBER(1),
N_GRADO_INVALIDITA NUMBER,
N_STATO NUMBER(3),
N_STATO_ACQUIS NUMBER(2),
S_UTENTE_MODIFICA VARCHAR2(50 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_MISURE_MENS_DETT
(
N_ID_DETT NUMBER,
N_ID_MISURA NUMBER,
D_DATA_ORA DATE,
N_ENERGIA NUMBER,
D_DATA_CREAZIONE DATE
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_OPE_ASSETTO
(
ID_OPE_ASSETTO RAW(16),
D_DATA_INS DATE,
DOC_ALLEGATO BLOB,
S_NOME_FILE VARCHAR2(255 BYTE),
S_EXT VARCHAR2(5 BYTE),
ID_CONVENZIONE NUMBER,
S_UTENTE_WEB VARCHAR2(50 BYTE),
N_FLG_CONSOLIDATO INTEGER,
S_NOME_ASSETTO VARCHAR2(200 BYTE),
N_RECENTE NUMBER(1)
)
LOB (DOC_ALLEGATO) STORE AS (
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
LOGGING
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
))
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_OPE_ASSETTO_DETT
(
ID_OPE_ASSETTO RAW(16),
ID_COD_ANAG NUMBER,
ID_ANE NUMBER,
IDGENERATORE NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_ORDINI_DISP
(
N_ID_ORDINI_DISP NUMBER,
N_ANNO_RIF NUMBER(4),
N_MESE_RIF NUMBER(2),
S_COD_IMPIANTO VARCHAR2(17 BYTE),
S_COD_CENSIMP VARCHAR2(17 BYTE),
D_DATA_INI_VARIAZIONE DATE,
D_DATA_FINE_VARIAZIONE DATE,
S_ORA_INI_VARIAZIONE VARCHAR2(5 BYTE),
S_ORA_FINE_VARIAZIONE VARCHAR2(5 BYTE),
N_POT_LIMITAZIONE NUMBER(6),
N_INDICE_AFF NUMBER(3,2),
N_VERSIONE NUMBER(5),
N_RECENTE NUMBER(1),
ID_COD_ANAG NUMBER,
S_COD_ORDINE VARCHAR2(20 BYTE),
D_DATA_INS DATE,
GBL_ID RAW(16),
D_DATA_CREAZIONE DATE,
N_FLG_UTILIZZATA NUMBER(1)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_ORDINI_DISP_DETT
(
N_ID_DETT NUMBER,
N_ID_ORDINE NUMBER,
D_DATA_ORA DATE,
N_POTENZA NUMBER(10,3),
D_DATA_INSERIMENTO DATE,
DEFF NUMBER,
INDICE_AFFIDABILITA NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_STO_AG_INDISP_DETT
(
ID_STO_AG_INDISP_DETT RAW(16),
D_DATA_ORA DATE,
N_P_INDISPONIBILE NUMBER(10,3),
N_PERCENTUALE_POT NUMBER(8,4),
D_DATA_INS DATE,
ID_COD_ANAG NUMBER,
ID_STO_ALLEGATO RAW(16),
ID_MPE_STO_AG_INDISP RAW(16),
N_RECENTE NUMBER(1),
IDGENERATORE NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_STO_AG_MISURE_DETT
(
D_DATA_RIF DATE,
D_DATA_INS DATE,
N_MISURA NUMBER,
ID_COD_ANAG NUMBER,
ID_STO_AG_MISURE_DETT RAW(16),
ID_STO_ALLEGATO RAW(16),
N_RECENTE NUMBER(1),
IDGENERATORE NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_STO_AG_VENTO_DETT
(
ID_STO_ALLEGATO RAW(16),
D_DATA DATE,
N_VELOCITA_VENTO NUMBER(10,2),
N_DIREZIONE_VENTO NUMBER(3),
N_TEMPERATURA_VENTO NUMBER(10,2),
D_DATA_INS DATE,
ID_COD_ANAG NUMBER,
ID_ANE NUMBER,
ID_STO_AG_VENTO_DETT RAW(16),
ID_MPE_STO_AG_VENTO RAW(16)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_STO_INDISP_DETT
(
ID_STO_INDISP_DETT RAW(16),
D_DATA_ORA DATE,
N_P_INDISPONIBILE NUMBER(10,3),
D_DATA_INS DATE,
ID_COD_ANAG NUMBER,
ID_STO_ALLEGATO RAW(16),
ID_MPE_STO_INDISP RAW(16),
N_RECENTE NUMBER(1),
N_PERCENTUALE_POT NUMBER(8,4)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_STO_MISURE_DETT
(
D_DATA_RIF DATE,
D_DATA_INS DATE,
N_MISURA NUMBER,
ID_COD_ANAG NUMBER,
ID_STO_MISURE_DETT RAW(16),
ID_STO_ALLEGATO RAW(16),
N_RECENTE NUMBER(1)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_MPE_STO_VENTO_DETT
(
ID_STO_ALLEGATO RAW(16),
D_DATA DATE,
N_VELOCITA_VENTO NUMBER(10,2),
N_DIREZIONE_VENTO NUMBER(3),
N_TEMPERATURA_VENTO NUMBER(10,2),
D_DATA_INS DATE,
ID_COD_ANAG NUMBER,
ID_ANE NUMBER,
ID_STO_VENTO_DETT RAW(16),
ID_MPE_STO_VENTO RAW(16)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_T_ANA_ANEMOMETRI
(
ID_ANE NUMBER,
MATRICOLA_ANEMOMETRO VARCHAR2(100 BYTE),
MODELLO_ANEMOMETRO VARCHAR2(100 BYTE),
ANE_LATITUDINE NUMBER(14,10),
ANE_LONGITUDINE NUMBER(14,10),
ANE_TIPO NUMBER(1),
ANE_TELELETTO NUMBER(1),
ANE_TELEFONO VARCHAR2(15 BYTE),
ANE_ALTEZZA NUMBER(10,3),
ANE_ALTITUDINE NUMBER(10,3),
ANE_MATRICOLA_LOGGER VARCHAR2(60 BYTE),
ANE_TIPO_LOGGER VARCHAR2(100 BYTE),
ANE_DATA_AVVIO_LOGGER DATE,
ANE_COD_STAZIONE VARCHAR2(500 BYTE),
ANE_DATA_CERTIFICAZIONE DATE,
ANE_IS_CALIBRATO NUMBER(1),
ANE_DATA_INSERIMENTO DATE,
ANE_STATO NUMBER(1),
ANE_CENSIMP VARCHAR2(50 BYTE),
ANE_CODICE_UP VARCHAR2(100 BYTE),
ANE_MORF_TERRENO VARCHAR2(2 BYTE),
ANE_NOME_IMPIANTO VARCHAR2(300 BYTE),
ANE_NOTE VARCHAR2(4000 BYTE),
ANE_RAGIONE_SOCIALE VARCHAR2(300 BYTE),
ANE_RECENTE NUMBER(1),
ANE_UTENTE_MOD VARCHAR2(200 BYTE),
ANE_VALIDO_AL DATE,
ANE_VALIDO_DAL DATE,
IDENTIFICATIVO_ANE NUMBER,
ANE_IS_SINCRO NUMBER(1),
ANE_ALIMENTAZIONE VARCHAR2(100 BYTE),
ANE_ALTEZZA_MINI_SONDE VARCHAR2(300 BYTE),
ANE_CAMPO_LIBERO VARCHAR2(4000 BYTE),
ANE_DATI_SU_SCADA NUMBER(1),
ANE_DI_RIF NUMBER(1),
ANE_FUSO_APP NUMBER(2),
ANE_LAT_UTM_ED50 VARCHAR2(100 BYTE),
ANE_LON_UTM_ED50 VARCHAR2(100 BYTE),
ANE_MODELLO_LOGGER VARCHAR2(100 BYTE),
ANE_MODEM_TYPE VARCHAR2(100 BYTE),
ANE_PRES_DATA_LOGGER NUMBER(1),
ANE_PROTOCOL VARCHAR2(4000 BYTE),
ANE_SERIAL_NUMBER VARCHAR2(100 BYTE),
ANE_DI_RIF_COD_UP VARCHAR2(4000 BYTE),
ID_FILE NUMBER,
ANE_DST NUMBER(1),
TIME_ZONE NUMBER(5)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_T_ANA_EOLICO
(
CODICEUP VARCHAR2(25 BYTE),
LATITUDINE NUMBER(14,10),
LONGITUDINE NUMBER(14,10),
ALTITUDINE NUMBER,
IDGENERATORE NUMBER,
NOMEGENERATORE VARCHAR2(50 BYTE),
POTENZAGENERATORE NUMBER,
ALTEZZA NUMBER,
ALTEZZA_TOP NUMBER,
MODELLO VARCHAR2(50 BYTE),
DIAMETRO_TURBINA NUMBER(3),
NUM_PALE NUMBER(1),
SOPRA_VENTO NUMBER(1),
VELOCITA_AVVIO NUMBER,
VELOCITA_STOP NUMBER,
STATO NUMBER(1),
IS_SINCRONIZZATO NUMBER(1),
DATA_INSERIMENTO DATE,
CODICE_CENSIMP VARCHAR2(50 BYTE),
IDENTIFICATIVO_GSE VARCHAR2(8 BYTE),
ID_CONFIG NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_T_ASSETTO_ELAB_CONS
(
ID_ASSETTO NUMBER,
NOME_ASSETTO VARCHAR2(200 BYTE),
CODICE_UP VARCHAR2(200 BYTE),
IDENTIFICATIVO_ANE NUMBER,
IDENTIFICATIVO_AERO VARCHAR2(10 BYTE),
ID_RIGA NUMBER,
ID_INT_AERO NUMBER,
ID_INT_ANE NUMBER,
DATA_INS DATE
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_T_PROG_INDISP_MANC_PROD
(
CODICEUP VARCHAR2(100 BYTE),
STARTDATE DATE,
ENDDATE DATE,
PINDISPONIBILE NUMBER(10,3),
TIPO_INDISP NUMBER(3),
NOTE VARCHAR2(4000 BYTE),
INSERITO_DA VARCHAR2(300 BYTE),
ID_INDISP VARCHAR2(20 BYTE),
IDINTERNO NUMBER,
ANNO NUMBER(4),
MESE NUMBER(2),
SORGENTE_DATO VARCHAR2(60 BYTE),
VERSIONE NUMBER,
DATA_INSERIMENTO DATE,
MODALITA NUMBER(2),
UTILIZZATA_CALCOLI NUMBER(1),
STATO NUMBER(1),
PERCENTUALE_POT NUMBER(6,3)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE TABLE DCS_T_PROG_INDISP_MANC_PROD_D
(
ID_DETT NUMBER,
ID_INDISP NUMBER,
DATA_ORA DATE,
POTENZA NUMBER(10,3),
DATA_INSERIMENTO DATE,
PERCENTUALE NUMBER(8,4)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
/*----------------------------------------------------------------------------
Le query in questa sezione sono di utilità generica per le successive query
-----------------------------------------------------------------------------*/
WITH DatePrompt /*trasforma le 2 date ricevute in input come parametri*/
AS (SELECT TO_DATE ('01' || SUBSTR ( :DataMeseDa, 3, 8 ), 'dd/mm/yyyy')
AS MyDataMeseDa, /*trasformo il testo-data ricevuto in input in una data in formato 01/mm/yyyy 00:00:00*/
LAST_DAY (TO_DATE ( :DataMeseA, 'dd/mm/yyyy'))
+ 1
- 1 / 86400
AS MyDataMeseA /*trasformo il testo-data ricevuto in input in una data in formato 31/mm/yyyy 23:59:59*/
FROM DUAL),
ListaMesiAnno /*elenco dei mesi/anni comprsi fra le 2 date in input*/
AS ( SELECT ADD_MONTHS (TRUNC (MyDataMeseDa, 'MM'), ROWNUM - 1)
AS MeseAnno
FROM DatePrompt
CONNECT BY ROWNUM <=
MONTHS_BETWEEN (TRUNC (MyDataMeseA, 'MM'),
TRUNC (MyDataMeseDa, 'MM'))
+ 1),
MwPotUp
AS (SELECT SUM (PotenzaGeneratore) / 1000 val
FROM dcs_t_ana_eolico
WHERE codiceup = :CodAnag
AND id_config = (SELECT MAX (id_config)
FROM dcs_t_ana_eolico
WHERE codiceup = :CodAnag)),
CheckNoParAneomometri
/*Restituisce 1 se sono tutti NULL i parametri in input :IdAne,:IdAssettoGSE e :IdAssettoOpe, altrimenti 0
Da utilizzare quando si estraggono i dati vento, serve a stabilire se quest'ultimo vanno estratti per uno o più specifici anemometri o per tuttto l'impianto*/
AS (SELECT COUNT(*) val
FROM dual
WHERE :IdAne IS NULL
AND :IdAssettoGSE IS NULL
AND :IdAssettoOpe IS NULL),
ListaAneAG
/*In funzione dei parametri in input (:IdAne NOT NULL oppure :IdAssettoGSE NOT NULL oppure :IdAssettoOpe NOT NULL) individua l'elenco delle coppie Anemometro/Aerogeneratore di interesse,
prelevandole dai dati operativi o dai dati storici
N.B. L'estrazione si basa sul presupporto che i parametri IdAssettoGSE e :IdAssettoOpe nonn possono essere contemporaneamente valorizzati*/
AS ( SELECT NULL IdAG,
:IdAne IdAne,
NULL idgeneratore,
NULL id_ane,
NULL nome_assetto
FROM dual /*Se in input è stato passato uno specifico anemometro (parametro :IdAne valorizzato), la query restituisce solo quell'identificativo di anemometro*/
WHERE :IdAne IS NOT NULL
AND :IdAssettoGSE IS NULL
AND :IdAssettoOpe IS NULL
UNION
SELECT DISTINCT /*Se in input è stato passato un'assetto validato da GSE (parametro IdAssettoGSE valorizzato), la query restituisce l'elenco degli identificativi di anemometro che appartengono a quell'assetto*/
a.identificativo_aero IdAG,
a.identificativo_ane IdAne,
a.id_int_aero idgeneratore,
a.id_int_ane id_ane,
a.nome_assetto nome_assetto
FROM dcs_T_ASSETTO_ELAB_CONS a
WHERE a.ID_ASSETTO = :IdAssettoGSE /*492*/
AND :IdAne IS NULL
AND :IdAssettoGSE IS NOT NULL
AND :IdAssettoOpe IS NULL
UNION
SELECT DISTINCT /*Se in input è stato passato un'assetto Operatore (parametro IdAssettoOpe valorizzato), la query restituisce l'elenco degli identificativi di aerogeneratore che appartengono a quell'assetto*/
ag.identificativo_gse IdAG,
a.identificativo_ane IdAne,
assd.idgeneratore,
assd.id_ane,
ass.s_nome_assetto nome_assetto
FROM dcs_mpe_ope_assetto ass,
dcs_mpe_ope_assetto_dett assd,
dcs_t_ana_eolico ag,
dcs_t_ana_anemometri a
WHERE ass.id_ope_assetto = HEXTORAW(:IdAssettoOpe) /*C1BC9F38F6190112E0530AA01523B9A3*/
AND ass.id_ope_assetto = assd.id_ope_assetto
AND assd.id_ane = a.id_ane
AND assd.idgeneratore = ag.idgeneratore
AND :IdAne IS NULL
AND :IdAssettoGSE IS NULL
AND :IdAssettoOpe IS NOT NULL
),
pPGreco
AS ( SELECT 3.14159265358979 val FROM dual),
DatiVentoDataOra
AS (SELECT DISTINCT * FROM (
/*dati operativi*/
SELECT DISTINCT
TRUNC (vo.lettura, 'HH24') DataOraVento,
DECODE (NVL (vo.direzione_vento, 0), 0, 0, 1) Flag3D
FROM dcs_MIFR_DFP_EOLICO vo
WHERE vo.valido = 1
AND ( (vo.fonte IN (0, 2) AND :FlagRianalisi IS NULL)
OR
(vo.fonte = 5 AND :FlagRianalisi IS NOT NULL)
)
AND vo.codice_up = :CodAnag /*codice_up corrisponde al campo MPE_COD_ANAG_UP.S_COD_IMPIANTO*/
AND vo.id_anemo = NVL(:IdAne, vo.id_anemo) /*id_anemo corrisponde al campo T_ANA_ANEMOMETRI.IDENTIFICATIVO_ANE*/
UNION ALL
/*dati storici*/
SELECT TRUNC (vs.d_data, 'HH24') DataOraVento,
DECODE (NVL (vs.n_direzione_vento, 0), 0, 0, 1) Flag3D
FROM dcs_mpe_sto_vento_dett vs,
dcs_mpe_cod_anag_up UP,
dcs_t_ana_anemometri a
WHERE vs.id_cod_anag = UP.id_cod_anag
AND vs.id_ane = a.id_ane(+)
AND UP.s_cod_impianto = :CodAnag
AND a.identificativo_ane = NVL(:IdAne, a.identificativo_ane)
AND :FlagRianalisi IS NULL)),
Pre1DatiVentoDataOraAG
AS (SELECT DISTINCT * FROM (
/*dati operativi*/
SELECT TRUNC (vo.lettura, 'HH24') DataOraVento,
DECODE (NVL (vo.direzione_vento, 0), 0, 0, 1) Flag3D,
l.IdAne
FROM dcs_MIFR_DFP_EOLICO vo,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne FROM ListaAneAG) l
WHERE vo.valido = 1
AND vo.fonte IN (0, 2, 5)
AND vo.codice_up = :CodAnag /*codice_up corrisponde al campo MPE_COD_ANAG_UP.S_COD_IMPIANTO*/
AND vo.id_anemo = l.IdAne (+) /*id_anemo corrisponde al campo T_ANA_ANEMOMETRI.IDENTIFICATIVO_ANE*/
AND ( chk.val = 1
OR
l.IdAne IS NOT NULL)
UNION ALL
/*dati storici*/
SELECT TRUNC (vs.d_data, 'HH24') DataOraVento,
DECODE (NVL (vs.n_direzione_vento, 0), 0, 0, 1) Flag3D,
l.IdAne
FROM dcs_mpe_sto_ag_vento_dett vs,
dcs_mpe_cod_anag_up UP,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne, id_ane FROM ListaAneAG) l
WHERE vs.id_cod_anag = UP.id_cod_anag
AND vs.id_ane = l.id_ane(+)
AND UP.s_cod_impianto = :CodAnag
AND ( chk.val = 1
OR
l.IdAne IS NOT NULL))
),
Pre2DatiVentoDataOraAG
AS (SELECT dv.DataOraVento, COUNT(DISTINCT dv.IdAne) AS IdAne
FROM Pre1DatiVentoDataOraAG dv
GROUP BY dv.DataOraVento
HAVING COUNT(DISTINCT dv.IdAne) = (SELECT COUNT(DISTINCT IdAne) FROM ListaAneAG)
),
DatiVentoDataOraAG
AS (SELECT DISTINCT dv.DataOraVento,
dv.Flag3D
FROM Pre1DatiVentoDataOraAG dv,
CheckNoParAneomometri chk,
Pre2DatiVentoDataOraAG dv2
WHERE (chk.val = 1 OR dv2.DataOraVento IS NOT NULL)
AND dv.DataOraVento = dv2.DataOraVento (+)
),
DatiMisureDataOra /*Curve A e C: Estrazione delle date disponibili a sistema per le misure*/
AS ( /*dati Misurati*/
SELECT TRUNC (mmd.d_data_ora, 'HH24') DataOraMisura
FROM dcs_MPE_MISURE_MENS mm, dcs_MPE_MISURE_MENS_DETT mmd
WHERE mm.n_recente = 1
AND mm.n_id_misura = mmd.n_id_misura
AND mm.s_cod_impianto = :CodAnag
UNION
/*dati storici*/
SELECT TRUNC (ms.d_data_rif, 'HH24') DataOraMisura
FROM dcs_mpe_sto_misure_dett ms, dcs_mpe_cod_anag_up UP
WHERE ms.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = :CodAnag
AND ms.n_recente = 1),
DatiMisureDataOraAG /*Curva B: Estrazione delle date disponibili a sistema per le misure*/
AS (/*dati storici*/
SELECT TRUNC (ms.d_data_rif, 'HH24') DataOraMisura
FROM dcs_mpe_sto_ag_misure_dett ms, dcs_mpe_cod_anag_up UP, (SELECT DISTINCT idgeneratore FROM ListaAneAG) l
WHERE ms.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = :CodAnag
AND ms.n_recente = 1
AND ms.idgeneratore = l.idgeneratore),
DatiIndispDataOra
AS ( /*dati operativi*/
SELECT TRUNC (indd.data_ora, 'HH24') DataOraIndisp
FROM ( SELECT codiceup, anno, mese, MAX(versione) versione
FROM dcs_T_PROG_INDISP_MANC_PROD
WHERE codiceup = :CodAnag
GROUP BY codiceup, anno, mese
) PilotaInd,
dcs_T_PROG_INDISP_MANC_PROD ind,
dcs_T_PROG_INDISP_MANC_PROD_D indd
WHERE PilotaInd.codiceup = ind.codiceup
AND PilotaInd.anno = ind.anno
AND PilotaInd.mese = ind.mese
AND PilotaInd.versione = ind.versione
AND ind.idinterno = indd.id_indisp
UNION
/*dati storici*/
SELECT TRUNC (inds.d_data_ora, 'HH24') DataOraIndisp
FROM dcs_mpe_sto_indisp_dett inds, dcs_mpe_cod_anag_up UP
WHERE inds.n_recente = 1
AND inds.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = :CodAnag),
DatiIndispDataOraAG
AS (/*dati storici*/
SELECT TRUNC (inds.d_data_ora, 'HH24') DataOraIndisp
FROM dcs_mpe_sto_ag_indisp_dett inds, dcs_mpe_cod_anag_up UP, (SELECT DISTINCT idgeneratore FROM ListaAneAG) l
WHERE inds.n_recente = 1
AND inds.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = :CodAnag
AND inds.idgeneratore = l.idgeneratore
UNION
SELECT DataOraIndisp FROM DatiIndispDataOra),
DatiOddDataOra
AS (SELECT TRUNC (od.d_data_ora, 'HH24') DataOraOdd
FROM dcs_mpe_ordini_disp o, dcs_mpe_ordini_disp_dett od, DatePrompt
WHERE o.n_id_ordini_disp = od.n_id_ordine
AND o.S_COD_IMPIANTO = :CodAnag
AND o.N_RECENTE = 1
AND od.d_data_ora BETWEEN MyDataMeseDa AND MyDataMeseA),
MinMaxDatiVento /*query propedeutica alla RPT_DISP_DATI_VENTO_2D_3D*/
AS ( SELECT MIN (DataOraVento) DataMin,
MAX (DataOraVento) DataMax,
Flag3D
FROM DatiVentoDataOra
GROUP BY Flag3D),
RPT_DISP_DATI_VENTO_2D_3D
AS /*
Report Disponibilità dei Dati Vento (2D+3D) e dei Dati Vento solo 3D
Restituisce 2 righe
Le informazioni restituite fanno parte di 2 diversi raggruppamenti:
(a) uno per i dati vento 2D+3D (FlagSoloConDirezione=0)
(b) uno per i soli dati vento 3D (FlagSoloConDirezione=1)
*/
(SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
0 FlagSoloConDirezione
FROM MinMaxDatiVento
UNION ALL
SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
1 FlagSoloConDirezione
FROM MinMaxDatiVento
WHERE Flag3D = 1),
MinMaxDatiVentoAG /*query propedeutica alla RPT_DISP_DATI_VENTO_2D_3D_AG*/
AS ( SELECT MIN (DataOraVento) DataMin,
MAX (DataOraVento) DataMax,
Flag3D
FROM DatiVentoDataOraAG
GROUP BY Flag3D),
RPT_DISP_DATI_VENTO_2D_3D_AG
AS /*
Report Disponibilità dei Dati Vento (2D+3D) e dei Dati Vento solo 3D
Restituisce 2 righe
Le informazioni restituite fanno parte di 2 diversi raggruppamenti:
(a) uno per i dati vento 2D+3D (FlagSoloConDirezione=0)
(b) uno per i soli dati vento 3D (FlagSoloConDirezione=1)
*/
(SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
0 FlagSoloConDirezione
FROM MinMaxDatiVentoAG
UNION ALL
SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
1 FlagSoloConDirezione
FROM MinMaxDatiVentoAG
WHERE Flag3D = 1),
RPT_DISP_DATI_MISURE
AS /*Report Disponibilità dei Dati delle Misure per impianto*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiMisureDataOra),
RPT_DISP_DATI_MISURE_AG
AS /*Report Disponibilità dei Dati delle Misure per aerogfeneratori dell'assetto*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiMisureDataOraAG),
RPT_DISP_DATI_INDISP
AS /*Report Disponibilità dei Dati delle Indisponibilita per impianto*/
(SELECT MIN (DataOraIndisp) DataMin, MAX (DataOraIndisp) DataMax
FROM DatiIndispDataOra),
RPT_DISP_DATI_INDISP_AG
AS /*Report Disponibilità dei Dati delle Indisponibilita per aerogfeneratori dell'assetto (include quelle dell'impianto)*/
(SELECT MIN (DataOraIndisp) DataMin, MAX (DataOraIndisp) DataMax
FROM DatiIndispDataOraAG),
RPT_DISP_DATI_2D
AS /*Report Disponibilità dei Dati per produzione delle curve 2D */
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOra v, DatiMisureDataOra m
WHERE v.DataOraVento = m.DataOraMisura),
RPT_DISP_DATI_2D_AG
AS /*Report Disponibilità dei Dati per produzione delle curve 2D */
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOraAG v, DatiMisureDataOraAG m
WHERE v.DataOraVento = m.DataOraMisura),
RPT_DISP_DATI_3D
AS /*Report Disponibilità dei Dati per produzione delle curve 3D*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOra v, DatiMisureDataOra m
WHERE v.DataOraVento = m.DataOraMisura AND v.Flag3D = 1),
RPT_DISP_DATI_3D_AG
AS /*Report Disponibilità dei Dati per produzione delle curve 3D*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOraAG v, DatiMisureDataOraAG m
WHERE v.DataOraVento = m.DataOraMisura AND v.Flag3D = 1)
/*
CURVA_A_PART2_DISP
La seguente query mette insieme tutte le RPT_DISP%, utili al calcolo delle statistiche per la curva A, presenti nella query CURVE_PART1_DISP_PRES
e viene interrogata da cruscotto per riportare in mascherale date minima e massima calcolate per ogni ambito dati*/
SELECT 'V3D' Flusso, DataMin, DataMax
FROM RPT_DISP_DATI_VENTO_2D_3D
WHERE FlagSoloConDirezione = 1
UNION ALL
SELECT 'VEN' Flusso, DataMin, DataMax
FROM RPT_DISP_DATI_VENTO_2D_3D
WHERE FlagSoloConDirezione = 0
UNION ALL
SELECT 'MIS' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_MISURE
UNION ALL
SELECT 'IND' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_INDISP
UNION ALL
SELECT 'C2D' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_2D
UNION ALL
SELECT 'C3D' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_3D;
[Edit MC: add code tags]
[Updated on: Tue, 12 October 2021 07:58] by Moderator Report message to a moderator
|
|
|
|
Re: Long query with WITH not responding [message #685030 is a reply to message #685028] |
Tue, 12 October 2021 08:04   |
John Watson
Messages: 8803 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
On my PC, after creating your tables the statement goes through EXPLAIN PLAN in 0.13 seconds. This is release 19.3, just a little database created with general purpose template. sga_target is 1g. This is the plan:Explained.
Elapsed: 00:00:00.13
orclz>
orclz> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1046375083
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 156 | 25 (4)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D66A0_1DBD34 | | | | |
| 3 | HASH UNIQUE | | 2 | 24 | 10 (20)| 00:00:01 |
| 4 | VIEW | | 2 | 24 | 9 (12)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
| 6 | HASH UNIQUE | | 1 | 83 | 3 (34)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | DCS_MIFR_DFP_EOLICO | 1 | 83 | 2 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
|* 9 | HASH JOIN | | 1 | 97 | 6 (0)| 00:00:01 |
| 10 | MERGE JOIN CARTESIAN | | 1 | 49 | 4 (0)| 00:00:01 |
|* 11 | TABLE ACCESS FULL | DCS_MPE_COD_ANAG_UP | 1 | 23 | 2 (0)| 00:00:01 |
| 12 | BUFFER SORT | | 1 | 26 | 2 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | DCS_T_ANA_ANEMOMETRI | 1 | 26 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | DCS_MPE_STO_VENTO_DETT | 1 | 48 | 2 (0)| 00:00:01 |
| 15 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D66A1_1DBD34 | | | | |
| 16 | SORT UNIQUE | | 2 | 232 | 8 (0)| 00:00:01 |
| 17 | UNION-ALL | | | | | |
|* 18 | HASH JOIN | | 1 | 58 | 4 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | DCS_MPE_MISURE_MENS | 1 | 36 | 2 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | DCS_MPE_MISURE_MENS_DETT | 1 | 22 | 2 (0)| 00:00:01 |
|* 21 | HASH JOIN SEMI | | 1 | 58 | 4 (0)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | DCS_MPE_STO_MISURE_DETT | 1 | 35 | 2 (0)| 00:00:01 |
|* 23 | TABLE ACCESS FULL | DCS_MPE_COD_ANAG_UP | 1 | 23 | 2 (0)| 00:00:01 |
| 24 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D66A2_1DBD34 | | | | |
| 25 | HASH GROUP BY | | 1 | 12 | 3 (34)| 00:00:01 |
| 26 | VIEW | | 2 | 24 | 2 (0)| 00:00:01 |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A0_1DBD34 | 2 | 24 | 2 (0)| 00:00:01 |
| 28 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D66A3_1DBD34 | | | | |
| 29 | UNION-ALL | | | | | |
| 30 | SORT AGGREGATE | | 1 | 18 | | |
| 31 | VIEW | | 2 | 36 | 2 (0)| 00:00:01 |
| 32 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A2_1DBD34 | 2 | 24 | 2 (0)| 00:00:01 |
| 33 | SORT AGGREGATE | | 1 | 21 | | |
|* 34 | VIEW | | 2 | 42 | 2 (0)| 00:00:01 |
| 35 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A2_1DBD34 | 2 | 24 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
| 36 | UNION-ALL | | | | | |
|* 37 | VIEW | | 2 | 42 | 2 (0)| 00:00:01 |
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A3_1DBD34 | 2 | 38 | 2 (0)| 00:00:01 |
|* 39 | VIEW | | 2 | 42 | 2 (0)| 00:00:01 |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A3_1DBD34 | 2 | 38 | 2 (0)| 00:00:01 |
| 41 | VIEW | | 1 | 18 | 2 (0)| 00:00:01 |
| 42 | SORT AGGREGATE | | 1 | 9 | | |
| 43 | VIEW | | 2 | 18 | 2 (0)| 00:00:01 |
| 44 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A1_1DBD34 | 2 | 18 | 2 (0)| 00:00:01 |
| 45 | VIEW | | 1 | 18 | 11 (10)| 00:00:01 |
| 46 | SORT AGGREGATE | | 1 | 9 | | |
| 47 | VIEW | | 2 | 18 | 11 (10)| 00:00:01 |
| 48 | SORT UNIQUE | | 2 | 275 | 11 (10)| 00:00:01 |
| 49 | UNION-ALL | | | | | |
|* 50 | HASH JOIN | | 1 | 217 | 7 (15)| 00:00:01 |
| 51 | NESTED LOOPS | | 1 | 195 | 5 (20)| 00:00:01 |
| 52 | VIEW | | 1 | 91 | 3 (34)| 00:00:01 |
| 53 | HASH GROUP BY | | 1 | 91 | 3 (34)| 00:00:01 |
|* 54 | TABLE ACCESS FULL | DCS_T_PROG_INDISP_MANC_PROD | 1 | 91 | 2 (0)| 00:00:01 |
|* 55 | TABLE ACCESS FULL | DCS_T_PROG_INDISP_MANC_PROD | 1 | 104 | 2 (0)| 00:00:01 |
| 56 | TABLE ACCESS FULL | DCS_T_PROG_INDISP_MANC_PROD_D | 1 | 22 | 2 (0)| 00:00:01 |
|* 57 | HASH JOIN SEMI | | 1 | 58 | 4 (0)| 00:00:01 |
|* 58 | TABLE ACCESS FULL | DCS_MPE_STO_INDISP_DETT | 1 | 35 | 2 (0)| 00:00:01 |
|* 59 | TABLE ACCESS FULL | DCS_MPE_COD_ANAG_UP | 1 | 23 | 2 (0)| 00:00:01 |
| 60 | VIEW | | 1 | 18 | 4 (0)| 00:00:01 |
| 61 | SORT AGGREGATE | | 1 | 18 | | |
|* 62 | HASH JOIN SEMI | | 2 | 36 | 4 (0)| 00:00:01 |
| 63 | VIEW | | 2 | 18 | 2 (0)| 00:00:01 |
| 64 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A0_1DBD34 | 2 | 24 | 2 (0)| 00:00:01 |
| 65 | VIEW | | 2 | 18 | 2 (0)| 00:00:01 |
| 66 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A1_1DBD34 | 2 | 18 | 2 (0)| 00:00:01 |
| 67 | VIEW | | 1 | 18 | 4 (0)| 00:00:01 |
| 68 | SORT AGGREGATE | | 1 | 21 | | |
|* 69 | HASH JOIN SEMI | | 2 | 42 | 4 (0)| 00:00:01 |
|* 70 | VIEW | | 2 | 24 | 2 (0)| 00:00:01 |
| 71 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A0_1DBD34 | 2 | 24 | 2 (0)| 00:00:01 |
| 72 | VIEW | | 2 | 18 | 2 (0)| 00:00:01 |
| 73 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66A1_1DBD34 | 2 | 18 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
7 - filter((:FLAGRIANALISI IS NULL AND ("VO"."FONTE"=0 OR "VO"."FONTE"=2) OR :FLAGRIANALISI IS NOT NULL AND
"VO"."FONTE"=5) AND "VO"."VALIDO"=1 AND "VO"."CODICE_UP"=SYS_OP_C2C(:CODANAG) AND
"VO"."ID_ANEMO"=NVL(:IDANE,"VO"."ID_ANEMO"))
8 - filter(:FLAGRIANALISI IS NULL)
9 - access("VS"."ID_COD_ANAG"="UP"."ID_COD_ANAG" AND "VS"."ID_ANE"="A"."ID_ANE")
11 - filter("UP"."S_COD_IMPIANTO"=:CODANAG)
13 - filter("A"."IDENTIFICATIVO_ANE"=NVL(:IDANE,"A"."IDENTIFICATIVO_ANE"))
18 - access("MM"."N_ID_MISURA"="MMD"."N_ID_MISURA")
19 - filter("MM"."N_RECENTE"=1 AND "MM"."S_COD_IMPIANTO"=:CODANAG)
21 - access("MS"."ID_COD_ANAG"="UP"."ID_COD_ANAG")
22 - filter("MS"."N_RECENTE"=1)
23 - filter("UP"."S_COD_IMPIANTO"=:CODANAG)
34 - filter("FLAG3D"=1)
37 - filter("FLAGSOLOCONDIREZIONE"=1)
39 - filter("FLAGSOLOCONDIREZIONE"=0)
50 - access("IND"."IDINTERNO"="INDD"."ID_INDISP")
54 - filter("CODICEUP"=:CODANAG)
55 - filter("PILOTAIND"."VERSIONE"="IND"."VERSIONE" AND "PILOTAIND"."CODICEUP"="IND"."CODICEUP" AND
"PILOTAIND"."ANNO"="IND"."ANNO" AND "PILOTAIND"."MESE"="IND"."MESE")
57 - access("INDS"."ID_COD_ANAG"="UP"."ID_COD_ANAG")
58 - filter("INDS"."N_RECENTE"=1)
59 - filter("UP"."S_COD_IMPIANTO"=:CODANAG)
62 - access("V"."DATAORAVENTO"="M"."DATAORAMISURA")
69 - access("V"."DATAORAVENTO"="M"."DATAORAMISURA")
70 - filter("V"."FLAG3D"=1)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
113 rows selected.
Elapsed: 00:00:00.27
orclz>
|
|
|
|
|
|
|
Re: Long query with WITH not responding [message #685035 is a reply to message #685032] |
Tue, 12 October 2021 14:48   |
John Watson
Messages: 8803 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You could try setting optimizer_mode=rule for your session (or hint it in the statement) which will prevent the CBO from doing any parsing. The plan you get might be rubbish, but it will be developed quickly.
|
|
|
|
|
Re: Long query with WITH not responding [message #685041 is a reply to message #685040] |
Wed, 13 October 2021 02:26   |
 |
Michel Cadot
Messages: 68413 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Trace files are stored on the server, either in (name between <> are parameters) "<diagnostic_dest>/diag/rdbms/<db_name>/<instance_name>/trace" or in "<background_dump_dest>".
The site you mentioned is b......t, sometimes the information are correct (mostly when they copy them from other sites), sometimes (often) they are wrong, but you can't know when. Avoid searching in it.
Before using 10053 trace, as Ed said, use a SQL (or 10046) trace then use TKPROF to analyze it.
All the steps are explained there.
The output of TKPROF will tell you if the time is spent during parse, execute or fetch steps.
If it is in parse step then you can use 10053 trace to see what the optimizer does.
|
|
|
Re: Long query with WITH not responding [message #685042 is a reply to message #685040] |
Wed, 13 October 2021 02:28   |
 |
Reversi72
Messages: 10 Registered: October 2021
|
Junior Member |
|
|
this is the result I get using the Toad Tracker
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:16:45
explain plan set statement_id='Administrator:101321091645' into TOAD_PLAN_TABLE For WITH
DatePrompt /*trasforma le 2 date ricevute in input come parametri*/
AS (SELECT TO_DATE ('01' || SUBSTR ( :DataMeseDa, 3, 8), 'dd/mm/yyyy')
AS MyDataMeseDa, /*trasformo il testo-data ricevuto in input in una data in formato 01/mm/yyyy 00:00:00*/
LAST_DAY (TO_DATE ( :DataMeseA, 'dd/mm/yyyy'))
+ 1
- 1 / 86400
AS MyDataMeseA /*trasformo il testo-data ricevuto in input in una data in formato 31/mm/yyyy 23:59:59*/
FROM DUAL),
ListaMesiAnno /*elenco dei mesi/anni comprsi fra le 2 date in input*/
AS ( SELECT ADD_MONTHS (TRUNC (MyDataMeseDa, 'MM'), ROWNUM - 1)
AS MeseAnno
FROM DatePrompt
CONNECT BY ROWNUM <=
MONTHS_BETWEEN (TRUNC (MyDataMeseA, 'MM'),
TRUNC (MyDataMeseDa, 'MM'))
+ 1),
MwPotUp
AS (SELECT SUM (PotenzaGeneratore) / 1000 val
FROM dcs_t_ana_eolico
WHERE codiceup = :CodAnag
AND id_config = (SELECT MAX (id_config)
FROM dcs_t_ana_eolico
WHERE codiceup = :CodAnag)),
CheckNoParAneomometri
/*Restituisce 1 se sono tutti NULL i parametri in input :IdAne,:IdAssettoGSE e :IdAssettoOpe, altrimenti 0
Da utilizzare quando si estraggono i dati vento, serve a stabilire se quest'ultimo vanno estratti per uno o più specifici anemometri o per tuttto l'impianto*/
AS (SELECT COUNT(*) val
FROM dual
WHERE :IdAne IS NULL
AND :IdAssettoGSE IS NULL
AND :IdAssettoOpe IS NULL),
ListaAneAG
/*In funzione dei parametri in input (:IdAne NOT NULL oppure :IdAssettoGSE NOT NULL oppure :IdAssettoOpe NOT NULL) individua l'elenco delle coppie Anemometro/Aerogeneratore di interesse,
prelevandole dai dati operativi o dai dati storici
N.B. L'estrazione si basa sul presupporto che i parametri IdAssettoGSE e :IdAssettoOpe nonn possono essere contemporaneamente valorizzati*/
AS ( SELECT NULL IdAG,
:IdAne IdAne,
NULL idgeneratore,
NULL id_ane,
NULL nome_assetto
FROM dual /*Se in input è stato passato uno specifico anemometro (parametro :IdAne valorizzato), la query restituisce solo quell'identificativo di anemometro*/
WHERE :IdAne IS NOT NULL
AND :IdAssettoGSE IS NULL
AND :IdAssettoOpe IS NULL
UNION
SELECT DISTINCT /*Se in input è stato passato un'assetto validato da GSE (parametro IdAssettoGSE valorizzato), la query restituisce l'elenco degli identificativi di anemometro che appartengono a quell'assetto*/
a.identificativo_aero IdAG,
a.identificativo_ane IdAne,
a.id_int_aero idgeneratore,
a.id_int_ane id_ane,
a.nome_assetto nome_assetto
FROM dcs_T_ASSETTO_ELAB_CONS a
WHERE a.ID_ASSETTO = :IdAssettoGSE /*492*/
AND :IdAne IS NULL
AND :IdAssettoGSE IS NOT NULL
AND :IdAssettoOpe IS NULL
UNION
SELECT DISTINCT /*Se in input è stato passato un'assetto Operatore (parametro IdAssettoOpe valorizzato), la query restituisce l'elenco degli identificativi di aerogeneratore che appartengono a quell'assetto*/
ag.identificativo_gse IdAG,
a.identificativo_ane IdAne,
assd.idgeneratore,
assd.id_ane,
ass.s_nome_assetto nome_assetto
FROM dcs_mpe_ope_assetto ass,
dcs_mpe_ope_assetto_dett assd,
dcs_t_ana_eolico ag,
dcs_t_ana_anemometri a
WHERE ass.id_ope_assetto = HEXTORAW(:IdAssettoOpe) /*C1BC9F38F6190112E0530AA01523B9A3*/
AND ass.id_ope_assetto = assd.id_ope_assetto
AND assd.id_ane = a.id_ane
AND assd.idgeneratore = ag.idgeneratore
AND :IdAne IS NULL
AND :IdAssettoGSE IS NULL
AND :IdAssettoOpe IS NOT NULL
),
pPGreco
AS ( SELECT 3.14159265358979 val FROM dual),
DatiVentoDataOra
AS (SELECT DISTINCT * FROM (
/*dati operativi*/
SELECT DISTINCT
TRUNC (vo.lettura, 'HH24') DataOraVento,
DECODE (NVL (vo.direzione_vento, 0), 0, 0, 1) Flag3D
FROM dcs_MIFR_DFP_EOLICO vo
WHERE vo.valido = 1
AND ( (vo.fonte IN (0, 2) AND :FlagRianalisi IS NULL)
OR
(vo.fonte = 5 AND :FlagRianalisi IS NOT NULL)
)
AND vo.codice_up = :CodAnag /*codice_up corrisponde al campo MPE_COD_ANAG_UP.S_COD_IMPIANTO*/
AND vo.id_anemo = NVL(:IdAne, vo.id_anemo) /*id_anemo corrisponde al campo T_ANA_ANEMOMETRI.IDENTIFICATIVO_ANE*/
UNION ALL
/*dati storici*/
SELECT TRUNC (vs.d_data, 'HH24') DataOraVento,
DECODE (NVL (vs.n_direzione_vento, 0), 0, 0, 1) Flag3D
FROM dcs_mpe_sto_vento_dett vs,
dcs_mpe_cod_anag_up UP,
dcs_t_ana_anemometri a
WHERE vs.id_cod_anag = UP.id_cod_anag
AND vs.id_ane = a.id_ane(+)
AND UP.s_cod_impianto = :CodAnag
AND a.identificativo_ane = NVL(:IdAne, a.identificativo_ane)
AND :FlagRianalisi IS NULL)),
Pre1DatiVentoDataOraAG
AS (SELECT DISTINCT * FROM (
/*dati operativi*/
SELECT TRUNC (vo.lettura, 'HH24') DataOraVento,
DECODE (NVL (vo.direzione_vento, 0), 0, 0, 1) Flag3D,
l.IdAne
FROM dcs_MIFR_DFP_EOLICO vo,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne FROM ListaAneAG) l
WHERE vo.valido = 1
AND vo.fonte IN (0, 2, 5)
AND vo.codice_up = :CodAnag /*codice_up corrisponde al campo MPE_COD_ANAG_UP.S_COD_IMPIANTO*/
AND vo.id_anemo = l.IdAne (+) /*id_anemo corrisponde al campo T_ANA_ANEMOMETRI.IDENTIFICATIVO_ANE*/
AND ( chk.val = 1
OR
l.IdAne IS NOT NULL)
UNION ALL
/*dati storici*/
SELECT TRUNC (vs.d_data, 'HH24') DataOraVento,
DECODE (NVL (vs.n_direzione_vento, 0), 0, 0, 1) Flag3D,
l.IdAne
FROM dcs_mpe_sto_ag_vento_dett vs,
dcs_mpe_cod_anag_up UP,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne, id_ane FROM ListaAneAG) l
WHERE vs.id_cod_anag = UP.id_cod_anag
AND vs.id_ane = l.id_ane(+)
AND UP.s_cod_impianto = :CodAnag
AND ( chk.val = 1
OR
l.IdAne IS NOT NULL))
),
Pre2DatiVentoDataOraAG
AS (SELECT dv.DataOraVento, COUNT(DISTINCT dv.IdAne) AS IdAne
FROM Pre1DatiVentoDataOraAG dv
GROUP BY dv.DataOraVento
HAVING COUNT(DISTINCT dv.IdAne) = (SELECT COUNT(DISTINCT IdAne) FROM ListaAneAG)
),
DatiVentoDataOraAG
AS (SELECT DISTINCT dv.DataOraVento,
dv.Flag3D
FROM Pre1DatiVentoDataOraAG dv,
CheckNoParAneomometri chk,
Pre2DatiVentoDataOraAG dv2
WHERE (chk.val = 1 OR dv2.DataOraVento IS NOT NULL)
AND dv.DataOraVento = dv2.DataOraVento (+)
),
DatiMisureDataOra /*Curve A e C: Estrazione delle date disponibili a sistema per le misure*/
AS ( /*dati Misurati*/
SELECT TRUNC (mmd.d_data_ora, 'HH24') DataOraMisura
FROM dcs_MPE_MISURE_MENS mm, dcs_MPE_MISURE_MENS_DETT mmd
WHERE mm.n_recente = 1
AND mm.n_id_misura = mmd.n_id_misura
AND mm.s_cod_impianto = :CodAnag
UNION
/*dati storici*/
SELECT TRUNC (ms.d_data_rif, 'HH24') DataOraMisura
FROM dcs_mpe_sto_misure_dett ms, dcs_mpe_cod_anag_up UP
WHERE ms.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = :CodAnag
AND ms.n_recente = 1),
DatiMisureDataOraAG /*Curva B: Estrazione delle date disponibili a sistema per le misure*/
AS (/*dati storici*/
SELECT TRUNC (ms.d_data_rif, 'HH24') DataOraMisura
FROM dcs_mpe_sto_ag_misure_dett ms, dcs_mpe_cod_anag_up UP, (SELECT DISTINCT idgeneratore FROM ListaAneAG) l
WHERE ms.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = :CodAnag
AND ms.n_recente = 1
AND ms.idgeneratore = l.idgeneratore),
DatiIndispDataOra
AS ( /*dati operativi*/
SELECT TRUNC (indd.data_ora, 'HH24') DataOraIndisp
FROM ( SELECT codiceup, anno, mese, MAX(versione) versione
FROM dcs_T_PROG_INDISP_MANC_PROD
WHERE codiceup = :CodAnag
GROUP BY codiceup, anno, mese
) PilotaInd,
dcs_T_PROG_INDISP_MANC_PROD ind,
dcs_T_PROG_INDISP_MANC_PROD_D indd
WHERE PilotaInd.codiceup = ind.codiceup
AND PilotaInd.anno = ind.anno
AND PilotaInd.mese = ind.mese
AND PilotaInd.versione = ind.versione
AND ind.idinterno = indd.id_indisp
UNION
/*dati storici*/
SELECT TRUNC (inds.d_data_ora, 'HH24') DataOraIndisp
FROM dcs_mpe_sto_indisp_dett inds, dcs_mpe_cod_anag_up UP
WHERE inds.n_recente = 1
AND inds.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = :CodAnag),
DatiIndispDataOraAG
AS (/*dati storici*/
SELECT TRUNC (inds.d_data_ora, 'HH24') DataOraIndisp
FROM dcs_mpe_sto_ag_indisp_dett inds, dcs_mpe_cod_anag_up UP, (SELECT DISTINCT idgeneratore FROM ListaAneAG) l
WHERE inds.n_recente = 1
AND inds.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = :CodAnag
AND inds.idgeneratore = l.idgeneratore
UNION
SELECT DataOraIndisp FROM DatiIndispDataOra),
DatiOddDataOra
AS (SELECT TRUNC (od.d_data_ora, 'HH24') DataOraOdd
FROM dcs_mpe_ordini_disp o, dcs_mpe_ordini_disp_dett od, DatePrompt
WHERE o.n_id_ordini_disp = od.n_id_ordine
AND o.S_COD_IMPIANTO = :CodAnag
AND o.N_RECENTE = 1
AND od.d_data_ora BETWEEN MyDataMeseDa AND MyDataMeseA),
MinMaxDatiVento /*query propedeutica alla RPT_DISP_DATI_VENTO_2D_3D*/
AS ( SELECT MIN (DataOraVento) DataMin,
MAX (DataOraVento) DataMax,
Flag3D
FROM DatiVentoDataOra
GROUP BY Flag3D),
RPT_DISP_DATI_VENTO_2D_3D
AS /*
Report Disponibilità dei Dati Vento (2D+3D) e dei Dati Vento solo 3D
Restituisce 2 righe
Le informazioni restituite fanno parte di 2 diversi raggruppamenti:
(a) uno per i dati vento 2D+3D (FlagSoloConDirezione=0)
(b) uno per i soli dati vento 3D (FlagSoloConDirezione=1)
*/
(SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
0 FlagSoloConDirezione
FROM MinMaxDatiVento
UNION ALL
SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
1 FlagSoloConDirezione
FROM MinMaxDatiVento
WHERE Flag3D = 1),
MinMaxDatiVentoAG /*query propedeutica alla RPT_DISP_DATI_VENTO_2D_3D_AG*/
AS ( SELECT MIN (DataOraVento) DataMin,
MAX (DataOraVento) DataMax,
Flag3D
FROM DatiVentoDataOraAG
GROUP BY Flag3D),
RPT_DISP_DATI_VENTO_2D_3D_AG
AS /*
Report Disponibilità dei Dati Vento (2D+3D) e dei Dati Vento solo 3D
Restituisce 2 righe
Le informazioni restituite fanno parte di 2 diversi raggruppamenti:
(a) uno per i dati vento 2D+3D (FlagSoloConDirezione=0)
(b) uno per i soli dati vento 3D (FlagSoloConDirezione=1)
*/
(SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
0 FlagSoloConDirezione
FROM MinMaxDatiVentoAG
UNION ALL
SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
1 FlagSoloConDirezione
FROM MinMaxDatiVentoAG
WHERE Flag3D = 1),
RPT_DISP_DATI_MISURE
AS /*Report Disponibilità dei Dati delle Misure per impianto*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiMisureDataOra),
RPT_DISP_DATI_MISURE_AG
AS /*Report Disponibilità dei Dati delle Misure per aerogfeneratori dell'assetto*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiMisureDataOraAG),
RPT_DISP_DATI_INDISP
AS /*Report Disponibilità dei Dati delle Indisponibilita per impianto*/
(SELECT MIN (DataOraIndisp) DataMin, MAX (DataOraIndisp) DataMax
FROM DatiIndispDataOra),
RPT_DISP_DATI_INDISP_AG
AS /*Report Disponibilità dei Dati delle Indisponibilita per aerogfeneratori dell'assetto (include quelle dell'impianto)*/
(SELECT MIN (DataOraIndisp) DataMin, MAX (DataOraIndisp) DataMax
FROM DatiIndispDataOraAG),
RPT_DISP_DATI_2D
AS /*Report Disponibilità dei Dati per produzione delle curve 2D */
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOra v, DatiMisureDataOra m
WHERE v.DataOraVento = m.DataOraMisura),
RPT_DISP_DATI_2D_AG
AS /*Report Disponibilità dei Dati per produzione delle curve 2D */
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOraAG v, DatiMisureDataOraAG m
WHERE v.DataOraVento = m.DataOraMisura),
RPT_DISP_DATI_3D
AS /*Report Disponibilità dei Dati per produzione delle curve 3D*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOra v, DatiMisureDataOra m
WHERE v.DataOraVento = m.DataOraMisura AND v.Flag3D = 1),
RPT_DISP_DATI_3D_AG
AS /*Report Disponibilità dei Dati per produzione delle curve 3D*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOraAG v, DatiMisureDataOraAG m
WHERE v.DataOraVento = m.DataOraMisura AND v.Flag3D = 1)
/*
CURVA_A_PART2_DISP
La seguente query mette insieme tutte le RPT_DISP%, utili al calcolo delle statistiche per la curva A, presenti nella query CURVE_PART1_DISP_PRES
e viene interrogata da cruscotto per riportare in mascherale date minima e massima calcolate per ogni ambito dati*/
SELECT
'V3D' Flusso, DataMin, DataMax
FROM RPT_DISP_DATI_VENTO_2D_3D
WHERE FlagSoloConDirezione = 1
UNION ALL
SELECT
'VEN' Flusso, DataMin, DataMax
FROM RPT_DISP_DATI_VENTO_2D_3D
WHERE FlagSoloConDirezione = 0
UNION ALL
SELECT
'MIS' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_MISURE
UNION ALL
SELECT
'IND' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_INDISP
UNION ALL
SELECT
'C2D' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_2D
UNION ALL
SELECT
'C3D' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_3D
Elapsed time: 171.022
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:19:36
Select *
From TOAD_PLAN_TABLE
Where statement_id = 'Administrator:101321091645'
order by id
STATEMENT_ID=['Administrator:101321091645']
Elapsed time: 0.019
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:20:36
Successful logon (0x1da52ae8): [WPOWERDB@GSESVIL11G]
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:20:36
declare cursor NlsParamsCursor is SELECT * FROM nls_session_parameters;begin SELECT Nvl(Lengthb(Chr(16777216)), Nvl(Lengthb(Chr(65536)), Nvl(Lengthb(Chr(256)), 1))), Nvl(Lengthb(Chr(1)), 1) INTO :MaxCharLength, :MinCharLength FROM dual; for NlsRecord in NlsParamsCursor loop if NlsRecord.parameter = 'NLS_DATE_LANGUAGE' then :NlsDateLanguage := NlsRecord.value; elsif NlsRecord.parameter = 'NLS_DATE_FORMAT' then :NlsDateFormat := NlsRecord.value; elsif NlsRecord.parameter = 'NLS_NUMERIC_CHARACTERS' then :NlsNumericCharacters := NlsRecord.value; elsif NlsRecord.parameter = 'NLS_TIMESTAMP_FORMAT' then :NlsTimeStampFormat := NlsRecord.value; elsif NlsRecord.parameter = 'NLS_TIMESTAMP_TZ_FORMAT' then :NlsTimeStampTZFormat := NlsRecord.value; end if; end loop;end;
MaxCharLength=[1]
MinCharLength=[1]
NlsDateLanguage=['ITALIAN']
NlsDateFormat=['DD-MON-RR']
NlsNumericCharacters=[',.']
NlsTimeStampFormat=['DD-MON-RR HH24:MI:SSXFF']
NlsTimeStampTZFormat=['DD-MON-RR HH24:MI:SSXFF TZR']
Elapsed time: 0.026
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:20:36
begin sys.dbms_application_info.set_module('TOAD background query session', null); end;
Elapsed time: 0.019
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:20:36
BEGIN DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;
Elapsed time: 0.019
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:20:36
BEGIN DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;
Elapsed time: 0.019
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:20:36
Select *
From TOAD_PLAN_TABLE
Where statement_id = 'Administrator:101321091645'
order by id
Describe Only
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:20:36
Select *
From TOAD_PLAN_TABLE
Where statement_id = 'Administrator:101321091645'
order by id
Describe Only
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:20:36
Select *
From TOAD_PLAN_TABLE
Where statement_id = 'Administrator:101321091645'
order by id
Elapsed time: 0.047
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:20:36
begin dbms_output.get_line(line => :line, status => :status); end;
status=[1]
Elapsed time: 0.019
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:20:36
BEGIN DBMS_OUTPUT.DISABLE; END;
Elapsed time: 0.019
--------------------------------------------------------------------------------
Timestamp: 13/10/2021 09:20:36
BEGIN DBMS_OUTPUT.DISABLE; END;
Elapsed time: 0.020
|
|
|
|
Re: Long query with WITH not responding [message #685051 is a reply to message #685044] |
Thu, 14 October 2021 03:50   |
 |
Reversi72
Messages: 10 Registered: October 2021
|
Junior Member |
|
|
Michel Cadot wrote on Wed, 13 October 2021 04:01
I don't know TOAD Tracker and don't trust it, use Oracle tools as said above.
below the trace
Trace file /oracleDB/app/oracle/diag/rdbms/gsesvil/GSESVIL/trace/GSESVIL_ora_13500910.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracleDB/app/oracle/product/11.2.0/dbhome_1
System name: AIX
Node name: gsesvil11g
Release: 2
Version: 7
Machine: 00FBC6D44C00
Instance name: GSESVIL
Redo thread mounted by this instance: 1
Oracle process number: 103
Unix process pid: 13500910, image: oracle@gsesvil11g
*** 2021-10-14 10:17:38.350
*** SESSION ID:(219.48829) 2021-10-14 10:17:38.350
*** CLIENT ID:() 2021-10-14 10:17:38.350
*** SERVICE NAME:(GSESVIL) 2021-10-14 10:17:38.350
*** MODULE NAME:(TOAD 13.2.0.258) 2021-10-14 10:17:38.350
*** ACTION NAME:(43392832,404951328,361410480) 2021-10-14 10:17:38.350
CLOSE #4575354896:c=12,e=39,dep=0,type=1,tim=56034610495168
WAIT #0: nam='SQL*Net more data from client' ela= 32 driver id=1413697536 #bytes=249 p3=0 obj#=40 tim=56034610497609
WAIT #0: nam='SQL*Net more data from client' ela= 11 driver id=1413697536 #bytes=84 p3=0 obj#=40 tim=56034610497635
=====================
PARSING IN CURSOR #4574946192 len=789 dep=1 uid=0 oct=3 lid=0 tim=56034610534933 hv=3159716790 ad='7000002075b7e58' sqlid='7ng34ruy5awxq'
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#
END OF STMT
BINDS #4574946192:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b81558 bln=22 avl=04 flg=05
value=335485
Bind#1
No oacdef for this bind.
EXEC #4574946192:c=79,e=249,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034610535114
FETCH #4574946192:c=103,e=328,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034610535462
=====================
PARSING IN CURSOR #4575350088 len=74 dep=1 uid=0 oct=3 lid=0 tim=56034610535512 hv=3309402135 ad='70000020697f8a0' sqlid='5n1fs4m2n2y0r'
select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1
END OF STMT
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b869d0 bln=22 avl=04 flg=05
value=335735
EXEC #4575350088:c=31,e=95,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034610535595
FETCH #4575350088:c=34,e=8152,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034610543758
FETCH #4575350088:c=3,e=10,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034610543934
CLOSE #4575350088:c=1,e=4,dep=1,type=3,tim=56034610543982
FETCH #4574946192:c=5,e=18,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034610544018
CLOSE #4574946192:c=0,e=1,dep=1,type=3,tim=56034610544049
=====================
PARSING IN CURSOR #4573663824 len=348 dep=1 uid=0 oct=3 lid=0 tim=56034610544153 hv=2512561537 ad='70000020894b560' sqlid='83taa7kaw59c1'
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#
END OF STMT
BINDS #4573663824:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b81558 bln=22 avl=04 flg=05
value=335485
EXEC #4573663824:c=71,e=226,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034610544311
FETCH #4573663824:c=39,e=123,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544447
FETCH #4573663824:c=2,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544470
FETCH #4573663824:c=2,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544489
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544505
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544521
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544537
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544553
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544569
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544585
FETCH #4573663824:c=12,e=52,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544650
FETCH #4573663824:c=2,e=7,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544812
FETCH #4573663824:c=1,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544833
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544850
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544867
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544884
FETCH #4573663824:c=5,e=16,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544913
FETCH #4573663824:c=7,e=23,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544950
FETCH #4573663824:c=1,e=5,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544969
FETCH #4573663824:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610544992
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545008
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545023
FETCH #4573663824:c=1,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545039
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545055
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545071
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545086
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545102
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545117
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545133
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545148
FETCH #4573663824:c=1,e=6,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545167
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545183
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545199
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545214
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545230
FETCH #4573663824:c=1,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545245
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545261
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545277
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545292
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545308
FETCH #4573663824:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545323
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545339
FETCH #4573663824:c=1,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545354
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545407
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545423
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545439
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545454
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610545470
FETCH #4573663824:c=5,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034610545496
CLOSE #4573663824:c=1,e=2,dep=1,type=3,tim=56034610545529
BINDS #4574946192:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b81558 bln=22 avl=04 flg=05
value=336387
Bind#1
No oacdef for this bind.
EXEC #4574946192:c=65,e=206,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034610554580
FETCH #4574946192:c=106,e=395,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034610554993
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b869d0 bln=22 avl=04 flg=05
value=336389
EXEC #4575350088:c=20,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034610555096
FETCH #4575350088:c=4,e=13,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034610555120
CLOSE #4575350088:c=0,e=2,dep=1,type=3,tim=56034610555152
FETCH #4574946192:c=3,e=9,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034610555174
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b869d0 bln=22 avl=04 flg=05
value=336390
EXEC #4575350088:c=17,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034610555245
FETCH #4575350088:c=2,e=7,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034610555263
FETCH #4575350088:c=1,e=4,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034610555283
CLOSE #4575350088:c=1,e=0,dep=1,type=3,tim=56034610555298
FETCH #4574946192:c=1,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034610555311
CLOSE #4574946192:c=0,e=1,dep=1,type=3,tim=56034610555331
BINDS #4573663824:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b81558 bln=22 avl=04 flg=05
value=336387
EXEC #4573663824:c=20,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034610555413
FETCH #4573663824:c=12,e=35,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610555458
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610555477
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610555494
FETCH #4573663824:c=7,e=27,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610563908
FETCH #4573663824:c=1,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610563955
FETCH #4573663824:c=1,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610563973
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610563990
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610564006
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610564024
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610564040
FETCH #4573663824:c=7,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034610564075
CLOSE #4573663824:c=1,e=2,dep=1,type=3,tim=56034610564114
=====================
PARSING IN CURSOR #4573187536 len=169 dep=1 uid=0 oct=3 lid=0 tim=56034610564221 hv=1173719687 ad='70000020697b9d0' sqlid='6aq34nj2zb2n7'
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#
END OF STMT
BINDS #4573187536:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b81558 bln=22 avl=04 flg=05
value=336387
EXEC #4573187536:c=72,e=231,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3286988581,tim=56034610564399
FETCH #4573187536:c=17,e=55,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=3286988581,tim=56034610564468
CLOSE #4573187536:c=0,e=0,dep=1,type=3,tim=56034610564490
=====================
PARSING IN CURSOR #4573181944 len=151 dep=1 uid=0 oct=3 lid=0 tim=56034610564523 hv=4139184264 ad='7000002058beaf8' sqlid='2q93zsrvbdw48'
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#
END OF STMT
BINDS #4573181944:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b81558 bln=22 avl=04 flg=05
value=336387
EXEC #4573181944:c=31,e=97,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3286988581,tim=56034610564609
FETCH #4573181944:c=3,e=9,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=3286988581,tim=56034610564629
CLOSE #4573181944:c=0,e=1,dep=1,type=3,tim=56034610564645
=====================
PARSING IN CURSOR #4573176352 len=141 dep=1 uid=0 oct=3 lid=0 tim=56034610564697 hv=793605046 ad='7000002075a4ea0' sqlid='7nuw4xwrnuwxq'
select col#,intcol#,toid,version#,packed,intcols,intcol#s,flags, synobj#, nvl(typidcol#, 0) from coltype$ where obj#=:1 order by intcol# desc
END OF STMT
BINDS #4573176352:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110951ca8 bln=22 avl=04 flg=05
value=336387
EXEC #4573176352:c=31,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1089920582,tim=56034610564783
FETCH #4573176352:c=13,e=40,p=0,cr=4,cu=0,mis=0,r=0,dep=1,og=4,plh=1089920582,tim=56034610564836
CLOSE #4573176352:c=1,e=0,dep=1,type=3,tim=56034610564852
=====================
PARSING IN CURSOR #4573695464 len=117 dep=1 uid=0 oct=3 lid=0 tim=56034610564888 hv=3141366592 ad='7000002058861a8' sqlid='9rfqm06xmuwu0'
select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltype$ where obj#=:1 order by intcol# asc
END OF STMT
BINDS #4573695464:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=1109d0870 bln=22 avl=04 flg=05
value=336387
EXEC #4573695464:c=29,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=156215980,tim=56034610564969
FETCH #4573695464:c=4,e=13,p=0,cr=4,cu=0,mis=0,r=0,dep=1,og=4,plh=156215980,tim=56034610564995
CLOSE #4573695464:c=0,e=1,dep=1,type=3,tim=56034610565012
=====================
PARSING IN CURSOR #4573689520 len=71 dep=1 uid=0 oct=3 lid=0 tim=56034610565039 hv=2470471216 ad='700000205885518' sqlid='f3g84j69n0tjh'
select col#,intcol#,ntab# from ntab$ where obj#=:1 order by intcol# asc
END OF STMT
BINDS #4573689520:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=1109cf268 bln=22 avl=04 flg=05
value=336387
EXEC #4573689520:c=28,e=88,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=760139525,tim=56034610565119
FETCH #4573689520:c=4,e=14,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=760139525,tim=56034610565147
CLOSE #4573689520:c=0,e=1,dep=1,type=3,tim=56034610565163
=====================
PARSING IN CURSOR #4575359856 len=191 dep=1 uid=0 oct=3 lid=0 tim=56034610574919 hv=1930451687 ad='7000002069710f0' sqlid='6qz82dptj0qr7'
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
END OF STMT
PARSE #4575359856:c=312,e=9736,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=56034610574917
BINDS #4575359856:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2b2b0 bln=22 avl=04 flg=05
value=336387
EXEC #4575359856:c=616,e=10240,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=2205639461,tim=56034610585315
FETCH #4575359856:c=31,e=97,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2205639461,tim=56034610585464
FETCH #4575359856:c=4,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2205639461,tim=56034610585547
STAT #4575359856 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=4 pr=0 pw=0 time=104 us cost=3 size=100 card=2)'
STAT #4575359856 id=2 cnt=1 pid=1 pos=1 obj=80 op='TABLE ACCESS CLUSTER LOB$ (cr=4 pr=0 pw=0 time=69 us cost=2 size=100 card=2)'
STAT #4575359856 id=3 cnt=1 pid=2 pos=1 obj=3 op='INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=34 us cost=1 size=0 card=1)'
CLOSE #4575359856:c=2,e=9,dep=1,type=0,tim=56034610594314
=====================
PARSING IN CURSOR #4573682704 len=90 dep=1 uid=0 oct=3 lid=0 tim=56034610594415 hv=673844243 ad='700000206970870' sqlid='9g485acn2n30m'
select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by intcol# asc
END OF STMT
BINDS #4573682704:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=1109cd698 bln=22 avl=04 flg=05
value=336387
EXEC #4573682704:c=43,e=135,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3937008439,tim=56034610594516
FETCH #4573682704:c=12,e=39,p=0,cr=4,cu=0,mis=0,r=0,dep=1,og=4,plh=3937008439,tim=56034610594572
CLOSE #4573682704:c=1,e=1,dep=1,type=3,tim=56034610594593
=====================
PARSING IN CURSOR #4573676760 len=86 dep=1 uid=0 oct=3 lid=0 tim=56034610594842 hv=2195287067 ad='70000020696fdf0' sqlid='32bhha21dkv0v'
select col#,intcol#,charsetid,charsetform from col$ where obj#=:1 order by intcol# asc
END OF STMT
BINDS #4573676760:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=1109cbf60 bln=22 avl=04 flg=05
value=336387
EXEC #4573676760:c=58,e=186,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034610594977
FETCH #4573676760:c=30,e=97,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610595097
FETCH #4573676760:c=1,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610595118
FETCH #4573676760:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610595133
FETCH #4573676760:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610595147
FETCH #4573676760:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610595161
FETCH #4573676760:c=1,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610595175
FETCH #4573676760:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610595188
FETCH #4573676760:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610595201
FETCH #4573676760:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610595215
FETCH #4573676760:c=1,e=1,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034610595228
FETCH #4573676760:c=3,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034610595252
CLOSE #4573676760:c=0,e=1,dep=1,type=3,tim=56034610595277
=====================
PARSING IN CURSOR #4573670816 len=119 dep=1 uid=0 oct=3 lid=0 tim=56034610595316 hv=166324347 ad='70000020696f560' sqlid='0fr8zhn4ymu3v'
select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum from opqtype$ where obj# = :1 order by intcol# asc
END OF STMT
BINDS #4573670816:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=1109ca828 bln=22 avl=04 flg=05
value=336387
EXEC #4573670816:c=31,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3355330683,tim=56034610595401
FETCH #4573670816:c=5,e=16,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=3355330683,tim=56034610595430
CLOSE #4573670816:c=0,e=1,dep=1,type=3,tim=56034610595446
WAIT #4575074768: nam='single-task message' ela= 360314 p1=0 p2=0 p3=0 obj#=40 tim=56034610985327
WAIT #4575074768: nam='SQL*Net message from dblink' ela= 942 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034610994856
WAIT #4575074768: nam='SQL*Net message to dblink' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034610994901
WAIT #4575074768: nam='SQL*Net message from dblink' ela= 9793 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611004752
WAIT #4575074768: nam='SQL*Net message to dblink' ela= 33 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611013972
WAIT #4575074768: nam='SQL*Net message from dblink' ela= 11042 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611025096
WAIT #4575074768: nam='SQL*Net message to dblink' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611044617
WAIT #4575074768: nam='SQL*Net message from dblink' ela= 30775 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611075486
WAIT #4575074768: nam='SQL*Net message to dblink' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611083806
WAIT #4575074768: nam='SQL*Net message from dblink' ela= 166 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611084038
WAIT #4575074768: nam='SQL*Net message to dblink' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611084101
WAIT #4575074768: nam='SQL*Net message from dblink' ela= 753 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611084869
WAIT #4575074768: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611085220
WAIT #4575074768: nam='SQL*Net message from dblink' ela= 75 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611085313
WAIT #4575074768: nam='SQL*Net message to dblink' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611085343
WAIT #4575074768: nam='SQL*Net message from dblink' ela= 29148 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611114508
WAIT #4575074768: nam='SQL*Net message to dblink' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611115021
WAIT #4575074768: nam='SQL*Net message from dblink' ela= 19896 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611134949
WAIT #4575074768: nam='SQL*Net message to dblink' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611143764
WAIT #4575074768: nam='SQL*Net message from dblink' ela= 470 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611144331
WAIT #4575074768: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611144505
WAIT #4575074768: nam='SQL*Net message from dblink' ela= 253 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=56034611144772
BINDS #4574946192:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=335513
Bind#1
No oacdef for this bind.
EXEC #4574946192:c=65,e=207,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611145492
FETCH #4574946192:c=153,e=8600,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611154121
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2de38 bln=22 avl=04 flg=05
value=335710
EXEC #4575350088:c=38,e=127,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611154386
FETCH #4575350088:c=8,e=25,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611154427
FETCH #4575350088:c=1,e=5,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611154449
CLOSE #4575350088:c=1,e=3,dep=1,type=3,tim=56034611154481
FETCH #4574946192:c=2,e=6,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611154500
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2de38 bln=22 avl=04 flg=05
value=335711
EXEC #4575350088:c=18,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611154575
FETCH #4575350088:c=2,e=7,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611154592
FETCH #4575350088:c=1,e=3,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611154613
CLOSE #4575350088:c=0,e=1,dep=1,type=3,tim=56034611154629
FETCH #4574946192:c=9,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611154669
CLOSE #4574946192:c=0,e=0,dep=1,type=3,tim=56034611154692
BINDS #4573663824:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=335513
EXEC #4573663824:c=43,e=135,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611154849
FETCH #4573663824:c=12,e=39,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611154899
FETCH #4573663824:c=1,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611154920
FETCH #4573663824:c=2,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611154937
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611154953
FETCH #4573663824:c=2,e=8,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611154974
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611154991
CLOSE #4573663824:c=0,e=1,dep=1,type=3,tim=56034611155007
BINDS #4574946192:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=336418
Bind#1
No oacdef for this bind.
EXEC #4574946192:c=73,e=233,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611164096
FETCH #4574946192:c=103,e=326,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611164441
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2de38 bln=22 avl=04 flg=05
value=336426
EXEC #4575350088:c=19,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611164545
FETCH #4575350088:c=5,e=16,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611164572
FETCH #4575350088:c=1,e=4,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611164593
CLOSE #4575350088:c=0,e=1,dep=1,type=3,tim=56034611164621
FETCH #4574946192:c=3,e=7,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611164640
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2de38 bln=22 avl=04 flg=05
value=362709
EXEC #4575350088:c=17,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611164712
FETCH #4575350088:c=2,e=8,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611164730
FETCH #4575350088:c=0,e=3,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611164747
CLOSE #4575350088:c=0,e=0,dep=1,type=3,tim=56034611164761
FETCH #4574946192:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611164775
CLOSE #4574946192:c=0,e=1,dep=1,type=3,tim=56034611164794
BINDS #4573663824:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=336418
EXEC #4573663824:c=21,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611164876
FETCH #4573663824:c=11,e=34,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611164921
FETCH #4573663824:c=3,e=8,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611164945
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611164963
FETCH #4573663824:c=2,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611164980
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611164996
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611165012
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611165028
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611165046
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611165065
CLOSE #4573663824:c=0,e=1,dep=1,type=3,tim=56034611165081
BINDS #4574946192:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=335463
Bind#1
No oacdef for this bind.
EXEC #4574946192:c=26,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611165422
FETCH #4574946192:c=90,e=8369,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611173802
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2de38 bln=22 avl=04 flg=05
value=335894
EXEC #4575350088:c=39,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611174111
FETCH #4575350088:c=10,e=32,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611174159
FETCH #4575350088:c=3,e=7,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611174186
CLOSE #4575350088:c=0,e=2,dep=1,type=3,tim=56034611174216
FETCH #4574946192:c=2,e=7,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611174237
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2de38 bln=22 avl=04 flg=05
value=336429
EXEC #4575350088:c=18,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611174314
FETCH #4575350088:c=3,e=10,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611174335
FETCH #4575350088:c=2,e=5,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611174357
FETCH #4575350088:c=5,e=18,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611174389
CLOSE #4575350088:c=0,e=1,dep=1,type=3,tim=56034611174405
FETCH #4574946192:c=3,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611174424
CLOSE #4574946192:c=0,e=1,dep=1,type=3,tim=56034611174447
BINDS #4573663824:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=335463
EXEC #4573663824:c=28,e=90,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611174557
FETCH #4573663824:c=25,e=197,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611174764
FETCH #4573663824:c=1,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611174818
FETCH #4573663824:c=1,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611174837
FETCH #4573663824:c=1,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611174855
FETCH #4573663824:c=4,e=13,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611174881
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611174898
FETCH #4573663824:c=2,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611174917
CLOSE #4573663824:c=1,e=1,dep=1,type=3,tim=56034611174939
BINDS #4574946192:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=336416
Bind#1
No oacdef for this bind.
EXEC #4574946192:c=37,e=118,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611175586
FETCH #4574946192:c=131,e=8381,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611183979
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2de38 bln=22 avl=04 flg=05
value=336424
EXEC #4575350088:c=34,e=107,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611184206
FETCH #4575350088:c=6,e=18,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611184240
FETCH #4575350088:c=1,e=4,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611184263
CLOSE #4575350088:c=1,e=1,dep=1,type=3,tim=56034611184293
FETCH #4574946192:c=2,e=6,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611184314
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2de38 bln=22 avl=04 flg=05
value=362713
EXEC #4575350088:c=19,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611184394
FETCH #4575350088:c=3,e=8,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611184413
FETCH #4575350088:c=5,e=14,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611184444
CLOSE #4575350088:c=1,e=1,dep=1,type=3,tim=56034611184460
FETCH #4574946192:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611184474
CLOSE #4574946192:c=0,e=1,dep=1,type=3,tim=56034611184498
BINDS #4573663824:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=336416
EXEC #4573663824:c=32,e=122,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611184683
FETCH #4573663824:c=12,e=38,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611184734
FETCH #4573663824:c=1,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611184753
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611184770
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611184786
FETCH #4573663824:c=2,e=8,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611184807
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611184824
FETCH #4573663824:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611184840
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611184855
FETCH #4573663824:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611184871
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611184887
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611184903
CLOSE #4573663824:c=1,e=0,dep=1,type=3,tim=56034611184918
BINDS #4574946192:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=335512
Bind#1
No oacdef for this bind.
EXEC #4574946192:c=27,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611185311
FETCH #4574946192:c=48,e=152,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611185473
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2de38 bln=22 avl=04 flg=05
value=335712
EXEC #4575350088:c=18,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611185553
FETCH #4575350088:c=3,e=10,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611185585
FETCH #4575350088:c=2,e=5,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611185605
CLOSE #4575350088:c=1,e=0,dep=1,type=3,tim=56034611185620
FETCH #4574946192:c=2,e=6,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611185637
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2de38 bln=22 avl=04 flg=05
value=335713
EXEC #4575350088:c=26,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611193781
FETCH #4575350088:c=7,e=21,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611193816
FETCH #4575350088:c=2,e=4,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611193839
CLOSE #4575350088:c=1,e=1,dep=1,type=3,tim=56034611193861
FETCH #4574946192:c=2,e=7,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611193880
CLOSE #4574946192:c=0,e=1,dep=1,type=3,tim=56034611193902
BINDS #4573663824:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=335512
EXEC #4573663824:c=23,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611193991
FETCH #4573663824:c=14,e=46,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611194048
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611194069
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611194086
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611194102
FETCH #4573663824:c=2,e=7,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611194122
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611194138
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611194154
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611194202
CLOSE #4573663824:c=0,e=1,dep=1,type=3,tim=56034611194217
BINDS #4574946192:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=335506
Bind#1
No oacdef for this bind.
EXEC #4574946192:c=25,e=82,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611194544
FETCH #4574946192:c=76,e=306,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611194860
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110bd1c30 bln=22 avl=04 flg=05
value=335717
EXEC #4575350088:c=22,e=70,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611194983
FETCH #4575350088:c=4,e=11,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611195006
FETCH #4575350088:c=1,e=3,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611195026
CLOSE #4575350088:c=0,e=1,dep=1,type=3,tim=56034611195045
FETCH #4574946192:c=2,e=7,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611195063
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110bd1c30 bln=22 avl=04 flg=05
value=335853
EXEC #4575350088:c=17,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611195136
FETCH #4575350088:c=2,e=7,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611195154
FETCH #4575350088:c=1,e=5,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611195174
FETCH #4575350088:c=1,e=6,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611195193
CLOSE #4575350088:c=0,e=1,dep=1,type=3,tim=56034611195208
FETCH #4574946192:c=2,e=6,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611195224
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110bd1c30 bln=22 avl=04 flg=05
value=335857
EXEC #4575350088:c=17,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611195295
FETCH #4575350088:c=2,e=6,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611195312
FETCH #4575350088:c=1,e=5,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611195331
FETCH #4575350088:c=0,e=2,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611195346
CLOSE #4575350088:c=0,e=1,dep=1,type=3,tim=56034611195361
FETCH #4574946192:c=2,e=5,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611195377
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110bd1c30 bln=22 avl=04 flg=05
value=335858
EXEC #4575350088:c=17,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611195449
FETCH #4575350088:c=2,e=6,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611195465
FETCH #4575350088:c=2,e=8,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611195487
FETCH #4575350088:c=1,e=4,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611195504
FETCH #4575350088:c=0,e=2,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611195519
CLOSE #4575350088:c=0,e=1,dep=1,type=3,tim=56034611195534
FETCH #4574946192:c=2,e=6,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=3984801583,tim=56034611195621
BINDS #4575350088:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110bd1c30 bln=22 avl=04 flg=05
value=335964
EXEC #4575350088:c=67,e=1762,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611205530
FETCH #4575350088:c=14,e=8557,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611214112
FETCH #4575350088:c=10,e=29,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611214230
FETCH #4575350088:c=1,e=5,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611214252
FETCH #4575350088:c=1,e=4,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=992489688,tim=56034611214270
FETCH #4575350088:c=2,e=5,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=992489688,tim=56034611214330
CLOSE #4575350088:c=1,e=3,dep=1,type=3,tim=56034611214382
=====================
PARSING IN CURSOR #4575124080 len=47 dep=1 uid=0 oct=3 lid=0 tim=56034611215248 hv=2539884050 ad='7000002074b8160' sqlid='bd4bznqbq73hk'
select bo#, intcol# from icoldep$ where obj#=:1
END OF STMT
PARSE #4575124080:c=261,e=829,p=0,cr=0,cu=1,mis=1,r=0,dep=1,og=4,plh=0,tim=56034611215247
BINDS #4575124080:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110bd3a78 bln=22 avl=04 flg=05
value=335964
EXEC #4575124080:c=449,e=9531,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=4271409967,tim=56034611224862
FETCH #4575124080:c=14,e=46,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=4271409967,tim=56034611224933
FETCH #4575124080:c=3,e=8,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=4271409967,tim=56034611224991
FETCH #4575124080:c=2,e=6,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=4271409967,tim=56034611225013
FETCH #4575124080:c=3,e=11,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=4271409967,tim=56034611225038
FETCH #4575124080:c=1,e=2,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=4271409967,tim=56034611225054
STAT #4575124080 id=1 cnt=4 pid=0 pos=1 obj=114 op='TABLE ACCESS BY INDEX ROWID ICOLDEP$ (cr=10 pr=0 pw=0 time=47 us cost=2 size=26 card=2)'
STAT #4575124080 id=2 cnt=4 pid=1 pos=1 obj=115 op='INDEX RANGE SCAN I_ICOLDEP$_OBJ (cr=6 pr=0 pw=0 time=50 us cost=1 size=0 card=2)'
CLOSE #4575124080:c=1,e=2,dep=1,type=3,tim=56034611225137
FETCH #4574946192:c=7,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3984801583,tim=56034611225177
CLOSE #4574946192:c=0,e=1,dep=1,type=3,tim=56034611225210
BINDS #4573663824:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b2e250 bln=22 avl=04 flg=05
value=335506
EXEC #4573663824:c=29,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611225356
FETCH #4573663824:c=32,e=98,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611225465
FETCH #4573663824:c=2,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611225487
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611225505
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611225521
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611225537
FETCH #4573663824:c=1,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611225554
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611225570
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611225585
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611225602
FETCH #4573663824:c=20,e=8132,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611233747
FETCH #4573663824:c=6,e=19,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611233925
FETCH #4573663824:c=2,e=5,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611233947
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611233964
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611233982
FETCH #4573663824:c=1,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611233999
FETCH #4573663824:c=10,e=34,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611234046
FETCH #4573663824:c=1,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611234063
FETCH #4573663824:c=2,e=6,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611234082
FETCH #4573663824:c=2,e=6,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611234102
FETCH #4573663824:c=3,e=8,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611234122
FETCH #4573663824:c=3,e=9,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,plh=2783779297,tim=56034611234145
FETCH #4573663824:c=6,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2783779297,tim=56034611234179
CLOSE #4573663824:c=0,e=3,dep=1,type=3,tim=56034611234255
*** 2021-10-14 10:20:31.096
=====================
PARSING IN CURSOR #4573650616 len=202 dep=1 uid=0 oct=3 lid=0 tim=56034783243759 hv=3819099649 ad='70000020894e1b0' sqlid='3nkd3g3ju5ph1'
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
END OF STMT
PARSE #4573650616:c=364,e=18522,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=56034783243757
BINDS #4573650616:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=110b2b278 bln=22 avl=01 flg=05
value=0
Bind#1
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=46 siz=0 off=24
kxsbbbfp=110b2b290 bln=32 avl=27 flg=01
value="SYS_TEMP_0FD9D727F_CCD662FA"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=56
kxsbbbfp=110b2b2b0 bln=22 avl=02 flg=01
value=1
EXEC #4573650616:c=919,e=20584,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=853875749,tim=56034783264651
FETCH #4573650616:c=20,e=63,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,plh=853875749,tim=56034783264744
STAT #4573650616 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 pr=0 pw=0 time=73 us cost=4 size=86 card=1)'
STAT #4573650616 id=2 cnt=0 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=66 us cost=3 size=0 card=1)'
CLOSE #4573650616:c=1,e=4,dep=1,type=3,tim=56034783265079
=====================
PARSING IN CURSOR #4575462392 len=97 dep=1 uid=0 oct=3 lid=0 tim=56034783265389 hv=791757000 ad='7000002058c6738' sqlid='87gaftwrm2h68'
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname from obj$ o where o.obj#=:1
END OF STMT
PARSE #4575462392:c=26,e=84,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2863542513,tim=56034783265389
BINDS #4575462392:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b22c88 bln=22 avl=06 flg=05
value=4254954111
EXEC #4575462392:c=27,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2863542513,tim=56034783265561
FETCH #4575462392:c=5,e=17,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=2863542513,tim=56034783265591
STAT #4575462392 id=1 cnt=0 pid=0 pos=1 obj=18 op='TABLE ACCESS BY INDEX ROWID OBJ$ (cr=2 pr=0 pw=0 time=20 us cost=3 size=42 card=1)'
STAT #4575462392 id=2 cnt=0 pid=1 pos=1 obj=36 op='INDEX RANGE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=17 us cost=2 size=0 card=1)'
CLOSE #4575462392:c=0,e=1,dep=1,type=3,tim=56034783265645
=====================
PARSING IN CURSOR #4575458432 len=172 dep=1 uid=0 oct=1 lid=0 tim=56034783275783 hv=2971140516 ad='7000001ea9c8bc0' sqlid='6sc8xwysjh0d4'
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D727F_CCD662FA" ("C0" DATE,"C1" NUMBER ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254954111 ) NOPARALLEL
END OF STMT
PARSE #4575458432:c=258,e=10094,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=56034783275783
BINDS #4573650616:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=110b22c50 bln=22 avl=02 flg=05
value=1
Bind#1
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=46 siz=0 off=24
kxsbbbfp=110b22c68 bln=32 avl=27 flg=01
value="SYS_TEMP_0FD9D727F_CCD662FA"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=56
kxsbbbfp=110b22c88 bln=22 avl=02 flg=01
value=1
EXEC #4573650616:c=70,e=223,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=853875749,tim=56034783284282
FETCH #4573650616:c=16,e=51,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=853875749,tim=56034783284353
CLOSE #4573650616:c=0,e=2,dep=2,type=3,tim=56034783284422
=====================
PARSING IN CURSOR #4575078736 len=26 dep=2 uid=0 oct=3 lid=0 tim=56034783284559 hv=440799922 ad='7000002072ee5d0' sqlid='77mtwmnd4c4pk'
select obj# from objerror$
END OF STMT
PARSE #4575078736:c=20,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1096442870,tim=56034783284558
EXEC #4575078736:c=27,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1096442870,tim=56034783295288
FETCH #4575078736:c=38,e=124,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=1096442870,tim=56034783295508
=====================
PARSING IN CURSOR #4575132280 len=37 dep=2 uid=0 oct=7 lid=0 tim=56034783304144 hv=2744241110 ad='700000207409810' sqlid='bu6n37ajt3kyq'
DELETE FROM objerror$ where obj# = :1
END OF STMT
PARSE #4575132280:c=41,e=135,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=4162627146,tim=56034783304142
=====================
PARSING IN CURSOR #4575797576 len=235 dep=2 uid=0 oct=6 lid=0 tim=56034783383934 hv=159997841 ad='7000002087fcc88' sqlid='4yyb4104skrwj'
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #4575797576:c=63,e=78260,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3272417377,tim=56034783383932
BINDS #4575797576:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=110b353a0 bln=22 avl=04 flg=05
value=100354
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=110b353b8 bln=22 avl=02 flg=01
value=7
Bind#2
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=7000002055bef41 bln=07 avl=07 flg=09
value="4/5/2018 17:51:8"
Bind#3
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=7000002055bef48 bln=07 avl=07 flg=09
value="10/14/2021 10:15:0"
Bind#4
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=7000002055bef4f bln=07 avl=07 flg=09
value="10/14/2021 10:15:0"
Bind#5
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=72 off=0
kxsbbbfp=110b35340 bln=22 avl=02 flg=05
value=5
Bind#6
oacdty=02 mxl=22(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=110b35358 bln=22 avl=00 flg=01
Bind#7
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=48
kxsbbbfp=110b35370 bln=22 avl=03 flg=01
value=8192
Bind#8
oacdty=23 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=32 off=0
kxsbbbfp=00000000 bln=32 avl=00 flg=09
Bind#9
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=72 off=0
kxsbbbfp=110b352e0 bln=22 avl=02 flg=05
value=6
Bind#10
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=110b352f8 bln=22 avl=04 flg=01
value=65535
Bind#11
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=48
kxsbbbfp=110b35310 bln=22 avl=03 flg=01
value=103
Bind#12
oacdty=01 mxl=32(23) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=46 siz=32 off=0
kxsbbbfp=7000002055bee4e bln=32 avl=23 flg=09
value="FER_P_CONTROLLO_QUALITA"
Bind#13
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b352b0 bln=22 avl=02 flg=05
value=1
WAIT #4575797576: nam='Disk file operations I/O' ela= 8 FileOperation=2 fileno=3 filetype=2 obj#=-1 tim=56034783385257
EXEC #4575797576:c=365,e=1260,p=0,cr=3,cu=3,mis=0,r=1,dep=2,og=4,plh=3272417377,tim=56034783385425
STAT #4575797576 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE OBJ$ (cr=3 pr=0 pw=0 time=405 us)'
STAT #4575797576 id=2 cnt=1 pid=1 pos=1 obj=37 op='INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=66 us cost=3 size=86 card=1)'
BINDS #4575132280:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b353b8 bln=22 avl=04 flg=05
value=100354
EXEC #4575132280:c=146,e=8449,p=0,cr=2,cu=1,mis=0,r=1,dep=2,og=4,plh=4162627146,tim=56034783394006
STAT #4575132280 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE OBJERROR$ (cr=2 pr=0 pw=0 time=8318 us)'
STAT #4575132280 id=2 cnt=1 pid=1 pos=1 obj=60 op='TABLE ACCESS FULL OBJERROR$ (cr=2 pr=0 pw=0 time=8042 us cost=2 size=5 card=1)'
CLOSE #4575132280:c=1,e=4,dep=2,type=3,tim=56034783394225
CLOSE #4575797576:c=1,e=1,dep=2,type=3,tim=56034783394266
FETCH #4575078736:c=12,e=39,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=1096442870,tim=56034783395071
STAT #4575078736 id=1 cnt=1 pid=0 pos=1 obj=60 op='TABLE ACCESS FULL OBJERROR$ (cr=3 pr=0 pw=0 time=116 us cost=2 size=15 card=3)'
CLOSE #4575078736:c=14,e=44,dep=2,type=1,tim=56034783395205
=====================
PARSING IN CURSOR #4575410128 len=48 dep=2 uid=0 oct=3 lid=0 tim=56034783395323 hv=1957319398 ad='7000002072afd88' sqlid='9k43zr1uannr6'
select obj#, owner, node from syn$ where name=:1
END OF STMT
PARSE #4575410128:c=27,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2982295759,tim=56034783395323
BINDS #4575410128:
Bind#0
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=46 siz=32 off=0
kxsbbbfp=110b74728 bln=32 avl=27 flg=05
value="SYS_TEMP_0FD9D727F_CCD662FA"
EXEC #4575410128:c=69,e=225,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2982295759,tim=56034783404917
FETCH #4575410128:c=554,e=9725,p=0,cr=170,cu=0,mis=0,r=0,dep=2,og=4,plh=2982295759,tim=56034783414698
STAT #4575410128 id=1 cnt=0 pid=0 pos=1 obj=68 op='TABLE ACCESS FULL SYN$ (cr=170 pr=0 pw=0 time=9722 us cost=48 size=37 card=1)'
CLOSE #4575410128:c=13,e=42,dep=2,type=1,tim=56034783414943
=====================
PARSING IN CURSOR #4575511936 len=189 dep=2 uid=0 oct=3 lid=0 tim=56034783415152 hv=186852205 ad='700000208945b20' sqlid='2tkw12w5k68vd'
select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,ext_username,spare2 from user$ where name=:1
END OF STMT
PARSE #4575511936:c=39,e=153,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3991378684,tim=56034783415151
BINDS #4575511936:
Bind#0
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=46 siz=32 off=0
kxsbbbfp=110b8bf70 bln=32 avl=27 flg=05
value="SYS_TEMP_0FD9D727F_CCD662FA"
EXEC #4575511936:c=40,e=128,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3991378684,tim=56034783415391
FETCH #4575511936:c=8,e=26,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=3991378684,tim=56034783415438
STAT #4575511936 id=1 cnt=0 pid=0 pos=1 obj=22 op='TABLE ACCESS BY INDEX ROWID USER$ (cr=1 pr=0 pw=0 time=34 us cost=1 size=125 card=1)'
STAT #4575511936 id=2 cnt=0 pid=1 pos=1 obj=46 op='INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=29 us cost=0 size=0 card=1)'
CLOSE #4575511936:c=4,e=13,dep=2,type=1,tim=56034783415504
EXEC #4575458432:c=2499,e=131811,p=0,cr=182,cu=5,mis=0,r=0,dep=1,og=4,plh=0,tim=56034783415674
CLOSE #4575458432:c=1,e=3,dep=1,type=0,tim=56034783415732
=====================
PARSING IN CURSOR #4575474696 len=210 dep=1 uid=0 oct=3 lid=0 tim=56034783415947 hv=864012087 ad='7000002069387e8' sqlid='96g93hntrzjtr'
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
END OF STMT
BINDS #4575474696:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=110b7c008 bln=22 avl=06 flg=05
value=4254954111
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=110b7c020 bln=22 avl=02 flg=01
value=1
EXEC #4575474696:c=51,e=161,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=841937906,tim=56034783416088
FETCH #4575474696:c=18,e=7575,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=3,plh=841937906,tim=56034783423677
CLOSE #4575474696:c=0,e=2,dep=1,type=3,tim=56034783423739
BINDS #4575474696:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=110b7c008 bln=22 avl=06 flg=05
value=4254954111
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=110b7c020 bln=22 avl=02 flg=01
value=2
EXEC #4575474696:c=47,e=149,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=841937906,tim=56034783423983
FETCH #4575474696:c=5,e=16,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=3,plh=841937906,tim=56034783424012
CLOSE #4575474696:c=0,e=1,dep=1,type=3,tim=56034783424033
BINDS #4573650616:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=110b7bfe8 bln=22 avl=01 flg=05
value=0
Bind#1
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=46 siz=0 off=24
kxsbbbfp=110b7c000 bln=32 avl=27 flg=01
value="SYS_TEMP_0FD9D7280_CCD662FA"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=56
kxsbbbfp=110b7c020 bln=22 avl=02 flg=01
value=1
EXEC #4573650616:c=94,e=409,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=853875749,tim=56034783424953
FETCH #4573650616:c=15,e=48,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,plh=853875749,tim=56034783425030
CLOSE #4573650616:c=1,e=1,dep=1,type=3,tim=56034783425121
BINDS #4575462392:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b7c020 bln=22 avl=06 flg=05
value=4254954112
EXEC #4575462392:c=29,e=94,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2863542513,tim=56034783425387
FETCH #4575462392:c=4,e=12,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=2863542513,tim=56034783425411
CLOSE #4575462392:c=0,e=1,dep=1,type=3,tim=56034783425433
=====================
PARSING IN CURSOR #4575458432 len=160 dep=1 uid=0 oct=1 lid=0 tim=56034783436044 hv=131307178 ad='700000047e3d340' sqlid='6dkkk2h3x75pa'
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D7280_CCD662FA" ("C0" DATE ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254954112 ) NOPARALLEL
END OF STMT
PARSE #4575458432:c=280,e=10573,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=56034783436043
BINDS #4573650616:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=110b8d740 bln=22 avl=02 flg=05
value=1
Bind#1
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=46 siz=0 off=24
kxsbbbfp=110b8d758 bln=32 avl=27 flg=01
value="SYS_TEMP_0FD9D7280_CCD662FA"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=56
kxsbbbfp=110b8d778 bln=22 avl=02 flg=01
value=1
EXEC #4573650616:c=75,e=236,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=853875749,tim=56034783445447
FETCH #4573650616:c=19,e=61,p=0,cr=3,cu=0,mis=0,r=0,dep=2,og=4,plh=853875749,tim=56034783445529
CLOSE #4573650616:c=11,e=8057,dep=2,type=3,tim=56034783453660
PARSE #4575078736:c=18,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1096442870,tim=56034783453903
EXEC #4575078736:c=16,e=52,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1096442870,tim=56034783453996
FETCH #4575078736:c=36,e=117,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=4,plh=1096442870,tim=56034783454134
CLOSE #4575078736:c=3,e=8,dep=2,type=3,tim=56034783454186
PARSE #4575410128:c=4,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2982295759,tim=56034783454216
BINDS #4575410128:
Bind#0
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=46 siz=32 off=0
kxsbbbfp=110b6c108 bln=32 avl=27 flg=05
value="SYS_TEMP_0FD9D7280_CCD662FA"
EXEC #4575410128:c=23,e=74,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=2982295759,tim=56034783454327
FETCH #4575410128:c=512,e=10958,p=0,cr=170,cu=0,mis=0,r=0,dep=2,og=4,plh=2982295759,tim=56034783465299
CLOSE #4575410128:c=4,e=12,dep=2,type=3,tim=56034783465480
PARSE #4575511936:c=19,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3991378684,tim=56034783465583
BINDS #4575511936:
Bind#0
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=01 csi=46 siz=32 off=0
kxsbbbfp=110b6c108 bln=32 avl=27 flg=05
value="SYS_TEMP_0FD9D7280_CCD662FA"
EXEC #4575511936:c=66,e=1173,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3991378684,tim=56034783474910
FETCH #4575511936:c=9,e=26,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=3991378684,tim=56034783474963
CLOSE #4575511936:c=3,e=7,dep=2,type=3,tim=56034783475008
EXEC #4575458432:c=1196,e=30180,p=0,cr=176,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=56034783475178
CLOSE #4575458432:c=1,e=2,dep=1,type=0,tim=56034783475233
BINDS #4575474696:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=110b7c008 bln=22 avl=06 flg=05
value=4254954112
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=110b7c020 bln=22 avl=02 flg=01
value=1
EXEC #4575474696:c=42,e=132,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=841937906,tim=56034783475535
FETCH #4575474696:c=22,e=8170,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=3,plh=841937906,tim=56034783483717
CLOSE #4575474696:c=1,e=4,dep=1,type=3,tim=56034783485204
BINDS #4573650616:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=80 off=0
kxsbbbfp=110b7bfe8 bln=22 avl=01 flg=05
value=0
Bind#1
oacdty=01 mxl=32(27) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=46 siz=0 off=24
kxsbbbfp=110b7c000 bln=32 avl=27 flg=01
value="SYS_TEMP_0FD9D7281_CCD662FA"
Bind#2
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=56
kxsbbbfp=110b7c020 bln=22 avl=02 flg=01
value=1
EXEC #4573650616:c=104,e=558,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=853875749,tim=56034783494948
FETCH #4573650616:c=17,e=56,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,plh=853875749,tim=56034783495032
CLOSE #4573650616:c=1,e=2,dep=1,type=3,tim=56034783495107
BINDS #4575462392:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=110b7c020 bln=22 avl=06 flg=05
value=4254954113
EXEC #4575462392:c=24,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2863542513,tim=56034783495314
FETCH #4575462392:c=4,e=11,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=2863542513,tim=56034783495338
CLOSE #4575462392:c=0,e=0,dep=1,type=3,tim=56034783495364
=====================
PARSING IN CURSOR #4575458432 len=182 dep=1 uid=0 oct=1 lid=0 tim=56034783505348 hv=3753657647 ad='7000002065778d8' sqlid='anussrbgvsj9g'
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D7281_CCD662FA" ("C0
|
|
|
|
Re: Long query with WITH not responding [message #685056 is a reply to message #685052] |
Thu, 14 October 2021 07:57   |
 |
Reversi72
Messages: 10 Registered: October 2021
|
Junior Member |
|
|
Michel Cadot wrote on Thu, 14 October 2021 04:22
Your file is truncated and does not even contain your query, so it is useless for this topic.
Please execute the steps described in the link I posted and execute tkprof on the trace file.
No need (for the moment) to post the complete trace file, the output of tkprof is sufficient.
So the plan is:
- Activate trace in the session
- Execute the query
- Deactivate the trace
- Get the trace file and execute tkprof on it (example: tkprof GSESVIL_ora_13500910.trc GSESVIL_ora_13500910.lst)
- Post the output of tkprof
below the result of the tkprof.
Thanks again for your patience
TKPROF: Release 11.2.0.2.0 - Development on Gio Ott 14 14:54:39 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: GSESVIL_ora_13500910.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
********************************************************************************
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 0 0.00 0.00 0 0 0 0
Execute 8 0.00 0.00 0 0 0 0
Fetch 26 0.00 0.02 0 61 0 18
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 34 0.00 0.02 0 61 0 18
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 5n1fs4m2n2y0r Plan Hash: 992489688
select pos#,intcol#,col#,spare1,bo#,spare2,spare3
from
icol$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 18 0.00 0.00 0 0 0 0
Fetch 43 0.00 0.01 0 86 0 25
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 61 0.00 0.02 0 86 0 25
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
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 0 0.00 0.00 0 0 0 0
Execute 8 0.00 0.00 0 0 0 0
Fetch 122 0.00 0.00 0 43 0 114
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 130 0.00 0.01 0 43 0 114
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
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 0 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 2 0.00 0.00 0 2 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
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 0 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 2 0.00 0.00 0 2 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 7nuw4xwrnuwxq Plan Hash: 1089920582
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 0 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 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 9rfqm06xmuwu0 Plan Hash: 156215980
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 0 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 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: f3g84j69n0tjh Plan Hash: 760139525
select col#,intcol#,ntab#
from
ntab$ where obj#=:1 order by intcol# asc
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 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 6qz82dptj0qr7 Plan Hash: 2205639461
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.01 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 0 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: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT ORDER BY (cr=4 pr=0 pw=0 time=104 us cost=3 size=100 card=2)
1 1 1 TABLE ACCESS CLUSTER LOB$ (cr=4 pr=0 pw=0 time=69 us cost=2 size=100 card=2)
1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=34 us cost=1 size=0 card=1)(object id 3)
********************************************************************************
SQL ID: 9g485acn2n30m Plan Hash: 3937008439
select col#,intcol#,reftyp,stabid,expctoid
from
refcon$ where obj#=:1 order by intcol# asc
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 1 0.00 0.00 0 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 32bhha21dkv0v Plan Hash: 2783779297
select col#,intcol#,charsetid,charsetform
from
col$ where obj#=:1 order by intcol# asc
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 11 0.00 0.00 0 4 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 4 0 10
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 0fr8zhn4ymu3v Plan Hash: 3355330683
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 0 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 2 0.00 0.00 0 1 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: bd4bznqbq73hk Plan Hash: 4271409967
select bo#, intcol#
from
icoldep$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 10 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.01 0 10 1 4
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
---------- ---------- ---------- ---------------------------------------------------
4 4 4 TABLE ACCESS BY INDEX ROWID ICOLDEP$ (cr=10 pr=0 pw=0 time=47 us cost=2 size=26 card=2)
4 4 4 INDEX RANGE SCAN I_ICOLDEP$_OBJ (cr=6 pr=0 pw=0 time=50 us cost=1 size=0 card=2)(object id 115)
********************************************************************************
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 1 0.00 0.01 0 0 0 0
Execute 8 0.00 0.04 0 0 0 0
Fetch 8 0.00 0.01 0 24 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 17 0.00 0.06 0 24 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: 2)
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=3 pr=0 pw=0 time=73 us cost=4 size=86 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=66 us cost=3 size=0 card=1)(object id 37)
********************************************************************************
SQL ID: 87gaftwrm2h68 Plan Hash: 2863542513
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname
from
obj$ o where o.obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 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 8 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.00 0 8 0 0
Misses in library cache during parse: 0
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
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=2 pr=0 pw=0 time=20 us cost=3 size=42 card=1)
0 0 0 INDEX RANGE SCAN I_OBJ1 (cr=2 pr=0 pw=0 time=17 us cost=2 size=0 card=1)(object id 36)
********************************************************************************
SQL ID: 6sc8xwysjh0d4 Plan Hash: 0
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D727F_CCD662FA" ("C0" DATE,
"C1" NUMBER ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO
4254954111 ) NOPARALLEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.03 0 0 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.04 0 0 1 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 77mtwmnd4c4pk Plan Hash: 1096442870
select obj#
from
objerror$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 9 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.00 0.00 0 9 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS FULL OBJERROR$ (cr=3 pr=0 pw=0 time=116 us cost=2 size=15 card=3)
********************************************************************************
SQL ID: bu6n37ajt3kyq Plan Hash: 4162627146
DELETE FROM objerror$
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 2 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 1 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE OBJERROR$ (cr=2 pr=0 pw=0 time=8318 us)
1 1 1 TABLE ACCESS FULL OBJERROR$ (cr=2 pr=0 pw=0 time=8042 us cost=2 size=5 card=1)
********************************************************************************
SQL ID: 4yyb4104skrwj Plan Hash: 3272417377
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,
dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14
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.07 0 0 0 0
Execute 1 0.00 0.00 0 3 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.07 0 3 3 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 UPDATE OBJ$ (cr=3 pr=0 pw=0 time=405 us)
1 1 1 INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=66 us cost=3 size=86 card=1)(object id 37)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
********************************************************************************
SQL ID: 9k43zr1uannr6 Plan Hash: 2982295759
select obj#, owner, node
from
syn$ where name=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.04 0 680 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.04 0 680 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS FULL SYN$ (cr=170 pr=0 pw=0 time=9722 us cost=48 size=37 card=1)
********************************************************************************
SQL ID: 2tkw12w5k68vd Plan Hash: 3991378684
select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,
decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,
ext_username,spare2
from
user$ where name=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 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 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.01 0 4 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID USER$ (cr=1 pr=0 pw=0 time=34 us cost=1 size=125 card=1)
0 0 0 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=29 us cost=0 size=0 card=1)(object id 46)
********************************************************************************
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 0 0.00 0.00 0 0 0 0
Execute 9 0.00 0.01 0 0 0 0
Fetch 9 0.00 0.02 0 27 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.00 0.03 0 27 0 0
Misses in library cache during parse: 0
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 6dkkk2h3x75pa Plan Hash: 0
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D7280_CCD662FA" ("C0" DATE )
IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254954112 )
NOPARALLEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.02 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: anussrbgvsj9g Plan Hash: 0
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D7281_CCD662FA" ("C0" DATE,
"C1" DATE,"C2" NUMBER ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE
(OBJNO 4254954113 ) NOPARALLEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.02 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: ga816hd0sb0hk Plan Hash: 0
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D7282_CCD662FA" ("C0" DATE,
"C1" DATE,"C2" NUMBER ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE
(OBJNO 4254954114 ) NOPARALLEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.02 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
explain plan set statement_id='a852251:101421101738' into TOAD_PLAN_TABLE For
WITH DatePrompt /*trasforma le 2 date ricevute in input come parametri*/
AS (SELECT TO_DATE ('01' || SUBSTR ( '01/01/2021', 3, 8), 'dd/mm/yyyy')
AS MyDataMeseDa, /*trasformo il testo-data ricevuto in input in una data in formato 01/mm/yyyy 00:00:00*/
LAST_DAY (TO_DATE ( '01/08/2021', 'dd/mm/yyyy'))
+ 1
- 1 / 86400
AS MyDataMeseA /*trasformo il testo-data ricevuto in input in una data in formato 31/mm/yyyy 23:59:59*/
FROM DUAL),
ListaMesiAnno /*elenco dei mesi/anni comprsi fra le 2 date in input*/
AS ( SELECT ADD_MONTHS (TRUNC (MyDataMeseDa, 'MM'), ROWNUM - 1)
AS MeseAnno
FROM DatePrompt
CONNECT BY ROWNUM <=
MONTHS_BETWEEN (TRUNC (MyDataMeseA, 'MM'),
TRUNC (MyDataMeseDa, 'MM'))
+ 1),
MwPotUp
AS (SELECT SUM (PotenzaGeneratore) / 1000 val
FROM wpowerdb.t_ana_eolico
WHERE codiceup = 'UP_BONORVA_1'
AND id_config = (SELECT MAX (id_config)
FROM wpowerdb.t_ana_eolico
WHERE codiceup = 'UP_BONORVA_1')),
CheckNoParAneomometri
/*Restituisce 1 se sono tutti NULL i parametri in input NULL,NULL e NULL, altrimenti 0
Da utilizzare quando si estraggono i dati vento, serve a stabilire se quest'ultimo vanno estratti per uno o più specifici anemometri o per tuttto l'impianto*/
AS (SELECT COUNT(*) val
FROM dual
WHERE NULL IS NULL
AND NULL IS NULL
AND NULL IS NULL),
ListaAneAG
/*In funzione dei parametri in input (NULL NOT NULL oppure NULL NOT NULL oppure NULL NOT NULL) individua l'elenco delle coppie Anemometro/Aerogeneratore di interesse,
prelevandole dai dati operativi o dai dati storici
N.B. L'estrazione si basa sul presupporto che i parametri IdAssettoGSE e NULL nonn possono essere contemporaneamente valorizzati*/
AS ( SELECT NULL IdAG,
NULL IdAne,
NULL idgeneratore,
NULL id_ane,
NULL nome_assetto
FROM dual /*Se in input è stato passato uno specifico anemometro (parametro NULL valorizzato), la query restituisce solo quell'identificativo di anemometro*/
WHERE NULL IS NOT NULL
AND NULL IS NULL
AND NULL IS NULL
UNION
SELECT DISTINCT /*Se in input è stato passato un'assetto validato da GSE (parametro IdAssettoGSE valorizzato), la query restituisce l'elenco degli identificativi di anemometro che appartengono a quell'assetto*/
a.identificativo_aero IdAG,
a.identificativo_ane IdAne,
a.id_int_aero idgeneratore,
a.id_int_ane id_ane,
a.nome_assetto nome_assetto
FROM T_ASSETTO_ELAB_CONS a
WHERE a.ID_ASSETTO = NULL /*492*/
AND NULL IS NULL
AND NULL IS NOT NULL
AND NULL IS NULL
UNION
SELECT DISTINCT /*Se in input è stato passato un'assetto Operatore (parametro IdAssettoOpe valorizzato), la query restituisce l'elenco degli identificativi di aerogeneratore che appartengono a quell'assetto*/
ag.identificativo_gse IdAG,
a.identificativo_ane IdAne,
assd.idgeneratore,
assd.id_ane,
ass.s_nome_assetto nome_assetto
FROM mpe_ope_assetto ass,
mpe_ope_assetto_dett assd,
t_ana_eolico ag,
t_ana_anemometri a
WHERE ass.id_ope_assetto = HEXTORAW(NULL) /*C1BC9F38F6190112E0530AA01523B9A3*/
AND ass.id_ope_assetto = assd.id_ope_assetto
AND assd.id_ane = a.id_ane
AND assd.idgeneratore = ag.idgeneratore
AND NULL IS NULL
AND NULL IS NULL
AND NULL IS NOT NULL
),
pPGreco
AS ( SELECT 3.14159265358979 val FROM dual),
/*
CURVE_PART1_DISP
------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
Query per il calcolo della disponibilità dei dati e della qualità degli stessi,
utili al calcolo delle curve A, B e C
-- TIPOLOGIE FLUSSI ESTRATTI
"V3D" --> Da utilizzare nel cruscotto per individuare i dati vento con direzione (Curve A, B e C)
"VEN" --> Da utilizzare nel cruscotto per individuare i dati vento con o senza direzione (Curve A e B)
"MIS" --> Da utilizzare nel cruscotto per individuare le misura (Curve A, B e C)
"IND" --> Da utilizzare nel cruscotto per individuare le indisponibilità (Curve A, B e C)
"C2D" --> Da utilizzare nel cruscotto per individuare i periodi di contemporanea disponibilità delle misure e dei dati vento con o senza direzione (Curve A e B)
"C3D" --> Da utilizzare nel cruscotto per individuare i periodi di contemporanea disponibilità delle misure e dei dati vento con direzione (Curve A, B e C)
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
*/
/*----------------------------------------------------------------------------
Le query in questa sezione servono ad estrarre l'elenco distinto delle date/ora
disponibili a sistema per ogni specifico ambito dati:
- DatiVentoDataOra (2D e 3D - per impianto. Da utilizzare per le curve A e C)
- DatiVentoDataOraAG (2D e 3D - per anemometri dell'assetto. Da utilizzare per la curva B)
- DatiMisureDataOra (per impianto. Da utilizzare per le curve A e C)
- DatiMisureDataOraAG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
- DatiIndispDataOra (per impianto. Da utilizzare per le curve A e C)
- DatiIndispDataOraAG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
- DatiOddDataOra
-----------------------------------------------------------------------------*/
DatiVentoDataOra
AS (SELECT DISTINCT * FROM (
/*dati operativi*/
SELECT DISTINCT
TRUNC (vo.lettura, 'HH24') DataOraVento,
DECODE (NVL (vo.direzione_vento, 0), 0, 0, 1) Flag3D
FROM MIFR_DFP_EOLICO@PSAT_LINK vo
WHERE vo.valido = 1
AND ( (vo.fonte IN (0, 2) AND NULL IS NULL)
OR
(vo.fonte = 5 AND NULL IS NOT NULL)
)
AND vo.codice_up = 'UP_BONORVA_1' /*codice_up corrisponde al campo MPE_COD_ANAG_UP.S_COD_IMPIANTO*/
AND vo.id_anemo = NVL(NULL, vo.id_anemo) /*id_anemo corrisponde al campo T_ANA_ANEMOMETRI.IDENTIFICATIVO_ANE*/
UNION ALL
/*dati storici*/
SELECT TRUNC (vs.d_data, 'HH24') DataOraVento,
DECODE (NVL (vs.n_direzione_vento, 0), 0, 0, 1) Flag3D
FROM mpe_sto_vento_dett vs,
mpe_cod_anag_up UP,
t_ana_anemometri a
WHERE vs.id_cod_anag = UP.id_cod_anag
AND vs.id_ane = a.id_ane(+)
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND a.identificativo_ane = NVL(NULL, a.identificativo_ane)
AND NULL IS NULL)),
Pre1DatiVentoDataOraAG
AS (SELECT DISTINCT * FROM (
/*dati operativi*/
SELECT TRUNC (vo.lettura, 'HH24') DataOraVento,
DECODE (NVL (vo.direzione_vento, 0), 0, 0, 1) Flag3D,
l.IdAne
FROM MIFR_DFP_EOLICO@PSAT_LINK vo,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne FROM ListaAneAG) l
WHERE vo.valido = 1
AND vo.fonte IN (0, 2, 5)
AND vo.codice_up = 'UP_BONORVA_1' /*codice_up corrisponde al campo MPE_COD_ANAG_UP.S_COD_IMPIANTO*/
AND vo.id_anemo = l.IdAne (+) /*id_anemo corrisponde al campo T_ANA_ANEMOMETRI.IDENTIFICATIVO_ANE*/
AND ( chk.val = 1
OR
l.IdAne IS NOT NULL)
UNION ALL
/*dati storici*/
SELECT TRUNC (vs.d_data, 'HH24') DataOraVento,
DECODE (NVL (vs.n_direzione_vento, 0), 0, 0, 1) Flag3D,
l.IdAne
FROM mpe_sto_ag_vento_dett vs,
mpe_cod_anag_up UP,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne, id_ane FROM ListaAneAG) l
WHERE vs.id_cod_anag = UP.id_cod_anag
AND vs.id_ane = l.id_ane(+)
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND ( chk.val = 1
OR
l.IdAne IS NOT NULL))
),
Pre2DatiVentoDataOraAG
AS (SELECT dv.DataOraVento, COUNT(DISTINCT dv.IdAne) AS IdAne
FROM Pre1DatiVentoDataOraAG dv
GROUP BY dv.DataOraVento
HAVING COUNT(DISTINCT dv.IdAne) = (SELECT COUNT(DISTINCT IdAne) FROM ListaAneAG)
),
DatiVentoDataOraAG
AS (SELECT DISTINCT dv.DataOraVento,
dv.Flag3D
FROM Pre1DatiVentoDataOraAG dv,
CheckNoParAneomometri chk,
Pre2DatiVentoDataOraAG dv2
WHERE (chk.val = 1 OR dv2.DataOraVento IS NOT NULL)
AND dv.DataOraVento = dv2.DataOraVento (+)
),
/* La query qui sotto è stata sostituita dalle precedenti: Pre1DatiVentoDataOraAG, Pre2DatiVentoDataOraAG e DatiVentoDataOraAG
DatiVentoDataOraAG
AS (/*
/*dati operativi*/
/* SELECT DISTINCT
TRUNC (vo.lettura, 'HH24') DataOraVento,
DECODE (NVL (vo.direzione_vento, 0), 0, 0, 1) Flag3D
FROM MIFR_DFP_EOLICO@PSAT_LINK vo,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne FROM ListaAneAG) l
WHERE vo.valido = 1
AND vo.fonte IN (0, 2, 5)
AND vo.codice_up = 'UP_BONORVA_1' */ /*codice_up corrisponde al campo MPE_COD_ANAG_UP.S_COD_IMPIANTO*/
/* AND vo.id_anemo = l.IdAne (+)*/ /*id_anemo corrisponde al campo T_ANA_ANEMOMETRI.IDENTIFICATIVO_ANE*/
/* AND ( chk.val = 1
OR
l.IdAne IS NOT NULL)
UNION */
/*dati storici*/
/* SELECT TRUNC (vs.d_data, 'HH24') DataOraVento,
DECODE (NVL (vs.n_direzione_vento, 0), 0, 0, 1) Flag3D
FROM mpe_sto_ag_vento_dett vs,
mpe_cod_anag_up UP,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne, id_ane FROM ListaAneAG) l
WHERE vs.id_cod_anag = UP.id_cod_anag
AND vs.id_ane = l.id_ane(+)
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND ( chk.val = 1
OR
l.IdAne IS NOT NULL)
),*/
DatiMisureDataOra /*Curve A e C: Estrazione delle date disponibili a sistema per le misure*/
AS ( /*dati Misurati*/
SELECT TRUNC (mmd.d_data_ora, 'HH24') DataOraMisura
FROM MPE_MISURE_MENS mm, MPE_MISURE_MENS_DETT mmd
WHERE mm.n_recente = 1
AND mm.n_id_misura = mmd.n_id_misura
AND mm.s_cod_impianto = 'UP_BONORVA_1'
UNION
/*dati storici*/
SELECT TRUNC (ms.d_data_rif, 'HH24') DataOraMisura
FROM mpe_sto_misure_dett ms, mpe_cod_anag_up UP
WHERE ms.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND ms.n_recente = 1),
DatiMisureDataOraAG /*Curva B: Estrazione delle date disponibili a sistema per le misure*/
AS (/*dati storici*/
SELECT TRUNC (ms.d_data_rif, 'HH24') DataOraMisura
FROM mpe_sto_ag_misure_dett ms, mpe_cod_anag_up UP, (SELECT DISTINCT idgeneratore FROM ListaAneAG) l
WHERE ms.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND ms.n_recente = 1
AND ms.idgeneratore = l.idgeneratore),
DatiIndispDataOra
AS ( /*dati operativi*/
SELECT TRUNC (indd.data_ora, 'HH24') DataOraIndisp
FROM ( SELECT codiceup, anno, mese, MAX(versione) versione
FROM T_PROG_INDISP_MANC_PROD
WHERE codiceup = 'UP_BONORVA_1'
GROUP BY codiceup, anno, mese
) PilotaInd,
T_PROG_INDISP_MANC_PROD ind,
T_PROG_INDISP_MANC_PROD_DETT indd
WHERE PilotaInd.codiceup = ind.codiceup
AND PilotaInd.anno = ind.anno
AND PilotaInd.mese = ind.mese
AND PilotaInd.versione = ind.versione
AND ind.idinterno = indd.id_indisp
UNION
/*dati storici*/
SELECT TRUNC (inds.d_data_ora, 'HH24') DataOraIndisp
FROM mpe_sto_indisp_dett inds, mpe_cod_anag_up UP
WHERE inds.n_recente = 1
AND inds.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = 'UP_BONORVA_1'),
DatiIndispDataOraAG
AS (/*dati storici*/
SELECT TRUNC (inds.d_data_ora, 'HH24') DataOraIndisp
FROM mpe_sto_ag_indisp_dett inds, mpe_cod_anag_up UP, (SELECT DISTINCT idgeneratore FROM ListaAneAG) l
WHERE inds.n_recente = 1
AND inds.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND inds.idgeneratore = l.idgeneratore
UNION
SELECT DataOraIndisp FROM DatiIndispDataOra),
DatiOddDataOra
AS (SELECT TRUNC (od.d_data_ora, 'HH24') DataOraOdd
FROM mpe_ordini_disp o, mpe_ordini_disp_dett od, DatePrompt
WHERE o.n_id_ordini_disp = od.n_id_ordine
AND o.S_COD_IMPIANTO = 'UP_BONORVA_1'
AND o.N_RECENTE = 1
AND od.d_data_ora BETWEEN MyDataMeseDa AND MyDataMeseA),
/*----------------------------------------------------------------------------
Le query in questa sezione servono ad individuare le date minima e massima
disponibili a sistema per ogni specifico ambito dati:
- RPT_DISP_DATI_VENTO_2D_3D (2D e 3D - per impianto o per anemometri dell'assetto. Da utilizzare per le curve A, B e C)
- RPT_DISP_DATI_MISURE (per impianto.Da utilizzare per la curve A e C)
- RPT_DISP_DATI_MISURE_AG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
- RPT_DISP_DATI_INDISP (per impianto.Da utilizzare per la curve A e C)
- RPT_DISP_DATI_INDISP_AG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
Dalle precedenti si ricavano le date minima e massima disponibili a sistema
fra i dati utilizzabili per il calcolo delle curve 2D e 3D:
- RPT_DISP_DATI_2D (per impianto.Da utilizzare per la curve A e C)
- RPT_DISP_DATI_2D_AG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
- RPT_DISP_DATI_3D (per impianto.Da utilizzare per la curve A e C)
- RPT_DISP_DATI_3D_AG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
-----------------------------------------------------------------------------*/
MinMaxDatiVento /*query propedeutica alla RPT_DISP_DATI_VENTO_2D_3D*/
AS ( SELECT MIN (DataOraVento) DataMin,
MAX (DataOraVento) DataMax,
Flag3D
FROM DatiVentoDataOra
GROUP BY Flag3D),
RPT_DISP_DATI_VENTO_2D_3D
AS /*
Report Disponibilità dei Dati Vento (2D+3D) e dei Dati Vento solo 3D
Restituisce 2 righe
Le informazioni restituite fanno parte di 2 diversi raggruppamenti:
(a) uno per i dati vento 2D+3D (FlagSoloConDirezione=0)
(b) uno per i soli dati vento 3D (FlagSoloConDirezione=1)
*/
(SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
0 FlagSoloConDirezione
FROM MinMaxDatiVento
UNION ALL
SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
1 FlagSoloConDirezione
FROM MinMaxDatiVento
WHERE Flag3D = 1),
MinMaxDatiVentoAG /*query propedeutica alla RPT_DISP_DATI_VENTO_2D_3D_AG*/
AS ( SELECT MIN (DataOraVento) DataMin,
MAX (DataOraVento) DataMax,
Flag3D
FROM DatiVentoDataOraAG
GROUP BY Flag3D),
RPT_DISP_DATI_VENTO_2D_3D_AG
AS /*
Report Disponibilità dei Dati Vento (2D+3D) e dei Dati Vento solo 3D
Restituisce 2 righe
Le informazioni restituite fanno parte di 2 diversi raggruppamenti:
(a) uno per i dati vento 2D+3D (FlagSoloConDirezione=0)
(b) uno per i soli dati vento 3D (FlagSoloConDirezione=1)
*/
(SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
0 FlagSoloConDirezione
FROM MinMaxDatiVentoAG
UNION ALL
SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
1 FlagSoloConDirezione
FROM MinMaxDatiVentoAG
WHERE Flag3D = 1),
RPT_DISP_DATI_MISURE
AS /*Report Disponibilità dei Dati delle Misure per impianto*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiMisureDataOra),
RPT_DISP_DATI_MISURE_AG
AS /*Report Disponibilità dei Dati delle Misure per aerogfeneratori dell'assetto*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiMisureDataOraAG),
RPT_DISP_DATI_INDISP
AS /*Report Disponibilità dei Dati delle Indisponibilita per impianto*/
(SELECT MIN (DataOraIndisp) DataMin, MAX (DataOraIndisp) DataMax
FROM DatiIndispDataOra),
RPT_DISP_DATI_INDISP_AG
AS /*Report Disponibilità dei Dati delle Indisponibilita per aerogfeneratori dell'assetto (include quelle dell'impianto)*/
(SELECT MIN (DataOraIndisp) DataMin, MAX (DataOraIndisp) DataMax
FROM DatiIndispDataOraAG),
RPT_DISP_DATI_2D
AS /*Report Disponibilità dei Dati per produzione delle curve 2D */
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOra v, DatiMisureDataOra m
WHERE v.DataOraVento = m.DataOraMisura),
RPT_DISP_DATI_2D_AG
AS /*Report Disponibilità dei Dati per produzione delle curve 2D */
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOraAG v, DatiMisureDataOraAG m
WHERE v.DataOraVento = m.DataOraMisura),
RPT_DISP_DATI_3D
AS /*Report Disponibilità dei Dati per produzione delle curve 3D*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOra v, DatiMisureDataOra m
WHERE v.DataOraVento = m.DataOraMisura AND v.Flag3D = 1),
RPT_DISP_DATI_3D_AG
AS /*Report Disponibilità dei Dati per produzione delle curve 3D*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOraAG v, DatiMisureDataOraAG m
WHERE v.DataOraVento = m.DataOraMisura AND v.Flag3D = 1)
/*
CURVA_A_PART2_DISP
La seguente query mette insieme tutte le RPT_DISP%, utili al calcolo delle statistiche per la curva A, presenti nella query CURVE_PART1_DISP_PRES
e viene interrogata da cruscotto per riportare in mascherale date minima e massima calcolate per ogni ambito dati*/
SELECT 'V3D' Flusso, DataMin, DataMax
FROM RPT_DISP_DATI_VENTO_2D_3D
WHERE FlagSoloConDirezione = 1
UNION ALL
SELECT 'VEN' Flusso, DataMin, DataMax
FROM RPT_DISP_DATI_VENTO_2D_3D
WHERE FlagSoloConDirezione = 0
UNION ALL
SELECT 'MIS' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_MISURE
UNION ALL
SELECT 'IND' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_INDISP
UNION ALL
SELECT 'C2D' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_2D
UNION ALL
SELECT 'C3D' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_3D
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 7.99 172.89 0 0 1 0
Execute 1 0.00 0.27 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 7.99 173.16 0 0 1 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 131
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
single-task message 1 0.36 0.36
SQL*Net message from dblink 11 0.03 0.10
SQL*Net message to dblink 10 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID: 7yxtn79vzvmf8 Plan Hash: 0
insert into "TOAD_PLAN_TABLE" (statement_id, timestamp, operation, options,
object_node, object_owner, object_name, object_instance, object_type,
search_columns, id, parent_id, position, other,optimizer, cost, cardinality,
bytes, other_tag, partition_start, partition_stop, partition_id,
distribution, cpu_cost, io_cost, temp_space, access_predicates,
filter_predicates, projection, time, qblock_name, object_alias, plan_id,
depth, remarks, other_xml )
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,
:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 81 0.05 1.51 0 7 193 81
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 82 0.05 1.51 0 7 193 81
Misses in library cache during parse: 0
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 131 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=318 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 2 0.00 0.00
CSS initialization 1 0.08 0.08
CSS operation: query 6 0.00 0.00
CSS operation: action 1 0.01 0.01
direct path write 1 0.00 0.00
********************************************************************************
SQL ID: 15knr3nbjkrcw Plan Hash: 2246842245
SELECT ORA_PLAN_ID_SEQ$.NEXTVAL
FROM
DUAL
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 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
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 SEQUENCE ORA_PLAN_ID_SEQ$ (cr=0 pr=0 pw=0 time=54 us)
1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=4 us cost=2 size=0 card=1)
********************************************************************************
Select *
From TOAD_PLAN_TABLE
Where statement_id = :STATEMENT_ID
order by id
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 17 0 81
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 17 0 81
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 131
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
81 81 81 SORT ORDER BY (cr=16 pr=0 pw=0 time=752 us cost=4 size=897561 card=81)
81 81 81 TABLE ACCESS FULL TOAD_PLAN_TABLE (cr=16 pr=0 pw=0 time=183 us cost=3 size=897561 card=81)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.01 0.02
SQL*Net more data to client 2 0.00 0.00
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 7.99 172.89 0 0 1 0
Execute 2 0.00 0.27 0 0 0 0
Fetch 1 0.00 0.00 0 17 0 81
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 7.99 173.16 0 17 1 81
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net more data from client 2 0.00 0.00
single-task message 1 0.36 0.36
SQL*Net message from dblink 11 0.03 0.10
SQL*Net message to dblink 10 0.00 0.00
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 3 0.01 0.02
SQL*Net more data to client 2 0.00 0.00
direct path read 1 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 24 0.00 0.14 0 0 1 0
Execute 165 0.05 1.70 0 12 198 83
Fetch 251 0.00 0.15 0 978 0 174
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 440 0.06 2.00 0 990 199 257
Misses in library cache during parse: 7
Misses in library cache during execute: 5
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 3 0.00 0.00
CSS initialization 1 0.08 0.08
CSS operation: query 6 0.00 0.00
CSS operation: action 1 0.01 0.01
direct path write 1 0.00 0.00
3 user SQL statements in session.
26 internal SQL statements in session.
29 SQL statements in session.
********************************************************************************
Trace file: GSESVIL_ora_13500910.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
3 user SQL statements in trace file.
26 internal SQL statements in trace file.
29 SQL statements in trace file.
29 unique SQL statements in trace file.
15041 lines in trace file.
175 elapsed seconds in trace file.
|
|
|
|
Re: Long query with WITH not responding [message #685084 is a reply to message #685058] |
Mon, 18 October 2021 09:31   |
 |
Reversi72
Messages: 10 Registered: October 2021
|
Junior Member |
|
|
Michel Cadot wrote on Thu, 14 October 2021 08:24
As the times for the recursive calls are negligible, the most important part is this one:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 7.99 172.89 0 0 1 0
Execute 2 0.00 0.27 0 0 0 0
Fetch 1 0.00 0.00 0 17 0 81
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 7.99 173.16 0 17 1 81
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net more data from client 2 0.00 0.00
single-task message 1 0.36 0.36
SQL*Net message from dblink 11 0.03 0.10
SQL*Net message to dblink 10 0.00 0.00
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 3 0.01 0.02
SQL*Net more data to client 2 0.00 0.00
direct path read 1 0.00 0.00
This indicates that Oracle does indeed much time in parsing but not on cpu and with no recorded waiting time.
What does this mean?
Oracle spent the time in some not instrumented code.
This smells the bug.
Unfortunately with 11.2.0.3 you will get no answer from Oracle.
I advise you now to try 2 things:
1/ As John mentioned, hint the query to use the rule-based optimizer (/*+ rule */ after select),
or change your session "optimizer_mode" to "rule" and get again the trace
2/ Keep the optimizer more at its normal value and take the 10053 trace to see what the optimizer does
so you may have some clues to hint the query and change the optimizer behavior
below the thece analysis with the hint /*+ rule */
thanks again
TKPROF: Release 11.2.0.2.0 - Development on Lun Ott 18 16:25:31 2021
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Trace file: GSESVIL_ora_14811422.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
********************************************************************************
SQL ID: 7ng34ruy5awxq Plan Hash: 3691521353
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 0 0.00 0.00 0 0 0 0
Execute 12 0.00 0.01 0 0 0 0
Fetch 35 0.00 0.11 5 86 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 47 0.00 0.12 5 86 0 23
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 5 0.03 0.06
********************************************************************************
SQL ID: 5n1fs4m2n2y0r Plan Hash: 992489688
select pos#,intcol#,col#,spare1,bo#,spare2,spare3
from
icol$ where obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 23 0.00 0.01 0 0 0 0
Fetch 50 0.00 0.00 1 100 0 27
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 73 0.00 0.02 1 100 0 27
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.00 0.00
********************************************************************************
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 0 0.00 0.00 0 0 0 0
Execute 12 0.00 0.00 0 0 0 0
Fetch 155 0.00 0.00 0 52 0 143
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 167 0.00 0.00 0 52 0 143
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
********************************************************************************
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 0 0.00 0.00 0 0 0 0
Execute 47 0.00 0.02 0 0 0 0
Fetch 47 0.00 0.06 4 172 0 31
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 94 0.00 0.09 4 172 0 31
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4 0.03 0.06
********************************************************************************
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 0 0.00 0.00 0 0 0 0
Execute 11 0.00 0.00 0 0 0 0
Fetch 11 0.00 0.02 2 49 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 22 0.00 0.02 2 49 0 11
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.01 0.02
********************************************************************************
SQL ID: 9tgj4g8y4rwy8 Plan Hash: 3632979230
select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,
NVL(lists,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),
NVL(scanhint,0),NVL(bitmapranges,0)
from
seg$ where ts#=:1 and file#=:2 and block#=:3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 28 0.00 0.06 0 0 0 0
Fetch 28 0.00 0.02 2 84 0 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 57 0.00 0.08 2 84 0 28
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 CLUSTER SEG$ (cr=3 pr=1 pw=0 time=15389 us cost=2 size=68 card=1)
1 1 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=1 pw=0 time=15348 us cost=1 size=0 card=1)(object id 9)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.01
********************************************************************************
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 0 0.00 0.00 0 0 0 0
Execute 25 0.00 0.01 0 0 0 0
Fetch 25 0.00 0.06 4 100 0 25
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 50 0.00 0.08 4 100 0 25
Misses in library cache during parse: 0
Optimizer mode: RULE
Parsing user id: SYS (recursive depth: 3)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 4 0.02 0.05
********************************************************************************
SQL ID: db78fxqxwxt7r Plan Hash: 2324581405
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 50 0.00 0.09 0 0 0 0
Fetch 50 0.00 0.11 10 150 0 907
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.00 0.20 10 150 0 907
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: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5 5 5 SORT ORDER BY (cr=3 pr=1 pw=0 time=11329 us cost=0 size=0 card=0)
5 5 5 TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=1 pw=0 time=11266 us)
1 1 1 INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=114 us)(object id 422)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 10 0.01 0.07
********************************************************************************
SQL ID: 87gaftwrm2h68 Plan Hash: 2863542513
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname
from
obj$ o where o.obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 29 0.00 0.00 0 0 0 0
Fetch 29 0.00 0.01 1 79 0 21
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 58 0.00 0.01 1 79 0 21
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.01 0.01
********************************************************************************
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 0 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 7 0.00 0.02 2 14 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 14 0.00 0.02 2 14 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.01 0.01
********************************************************************************
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 0 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 15 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 15 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
********************************************************************************
SQL ID: 7nuw4xwrnuwxq Plan Hash: 1089920582
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 0 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 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 9rfqm06xmuwu0 Plan Hash: 156215980
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 0 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 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: f3g84j69n0tjh Plan Hash: 760139525
select col#,intcol#,ntab#
from
ntab$ where obj#=:1 order by intcol# asc
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 1 0.00 0.00 0 1 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 1 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 6qz82dptj0qr7 Plan Hash: 2205639461
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.02 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.03 0 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: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT ORDER BY (cr=4 pr=0 pw=0 time=73 us cost=3 size=100 card=2)
1 1 1 TABLE ACCESS CLUSTER LOB$ (cr=4 pr=0 pw=0 time=43 us cost=2 size=100 card=2)
1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=20 us cost=1 size=0 card=1)(object id 3)
********************************************************************************
SQL ID: 9g485acn2n30m Plan Hash: 3937008439
select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order
by intcol# asc
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 1 0.00 0.00 0 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 32bhha21dkv0v Plan Hash: 2783779297
select col#,intcol#,charsetid,charsetform
from
col$ where obj#=:1 order by intcol# asc
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 11 0.00 0.00 0 4 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.00 0 4 0 10
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 0fr8zhn4ymu3v Plan Hash: 3355330683
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 0 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 2 0.00 0.00 0 1 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
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 0 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 11 0.00 0.00 0 22 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.00 0.00 0 22 0 4
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
********************************************************************************
SQL ID: gx4mv66pvj3xz Plan Hash: 1434859146
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 0 0.00 0.00 0 0 0 0
Execute 7 0.00 0.00 0 0 0 0
Fetch 35 0.00 0.01 1 70 0 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 42 0.00 0.01 1 70 0 28
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.01 0.01
********************************************************************************
SQL ID: 53saa2zkr6wc3 Plan Hash: 2631433895
select intcol#,nvl(pos#,0),col#,nvl(spare1,0)
from
ccol$ where con#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 28 0.00 0.00 0 0 0 0
Fetch 57 0.00 0.01 1 114 0 29
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 85 0.00 0.02 1 114 0 29
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 3)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.01 0.01
********************************************************************************
SQL ID: 9gkq7rruycsjp Plan Hash: 2930975892
select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused,
definitrans, defmaxtrans, deftiniexts, defextsize, defminexts, defmaxexts,
defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256)
subparttype, mod(trunc(spare2/256), 256) subpartkeycols,
mod(trunc(spare2/65536), 65536) defsubpartcnt, mod(trunc(spare2/4294967296),
256) defhscflags, mod(spare3, 256) interval_dty, rowid, defmaxsize
from
partobj$ where obj# = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 1 0
Execute 4 0.00 0.01 0 0 0 0
Fetch 4 0.00 0.00 0 4 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.00 0.01 0 4 1 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: 3
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID PARTOBJ$ (cr=1 pr=0 pw=0 time=25 us cost=1 size=53 card=1)
0 0 0 INDEX UNIQUE SCAN I_PARTOBJ$ (cr=1 pr=0 pw=0 time=20 us cost=0 size=0 card=1)(object id 565)
********************************************************************************
SQL ID: 7a2h0w34panpz Plan Hash: 0
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6609_A763F95" ("C0" DATE,
"C1" NUMBER ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO
4254950921 ) NOPARALLEL
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 2 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 2 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: bu6n37ajt3kyq Plan Hash: 4162627146
DELETE FROM objerror$
where
obj# = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.01 0 6 3 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 6 3 3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE OBJERROR$ (cr=2 pr=0 pw=0 time=158 us)
1 1 1 TABLE ACCESS FULL OBJERROR$ (cr=2 pr=0 pw=0 time=64 us cost=2 size=13 card=1)
********************************************************************************
SQL ID: 4yyb4104skrwj Plan Hash: 3272417377
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,
dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14
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.01 2 9 7 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.02 2 9 7 3
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 UPDATE OBJ$ (cr=3 pr=1 pw=0 time=5838 us)
1 1 1 INDEX RANGE SCAN I_OBJ2 (cr=3 pr=1 pw=0 time=5585 us cost=3 size=86 card=1)(object id 37)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.00
Disk file operations I/O 1 0.00 0.00
********************************************************************************
SQL ID: 9k43zr1uannr6 Plan Hash: 2982295759
select obj#, owner, node
from
syn$ where name=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 8 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.29 166 1360 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.00 0.30 166 1360 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: 2)
Number of plan statistics captured: 4
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS FULL SYN$ (cr=170 pr=42 pw=0 time=64717 us cost=48 size=37 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 1 0.00 0.00
db file scattered read 18 0.02 0.17
********************************************************************************
SQL ID: 2tkw12w5k68vd Plan Hash: 3991378684
select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,
decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,
ext_username,spare2
from
user$ where name=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 8 0.00 0.01 0 0 0 0
Fetch 8 0.00 0.00 0 8 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.00 0.02 0 8 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: 2)
Number of plan statistics captured: 4
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS BY INDEX ROWID USER$ (cr=1 pr=0 pw=0 time=25 us cost=1 size=125 card=1)
0 0 0 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=21 us cost=0 size=0 card=1)(object id 46)
********************************************************************************
SQL ID: gz03nfakdg5kp Plan Hash: 0
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D660A_A763F95" ("C0" DATE )
IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950922 )
NOPARALLEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: 77mtwmnd4c4pk Plan Hash: 1096442870
select obj#
from
objerror$
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.01 0 0 0 0
Execute 8 0.00 0.00 0 0 0 0
Fetch 11 0.00 0.01 2 19 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 27 0.00 0.02 2 19 0 3
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 4
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 1 2 TABLE ACCESS FULL OBJERROR$ (cr=3 pr=1 pw=0 time=3659 us cost=2 size=13 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.00
********************************************************************************
SQL ID: aa778rdasfv9a Plan Hash: 0
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D660B_A763F95" ("C0" DATE,
"C1" DATE,"C2" NUMBER ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE
(OBJNO 4254950923 ) NOPARALLEL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
SQL ID: g1phggv0th0mt Plan Hash: 0
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D660C_A763F95" ("C0" DATE,
"C1" DATE,"C2" NUMBER ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE
(OBJNO 4254950924 ) NOPARALLEL
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 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 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 /*+ rule */ * from (
WITH DatePrompt /*trasforma le 2 date ricevute in input come parametri*/
AS (SELECT TO_DATE ('01' || SUBSTR ( '01/01/2021', 3, 8), 'dd/mm/yyyy')
AS MyDataMeseDa, /*trasformo il testo-data ricevuto in input in una data in formato 01/mm/yyyy 00:00:00*/
LAST_DAY (TO_DATE ( '01/08/2021', 'dd/mm/yyyy'))
+ 1
- 1 / 86400
AS MyDataMeseA /*trasformo il testo-data ricevuto in input in una data in formato 31/mm/yyyy 23:59:59*/
FROM DUAL),
ListaMesiAnno /*elenco dei mesi/anni comprsi fra le 2 date in input*/
AS ( SELECT ADD_MONTHS (TRUNC (MyDataMeseDa, 'MM'), ROWNUM - 1)
AS MeseAnno
FROM DatePrompt
CONNECT BY ROWNUM <=
MONTHS_BETWEEN (TRUNC (MyDataMeseA, 'MM'),
TRUNC (MyDataMeseDa, 'MM'))
+ 1),
MwPotUp
AS (SELECT SUM (PotenzaGeneratore) / 1000 val
FROM wpowerdb.t_ana_eolico
WHERE codiceup = 'UP_BONORVA_1'
AND id_config = (SELECT MAX (id_config)
FROM wpowerdb.t_ana_eolico
WHERE codiceup = 'UP_BONORVA_1')),
CheckNoParAneomometri
/*Restituisce 1 se sono tutti NULL i parametri in input NULL,NULL e NULL, altrimenti 0
Da utilizzare quando si estraggono i dati vento, serve a stabilire se quest'ultimo vanno estratti per uno o più specifici anemometri o per tuttto l'impianto*/
AS (SELECT COUNT(*) val
FROM dual
WHERE NULL IS NULL
AND NULL IS NULL
AND NULL IS NULL),
ListaAneAG
/*In funzione dei parametri in input (NULL NOT NULL oppure NULL NOT NULL oppure NULL NOT NULL) individua l'elenco delle coppie Anemometro/Aerogeneratore di interesse,
prelevandole dai dati operativi o dai dati storici
N.B. L'estrazione si basa sul presupporto che i parametri IdAssettoGSE e NULL nonn possono essere contemporaneamente valorizzati*/
AS ( SELECT NULL IdAG,
NULL IdAne,
NULL idgeneratore,
NULL id_ane,
NULL nome_assetto
FROM dual /*Se in input è stato passato uno specifico anemometro (parametro NULL valorizzato), la query restituisce solo quell'identificativo di anemometro*/
WHERE NULL IS NOT NULL
AND NULL IS NULL
AND NULL IS NULL
UNION
SELECT DISTINCT /*Se in input è stato passato un'assetto validato da GSE (parametro IdAssettoGSE valorizzato), la query restituisce l'elenco degli identificativi di anemometro che appartengono a quell'assetto*/
a.identificativo_aero IdAG,
a.identificativo_ane IdAne,
a.id_int_aero idgeneratore,
a.id_int_ane id_ane,
a.nome_assetto nome_assetto
FROM T_ASSETTO_ELAB_CONS a
WHERE a.ID_ASSETTO = NULL /*492*/
AND NULL IS NULL
AND NULL IS NOT NULL
AND NULL IS NULL
UNION
SELECT DISTINCT /*Se in input è stato passato un'assetto Operatore (parametro IdAssettoOpe valorizzato), la query restituisce l'elenco degli identificativi di aerogeneratore che appartengono a quell'assetto*/
ag.identificativo_gse IdAG,
a.identificativo_ane IdAne,
assd.idgeneratore,
assd.id_ane,
ass.s_nome_assetto nome_assetto
FROM mpe_ope_assetto ass,
mpe_ope_assetto_dett assd,
t_ana_eolico ag,
t_ana_anemometri a
WHERE ass.id_ope_assetto = HEXTORAW(NULL) /*C1BC9F38F6190112E0530AA01523B9A3*/
AND ass.id_ope_assetto = assd.id_ope_assetto
AND assd.id_ane = a.id_ane
AND assd.idgeneratore = ag.idgeneratore
AND NULL IS NULL
AND NULL IS NULL
AND NULL IS NOT NULL
),
pPGreco
AS ( SELECT 3.14159265358979 val FROM dual),
/*
CURVE_PART1_DISP
------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
Query per il calcolo della disponibilità dei dati e della qualità degli stessi,
utili al calcolo delle curve A, B e C
-- TIPOLOGIE FLUSSI ESTRATTI
"V3D" --> Da utilizzare nel cruscotto per individuare i dati vento con direzione (Curve A, B e C)
"VEN" --> Da utilizzare nel cruscotto per individuare i dati vento con o senza direzione (Curve A e B)
"MIS" --> Da utilizzare nel cruscotto per individuare le misura (Curve A, B e C)
"IND" --> Da utilizzare nel cruscotto per individuare le indisponibilità (Curve A, B e C)
"C2D" --> Da utilizzare nel cruscotto per individuare i periodi di contemporanea disponibilità delle misure e dei dati vento con o senza direzione (Curve A e B)
"C3D" --> Da utilizzare nel cruscotto per individuare i periodi di contemporanea disponibilità delle misure e dei dati vento con direzione (Curve A, B e C)
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
*/
/*----------------------------------------------------------------------------
Le query in questa sezione servono ad estrarre l'elenco distinto delle date/ora
disponibili a sistema per ogni specifico ambito dati:
- DatiVentoDataOra (2D e 3D - per impianto. Da utilizzare per le curve A e C)
- DatiVentoDataOraAG (2D e 3D - per anemometri dell'assetto. Da utilizzare per la curva B)
- DatiMisureDataOra (per impianto. Da utilizzare per le curve A e C)
- DatiMisureDataOraAG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
- DatiIndispDataOra (per impianto. Da utilizzare per le curve A e C)
- DatiIndispDataOraAG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
- DatiOddDataOra
-----------------------------------------------------------------------------*/
DatiVentoDataOra
AS (SELECT DISTINCT * FROM (
/*dati operativi*/
SELECT DISTINCT
TRUNC (vo.lettura, 'HH24') DataOraVento,
DECODE (NVL (vo.direzione_vento, 0), 0, 0, 1) Flag3D
FROM MIFR_DFP_EOLICO@PSAT_LINK vo
WHERE vo.valido = 1
AND ( (vo.fonte IN (0, 2) AND NULL IS NULL)
OR
(vo.fonte = 5 AND NULL IS NOT NULL)
)
AND vo.codice_up = 'UP_BONORVA_1' /*codice_up corrisponde al campo MPE_COD_ANAG_UP.S_COD_IMPIANTO*/
AND vo.id_anemo = NVL(NULL, vo.id_anemo) /*id_anemo corrisponde al campo T_ANA_ANEMOMETRI.IDENTIFICATIVO_ANE*/
UNION ALL
/*dati storici*/
SELECT TRUNC (vs.d_data, 'HH24') DataOraVento,
DECODE (NVL (vs.n_direzione_vento, 0), 0, 0, 1) Flag3D
FROM mpe_sto_vento_dett vs,
mpe_cod_anag_up UP,
t_ana_anemometri a
WHERE vs.id_cod_anag = UP.id_cod_anag
AND vs.id_ane = a.id_ane(+)
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND a.identificativo_ane = NVL(NULL, a.identificativo_ane)
AND NULL IS NULL)),
Pre1DatiVentoDataOraAG
AS (SELECT DISTINCT * FROM (
/*dati operativi*/
SELECT TRUNC (vo.lettura, 'HH24') DataOraVento,
DECODE (NVL (vo.direzione_vento, 0), 0, 0, 1) Flag3D,
l.IdAne
FROM MIFR_DFP_EOLICO@PSAT_LINK vo,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne FROM ListaAneAG) l
WHERE vo.valido = 1
AND vo.fonte IN (0, 2, 5)
AND vo.codice_up = 'UP_BONORVA_1' /*codice_up corrisponde al campo MPE_COD_ANAG_UP.S_COD_IMPIANTO*/
AND vo.id_anemo = l.IdAne (+) /*id_anemo corrisponde al campo T_ANA_ANEMOMETRI.IDENTIFICATIVO_ANE*/
AND ( chk.val = 1
OR
l.IdAne IS NOT NULL)
UNION ALL
/*dati storici*/
SELECT TRUNC (vs.d_data, 'HH24') DataOraVento,
DECODE (NVL (vs.n_direzione_vento, 0), 0, 0, 1) Flag3D,
l.IdAne
FROM mpe_sto_ag_vento_dett vs,
mpe_cod_anag_up UP,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne, id_ane FROM ListaAneAG) l
WHERE vs.id_cod_anag = UP.id_cod_anag
AND vs.id_ane = l.id_ane(+)
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND ( chk.val = 1
OR
l.IdAne IS NOT NULL))
),
Pre2DatiVentoDataOraAG
AS (SELECT dv.DataOraVento, COUNT(DISTINCT dv.IdAne) AS IdAne
FROM Pre1DatiVentoDataOraAG dv
GROUP BY dv.DataOraVento
HAVING COUNT(DISTINCT dv.IdAne) = (SELECT COUNT(DISTINCT IdAne) FROM ListaAneAG)
),
DatiVentoDataOraAG
AS (SELECT DISTINCT dv.DataOraVento,
dv.Flag3D
FROM Pre1DatiVentoDataOraAG dv,
CheckNoParAneomometri chk,
Pre2DatiVentoDataOraAG dv2
WHERE (chk.val = 1 OR dv2.DataOraVento IS NOT NULL)
AND dv.DataOraVento = dv2.DataOraVento (+)
),
/* La query qui sotto è stata sostituita dalle precedenti: Pre1DatiVentoDataOraAG, Pre2DatiVentoDataOraAG e DatiVentoDataOraAG
DatiVentoDataOraAG
AS (/*
/*dati operativi*/
/* SELECT DISTINCT
TRUNC (vo.lettura, 'HH24') DataOraVento,
DECODE (NVL (vo.direzione_vento, 0), 0, 0, 1) Flag3D
FROM MIFR_DFP_EOLICO@PSAT_LINK vo,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne FROM ListaAneAG) l
WHERE vo.valido = 1
AND vo.fonte IN (0, 2, 5)
AND vo.codice_up = 'UP_BONORVA_1' */ /*codice_up corrisponde al campo MPE_COD_ANAG_UP.S_COD_IMPIANTO*/
/* AND vo.id_anemo = l.IdAne (+)*/ /*id_anemo corrisponde al campo T_ANA_ANEMOMETRI.IDENTIFICATIVO_ANE*/
/* AND ( chk.val = 1
OR
l.IdAne IS NOT NULL)
UNION */
/*dati storici*/
/* SELECT TRUNC (vs.d_data, 'HH24') DataOraVento,
DECODE (NVL (vs.n_direzione_vento, 0), 0, 0, 1) Flag3D
FROM mpe_sto_ag_vento_dett vs,
mpe_cod_anag_up UP,
CheckNoParAneomometri chk,
(SELECT DISTINCT IdAne, id_ane FROM ListaAneAG) l
WHERE vs.id_cod_anag = UP.id_cod_anag
AND vs.id_ane = l.id_ane(+)
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND ( chk.val = 1
OR
l.IdAne IS NOT NULL)
),*/
DatiMisureDataOra /*Curve A e C: Estrazione delle date disponibili a sistema per le misure*/
AS ( /*dati Misurati*/
SELECT TRUNC (mmd.d_data_ora, 'HH24') DataOraMisura
FROM MPE_MISURE_MENS mm, MPE_MISURE_MENS_DETT mmd
WHERE mm.n_recente = 1
AND mm.n_id_misura = mmd.n_id_misura
AND mm.s_cod_impianto = 'UP_BONORVA_1'
UNION
/*dati storici*/
SELECT TRUNC (ms.d_data_rif, 'HH24') DataOraMisura
FROM mpe_sto_misure_dett ms, mpe_cod_anag_up UP
WHERE ms.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND ms.n_recente = 1),
DatiMisureDataOraAG /*Curva B: Estrazione delle date disponibili a sistema per le misure*/
AS (/*dati storici*/
SELECT TRUNC (ms.d_data_rif, 'HH24') DataOraMisura
FROM mpe_sto_ag_misure_dett ms, mpe_cod_anag_up UP, (SELECT DISTINCT idgeneratore FROM ListaAneAG) l
WHERE ms.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND ms.n_recente = 1
AND ms.idgeneratore = l.idgeneratore),
DatiIndispDataOra
AS ( /*dati operativi*/
SELECT TRUNC (indd.data_ora, 'HH24') DataOraIndisp
FROM ( SELECT codiceup, anno, mese, MAX(versione) versione
FROM T_PROG_INDISP_MANC_PROD
WHERE codiceup = 'UP_BONORVA_1'
GROUP BY codiceup, anno, mese
) PilotaInd,
T_PROG_INDISP_MANC_PROD ind,
T_PROG_INDISP_MANC_PROD_DETT indd
WHERE PilotaInd.codiceup = ind.codiceup
AND PilotaInd.anno = ind.anno
AND PilotaInd.mese = ind.mese
AND PilotaInd.versione = ind.versione
AND ind.idinterno = indd.id_indisp
UNION
/*dati storici*/
SELECT TRUNC (inds.d_data_ora, 'HH24') DataOraIndisp
FROM mpe_sto_indisp_dett inds, mpe_cod_anag_up UP
WHERE inds.n_recente = 1
AND inds.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = 'UP_BONORVA_1'),
DatiIndispDataOraAG
AS (/*dati storici*/
SELECT TRUNC (inds.d_data_ora, 'HH24') DataOraIndisp
FROM mpe_sto_ag_indisp_dett inds, mpe_cod_anag_up UP, (SELECT DISTINCT idgeneratore FROM ListaAneAG) l
WHERE inds.n_recente = 1
AND inds.id_cod_anag = UP.id_cod_anag
AND UP.s_cod_impianto = 'UP_BONORVA_1'
AND inds.idgeneratore = l.idgeneratore
UNION
SELECT DataOraIndisp FROM DatiIndispDataOra),
DatiOddDataOra
AS (SELECT TRUNC (od.d_data_ora, 'HH24') DataOraOdd
FROM mpe_ordini_disp o, mpe_ordini_disp_dett od, DatePrompt
WHERE o.n_id_ordini_disp = od.n_id_ordine
AND o.S_COD_IMPIANTO = 'UP_BONORVA_1'
AND o.N_RECENTE = 1
AND od.d_data_ora BETWEEN MyDataMeseDa AND MyDataMeseA),
/*----------------------------------------------------------------------------
Le query in questa sezione servono ad individuare le date minima e massima
disponibili a sistema per ogni specifico ambito dati:
- RPT_DISP_DATI_VENTO_2D_3D (2D e 3D - per impianto o per anemometri dell'assetto. Da utilizzare per le curve A, B e C)
- RPT_DISP_DATI_MISURE (per impianto.Da utilizzare per la curve A e C)
- RPT_DISP_DATI_MISURE_AG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
- RPT_DISP_DATI_INDISP (per impianto.Da utilizzare per la curve A e C)
- RPT_DISP_DATI_INDISP_AG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
Dalle precedenti si ricavano le date minima e massima disponibili a sistema
fra i dati utilizzabili per il calcolo delle curve 2D e 3D:
- RPT_DISP_DATI_2D (per impianto.Da utilizzare per la curve A e C)
- RPT_DISP_DATI_2D_AG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
- RPT_DISP_DATI_3D (per impianto.Da utilizzare per la curve A e C)
- RPT_DISP_DATI_3D_AG (per aerogeneratori dell'assetto.Da utilizzare per la curva B)
-----------------------------------------------------------------------------*/
MinMaxDatiVento /*query propedeutica alla RPT_DISP_DATI_VENTO_2D_3D*/
AS ( SELECT MIN (DataOraVento) DataMin,
MAX (DataOraVento) DataMax,
Flag3D
FROM DatiVentoDataOra
GROUP BY Flag3D),
RPT_DISP_DATI_VENTO_2D_3D
AS /*
Report Disponibilità dei Dati Vento (2D+3D) e dei Dati Vento solo 3D
Restituisce 2 righe
Le informazioni restituite fanno parte di 2 diversi raggruppamenti:
(a) uno per i dati vento 2D+3D (FlagSoloConDirezione=0)
(b) uno per i soli dati vento 3D (FlagSoloConDirezione=1)
*/
(SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
0 FlagSoloConDirezione
FROM MinMaxDatiVento
UNION ALL
SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
1 FlagSoloConDirezione
FROM MinMaxDatiVento
WHERE Flag3D = 1),
MinMaxDatiVentoAG /*query propedeutica alla RPT_DISP_DATI_VENTO_2D_3D_AG*/
AS ( SELECT MIN (DataOraVento) DataMin,
MAX (DataOraVento) DataMax,
Flag3D
FROM DatiVentoDataOraAG
GROUP BY Flag3D),
RPT_DISP_DATI_VENTO_2D_3D_AG
AS /*
Report Disponibilità dei Dati Vento (2D+3D) e dei Dati Vento solo 3D
Restituisce 2 righe
Le informazioni restituite fanno parte di 2 diversi raggruppamenti:
(a) uno per i dati vento 2D+3D (FlagSoloConDirezione=0)
(b) uno per i soli dati vento 3D (FlagSoloConDirezione=1)
*/
(SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
0 FlagSoloConDirezione
FROM MinMaxDatiVentoAG
UNION ALL
SELECT MIN (DataMin) DataMin,
MAX (DataMax) DataMax,
1 FlagSoloConDirezione
FROM MinMaxDatiVentoAG
WHERE Flag3D = 1),
RPT_DISP_DATI_MISURE
AS /*Report Disponibilità dei Dati delle Misure per impianto*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiMisureDataOra),
RPT_DISP_DATI_MISURE_AG
AS /*Report Disponibilità dei Dati delle Misure per aerogfeneratori dell'assetto*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiMisureDataOraAG),
RPT_DISP_DATI_INDISP
AS /*Report Disponibilità dei Dati delle Indisponibilita per impianto*/
(SELECT MIN (DataOraIndisp) DataMin, MAX (DataOraIndisp) DataMax
FROM DatiIndispDataOra),
RPT_DISP_DATI_INDISP_AG
AS /*Report Disponibilità dei Dati delle Indisponibilita per aerogfeneratori dell'assetto (include quelle dell'impianto)*/
(SELECT MIN (DataOraIndisp) DataMin, MAX (DataOraIndisp) DataMax
FROM DatiIndispDataOraAG),
RPT_DISP_DATI_2D
AS /*Report Disponibilità dei Dati per produzione delle curve 2D */
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOra v, DatiMisureDataOra m
WHERE v.DataOraVento = m.DataOraMisura),
RPT_DISP_DATI_2D_AG
AS /*Report Disponibilità dei Dati per produzione delle curve 2D */
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOraAG v, DatiMisureDataOraAG m
WHERE v.DataOraVento = m.DataOraMisura),
RPT_DISP_DATI_3D
AS /*Report Disponibilità dei Dati per produzione delle curve 3D*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOra v, DatiMisureDataOra m
WHERE v.DataOraVento = m.DataOraMisura AND v.Flag3D = 1),
RPT_DISP_DATI_3D_AG
AS /*Report Disponibilità dei Dati per produzione delle curve 3D*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOraAG v, DatiMisureDataOraAG m
WHERE v.DataOraVento = m.DataOraMisura AND v.Flag3D = 1)
/*
CURVA_A_PART2_DISP
La seguente query mette insieme tutte le RPT_DISP%, utili al calcolo delle statistiche per la curva A, presenti nella query CURVE_PART1_DISP_PRES
e viene interrogata da cruscotto per riportare in mascherale date minima e massima calcolate per ogni ambito dati*/
SELECT 'V3D' Flusso, DataMin, DataMax
FROM RPT_DISP_DATI_VENTO_2D_3D
WHERE FlagSoloConDirezione = 1
UNION ALL
SELECT 'VEN' Flusso, DataMin, DataMax
FROM RPT_DISP_DATI_VENTO_2D_3D
WHERE FlagSoloConDirezione = 0
UNION ALL
SELECT 'MIS' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_MISURE
UNION ALL
SELECT 'IND' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_INDISP
UNION ALL
SELECT 'C2D' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_2D
UNION ALL
SELECT 'C3D' Flusso, DataMin, DataMax FROM RPT_DISP_DATI_3D
)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 7.98 157.77 0 0 1 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 7.98 157.77 0 0 1 0
Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: 131
El
|
|
|
Re: Long query with WITH not responding [message #685085 is a reply to message #685084] |
Mon, 18 October 2021 11:58   |
John Watson
Messages: 8803 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I was just looking at these structures: RPT_DISP_DATI_2D_AG
AS /*Report Disponibilità dei Dati per produzione delle curve 2D */
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOraAG v, DatiMisureDataOraAG m
WHERE v.DataOraVento = m.DataOraMisura),
RPT_DISP_DATI_3D_AG
AS /*Report Disponibilità dei Dati per produzione delle curve 3D*/
(SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOraAG v, DatiMisureDataOraAG m
WHERE v.DataOraVento = m.DataOraMisura AND v.Flag3D = 1) you then use UNION ALL to concatenate the results of these and several other queries. The way you have written it will take multiple passes through the data.
I haven't tried to work out any detail, but would this give you the same result:
SELECT MIN (DataOraMisura) DataMin, MAX (DataOraMisura) DataMax
FROM DatiVentoDataOraAG v, DatiMisureDataOraAG m
WHERE v.DataOraVento = m.DataOraMisura
group by rollup (v.Flag3D)
Getting rid of all those WITHs should be a lot quicker.
|
|
|
|
Re: Long query with WITH not responding [message #685150 is a reply to message #685086] |
Fri, 29 October 2021 04:44  |
 |
Reversi72
Messages: 10 Registered: October 2021
|
Junior Member |
|
|
I write this last post to inform those interested in knowing how this story ended. I spoke to my client's DBA. I learned that our development environment dedicates 24 GB of RAM and 0.20 (1/5) CPU to the Oracle RDBMS. In the opinion of the DBA, the problem was related to the physical resources of the server and advised me to reduce the number of rows in the query. In short, I have simplified (reduced the number of instructions) the queries, now the parsing is immediate, the execution of the data extraction is less efficient.
Thanks everyone for the support.
[Updated on: Fri, 29 October 2021 04:45] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Mon Mar 20 04:08:34 CDT 2023
|