[오라클 튜닝] sql 튜닝

trace 확인 sql
--------------------------------------------------------------------------------
conn / as sysdba
show parameter user_dump_dest
grant alter session to scott;

conn scott/tiger
alter session set sql_trace=true;
select * from dept;
!
cd [user_dump_dest]
ex ) cd /app/oracle/diag/rdbms/testdb/testdb/trace

tkprof testdb_ora_28098.trc testdb_ora_28098.tkf explain=scott/tiger sys=no
tail -100 testdb_ora_28098.tkf
exit
alter session set sql_trace=false;
--------------------------------------------------------------------------------

* 특정 SQL Parsing 결과 확인
 => 사용자가 실행한 SQL 문의 파싱 결과를 공유풀 영역에서 참조
--------------------------------------------------------------------------------
set echo off feedback off pagesize 24
break on sql_text skip 1

col sql_text format a40 work heading sql_text
col loads format 9999 heading loads
col version_count format 9999999 heading version|count
col invalidations format 999999 heading invali|dations
col parse_calls format 9999 heading parse|calls
col sorts format 9999 heading loads
col loads format 9999 heading sorts

select sql_text, version_count, loads, invalidations, parse_calls, sorts
from v$sqlarea
where sql_text like '%from dept%'
order by sql_text
/

set feedback on pagesize 24
--------------------------------------------------------------------------------

* Analyze 및 통계 확인
vi analyze_scott_table.sql
--------------------------------------------------------------------------------
set verify off
set echo off
set feedback off
set heading off
set pagesize 0

/
-- Analyze 테이블
spool analyze.sql
select 'analyze table ', owner || '.' || table_name, ' compute statistics;'
from dba_tables
where owner = 'SCOTT';
spool off
prompt '>Table Analyzing...'
@analyze
--
-- Analyze 인덱스
spool analyze.sql
select 'analyze index ', owner || '.' || index_name, ' compute statistics;'
from dba_indexes
where owner = 'SCOTT';
spool off
prompt '>Index Analyzing...'
@analyze
/
--
set verify on
set echo on
set feedback on
set heading on
--------------------------------------------------------------------------------
vi stats.sql
--------------------------------------------------------------------------------
-- 특정 사용자의 모든 테이블 컬럼 구조와 데이터의 유형을 분석
-- 스크립트 실행전 반드시 analyze_scott_table.sql 실행
set feed off echo off pages 100
set linesize 200
col cluster_name format a11 headi 'Cluster'
col table_name format a10 headi 'Table'
col column_id noprint
col row_type noprint
col num_rows format 999990 headi 'Rows'
col blocks format 99990 headi 'Blocks'
col empty_blocks format 99990 headi 'Empty|Blocks'
col avg_row_len format 999990 headi 'Ave Row|Length'
col pct_free format 90 headi 'Pct|Free'
col pct_used format 90 headi 'Pct|Used'
col chain_cnt format 90 headi 'Chain|Count'
col column_name format a15 headi 'Column'
col num_distinct format 9999990 headi 'Distinct|Value'
col index_name format a10 headi 'Index'

break on cluster_name skip 1 on table_name skip 1 on num_rows on blocks on empty_blocks on avg_row_len on row_type on pct_free on pct_used on chain_cnt skip 1

spool stats.dat
select decode(clu.cluster_name, null, 'Unclustered', clu.cluster_name) cluster_name,
 t.table_name, 'C' row_type, col.column_id, t.num_rows, t.blocks, t.empty_blocks,
 t.avg_row_len, t.pct_free, t.pct_used, t.chain_cnt, col.column_name, col.num_distinct
from user_tables t, user_tab_columns col, user_clusters clu
where t.num_rows is not null
and clu.cluster_name (+)= t.cluster_name and t.table_name = col.table_name
and t.table_name <> 'PLAN_TABLE'
/
spool off
set feed on echo on
--------------------------------------------------------------------------------

* Index 구조의 상태 수집, 분석
vi index_desc_scott.sql
--------------------------------------------------------------------------------
set heading on
set pagesize 66
set linesize 500
--
ttitle 'Index Information'
col tab_name format a10 headi 'Table'
col num_rows format 99999 headi '# Rows'
col ind_name format a15 headi 'Index'
col tablespace_name format a10 headi 'Tablespace'
col uniqueness format a1 headi 'U'
col col_name format a12 headi 'Column'
col pct_free format 99 headi 'Pct|Free'
col clustering_factor format 99990 headi 'Cluster|Factor'
col blevel format 99 headi 'BLevel'
col leaf_blocks format 9999 headi 'Leaf'
col num_distinct format 99999 headi '# Dist'
col balance format 990
break on tab_name skip 1 on ind_name on uniqueness on num_rows

spool tblidx.lst

select x.table_name tab_name, x.num_rows num_rows, a.index_name ind_name, a.tablespace_name,
 decode(a.uniqueness, 'UNIQUE', 'U', 'N') uniqueness,
 b.column_name col_name, y.num_distinct num_distinct,
 a.pct_free, a.clustering_factor, a.blevel, a.leaf_blocks,
 (c.del_lf_rows_len/c.lf_rows_len)*100 as balance
from dba_tables x, dba_tab_columns y, dba_indexes a, dba_ind_columns b, index_stats c
where x.owner = 'SCOTT' and a.table_owner = x.owner and a.table_owner = b.index_owner
and x.table_name = a.table_name and a.table_name = b.table_name and a.index_name = b.index_name
and x.owner = y.owner and x.table_name = y.table_name and b.column_name = y.column_name
and b.index_name = c.name(+)
order by x.table_name
/
spool off
--------------------------------------------------------------------------------

* STATSPACK 패키지 사용

1) timed_statistics true인지 체크
show parameter timed_statistics
false이면
alter system set timed_statistics = true;

2) 패키지 생성
@$ORACLE_HOME/rdbms/admin/spcreate

3) 패키지 실행
- perfstat 연결
conn perfstat/perfstat
- 수집 시작
execute statspack.snap
- Snap ID 확인
SELECT snap_id, snap_time FROM STATS$SNAPSHOT;
- 통계 확인
@$ORACLE_HOME/rdbms/admin/spreport

* SGA 영역 현재 상태 확인
select * from v$sgastat
where pool = 'shared pool'
and name like 'free%';

* 대기 현상 확인
col name format a30
select * from v$sysstat
where name in ('db block gets', 'consistent gets', 'physical reads');

* 파일 I/O 확인
select file#, phyrds, phywrts, phyblkwrt, phyblkwrt, singleblkrds, readtim
from v$filestat;

* alert 파일 확인
show parameter background_dump_dest
!tail -100 /app/oracle/diag/rdbms/testdb/testdb/trace/alert_testdb.log