Instance analysis ORACLE database performance optimization, instance analysis oracle

Instance analysis ORACLE database performance optimization, instance analysis oracle

ORACLE Database optimization methods differ greatly from those of MYSQL. Today, an ORACLE database instance is used to analyze how to optimize ORACLE databases from tables and data.

Tsfree. SQL View

This SQL statement quickly compares the total space in each tablespace with the total space available in each tablespace.

A tablespace is the logical division of a database. A tablespace can belong to only one database. All database objects are stored in the specified tablespace. But it mainly stores tables, which are called tablespaces.

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;    

Use of varray tables

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 security to ensure that SQL data executors have the permission to execute
2. Check SQL syntax
3. Possible query re-writing
4. Run

Create execution plan producer accept parsed SQL bundle execution plan read results record sort result set

Data access method:

1. Full table scan db_file_multiblock_read_count = 128 maximum number of blocks read at a time Oracle enable parallel reading: Alter table employee parallel degree 35; sequential reading until the end 1 when the table does not have index 2, the query does not contain the where Clause 3, the index in the built-in function is invalid 4, the like Operation % starts with 5, and the cost-based optimizer_mode = all_rows7 is used when there is a small amount of data in the initialization file, indexes such as status cannot be used for negative condition queries! = 0, not in, not exists can be optimized to in (2, 3 );

In the following cases, the SQL statement will cause a full table scan:

1. If null condition is used for query, the full table is scanned because the index cannot be empty, you can use this method to update emp set name = 'n'/A' where name is null; select name from emp where name = 'n'/A'; 2, to query fields without indexes, find the query fields without indexes after the where condition. Adding indexes can greatly improve the query performance. 3. For a query with the like condition, like '% x %' is used for full table scan. like 'x % 'is not used for full table scan because like starts with a character. 4. the built-in functions make the index invalid. For Date-type data, the built-in functions (to_date, to_char) are very serious. If function-based indexes matching the built-in functions are not created, these functions usually cause the SQL optimizer to scan the entire table for select name from emp where date <sysdate-8; check whether the where clause script contains 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; use function indexes to solve this problem. 5. all_rows Optimizer The goal is to increase throughput and tend to use full table scanning. Therefore, optimizer_mode should be set to first_rows6 for any SQL quick query to return partial result sets, indexes can be used when 80% of data can be filtered. For order status, indexes are not recommended if the status is small. If there are many Status values, indexes can be used. 7. If most of the query fields are single data queries, the reason for better performance with Hash indexes is that the time complexity of B-TREE indexes is O (log (n )) the time complexity of the Hash index is O (1) 8, which conforms to the leftmost prefix of the index. For example, the index (passWord, userName) select * from user u where u. pass_word =? And u. user_name =? Can hit index select * from user u where u. user_name =? And u. pass_word =? Can hit index select * from user u where u. pass_word =? Can hit index select * from user u where u. user_name =? Index hit is not allowed

How to find influential SQL statements

View v $ sqlarea. The following parameters are sorted in descending order of importance by executions: The sooner SQL statements are executed, because it will have a huge impact on the overall performance. Disk_reads: disk read. A High Disk Read volume may indicate that the query results in excessive input and output. Rows_processed: record processing. processing a large number of record queries results in a large input output and affects the TEMP tablespace during sorting. Buffer_gets: Buffer read. A high buffer read volume may indicate a query with high resource usage. Sorts: Sorting can significantly reduce the speed, especially in the TEMP tablespace.

2. Obtain the contestant

Oracle stores single-Table clusters and multi-Table clusters in hashes to reduce input and output in connection operations.

3. ROWID access

Rowid is the fastest way to access a single piece of data. In actual reference, the ROWID is collected from the index, and then the data is read through the ROWID.

Index access method

Indexes can be considered as a group of primary key and ROWID combinations. The purpose of index access is to collect the ROWID required for fast reading the target.

B-tree index. Bitmap index is a function-based index.

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

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

Fast full index Scanning

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

Single index scan: read a single ROWID

Descending index range scan: reads one or more ROWID index values in descending order

AND-EQUALS: select * from table where a = 'A' and B> 34; collect multiple rowids from where statements

Connection operation

Nested loop connection

