性能调优笔记

SQL> show parameter dump

cp alert_xxx.log.20110709 alert_xxx.log
cat /dev/null > alert_xxx.log

LOG_CHECKPOINTS_TO_ALERT
SQL> alter system set LOG_CHECKPOINTS_TO_ALERT=true scope=both;

bbk2103  Statspack    
SQL> ALTER SESSION SET sql_trace=TRUE;
sys用户
SQL> SELECT sid, serial#,username FROM v$session;
SQL> EXECUTE dbms_system.set_sql_trace_in_session(143, 36, TRUE);
SQL> show parameter sql_trace

rdbms/admin/spdoc.txt  Statspack使用文档

DB_FILE_MULTIBLOCK_READ_COUNT

v$session_longops  一般是大于6秒的操作

SELECT name, value FROM v$sysstat WHERE name LIKE '%table scan%';   查看全表扫描

Thread 1 cannot allocate new log, sequence 1466
Checkpoint not complete
   Current log# 3 seq# 1465 mem# 0: /../redo03.log

alter system set cursor_sharing='similar';

v$librarycache
v$db_object_cache

EXECUTE dbms_shared_pool.keep(‘package_name’);

select sql_text from v$sqlarea WHERE executions < 5 order by upper(sql_text);
select sql_text, parse_calls, executions from v$sqlarea order by parse_calls;

select namespace,gets,gethits,pins,pinhits,reloads,invalidations from v$librarycache;

select shared_pool_size_for_estimate AS pool_size,estd_lc_size,estd_lc_time_saved from v$shared_pool_advice;

select operation,object_owner,object_name,cost from v$sql_plan order by hash_value;  bbk2158

v$rowcache   bbk2174
select parameter,gets,getmisses from v$rowcache;
--------------------------------------------------------------------------------------------------------
Tuning the Buffer Cache  bbk2175

SELECT name, value FROM v$sysstat WHERE name = 'free buffer inspected'; bbk2178

SELECT event, total_waits FROM v$system_event WHERE event in('free buffer waits', 'buffer busy waits');

V$BH   bbk2181

alter table tablename cache;    bbk2182
select /*+ CACHE */

p199
SQL> SELECT s.segment_name, s.segment_type, s.freelists,
2 w.wait_time, w.seconds_in_wait, w.state
3 FROM dba_segments s, v$session_wait w
4 WHERE w.event ='buffer busy waits'
5 AND w.p1 = s.header_file
6 AND w.p2 = s.header_block;

show parameter db_writer_processes
show parameter disk_asynch_io
show parameter dbwr_io_slaves
--------------------------------------------------------------------------------------------------------------
Dynamic Instance Resiz   bbk2183

show parameter sga_max_size

select name,block_size,resize_state,current_size,buffers from v$buffer_pool;
-----------------------------------------------------------------------------------------------------------
Sizing Other SGA Structures bbk2184

show parameter log_buffer
select * from v$sgastat where pool is null;

select * from v$sysstat WHERE name IN ('redo entries','redo buffer allocation retries');  redo buffer allocation retries/redo entries < 1%

SELECT sid, event, seconds_in_wait, state FROM v$session_wait WHERE event = 'log buffer space%';
--------------------------------------------------------------------------------------
Sizing Other SGA Structures    bbk2185
---------------------------------------------------------------------------------------
Tuning the Oracle Shared Server  bbk2188

v$circuit  Monitoring Process   bbk2189
------------------------------------------------------------------------------------------
Optimizing Sort Operations bbk2190

– PGA_AGGREGATE_TARGET          bbk2191         
(Ranges from 10 MB to 4000 GB)
– WORKAREA_SIZE_POLICY
– AUTO | MANUAL

v$pgastat : over allocation count ; cache hit percentage

SELECT ROUND(pga_target_for_estimate/1024/1024) AS target_mb,
estd_pga_cache_hit_percentage AS cache_hit_percent, estd_overalloc_count
FROM v$pga_target_advice
ORDER BY target_mb

select * from V$sysstat where name like '%sort%'; bbk2195
---------------------------------------------------------------------------------------------------
Using Resource Manager bbk2196

desc dbms_resource_manager
desc dbms_resource_manager_privs
----------------------------------------------------------------------------------------
SQL Statement Tuning bbk2228

DESC plan_table
@?/rdbms/admin/utlxplan.sql     P334

EXPLAIN PLAN FOR
select employee_id,last_name,salary from hr.employees where employee_id=120;
 
@?/rdbms/admin/utlxpls.sql   串行
@?/rdbms/admin/utlxplp.sql   并行

SELECT * FROM table(dbms_xplan.display);

ALTER SESSION SET sql_trace = True;   p336
EXECUTE dbms_session.set_sql_trace true;  p338

$ tkprof wilson_ora_6572.trc myoutput.txt   p339

Autotrace    p342
---------------------------------------------------------------------------
Managing Statistics  bbk2340

DBMS_STATS

user_tab_col_statistics

