{"id":406,"date":"2024-07-28T03:31:38","date_gmt":"2024-07-28T03:31:38","guid":{"rendered":"https:\/\/humtechno.com\/intro\/?p=406"},"modified":"2024-07-28T03:32:39","modified_gmt":"2024-07-28T03:32:39","slug":"oracle-database-performance-monitoring","status":"publish","type":"post","link":"https:\/\/humtechno.com\/intro\/2024\/07\/28\/oracle-database-performance-monitoring\/","title":{"rendered":"Oracle Database Performance Monitoring"},"content":{"rendered":"\n\n\n\n

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’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.<\/p>\n\n

Check Database Session Locking
<\/mark><\/strong>select\u00a0 blocking_session, sid, serial#, wait_class,\u00a0\u00a0 seconds_in_wait\u000bfrom\u000b\u00a0\u00a0 v$session where blocking_session is not NULL order by blocking_session;
select username, sid,serial#,sql_id , wait_time from v$session where (sid ) in
(
select blocking_session from v$session
);
select inst_id,last_call_et,username,machine,event,status from gv$session where username=’T24′ and lower(event) like ‘%contention%’;
select * from gv$session a , gv$lock b where a.sid=b.sid and A.INST_ID=b.inst_id and username=’T24′ and lower(event) like ‘%contention%’;<\/p>\n\n

select * from dba_blockers;<\/p>\n\n

Check the Queries which last_call_et column value high and increasing in your Database<\/mark><\/strong><\/p>\n\n

select event,last_call_et,username,module,program,machine,sql_id,prev_sql_id,
((select max(sql_text) from gv$sql a where a.sql_id=s.sql_id)) as sql1,
((select max(sql_text) from gv$sql a where a.sql_id=s.prev_sql_id)) as sql2
from gv$session s where status=’ACTIVE’ and not username is null order by last_call_et desc;<\/p>\n\n

Check Active \/ In Active Database Sessions<\/mark><\/strong><\/p>\n\n

select inst_id,sid,last_call_et,username,machine,event,status from gv$session where username=’T24′ order by last_call_et desc;
select inst_id,sid,last_call_et,username,machine,event,status from gv$session where username=’T24′ and status=’ACTIVE’ order by last_call_et desc;<\/p>\n\n

Check User Level Session Locking.<\/mark><\/strong><\/p>\n\n

SELECT se.inst_id,se.last_call_et,
lk.SID, se.username, se.OSUser, se.Machine,DECODE (lk.TYPE,’TX’, ‘Transaction’,’TM’, ‘DML’,’UL’, ‘PL\/SQL User Lock’,lk.TYPE)
lock_type, DECODE (lk.lmode, 0, ‘None’, 1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S\/Row-X (SSX)’,
6, ‘xclusive’,
TO_CHAR (lk.lmode))
mode_held,
DECODE (lk.request,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S\/Row-X (SSX)’,
6, ‘Exclusive’,
TO_CHAR (lk.request))
mode_requested,
TO_CHAR (lk.id1) lock_id1,
TO_CHAR (lk.id2) lock_id2,
ob.owner,
ob.object_type,
ob.object_name,
DECODE (lk.Block, 0, ‘No’, 1, ‘Yes’, 2, ‘Global’) block,
se.lockwait
FROM GV$lock lk, dba_objects ob, GV$session se
WHERE lk.TYPE IN (‘TX’, ‘TM’, ‘UL’)
AND lk.SID = se.SID
AND lk.id1 = ob.object_id(+)
AND lk.inst_id = se.inst_id
and last_call_et > 100;<\/p>\n\n

Generate AWR Report
<\/mark><\/strong>Check “DB Time”<\/p>\n\n

\"\"<\/figure>\n\n


Check “SQL ordered by Elapsed Time”<\/mark><\/p>\n\n

\"\"<\/figure>\n\n


Check Top 10 Foreground Events by Total Wait Time
<\/mark><\/p>\n\n

\"\"<\/figure>\n\n

Wait Classes by Total Wait Time<\/mark><\/p>\n\n

\"\"<\/figure>\n\n

Check Table Stats<\/mark><\/strong><\/p>\n\n

select max(LAST_ANALYZED),’execute dbms_stats.gather_table_stats(ownname=>’ || ”” || ‘t24’ || ”” || ‘,’ || ‘tabname=>’ || ”” || table_name || ”” || ‘,DEGREE=> 10, estimate_percent=> 10 );’ from dba_tab_statistics where owner=’T24′ and stale_stats =’YES’
group by table_name order by max(LAST_ANALYZED) asc;
select max(LAST_ANALYZED),’execute dbms_stats.gather_table_stats(ownname=>’ || ”” || ‘t24’ || ”” || ‘,’ || ‘tabname=>’ || ”” || table_name || ”” || ‘,DEGREE=> 10, estimate_percent=> 10 );’ from dba_tab_statistics where owner=’T24′ and stale_stats =’YES’ group by table_name order by max(LAST_ANALYZED) asc;<\/p>\n\n

select owner,table_name,LAST_ANALYZED from dba_tables;<\/p>\n\n

Check Invalid Objects<\/mark><\/strong><\/p>\n\n

select * from dba_ind_partitions where index_owner=’T24′ and not status=’USABLE’ ;
select index_name,status from dba_indexes where not status=’VALID’ and owner=’T24′;<\/p>\n\n

Move Objects in Keep Pool<\/p>\n\n

select table_name,BUFFER_POOL from dba_tables where OWNER=’T24′ AND not BUFFER_POOL=’DEFAULT’;<\/p>\n\n

alter TABLE T24.F_BATCH storage (buffer_pool keep);<\/p>\n\n

Increase INITRANS and PCT Free as DML <\/mark><\/strong><\/p>\n\n

ALTER TABLE t24.F_LOCKING INITRANS 100 PCTUSED 0 PCTFREE 60;<\/p>\n\n

Check Execution Plan for Query<\/mark><\/strong><\/p>\n\n

explain plan for insert into test1 select * from test1;\nselect * from table(dbms_xplan.display);\n\nCreate Partitions on Tables on Index\n<\/mark><\/strong><\/code><\/pre>\n\n
<\/p>\n\n\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":333,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"pagelayer_contact_templates":[],"_pagelayer_content":"","footnotes":""},"categories":[3],"tags":[44,43],"class_list":["post-406","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-database-performance","tag-oracle-database-performance"],"_links":{"self":[{"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/406","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\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/comments?post=406"}],"version-history":[{"count":3,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/406\/revisions"}],"predecessor-version":[{"id":413,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/posts\/406\/revisions\/413"}],"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=406"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/categories?post=406"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/humtechno.com\/intro\/wp-json\/wp\/v2\/tags?post=406"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}