SQL Query Optimization in Oracle The following query
was canceled after 15 hours and 37 minutes
execution.
SQL> select count (*) from (SELECT BITOWN03.BS_C_CLIENTES_BIT_03.DNICIF_DE, BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID 2, 3 HSCONTRATOS_ESTADOS.ESTADO_DE
, BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT
4, 5 BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID 6 FROM BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03
7, 8 BITOWN03.LK_C_ESTADOS_BIT_03 HSCONTRATOS_ESTADOS,
BITOWN03.BS_C_CLIENTES_BIT_03 9, 10
BITOWN03.BS_C_CONTRATOS_BIT_03 CONTRACT = 11 WHERE BITOWN03.BS_C_CLIENTES_BIT_03.ABONADO_ID CONTRATOS.ABONADO_ID
BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.CONTRATO_ID = 12 AND 13 AND CONTRATOS.CONTRATO_ID
BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID HSCONTRATOS_ESTADOS.ESTADO_ID
= 14 BETWEEN 15 AND BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.FEC_ESTADO_DT to_date ('01-06-2005 00:00:00 ',' DD-MM-YYYY HH24: MI: SS ') AND 16
to_date ('30-23 06-2005: 59:59 ',' DD-MM-YYYY HH24: MI: SS ') AND 17
BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03.ESTADO_ID =' BA '); BITOWN03.HS_C_ESTADOS_CONTRATO_BIT_03,
* ERROR at line 7: ORA-00028: your session has Been Killed
Elapsed: 15:37:01.66 the following implementation plan
SQL> @ C: \\ ORACLE \\ ORA92 \\ RDBMS \\ ADMIN \\ UTLXPLS PLAN_TABLE_OUTPUT
-------------------------------------- -------------------------------------------------- -------------------------------- ---------------- -------------------------------------------------- -------------------------------------------------- -
Predicate Information (identified by Operation id): --------------------------------------
------------- 5 - access ("HSCONTRATOS_ESTADOS. ESTADO_ID" = 'BA')
6 - access ("HS_C_ESTADOS_CONTRATO_BIT_03. FEC_ESTADO_DT> = TO_DATE ('2005- 06-01 00:00:00 ',' yyyy-mm-dd HH24: mi: ss') AND "HS_C_ESTADOS_CONTRATO_BIT_03. ESTADO_ID" = 'BA' AND
"HS_C_ESTADOS_CONTRATO_BIT_03. FEC_ESTADO_DT"
filter ("HS_C_ESTADOS_CONTRATO_BIT_03 . ESTADO_ID "=" HSCONTRATOS_ESTADOS. ESTADO_ID "AND
" HS_C_ESTADOS_CONTRATO_BIT_03. ESTADO_ID "= 'BA') 9 - filter (" BS_C_CLIENTES_BIT_03. ABONADO_ID "=" contracts "." ABONADO_ID)
10 - access ("HS_C_ESTADOS_CONTRATO_BIT_03." CONTRATO_ID "=" Contracts. "CONTRATO_ID)
Note: cpu costing is off
**************** ************************************************** SOLUTION **************
the case. ************************************************ ********************************
The tables contain statistics faithful. The Cartesian product not deceived: cross 58 GB to meet our request. Anyway, something is wrong. That plan is not optimal.
statistics generated by the next call to DBMS_STATS
.
SQL> begin 2 DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME => user,
3 TABNAME => table,
4 ESTIMATE_PERCENT => dbms_stats.auto_sample_size,
5 METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1', 6 DEGREE
=> 4);
7 end;
8 /
PL / SQL procedure successfully completed.
must not only analyze the tables, but also the indices. Some of them are analyzed using the procedure DBMS_STATS.GATHER_INDEX_STATS , but one of the tables is partitioned. The best and simplest is to include the parameter
CASCADE => TRUE
in the call to DBMS_STATS .
is true that the cost of increased analysis of these tables. In addition, the CASCADE parameter affects not only rates but also materialized views, partitions, etc.. (Ie, all its dependencies).
With tables analyzed in this way, the new implementation plan is this:
SQL> @c:\oracle\ora92\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
BY INDEX ROWID 00:00:00 ',
' yyyy-mm-dd HH24: mi: ss ') AND "HS_C_ESTADOS_CONTRATO_BIT_03." ESTADO_ID "=' BA 'AND
" HS_C_ESTADOS_CONTRATO_BIT_03. "FEC_ESTADO_DT"
HH24: mi: ss') )
filter ("HS_C_ESTADOS_CONTRATO_BIT_03." ESTADO_ID "= 'BA')
6 - access (" HSCONTRATOS_ESTADOS. "ESTADO_ID" = 'BA')
9 - access ("BS_C_CLIENTES_BIT_03." ABONADO_ID "=" Contracts. " "ABONADO_ID)
Note: cpu costing is off 30 rows selected
<=TO_DATE('2005-06-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
.
The execution now takes only:
3 seconds.
The cost of testing increases of 26 minutes 32 minutes. The gain justifies the cost.
Okay. :-)
# posted by Javier Morales @ 6:13 PM 0 comments
Monday, October 17, 2005
NOT IN vs. NOT EXISTS.
to Fernando, which awaits. :-P The following query was canceled after 5 hours and 11 minutes
run.
SELECT count (CLI.COD_ABONADO)
BITOWN02.TM_C_CLIENTES_SAC_02 FROM CLI,
BITOWN02.TE_ERRORES_BIT_02 WHERE YOU
TE.FILA_ID CLI.ROWID = (+)
AND 'TM_C_CLIENTES_SAC_02' = TE.TABLA_DE (+) TE.ERROR_ID IS NULL AND CLI.COD_ABONADO AND NOT IN (SELECT
FROM CU.ABONADO_id BITOWN03.BS_C_CONTRATOS_BIT_03 CONT, CU BITOWN03.BS_C_CUENTAS_BIT_03 WHERE CU.CUENTA_ID = CONT.CUENTA_ID
)
the following implementation plan
SQL> @ c: \\ oracle \\ ora92 \\ rdbms \\ admin \\ utlxpls
PLAN_TABLE_OUTPUT ------------------------------------------------
----------- ------------------------------------- -------------------------------------------------- --------------------------------------------
HASH JOIN OUTER ACCESS BY INDEX ROWID
: B1)))
3 - filter ("TE_M_ERRORES_BIT_01. ERROR_ID" IS NULL)
4 - access ("SYS_ALIAS_1." ROWID = "TE_M_ERRORES_BIT_01. FILA_ID "(+))
10 - filter (LNNVL ( "CU." ABONADO_ID "
: B1))
11 - access (" CU "." CUENTA_ID "=" account "." CUENTA_ID)
Note: CPU Costing is off 30 rows selected
.
<=TO_DATE('2005-06-30 23:59:59', 'yyyy-mm-dd
********************************************** **********************************
SOLUTION to the case.
************************************************ ********************************
In this case, consultation with IN has an acceptable cost. However, after five hours of execution, one suspects that things are not going well. The implementation strategy is to make two FILTER: one for the Client OuterJoin without errors and the second to combine (in a very heavy NestedLoops) with contract accounts.
As the subquery is becoming even heavier than the main can be replaced by IN
EXISTS clause is
a good strategy.
Yes, I have also been careful not null codes to answer the inquiry, as NOT IN and NOT EXISTS
Explain plan for SELECT count (CLI.COD_ABONADO)
BITOWN02.TM_C_CLIENTES_SAC_02 FROM CLI,
BITOWN02.TE_ERRORES_BIT_02 WHERE YOU CLI.ROWID = TE.FILA_ID (+)
AND 'TM_C_CLIENTES_SAC_02' = TE.TABLA_DE (+)
TE.ERROR_ID IS NULL AND
AND NOT EXISTS (SELECT null
BITOWN03.BS_C_CONTRATOS_BIT_03 CONT FROM, WHERE BITOWN03.BS_C_CUENTAS_BIT_03 CU CLI.COD_ABONADO = CU.ABONADO_ID AND CU.CUENTA_ID = CONT.CUENTA_ID)
The resulting execution plan appears to be similar to the above, including the cost seems worse.
SQL> @ c: \\ oracle \\ ora92 \\ rdbms \\ admin \\ utlxpls
PLAN_TABLE_OUTPUT
------------------------- --------------------------------------------------
------- ----------------------------------------- -------------------------------------------------- --------------------------------------------------
---------
11
11 - access ("CU". "CUENTA_ID" = "CONT." CUENTA_ID) Note: cpu costing is off
I launch the performance:
28 seconds.
Très bien. :-)
# posted by Javier Morales @ 6:12 PM 0 comments
Thursday, October 13, 2005
Say it otherwise.
<>
To Manel Moreno, who has given me no kiss for that.
: P
<>
The following query takes 11 hours to run.
SELECT DISTINCT A.
NODO_ID, B. NODO_B_ID NODO_EQ1
FROM BITOWN03.BS_R_NODOS_BIT_03 A,
BITOWN03.RE_R_CONEX_EXTERNAS_BIT_03 B, C BITOWN03.TMP_NODOS_OK_EST_BIT_03 NODO_ID = B WHERE A. AND B. NODO_A_ID
NODO_B_ID = C. NODO_ID;
the following implementation plan SQL> @ c: \\ oracle \\ ora92 \\ rdbms \\ admin \\ utlxpls
PLAN_TABLE_OUTPUT
------------- --------------------------------------------------
---------------------- -------------------------- -------------------------------------------------- ------- solution to the case.
************************************************ ********************************
Another Cartesian product. In this case the Cartesian knows what he does. Cross almost 6,000 million rows (a total of 61 gigabytes of data) and at minimal cost. What a paradox.
A detail to understand this decision: there are no restrictions on Primary Key or Foreign Key
or unique indexes, constraints or Not Null. Because of this, Oracle is practical to combine all results in a table (4.5 million) over the 1400 rows in another table, a
"everyone with everyone" .
Not bad. However, no information to Oracle, much to analyze the tables, it will not be able to obtain a priori
. We are interested only unique values \u200b\u200bin the relationship table, which exist in their related tables, but as I say, there is nothing to provide that information to Oracle.
change some details of the consultation and leave it like that:
SELECT DISTINCT A.
NODO_ID, B. NODO_B_ID NODO_EQ1
FROM BITOWN03.BS_R_NODOS_BIT_03 A, (select distinct nodo_a_id, nodo_b_id from BITOWN03.RE_R_CONEX_EXTERNAS_BIT_03) B, C
BITOWN03.TMP_NODOS_OK_EST_BIT_03 NODO_ID = B WHERE A. AND B. NODO_A_ID
NODO_B_ID = C. NODO_ID;
report of two things : that I have interest in obtaining the separate codes in the relationship table, and also exist in the other two tables.
The implementation plan to run completely changed as I have said otherwise
. Now Oracle does this other implementation plan.
SQL> @ c: \\ oracle \\ ora92 \\ rdbms \\ admin \\ utlxpls
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
VIEW
6 - access ("B". "NODO_B_ID" = "C". "NODO_ID)
7 - access (" A "." NODO_ID "=" B "." NODO_A_ID)
The cost now seems completely shot. 13626
cost units. Regarding the previous cost of only 23. But now appears the Cartesian and it seems that the implementation is more faithful to what we want. I have just released implementation:
one minute and twenty seconds. Well
.
# posted by Javier Morales @ 6:11 PM 0 comments
Tuesday, October 11, 2005
Poor Cardinality
For Cristina Alvarez, who trusted me from day one and hid it with all his soul.
:)
The following query was canceled after one hour and twenty minutes of execution.
SELECT N2.ENTIDAD_ID, CLI.rowid Row_A, cli .*,
MAX (REL.ABONADO_PADRE_ID) OVER (PARTITION BY ABONADO_HIJO_ID) REL_ABONADO_PADRE_ID BITOWN03.BS_V_MGEC_NODO_BIT_03 FROM N1, N2 BITOWN03.BS_V_MGEC_NODO_BIT_03 ,
BITOWN03. BS_V_MGEC_REL_NODOS_BIT_03 RN,
BITOWN02.TM_C_CLIENTES_SAC_02 CLI, REL BITOWN03.RE_C_RELACIONES_ABONADO_BIT_03
N1.ENTIDAD_ID = CLI.COD_DNICIF
WHERE AND AND N1.NODO_ID = RN.NODO_ID
RN.FECHA_FIN_DT RN.TIPO_RELACION_ID = 2 AND IS NULL AND RN.NODO_PADRE_ID
AND CLI = N2.NODO_ID . COD_ABONADO = REL.ABONADO_HIJO_ID (+);
the following implementation plan
PLAN_TABLE_OUTPUT
-------------------- --------------------------------------------------
----------------------------- ------------------- --------------------------------------------------
------------------------------ Rows 1
filter ("CLI". "COD_ABONADO" = "REL." ABONADO_HIJO_ID "(+))
11 - filter ("N1". "ENTIDAD_ID" = "CLI". "COD_DNICIF)
12 - access (" N1 "." NODO_ID "=" RN "." NODO_ID)
14 - access ("RN". "NODO_PADRE_ID" = "N2". "NODO_ID)
********* ************************************************** *********************
solution to the case.
************************************************ ********************************
Omitting the detail that the clause distinct enough. The plans maintain that resulting from similar performances.
seems quite acceptable plan. However, a Cartesian product attention ... "Only get a row? In this case, the Cartesian will not hurt. We will see the total of rows in the table if the table has generated statistics (column num_rows
of
user_t can give us a clue) and if the filter operation 7 really solves a single row.
SQL> select count (*) from bitown03.BS_V_MGEC_REL_NODOS_BIT_03;
COUNT (*) ----------
118,907
SQL> select table_name, NUM_ROWS from dba_tables 2 WHERE table_name = 'BS_V_MGEC_REL_NODOS_BIT_03';
NUM_ROWS TABLE_NAME ------------------------------ ----------
BS_V_MGEC_REL_NODOS_BIT_03 118,907
SQL> select count (*) from bitown03.BS_V_MGEC_REL_NODOS_BIT_03
TIPO_RELACION_ID 2 where = 2 AND FECHA_FIN_DT IS NULL; -> FILTER OPERATING 7
COUNT (*) ----------
12844 Apparently not ... We're seeing a Cartesian product of 1.5 million rows (FULL TABLE SCAN operation 9) on 12,844 items. Oracle is bad estimating the cardinality of the rows. Cree for filter 7 only get one row and therefore does not trigger the Cartesian product cost. Fatty
error.
must correctly analyze the tables involved, indicating that they also analyze the values \u200b\u200bof the columns involved. The commands to perform this analysis are:
SQL> exec dbms_stats.gather_table_stats (OWNNAME => 'user', TABNAME => 'BS_V_MGEC_REL_NODOS_BIT_03' METHOD_OPT => 'for all columns');
PL / SQL procedure successfully completed.
Elapsed: 00:00:07.71 SQL> exec dbms_stats.gather_table_stats (OWNNAME => 'user', TABNAME => 'BS_V_MGEC_NODO_BIT_03' METHOD_OPT => 'for all columns');
PL / SQL procedure successfully completed.
Elapsed: 00:00:14.57
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
7 - filter ("RN". "TIPO_RELACION_ID" = 2 AND "RN". "FECHA_FIN_DT" IS NULL) 11 - access ("CLI". "COD_ABONADO" = "REL." "(+))
ABONADO_HIJO_ID filter ("CLI". "COD_ABONADO" = "REL." "(+))
ABONADO_HIJO_ID
Note: cpu costing is off Indeed, the plan's cost is higher, but REAL.
has taken only 23 seconds!
. In some cases this was to replicate the production environment for use as a test. In others, simply test allowed to recover the backup database on another machine and leave the operational database as a second environment. 
However, in cases where the name should be different, we must be very precise with the steps. Previous two steps included as options in case they are useful. 
------------------------------- 
SHUTDOWN IMMEDIATE; 