DBA Checklist (Activities of Oracle DBA) Contents of this Post Daily DBA Checklist Daily Night DBA Checklist Weekly DBA Checklist Weekly Tuning DBA Checklist Monthly DBA Checklist Quarterly DBA Checklist One Time Activity DBA Checklist Daily DBA Checklist Health check of the Database Instance and Listener. Viewing the Alert log file and/or check Alert log in regular interval to solve t he ORA errors. Check any session blocking the other session and oracle locks. Clear locks Check long running UNIX process Ensure that there are no DBMS_JOBS with the status of failed or broken. Also las t refresh times of all running jobs should be current. Check all cron house keeping script logs Daily Tablespace Utilization. Rebuilding of Indexes, if bulk load of data is inserted. Check the temporary tablespace/files. Check the UNDO tablespace and retaintion. Monitor the Unix /tmp and /var Location Monitor the UTL_FILE location. Monitor all Database file system or drive. Monitor Archive Log location. Verify success of database archiving to tape Monitoring Backups. Monitoring the log files, backups, database space usage and the use of system re sources. Monitoring Production Database Performance Find high CPU/Memory/Physical IO consuming processes and trace the SQL/From runn ing behind and Update to Application team. Check OEM Agent is running Or not in each node. Verify DBSNMP is running Verify success of database backup Daily RMAN(Incremental+Cumulative)/Data Pump export backups after business hours . User Management. User Profile monitoring. Monitor User account GRACE period. Check Invalid objects and recompile. Check and monitor Audit log or table. Backup your CRONTAB or Win Schedular Daily Night DBA Checklist Look for objects that break rules (Check for Huge NEXT_EXTENT or MAX_EXTENT) Check the objects reaching to it s Max extents Note, All tables should have unique primary keys, so check missing/disabled PK a nd Check for Block corruption Weekly DBA Checklist Database Growth Comparision. Identify bad growth projections.
RMAN full databsae(Level 0) backup. Weekly cold backup during maintaince windows. Analyze Database and Schemas to gather statistics Check Index monitoring Usage to validate the Index usage which is not used yet. Drop/Mark Unuseable the unused Indexes. All indexes should use INDEXES tablespace and shoud not user DATA tablespace. All index datafile should not be in same file system where the DATA tablespace o r SYSTEM/SYSAUX/UNDO/TEMP datafiles are. Look in SQL*Net logs for errors, issues (Both in Client side & Server side) Archive all Alert Logs and application log to history Check the number of log switch per hour How_much_redo_generated_per_hour Check free quota limited available of each user Truncate the listener.log file in the $ORACLE_HOME/network/log, if the listener log has increased to a size > than 500 MB. Ensure the space is released, otherwi se 'reload' listener. Weekly Tuning DBA Checklist Check Check Check Check Check Check Check Check Check Check
the Chaining & Migrated Rows the size of tables & check weather it need to partition or not the objects having the more extents the tables having FK but there is no Index the tables having no Indexes and tables having more Indexes the frequently pin objects & place them in separate tablespace & in cache the objects reload in memory many time open cursor not reaching to the max limit locks not reaching to the max lock I/O of each data file
Monthly DBA Checklist Index Rebuild. Tablespace Reorganization. Bounce critical database once a month (If no cold backup configured) Look for Harmful Growth Rates Review database file activity. Compare to past output to identify trends that c ould lead to possible contention. Investigate fragmentation (e.g. row chaining, etc.). Check location of data file also check auto extendable or not Check default tablespace & temporary tablespace of each user Check the Extents of each object and compare if any object extent are overridden which is define at tablespace level Tablespace need coalescing Check the overall database statistics Trend Analysis of objects with tablespace, last analyzed, no. of Rows, Growth in days & growth in KB Quarterly DBA Checklist Patching Database Reorganization Check the quota of non-system tables in system tablespace. Bounce most critical database once a month (If no cold backup configured) Review common Oracle tuning points such as cache hit ratio, latch contention, an d other points dealing with memory management One Time Activity DBA Checklist
Database user creation with required privileges Make the portal of Oracle Predefined error with possible solution. Check database startup time(if not 24X7) Check location of control file Check location of log file Prepare the Backup strategy and test all the recovery scenario
Long Running SQL (V$SESSION_LONGOPS) Contents of this Post Active SQL Find Long Operations Check Time Remaining Of Export/Import Monitor Index Creation Queries currently running for more than 60 seconds Oracle Locks The V$SESSION_LONGOPS view displays the status of various operations that run fo r longer than 6 seconds. These operations currently include Backup and recovery functions Statistics gathering Query execution Import progress Index Creation etc.. To monitor query execution progress, you must be using the cost-based optimizer and you must: · Set the TIMED_STATISTICS or SQL_TRACE parameter to true · Gather statistics for your objects with the ANALYZE statement or the DBM S_STATS package You can add information to this view about application-specific long-running ope rations by using the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure. ACTIVE SQL set lines 200 pages 200 col SQL_TEXT for a77 select S.USERNAME, s.sid, s.osuser, sql_text from v$sqltext_with_newlines t,V$SESSION s where t.address =s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE' and s.username <> 'SYSTEM' --AND USERNAME='&USERNAME' order by s.sid,t.piece; Find Long Operations (e.g. full table scans, RMAN, Insert, Import) select substr(sql_text,instr(sql_text,'INTO "'),30) table_name, rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60, 1) Minutes, trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24 :mi:ss'))*24*60)) Rows_Per_Minute
from sys.v_$sqlarea where sql_text like 'INSERT %INTO "%' and command_type = 2 and open_versions > 0; OR set lines 200 pages 200 col username format a20 col message format a70 --col remaining format 9999 select username||'-'||sid||','||SERIAL# username , to_char(start_time, 'hh24:mi:ss dd/mm/yy') started , time_remaining remaining_Sce , ELAPSED_SECONDS , round((sofar/totalwork)* 100,2) "COMPLETE%" , message from v$session_longops where time_remaining <> 0 --and TARGET like '%&USERNAME.%' order by time_remaining desc; Check Time Remaining Of Export/Import SELECT table_name ,rows_processed ,Minutes,Rows_Per_Minute ,(1/Rows_Per_Minute)*(147515763-rows_processed) Time_Remaining_Min From (select substr(sql_text,instr(sql_text,'INTO "'),30) table_name, rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) M inutes, trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi: ss'))*24*60)) Rows_Per_Minute from sys.v_$sqlarea where sql_text like 'INSERT %INTO "%' and command_type = 2 and open_versions > 0); Monitor Index Creation col sid format 9999 col start_time format a5 heading "Start|time" col elapsed format 9999 heading "Mins|past" col min_remaining format 9999 heading "Mins|left" col message format a81 select sid , to_char(start_time,'hh24:mi') start_time , elapsed_seconds/60 elapsed , round(time_remaining/60,2) "min_remaining" , message from v$session_longops where time_remaining > 0 AND MESSAGE like '%&TABLE_NAME.%'; --AND MESSAGE like '%&USWENAME.TABLE_NAME.%'; Queries currently running for more than 60 seconds (For Procedure & Package) select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from
v$session s join v$sqltext_with_newlines q on s.sql_address = q.address where status='ACTIVE' and type <>'BACKGROUND' and last_call_et> 60 order by sid,serial#,q.piece; Oracle Locks select object_name, object_type, session_id, type, -- Type or system/user lock lmode, -- lock mode in which session holds lock request, block, ctime -- Time since current mode was granted from v$locked_object, all_objects, v$lock where v$locked_object.object_id = all_objects.object_id AND v$lock.id1 = all_objects.object_id AND v$lock.sid = v$locked_object.session_id order by session_id, ctime desc, object_name /
Scripts For Oracle Segments/Tables Contents of this Post Schema Size Table Size BLOB column Size Data File for Table Last DML on Table Missing Columns in 2 Table Extract TABLE Creation Scripts TOP Table/Index Reads What is Segment, Tablespaces, Schema And Datafiles A segment is a database object that has space allocated to it. It is a logical s tructure of the database Segment is nothing but a table/index/mview etc. A segme nt consists of one or more extents allocated within a tablespace. Oracle stores data logically in tablespaces and physically in datafiles associated with the co rresponding tablespace. Although databases, tablespaces, datafiles, and segments are closely related, th ey have important differences: Databases and Tablespaces : An Oracle database is comprised of one or more logic al storage units called tablespaces. The database's data is collectively stored
in the database's tablespaces. Tablespaces and Datafiles : Each tablespace in an Oracle database is comprised o f one or more operating system files called datafiles. A tablespace's datafiles physically store the associated database data on disk. Databases and Datafiles : A database's data is collectively stored in the datafi les that constitute each tablespace of the database. For example, the simplest O racle database would have one tablespace and one datafile. A more complicated da tabase might have three tablespaces, each comprised of two datafiles (for a tota l of six datafiles). Schema objects, Segments, and Tablespaces : When a schema object such as a table or index is created, its segment is created within a designated tablespace in t he database. For example, suppose you create a table in a specific tablespace us ing the CREATE TABLE command with the TABLESPACE option. Oracle allocates the sp ace for this table's data segment in one or more of the datafiles that constitut e the specified tablespace. An object's segment allocates space in only one tabl espace of a database. See Chapter 3, "Data Blocks, Extents, and Segments", for m ore information about extents and segments and how they relate to tablespaces. Types of segment:CLUSTER INDEX INDEX PARTITION LOB PARTITION LOBINDEX LOBSEGMENT NESTED TABLE ROLLBACK TABLE/DATA TABLE PARTITION TYPE2 UNDO SCHEMA Size set lines 200 pages 200 column Bytes format 9,999,999,999; break on owner skip 1; compute Sum of "Size In MB" on OWNER select owner, segment_type, sum(bytes)/1024/1024 "Size In MB" from dba_segments --where owner in ('SCOOT','ADMIN') group by owner, segment_type order by 1; clear breaks clear compute Table Size SELECT owner, table_name, TRUNC(sum(bytes)/1024/1024) SIZE_In_MB FROM (SELECT segment_name table_name, owner, bytes FROM dba_segments WHERE segment_type = 'TABLE' UNION ALL SELECT i.table_name, i.owner, s.bytes FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner
AND s.segment_type = 'INDEX' UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner = l.owner AND s.segment_type = 'LOBSEGMENT' UNION ALL SELECT l.table_name, l.owner, s.bytes FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = 'LOBINDEX') WHERE owner = '&USERNAME' le size in DB AND table_name = '&TABLE_NAME' size in DB GROUP BY table_name, owner HAVING SUM(bytes)/1024/1024 > 10 10 MB */ ORDER BY owner,SUM(bytes) desc;
-- Disable this line for all Tab -- Disable this line for all Table /* Ignore really small tables <
OR select OWNER,TABLE_NAME,ROW_SIZE_KB,BLOCK_SIZE_KB,(ROW_SIZE_KB*BLOCKS)/1024 Tot_ size_MB from ( select OWNER,TABLE_NAME, ROUND((AVG_ROW_LEN * NUM_ROWS / 1024), 2) ROW_SIZE_ KB, (BLOCKS * 8) BLOCK_SIZE_KB,BLOCKS from DBA_TABLES where OWNER not in ('SYS','SYSTEM','ORDSYS','DBSNMP','CTXSYS','XDB','OLAPSYS ','WMSYS','WKSYS','MDSYS','EXFSYS','SYSMAN') ) Where ROW_SIZE_KB <> 0 and (ROW_SIZE_KB*BLOCKS)/1024 > 10 order by OWNER,TABLE_NAME; BLOB column Size select a.*,round(dbms_lob.getlength(a.bin)/1024,2) "Blob_Size_KB" from OWNER.BLO B_TABLE_NAME a where rownum < 20; select round(sum(dbms_lob.getlength(bin))/(1024*1024),2) "All_Blob_Size_MB" from OWNER.BLOB_TABLE_NAME; Query to get data file name of a given table (Output depends on Size of the Tabl e) column FILE_NAME for a60 select file_name, round( bytes/1024/1024 ) mbytes, round( ratio_to_report(bytes) over () * 100, 2 ) pct from ( select sum(a.bytes) bytes, b.file_name from dba_extents a, dba_data_files b where a.owner = 'RMAN' and a.segment_name = 'DB' and a.segment_type = 'TABLE' and a.file_id = b.file_id group by b.file_name
) order by file_name; Last DML Of the TABLE select TABLE_OWNER,TABLE_NAME,INSERTS,UPDATES,DELETES,to_char(TIMESTAMP,'dd-Monyyyy hh24:mi:ss') TIMESTAMP,TRUNCATED,DROP_SEGMENTS from DBA_TAB_MODIFICATIONS where TABLE_OWNER in ('SCXDOWNLOAD') and TABLE_NAME ='HISMES0'; Use below processes:STEP STEP STEP STEP STEP
1:2:3:4:5:-
Alter Table Tabname Monitoring; Select * from User_Tab_Modifications; exec dbms_stats.flush_database_monitoring_info; select * from dba_tab_modifications; alter table tablename nomonitoring;
Compare Columns between 2 Tables Over DB_LINK Outer table should be the base table and inner is the derive. select OWNER,TABLE_NAME,COLUMN_NAME from dba_tab_cols@DB_LINK where table_name='TABLE' and COLUMN_NAME not in (select COLUMN_NAME from dba_tab_cols where table_name='T ABLE'); Extract TABLE Creation Scripts set long 999999 select dbms_metadata.get_ddl('TABLE',table_name,owner) || '/' from dba_tables wh ere owner='RMAN' and table_name='DB' union all select dbms_metadata.get_ddl('INDEX',index_name,owner) || '/' from dba_indexes w here owner='RMAN' and table_name='DB'; TOP 10 Table Reads PHYSICAL_READS Whenever you execute a query, Oracle has to go and fetch data to give you the re sult of the query execution. Here, data means the actual data in data blocks. Wh enever a new data block is requested, it has to be fetched from the physical dat afiles residing on the physical disks. This fetching of data blocks from the phy sical disk involves an I/O operation known as physical I/O. By virtue of this ph ysical I/O, now the block has been fetched and read into the memory area called buffer cache. This is a default action. We know that a data block might be requested multiple times by multiple queries. So what happens when the same data block is requested again by the some other us er? (See LOGICAL READS) col segment_name format a30 col owner format a30
select owner,segment_name,object_type,total_physical_reads from ( select owner,object_name as segment_name,object_type, value as total_physical_reads from v$segment_statistics where statistic_name in ('physical reads') And OBJECT_TYPE = 'TABLE' Table Access -- And OBJECT_TYPE = 'INDEX' Index Access And OWNER in ('ERMOWNER','INSIGHT','SNAPMGR') order by total_physical_reads desc) where rownum <11;
-- Top 10 -- Top 10
LOGICAL_READS Once a physical I/O has taken place and the block has been read into the memory, the next request for the same data block won t require the block to be fetched fr om the disk and hence avoiding a physical I/O. So now to return the results for the select query requesting the same data block, the block will be fetched from the memory and is called a Logical I/O. Whenever the quantum of Logical I/O is calculated, two kinds of reads are consid ered - Consistent reads and Current reads. Jointly, these 2 statistics are known as Logical I/O performed by Oracle. Let us look at these separately to better understand the concept of Logical I/O. See More : http://www.dba-oracle.com/t_oracle_logical_io_physical_io.htm col segment_name format a30 col owner format a30 select owner,segment_name,object_type,total_logical_reads from ( select owner,object_name as segment_name,object_type, value as total_logical_reads from v$segment_statistics where statistic_name in ('logical reads') And OBJECT_TYPE = 'TABLE' Table Access -- And OBJECT_TYPE = 'INDEX' Index Access And OWNER in ('ERMOWNER','INSIGHT','SNAPMGR') order by total_logical_reads desc) where rownum <11;
Oracle Tablespaces And Datafiles
-- Top 10 -- Top 10
Contents of this Post Check Tablespace Size Details DataFile AUTOEXTENSIBLE Info Check Max/Min Size Of DataFile In Tablespace Tablespace & Datafile Creation Time Tablespace Report With Space MARK TEMPORARY Tablespace UNDO Tablespace Permanent Tablespace Size set lines 200 pages 200 col TABLESPACE_NAME for a25 col "FREE%" for a10 col "USED%" for a10 col STATUS for a7 Select a.TABLESPACE_NAME,round(a.BYTES/1073741824,2) AVAIL_GB,round(b.BYTES/1073 741824,2) USED_GB,round(c.BYTES/1048576,2) FREE_MB, round((b.BYTES*100)/a.BYTES,2)||'%' "USED%", round((c.BYTES*100)/a.BYTES,2)||'%' "FREE%" ,NVL2(to_char(a.BYTES),'ONLINE','OFFLINE') STATUS from sm$ts_avail a,sm$ts_used b,sm$ts_free c where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and a.TABLESPACE_NAME=c.TABLESPACE_NAME(+); OR select TABLESPACE_NAME, sum(BYTES)/(1024*1024) SIZE_MB from dba_free_space group by TABLESPACE_NAME; select sum(BYTES)/(1024*1024) SIZE_MB from dba_free_space where TABLESPACE_NAME= 'UNDOTBS'; Datafile Location column FILE_NAME for A55 select FILE_ID,FILE_NAME, bytes/(1024*1024) Size_MB from dba_data_files where TA BLESPACE_NAME='UNDOTBS' order by FILE_ID; Resize or Add Datafile to a Tablespace ALTER TABLESPACE DATA_B ADD DATAFILE '/u02/oradata/SOA/PROD_soainfra14.dbf' SIZE 2G; (9i DB doesn't support size in GB) ALTER DATABASE DATAFILE '/u02/oradata/SOA/PROD_soainfra13.dbf' RESIZE 5G; DataFile + AUTOEXTENSIBLE Info col TABLESPACE_NAME for a15 col "File Name" for a60 col "AutoEx Remaining GB" for a19 col STATUS for a10 col "AutoEx" for a6 SELECT to_char(FILE_ID,99)||') '||FILE_NAME "File Name",TABLESPACE_NAME,round(BY TES/1024/1024,2) "Size MB", STATUS, AUTOEXTENSIBLE "AutoEx" ,round(USER_BYTES/1024/1024,2) "UsrMB",(INCREMENT_BY*8192)/1024/1024 "AutoEx By Mb" ,round(MAXBYTES/1024/1024/1024,2) "AutoEx MaxGB", DECODE(AUTOEXTENSIBLE,'NO','N/ A' ,round((MAXBYTES-BYTES)/1024/1024/1024,2)) "AutoEx Remaining GB" from dba_data_files
--where TABLESPACE_NAME='VWSE_DATA' --Where AUTOEXTENSIBLE ='YES' -- in ('DST_ADMIN_DATA','DST_ADMIN_INDEX') Order by TABLESPACE_NAME,FILE_ID; Identify Max/Min Size Of DataFile In Tablespace col FILE_NAME for a50 col ID for 9999 col TABLESPACE_NAME for a20 select TO_CHAR(FILE_ID,99) ID ,FILE_NAME,TABLESPACE_NAME,Tot_Gb,AUTOEXTENSIBLE ,INCREMENT_BY,Max_Gb,Usr_Mb ,DECODE(Min_File_Size,1,'Min Size DataFile','Max Size DataFile') "File_Desc" from ( select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1073741824 Tot_Gb, AUTOEXTENSIBLE ,INCREMENT_BY, MAXBYTES/1073741824 Max_Gb,USER_BYTES/1048576 Usr_Mb ,ROW_NUMBER () OVER(PARTITION BY TABLESPACE_NAME ORDER BY BYTES) Min_File_Size ,ROW_NUMBER () OVER(PARTITION BY TABLESPACE_NAME ORDER BY BYTES Desc) Max_File_S ize from dba_data_files --WHERE TABLESPACE_NAME in ('DATA','INDEX','EX_DATA')) WHERE Min_File_Size = 1 -- Enable To Sele ct Minimun Size Of Datafile --WHERE Max_File_Size=1 -- Enable To Select Maximun Size Of Datafile ORDER BY TABLESPACE_NAME; Tablespace & Datafile Creation Time SET LINE 150 pages 200 col FileNo for 99999 col "Tablespace Name" for a20 col "Datafile Name" for a50 col "DataFile Size" for a13 col "Size In MB" noprint BREAK ON "Tablespace_Created" TTITLE CENTER 'T A B L E S P A C E A N D D A T A F I L E C R E A T I O N T I M E' SKIP 1 CENTER ============================================================ ========= SKIP 1 SELECT FILE# FileNo,b.NAME "Tablespace Name" ,to_char(c.Tblsp_Creation_Time,'ddth-Mon-YY hh24:mi') "Tablespace_Created", a.NAME "Datafile Name" , TO_CHAR(CREATION_TIME,'ddth-Mon-YY hh24:mi') "Datafile_Created" , BYTES/1024/1024 "Size In MB", decode(sign(length(BYTES)-10),-1,lpad(BYTES/1024/1024,3)||' MB' ,lpad(BYTES/1024/1024/1024,3)||' GB') "DataFile Size", STATUS,ENABLED from v$datafile a, v$tablespace b, (select b.NAME Tablespace ,min(CREATION_TIME) Tblsp_Creation_Time from v$datafile a ,v$tablespace b where a.TS#=b.TS# group by b.NAME) c WHERE a.TS#=b.TS# AND b.NAME=c.Tablespace order by b.NAME,CREATION_TIME; clear breaks; Data File Status And Creation Time
col TABLESPACE for a12 Select a.NAME ,b.NAME TABLESPACE ,TO_CHAR(CREATION_TIME,'dd-Mon-yy hh24:mi:ss') CREATION_TIME ,STATUS,ENABLED ,LAST_TIME ,ONLINE_TIME From v$datafile a, v$tablespace b Where a.TS# = b.TS# And b.NAME ='UNDOTBS'; Tablespace Report With Space MARK set lines 200 pages 200 col TABLESPACE_NAME for a25 col "FREE%" for a10 col "USED%" for a10 col STATUS for a7 Select a.TABLESPACE_NAME ,round(a.BYTES/1073741824,2) AVAIL_GB ,round(b.BYTES/1073741824,2) USED_GB ,round(c.BYTES/1048576,2) FREE_MB ,round((b.BYTES*100)/a.BYTES,2)||'%' "USED%" , round((c.BYTES*100)/a.BYTES,2)||'%' "FREE%" ,NVL2(to_char(a.BYTES),NULL,'OFFLINE') STATUS ,DECODE(sign((TO_NUMBER(c.BYTES)/1048576) -100) ,-1,'<<==== '||c.TABLESPACE_NAME ,NULL) LOW_SPACE_MARK from sm$ts_avail a,sm$ts_used b,sm$ts_free c where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and a.TABLESPACE_NAME=c.TABLESPACE_NAME(+) Order by LOW_SPACE_MARK; Temporary Tablespace (Check separate Post for TEMPORARY Tablespace in this blog) select FILE_NAME ,TABLESPACE_NAME,BYTES/1024/1024/1024 "GB" ,STATUS ,AUTOEXTENSIBLE ,(INCREMENT_BY*8192)/1024/1024 INCREMENT_Mb ,MAXBYTES/1024/1024/1024 "MaxGB" ,USER_BYTES/1024/1024/1024 "UsrGB" from dba_temp_files where TABLESPACE_NAME = 'DST_TEMP'; Check For Auto extendable select tablespace_name, substr(file_name,1,60) file_name, status, bytes, AUTOE XTENSIBLE FROM dba_data_files where tablespace_name ='STATSPACK'; UNDO TABLESPACE (Check separate Post for UNDO Tablespace in this blog) select * from dba_data_files where tablespace_name like '%UNDO%'; select sum(BYTES)/1024/1024/1024 "UNDO DATAFILE In GB" from dba_data_files where tablespace_name ='UNDO'; Unset UNDO Tablespace alter system set undo_tablespace = '';
alter system set undo_tablespace = '' scope=memory; Drop UNDO Tablespace DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS; drop tablespace undo_old including contents; Create/Manage UNDO Tablespace create undo tablespace UNDO datafile '/apps/data/undo02.dbf' size 3072m; alter tablespace UNDO add datafile '/apps/data/undo02.dbf' size 3072m; alter database datafile '/apps/data/undo06.dbf' offline drop; alter database datafile '/apps/data/undo06.dbf' online; recover datafile '/apps/data/undo06.dbf';ime" asc;
TOP 10 SQL Query (SqlArea) TOP 10 SQL Query (SqlArea) Top-N queries provide a method for limiting the number of rows returned from ord ered sets of data. They are extremely useful when you want to return the top or bottom "N" number of rows from a set or when you are paging through data. spool sqlarea.lst set pagesize 66 linesize 180 trimspool on set echo off column executions heading "Execs" format 99999999 column rows_processed heading "Rows Procd" format 99999999 column loads heading "Loads" format 999999.99 column buffer_gets heading "Buffer Gets" column disk_reads heading "Disk Reads" column elapsed_time heading "Elasped Time" format 999999999 column cpu_time heading "CPU Time" format 999999999 column sql_text heading "SQL Text" format a60 wrap column avg_cost heading "Avg Cost" format 99999999 column etime_per_exec heading "ETime Per Exec" format 999999999 column ctime_per_exec heading "CPU Time Per Exec" format 999999999 column gets_per_exec heading "Gets Per Exec" format 99999999 column reads_per_exec heading "Read Per Exec" format 99999999 column rows_per_exec heading "Rows Per Exec" format 99999999 break on report compute sum compute sum compute avg compute avg
of of of of
rows_processed executions avg_cost etime_per_exec
on report on report on report on report
compute compute compute compute
avg avg avg avg
of of of of
ctime_per_exec gets_per_exec reads_per_exec row_per_exec
on report on report on report on report
PROMPT PROMPT Top 10 most expensive SQL (Elapsed Time (secs) )... PROMPT select rownum as rank, a.* from ( select elapsed_Time/1000000 elapsed_time, executions, buffer_gets, disk_reads, cpu_time hash_value, sql_text from v$sqlarea where elapsed_time/1000000 > 5 order by elapsed_time desc) a where rownum < 11 / PROMPT PROMPT Top 10 most expensive SQL (Elapsed Time (secs) Per Exec )... PROMPT select rownum as rank, a.* from ( select elapsed_Time/1000000 elapsed_time, executions, elapsed_Time / (1000000 * decode(executions,0,1, executions) ) etime_per_exec, buffer_gets, disk_reads, cpu_time hash_value, sql_text from v$sqlarea where elapsed_time/1000000 > 5 order by etime_per_exec desc) a where rownum < 11 / PROMPT PROMPT Top 10 most expensive SQL (CPU Time (secs) )... PROMPT col SQL_TEXT for a99 select rownum as rank, a.* from ( select cpu_time/1000000 cpu_time, executions, buffer_gets, disk_reads, cpu_time hash_value, sql_text from v$sqlarea where cpu_time/1000000 > 5 order by cpu_time desc) a where rownum < 11 /
PROMPT PROMPT Top 10 most expensive SQL (CPU Time (secs) per Exec)... PROMPT select rownum as rank, a.* from ( select cpu_time/1000000 cpu_time, executions, cpu_time / (1000000 * decode(executions,0,1, executions)) ctime_per_exec, buffer_gets, disk_reads, cpu_time hash_value, sql_text from v$sqlarea where cpu_time/1000000 > 5 order by ctime_per_exec desc) a where rownum < 11 / PROMPT PROMPT Top 10 most expensive SQL (Buffer Gets)... PROMPT select rownum as rank, a.* from ( select buffer_gets, executions, buffer_gets/ decode(executions,0,1, executions) gets_per_exec, hash_value, sql_text from v$sqlarea where buffer_gets > 50000 order by buffer_gets desc) a where rownum < 11 / PROMPT PROMPT Top 10 most expensive SQL (Buffer Gets by Executions)... PROMPT select rownum as rank, a.* from ( select buffer_gets, executions, buffer_gets/ decode(executions,0,1, executions) gets_per_exec, hash_value, sql_text from v$sqlarea where buffer_gets > 50000 order by gets_per_exec desc) a where rownum < 11 / PROMPT PROMPT Top 10 most expensive SQL (Physical Reads)... PROMPT select rownum as rank, a.* from ( select disk_reads, executions,
disk_reads / decode(executions,0,1, executions) reads_per_exec, hash_value, sql_text from v$sqlarea where disk_reads > 10000 order by disk_reads desc) a where rownum < 11 / PROMPT Top 10 most expensive SQL (Physical Reads by Executions)... PROMPT select rownum as rank, a.* from ( select disk_reads, executions, disk_reads / decode(executions,0,1, executions) reads_per_exec, hash_value, sql_text from v$sqlarea where disk_reads > 10000 order by reads_per_exec desc) a where rownum < 11 / PROMPT Top 10 most expensive SQL (Rows Processed by Executions)... PROMPT select rownum as rank, a.* from ( select rows_processed, executions, rows_processed / decode(executions,0,1, executions) rows_per_exec, hash_value, sql_text from v$sqlarea where rows_processed > 10000 order by rows_per_exec desc) a where rownum < 11 / PROMPT PROMPT Top 10 most expensive SQL (Buffer Gets vs Rows Processed)... PROMPT select rownum as rank, a.* from ( select buffer_gets, lpad(rows_processed || decode(users_opening + users_executing, 0, ' ','*'),20) "rows_processed", executions, loads, (decode(rows_processed,0,1,1)) * buffer_gets/ decode(rows_processed,0,1, rows_processed) avg_cost, sql_text from v$sqlarea where decode(rows_processed,0,1,1) * buffer_gets/ decode(rows_processed,0,1,rows _processed) > 10000 order by 5 desc) a where rownum < 11 / rem Check to see if there are any candidates for procedures or rem for using bind variables. Check this by comparing UPPER
rem rem This May be a candidate application for using the init.ora parameter rem CURSOR_SHARING = FORCE|SIMILAR select rownum as rank, a.* from ( select upper(substr(sql_text, 1, 65)) sqltext, count(*) from v$sqlarea group by upper(substr(sql_text, 1, 65)) having count(*) > 1 order by count(*) desc) a where rownum < 11 / prompt Output spooled to sqlarea.lst spool off