Instance optimization of ORACLE database performance

This article mainly introduces the analysis of ORACLE database performance optimization problems and solutions from examples. Friends in need can refer to it. I hope it will be helpful to everyone. The optimization method of ORACLE database is very different from that of MYSQL. Today, through an ORACLE database instance, we will analyze how to optimize ORACLE database from various aspects such as tables and data.

tsfree.sql view

This sql statement quickly compares the total amount of space in each table space with the total amount of available space in each table space

Table space is a logical division of the database. One table space can only belong to one database. All database objects are stored in the specified table space. But it mainly stores tables, so it is called table space.

SELECT FS.TABLESPACE_NAME "Talbspace",
(DF.TOTALSPACE - FS.FREESPACE) "Userd MB",
FS.FREESPACE "Free MB",
DF.TOTALSPACE "Total MB",
ROUND(100 * (FS.FREESPACE / DF.TOTALSPACE)) "Pct Free" FROM
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576) TOTALSPACE FROM
DBA_DATA_FILES GROUP BY TABLESPACE_NAME) DF,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1048576) FREESPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) FS WHERE DF.TABLESPACE_NAME = FS.TABLESPACE_NAME;

Usage of varray table

CREATE OR REPLACE TYPE EMPLOYER_NAME AS OBJECT (E_NAME VARCHAR(40));

CREATE OR REPLACE TYPE PRIOR_EMPLOYER_NAME_ARR AS VARRAY(10) OF 
EMPLOYER_NAME;

CREATE OR REPLACE TYPE FULL_MAILLING_ADRESS_TYPE AS OBJECT(STREET 
VARCHAR2(80), CITY VARCHAR2(80), STATE CHAR(2), ZIP VARCHAR2(10));

CREATE OR REPLACE TYPE EMPLOYEE AS OBJECT(LAST_NAME VARCHAR(40), 
FULL_ADDRESS FULL_MAILLING_ADRESS_TYPE, PRIOR_EMPLOYERS 
PRIOR_EMPLOYER_NAME_ARR);

CREATE TABLE EMP OF EMPLOYEE;

INSERT INTO EMP VALUES('Jim', FULL_MAILLING_ADRESS_TYPE('Airplan Ave', 'Rocky', 'NC', '2343'), PRIOR_EMPLOYER_NAME_ARR(EMPLOYER_NAME('IBM'), EMPLOYER_NAME('APPLE'), EMPLOYER_NAME('CNN')));

--Rollback

DROP TYPE PRIOR_EMPLOYER_NAME_ARR FORCE;
DROP TYPE FULL_MAILLING_ADDRESS_TYPE FORCE;
DROP TYPE EMPLOYEE FORCE;
DROP TYPE EMPLOYER_NAME FORCE;
DROP TABLE EMP;
COMMIT;
SELECT P.LAST_NAME, PRIOR_EMPLOYERS.*
FROM EMP P, TABLE(P.PRIOR_EMPLOYERS) PRIOR_EMPLOYERS
WHERE P.LAST_NAME = 'Jim';

SQL execution process

1, Check security, ensure that the sql data executor has permission to execute
2, check sql syntax
3, rewrite possible queries
4, execute

创建执行计划
生产器接受经过解析的sql
捆绑执行计划 执行执行计划 读取结果记录 排序结果集

Data access method:

1，全表扫描 db_file_multiblock_read_count = 128
一次性最大读取block的数量
Oracle开启并行： Alter table employee parallel degree 35;
 顺序读取，直到结尾
1，当表中不存在索引
2，查询中不包含where字句
3，内置函数中的索引无效
4，like操作 %开头
5，使用基于成本优化器 数据量少时
6，当初始化文件中存在optimizer_mode = all_rows
7，负向条件查询不能使用索引 例如 status != 0, not in, not exists 可以优化为 in (2,3);

SQL statements in the following situations will cause a full table scan:

