Come ti distruggo l’oracolo–RELOADED

(originariamente pubblicato sul mio vecchio blog il 27/3/2007)

Ero intento a chiudere gli ultimi aggiornamenti richiesti dal settore sviluppo che, a detta di %UberDEV%, avrebbero drammaticamente migliorato i tempi di esecuzione.

Terminato il rilascio, vista che l’oracolo è da sempre il pezzo in cui più si evidenziano problematiche, ero intento a guardare %WebManagement% per scorgere i miglioramenti sperati.

Purtroppo così non era. Lla CPU di %SuperMacchinone% che andava direttamente al 100% mi invitava a dare un’occhiata alle numerose novelle che l’oracolo poteva raccontarmi nella sezione %PerfMon%.

Cliccando a destra e a manca sulla console mi salta all’occhio una query di cui l’oracolo mi riporta, in prima istanza, solo un’estratto, già di per se spaventoso…

Un ulteriore clic del mouse mi permette di scoprire un gioiello di un intelligenza superiore:

SELECT PARTS.DOCUMENT_ID, PARTS.TIMESTAMP, PARTS.NAME_ID, IDXFIELDS.PART_TYPE_ID, IDXFIELDS.IS_REPEATED, ISBINARY, IDXFIELDS.TARGET, IDXFIELDS.NAME, IDXFIELDS.PATH||’\'||PARTS.PATH AS PATH FROM( SELECT UPS.DOCUMENT_ID, PAR.TIMESTAMP, PAR.NAME_ID, PAR.PATH, 0 ISBINARY FROM( SELECT DOCUMENT_ID FROM PARENT PR WHERE TIMESTAMP > :B3 AND DOCTYPE_ID = :B1 GROUP BY DOCUMENT_ID, TIMESTAMP, NAME_ID, SUBFOLDER, FILE_NAME UNION SELECT PA.DOCUMENT_ID FROM CHILD CH LEFT JOIN MAIN_CHILD MC ON CH.ID = MC.CHILD_ID LEFT JOIN PARENT PA ON MC.PARENT_ID = PA.ID WHERE CH.TIMESTAMP > :B3 AND PA.DOCTYPE_ID = :B1 GROUP BY PA.DOCUMENT_ID, CH.TIMESTAMP, CH.NAME_ID, CH.SUBFOLDER, CH.FILE_NAME UNION SELECT PA.DOCUMENT_ID FROM FULLTEXT FT LEFT JOIN PARENT PA ON FT.PARENT_ID = PA.ID WHERE FT.TIMESTAMP > :B3 AND PA.DOCTYPE_ID = :B1 AND FT.ISBINARY = 0 GROUP BY PA.DOCUMENT_ID, FT.TIMESTAMP, FT.NAME_ID, FT.SUBFOLDER, FT.FILE_NAME) UPS LEFT JOIN (SELECT DOCUMENT_ID, TIMESTAMP, NAME_ID, SUBFOLDER||’\'||FILE_NAME PATH FROM PARENT PR GROUP BY DOCUMENT_ID, TIMESTAMP, NAME_ID, SUBFOLDER, FILE_NAME) PAR ON UPS.DOCUMENT_ID = PAR.DOCUMENT_ID UNION SELECT CHIL.DOCUMENT_ID, CHIL.TIMESTAMP , CHIL.NAME_ID , CHIL.PATH, 0 ISBINARY FROM( SELECT DOCUMENT_ID FROM PARENT PR WHERE TIMESTAMP > :B3 AND DOCTYPE_ID = :B1 GROUP BY DOCUMENT_ID, TIMESTAMP, NAME_ID, SUBFOLDER, FILE_NAME UNION SELECT PA.DOCUMENT_ID FROM CHILD CH LEFT JOIN MAIN_CHILD MC ON CH.ID = MC.CHILD_ID LEFT JOIN PARENT PA ON MC.PARENT_ID = PA.ID WHERE CH.TIMESTAMP > :B3 AND PA.DOCTYPE_ID = :B1 GROUP BY PA.DOCUMENT_ID, CH.TIMESTAMP, CH.NAME_ID, CH.SUBFOLDER, CH.FILE_NAME UNION SELECT PA.DOCUMENT_ID FROM FULLTEXT FT LEFT JOIN PARENT PA ON FT.PARENT_ID = PA.ID WHERE FT.TIMESTAMP > :B3 AND PA.DOCTYPE_ID = :B1 AND FT.ISBINARY = 0 GROUP BY PA.DOCUMENT_ID, FT.TIMESTAMP, FT.NAME_ID, FT.SUBFOLDER, FT.FILE_NAME) UPS LEFT JOIN (SELECT PA.DOCUMENT_ID, CH.TIMESTAMP, CH.NAME_ID, CH.SUBFOLDER||’\'||CH.FILE_NAME PATH FROM CHILD CH LEFT JOIN MAIN_CHILD MC ON CH.ID = MC.CHILD_ID LEFT JOIN PARENT PA ON MC.PARENT_ID = PA.ID GROUP BY PA.DOCUMENT_ID, CH.TIMESTAMP, CH.NAME_ID, CH.SUBFOLDER, CH.FILE_NAME) CHIL ON UPS.DOCUMENT_ID = CHIL.DOCUMENT_ID UNION SELECT FTXT.DOCUMENT_ID, FTXT.TIMESTAMP, FTXT.NAME_ID, FTXT.PATH, FTXT.ISBINARY FROM( SELECT DOCUMENT_ID FROM PARENT PR WHERE TIMESTAMP > :B3 AND DOCTYPE_ID = :B1 GROUP BY DOCUMENT_ID, TIMESTAMP, NAME_ID, SUBFOLDER, FILE_NAME UNION SELECT PA.DOCUMENT_ID FROM CHILD CH LEFT JOIN MAIN_CHILD MC ON CH.ID = MC.CHILD_ID LEFT JOIN PARENT PA ON MC.PARENT_ID = PA.ID WHERE CH.TIMESTAMP > :B3 AND PA.DOCTYPE_ID = :B1 GROUP BY PA.DOCUMENT_ID, CH.TIMESTAMP, CH.NAME_ID, CH.SUBFOLDER, CH.FILE_NAME UNION SELECT PA.DOCUMENT_ID FROM FULLTEXT FT LEFT JOIN PARENT PA ON FT.PARENT_ID = PA.ID WHERE FT.TIMESTAMP > :B3 AND PA.DOCTYPE_ID = :B1 AND FT.ISBINARY = 0 GROUP BY PA.DOCUMENT_ID, FT.TIMESTAMP, FT.NAME_ID, FT.SUBFOLDER, FT.FILE_NAME) UPS LEFT JOIN (SELECT PA.DOCUMENT_ID, FT.TIMESTAMP, FT.NAME_ID, FT.SUBFOLDER||’\'||FT.FILE_NAME PATH, ISBINARY FROM FULLTEXT FT LEFT JOIN PARENT PA ON FT.PARENT_ID = PA.ID GROUP BY PA.DOCUMENT_ID, FT.TIMESTAMP, FT.NAME_ID, FT.SUBFOLDER, FT.FILE_NAME, FT.ISBINARY) FTXT ON UPS.DOCUMENT_ID = FTXT.DOCUMENT_ID ) PARTS LEFT JOIN (SELECT TY.DOCTYPEID,PN.PART_TYPE_ID,PN.ID NAME_ID, LC.MACHINE_NAME||DST.FOLDER_NAME||DT.FOLDER_NAME||’\'||PN.FOLDER_NAME PATH, TY.IS_REPEATED, TY.TARGET, TY.NAME FROM( SELECT DISTINCT(DOCPARTNAME),DOCTYPEID,IS_REPEATED, TARGET, NAME FROM INDEXFIELDS WHERE DOCTYPEID = :B1 ) TY LEFT JOIN PART_NAME PN ON TY.DOCPARTNAME=PN.ID LEFT JOIN DOCTYPE DT ON TY.DOCTYPEID=DT.ID LEFT JOIN DESTINATION DST ON DST.LOCATION_ID = :B2 LEFT JOIN LOCATION LC ON LC.ID = :B2 ) IDXFIELDS ON PARTS.NAME_ID = IDXFIELDS.NAME_ID GROUP BY PARTS.DOCUMENT_ID, PARTS.TIMESTAMP, PARTS.NAME_ID, IDXFIELDS.PART_TYPE_ID, IDXFIELDS.IS_REPEATED, ISBINARY, IDXFIELDS.TARGET, IDXFIELDS.NAME, IDXFIELDS.PATH||’\'||PARTS.PATH ORDER BY PARTS.DOCUMENT_ID ,IDXFIELDS.PATH||’\'||PARTS.PATH, TIMESTAMP