v$segstat_name   v$segstat   bbk2342

Histograms   bbk2343   p359
1.You create histograms with the
dbms_stats.gather_table_stats procedure.
2.Data dictionary views: dba_histograms,
dba_tab_histograms

EXECUTE dbms_stats.gather_table_stats('HR','EMPLOYEES', METHOD_OPT =>'FOR COLUMNS SIZET AUTO salary');
SELECT * FROM dba_histograms WHERE owner='HR' and table_name='EMPLOYEES' and column_name='SALARY'
-------------------------------------------------------------------------------------
Using Oracle Blocks Efficiently bbk2515

SQL> analyze table t compute statistics;
SQL> SELECT owner, table_name, blocks, empty_blocks
2 FROM dba_tables
3 WHERE empty_blocks/(blocks+empty_blocks) < .1;

help set/help show bbk2516

Alter Table Employees Move   bbk2668    p390

Detecting Migration and Chaining p400
SQL> ANALYZE TABLE oe.orders COMPUTE STATISTICS;
SQL> SELECT num_rows, chain_cnt FROM dba_tables
2 WHERE table_name='ORDERS';
-----------------------------------------------------------------------------------------------------
Using Oracle Data Storage Structures Efficiently bbk2863

index cluster
create cluster mycluser(depton number(2)) size 1024;
create index myc_idx on cluster mycluser;
SQL> create table dept
  2  (deptid number(2) primary key,
  3  dname varchar2(20),
  4  loc varchar2(30)
  5  )
  6  cluster mycluser(deptid);
SQL> create table emp
  2  (empid number primary key,
  3  ename varchar2(20),
  4  sal number,
  5  deptno number(2) references dept(deptid)
  6  )
  7  cluster mycluser(deptno);

hash cluster
create cluster hc(hk number) hashkeys 1000 size 8192;

bbk2901
create table range1
(rk date,
data varchar2(20)
)
partition by range(rk)
(
        partition p1 values less than (to_date('01/01/2011', 'dd/mm/yyyy')) tablespace ts0,
   partition p2 values less than (to_date('01/01/2012', 'dd/mm/yyyy')) tablespace ts1
);

select * from range1 partition(p2)

alter table range1 add partition p3 values less than (maxvalue) tablespace ts2

create table emp
(empno int,
ename varchar2(20)
)
partition by hash(empno)
(
partition part1 tablespace ts1,
partition part2 tablespace ts2
);

bbk2902
create table list1
( stateid varchar2(2),
  mydata varchar2(20)
)
partition by list(stateid)
( partition p1 values('TX','MA','NY') tablespace ts0,
  partition p2 values('Tb','Mc') tablespace ts1,
  partition p3 values(default) tablespace ts2
);

create table composite1
(range_key date,
hash_key int,
data varchar2(20)
)
partition by range(range_key)
subpartition by hash(hash_key)
( partition p1 values less than (to_date('01/01/2011', 'dd/mm/yyyy'))
  ( subpartition h1,
    subpartition h2
  ),
  partition p2 values less than (to_date('01/01/2012', 'dd/mm/yyyy'))
  ( subpartition h21,
    subpartition h22
  )
);

create index local_idx1 on range1(a,b) local;
create index g_idx1 on range1(mydata);

create index gp_idx1 on range1(b) global
partition by range(b)
( partition idx1 values less than (10000),
  partition idx1 values less than (maxvalue)
);
---------------------------------------------------------------------------------------------
Application Tuning bbk3019

bbk3152
SQL> CREATE INDEX emp_last_name_idx
2 ON hr.employees
3 (last_name, first_name)
4 COMPRESS;

SQL> ALTER INDEX emp_last_name_idx
2 REBUILD COMPRESS;

Index-Organized Tables    bbk3153 p457

create table iot1
( x int,
  y date,
  z varchar2(1000),
  constraint iot1_pk primary key(x)
)
ORGANIZATION INDEX
pctthreshold 10
overflow;

create table iot2
( x int,
  y date,
  z varchar2(1000),
  constraint iot2_pk primary key(x)
)
ORGANIZATION INDEX
including y
overflow;
----------------------------------------------------------------------------------
Using Materialized Views bbk3201

grant create any materialized view to pts;
create materialized view mv as select * from t;
exec dbms_mview.refresh('MV','C');
drop materialized view mv;

create materialized view log on t;
exec dbms_mview.refresh('MV','F');

grant query rewrite to pts;

insert /*+ APPEND */ into my_all_objects select * from my_all_objects;

analyze table my_all_objects compute statistics;

alter session set query_rewrite_enabled=true;
alter session set query_rewrite_integrity=enforced;

create materialized view my_all_objects_aggs
build immediate
refresh on commit
enable query rewrite
as
select owner, count(1) from my_all_objects group by owner;

analyze table my_all_objects_aggs compute statistics;
-----------------------------------------------------------------------------------------------------
Monitoring and Detecting Lock Contention bbk3204

select * from v$mystat where rownum=1;
select * from v$lock;