Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    13

    Unanswered: query taking lot of time

    Hi pls,, tell me why below query is taking lot of time. pls optimise this query .


    NHV_ELEMENT_FOR_USER B is the master table which have each and every record (element Id present in other tables)





    ################################################## ###########################
    SELECT distinct B.ELEMENT_ID,B.SYS_NAME,B.ALIAS,B.IF_TYPE,
    (SUM(A.MEMORYUTILIZATION)) as MemoryUTILISATION,
    SUM(A.PHYSICALMEMORYUSED) as PHYSICALMEMORYUSED,E.CALENDAR_DATE,
    to_date(A.SAMPLE_TIMESTAMP) as SAMPLE_TIMESTAMP1,
    SUM(A.CPUUTILIZATION) as CPUUTILISATION,
    SUM(C.BITSIN) as BITSIN,SUM(C.BITSOUT) as BITSOUT,
    SUM(C.BYTESIN) as BYTESIN,SUM(C.BYTESOUT) as BYTESOUT,
    SUM(C.BITPERSECIN) as BITSPERSECIN,
    SUM(C.BitPerSecOut) as BITPERSECOUT,
    to_Date(C.SAMPLE_TIMESTAMP) AS SAMPLE_TIMESTAMP2
    ,to_DATE(D.SAMPLE_TIMESTAMP) as SAMPLE_TIMESTAMP3,
    SUM(D.CPUSYSTEMUTILIZATION) as CPUSYSTEMUTILISATION,
    SUM(D.MEMORYUTILIZATION) as MEMORYUTILIZATION
    from NHV_ELEMENT_FOR_USER B
    left join NHV_ST_B_104022 A on B.ELEMENT_ID = A.ELEMENT_ID
    LEFT JOIN NHV_ST_B_104050 C on B.ELEMENT_ID = C.ELEMENT_ID
    LEFT JOIN NHV_ST_B_104025 D on D.ELEMENT_ID = B.ELEMENT_ID
    INNER join NH_DATE_DIM E On to_date(D.SAmple_timestamp) = E.CALENDAR_DATE
    OR to_date(A.SAmple_timestamp) = E.CALENDAR_DATE
    OR to_date(C.SAmple_timestamp) = E.CALENDAR_DATE
    group by B.ELEMENT_ID,B.SYS_NAME,B.ALIAS,B.IF_TYPE,
    E.CALENDAR_DATE,to_date(A.SAMPLE_TIMESTAMP),
    to_Date(C.SAMPLE_TIMESTAMP),
    to_DATE(D.SAMPLE_TIMESTAMP)
    order by 2,4,3

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since NOBODY can optimize SQL just by looking at it, we need a few more details.
    1) DDL for all tables & indexes
    2) EXPLAIN PLAN
    3) output from SQL_TRACE & tkprof
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2011
    Posts
    13
    thnaks for ur quick reply but tell me how to provide u DDL details and SQL TRACE.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >thnaks for ur quick reply but tell me how to provide u DDL details
    DBMS_METADATA

    >and SQL TRACE.
    ALTER SESSION SET SQL_TRACE=TURE;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2011
    Posts
    13
    Pls take remote of my laptop .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •