If you use bind variable instead of literal like following then Oracle will evaluate as same SQL and will use same execution plan and won’t be hard parse in Oracle. But Oracle optimizer will evaluate these SQLs like different SQL. select * from customer where id=63 Ībove queries are almost same, just id variable is different. If you don’t use bind variables in the SQL queries then Oracle will know similar sqls differently like following. select count(*) FROM gv$session s, gv$process pĪnd s.TYPE ='USER' and s.username!='SYS' and status='ACTIVE' and last_call_et > 600 You can list all user sessions which are ACTIVE state more than 600 Second with following script. You can list only Inactive User sessions without sys user with following script select count(*) FROM gv$session s, gv$process pĪnd s.TYPE ='USER' and s.username!='SYS' and status='INACTIVE' You can list only Active User sessions without sys user with following script select count(*) FROM gv$session s, gv$process pĪnd s.TYPE ='USER' and s.username!='SYS' and status='ACTIVE' select count(*) FROM gv$session s, gv$process p You can list how many Active and Inactive User sessions are in the Oracle database with following script. select * FROM gv$session s, gv$process pĪnd s.TYPE ='USER' and s.username!='SYS' This script will list you just only User type sessions and their detais. To list all User Sessions not Background, use following scripts. GROUP BY end_time.sample_time, start_time.sample_time WHERE ash.sample_time BETWEEN start_time.sample_time WHERE sample_time BETWEEN SYSDATE - 1 / 1440 AND SYSDATE) end_time, WHERE sample_time BETWEEN SYSDATE - 1 / 1440 AND SYSDATE) start_time, ( ( CAST (end_time.sample_time AS DATE)įROM (SELECT MIN (sample_time) sample_time WHEN ( ( CAST (end_time.sample_time AS DATE) You can find average Active session of database with following script. UPPER(SQL_TEXT) LIKE '%DEVECI%' ORDER BY ELAPSED_TIME_DELTA DESC SELECT st.sql_text, sub.sql_id, sub.ELAPSED_TIME PER_EXEC_ELAPSED_TIME_MINUTESįROM dba_hist_sqlstat t, dba_hist_snapshot s, DBA_HIST_SQLTEXT stĪND t.instance_number = s.instance_numberĪND h.instance_number = x.instance_numberĪND h.event in ('db file sequential read','db file scattered read') You can find TOP Elapsed time SQL in certain hours with below script. Oracle DBA should monitor database everytime and if there are many sqls which is running long execution time then it should be reported to the developer and developer and dba should examine these sqls. Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA ) Oracle DBA ( Database Administrator ) needs useful scripts to monitor, analyze and check Oracle database for routine database operations and monitoring.īefore Oracle DBA Scripts, If you don’t know Oracle database and want to learn, click following link to start learning Oracle database with this tutorial.

I will share all Oracle DBA Scripts ( Oracle RAC, Dataguard, Performance Tuning, Monitoring and etc.

Camelot unchained