BMC Oracle

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