These are the very useful scripts, that I often use it. this can be very
handy.
[ For RAC, you may have to tweak it a bit, using gv$ views for some scripts]
Sample Output :
Ok, Now I know what sql's are running currently, I am interested in checking the execution plan of it. You can get the execution plan from cursor. If not available, then you can get it from AWR.
Cursor :
select * from table(dbms_xplan.display_cursor('&sql_id',0));
From AWR it gives all previous execution plans, which are used for a that SQL ID
Suppose, I need to get a execution plan for sql_id with with specific plan_hash_value only, then I would do.
SQL_ID & Plan_Hash_Value:
select plan_table_output from table (dbms_xplan.display_awr('&sql_id','&PlanHash'));
Get bind values for SQL query along with execution plan
select * from table(dbms_xplan.display_cursor('&sqlid',NULL,'peeked_binds'))
Now I am also interested to see, all previous history when this SQL was executed, how may times executed and what execution plan it used that time. Now this is very interesting and very useful.
set lines 200 pages 200
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
col sql_profile format a30
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode (nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode (nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,sql_profile
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','XXXXXXXXX')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
Output :
If you see above output, it will give you, Snapshot ID, Node, execution time, Plan details (if it used a different execution plan at different time), number of execution, avg execution time, AVG_LIO (Logical IO or Buffer Gets), also whether it is using any SQL profile that you have created. Pretty cool script !!
set lines 200 pages 200
col WAIT_CLASS format a30
select * from (Select distinct a.event, a.total_waits, a.time_waited, a.average_wait, c.wait_class
From v$system_event a, v$event_name b, v$system_wait_class c, v$active_session_history d
Where a.event_id=b.event_id
And a.event_id=d.event_id
And b.wait_class#=c.wait_class#
And c.wait_class in ('CPU','CPU Wait','Application','Concurrency','Cluster','Other','Network','Configuration','Commit','User I/O','System I/O')
And d.sample_time > sysdate - ( 5 / ( 24 * 60 ) )
order by average_wait desc)
where rownum <= 15;
-- Waits during last 5 Minutes
SELECT event Event_LAST_5Min,
COUNT(*)
FROM v$active_session_history
WHERE session_state = 'WAITING'
AND sample_time > sysdate - ( 5 / ( 24 * 60 ) )
GROUP BY event
ORDER BY COUNT(*) DESC;
select event, sql_id, count(*),
avg(time_waited) avg_time_waited
from v$active_session_history
where event like nvl('&event','%more data from%')
group by event, sql_id
order by event, 3
/
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
break on index_name on uniqueness skip 1
column column_expression format a20
col COLUMN_NAME format a40
col INDEX_NAME format a30
select i.index_name, i.uniqueness, c.column_name, f.column_expression,i.status,i.visibility,i.last_analyzed
from all_ind_columns c, all_indexes i, all_IND_EXPRESSIONS f
where i.table_owner = '&OWNER'
and i.table_name = '&TABLE_NAME'
and i.index_name = c.index_name
and i.owner = c.indeX_owner
and c.index_owner = f.index_owner(+)
and c.index_name = f.index_name(+)
and c.table_owner = f.table_owner(+)
and c.table_name = f.table_name(+)
and c.column_position = f.column_position(+)
order by i.index_name, c.column_position
/
select DBMS_METADATA.GET_DDL('TABLE','&TABLE_name') from DUAL;
select
dbms_metadata.get_ddl('TABLESPACE',&tablespace_name)
from
dba_tablespaces
;
I end here, with these 10 most useful scripts, I will share some more scripts in my next blogs.
[ For RAC, you may have to tweak it a bit, using gv$ views for some scripts]
1. Monitor Database, what top SQL's are currently running in your DB instance.
(Get sqlid, sql text, serial, elapsed time, plan_hash_value etc ) This can be a good start :)
set lines 200 pages 200
col USERNAME format a15
col SQL_TEXT format a30
select sesion.sid,
sesion.SERIAL#,
sesion.username,
optimizer_mode,
plan_hash_value,
address,
cpu_time,
elapsed_time/1000/1000 elapsed_Second,
sqlarea.SQL_ID,
substr(sql_text,1,30) sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
and rownum <= 20
order by elapsed_time desc
;
col SQL_TEXT format a30
select sesion.sid,
sesion.SERIAL#,
sesion.username,
optimizer_mode,
plan_hash_value,
address,
cpu_time,
elapsed_time/1000/1000 elapsed_Second,
sqlarea.SQL_ID,
substr(sql_text,1,30) sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null
and rownum <= 20
order by elapsed_time desc
;
Sample Output :
2. Show SQL Execution Plan for any SQL ID
Ok, Now I know what sql's are running currently, I am interested in checking the execution plan of it. You can get the execution plan from cursor. If not available, then you can get it from AWR.
Cursor :
select * from table(dbms_xplan.display_cursor('&sql_id',0));
AWR:
select plan_table_output from table
(dbms_xplan.display_awr('&sqlid'));From AWR it gives all previous execution plans, which are used for a that SQL ID
Suppose, I need to get a execution plan for sql_id with with specific plan_hash_value only, then I would do.
SQL_ID & Plan_Hash_Value:
select plan_table_output from table (dbms_xplan.display_awr('&sql_id','&PlanHash'));
Get bind values for SQL query along with execution plan
select * from table(dbms_xplan.display_cursor('&sqlid',NULL,'peeked_binds'))
3. SQL ID execution history, previous execution and elapsed time, what execution plan it used.
Now I am also interested to see, all previous history when this SQL was executed, how may times executed and what execution plan it used that time. Now this is very interesting and very useful.
set lines 200 pages 200
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
col sql_profile format a30
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode (nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode (nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,sql_profile
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','XXXXXXXXX')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
Output :
If you see above output, it will give you, Snapshot ID, Node, execution time, Plan details (if it used a different execution plan at different time), number of execution, avg execution time, AVG_LIO (Logical IO or Buffer Gets), also whether it is using any SQL profile that you have created. Pretty cool script !!
4. Find Top wait events in last 5 minutes.
set lines 200 pages 200
col WAIT_CLASS format a30
select * from (Select distinct a.event, a.total_waits, a.time_waited, a.average_wait, c.wait_class
From v$system_event a, v$event_name b, v$system_wait_class c, v$active_session_history d
Where a.event_id=b.event_id
And a.event_id=d.event_id
And b.wait_class#=c.wait_class#
And c.wait_class in ('CPU','CPU Wait','Application','Concurrency','Cluster','Other','Network','Configuration','Commit','User I/O','System I/O')
And d.sample_time > sysdate - ( 5 / ( 24 * 60 ) )
order by average_wait desc)
where rownum <= 15;
-- Waits during last 5 Minutes
SELECT event Event_LAST_5Min,
COUNT(*)
FROM v$active_session_history
WHERE session_state = 'WAITING'
AND sample_time > sysdate - ( 5 / ( 24 * 60 ) )
GROUP BY event
ORDER BY COUNT(*) DESC;
5. Find SQL ID or SQL Query waiting on wait event
select event, sql_id, count(*),
avg(time_waited) avg_time_waited
from v$active_session_history
where event like nvl('&event','%more data from%')
group by event, sql_id
order by event, 3
/
6. Find database size
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
7. Find all Tablespace Size and usage
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
8. Find all indexes for a table, with details
break on index_name on uniqueness skip 1
column column_expression format a20
col COLUMN_NAME format a40
col INDEX_NAME format a30
select i.index_name, i.uniqueness, c.column_name, f.column_expression,i.status,i.visibility,i.last_analyzed
from all_ind_columns c, all_indexes i, all_IND_EXPRESSIONS f
where i.table_owner = '&OWNER'
and i.table_name = '&TABLE_NAME'
and i.index_name = c.index_name
and i.owner = c.indeX_owner
and c.index_owner = f.index_owner(+)
and c.index_name = f.index_name(+)
and c.table_owner = f.table_owner(+)
and c.table_name = f.table_name(+)
and c.column_position = f.column_position(+)
order by i.index_name, c.column_position
/
9. Fetch Table definition
select DBMS_METADATA.GET_DDL('TABLE','&TABLE_name') from DUAL;
10. Fetch Tablespace definition
select
dbms_metadata.get_ddl('TABLESPACE',&tablespace_name)
from
dba_tablespaces
;
I end here, with these 10 most useful scripts, I will share some more scripts in my next blogs.
No comments:
Post a Comment