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 ...
One blog for all ORACLE DBA scripts & AWS Cloud commands, OCI commands etc