Shulou(Shulou.com)06/01 Report--

How to optimize the performance of ORACLE database? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

The optimization mode of ORACLE database is very different from that of MYSQL. Today, through an example of ORACLE database, it is convenient to analyze how to optimize ORACLE database from tables, data and so on.

Tsfree.sql view

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

A table space is a logical partition of a database, and a table space can only belong to one database. All database objects are stored in the specified tablespace. But the main storage is the table, so it is called tablespace.

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 FROMDBA_DATA_FILES GROUP BY TABLESPACE_NAME) DF, (SELECT TABLESPACE_NAME ROUND (SUM (BYTES) / 1048576) FREESPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FS WHERE DF.TABLESPACE_NAME = FS.TABLESPACE_NAME

The use 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_ADRESS_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 the security to ensure that the executor of sql data has permission to execute.

2. Check the sql syntax

3. Possible query rewriting

4, execute

Create execution plan producer to accept parsed sql bundle execution plan execution plan read result record sort result set

Data access method:

1. Full table scan db_file_multiblock_read_count = 128. maximum number of block read at a time Oracle enables parallelism: Alter table employee parallel degree 35 Read sequentially until the end 1, when index 2 does not exist in the table, the query does not contain the where sentence 3, the index in the built-in function is invalid 4, and the index in the built-in function is invalid 4. When the amount of data using the cost-based optimizer is small, 6, there is optimizer_mode = all_rows7 in the initialization file, and negative conditional queries cannot use indexes such as status! = 0, not in, not exists can be optimized to in (2).

SQL statements in the following cases cause a full table scan:

1. Use null conditional query to cause full table scan, because the index cannot be empty. In order to bypass the problem of full table scan, we can take this method: update emp set name ='N big A 'where name is null; select name from emp where name =' N big A 'scan 2, query fields without index, and find fields without index in the query behind the where condition. Adding index can greatly improve query performance. 3. Query with like condition like'% x% 'full table scan, like' x% 'will not scan the full table, because like starts with a character. 4. Built-in functions invalidate indexes, which are very serious for Date-type data (to_date,to_char) if no function-based indexes are created that match the built-in functions, these functions usually cause the sql optimizer to scan the select name from emp where date

< sysdate -8;检查where子句脚本是否含有 substr to_char decodeSELECT SQL_TEXT, DISK_READS, EXECUTIONS, PARSE_CALLSFROM V$SQLAREAWHERE LOWER(SQL_TEXT) LIKE '%substr%'OR LOWER(SQL_TEXT) LIKE '%to_char%'OR LOWER(SQL_TEXT) LIKE '%decode%'ORDER BY DISK_READS DESC;使用函数索引解决这个问题5，all_rows 优化器目标是提高吞吐量而且倾向于使用全表扫描，因此 对于任何一个要求sql快速查询返回部分结果集而言，optimizer_mode 应该设置为first_rows6,经验上，能过滤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 = ? 不可以命中索引 如何找出影响力高的sql语句 视图 v$sqlarea ,下列参数按照重要性从高到低排序 executions ：越经常执行的sql语句就应当越早的调整，因为它会对整体的性能产生巨大的影响。 disk_reads: 磁盘读取，高的磁盘读取量可能表明查询导致过多的输入输出量。 rows_processed:记录处理，处理大量的记录的查询会导致较大的输入输出量，同时在排序的时候对TEMP表空间产生影响。 buffer_gets:缓冲区读取，高的缓冲读取量可能表明了一个高资源使用量的查询。 sorts:排序会导致速度的明显减低，尤其是在TEMP表空间中进行的排序。 2.赛列获取 Oracle对单表簇和多表簇进行散列存储，用来在连接操作中减低输入 输出 3，ROWID 访问 通过Rowid访问单条数据最快的方式，在实际的引用中，首先从索引中收集ROWID,然后通过ROWID进行数据读取 索引访问方式 索引都可以看做一组符合主键和ROWID的组合，索引访问的目的是收集对目标快速读取时所需要的ROWID B树索引，位图索引 基于函数的索引. 索引范围扫描：读取一个或者多个ROWID 索引数值升序排列 eg：select * from table where a = 'a'; 快速全索引扫描 eg: select distinct color,count(*) from table group by color; 单个索引扫描:读取一个单独的ROWID 降序索引范围扫描:读取一个或者多个ROWID 索引数值降序排列 AND - EQUALS: select * from table where a = 'a' and b >

Collect multiple ROWID from where sentences

Connection operation

Nested loop connection

Hash join

Hash joins are usually faster than nested loop joins, especially when driving tables and filtering in the query's where clause leaves only a small number of records

Sort merge join

Connection Tip:

Table reverse join hint, for example, NOT IN, NOT EXISTS try to avoid using the NOT IN clause (which will invoke the subquery) and instead use the NOT EXISTS clause (which will invoke the associated subquery), because if any record returned by the fruit subquery contains null values, the query will not return the record, and if the NOT IN clause query is allowed to be empty, the performance of this query is very low The subquery is re-executed for each record in the outer query block.

Sort size sort_area_size_init.ora parameter, view sort_area_size in the console

Query statement: show parameter sort_area_size

Disk sorting is 14000 times slower than memory sorting.

Disk sorting is expensive for several reasons:

1. Compared with sorting in memory, the speed is too slow

2. Disk sorting consumes resources of temporary tablespaces

Database allocates 2 temporary tablespaces:

Select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username='SYS'

Select * from dba_temp_free_space

Oracle temporary tablespaces play two main roles: temporary table segment allocation and sort summary overflow segments.

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

The first step is to select PGA's memory sort area, hash area, and bitmap area.

If the SQL uses a high sort space and the PGA corresponding to a single server process is not sufficient to support the sorting requirements, the temporary tablespace will act as the data write for the sort segment.

While disk sorting slows down individual tasks, it also affects other tasks that are being performed in the Oracle instance, and too much disk sorting will lead to too much idle buffer waiting

And the high cost of paging blocks of other tasks out of the buffer pool.

Oracle first tries to sort in the memory area allocated by sort_area_size. Oracle will call disk sort only if it can no longer sort in memory.

And migrate the memory framework to the TEMP tablespace to continue sorting.

General principles for using index range scanning

For original sorted tables, index range scanning should be used for only reading less than 40% of table records, while for more than 40%, full table scan should be used. For unsorted tables, index range scanning should be used for only reading less than 7% of the table records, while for more than 7%, full table scans should be used.

How to access the table

Sql optimizer

For any sql statement, there is only one way to optimize table access, and your job is to find this way and use it for a long time.

Db_file_multiblock_read_count

The goal is to generate the fastest and least resource execution plan for sql statements

1, rule-based optimizer

Step for each table in the where clause-generate a list of feasible execution plans that list all the paths that can be used to access the table-specify a level value for each execution plan-select the plan with the lowest level value-evaluate the selection level lowest join method of the result set based on the rule optimizer (PBO) feature-always use an index Using an index is always preferable to using a full table scan or using a sort merge join (sort merge join does not require an index)-always starting from the drive table the last table in the from statement is the driver table, and the number of records selected in this table should be the least (the least query return value), and RBO uses this driver table as the first operation table when performing nested loop join operations. -use full table scans only when it is inevitable-any index is fine-sometimes the simpler the better

2, cost-based optimizer (CBO)

Rule-based optimization provides a more complex optimization alternative 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 will choose the wrong full table scan 1, the maximum peak is too high 2, the wrong optimizer_mode, if optimizer_mode is set to all_rows,choose, then the sql optimizer will prefer to use full table scan. 3, multi-table joins. When there are more than 3 table joins, cbo will scan all the tables even if there is an index in the join. 4. Unbalanced index distribution, for example, there is an index on the color = 'blue' color field, but only 1% of the records belong to blue

SQL's SGA statistics

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

Table scans (short table)-the number of times a full table scan of a small table

Table scans (long table)-the number of full table scans of large tables to evaluate whether to reduce the number of large table scans by indexing or to improve query execution speed by calling Oracle parallelism (opq).

Table scans Rows Gotten-this number indicates the number of records scanned by the full table

Table scans blocks Gotten-number of databases obtained by scanning

Table fetch by rowid-the number of records accessed through the index, which is usually a nested loop join

Table fetch by Continued Row-this number indicates the number of records connected to other data blocks

SQL that can be used multiple times in the library cache

Oracle has a problem identifying "the same" sql statement.

For example, although select from customer; Select From Customer; distinguishes the case of letters, Oracle recompiles and executes the second sql statement.

This is the answer to the question about how to optimize the performance of ORACLE database. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.