BMC Oracle
Jump to navigation
Jump to search
Performance tuning of an BMC using Oracle 11 database
1. archive auditlogs 2. identify tables with high number of rows 3. identify high insert/delete tables
- clean up - drop indexes
4. shrink tables 5. rebuilding indexes 6. change lob to inline
-- Size in MB and num_rows
SELECT alt.table_name, ars.viewname, alt.num_rows, round((alt.num_rows*alt.avg_row_len)/(1024*1024)) MB, alt.LAST_ANALYZED
FROM all_tables alt
LEFT JOIN ARSCHEMA ars
ON to_char(ars.schemaid) = LTRIM(alt.table_name, 'THB')
WHERE num_rows > 0
ORDER BY MB DESC;
SELECT s.segment_name, s.blocks, s.bytes, s.segment_type, ars.viewname
FROM user_segments s
LEFT JOIN ARSCHEMA ars
ON to_char(ars.schemaid) = LTRIM(s.segment_name, 'THB')
ORDER BY bytes DESC;
EXCEL
=VLOOKUP(VALUE(RIGHT(B2; LEN(B2)-1));ARSCHEMA!$A$1:$B$4000;2;FALSE)
[<999999]#,##0." KB ";[<999999999]#,##0.." MB";#,##0..." GB"
SELECT s.segment_name, s.blocks, s.bytes, s.segment_type,
CASE s.segment_type
WHEN 'TABLE' THEN s.segment_name
WHEN 'INDEX' THEN (SELECT table_name FROM user_indexes WHERE index_name = s.segment_name)
WHEN 'LOBINDEX' THEN (SELECT table_name FROM user_indexes WHERE index_name = s.segment_name)
WHEN 'LOBSEGMENT' THEN (SELECT l.table_name FROM user_lobs l WHERE l.segment_name = s.segment_name)
END AS table_name,
CASE s.segment_type
WHEN 'TABLE' THEN
CASE SUBSTR(s.segment_name,1,4)
WHEN 'MLOG' THEN SUBSTR(s.segment_name,7)
ELSE LTRIM(s.segment_name,'TBH')
END
WHEN 'INDEX' THEN LTRIM((SELECT table_name FROM user_indexes WHERE index_name = s.segment_name),'TBH')
WHEN 'LOBINDEX' THEN LTRIM((SELECT table_name FROM user_indexes WHERE index_name = s.segment_name),'TBH')
WHEN 'LOBSEGMENT' THEN REGEXP_REPLACE((SELECT LTRIM(l.table_name, 'TBH') FROM user_lobs l WHERE l.segment_name = s.segment_name),'^(\d+)C.*','\1')
WHEN 'LOBINDEX' THEN null
ELSE s.segment_type
END AS scid,
CASE s.segment_type
WHEN 'INDEX' THEN
CASE SUBSTR(s.segment_name,1,2)
WHEN 'IT' THEN '1'
WHEN 'IB' THEN '1'
WHEN 'IH' THEN '1'
ELSE REGEXP_REPLACE(s.segment_name, 'I[0-9]*_([0-9]*)_[0-9]*','\1')
END
WHEN 'LOBSEGMENT' THEN (SELECT SUBSTR(l.column_name,2) FROM user_lobs l WHERE l.segment_name = s.segment_name)
ELSE null
END as columnid,
CASE s.segment_type
WHEN 'TABLE' THEN (SELECT num_rows FROM user_tables WHERE table_name = s.segment_name)
WHEN 'INDEX' THEN (SELECT num_rows FROM user_indexes WHERE index_name = s.segment_name)
END AS num_rows,
CASE s.segment_type
WHEN 'TABLE' THEN (SELECT last_analyzed FROM user_tables WHERE table_name = s.segment_name)
WHEN 'INDEX' THEN (SELECT last_analyzed FROM user_indexes WHERE index_name = s.segment_name)
END AS last_analyzed
FROM user_segments s
ORDER BY bytes DESC;
=== BMC Tables
-- arschema
SELECT VIEWNAME FROM ARSCHEMA WHERE SCHEMAID='1397';
SELECT SCHEMAID, VIEWNAME, SCHEMATYPE FROM ARSCHEMA;
SELECT SCHEMAID, VIEWNAME, SCHEMATYPE FROM SCHEMA_INDEX;
SELECT uic.index_name, uic.table_name, uic.column_name, uic.column_position, ui.last_analyzed, ui.uniqueness FROM user_ind_columns uic INNER JOIN user_indexes ui ON uic.index_name = ui.index_name WHERE uic.table_name like 'T%' AND ui.index_name not like 'IT%'
SELECT indexname, schemaid, listindex, numfields, uniqueflag, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14, f15, f16 FROM schema_index
SELECT schemaid, name, schematype, numfields, viewname FROM arschema
select status from user_indexes where status = 'UNUSABLE';
=== TABLE SPACE ===
-- ASM Data files
SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'ARSYS';
SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME like 'ARSYS%';
CREATE TABLESPACE ARSYS_HOTZONE
GRANT UNLIMITED TABLESPACE TO aradmin
-- Move Tables
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:47812348053
ALTER TABLE T1100 MOVE TABLESPACE HOTZONE
ALTER INDEX I1100_179_1 REBUILD
-- All tables, views, indexes
SELECT OBJECT_NAME,OBJECT_TYPE,OWNER FROM all_objects WHERE owner = 'ARADMIN' AND OBJECT_TYPE IN ('TABLE','VIEW','INDEX')
-- Invalid Views
SELECT 'ALTER VIEW ' || object_name || ' COMPILE;' FROM all_objects WHERE owner = 'ARADMIN' AND object_type IN ('VIEW') AND status = 'INVALID'
ALTER VIEW AR_SYSTEM_EMAIL_MESSAGES COMPILE;
-- Shrink Table
alter table T223 enable row movement;
alter table T223 shrink space compact;
alter table T223 disable row movement;
ALTER INDEX I223_% REBUILD NOLOGGING
=== Size ===
SELECT TABLE_NAME, NUM_ROWS FROM TABS WHERE NUM_ROWS > 0 ORDER BY NUM_ROWS DESC;
SELECT s.segment_name, s.blocks, s.bytes, s.segment_type FROM user_segments s ORDER BY bytes DESC;
-- Size in MB and num_rows
SELECT table_name, num_rows, round((num_rows*avg_row_len)/(1024*1024)) MB, LAST_ANALYZED
FROM all_tables
WHERE num_rows > 0 -- Ignore empty Tables.
ORDER BY mb desc
-- LOBS related to Table and Column
SELECT s.segment_name, l.table_name, l.column_name, s.bytes
FROM user_segments s, user_lobs l
WHERE l.segment_name = s.segment_name
ORDER BY s.bytes desc;
-- Chained Rows
SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;
ANALYZE TABLE tablename LIST CHAINED ROWS;
=== INDEXES ===
INDEXES help oracle to find data in a WHERE clause quickly, they do need to be maintained in UPDATE/DELETE statements
SELECT T1157.C1 FROM T1157 WHERE (T1157.C10000901 IS NULL) ORDER BY C1;
CREATE INDEX I1146_179_2 ON aradmin.T1146 (C179,C1);
-- Index Size
SELECT us.segment_name, ui.table_name, ui.uniqueness, ui.num_rows, us.bytes, ui.last_analyzed, blevel, degree, ui.status FROM user_segments us, user_indexes ui WHERE us.segment_name = ui.index_name AND ui.index_name NOT LIKE 'SYS_%'
-- Prepare index rebuilt
SELECT 'ALTER INDEX ' || index_name || ' rebuild compute statistics;' FROM user_indexes where table_name like '%1148' AND index_name not like 'SYS_%'
ALTER INDEX IT1148 rebuild compute statistics;
SELECT index_name,table_name,uniqueness,blevel,degree,num_rows,status from user_indexes where table_name like '%1148' AND index_name not like 'SYS_%'
-- Analyze indexes
-- USE dbms_stats instead ... SELECT 'ANALYZE INDEX ' || OBJECT_NAME || ' COMPUTE STATISTICS;' FROM all_objects WHERE owner = 'ARADMIN' AND OBJECT_TYPE = 'INDEX';
select 'exec dbms_stats.gather_index_stats(ownname=> ||owner||, indname=> || OBJECT_NAME ||);' FROM all_objects WHERE owner = 'ARADMIN' AND OBJECT_TYPE = 'INDEX';
SELECT 'ANALYZE INDEX ' || OBJECT_NAME || ' VALIDATE STRUCTURE;' FROM all_objects WHERE owner = 'ARADMIN' AND OBJECT_TYPE = 'INDEX';
-- Indexes gone bad
SELECT name,height,(del_lf_rows/lf_rows)*100 as del_ratio FROM INDEX_STATS WHERE height > 4 OR (del_lf_rows/lf_rows)*100 > 20;
SELECT index_name,table_name,uniqueness,blevel,degree,num_rows,status from user_indexes where (table_name like 'T%' OR table_name like 'H%') AND index_name not like 'SYS_%' and BLEVEL > 2
-- Rebuilding indexes
ALTER INDEX I776_C303503800_1 REBUILD NOLOGGING
SELECT 'ALTER INDEX ' || index_name || ' REBUILD NOLOGGING ONLINE COMPUTE STATISTICS;' from user_indexes where (table_name like 'T%' OR table_name like 'H%') AND index_name not like 'SYS_%' and BLEVEL > 2
select table_name, num_rows, blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows",round((blocks*8),2)||'kb' "used size", round((num_rows*avg_row_len/1024),2)||'kb' "max size" from user_tables where table_name = 'T1148';
-- Index Monitoring
SELECT 'alter index '||index_name||' monitoring usage;' from user_indexes where index_type = 'NORMAL' and table_name IN ('T223','T1397','T2366','T2352','T1148','T1146','T2448','T2351','T776','T2305','T2020','T2546');
ALTER INDEX I148_40000_1 MONITORING USAGE;
ALTER INDEX I148_40000_1 NOMONITORING USAGE;
SELECT table_name, index_name, used FROM v$object_usage WHERE used = 'YES';
SELECT table_name, index_name, used FROM v$object_usage WHERE used = 'NO';
SELECT index_name,table_name,uniqueness,blevel,degree,num_rows,status from user_indexes where index_name IN (SELECT index_name FROM v$object_usage WHERE used = 'YES')
SELECT index_name,table_name,uniqueness,blevel,degree,num_rows,status from user_indexes where index_name IN (SELECT index_name FROM v$object_usage WHERE used = 'NO')
-- EXPLAIN PLAN
SELECT DISTINCT(SQL_ID) FROM V$SQL_PLAN WHERE ORDER BY cpu_cost DESC
SELECT distinct(sql_id), count(sql_id) amount, io_cost FROM V$SQL_PLAN WHERE OPERATION = 'TABLE ACCESS' AND OPTIONS = 'FULL' GROUP BY sql_id, io_cost ORDER BY (io_cost * amount) DESC NULLS LAST
-- blocking sessions
SELECT blocking_session, sid, serial#, wait_class, seconds_in_wait FROM v$session WHERE blocking_session is not NULL order by blocking_session;
SELECT sid, sql_text FROM v$session s, v$sql q WHERE sid in
(SELECT sid FROM v$session WHERE state in ('WAITING') and wait_class != 'Idle' and event='enq: TX - row lock contention' AND (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));
--history of sessions that block other sessions
select * from (
SELECT a.sql_id ,
a.sample_time ,
ROW_NUMBER() OVER (PARTITION BY a.blocking_session,a.user_id ,a.program
order by blocking_session,a.user_id ,a.program ) rn,
a.blocking_session,a.user_id ,a.program, s.sql_text
FROM sys.WRH$_ACTIVE_SESSION_HISTORY a ,sys.wrh$_sqltext s
where a.sql_id=s.sql_id
and blocking_session_serial# <> 0
and a.user_id <> 0
) where rn = 1
SELECT username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global') STATUS,
DECODE(v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', TO_CHAR(lmode)
) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id;
-- Full Table Scans
SELECT
p.object_owner, p.object_name, t.num_rows, ltrim(t.cache) ch, decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K, s.blocks, sum(a.executions) nbr_FTS, p.sql_id, a.sql_text
FROM
dba_tables t, dba_segments s, v$sqlarea a,
(SELECT distinct address, object_owner, sql_id, object_name FROM v$sql_plan WHERE operation = 'TABLE ACCESS' AND options = 'FULL' AND object_name not like 'MLOG%') p
WHERE
a.address = p.address
AND t.owner = s.owner
AND t.table_name = s.segment_name
AND t.table_name = p.object_name
AND t.owner = p.object_owner
AND t.owner not in ('SYS','SYSTEM')
HAVING
sum(a.executions) > 1
GROUP BY
p.object_owner, p.object_name, t.num_rows, t.cache, t.buffer_pool, s.blocks, p.sql_id, a.sql_text
order by
s.blocks desc, sum(a.executions) desc;
-- activity per session
SELECT a.SID, a.status, a.last_call_et, A.SID, A.username, C.piece, c.sql_text sqltext FROM v$session a, v$sqltext c WHERE a.sql_address = c.address(+);
=== OPEN CURSORS ===
-- open cursor
select max(a.value) as highest_open_cur, p.value as max_open_cur from v$sesstat a, v$statname b, v$parameter p where a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;
-- which session portal is using for open cursors?
select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' AND s.username = 'LPORTAL';
=== What keeps the database busy? ===
-- execution time v.s. disk io
SELECT to_char( to_date( round(ELAPSED_TIME/1000000, 0), 'sssss'), 'hh24:mi:ss') as TIME,
SQL_ID,
Executions,
DISK_READS,
DIRECT_WRITES,
to_char(LAST_ACTIVE_TIME, 'YYYY-MON-DD-HH24:MI:SS') as "Last Active Time",
BUFFER_GETS,
CPU_TIME,
floor(round(sum((Executions * ELAPSED_TIME)/1000000), 0)/86400) || 'd ' ||
to_char(to_date(mod(round(sum((Executions * ELAPSED_TIME)/1000000), 0),86400), 'sssssss'), 'hh24"h" mi"m" ss"s"') as "total_time",
sql_text
from v$sql
WHERE ELAPSED_TIME >= 10000000 --this is 10 seconds
GROUP BY ELAPSED_TIME, SQL_ID, Executions, DISK_READS, DIRECT_WRITES, LAST_ACTIVE_TIME, BUFFER_GETS, CPU_TIME, sql_text
ORDER BY 9 DESC
SELECT SQL_ID, SQL_TEXT, Executions, BUFFER_GETS, DISK_READS, DIRECT_WRITES, to_char(LAST_ACTIVE_TIME, 'YYYY-MON-DD-HH24:MI:SS'), CPU_TIME FROM v$sql
SELECT MIN(LAST_ACTIVE_TIME) FROM v$sql
SELECT SQL_TEXT FROM v$sql WHERE LAST_ACTIVE_TIME < '28-MAY-14'
-- find SQL by ID
SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID = '7pf3kz41synpu';
-- bind value
select child_address, name, value_string FROM v$sql_bind_capture WHERE sql_id='44v0t18cz509t'
-- Find entries that were last updated more than 13 months ago.
DELETE FROM T2354 WHERE C6 < (sysdate - to_date('1-1-1970 00:00:00','MM-DD-YYYY HH24:Mi:SS') - 396)*86400
DELETE FROM T1148 WHERE C6 < (sysdate - to_date('1-1-1970 00:00:00','MM-DD-YYYY HH24:Mi:SS') - 396)*86400
=== Deadlocks ===
SELECT blocking_session, sid, serial#, wait_class, seconds_in_wait FROM v$session WHERE sid = 229
SELECT sid, sql_text FROM v$session s, v$sql q WHERE sid in (SELECT sid FROM v$session WHERE blocking_session is not NULL)
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
alter system kill session '229,2159' immediate;
-- Major Cleanup
SELECT COUNT(C6) FROM T1397 WHERE C6 < (sysdate - to_date('1-1-1970 00:00:00','MM-DD-YYYY HH24:Mi:SS') - 396)*86400;
DELETE FROM T1397 WHERE C6 < (sysdate - to_date('1-1-1970 00:00:00','MM-DD-YYYY HH24:Mi:SS') - 396)*86400;
COMMIT
alter table T1397 enable row movement;
alter table T1397 shrink space compact;
alter table T1397 disable row movement;
EXEC DBMS_STATS.GATHER_TABLE_STATS ('ARADMIN', 'T1397');
--
EXEC DBMS_STATS.gather_table_stats('SMAPP', 'SIM');
EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS ('DWH');
EXEC DBMS_STATS.GATHER_TABLE_STATS ('DWH', 'DS_PARTB');
EXEC DBMS_STATS.GATHER_TABLE_STATS ('DWH', 'DS_PARTB');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'DWH', cascade=>TRUE);
-- AWR
SELECT snap_id,
snap_level,
begin_interval_time
FROM
dba_hist_snapshot
WHERE begin_interval_time like '25-SEP-14%'
ORDER BY begin_interval_time;
70594
70630
instance: 1
db: 3096395807
@awrrpt.sql
/u01/app/oracle/product/11.1/db/rdbms/admin