Skip to main content

Posts

Showing posts from September, 2020

How to get SQL ID STAT information in Oracle Database 12c

 How to get SQL ID STAT information in Oracle Database 12c Please use the following Query to get the information of SQL ID statistics in oracle database. It will provide you the information about SQL ID and their Plan hash value information along with execution details. Don't forget to remove comments and format the SQL before running. WITH sql_id_list AS ( SELECT /*+ MATERIALIZE */ regexp_substr(:SQL_ID, '[^,]+', 1, LEVEL) SQL_ID   FROM dual CONNECT BY LEVEL <= length(:SQL_ID) - length(REPLACE(:SQL_ID, ',', '')) + 1 ), sqlstat AS ( SELECT TRUNC(last_active_Time,:TRUNC_DATE_FORMAT) last_active_Time       --,s.instance_number inst_num       ,s.sql_id       ,s.plan_hash_value       ,MAX(s.child_number) max_child_number       ,command_type       ,parsing_schema_name schema       --,module       ,ROUND((SUM(s.elapsed_time)/1000000)/60,2) elapsed_time_minutes ...

How to get SQL Standard Deviation information of SQL ID in Oracle 12c

 How to get SQL Standard Deviation information of SQL ID in Oracle Database. Please user the below SQL to get the information of SQL Standard Deviation information. Don't forget to remove comment lines before running and formatting the sql. WITH sqlstat AS ( SELECT TRUNC(h.begin_interval_time,:TRUNC_DATE_FORMAT) snap_time  -- Change to MI, HH , or DD for a individual sql statement to checking performance over time       ,s.instance_number inst_num       --,MIN(h.begin_interval_time) min_start_date       --,MAX(h.begin_interval_time) max_end_date       --,MAX(s.snap_id) max_snap_id       ,s.sql_id       ,s.plan_hash_value       ,parsing_schema_name schema       ,module       ,ROUND((SUM(s.elapsed_time_delta)/1000000)/60,2) elapsed_time_minutes       ,ROUND(SUM(s.elapsed_time_delta)/1000000,2) elapsed_time_seconds     ...