The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 185): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `forecast_billing_date` is indexed, the index won’t be used as it’s wrapped with the function `TO_CHAR`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
2. Avoid Calling Functions With Indexed Columns (query line: 185): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `DAY` is indexed, the index won’t be used as it’s wrapped with the function `SUBSTR`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
3. Avoid Calling Functions With Indexed Columns (query line: 199): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `forecast_billing_date` is indexed, the index won’t be used as it’s wrapped with the function `TO_CHAR`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
4. Avoid Calling Functions With Indexed Columns (query line: 199): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `DAY` is indexed, the index won’t be used as it’s wrapped with the function `SUBSTR`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
5. Avoid Calling Functions With Indexed Columns (query line: 224): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `DAY` is indexed, the index won’t be used as it’s wrapped with the function `TO_DATE`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
6. Avoid Correlated Subqueries In Select Clause (modified query below): The aggregation function located in a subquery inside the SELECT clause, is executed once for every matched row. Extracting this subquery to a temporary table will improve performance significantly.
7. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

ALTER TABLE `GCCOM_COMPANY` ADD INDEX `gccom_company_idx_id_company` (`ID_COMPANY`);
ALTER TABLE `GCCOM_CONTRACT` ADD INDEX `gccom_contract_idx_id_contract` (`ID_CONTRACT`);
ALTER TABLE `GCCOM_CONTRACTED_SERVICE` ADD INDEX `gccom_service_idx_drop_da_id_supp_status_from_da` (`DROP_DATE`,`ID_SECTOR_SUPPLY`,`STATUS`,`FROM_DATE`);
ALTER TABLE `GCCOM_CONTSERV_SPECIALTYPES` ADD INDEX `gccom_specialtypes_idx_end_date_id_service_ini_date` (`END_DATE`,`ID_CONTRACTED_SERVICE`,`INI_DATE`);
ALTER TABLE `GCCOM_FARE` ADD INDEX `gccom_fare_idx_id_fare` (`ID_FARE`);
ALTER TABLE `GCCOM_LOST_HISTORY_SS` ADD INDEX `gccom_history_idx_id_supply` (`ID_SECTOR_SUPPLY`);
ALTER TABLE `GCCOM_MASTER_VALUES` ADD INDEX `gccom_values_idx_cod_value` (`COD_VALUE`);
ALTER TABLE `GCCOM_SECTOR_SUPPLY` ADD INDEX `gccom_supply_idx_id_supply` (`ID_SECTOR_SUPPLY`);
ALTER TABLE `GCGT_ELECTRIC_ATTRIBUTES` ADD INDEX `gcgt_attributes_idx_id_supply` (`ID_SECTOR_SUPPLY`);
ALTER TABLE `GCGT_ME_COMBINATION_USAGE_TYPE` ADD INDEX `gcgt_combination_idx_id_type_id_meter` (`ID_COMBINATION_USAGE_TYPE`,`ID_RATE_METER`);
ALTER TABLE `GCGT_ME_MEASURER` ADD INDEX `gcgt_measurer_idx_id_measurer` (`ID_MEASURER`);
ALTER TABLE `GCGT_ME_MEA_CTYPE_HIST` ADD INDEX `gcgt_mea_idx_to_date_id_measure_from_date` (`TO_DATE`,`ID_MEASURER`,`FROM_DATE`);
ALTER TABLE `GCGT_ME_RATE_METER` ADD INDEX `gcgt_rate_idx_id_meter_id_fare` (`ID_RATE_METER`,`ID_FARE`);
ALTER TABLE `GCGT_ME_USAGE_TYPE_METER` ADD INDEX `gcgt_usage_idx_id_measurer_cod_type` (`ID_MEASURER`,`COD_USAGE_TYPE`);
ALTER TABLE `GCGT_RE_CONSUM_H_E` ADD INDEX `gcgt_consum_idx_active_day` (`ACTIVE`,`DAY`);
ALTER TABLE `GCGT_RE_MEASUREMENT_POINT` ADD INDEX `gcgt_measurement_idx_cups` (`CUPS`);
ALTER TABLE `GCGT_RE_MODE_TYPE` ADD INDEX `gcgt_mode_idx_cod_develop` (`COD_DEVELOP`);
ALTER TABLE `GCGT_RE_MP_NATURE` ADD INDEX `gcgt_mp_idx_id_nature` (`ID_MP_NATURE`);
ALTER TABLE `GCGT_RE_ORIGIN` ADD INDEX `gcgt_origin_idx_cod_develop` (`COD_DEVELOP`);
ALTER TABLE `GCGT_RE_READING_E` ADD INDEX `gcgt_reading_idx_id_measurer_day` (`ID_MEASURER`,`DAY`);
ALTER TABLE `gccom_delivery_note` ADD INDEX `gccom_note_idx_ind_sim_id_serv_deliver_rownum` (`ind_simulated`,`id_contracted_service`,`delivery_note_status`,`ROWNUM`);
ALTER TABLE `gccom_delivery_note` ADD INDEX `gccom_note_idx_delive_ind_si_ind_bi_id_ser_rownum` (`delivery_note_status`,`ind_simulated`,`ind_not_billed`,`id_contracted_service`,`ROWNUM`);

