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性能调优是数据库管理中的关键任务,旨在提高数据库系统的响应速度和整体效率。以下是针对Oracle性能调优的详尽解析: 首先,调优的角色包括系统设计人员、系统开发人员、DBA(数据库管理员)以及操作系统...
标题中提到的“ORACLE DBA工作笔记 运维数据迁移与性能调优”揭示了这本书籍主要围绕着Oracle数据库管理员(DBA)在日常工作中经常需要进行的两项关键任务:数据迁移和性能调优。作为一名Oracle DBA,不仅要负责...
阿里巴巴Java性能调优华山版是一套系统性能调优教程,!通过这份笔记的学习,你将会有一个系统的调优头脑和策略!快了何止100%?需要的朋友可下载试试! 众所周知性能调优可以使系统稳定,用户体验更佳,甚至在...
"Windchill性能调优笔记.docx"文件很可能是对以上这些方面的详细记录和指南,包含了具体的步骤和建议。在实际操作中,应根据企业的具体情况进行调整,通过不断的测试和调整,找到最适合自己企业的性能优化方案。记住...
### 马士兵JVM调优笔记知识点梳理 #### 一、Java内存结构 Java程序运行时,其内存被划分为几个不同的区域,包括堆内存(Heap)、方法区(Method Area)、栈(Stack)、程序计数器(Program Counter Register)以及...
Linux性能调优是系统管理员和开发人员优化Linux系统性能的重要技能。它涉及对系统资源和应用程序的分析、监控、和调整,以实现更高的效率和响应速度。本篇学习笔记详细介绍了性能分析的步骤、优化工具、性能指标的...
针对“Oracle DBA性能调优学习笔记”这一主题,我们可以提取并解释出以下重点知识。 首先,性能调优是一个多角色参与的过程。不仅DBA需要参与,应用架构师、应用设计师、应用开发人员以及OS和存储系统管理员也同样...
JVM性能调优是一项关键技术,旨在优化JVM的内存管理、垃圾收集、类加载等方面,以提升程序运行速度、减少内存占用并避免系统崩溃。本教程"JVM性能调优经典教程"由马士兵老师倾力讲解,旨在帮助Java开发者进阶,掌握...
在开发过程中总结的ios性能调优技巧,其中有自己实际工程中的总结,也有从网络上的摘抄。
本部分将详细介绍Oracle 11g数据库性能优化笔记的第五部分内容,通过实例调优的步骤来逐步深入理解如何使用Oracle提供的性能视图来改善数据库实例的性能。 首先,确定问题的范围和性能目标是实例调整的起始点。这...
《JVM调优笔记》 Java虚拟机(JVM)是Java编程语言的核心组成部分,它负责执行字节码,管理内存,以及优化程序性能。JVM调优是提高Java应用程序性能的关键步骤,涉及到多个方面,包括堆内存设置、垃圾收集器选择、...
在本节"Go语言学习(五)高...记得结合提供的"Go语言学习(五)高质量编程与性能调优实战_青训营笔记.md"和"Go语言学习(五)高质量编程与性能调优实战_青训营笔记.pdf"文档深入学习,以获取更全面的理解和实践指导。
马老师 JVM 调优实战笔记 JVM 调优是 Java 开发者们不可或缺的技能,它直接影响着 Java 应用程序的性能和稳定性。本笔记是马老师的 JVM 调优实战笔记,涵盖了 JVM 的概述、内存结构、堆内存、垃圾回收算法、JVM ...
MySQL性能调优与运维是DBA日常工作中至关重要的任务,涉及到数据库系统的稳定性和效率。以下是一些关键知识点的详细说明: 1. **热点数据导出与加载的影响**:热点数据导出是为了避免数据库重启后因预热缓存而消耗...
根据给定的信息,本文将详细解析MySQL调优过程中的几个关键知识点:存储引擎介绍、查询执行流程分析以及执行计划详解。 ### 一、MySQL存储引擎介绍 #### 1. MySQL插拔式存储引擎概述 MySQL的一大特点是其插拔式的...
在IT行业中,容量测试和性能调优是两个关键的领域,它们对于确保系统稳定性和高效运行至关重要。容量测试主要是为了评估系统在预期或峰值负载下的处理能力,而性能调优则是通过对系统的各种参数进行调整,以提高其...
这份"Java生产环境下性能监控与调优详解课程笔记(完整版)"涵盖了多个重要的知识点,旨在帮助开发者和运维人员提升Java应用的性能。 一、Java性能监控工具 1. JVisualVM:集成在JDK中的一个多用途工具,可以进行...
本资料集是作者精心整理的Oracle调优笔记,涵盖了一系列实用的调优技巧和工具,旨在帮助读者理解和实践Oracle数据库的性能优化。 1. **SQL优化**:SQL查询是数据库性能的关键。优化SQL语句可以显著提升性能,包括...
JVM性能调优是Java开发中至关重要的一环,它直接影响应用程序的运行效率和稳定性。jstat(JVM Statistics Monitoring Tool)是Oracle JDK提供的一款强大的命令行工具,用于实时监控Java虚拟机的各种运行状态,包括...