Hash join

Hash join is usually faster than nested loop join, especially when the driver table and the where clause of the query are filtered, and only a few records are left.

Sort and merge connections

Connection prompt:

Table reverse join prompt, for example, not in, not exists tries to avoid using the not in clause (it will call the subquery ), instead, use the not exists clause (which will call the associated subquery), because if any record returned by the subquery contains null values, the query will NOT return records, if the not in Clause query is allowed to be null, the query performance is very low, and the subquery will re-execute each record IN the outer query block.

Sort_area_size_init.ora. You can view sort_area_size on the console;

Query statement: show parameter sort_area_size;

The execution speed of disk sorting is 14000 times slower than that of memory sorting.

Disk sorting is expensive for the following reasons:

1. Sorting and comparison in the memory is too slow
2. Disk sorting consumes Resources in the temporary tablespace

The database allocates two temporary tablespaces:

Select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username = 'sys ';

Select * from dba_temp_free_space;

Oracle temporary tablespace mainly plays two main roles: temporary table data segment allocation and sorting summary overflow segments.

Sorting summary overflow has a wide range. We perform order by/group by operations in SQL statements,

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

If SQL uses a high sorting space, when the PGA corresponding to a single server process is insufficient to support the sorting requirements, the temporary tablespace will act as the Data Writing of the sorting segment.

Disk sorting reduces the speed of a single task and affects other tasks being executed in the Oracle instance. Excessive disk sorting results in excessive idle buffer waits.

It is expensive to split data blocks of other tasks from the buffer pool.

Oracle first tries to sort in the memory area allocated by sort_area_size. Oracle calls disk sorting only when it cannot sort in memory.
Migrate the memory framework to the TEMP tablespace and continue sorting.

General principles of using index range scanning

-- For a table in the original sorting, index range scanning should be used to query only the records of tables with less than 40%; otherwise, full table scanning should be used for the excess 40%. -- For unordered tables, index range scanning should be used to read only less than 7% of the table records; otherwise, full table scanning should be used for excess 7%.

Table Access Method

SQL Optimizer

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

Db_file_multiblock_read_count

The objective is to generate the fastest and least-resource execution plan for SQL statements.

1. Rule-Based Optimizer

To generate a list of feasible execution plans for each table in the where clause, this list lists all the paths that can be used to access the table -- specify a level value for each execution plan -- select a plan with the lowest level value -- evaluate the method of selecting the lowest level join of the result set based on Rules optimizer (PBO) feature-always use index, always use index than use full table scan or use sort merge connection (sort merge connection does not require index) more desirable-the last table in the from clause always starts from the driver table. The number of records selected in this table should be the least (the query return value is the least ), when RBO executes the nested loop join operation, the driver table is used as the first operation table. -Full table scan is available only when it is inevitable-any index is available-sometimes the simpler the better

2. Cost-based optimizer (CBO)

Analyze table TT_TCAS_HK_QTY compute statistics; analyze table sort estimate statistics sample 5000 ROWS; analyze table sort estimate statistics sample 5000 rows for all indexed columns; in the following cases, the CBO selects the wrong full table Scan 1, the maximum peak value is too high 2, the wrong optimizer_mode, if optimizer_mode is set to all_rows, choose, the SQL optimizer tends to use full table scanning. 3. Multi-table join. If there are more than three table connections, cbo will still perform full table scan for these tables even if there is an index in the connection. 4. unbalanced index distribution. For example, the color = 'blue' color field has an index, but only 1% of the records belong to blue,

SQL SGA statistics

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

Table scans (short table) -- number of full table scans for small tables

Table scans (long table)-the number of full table scans on a large table, and whether to reduce the number of scans on a large table by adding an index or call Oracle Parallel (opq) to speed up query execution.

Table scans Rows Gotten -- this number indicates the number of scan records in the entire table.

Table scans blocks Gotten -- scan to obtain the number of databases

Table fetch by rowid -- number of access records through indexes. The indexes here are usually nested loop connections.

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

SQL statements that can be used multiple times in the library Cache

Oracle identifies "the same" SQL statement as a problem

For example, select from customer; Select From Customer; despite the case-sensitive differences between letters, Oracle recompiles and executes the second SQL statement;