Oracle Database Tuning ADDM
ADDM (Automatic Database Diagnostic Monitor) implements the Oracle performance method and analyzes statistics to provide automatic diagnosis of major performance problems. Because ADDM can significantly shorten the time required to improve the performance of a system, it is one of the most used performance tuning tools of Oracle database.
1. Run some operations on your database instance.
First, start your database instance and do the database operations (select, update, delete...) which will be optimized. e.g. run a script to make some queries in database:
sh run.sh -r rdf1mm-performance -t p -f p3.txt
2. Login to EM
Login to Database EM home page (https://:5500/em), click Performance tab.
3. Run ADDM
Click Button "Run ADDM Now", and click "Yes" on the confirmation page. After a while, we will see the ADDM analysis page.
Database Activity chart shows the database activity during the ADDM analysis period.
In the ADDM Performance Analysis table, ADDM findings are listed in descending order, from highest to least impact. The Informational Findings section lists areas that have no performance impact and are for information only.
4. Implement ADDM recommendations
Click the ADDM findings that has the impact on performance. e.g. Top SQL Statements.
The Category column displays the category of recommendation. The Benefit column displays the estimated benefit of implementing the recommendation.
If we click "Run Advisor Now", SQL tuning advisor will give us some recommendations on how to tune the SQLs.
相关文档：
extent--最小空间分配单位 --tablespace management
block --最小i/o单位 --segment management
create tablespace james
datafile '/export/home/oracle/oradata/james.dbf'
size 100M --初始的文件大小
autoextend On --自动增长
next 10M� ......
oracle从9i开始支持一种新的数据类型---- xmltype，用于存储和管理xml数据，并提供了很多的functions，用来直接读取xml文档和管理节点。下面将介绍xmltype的一些基本使用。
1.建立含有xmltype数据类型的表
create table abc (id number,xmldoc sys.xmltype);
声明xmltype型字段用:sys.xmltype
2.向带有xmltype类型的表 ......
oracle常用经典SQL查询
常用SQL查询：
1、查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
2、查看表空间物理文件的名称 ......
ORACLE SQL性能优化大总结
1） 选择最有效率的表名顺序(只在基于规则的优化器中有效)：
" ?; J) n+ v8 J- v" q Oracle的解析器按照从右到左的顺序处理from子句中的表名，from子句中写在最后的表(基础表 driving table)将被最先处理，在from子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表� ......
（一）什么叫用户（user）：
A user is a name defined in the database that can connect to and access objects.
用户是用连接数据库和访问数据库对象的。（用户是用来连接数据库访问数据库）。
（二）什么叫模式(schema)：
A schema is a collection of database objects (used by a user.).
Schema objects are th ......
