Optimize Oracle SQL query

Question

Oracle SQL Performance Tuning

Can someone please help me optimize this query? (Please note that I had multiple subqueries in select statement, I am only providing here 2 subqueries for ease).

I have provided explain plan here and will be adding the details of table too.

(This is the case where I converted a SQL server DTS package into Oracle Procedure. So, all the tables are created by me and there is almost no index present.)

SELECT dlr.PK_DealerCode_vc,
       dlr.DealerNameShort_vc,
       CASE WHEN mmacode_vc = ''  THEN Province_vc 
            ELSE '' END,
       dlr.AREAName_vc,
       dlr.MMAName_vc,
       gmma.GMMAName_vc,
       ZoneName_vc,
       to_char(to_date('30-jun-2020'), 'mm') || ' ' || to_char(to_date('30-jun-2020'), 'yy'),
       NVL((SELECT SUM(VehicleCount_in) 
              FROM VehicleCountByDA b 
             INNER JOIN DomesticGeographyData a ON TRIM(a.PK_DA_vc) = TRIM(b.PK_DA_vc) 
             WHERE (TO_NUMBER(TO_CHAR(to_date('30-jun-2020'), 'yyyy')) - ReportYear_in) = 4 
               AND TRIM(a.COCMACode_vc) = TRIM(dlr.PK_DealerCode_vc) 
               AND gmprimarycode_vc = 'R' 
               AND TRIM(gmcustomweightgroupname_vc) = 'NON-TRUCK'), 0) "CarIndY - 4",
       NVL((SELECT SUM(VehicleCount_in) 
              FROM VehicleCountByDA b 
             INNER JOIN DomesticGeographyData a ON TRIM(a.PK_DA_vc) = TRIM(b.PK_DA_vc) 
             WHERE (TO_NUMBER(TO_CHAR(to_date('30-jun-2020'), 'yyyy')) - ReportYear_in) = 3 
               AND TRIM(a.COCMACode_vc) = TRIM(dlr.PK_DealerCode_vc) 
               AND gmprimarycode_vc = 'R' 
               AND TRIM(gmcustomweightgroupname_vc) = 'NON-TRUCK'), 0) "CarIndY - 3" 
FROM DealerFile dlr,
     (SELECT DISTINCT PK_DealerCode_vc,
                      GMMAName_vc 
        FROM DealerGuideFile) gmma 
WHERE ((nvl(dlr.ChevFranchise_bt, 0) = 1 
       AND nvl(dlr.PBGFranchise_bt, 0) = 0))
  AND StatusName_vc = 'Active' 
  AND TRIM(dlr.PK_DealerCode_vc) = TRIM(gmma.PK_DealerCode_vc) 
  AND DealerName_vc NOT LIKE ('%Study Area%') 
  AND DealerName_vc NOT LIKE ('%Open Point%') 
  AND DealerName_vc NOT LIKE ('%Branch%') 
  AND LENGTH(RTRIM(dlr.PK_DealerCode_vc)) <= 5;

Explain Plan statement is attached here:

