#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    5
    Rep Power
    0

    Difference between v$sql and dba_hist_sqlstat


    1. difference between v$sql and dba_hist_sqlstat?
    2. as of i have understood, dba_hist_sqlstat gets refreshed from v$sql.
    if i run this below query i get different outputs.. how??

    by using v$sql table

    WITH t AS
    (SELECT force_matching_signature ,
    SUM(executions_delta) executions,
    COUNT(*) cnt ,
    parsing_schema_name ,
    MIN(MODILE1) MODULE1 ,
    MAX(MODILE2) MODULE2
    FROM
    (SELECT sql_id ,
    force_matching_signature ,
    SUM(executions) executions_delta ,
    parsing_schema_name ,
    MIN(MODULE) MODILE1 ,
    MAX(MODULE) MODILE2
    FROM v$sql ss
    WHERE ss.force_matching_signature != 0
    AND parsing_schema_name NOT IN ('SYS','ORACLE','DBSNMP')
    GROUP BY sql_id ,
    force_matching_signature ,
    parsing_schema_name
    )
    GROUP BY force_matching_signature ,
    parsing_schema_name
    HAVING COUNT(*) >= 3
    )
    SELECT TO_CHAR(force_matching_signature) AS matching_signature,
    executions ,
    cnt ,
    (SELECT
    (SELECT CAST(SUBSTR(st.sql_text, 1, 4000) AS VARCHAR2(4000)) AS sql_text
    FROM v$sql st
    WHERE sql_id = hs.sql_id
    ) sql_text
    FROM v$sql hs
    WHERE force_matching_signature = t.force_matching_signature
    AND rownum = 1
    ) AS single_sql_statement ,
    parsing_schema_name ,
    MODULE1 ,
    MODULE2
    FROM t

    by using dba_hist tables

    WITH t AS
    (SELECT force_matching_signature ,
    SUM(executions_delta) executions,
    COUNT(*) cnt ,
    parsing_schema_name ,
    MIN(MODILE1) MODULE1 ,
    MAX(MODILE2) MODULE2
    FROM
    (SELECT sql_id ,
    force_matching_signature ,
    SUM(executions_delta) executions_delta ,
    parsing_schema_name ,
    MIN(MODULE) MODILE1 ,
    MAX(MODULE) MODILE2
    FROM dba_hist_sqlstat ss
    WHERE ss.force_matching_signature != 0
    AND parsing_schema_name NOT IN ('SYS','ORACLE','DBSNMP')
    GROUP BY sql_id ,
    force_matching_signature ,
    parsing_schema_name
    )
    GROUP BY force_matching_signature ,
    parsing_schema_name
    HAVING COUNT(*) >= 3
    )
    SELECT TO_CHAR(force_matching_signature) AS matching_signature,
    executions ,
    cnt ,
    (SELECT
    (SELECT CAST(SUBSTR(st.sql_text, 1, 4000) AS VARCHAR2(4000)) AS sql_text
    FROM dba_hist_sqltext st
    WHERE sql_id = hs.sql_id
    ) sql_text
    FROM dba_hist_sqlstat hs
    WHERE force_matching_signature = t.force_matching_signature
    AND rownum = 1
    ) AS single_sql_statement ,
    parsing_schema_name ,
    MODULE1 ,
    MODULE2
    FROM t


    Thanks in advance............
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    5
    Rep Power
    0
    are these outputs valid??
  4. #3
  5. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    119
    Your first query returns ORA-01427: single-row subquery returns more than one row.

IMN logo majestic logo threadwatch logo seochat tools logo