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;
分享到:
相关推荐
Oracle性能调优笔记
阿里巴巴Java性能调优华山版是一套系统性能调优教程,!通过这份笔记的学习,你将会有一个系统的调优头脑和策略!快了何止100%?需要的朋友可下载试试! 众所周知性能调优可以使系统稳定,用户体验更佳,甚至在...
ORACLE DBA工作笔记 运维数据迁移与性能调优 清晰版 。。。
Oracle DBA性能调优学习笔记
linux性能调优学习笔记
辛星笔记的MySQL部分对于InnoDB的性能调优的笔记。
在开发过程中总结的ios性能调优技巧,其中有自己实际工程中的总结,也有从网络上的摘抄。
该资源为老师JVM调优讲解完整版课件,现在分享给大家,希望对与想进阶java的小伙伴带来帮助
mysql DBA工作笔记,怎么性能调优,运维相关:比如热点数据导出与加载的影响; RR与RC下Mysql 不同的加锁解锁方式等实战经验及理论知识
学习笔记——JVM性能调优之 jstat(csdn)————程序
最近几年进行ORACLE性能调优做的笔记,值得参考
Go语言学习(五) 高质量编程与性能调优实战_青训营笔记
本人精心整理的oracle调优笔记,包含很多调优技巧很调优工具
redis性能优化参考,主要是自己看redis开发与运维时做的读书笔记....拿出来分享...希望各位多多指教.
本人通过对oracle11g的性能优化指南 做了全面的翻译和整理工作,并将每一部分整理成文档,后续会陆续更新,希望对大家有帮助,尤其是不爱看英文原版的朋友特别有帮助。
Oracle 12c-优化 Hint详解.pdf Oracle 12c-优化 Oracle 索引技术.pdf Oracle 12c 执行计划.pdf Oracle 12c锁.pdf Oracle 12c查询优化器.pdf Oracle 12c 使用绑定变量 VS 不使用绑定变量.pdf Oracle 12c 自动工作负载...
JVM修炼之路:GC调优与性能调优 Java虚拟机(JVM)是Java平台的核心组件,它负责将Java代码编译成字节码,并在运行时执行。在JVM的运行过程中,垃圾收集(GC)和性能调优是两个重要的方面。本文将详细介绍JVM的GC...
mysql 性能和架构设计原理,设计底层原理,不停留原理,开发和运维人员进阶必看
架构篇(1)读书笔记1.Scale(扩展):从数据库来看,就是让数据库能够提供更强的服务能力ScaleOut:是通过增加处理节点的方式来提高整体处理能力ScaleUp:是通过增加当前处理节点的处理能力来提高整体的处理能力2....
高性能Mysql笔记,网易云课堂https://study.163.com/course/courseMain.htm?courseId=1003746011课程笔记