The optimized query:

WITH CONSUM AS (SELECT
        C.ID_MEASURER,
        C.ID_MEASURING_POINT,
        C.CONSUM_TYPE 
    FROM
        GCGT_RE_CONSUM_H_E C 
    WHERE
        C.DAY BETWEEN 20130201 AND 20130228 
        AND C.ACTIVE = 1), CORE AS (SELECT
        SS.NISS,
        CO.ID_CONTRACT,
        CO.FROM_DATE AS FECHA_INI_CONTRATO,
        SS.ID_DISTRIBUTER,
        SS.ID_SECTOR_SUPPLY,
        MP.CUPS,
        M.ID_COMBINATION_USAGE_TYPE,
        MP.MP_FUNCTION,
        NVL(MP.FLAG_TELEMETRY,
        0) AS TELEMEDIDO,
        NVL(CTYP.RELEVANCE,
        0) AS RELEVANT_CONSUM_TYPE,
        MP.ID_MEASURER,
        CTYP.COD_USAGE_TYPE AS CONSUM_TYPE,
        0 AS VALUE_1,
        0 AS VALUE_2,
        0 AS VALUE_3,
        0 AS VALUE_4,
        0 AS VALUE_5,
        0 AS VALUE_6,
        0 AS VALUE_7,
        0 AS VALUE_8,
        0 AS VALUE_9,
        0 AS VALUE_10,
        0 AS VALUE_11,
        0 AS VALUE_12,
        0 AS VALUE_13,
        0 AS VALUE_14,
        0 AS VALUE_15,
        0 AS VALUE_16,
        0 AS VALUE_17,
        0 AS VALUE_18,
        0 AS VALUE_19,
        0 AS VALUE_20,
        0 AS VALUE_21,
        0 AS VALUE_22,
        0 AS VALUE_23,
        0 AS VALUE_24,
        0 AS VALUE_25,
        '' AS SOURCE_1,
        '' AS SOURCE_2,
        '' AS SOURCE_3,
        '' AS SOURCE_4,
        '' AS SOURCE_5,
        '' AS SOURCE_6,
        '' AS SOURCE_7,
        '' AS SOURCE_8,
        '' AS SOURCE_9,
        '' AS SOURCE_10,
        '' AS SOURCE_11,
        '' AS SOURCE_12,
        '' AS SOURCE_13,
        '' AS SOURCE_14,
        '' AS SOURCE_15,
        '' AS SOURCE_16,
        '' AS SOURCE_17,
        '' AS SOURCE_18,
        '' AS SOURCE_19,
        '' AS SOURCE_20,
        '' AS SOURCE_21,
        '' AS SOURCE_22,
        '' AS SOURCE_23,
        '' AS SOURCE_24,
        '' AS SOURCE_25,
        0 AS QUALITY_1,
        0 AS QUALITY_2,
        0 AS QUALITY_3,
        0 AS QUALITY_4,
        0 AS QUALITY_5,
        0 AS QUALITY_6,
        0 AS QUALITY_7,
        0 AS QUALITY_8,
        0 AS QUALITY_9,
        0 AS QUALITY_10,
        0 AS QUALITY_11,
        0 AS QUALITY_12,
        0 AS QUALITY_13,
        0 AS QUALITY_14,
        0 AS QUALITY_15,
        0 AS QUALITY_16,
        0 AS QUALITY_17,
        0 AS QUALITY_18,
        0 AS QUALITY_19,
        0 AS QUALITY_20,
        0 AS QUALITY_21,
        0 AS QUALITY_22,
        0 AS QUALITY_23,
        0 AS QUALITY_24,
        0 AS QUALITY_25,
        20130228 AS DAY,
        TRUNC(20130228,
        -2) AS FIRST_DAY,
        TRUNC(20130228,
        -2) + 31 AS LAST_DAY,
        NVL(MTY.COD_USER,
        '') AS ORIGIN,
        SC.ID_CONTRACTED_SERVICE,
        0 AS BAJA,
        NVL(SC.IND_GUARANTOR,
        0) AS GESTION_ATR,
        SC.NISC 
    FROM
        GCGT_RE_MEASUREMENT_POINT MP 
    INNER JOIN
        GCCOM_SECTOR_SUPPLY SS 
            ON MP.ID_SECTOR_SUPPLY = SS.ID_SECTOR_SUPPLY 
    INNER JOIN
        GCGT_ME_MEASURER M 
            ON M.ID_MEASURER = MP.ID_MEASURER 
    INNER JOIN
        GCCOM_CONTRACTED_SERVICE SC 
            ON SC.ID_SECTOR_SUPPLY = SS.ID_SECTOR_SUPPLY 
    INNER JOIN
        GCCOM_CONTRACT CO 
            ON CO.ID_CONTRACT = SC.ID_CONTRACT 
    INNER JOIN
        GCGT_RE_MP_NATURE NAT 
            ON NAT.ID_MP_NATURE = MP.ID_MP_NATURE 
    INNER JOIN
        GCGT_ME_MEA_CTYPE_HIST CTYP 
            ON MP.ID_MEASURER = CTYP.ID_MEASURER 
    INNER JOIN
        GCGT_RE_MODE_TYPE MTY 
            ON MTY.COD_DEVELOP = NAT.COD_MODO 
    WHERE
        1 = 1 
        AND 1 = 1 
        AND CTYP.FROM_DATE <= 20130228 
        AND (CTYP.TO_DATE IS NULL 
        OR CTYP.TO_DATE >= 20130201) 
        AND CTYP.COD_USAGE_TYPE NOT IN ('AEH', 'R1H', 'AEQ', 'R1Q') 
        AND MP.CUPS IS NOT NULL 
        AND SC.STATUS NOT IN ('ESTSC00004', 'ESTSC00005') 
        AND (SC.DROP_DATE IS NULL 
        OR SC.DROP_DATE >= (TO_DATE(20130228, 'YYYYMMDD'))) 
        AND SC.FROM_DATE <= LAST_DAY(TO_DATE(20130201, 'YYYYMMDD')) 
        AND (CO.STATUS NOT IN ('ESTCO00005', 'ESTCO00004') 
        OR (CO.END_DATE >= (TO_DATE(20130201, 'YYYYMMDD')) 
        AND CO.STATUS = 'ESTCO00005') 
        OR (CO.DROP_DATE >= (TO_DATE(20130201, 'YYYYMMDD')) 
        AND CO.STATUS = 'ESTCO00004')) 
        AND EXISTS (SELECT
            UTM.ID_MEASURER 
        FROM
            GCGT_ME_USAGE_TYPE_METER UTM 
        WHERE
            UTM.ID_MEASURER = MP.ID_MEASURER 
            AND UTM.COD_USAGE_TYPE = CTYP.COD_USAGE_TYPE) 
        AND NOT EXISTS (SELECT
            1 
        FROM
            CONSUM C 
        WHERE
            C.ID_MEASURER = MP.ID_MEASURER 
            AND C.ID_MEASURING_POINT = MP.ID_MEASURING_POINT 
            AND C.CONSUM_TYPE = CTYP.COD_USAGE_TYPE)) SELECT
            CORE.NISS,
            CORE.ID_CONTRACT,
            '' AS MOT_BLOQ,
            CORE.FECHA_INI_CONTRATO,
            SUBSTR(CORE.DAY,
            1,
            6) AS PERIODO,
            EATT.FOREIGN_CONTRACT_NUMBER AS ATR_POLICY,
            COM.NAME_TYPE AS COD_DISTRIBUIDORA,
            (SELECT
                COUNT(1) 
            FROM
                gccom_delivery_note dn 
            WHERE
                dn.id_contracted_service = CORE.ID_CONTRACTED_SERVICE 
                AND dn.delivery_note_status IN (
                    'BIDOCST001', 'BIDOCST003'
                ) 
                AND dn.ind_simulated = 0 
                AND TO_CHAR(dn.forecast_billing_date, 'YYYYMM') >= SUBSTR(CORE.DAY, 1, 6) 
                AND dn.ROWNUM < 2) AS IND_CALC_ORDER,
            (SELECT
                COUNT(1) 
            FROM
                gccom_delivery_note dn 
            WHERE
                dn.id_contracted_service = CORE.ID_CONTRACTED_SERVICE 
                AND dn.delivery_note_status = 'BIDOCST001' 
                AND dn.ind_simulated = 0 
                AND (
                    dn.ind_not_billed = 0 
                    OR dn.ind_not_billed IS NULL
                ) 
                AND TO_CHAR(dn.forecast_billing_date, 'YYYYMM') >= SUBSTR(CORE.DAY, 1, 6) 
                AND dn.ROWNUM < 2) AS IND_SENT_FACT,
            es_temp1.IND_SENT_FACT_ANT,
            CORE.CUPS,
            FAR.SHORT_NAME_TYPE AS TARIFA,
            CT.DESCRIPTION AS TIPO_CLIENTE,
            CORE.TELEMEDIDO,
            CORE.RELEVANT_CONSUM_TYPE,
            (SELECT
                COUNT(1) 
            FROM
                GCGT_RE_READING_E RE 
            WHERE
                RE.ID_MEASURER = CORE.ID_MEASURER 
                AND RE.DAY BETWEEN CORE.FIRST_DAY AND CORE.LAST_DAY) AS CLOSED,
            es_temp2.MULTIPLEPM,
            NVL((SELECT
                CGMV.NAME_TYPE 
            FROM
                GCCOM_LOST_HISTORY_SS GLH 
            INNER JOIN
                GCCOM_MASTER_VALUES CGMV 
                    ON GLH.COD_VALUE = CGMV.COD_VALUE 
            WHERE
                GLH.ID_SECTOR_SUPPLY = CORE.ID_SECTOR_SUPPLY 
                AND TO_DATE(CORE.DAY, 'YYYYMMDD') BETWEEN GLH.INIT_DATE AND GLH.END_DATE 
                AND ROWNUM < 2),
            '') AS ITC,
            CORE.CONSUM_TYPE,
            CORE.VALUE_1,
            CORE.VALUE_2,
            CORE.VALUE_3,
            CORE.VALUE_4,
            CORE.VALUE_5,
            CORE.VALUE_6,
            CORE.VALUE_7,
            CORE.VALUE_8,
            CORE.VALUE_9,
            CORE.VALUE_10,
            CORE.VALUE_11,
            CORE.VALUE_12,
            CORE.VALUE_13,
            CORE.VALUE_14,
            CORE.VALUE_15,
            CORE.VALUE_16,
            CORE.VALUE_17,
            CORE.VALUE_18,
            CORE.VALUE_19,
            CORE.VALUE_20,
            CORE.VALUE_21,
            CORE.VALUE_22,
            CORE.VALUE_23,
            CORE.VALUE_24,
            CORE.VALUE_25,
            CORE.SOURCE_1,
            CORE.SOURCE_2,
            CORE.SOURCE_3,
            CORE.SOURCE_4,
            CORE.SOURCE_5,
            CORE.SOURCE_6,
            CORE.SOURCE_7,
            CORE.SOURCE_8,
            CORE.SOURCE_9,
            CORE.SOURCE_10,
            CORE.SOURCE_11,
            CORE.SOURCE_12,
            CORE.SOURCE_13,
            CORE.SOURCE_14,
            CORE.SOURCE_15,
            CORE.SOURCE_16,
            CORE.SOURCE_17,
            CORE.SOURCE_18,
            CORE.SOURCE_19,
            CORE.SOURCE_20,
            CORE.SOURCE_21,
            CORE.SOURCE_22,
            CORE.SOURCE_23,
            CORE.SOURCE_24,
            CORE.SOURCE_25,
            CORE.QUALITY_1,
            CORE.QUALITY_2,
            CORE.QUALITY_3,
            CORE.QUALITY_4,
            CORE.QUALITY_5,
            CORE.QUALITY_6,
            CORE.QUALITY_7,
            CORE.QUALITY_8,
            CORE.QUALITY_9,
            CORE.QUALITY_10,
            CORE.QUALITY_11,
            CORE.QUALITY_12,
            CORE.QUALITY_13,
            CORE.QUALITY_14,
            CORE.QUALITY_15,
            CORE.QUALITY_16,
            CORE.QUALITY_17,
            CORE.QUALITY_18,
            CORE.QUALITY_19,
            CORE.QUALITY_20,
            CORE.QUALITY_21,
            CORE.QUALITY_22,
            CORE.QUALITY_23,
            CORE.QUALITY_24,
            CORE.QUALITY_25,
            CORE.DAY,
            ORI.DESCRIPTION AS ORIGIN,
            CORE.ID_CONTRACTED_SERVICE,
            CORE.BAJA,
            CORE.GESTION_ATR,
            NVL((SELECT
                SP.IND_COGENERATION 
            FROM
                GCCOM_CONTSERV_SPECIALTYPES SP 
            WHERE
                CORE.ID_CONTRACTED_SERVICE = SP.ID_CONTRACTED_SERVICE 
                AND (SP.END_DATE IS NULL 
                OR SP.END_DATE >= TO_DATE(20130228, 'YYYYMMDD')) 
                AND SP.INI_DATE <= TO_DATE(20130228, 'YYYYMMDD')),
            0) AS COGENERADOR,
            CORE.NISC 
        FROM
            CORE,
            GCGT_ELECTRIC_ATTRIBUTES EATT,
            GCCOM_COMPANY COM,
            GCGT_ME_RATE_METER RM,
            GCGT_ME_COMBINATION_USAGE_TYPE CUT,
            GCCOM_FARE FAR,
            GCGT_RE_MP_CUSTOMER_TYPE CT,
            GCGT_RE_ORIGIN ORI 
        LEFT JOIN
            es_temp1 
                ON es_temp1.id_contracted_service = CORE.ID_CONTRACTED_SERVICE 
        LEFT JOIN
            es_temp2 
                ON es_temp2.ID_SECTOR_SUPPLY = CORE.ID_SECTOR_SUPPLY 
        WHERE
            EATT.ID_SECTOR_SUPPLY = CORE.ID_SECTOR_SUPPLY 
            AND COM.ID_COMPANY = CORE.ID_DISTRIBUTER 
            AND CUT.ID_COMBINATION_USAGE_TYPE(+) = CORE.ID_COMBINATION_USAGE_TYPE 
            AND CUT.ID_RATE_METER = RM.ID_RATE_METER(+) 
            AND FAR.ID_FARE(+) = RM.ID_FARE 
            AND (
                CT.COD_DEVELOP = CORE.MP_FUNCTION 
                OR CORE.MP_FUNCTION IS NULL
            ) 
            AND ORI.COD_DEVELOP = CORE.ORIGIN