SQL & PL/SQL

Requirement comes for tunne the query in prod

query consuming high CPU utilization

sql_id : fp79wb32h7a6t

Module : AHC, P_PatientDQ()

Cost: 7

Elapsed Time : hung

Status: Hung , 35,253,460 excutions in 30 minutes interval

-- Create table

create table ahc.PATIENTVISITS

(

  ahcid               NUMBER,

  visitid             NUMBER,

  visitstatus         NVARCHAR2(100),

  visitdate           DATE,

  visitintime         DATE,

  visitouttime        DATE

)

-- Create/Recreate indexes

create index ahc.F1_PATIENTVISIT_AHCID on ahc.PATIENTVISITS (AHCID, VISITID);

create index ahc.VISITDATE_IDX on ahc.PATIENTVISITS (TRUNC(VISITDATE));

  First table total count

select count(1)  from

AHC.patientvisits pvs

--record count 1387485

create table ahc.PATIENTDEPENDENTVISITS

(

  ahcid            NUMBER(19) not null,

  packageid        NUMBER(10) not null,

  visitid          NUMBER(19) not null,

  dependentvisitid NUMBER(19) not null,

  isdiabetic       NUMBER(2)

)

-- Create/Recreate indexes

create index ahc.K1_PATIENTDEPENDENT_AHCID on ahc.PATIENTDEPENDENTVISITS (AHCID);

Second table total count

select count(1) from

AHC.patientdependentvisits pdv

--record count 774599

Original Query:

SELECT count(*)

  FROM AHC.PATIENTVISITS PVS

WHERE PVS.VISITID IN (SELECT PDV.DEPENDENTVISITID

                       FROM AHC.PATIENTDEPENDENTVISITS PDV

                       WHERE PDV.AHCID = 37

                       AND PDV.VISITID = 218

                       AND PDV.ISDIABETIC = 0)

   AND UPPER(PVS.VISITSTATUS) = 'PENDING'

   AND PVS.AHCID = 37

count(1)

-----------

1

PL/sql Developer Explain plan For original query

Description                                                    owner Objects_name                        cost cardin bytes cpucost  Io cost

After changing the query

SELECT /*+ FIRST_ROWS*/ COUNT(1)

  FROM AHC.PATIENTVISITS PVS

  JOIN AHC.PATIENTDEPENDENTVISITS PDV  ON  PVS.VISITID=PDV.DEPENDENTVISITID

  WHERE PVS.AHCID = PDV.AHCID

  AND PDV.AHCID = 37

  AND PDV.VISITID = 218

  AND PDV.ISDIABETIC = 0

  AND UPPER(PVS.VISITSTATUS) = 'PENDING'

count(1)

-----------

1

PL/sql Developer Explain plan For Changed  query

Description                                                    owner Objects_name                                         cost cardin bytes cpucost  Io cost

here after i have changed the query no improvement displaying in cost=7 io wait also=7  can any one please help for these

how to tune or rewrite non key table join bunch of multiple records on table

Thanks

vsm