Plan hash value: 37211997
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                              |    13 |  1781 |   142K  (2)| 00:00:12 |
|   1 |  SORT AGGREGATE      |                              |     1 |    55 |            |          |
|*  2 |   HASH JOIN          |                              |  2833 |   152K| 23748   (2)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL | GMSQLDBDOMESTICGEOGRAPHYDATA |   539 |  8085 |  1020   (1)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL | GM_TEMP_VEHICLECOUNTBYDA     |   525 | 21000 | 22728   (2)| 00:00:02 |
|   5 |  SORT AGGREGATE      |                              |     1 |    55 |            |          |
|*  6 |   HASH JOIN          |                              |  2833 |   152K| 23748   (2)| 00:00:02 |
|*  7 |    TABLE ACCESS FULL | GMSQLDBDOMESTICGEOGRAPHYDATA |   539 |  8085 |  1020   (1)| 00:00:01 |
|*  8 |    TABLE ACCESS FULL | GM_TEMP_VEHICLECOUNTBYDA     |   525 | 21000 | 22728   (2)| 00:00:02 |
|   9 |  VIEW                | VM_NWVW_1                    |    13 |  1781 |    16   (7)| 00:00:01 |
|  10 |   HASH UNIQUE        |                              |    13 |  1898 |    16   (7)| 00:00:01 |
|* 11 |    HASH JOIN         |                              |    13 |  1898 |    15   (0)| 00:00:01 |
|* 12 |     TABLE ACCESS FULL| GMSQLDBGMDEALERFILE          |     3 |   342 |    10   (0)| 00:00:01 |
|  13 |     TABLE ACCESS FULL| MAEDEALERGUIDEFILE           |   511 | 16352 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$16C51A37
   3 - SEL$16C51A37 / A@SEL$1
   4 - SEL$16C51A37 / B@SEL$1
   5 - SEL$20B10B79
   7 - SEL$20B10B79 / A@SEL$2
   8 - SEL$20B10B79 / B@SEL$2
   9 - SEL$3E77572C / VM_NWVW_1@SEL$A5893905
  10 - SEL$3E77572C
  12 - SEL$3E77572C / DLR@SEL$3
  13 - SEL$3E77572C / MAEDEALERGUIDEFILE@SEL$6
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(TRIM("A"."PK_DA_VC")=TRIM("B"."PK_DA_VC"))
   3 - filter(TRIM("A"."COCMACODE_VC")=TRIM(:B1))
   4 - filter(2020-"B"."REPORTYEAR_IN"=4 AND TRIM("B"."GMCUSTOMWEIGHTGROUPNAME_VC")='NON-TRUC
              K' AND "B"."GMPRIMARYCODE_VC"='R')
   6 - access(TRIM("A"."PK_DA_VC")=TRIM("B"."PK_DA_VC"))
   7 - filter(TRIM("A"."COCMACODE_VC")=TRIM(:B1))
   8 - filter(2020-"B"."REPORTYEAR_IN"=3 AND TRIM("B"."GMCUSTOMWEIGHTGROUPNAME_VC")='NON-TRUC
              K' AND "B"."GMPRIMARYCODE_VC"='R')
  11 - access(TRIM("DLR"."PK_DEALERCODE_VC")=TRIM("PK_DEALERCODE_VC"))
  12 - filter(NVL("DLR"."PBGFRANCHISE_BT",0)=0 AND 
              LENGTH(RTRIM("DLR"."PK_DEALERCODE_VC"))<=5 AND "STATUSNAME_VC"='Active' AND "DEALERNAME_VC" 
              NOT LIKE '%Study Area%' AND "DEALERNAME_VC" NOT LIKE '%Open Point%' AND "DEALERNAME_VC" NOT 
              LIKE '%Branch%' AND NVL("DLR"."CHEVFRANCHISE_BT",0)=1 AND "DEALERNAME_VC" IS NOT NULL AND 
              "DEALERNAME_VC" IS NOT NULL AND "DEALERNAME_VC" IS NOT NULL)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) SUM("B"."VEHICLECOUNT_IN")[22]
   2 - (#keys=1; rowset=256) "B"."VEHICLECOUNT_IN"[NUMBER,22]
   3 - (rowset=256) "A"."PK_DA_VC"[VARCHAR2,8]
   4 - (rowset=256) "B"."PK_DA_VC"[VARCHAR2,10], "B"."VEHICLECOUNT_IN"[NUMBER,22]
   5 - (#keys=0) SUM("B"."VEHICLECOUNT_IN")[22]
   6 - (#keys=1; rowset=256) "B"."VEHICLECOUNT_IN"[NUMBER,22]
   7 - (rowset=256) "A"."PK_DA_VC"[VARCHAR2,8]
   8 - (rowset=256) "B"."PK_DA_VC"[VARCHAR2,10], "B"."VEHICLECOUNT_IN"[NUMBER,22]
   9 - "$vm_col_3"[VARCHAR2,100], "$vm_col_4"[VARCHAR2,2], "$vm_col_5"[VARCHAR2,4], 
       "$vm_col_6"[VARCHAR2,50], "$vm_col_7"[VARCHAR2,25], "$vm_col_8"[VARCHAR2,30], 
       "$vm_col_9"[VARCHAR2,25], "$vm_col_10"[VARCHAR2,8]
  10 - (#keys=11) ROWID[ROWID,10], "GMMANAME_VC"[VARCHAR2,30], 
       "PK_DEALERCODE_VC"[VARCHAR2,8], "DLR"."DEALERNAMESHORT_VC"[VARCHAR2,100], 
       "PROVINCE_VC"[VARCHAR2,2], "MMACODE_VC"[VARCHAR2,4], "DLR"."AREANAME_VC"[VARCHAR2,50], 
       "DLR"."MMANAME_VC"[VARCHAR2,25], "GMMANAME_VC"[VARCHAR2,30], "ZONENAME_VC"[VARCHAR2,25], 
       "DLR"."PK_DEALERCODE_VC"[VARCHAR2,8]
  11 - (#keys=1) ROWID[ROWID,10], "DLR"."PK_DEALERCODE_VC"[VARCHAR2,8], 
       "DLR"."DEALERNAMESHORT_VC"[VARCHAR2,100], "PROVINCE_VC"[VARCHAR2,2], 
       "ZONENAME_VC"[VARCHAR2,25], "MMACODE_VC"[VARCHAR2,4], "DLR"."MMANAME_VC"[VARCHAR2,25], 
       "DLR"."AREANAME_VC"[VARCHAR2,50], "PK_DEALERCODE_VC"[VARCHAR2,8], "GMMANAME_VC"[VARCHAR2,30]
  12 - (rowset=256) ROWID[ROWID,10], "DLR"."PK_DEALERCODE_VC"[VARCHAR2,8], 
       "DLR"."DEALERNAMESHORT_VC"[VARCHAR2,100], "PROVINCE_VC"[VARCHAR2,2], 
       "ZONENAME_VC"[VARCHAR2,25], "MMACODE_VC"[VARCHAR2,4], "DLR"."MMANAME_VC"[VARCHAR2,25], 
       "DLR"."AREANAME_VC"[VARCHAR2,50]
  13 - (rowset=256) "PK_DEALERCODE_VC"[VARCHAR2,8], "GMMANAME_VC"[VARCHAR2,30]


CREATE TABLE GMSQLDBDomesticGeographyData (
    PK_DA_vc Varchar2(8) NOT NULL ,
    FSA_vc Varchar2(3) NULL ,
    FSAName_vc Varchar2(100) NULL ,
    COCMACode_vc Varchar2(5) NULL ,
    COCMAName_vc Varchar2(50) NULL ,
    PBGMACode_vc Varchar2(5) NULL ,
    PBGMAName_vc Varchar2(50) NULL ,
    CADMACode_vc Varchar2(5) NULL ,
    CADMAName_vc Varchar2(50) NULL ,
    HUMMACode_vc Varchar2(5) NULL ,
    HUMMAName_vc Varchar2(50) NULL ,
    MDMACode_vc Varchar2(5) NULL ,
    MDMAName_vc Varchar2(50) NULL ,
    DomesticMMACode_vc Varchar2(4) NULL ,
    DomesticMMAName_vc Varchar2(25) NULL ,
    ProvinceCode_vc Varchar2(2) NULL ,
    ProvinceName_vc Varchar2(50) NULL ,
    ReportTerritoryCode_vc Varchar2(2) NULL ,
    ReportTerritoryName_vc Varchar2(50) NULL ,
    ZoneCode_vc Varchar2(2) NULL ,
    ZoneName_vc Varchar2(25) NULL ,
    GMMACode_vc Varchar2(2) NULL ,
    GMMAName_vc Varchar2(30) NULL ,
    AREACode_vc Varchar2(5) NULL ,
    AREAName_vc Varchar2(50) NULL ,
    OntarioSpecialMarket_vc Varchar2(50) NULL ,
    CONSTRAINT PK_GMSQLDBDomesticGeographyData PRIMARY KEY  
    (
        PK_DA_vc
    )  
);


CREATE TABLE GM_TEMP_VehicleCountByDA (
    PK_DA_vc Varchar2(10) NULL ,
    VehicleCount_in Number(10) NULL ,
    ReportYear_in Number(10) NULL ,
    ReportMonth_in Number(10) NULL ,
    gmcustomtypecode_vc Varchar2(100) NULL ,
    GMCustomDetailedSegmentCode_vc Varchar2(100) NULL ,
    gmcustomweightgroupname_vc Varchar2(100) NULL ,
    gmcustomeManufacturername_vc Varchar2(50) NULL ,
    genericmanufacturername_vc Varchar2(50) NULL ,
    GMPrimaryCode_vc Varchar2(1) NULL ,
    makedescription_vc Varchar2(50) NULL ,
    GMCustomImportDomesticName_vc Varchar2(30) NULL 
);


CREATE TABLE MAEDealerGuideFile (
    PK_DealerCode_vc Varchar2(8) NOT NULL ,
    StatusCode_vc Varchar2(1) NOT NULL ,
    OPFlag_vc Varchar2(1) NULL ,
    StatusName_vc Varchar2(15) NULL ,
    DealerName_vc Varchar2(100) NULL ,
    DealerNameShort_vc Varchar2(100) NULL ,
    ZoneCode_vc Varchar2(2) NULL ,
    ZoneName_vc Varchar2(25) NULL ,
    SalesTerritoryCode_vc Varchar2(2) NULL ,
    GMMACode_vc Varchar2(2) NULL ,
    GMMAName_vc Varchar2(30) NULL ,
    GMMALevelCode_vc Varchar2(1) NULL ,
    GMMAGroupKey Number(10) DEFAULT (0) NULL ,
    OutOfVolume Number(10) DEFAULT (0) NULL ,
    Rank Number(10) DEFAULT (0) NULL ,
    MMACode_vc Varchar2(4) NULL ,
    MMAName_vc Varchar2(25) NULL ,
    NationalCode_vc Varchar2(3) DEFAULT ('NAT') NULL ,
    NationalName_vc Varchar2(8) DEFAULT ('NATIONAL') NULL ,
    NationalAssignedCode_vc Varchar2(1) DEFAULT ('A') NULL ,
    NationalAssignedName_vc Varchar2(10) DEFAULT ('ASSIGNED') NULL ,
    ReplacedByDealerCode_vc Varchar2(20) NULL ,
    ReplacingDealerCode_vc Varchar2(20) NULL ,
    FranchiseCodes_vc Varchar2(15) NULL ,
    FranchisesCarried Varchar2(30) DEFAULT (' ') NULL ,
    FranchisesCarried4Print Varchar2(30) DEFAULT ('  ') NULL ,
    Chevrolet Varchar2(1) NOT NULL ,
    PBG Varchar2(1) NOT NULL ,
    Cadillac Varchar2(1) NOT NULL ,
    Hummer Varchar2(1) NOT NULL ,
    Saturn Varchar2(1) NOT NULL ,
    Saab Varchar2(1) NOT NULL ,
    Isuzu Varchar2(1) NOT NULL ,
    AreaName_vc Varchar2(50) NULL ,
    CityCode_vc Varchar2(2) NULL ,
    MACode_vc Varchar2(5) NULL ,
    MAName_vc Varchar2(20) NULL ,
    DealerBACCode_vc Varchar2(11) NULL 
);



CREATE TABLE GMSQLDBGMDealerFile (
    PK_DealerCode_vc Varchar2(8) NOT NULL ,
    StatusCode_vc Varchar2(1) NULL ,
    StatusName_vc Varchar2(15) NULL ,
    OPFlag_vc Varchar2(1) NULL ,
    BusFranchiseFlag_vc Varchar2(1) NULL ,
    Language_vc Varchar2(1) NULL ,
    FrenchFlag_vc Varchar2(1) NULL ,
    DealerName_vc Varchar2(100) NULL ,
    DealerNameShort_vc Varchar2(100) NULL ,
    Address_vc Varchar2(50) NULL ,
    POBox_vc Varchar2(50) NULL ,
    City_vc Varchar2(30) NULL ,
    Province_vc Varchar2(2) NULL ,
    FSALDU_vc Varchar2(7) NULL ,
    ZoneCode_vc Varchar2(2) NULL ,
    ZoneName_vc Varchar2(25) NULL ,
    EWCode_vc Varchar2(1) NULL ,
    EWName_vc Varchar2(4) NOT NULL ,
    FranchiseCodes_vc Varchar2(15) NULL ,
    ChevFranchise_bt Number(1) NULL ,
    PBGFranchise_bt Number(1) NULL ,
    CadFranchise_bt Number(1) NULL ,
    HumFranchise_bt Number(1) NULL ,
    SatFranchise_bt Number(1) NULL ,
    SaabFranchise_bt Number(1) NULL ,
    IsuzuFranchise_bt Number(1) NULL ,
    SalesTerritoryCode_vc Varchar2(2) NULL ,
    GMMACode_vc Varchar2(2) NULL ,
    GMMALevelCode_vc Varchar2(1) NULL ,
    MMACode_vc Varchar2(4) NULL ,
    MMAName_vc Varchar2(25) NULL ,
    TerminationDate_d Varchar2(11) NULL ,
    ReplacingDealercode_vc Varchar2(20) NULL ,
    ReplacedByDealerCode_vc Varchar2(20) NULL ,
    ServiceTerritoryCode_vc Varchar2(2) NULL ,
    AreaName_vc Varchar2(50) NULL ,
    CityCode_vc Varchar2(2) NULL ,
    MACode_vc Varchar2(5) NULL ,
    MAName_vc Varchar2(20) NULL ,
    DealerBACCode_vc Varchar2(11) NULL 
);

Cardinality of columns:

TABLE_NAME  COLUMN_NAME NUM_DISTINCT
GM_TEMP_VEHICLECOUNTBYDA    PK_DA_VC    54936
GM_TEMP_VEHICLECOUNTBYDA    VEHICLECOUNT_IN 660
GM_TEMP_VEHICLECOUNTBYDA    REPORTYEAR_IN   6
GM_TEMP_VEHICLECOUNTBYDA    REPORTMONTH_IN  2
GM_TEMP_VEHICLECOUNTBYDA    GMCUSTOMTYPECODE_VC 2
GM_TEMP_VEHICLECOUNTBYDA    GMCUSTOMDETAILEDSEGMENTCODE_VC  72
GM_TEMP_VEHICLECOUNTBYDA    GMCUSTOMWEIGHTGROUPNAME_VC  4
GM_TEMP_VEHICLECOUNTBYDA    GMCUSTOMEMANUFACTURERNAME_VC    47
GM_TEMP_VEHICLECOUNTBYDA    GENERICMANUFACTURERNAME_VC  48
GM_TEMP_VEHICLECOUNTBYDA    GMPRIMARYCODE_VC    3
GM_TEMP_VEHICLECOUNTBYDA    MAKEDESCRIPTION_VC  71
GM_TEMP_VEHICLECOUNTBYDA    GMCUSTOMIMPORTDOMESTICNAME_VC   2
MAEDEALERGUIDEFILE  PK_DEALERCODE_VC    511
MAEDEALERGUIDEFILE  STATUSCODE_VC   4
MAEDEALERGUIDEFILE  OPFLAG_VC   2
MAEDEALERGUIDEFILE  STATUSNAME_VC   2
MAEDEALERGUIDEFILE  DEALERNAME_VC   502
MAEDEALERGUIDEFILE  DEALERNAMESHORT_VC  509
MAEDEALERGUIDEFILE  ZONECODE_VC 3
MAEDEALERGUIDEFILE  ZONENAME_VC 3
MAEDEALERGUIDEFILE  SALESTERRITORYCODE_VC   14
MAEDEALERGUIDEFILE  GMMACODE_VC 7
MAEDEALERGUIDEFILE  GMMANAME_VC 7
MAEDEALERGUIDEFILE  GMMALEVELCODE_VC    9
MAEDEALERGUIDEFILE  GMMAGROUPKEY    39
MAEDEALERGUIDEFILE  OUTOFVOLUME 24
MAEDEALERGUIDEFILE  RANK    1
MAEDEALERGUIDEFILE  MMACODE_VC  20
MAEDEALERGUIDEFILE  MMANAME_VC  20
MAEDEALERGUIDEFILE  NATIONALCODE_VC 1
MAEDEALERGUIDEFILE  NATIONALNAME_VC 1
MAEDEALERGUIDEFILE  NATIONALASSIGNEDCODE_VC 1
MAEDEALERGUIDEFILE  NATIONALASSIGNEDNAME_VC 1
MAEDEALERGUIDEFILE  REPLACEDBYDEALERCODE_VC 0
MAEDEALERGUIDEFILE  REPLACINGDEALERCODE_VC  279
MAEDEALERGUIDEFILE  FRANCHISECODES_VC   88
MAEDEALERGUIDEFILE  FRANCHISESCARRIED   7
MAEDEALERGUIDEFILE  FRANCHISESCARRIED4PRINT 7
MAEDEALERGUIDEFILE  CHEVROLET   2
MAEDEALERGUIDEFILE  PBG 2
MAEDEALERGUIDEFILE  CADILLAC    2
MAEDEALERGUIDEFILE  HUMMER  1
MAEDEALERGUIDEFILE  SATURN  1
MAEDEALERGUIDEFILE  SAAB    1
MAEDEALERGUIDEFILE  ISUZU   1
MAEDEALERGUIDEFILE  AREANAME_VC 464
MAEDEALERGUIDEFILE  CITYCODE_VC 3
MAEDEALERGUIDEFILE  MACODE_VC   3
MAEDEALERGUIDEFILE  MANAME_VC   2
MAEDEALERGUIDEFILE  DEALERBACCODE_VC    448
GMSQLDBGMDEALERFILE PK_DEALERCODE_VC    556
GMSQLDBGMDEALERFILE STATUSCODE_VC   3
GMSQLDBGMDEALERFILE STATUSNAME_VC   3
GMSQLDBGMDEALERFILE OPFLAG_VC   2
GMSQLDBGMDEALERFILE BUSFRANCHISEFLAG_VC 0
GMSQLDBGMDEALERFILE LANGUAGE_VC 2
GMSQLDBGMDEALERFILE FRENCHFLAG_VC   0
GMSQLDBGMDEALERFILE DEALERNAME_VC   546
GMSQLDBGMDEALERFILE DEALERNAMESHORT_VC  550
GMSQLDBGMDEALERFILE ADDRESS_VC  469
GMSQLDBGMDEALERFILE POBOX_VC    130
GMSQLDBGMDEALERFILE CITY_VC 426
GMSQLDBGMDEALERFILE PROVINCE_VC 13
GMSQLDBGMDEALERFILE FSALDU_VC   468
GMSQLDBGMDEALERFILE ZONECODE_VC 3
GMSQLDBGMDEALERFILE     ZONENAME_VC 3
GMSQLDBGMDEALERFILE     EWCODE_VC   0
GMSQLDBGMDEALERFILE     EWNAME_VC   0
GMSQLDBGMDEALERFILE     FRANCHISECODES_VC   100
GMSQLDBGMDEALERFILE     CHEVFRANCHISE_BT    2
GMSQLDBGMDEALERFILE     PBGFRANCHISE_BT 2
GMSQLDBGMDEALERFILE     CADFRANCHISE_BT 2
GMSQLDBGMDEALERFILE     HUMFRANCHISE_BT 1
GMSQLDBGMDEALERFILE     SATFRANCHISE_BT 1
GMSQLDBGMDEALERFILE     SAABFRANCHISE_BT    1
GMSQLDBGMDEALERFILE     ISUZUFRANCHISE_BT   1
GMSQLDBGMDEALERFILE     SALESTERRITORYCODE_VC   14
GMSQLDBGMDEALERFILE     GMMACODE_VC 7
GMSQLDBGMDEALERFILE     GMMALEVELCODE_VC    9
GMSQLDBGMDEALERFILE     MMACODE_VC  20
GMSQLDBGMDEALERFILE     MMANAME_VC  20
GMSQLDBGMDEALERFILE     TERMINATIONDATE_D   1
GMSQLDBGMDEALERFILE     REPLACINGDEALERCODE_VC  304
GMSQLDBGMDEALERFILE     REPLACEDBYDEALERCODE_VC 23
GMSQLDBGMDEALERFILE     SERVICETERRITORYCODE_VC 13
GMSQLDBGMDEALERFILE     AREANAME_VC 468
GMSQLDBGMDEALERFILE     CITYCODE_VC 3
GMSQLDBGMDEALERFILE     MACODE_VC   3
GMSQLDBGMDEALERFILE     MANAME_VC   2
GMSQLDBGMDEALERFILE     DEALERBACCODE_VC    486
GMSQLDBDOMESTICGEOGRAPHYDATA    PK_DA_VC    53941
GMSQLDBDOMESTICGEOGRAPHYDATA    FSA_VC  1576
GMSQLDBDOMESTICGEOGRAPHYDATA    FSANAME_VC  588
GMSQLDBDOMESTICGEOGRAPHYDATA    COCMACODE_VC    438
GMSQLDBDOMESTICGEOGRAPHYDATA    COCMANAME_VC    436
GMSQLDBDOMESTICGEOGRAPHYDATA    PBGMACODE_VC    426
GMSQLDBDOMESTICGEOGRAPHYDATA    PBGMANAME_VC    425
GMSQLDBDOMESTICGEOGRAPHYDATA    CADMACODE_VC    126
GMSQLDBDOMESTICGEOGRAPHYDATA    CADMANAME_VC    125
GMSQLDBDOMESTICGEOGRAPHYDATA    HUMMACODE_VC    1
GMSQLDBDOMESTICGEOGRAPHYDATA    HUMMANAME_VC    1
GMSQLDBDOMESTICGEOGRAPHYDATA    MDMACODE_VC 1
GMSQLDBDOMESTICGEOGRAPHYDATA    MDMANAME_VC 1
GMSQLDBDOMESTICGEOGRAPHYDATA    DOMESTICMMACODE_VC  20
GMSQLDBDOMESTICGEOGRAPHYDATA    DOMESTICMMANAME_VC  20
GMSQLDBDOMESTICGEOGRAPHYDATA    PROVINCECODE_VC 12
GMSQLDBDOMESTICGEOGRAPHYDATA    PROVINCENAME_VC 12
GMSQLDBDOMESTICGEOGRAPHYDATA    REPORTTERRITORYCODE_VC  14
GMSQLDBDOMESTICGEOGRAPHYDATA    REPORTTERRITORYNAME_VC  14
GMSQLDBDOMESTICGEOGRAPHYDATA    ZONECODE_VC 3
GMSQLDBDOMESTICGEOGRAPHYDATA    ZONENAME_VC 3
GMSQLDBDOMESTICGEOGRAPHYDATA    GMMACODE_VC 7
GMSQLDBDOMESTICGEOGRAPHYDATA    GMMANAME_VC 7
GMSQLDBDOMESTICGEOGRAPHYDATA    AREACODE_VC 438
GMSQLDBDOMESTICGEOGRAPHYDATA    AREANAME_VC 437
GMSQLDBDOMESTICGEOGRAPHYDATA    ONTARIOSPECIALMARKET_VC 3

Please share the execution plan always for performance tuning questions. — Ankit Bajpai, Sep 12 '20 at 09:49
https://drive.google.com/file/d/1YRj1z-ytj60YIruuRbeaQi1rOxCYRl31/view?usp=sharing — Swechchha, Sep 12 '20 at 10:44
Please find complete exexution plan on this location. (There are no indexes) — Swechchha, Sep 12 '20 at 10:44
Please try to post the execution plan in the question itself. Everyone (including me) doesn't have the access to google drive. — Ankit Bajpai, Sep 12 '20 at 11:50
Many sites block links like that. And many forum participants refuse to open them for the same secuirity reasons others block them. Also, it is impossible to analyze a query without knowing the structure and data types of the tables involved, You need to update your question and post the ddl of the tables. — EdStevens, Sep 12 '20 at 12:38
Please read [this fine post on asking Oracle query tuning questions](https://stackoverflow.com/a/34975420/146325). It will help you understand why we cannot answer this question until you provide a lot more details, especially data volumes. — APC, Sep 12 '20 at 18:03

Somy · Accepted Answer · 2020-09-12T15:38:13.933

Few quick suggestions -

I see that you are using trim on every join. Would recommend not to do that wherever possible, at least for the joins based on primary keys for e.g - TRIM(a.PK_DA_vc) = TRIM(b.PK_DA_vc) if not absolutely necessary. Since, Primary keys have by default index built on them in oracle. If you wrap them with function while joining, the query optimizer won’t be able to use index based on pk while joining which will negatively impact your query performance.
On the query to get “carIndy -4 & ..-3 etc.. you don’t need to repeat the query every time. In select, Just use Sum(case when (TO_NUMBER(TO_CHAR(to_date('30-jun-2020'), 'yyyy')) - ReportYear_in) = 3 then VehicleCount_in else 0 end) as "CarIndY - 3", .... "CarIndY - 4" In the same query and use this query in join rather than select. Since these are essentially counts, this subset will only have one row with all the colums you need so you should be able to cross join it and get all the carIndY - n colums in select of the main query
Also , put the main join conditions first before the filter conditions such as in your query you have -

WHERE ((nvl(dlr.ChevFranchise_bt, 0) = 1 AND nvl(dlr.PBGFranchise_bt, 0) = 0)) AND StatusName_vc = 'Active' AND TRIM(dlr.PK_DealerCode_vc) = TRIM(gmma.PK_DealerCode_vc)

Here, the 4th and condition is the main join criteria and it should be mentioned first before other criteria in where

thanks a lot . all that was needed was provided in your 2nd point. cost reduced from 142k to 24k. — Swechchha, Sep 14 '20 at 07:35

Ankit Bajpai · Answer 2 · 2020-09-12T12:37:48.563

Since there are no indices, You may try below query with below indexes -

SELECT dlr.PK_DealerCode_vc,
       dlr.DealerNameShort_vc,
       CASE WHEN mmacode_vc = ''  THEN Province_vc 
            ELSE '' END,
       dlr.AREAName_vc,
       dlr.MMAName_vc,
       gmma.GMMAName_vc,
       ZoneName_vc,
       to_char(to_date('30-jun-2020'), 'mm') || ' ' || to_char(to_date('30-jun-2020'), 'yy'),
       NVL(SUM(CASE WHEN (TO_NUMBER(TO_CHAR(to_date('30-jun-2020'), 'yyyy')) - ReportYear_in) = 4 THEN VehicleCount_in END), 0) "CarIndY - 4",
       NVL(SUM(CASE WHEN (TO_NUMBER(TO_CHAR(to_date('30-jun-2020'), 'yyyy')) - ReportYear_in) = 3 THEN VehicleCount_in END), 0) "CarIndY - 3"
 FROM DealerFile dlr,
 JOIN (SELECT DISTINCT PK_DealerCode_vc,
                       GMMAName_vc 
         FROM DealerGuideFile) gmma ON TRIM(dlr.PK_DealerCode_vc) = 
 TRIM(gmma.PK_DealerCode_vc)
 JOIN (SELECT COCMACode_vc, VehicleCount_in, ReportYear_in
         FROM VehicleCountByDA b 
         JOIN DomesticGeographyData a ON TRIM(a.PK_DA_vc) = TRIM(b.PK_DA_vc) 
        WHERE gmprimarycode_vc = 'R' 
          AND TRIM(gmcustomweightgroupname_vc) = 'NON-TRUCK') CarIndY ON TRIM(CARINDY.COCMACode_vc) = TRIM(dlr.PK_DealerCode_vc) 
WHERE nvl(dlr.ChevFranchise_bt, 0) = 1 
  AND nvl(dlr.PBGFranchise_bt, 0) = 0
  AND StatusName_vc = 'Active'
  AND DealerName_vc NOT LIKE '%Study Area%'
  AND DealerName_vc NOT LIKE '%Open Point%'
  AND DealerName_vc NOT LIKE '%Branch%'
  AND LENGTH(RTRIM(dlr.PK_DealerCode_vc)) <= 5;

You may have below indexes on each table -

DealerFile - TRIM(PK_DealerCode_vc), TRIM(PK_DealerCode_vc), nvl(ChevFranchise_bt, 0), nvl(PBGFranchise_bt, 0), LENGTH(RTRIM(PK_DealerCode_vc));
VehicleCountByDA - TRIM(PK_DA_vc);
DomesticGeographyData - TRIM(PK_DA_vc), TRIM(COCMACode_vc);

score 1 · Answer 3 · answered Sep 12 '20 at 12:35

Are your 'dates' really hard-coded strings, or did you do that just for the posting here? Hard-coding it seems very impractical, so I'm assuming you did that for demonstration ..

This construct in your SELECT

to_char(to_date('30-jun-2020'), 'mm') || ' ' || to_char(to_date('30-jun-2020'), 'yy'),

can be simplified to

to_char(to_date('30-jun-2020','dd-mon-yyyy'),'mmyy')

And

WHERE (TO_NUMBER(TO_CHAR(to_date('30-jun-2020'), 'yyyy')) - ReportYear_in) = 4

And if that date is not hard coded, there is almost certainly other opportunities to improve your handling of dates, but we'd need to know what you are really using there.

https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

score 0 · Answer 4 · answered Sep 14 '20 at 07:38

as suggested by @Somy a simple edit reduced the cost by 4 times:

SELECT 
--  DealerCode_vc varchar(6), DealerName_vc varchar(100), province_vc, MAName_vc varchar(25), MMAName_vc varchar(25), GMMAName_vc varchar(25), Zone_vc varchar(25), ReportPeriod_vc varchar(50), 
    dlr.PK_DealerCode_vc, dlr.DealerNameShort_vc, 
    case when mmacode_vc = '' then Province_vc else '' end province_vc, 
    dlr.AREAName_vc, dlr.MMAName_vc, gmma.GMMAName_vc, dlr.ZoneName_vc, 
    to_char(to_date('30-jun-2020'), 'mm') || ' ' || to_char(to_date('30-jun-2020'), 'yy') reportperiod_vc
--  CarIndY-4 int, 
    ,NVL(sum(case when  (TO_NUMBER(TO_CHAR(to_date('30-jun-2020'), 'yyyy')) - ReportYear_in) = 4  then VehicleCount_in else 0 end ),0) "CarIndY-4" 
--  CarIndY-3 int, 
    ,NVL(sum(case when  (TO_NUMBER(TO_CHAR(to_date('30-jun-2020'), 'yyyy')) - ReportYear_in) = 3  then VehicleCount_in else 0 end),0) "CarIndY-3"
FROM GMSQLDBGMDealerFile dlr,
(Select distinct PK_DealerCode_vc, GMMAName_vc from MAEDealerGuideFile) gmma,
GM_TEMP_VehicleCountByDA b,GMSQLDBDomesticGeographyData a
WHERE trim(dlr.PK_DealerCode_vc) = trim(gmma.PK_DealerCode_vc)
AND trim(a.COCMACode_vc) = trim(dlr.PK_DealerCode_vc)
and trim(a.PK_DA_vc) = trim(b.PK_DA_vc) 
and ((nvl(dlr.ChevFranchise_bt,0) = 1 AND nvl(dlr.PBGFranchise_bt,0)=0)) 
AND StatusName_vc = 'Active'
AND DealerName_vc not like ('%Study Area%')
AND DealerName_vc not like ('%Open Point%')
AND DealerName_vc not like ('%Branch%')
AND LENGTH(RTRIM(dlr.PK_DealerCode_vc))<=5
AND gmprimarycode_vc='R' and trim(gmcustomweightgroupname_vc) = 'NON-TRUCK'
group by    dlr.PK_DealerCode_vc, dlr.DealerNameShort_vc, 
    case when mmacode_vc = '' then Province_vc else '' end , 
    dlr.AREAName_vc, dlr.MMAName_vc, gmma.GMMAName_vc, dlr.ZoneName_vc, 
    to_char(to_date('30-jun-2020'), 'mm') || ' ' || to_char(to_date('30-jun-2020'), 'yy') ;

Optimize Oracle SQL query

Oracle SQL Performance Tuning

4 Answers4