performance of query

Hi,

This is the query which is running slow:it used to run quickly till yesterday.Yesterday overnight there was an entire statistics collection of the database..did that cause the problem..how can i fix

If i remove the subselect (higlighted part) and then run the remaining quer it runs fast,even if run the subquery alone it also runs quickly

select project2_.PG_PG_ID as col_0_0_,
       project2_.TC_CODE as col_1_0_,
       projectgro7_.GROUP_NAME as col_2_0_,
       count(distinct relationsh0_.CO_CO_ID) as col_3_0_
 from RELATIONSHIPS relationsh0_
    , PROJECT_COST_CENTRES projectcos1_
    , PROJECTS project2_
    , PROJECT_GROUPS projectgro7_
where nvl(relationsh0_.deleted, 'N') = 'N'
 AND ( SELECT vra.readonly
       FROM v_relationship_access vra
       WHERE vra.email_address = lower ('mark.conboy@db.com')
       AND vra.rel_rel_id = relationsh0_.rel_id
     ) in ('Y', 'N')
 and project2_.PG_PG_ID=projectgro7_.PG_ID
 and projectcos1_.PRO_PRO_ID=project2_.PRO_ID
 and relationsh0_.PCC_PCC_ID=projectcos1_.PCC_ID
group by project2_.PG_PG_ID
       , project2_.TC_CODE
       , projectgro7_.GROUP_NAME
having count(relationsh0_.CO_CO_ID) >0
order by project2_.TC_CODE desc
       , projectgro7_.GROUP_NAME


The script for view v_relationship_access is :

CREATE OR REPLACE VIEW VDB_OWNER.V_RELATIONSHIP_ACCESS
(EMAIL_ADDRESS, CO_ID, REL_REL_ID, READONLY)
AS
SELECT email_address
     , co_id
     , rel_rel_id
     , readonly
FROM  RELATIONSHIP_ACCESS;

and i have indexes on RELATIONSHIP_ACCESS TABLE for email_address and rel_rel_id


CREATE UNIQUE INDEX RELA_PK ON RELATIONSHIP_ACCESS
(EMAIL_ADDRESS, CO_ID, REL_REL_ID)
LOGGING
TABLESPACE VDB_INDX01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX RELA_REL_FK ON RELATIONSHIP_ACCESS
(REL_REL_ID)
LOGGING
TABLESPACE VDB_INDX01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
Start Free Trial