Query to get the SQL details that take more than 5 minutes
=========================================================

SELECT
    sql_id,
    child_number,
    executions,
    elapsed_time,
    cpu_time,
    buffer_gets,
    disk_reads,
    sql_text
FROM
    V$SQL
WHERE
    cpu_time/1000/1000/60 > 5
ORDER BY
    cpu_time DESC;

Explanation of the columns:
===============================
sql_id: The SQL identifier for the SQL statement.
child_number: The child number of the SQL statement (different child numbers represent 
             different execution plans for the same SQL).
executions: The number of times the SQL statement has been executed.
elapsed_time: The total elapsed time (in microseconds) for all executions of the SQL statement.
cpu_time: The total CPU time (in microseconds) consumed by all executions of the SQL statement.
            ==>To get the cpu_time in minutes , we need to write cpu_time/1000/1000/60 
	    ==>First 1000 is to get the time in milliseconds, second 1000 is 
                to get the time in seconds, 60 is for minutes
buffer_gets: The total number of buffer gets (logical reads) for all executions 
             of the SQL statement.
disk_reads: The total number of disk reads for all executions of the SQL statement.
sql_text: The actual SQL text of the statement.



#####Start of the script
#!/bin/bash

#No. of minutes 
MINS=5
# SQL Query to get CPU intensive statements that are taking more than 5 minutes tmie
SQL_QUERY="SELECT sql_id, child_number, executions, elapsed_time, cpu_time, 
           buffer_gets, disk_reads, sql_text
           FROM V\$SQL
           WHERE cpu_time/1000/1000/60 > ${MINS}
           ORDER BY cpu_time DESC;"

# Function to execute SQL query using sqlplus
function execute_sqlplus_query() {
    QUERY="$1"
    sqlplus -S "/as sysdba"    EOF
    set pagesize 0
    set linesize 300
    set feedback off
    set heading off
    set trimspool on
    set colsep '|'
    ${QUERY}
    exit;
EOF
}

# Main function
function main() {
    # Execute the SQL query using sqlplus
    execute_sqlplus_query "${SQL_QUERY}"
}

main
# End of the script

Explanation of the script:
=========================
=> Function definitions  should be loaded before they get called, means definition
         of the function should be placed before they are getting called in the script.
       In this shell script we have two functions 1) main()  2) execute_sqlplus_query
=> In the script ,Connectivity is established by using superuser privileges.
            No need to have tnsnames.ora entry
=> execution starts from the function call  in the last line i.e) "main"
=> In the definition of main , one function execute_sqlplus_query is getting called
        with one parameter i.e) SQL_QUERY.
=> SQL_QUERY is the variable that contains the select query , in the SQL_QUERY ,
       \(back slash has been used because the $SQL will be trated as 
     variable in the shell , hence to change that meaning \ (back slash) has been used
=> SQL_QUERY will be copied to QUERY variable by using $1 and that will be executed
       in the execute_sqlplus_query function and exit  


No.of Parameters to main script: 0 (Zero)
===============================

Sub scripts: None
============

Sample output:
===============

SQL_ID        CHILD_NUMBER EXECUTIONS ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS
------------- ------------ ---------- ------------ ---------- ----------- ----------
SQL_TEXT
b6usrg82hwsa3            0         12    404680720  391417122    16955963      67802
call dbms_stats.gather_database_stats_job_proc (  )

Difference between CPU_TIME and  ELAPSED_TIME?
In summary, CPU_TIME measures the CPU processing time, while ELAPSED_TIME
 measures the total time taken for the entire query execution, including CPU processing time and any wait times for I/O or other resources. Both metrics are essential for performance tuning and identifying performance bottlenecks in SQL queries.