{"id":606,"date":"2024-09-26T13:02:28","date_gmt":"2024-09-26T13:02:28","guid":{"rendered":"https:\/\/humtechno.com\/intro\/?p=606"},"modified":"2024-09-26T13:03:46","modified_gmt":"2024-09-26T13:03:46","slug":"comprehensive-oracle-database-health-check","status":"publish","type":"post","link":"https:\/\/humtechno.com\/intro\/2024\/09\/26\/comprehensive-oracle-database-health-check\/","title":{"rendered":"Comprehensive Oracle Database Health Check"},"content":{"rendered":"\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Overview<\/strong><\/h2>\n\n<p><a href=\"https:\/\/humtechno.com\/intro\/wp-content\/uploads\/2024\/09\/Oracle-Database-Performance-and-Tuning.pdf\">Download PDF<\/a><\/p>\n\n<p><br>An Oracle database health check involves a thorough review of the database&#8217;s performance, configuration, and security to ensure optimal operation. It typically includes examining system resource usage (CPU, memory, and storage), checking database logs for errors, and verifying backup and recovery processes. Performance metrics such as query response times, index efficiency, and I\/O bottlenecks are analyzed to identify potential issues. A health check also assesses database security by reviewing user access, roles, and privileges. Regular checks help detect early signs of failure or inefficiency, ensuring that the database runs smoothly and securely.<\/p>\n\n<p><br>Oracle database deployment and design are critical to ensuring the system is robust, scalable, and efficient. The deployment process involves installing the database on physical or virtual servers, configuring the storage, memory, and network settings for optimal performance, and setting up failover options like Oracle RAC or Data Guard for high availability.<\/p>\n\n<p><br>Fundamentally, Oracle&#8217;s architecture follows a multi-tiered approach with the instance (memory and background processes), the database (physical storage), and clients (applications) interacting with it. Key components include tablespaces, data files, redo logs, and control files, which manage data and recovery. Security, backup strategies, and efficient indexing are essential in the design phase to meet business continuity, performance, and security requirements.<\/p>\n\n<p><br>The design should also account for scalability, by utilizing Oracle&#8217;s partitioning, clustering, and load balancing features, while maintaining security through role-based access and encryption.<br>Improving Oracle Database performance involves several strategies. First, optimizing SQL queries can significantly reduce execution time. Second, proper indexing can enhance data retrieval speed. Third, regular database maintenance, such as statistics collection and defragmentation, ensures optimal performance. Fourth, using Oracle&#8217;s built-in performance tuning tools, like Automatic Workload Repository (AWR) and SQL Tuning Advisor, helps identify and resolve bottlenecks. Lastly, scaling hardware resources, such as CPU and memory, can provide the necessary power for high-demand operations.<br><\/p>\n\n<p><strong>Below are some major categories involving performing Health check of Oracle Database.<\/strong><\/p>\n\n<ul class=\"wp-block-list\">\n<li>Oracle Database Architecture Health Check<\/li>\n\n\n\n<li>Oracle Database Engine Health Check<\/li>\n\n\n\n<li>Oracle Database Processes Level Health Check<\/li>\n<\/ul>\n\n<p><\/p>\n\n<ol class=\"wp-block-list\"><\/ol>\n\n<h2 class=\"wp-block-heading has-text-align-center\"><strong>Oracle Database Architecture Health Check<\/strong><\/h2>\n\n<p>Oracle Database Architecture Health Check ensures optimal performance, much like reviewing the core components of a computer. The database\u2019s memory (SGA and PGA) must be balanced to handle workloads efficiently. ASM (Automatic Storage Management) is used to manage datafiles for efficient disk I\/O. Critical components like Redo Log files are stored on fast, high-performance storage for quick recovery, while Archive logs are placed on separate storage for point-in-time recovery. Regular backups are stored on tape drives or other media, isolated from the main database storage, ensuring data protection.<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check CPU Utilization<\/strong><\/li>\n<\/ul>\n\n<p>Top<\/p>\n\n<p>cat \/proc\/stat<\/p>\n\n<p>yum install sysstat<br>mpstat<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check Storage I\/O<\/strong><\/li>\n<\/ul>\n\n<p>iostat<\/p>\n\n<p>yum install iotop<br>iotop<\/p>\n\n<p><strong>Using Resource Monitor:<br><\/strong>\u2022 Press Ctrl + Shift + Esc to open Task Manager.<br>\u2022 Go to the Performance tab and click Open Resource Monitor at the bottom.<br>\u2022 Switch to the Disk tab in Resource Monitor.<br>\u2022 Here, you can monitor disk I\/O statistics, including:<br>o Disk Reads\/sec: Number of read operations per second.<br>o Disk Writes\/sec: Number of write operations per second.<br>o Disk Queue Length: The length of the queue of I\/O operations waiting for disk access.<\/p>\n\n<p><strong>Using Performance Monitor (PerfMon):<br><\/strong>\u2022 Press Windows + R, type perfmon, and press Enter.<br>\u2022 In Performance Monitor, add counters for disk-related metrics:<br>o Right-click on Performance Monitor in the left panel.<br>o Click Add Counters.<br>o Choose PhysicalDisk and select counters such as:<br>\uf0a7 Disk Reads\/sec<br>\uf0a7 Disk Writes\/sec<br>\uf0a7 Avg. Disk sec\/Read<br>\uf0a7 Avg. Disk sec\/Write<br>\uf0a7 Disk Queue Length<br>These counters give detailed real-time data about I\/O performance.<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check Memory Utilization<\/strong><\/li>\n<\/ul>\n\n<p><strong><br><\/strong>Free -g<\/p>\n\n<pre class=\"wp-block-code\"><code>cat \/proc\/meminfo<\/code><\/pre>\n\n<p>vmstat 1<\/p>\n\n<p><strong>Using Task Manager:<br><\/strong>\u2022 Press Ctrl + Shift + Esc to open Task Manager.<br>\u2022 Go to the Performance tab, and select Memory on the left.<\/p>\n\n<p><strong>Using Resource Monitor:<br><\/strong>\u2022 Open Task Manager (Ctrl + Shift + Esc), go to the Performance tab, and click on Open Resource Monitor.<br>\u2022 Switch to the Memory tab to get detailed information about memory utilization.<\/p>\n\n<p><strong>Using Performance Monitor (PerfMon):<br><\/strong>\u2022 Press Windows + R, type perfmon, and press Enter.<br>\u2022 In Performance Monitor, you can add memory-related counters to track usage.<br>o Right-click in the window, choose Add Counters.<br>o Select Memory, and choose counters like:<br>\uf0a7 Available MBytes<br>\uf0a7 Committed Bytes<br>\uf0a7 Cache Faults\/sec<br>\uf0a7 Page Faults\/sec<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Redo Log File Should be on Fast Storage<\/strong><\/li>\n<\/ul>\n\n<p><br>dd if=\/dev\/zero of=testfile bs=1G count=1 oflag=direct<\/p>\n\n<p>dd if=testfile of=\/dev\/null bs=1G count=1 iflag=direct<\/p>\n\n<p>winsat disk -drive c<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Backups Should Be Copy External Storage Like Tape Drive or Other Storage.<\/strong><\/li>\n<\/ul>\n\n<p><\/p>\n\n<h2 class=\"wp-block-heading has-text-align-center\">Oracle Database Engine Health Check<\/h2>\n\n<p>An Oracle Database Engine Health Check is essential for maintaining optimal performance and involves monitoring several critical components. The System Global Area (SGA) should be appropriately sized to minimize memory contention, while the Program Global Area (PGA) needs careful management to ensure efficient memory allocation for session data and sorting operations. Additionally, the size of the redo logs plays a significant role in recovery performance, and resizing them can prevent frequent log switches that may hinder throughput. Monitoring log switches is crucial, as frequent occurrences can indicate transaction volume issues, and analyzing wait events provides insights into resource contention, helping identify bottlenecks and optimize the database environment effectively.<\/p>\n\n<p>\u2022 <strong>System Global Area (SGA)<\/strong>: This shared memory area contains data and control information for the Oracle instance, and its size should be tuned to accommodate the workload, minimizing memory contention.<\/p>\n\n<p>\u2022 <strong>Program Global Area (PGA)<\/strong>: This memory region is allocated to each Oracle process, and monitoring its usage helps ensure efficient memory allocation for sorting, hashing, and managing session data.<\/p>\n\n<p>\u2022 <strong>Redo Log Size<\/strong>: The size of the redo log files impacts recovery performance; appropriately sizing these logs can enhance database recovery speed and prevent frequent log switches.<\/p>\n\n<p>\u2022 <strong>Log Switches<\/strong>: Frequent log switches can indicate issues with transaction volume or log size, and monitoring this helps in optimizing the log configuration for better throughput.<\/p>\n\n<p>\u2022 <strong>Wait Events<\/strong>: Analyzing wait events provides insights into resource contention, helping identify bottlenecks and performance issues, which is essential for tuning and optimizing the database environment.<\/p>\n\n<p>\u2022<strong> Shared Server and Dedicated Server Process<\/strong><\/p>\n\n<p><strong>For Configure Shared Server<br><\/strong>select name,value from gv$parameter where name in (&#8216;shared_servers&#8217;,&#8217;dispatchers&#8217;,&#8217;max_shared_servers&#8217;,&#8217;shared_server_sessions&#8217;,&#8217;max_dispatchers&#8217;);<br>SELECT * FROM V$SHARED_SERVER;<br>SELECT * FROM V$DISPATCHER;<br>ALTER SYSTEM SET SHARED_SERVERS = 5;<br>ALTER SYSTEM SET DISPATCHERS = &#8216;(PROTOCOL=TCP)(DISPATCHERS=3)&#8217;;<br>ALTER SYSTEM SET MAX_SHARED_SERVERS = 10;<br>ALTER SYSTEM SET SHARED_SERVER_SESSIONS = 100;<br>ALTER SYSTEM SET MAX_DISPATCHERS = 5;<br>SELECT * FROM V$SHARED_SERVER;<br>SELECT * FROM V$DISPATCHER;<br>\u2022 <strong>Monitor Block Tracking File Size which use for Incremental Backup.<\/strong><\/p>\n\n<p>ALTER DATABASE Disable BLOCK CHANGE TRACKING;<br>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE \u2018\/u01\/blocktracking.ora&#8217;;<br>SELECT filename, status, bytes FROM v$block_change_tracking;<br>\u2022 <strong>Configure Keep_Pool and Move Frequently Used Objects in Keep Pool<\/strong><\/p>\n\n<p>select table_name,BUFFER_POOL from dba_tables where OWNER=&#8217;T24&#8242; AND not BUFFER_POOL=&#8217;DEFAULT&#8217;;<br>alter TABLE T24.F_BATCH storage (buffer_pool keep);<\/p>\n\n<h2 class=\"wp-block-heading has-text-align-center\">Oracle Database Processes Execution Level Health Check<\/h2>\n\n<p>An Oracle Database Execution Level Health Check focuses on monitoring and optimizing SQL query performance and execution plans. It involves analyzing SQL statements using tools like EXPLAIN PLAN, SQL Trace, and TKPROF to identify inefficient queries, missing indexes, or suboptimal joins. The check also reviews bind variable usage, index efficiency, and parallel execution settings to enhance performance. By detecting long-running queries or heavy resource-consuming statements, this health check ensures that database operations are executed optimally, improving overall query performance and resource utilization.<br><strong>Operational Health Check<\/strong><\/p>\n\n<p><\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Archive Log Location Space Monitoring<\/strong><\/li>\n<\/ul>\n\n<p><strong><br><\/strong>SELECT TRUNC (first_time) &#8220;Date&#8221;, inst_id, TO_CHAR (first_time, &#8216;Dy&#8217;) &#8220;Day&#8221;,<br>COUNT (1) &#8220;Total&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;00&#8217;, 1, 0)) &#8220;h0&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;01&#8217;, 1, 0)) &#8220;h1&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;02&#8217;, 1, 0)) &#8220;h2&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;03&#8217;, 1, 0)) &#8220;h3&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;04&#8217;, 1, 0)) &#8220;h4&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;05&#8217;, 1, 0)) &#8220;h5&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;06&#8217;, 1, 0)) &#8220;h6&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;07&#8217;, 1, 0)) &#8220;h7&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;08&#8217;, 1, 0)) &#8220;h8&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;09&#8217;, 1, 0)) &#8220;h9&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;10&#8217;, 1, 0)) &#8220;h10&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;11&#8217;, 1, 0)) &#8220;h11&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;12&#8217;, 1, 0)) &#8220;h12&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;13&#8217;, 1, 0)) &#8220;h13&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;14&#8217;, 1, 0)) &#8220;h14&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;15&#8217;, 1, 0)) &#8220;h15&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;16&#8217;, 1, 0)) &#8220;h16&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;17&#8217;, 1, 0)) &#8220;h17&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;18&#8217;, 1, 0)) &#8220;h18&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;19&#8217;, 1, 0)) &#8220;h19&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;20&#8217;, 1, 0)) &#8220;h20&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;21&#8217;, 1, 0)) &#8220;h21&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;22&#8217;, 1, 0)) &#8220;h22&#8221;,<br>SUM (DECODE (TO_CHAR (first_time, &#8216;hh24&#8242;), &#8217;23&#8217;, 1, 0)) &#8220;h23&#8221;,<br>ROUND (COUNT (1) \/ 24, 2) &#8220;Avg&#8221;<br>FROM gv$log_history<br>WHERE thread# = inst_id<br>AND first_time > sysdate -7<br>GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, &#8216;Dy&#8217;)<br>ORDER BY 1,2;<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Monitor Flash Recover Area<\/strong><\/li>\n<\/ul>\n\n<p><br>select name, floor(space_limit \/ 1024 \/ 1024\/1024) &#8220;Size GB&#8221;, ceil(space_used \/ 1024 \/ 1024\/1024) &#8220;Used GB&#8221; from v$recovery_file_dest order by name;<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check Tablespace Size<\/strong><\/li>\n<\/ul>\n\n<p><br>SELECT ts.tablespace_name,<br>size_info.megs_alloc,<br>size_info.megs_free\/1024,<br>size_info.megs_used,<br>size_info.pct_free,<br>size_info.pct_used,<br>size_info.MAX,<br>size_info.Available \/1024 Available_GB,<br>Available_Percentage<br>FROM (SELECT a.tablespace_name,<br>ROUND (a.bytes_alloc \/ 1024 \/ 1024) megs_alloc,<br>ROUND (NVL (b.bytes_free, 0) \/ 1024 \/ 1024) megs_free,<br>ROUND ( (a.bytes_alloc &#8211; NVL (b.bytes_free, 0)) \/ 1024 \/ 1024)<br>megs_used,<br>ROUND ( (NVL (b.bytes_free, 0) \/ a.bytes_alloc) * 100)<br>Pct_Free,<br>100 &#8211; ROUND ( (NVL (b.bytes_free, 0) \/ a.bytes_alloc) * 100)<br>Pct_used,<br>ROUND (maxbytes \/ 1048576) MAX,<br>((ROUND (maxbytes \/ 1048576))- (ROUND ( (a.bytes_alloc &#8211; NVL (b.bytes_free, 0)) \/ 1024 \/ 1024))) as Available<br>, (((ROUND (maxbytes \/ 1048576))- (ROUND ( (a.bytes_alloc &#8211; NVL (b.bytes_free, 0)) \/ 1024 \/ 1024))) \/(ROUND (maxbytes \/ 1048576)))*100 as Available_Percentage<br>FROM ( SELECT f.tablespace_name,<br>SUM (f.bytes) bytes_alloc,<br>SUM (<br>DECODE (f.autoextensible,<br>&#8216;YES&#8217;, f.maxbytes,<br>&#8216;NO&#8217;, f.bytes))<br>maxbytes<br>FROM dba_data_files f<br>GROUP BY tablespace_name) a,<br>( SELECT f.tablespace_name, SUM (f.bytes) bytes_free<br>FROM dba_free_space f<br>GROUP BY tablespace_name) b<br>WHERE a.tablespace_name = b.tablespace_name(+)<br>UNION ALL<br>SELECT h.tablespace_name,<br>ROUND (SUM (h.bytes_free + h.bytes_used) \/ 1048576) megs_alloc,<br>ROUND (<br>SUM (<br>(h.bytes_free + h.bytes_used) &#8211; NVL (p.bytes_used, 0))<br>\/ 1048576)<br>megs_free,<br>ROUND (SUM (NVL (p.bytes_used, 0)) \/ 1048576) megs_used,<br>ROUND (<br>( SUM (<br>(h.bytes_free + h.bytes_used)<br>&#8211; NVL (p.bytes_used, 0))<br>\/ SUM (h.bytes_used + h.bytes_free))<br>* 100)<br>Pct_Free,<br>100<br>&#8211; ROUND (<br>( SUM (<br>(h.bytes_free + h.bytes_used)<br>&#8211; NVL (p.bytes_used, 0))<br>\/ SUM (h.bytes_used + h.bytes_free))<br>* 100)<br>pct_used,<br>ROUND (<br>SUM (<br>DECODE (f.autoextensible,<br>&#8216;YES&#8217;, f.maxbytes,<br>&#8216;NO&#8217;, f.bytes)<br>\/ 1048576))<br>MAX,<br>(((ROUND (SUM (DECODE (f.autoextensible,&#8217;YES&#8217;, f.maxbytes,&#8217;NO&#8217;, f.bytes) \/ 1048576))) -(ROUND (SUM (NVL (p.bytes_used, 0)) \/ 1048576)))\/1024) as Available_GB<br>,((((ROUND (SUM (DECODE (f.autoextensible,&#8217;YES&#8217;, f.maxbytes,&#8217;NO&#8217;, f.bytes) \/ 1048576))) -(ROUND (SUM (NVL (p.bytes_used, 0)) \/ 1048576)))) \/(ROUND (SUM (DECODE (f.autoextensible,&#8217;YES&#8217;, f.maxbytes,&#8217;NO&#8217;, f.bytes) \/ 1048576)))) * 100 as Available_Percentage<br>FROM sys.v_$TEMP_SPACE_HEADER h,<br>sys.v_$Temp_extent_pool p,<br>dba_temp_files f<br>WHERE p.file_id(+) = h.file_id<br>AND p.tablespace_name(+) = h.tablespace_name<br>AND f.file_id = h.file_id<br>AND f.tablespace_name = h.tablespace_name<br>GROUP BY h.tablespace_name) size_info,<br>sys.dba_tablespaces ts,<br>sys.dba_tablespace_groups tsg<br>WHERE ts.tablespace_name = size_info.tablespace_name<br>AND ts.tablespace_name = tsg.tablespace_name(+)<br>order by Available_Percentage asc;<\/p>\n\n<p><\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Monitor Database Backups<\/strong><\/li>\n<\/ul>\n\n<p>SELECT status status,<br>object_type object_type,<br>input_bytes \/ 1024 \/ 1024 \/ 1024 input_bytes,<br>output_bytes \/ 1024 \/ 1024 \/ 1024 output_bytes,<br>TO_CHAR (start_time, &#8216;mm\/dd\/yyyy:hh:mi:ss&#8217;) AS start_time,<br>TO_CHAR (end_time, &#8216;mm\/dd\/yyyy:hh:mi:ss&#8217;) AS end_time<br>FROM v$rman_status<br>WHERE start_time &gt; SYSDATE &#8211; 1 AND operation = &#8216;BACKUP&#8217;;<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Monitor Database Alert Log<\/strong><\/li>\n<\/ul>\n\n<p class=\"has-text-align-left\"><strong>Performance Related Health Check<\/strong><\/p>\n\n<p><\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check Database Session Locking<\/strong><\/li>\n<\/ul>\n\n<p><strong><br><\/strong>select blocking_session, sid, serial#, wait_class, seconds_in_wait<br>from<br>v$session where blocking_session is not NULL order by blocking_session;<br>select username, sid,serial#,sql_id , wait_time from v$session where (sid ) in<br>(<br>select blocking_session from v$session<br>);<br>select inst_id,last_call_et,username,machine,event,status from gv$session where username=&#8217;T24&#8242; and lower(event) like &#8216;%contention%&#8217;;<br>select * from gv$session a , gv$lock b where a.sid=b.sid and A.INST_ID=b.inst_id and username=&#8217;T24&#8242; and lower(event) like &#8216;%contention%&#8217;;<br>select * from dba_blockers;<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check the Queries which last_call_et column value high and increasing in your Database<\/strong><\/li>\n<\/ul>\n\n<p><br>select event,last_call_et,username,module,program,machine,sql_id,prev_sql_id,<br>((select max(sql_text) from gv$sql a where a.sql_id=s.sql_id)) as sql1,<br>((select max(sql_text) from gv$sql a where a.sql_id=s.prev_sql_id)) as sql2<br>from gv$session s where status=&#8217;ACTIVE&#8217; and not username is null order by last_call_et desc;<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check Active \/ In Active Database Sessions<\/strong><\/li>\n<\/ul>\n\n<p><br>select inst_id,sid,last_call_et,username,machine,event,status from gv$session where username=&#8217;T24&#8242; order by last_call_et desc;<br>select inst_id,sid,last_call_et,username,machine,event,status from gv$session where username=&#8217;T24&#8242; and status=&#8217;ACTIVE&#8217; order by last_call_et desc;<br>Check User Level Session Locking.<br>SELECT se.inst_id,se.last_call_et,<br>lk.SID, se.username, se.OSUser, se.Machine,DECODE (lk.TYPE,&#8217;TX&#8217;, &#8216;Transaction&#8217;,&#8217;TM&#8217;, &#8216;DML&#8217;,&#8217;UL&#8217;, &#8216;PL\/SQL User Lock&#8217;,lk.TYPE)<br>lock_type, DECODE (lk.lmode, 0, &#8216;None&#8217;, 1, &#8216;Null&#8217;,<br>2, &#8216;Row-S (SS)&#8217;,<br>3, &#8216;Row-X (SX)&#8217;,<br>4, &#8216;Share&#8217;,<br>5, &#8216;S\/Row-X (SSX)&#8217;,<br>6, &#8216;xclusive&#8217;,<br>TO_CHAR (lk.lmode))<br>mode_held,<br>DECODE (lk.request,<br>0, &#8216;None&#8217;,<br>1, &#8216;Null&#8217;,<br>2, &#8216;Row-S (SS)&#8217;,<br>3, &#8216;Row-X (SX)&#8217;,<br>4, &#8216;Share&#8217;,<br>5, &#8216;S\/Row-X (SSX)&#8217;,<br>6, &#8216;Exclusive&#8217;,<br>TO_CHAR (lk.request))<br>mode_requested,<br>TO_CHAR (lk.id1) lock_id1,<br>TO_CHAR (lk.id2) lock_id2,<br>ob.owner,<br>ob.object_type,<br>ob.object_name,<br>DECODE (lk.Block, 0, &#8216;No&#8217;, 1, &#8216;Yes&#8217;, 2, &#8216;Global&#8217;) block,<br>se.lockwait<br>FROM GV$lock lk, dba_objects ob, GV$session se<br>WHERE lk.TYPE IN (&#8216;TX&#8217;, &#8216;TM&#8217;, &#8216;UL&#8217;)<br>AND lk.SID = se.SID<br>AND lk.id1 = ob.object_id(+)<br>AND lk.inst_id = se.inst_id<br>and last_call_et > 100;<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Generate AWR Report<\/strong><\/li>\n\n\n\n<li><strong>Check &#8220;DB Time&#8221;<\/strong><\/li>\n<\/ul>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check &#8220;SQL ordered by Elapsed Time&#8221;<\/strong><\/li>\n<\/ul>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check Top 10 Foreground Events by Total Wait Time<\/strong><\/li>\n<\/ul>\n\n<ul class=\"wp-block-list\">\n<li><strong>Wait Classes by Total Wait Time<\/strong><\/li>\n<\/ul>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check Table Stats<\/strong><\/li>\n<\/ul>\n\n<p><br>select max(LAST_ANALYZED),&#8217;execute dbms_stats.gather_table_stats(ownname=>&#8217; || &#8221;&#8221; || &#8216;t24&#8217; || &#8221;&#8221; || &#8216;,&#8217; || &#8216;tabname=>&#8217; || &#8221;&#8221; || table_name || &#8221;&#8221; || &#8216;,DEGREE=> 10, estimate_percent=> 10 );&#8217; from dba_tab_statistics where owner=&#8217;T24&#8242; and stale_stats =&#8217;YES&#8217;<br>group by table_name order by max(LAST_ANALYZED) asc;<br>select max(LAST_ANALYZED),&#8217;execute dbms_stats.gather_table_stats(ownname=>&#8217; || &#8221;&#8221; || &#8216;t24&#8217; || &#8221;&#8221; || &#8216;,&#8217; || &#8216;tabname=>&#8217; || &#8221;&#8221; || table_name || &#8221;&#8221; || &#8216;,DEGREE=> 10, estimate_percent=> 10 );&#8217; from dba_tab_statistics where owner=&#8217;T24&#8242; and stale_stats =&#8217;YES&#8217; group by table_name order by max(LAST_ANALYZED) asc;<br>select owner,table_name,LAST_ANALYZED from dba_tables;<br>Check Invalid Objects<br>select * from dba_ind_partitions where index_owner=&#8217;T24&#8242; and not status=&#8217;USABLE&#8217; ;<br>select index_name,status from dba_indexes where not status=&#8217;VALID&#8217; and owner=&#8217;T24&#8242;;<br>Check Execution Plan for Query<br>explain plan for insert into test1 select * from test1;<br>select * from table(dbms_xplan.display);<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check Usage of Indexes<\/strong><\/li>\n<\/ul>\n\n<p>ALTER INDEX abc1 MONITORING USAGE;<br>SELECT index_name, table_name, monitoring, used FROM V$OBJECT_USAGE;<br>ALTER INDEX abc1 NO MONITORING USAGE;<\/p>\n\n<ul class=\"wp-block-list\">\n<li><strong>Check List of Queries Perform Full Scan<\/strong><\/li>\n<\/ul>\n\n<p>SELECT sql_id,<br>child_number,<br>object_owner,<br>object_name,<br>operation,<br>options<br>FROM v$sql_plan<br>WHERE operation = &#8216;TABLE ACCESS&#8217;<br>AND options = &#8216;FULL&#8217; ;<\/p>\n\n<p><\/p>\n\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":2,"featured_media":333,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"pagelayer_contact_templates":[],"_pagelayer_content":"","footnotes":""},"categories":[3],"tags":[91,89,90],"class_list":["post-606","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-database-health-check","tag-oracle-health-check","tag-system-health-check"],"_links":{"self":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/606","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/comments?post=606"}],"version-history":[{"count":5,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/606\/revisions"}],"predecessor-version":[{"id":612,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/606\/revisions\/612"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/media\/333"}],"wp:attachment":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/media?parent=606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/categories?post=606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/tags?post=606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}