che l’oracolo fa fatica a comprendere:

Operazione	Oggetto	Object type	Ordine	Righe	Dimensione (KB)	Costo	Tempo (sec)	Costo CPU	Costo I/O
SELECT STATEMENT			97			69782
SORT GROUP BY			96	257	84.077	69782	838	8065675466	68396
HASH JOIN RIGHT OUTER			95	20682	6,766.084	69779	838	8046497134	68396
VIEW			19	11	2.481	12	1	363996	12
MERGE JOIN OUTER			18	11	1.568	12	1	363996	12
MERGE JOIN OUTER			13	11	1.010	8	1	283570	8
NESTED LOOPS OUTER			8	11	0.730	4	1	203365	4
NESTED LOOPS OUTER			5	11	0.602	3	1	111169	3
TABLE ACCESS BY INDEX ROWID	INDEXFIELDS	TABLE	2	11	0.483	2	1	19193	2
INDEX RANGE SCAN	INDEXF_DOCTYPEID_IDX	INDEX	1	11		1	1	9321	1
TABLE ACCESS BY INDEX ROWID	DOCTYPE	TABLE	4	1	0.011	1	1	8361	1
INDEX UNIQUE SCAN	SYS_C007308	INDEX (UNIQUE)	3	1		0		1050	0
TABLE ACCESS BY INDEX ROWID	PART_NAME	TABLE	7	1	0.012	1	1	8381	1
INDEX UNIQUE SCAN	SYS_C007342	INDEX (UNIQUE)	6	1		0		1050	0
BUFFER SORT			12	1	0.025	7	1	275189	7
VIEW			11	1	0.025	0		7291	0
TABLE ACCESS BY INDEX ROWID	DESTINATION	TABLE	10	1	0.028	2	1	14633	2
INDEX RANGE SCAN	LOCATION_ID_IDX	INDEX	9	1		1	1	7321	1
BUFFER SORT			17	1	0.051	12	1	356705	12
VIEW			16	1	0.051	0		7311	0
TABLE ACCESS BY INDEX ROWID	LOCATION	TABLE	15	1	0.054	1	1	8381	1
INDEX UNIQUE SCAN	SYS_C007323	INDEX (UNIQUE)	14	1		0		1050	0
VIEW			94	20682	2,100.516	69766	838	8041153438	68384
SORT UNIQUE			93	20682	2,198.066	69766	838	1666537346	10864
UNION-ALL			92
HASH JOIN OUTER			41	3972	403.406	11054	133	1657075917	10769
VIEW			37	3972	50.426	4782	58	1009381277	4609
SORT UNIQUE			36	3972	251.002	4782	58	16162781	150
UNION-ALL			35
HASH GROUP BY			22	53	2.743	153	2	16162781	150
TABLE ACCESS BY INDEX ROWID	PARENT	TABLE	21	53	2.743	151	2	4496026	150
INDEX RANGE SCAN	PAR_DOCTYPE_ID_IDX	INDEX	20	6591		19	1	1453707	19
HASH GROUP BY			29	179	14.509	2757	34	648352054	2646
HASH JOIN			28	179	14.509	2755	34	636591944	2646
TABLE ACCESS BY INDEX ROWID	PARENT	TABLE	24	6591	83.675	151	2	4166456	150
INDEX RANGE SCAN	PAR_DOCTYPE_ID_IDX	INDEX	23	6591		19	1	1453707	19
HASH JOIN			27	11665	797.412	2604	32	627360489	2496
TABLE ACCESS FULL	CHILD	TABLE	25	11665	672.104	1989	24	338633062	1931
TABLE ACCESS FULL	MAIN_CHILD	TABLE	26	984212	10,572.590	597	8	185646627	565
HASH GROUP BY			34	3740	233.750	1872	23	344866443	1813
HASH JOIN			33	3740	233.750	1749	21	327355578	1693
TABLE ACCESS BY INDEX ROWID	PARENT	TABLE	31	6591	83.675	151	2	4166456	150
INDEX RANGE SCAN	PAR_DOCTYPE_ID_IDX	INDEX	30	6591		19	1	1453707	19
TABLE ACCESS FULL	FULLTEXT	TABLE	32	3740	186.270	1598	20	318916622	1543
VIEW			40	433482	38,522.326	6263	76	600840790	6160
HASH GROUP BY			39	433482	21,166.113	6263	76	600840790	6160
TABLE ACCESS FULL	PARENT	TABLE	38	433482	21,166.113	865	11	144343741	840
HASH JOIN OUTER			67	9026	916.703	35434	426	3615353340	34813
VIEW			59	3972	50.426	4782	58	1009381277	4609
SORT UNIQUE			58	3972	251.002	4782	58	16162781	150
UNION-ALL			57
HASH GROUP BY			44	53	2.743	153	2	16162781	150
TABLE ACCESS BY INDEX ROWID	PARENT	TABLE	43	53	2.743	151	2	4496026	150
INDEX RANGE SCAN	PAR_DOCTYPE_ID_IDX	INDEX	42	6591		19	1	1453707	19
HASH GROUP BY			51	179	14.509	2757	34	648352054	2646
HASH JOIN			50	179	14.509	2755	34	636591944	2646
TABLE ACCESS BY INDEX ROWID	PARENT	TABLE	46	6591	83.675	151	2	4166456	150
INDEX RANGE SCAN	PAR_DOCTYPE_ID_IDX	INDEX	45	6591		19	1	1453707	19
HASH JOIN			49	11665	797.412	2604	32	627360489	2496
TABLE ACCESS FULL	CHILD	TABLE	47	11665	672.104	1989	24	338633062	1931
TABLE ACCESS FULL	MAIN_CHILD	TABLE	48	984212	10,572.590	597	8	185646627	565
HASH GROUP BY			56	3740	233.750	1872	23	344866443	1813
HASH JOIN			55	3740	233.750	1749	21	327355578	1693
TABLE ACCESS BY INDEX ROWID	PARENT	TABLE	53	6591	83.675	151	2	4166456	150
INDEX RANGE SCAN	PAR_DOCTYPE_ID_IDX	INDEX	52	6591		19	1	1453707	19
TABLE ACCESS FULL	FULLTEXT	TABLE	54	3740	186.270	1598	20	318916622	1543
VIEW			66	985053	87,538.890	30634	368	2503961113	30204
HASH GROUP BY			65	985053	76,957.266	30634	368	2503961113	30204
HASH JOIN RIGHT OUTER			64	985053	76,957.266	12208	147	1323465319	11981
TABLE ACCESS FULL	PARENT	TABLE	60	433482	4,233.223	865	11	144343741	840
HASH JOIN RIGHT OUTER			63	985053	67,337.607	7021	85	868945197	6872
TABLE ACCESS FULL	MAIN_CHILD	TABLE	61	984212	10,572.590	597	8	185646627	565
TABLE ACCESS FULL	CHILD	TABLE	62	985053	56,755.983	1981	24	289380412	1931
HASH JOIN OUTER			91	7684	877.957	22758	274	2731162224	22289
VIEW			85	3972	50.426	4782	58	1009381277	4609
SORT UNIQUE			84	3972	251.002	4782	58	16162781	150
UNION-ALL			83
HASH GROUP BY			70	53	2.743	153	2	16162781	150
TABLE ACCESS BY INDEX ROWID	PARENT	TABLE	69	53	2.743	151	2	4496026	150
INDEX RANGE SCAN	PAR_DOCTYPE_ID_IDX	INDEX	68	6591		19	1	1453707	19
HASH GROUP BY			77	179	14.509	2757	34	648352054	2646
HASH JOIN			76	179	14.509	2755	34	636591944	2646
TABLE ACCESS BY INDEX ROWID	PARENT	TABLE	72	6591	83.675	151	2	4166456	150
INDEX RANGE SCAN	PAR_DOCTYPE_ID_IDX	INDEX	71	6591		19	1	1453707	19
HASH JOIN			75	11665	797.412	2604	32	627360489	2496
TABLE ACCESS FULL	CHILD	TABLE	73	11665	672.104	1989	24	338633062	1931
TABLE ACCESS FULL	MAIN_CHILD	TABLE	74	984212	10,572.590	597	8	185646627	565
HASH GROUP BY			82	3740	233.750	1872	23	344866443	1813
HASH JOIN			81	3740	233.750	1749	21	327355578	1693
TABLE ACCESS BY INDEX ROWID	PARENT	TABLE	79	6591	83.675	151	2	4166456	150
INDEX RANGE SCAN	PAR_DOCTYPE_ID_IDX	INDEX	78	6591		19	1	1453707	19
TABLE ACCESS FULL	FULLTEXT	TABLE	80	3740	186.270	1598	20	318916622	1543
VIEW			90	838627	85,173.055	17961	216	1634412597	17680
HASH GROUP BY			89	838627	49,957.272	17961	216	1634412597	17680
HASH JOIN RIGHT OUTER			88	838627	49,957.272	5449	66	687862659	5331
TABLE ACCESS FULL	PARENT	TABLE	86	433482	4,233.223	865	11	144343741	840
TABLE ACCESS FULL	FULLTEXT	TABLE	87	838627	41,767.556	1591	20	276621199	1543