1，使用null条件查询导致全表扫，因为索引不能为空
为了绕过全表扫这个问题，可以采取这样的方法 
update emp set name = 'N/A' where name is null; 
select name from emp where name = 'N/A';
2，对没有索引的字段查询，找到where条件后面的查询不带索引的字段，加索引可以
大大提高查询性能。
3，带有like条件的查询 like '%x%' 全表扫描，like 'x%' 不会全表扫，因为like
以字符开始。
4，内置的函数使索引无效，对于Date类型的数据来说非常的严重
内置函数 （to_date，to_char）
如果没有创建与内置函数匹配的基于函数的索引，那么这些函数通常会导致sql优化器全表扫描
select name from emp where date < sysdate -8;
检查where子句脚本是否含有 substr to_char decode
SELECT SQL_TEXT, DISK_READS, EXECUTIONS, PARSE_CALLS
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE &#39;%substr%&#39;
OR LOWER(SQL_TEXT) LIKE &#39;%to_char%&#39;
OR LOWER(SQL_TEXT) LIKE &#39;%decode%&#39;
ORDER BY DISK_READS DESC;
使用函数索引解决这个问题

5，all_rows 优化器目标是提高吞吐量而且倾向于使用全表扫描，因此 对于任何一
个要求sql快速查询返回部分结果集而言，optimizer_mode 
应该设置为first_rows

6,经验上，能过滤80%数据时就可以使用索引，对于订单状态，如果状态很少，不宜
使用索引，如果状态值很多可以使用索引。

7，如果查询字段大部分是单条数据查询，使用Hash索引性能更好
原因:B-TREE 索引的时间复杂度是O(log(n))
Hash 索引的时间复杂度是O(1)
   
8,符合索引最左前缀，例如建立符合索引(passWord,userName)
select * from user u where u.pass_word = ? and u.user_name = ? 可以命中索引
select * from user u where u.user_name = ? and u.pass_word= ? 可以命中索引
select * from user u where u.pass_word = ? 可以命中索引
select * from user u where u.user_name = ? 不可以命中索引

How to find out the sql statements with high influence

 视图 v$sqlarea ,下列参数按照重要性从高到低排序
 executions ：越经常执行的sql语句就应当越早的调整，因为它会对整体的性能产生巨大的影响。
 disk_reads: 磁盘读取，高的磁盘读取量可能表明查询导致过多的输入输出量。
 rows_processed:记录处理，处理大量的记录的查询会导致较大的输入输出量，同时在排序的时候对TEMP表空间产生影响。
 buffer_gets:缓冲区读取，高的缓冲读取量可能表明了一个高资源使用量的查询。
 sorts:排序会导致速度的明显减低，尤其是在TEMP表空间中进行的排序。

2. Get the column

Oracle对单表簇和多表簇进行散列存储，用来在连接操作中减低输入 输出

3, ROWID access

The fastest way to access a single piece of data through Rowid. In the actual reference, first collect the ROWID from the index, and then use the ROWID Read data

Index access method

索引都可以看做一组符合主键和ROWID的组合，索引访问的目的是收集对目标快速读取时所需要的ROWID

B-tree index, bitmap index based on function index.

Index range scan: Read one or more ROWID index values ​​in ascending order

eg：select * from table where a = 'a';

Fast full index scan

eg: select distinct color,count(*) from table group by color;

Single index scan: read a single ROWID

Descending index range scan: read one or more ROWID Sort index values ​​in descending order

AND - EQUALS: select * from table where a = 'a' and b > 34; Collect multiple ROWIDs from the where clause

Join operation

Nested loop join

Hash join

Hash join is usually faster than nested loop join, especially in Drive the table and filter in the where clause of the query, leaving only a small number of records

Sort merge connection

Connection prompt:

表反向连接提示，例如，NOT IN, NOT EXISTS
尽量避免使用 NOT IN 子句（它将调用子查询），而应该使用NOT EXISTS 子句(它将调用相关联的子查询)，
因为如果子查询返回的任何一条记录中包含空值，那么该查询将不会返回记录,如果允许NOT IN 子句查询为空，那么
这种查询的性能非常的低，子查询会在外层查询块中对每一条记录重新执行一次。

Sort size sort_area_size_init.ora parameter, view sort_area_size on the console;

Query statement: show parameter sort_area_size;

The execution speed of disk sorting is 14,000 times slower than the execution speed of memory sorting

The reason why disk sorting is expensive is as follows:

1, Compared with sorting in memory, the speed is too slow
2, disk sorting consumes resources of temporary table space

The database allocates 2 temporary table spaces:

select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username='SYS';

select * from dba_temp_free_space;

