Important stats related SQL queries with details of determining Tablespace

This article on Oracle database provides details about how can we monitor tablespace during sql execution. In this article we will also see how to what are the sqls are currently executing. During this process we will understand, how to get SQL ID associated with each sql statement.

Tablespaces in the Oracle database

The tablespace is one or more logical storage units in the Oracle database. Each database table belongs to some tablespace in the oracle database. Normally, Oracle DBA has alerts setup to monitor tablespace, so that SQL or jobs which are based on SQL statements will not fail due to tablespace issue. However, as a developer, we can also monitor tablespace using the SQL statement below -

SELECT B.TABLESPACE_NAME, TBS_SIZE SIZE_MB, A.FREE_SPACE FREE_MB FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024 ,2) AS FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS TBS_SIZE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME UNION SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TBS_SIZE FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME ) B WHERE A.TABLESPACE_NAME(+)=B.TABLESPACE_NAME;

To determine used and free tablespace, use the sql below

select tablespace_name, SUM(bytes_used/1024/1024/1024) "Temp_Used", sum(bytes_free/1024/1024/1024) "Temp_Free"

from v$temp_space_header where tablespace_name like '%TEMP%' group by tablespace_name;

Determine currently running Active DB Sessions

As Oracle developer or application developer, we can use the query below to determine the active DB sessions in the Oracle database -

SELECT * FROM GV$SESSION WHERE STATUS = 'ACTIVE' AND TYPE <> 'BACKGROUND' AND USERNAME = 'TEST_SCHEMA_ID';

This query will return important details e.g. USERNAME, STATUS, database MACHINE, SERVICE_NAME, SQL_ID etc.

Determine which SQL statement is running in Database

Before determining SQL statement, we need to determine what are the active session associated with SQL statement which can be determined with help of above section - 'Determine currently running Active DB Sessions'.

By using the result of above query, get the SQL_ID associated with active session. Once we have SQL_ID then execute the SQL statement below to determine the query which is running in the database -

SELECT * FROM GV$SQL WHERE SQL_ID = '61f38skxkw8hc'

e.g. Here '61f38skxkw8hc' is value for SQL_ID


Get Explain Plan
In order to get explain plan execute the statement below -

select * from TABLE(dbms_xplan.display_awr('61f38skxkw8hc'));

Get Start and End time of SQL Query

select sql_id, first_load_time, last_load_time, elapsed_time, cpu_time from   v$sql where  sql_text like 'with /* slow */ rws as (%';

select sql_id, elapsed_time_delta/executions_delta avg_elapsed
from   sys.dba_hist_sqlstat
where  snap_id = :snap;

Getting SQL Event details

SELECT SNAP_ID, SQL_TIME, SESSION_ID, USER_ID, SQL_ID, SQL_OPNAME,
TO_CHAR(A.SQL_EXEC_START, 'MM/DD/YYYY HH24:MI:SS'), SESSION_STATE,
EVENT, TIME_WAITED, PROGRAM, A.MACHINE FROM DBA_HIST_ACTIVE_SESS_HISTORY A
WHERE
A.SQL_TIME BETWEEN TO_DATE('03/10/2018 09:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND TO_DATE('03/11/2018 6:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND SQL_ID = 'XXX'
AND EVENT IS NOT NULL
AND EVENT <> 'CELL SINGLE BLOCK PHYSICAL READ'
ORDER BY SQL_TIME ASC