Oracle temporary table space mainly plays two main roles: temporary table data Segment allocation and sort summary overflow segments.

The scope of sorting and summary overflow is relatively wide. We perform order by/group by and other operations in the SQL statement.

First select the memory sort area, hash area and bitmap area of ​​the PGA.

If the sort space used by SQL is very high and the PGA corresponding to a single server process is not enough to support the sorting requirements, the temporary table space will serve as the data write for the sort segment.

Disk sorting will slow down a single task and also affect other tasks being executed in the Oracle instance, and excessive disk sorting will result in excessive idle buffer waits

And the expensive cost of paging data blocks from other tasks out of the buffer pool.

Oracle first tries to sort in the memory area allocated by sort_area_size. Only when Oracle cannot sort in memory, will it call the disk sort
and migrate the memory frame to the TEMP table space to continue sorting.

General principles for using index range scans

 -- 对于原始排序的表， 仅读取少于40%的表记录查询就应该使用索引范围扫描，反之，多余40%，使用全表扫。
 -- 对于未排序的表， 仅读取少于7%的表记录查询就应该使用索引范围扫描，反之，多余7%，使用全表扫。

Table access methods

sql optimizer

For any SQL statement, there is a unique way to optimize table access, and your job is to find this way and use it for the long term.

db_file_multiblock_read_count

The purpose is to generate the fastest execution plan with the least resources for the sql statement

1, rule-based optimizer

步骤 
对于在where子句中的每一个表
-- 生成一个可行的执行计划列表，这个列表中列出所有可以用来访问表的路径
-- 为每一个执行计划指定级别数值
-- 选择级别数值最低的计划
-- 对结果集的选择级别最低 连接方法进行评估
 
基于规则优化器（PBO）特征
- 总是使用索引，使用索引永远比使用全表扫描或使用排序合并连接（排序合并连接不需要索引）更加可取
- 总是从驱动表开始 在from字句的最后一个表是驱动表,在这个表中选择的记录数应该是最少(查询返回值最少)，RBO在执行嵌套循环连接
操作时，将这个驱动表作为第一个操作表。
- 只有在不可避免的情况下才使用全表扫描
-任何索引都可以
- 有时越简单越好

2, Cost-Based Optimizer (CBO)

 基于规则优化提供更加复杂的优化替代方案
 ANALYZE TABLE TT_TCAS_HK_QTY COMPUTE STATISTICS;
 ANALYZE TABLE TT_TCAS_HK_QTY ESTIMATE STATISTICS SAMPLE 5000 ROWS;
 ANALYZE TABLE TT_TCAS_HK_QTY ESTIMATE STATISTICS SAMPLE 5000 ROWS FOR ALL INDEXED COLUMNS;
 
 CBO在以下情况会选择错误的全表扫描
 1，最高峰值过高
 2，错误的optimizer_mode,如果optimizer_mode设置为all_rows,choose,那么sql优化器会倾向于使用全表扫描。
 3，多表连接，存在多余3张表连接时，即使连接中存在索引，cbo仍然会对这些表进行全表扫描。
 4，不平衡的索引分布，比如 color = 'blue' color字段上有索引,但是只有1%的记录属于blue，

SQL SGA statistics

select name,value from v$sysstat where name like 'table%'

table scans(short table) -- The number of full table scans for a small table

table scans(long table) -- The number of full table scans for a large table, to evaluate whether to add an index Reduce the number of scans of large tables or increase query execution speed by calling Oracle parallelism (opq).

table scans Rows Gotten -- This number indicates the number of records scanned by the full table scan

table scans blocks Gotten -- The number of scanned databases

Table fetch by rowid -- The number of records accessed through the index. The index here is usually a nested loop connection

table fetch by Continued Row -- This number indicates the connection with other data blocks Number of records together

SQL that can be used multiple times in the library cache

Oracle has a problem identifying "identical" sql statements

For example: select from customer; Select From Customer; Even though the case of letters differs, Oracle will recompile and execute the second sql statement;

Related recommendations:

Detailed explanation The basic principles of oracle paging query

The "competition" between MySQL and Oracle

Summary on the use of oracle extensions

The above is the detailed content of Instance optimization of ORACLE database performance. For more information, please follow other related articles on the PHP Chinese website!