Note: Troubleshooting steps for Oracle slow query

This is the 9th day of my participation in the August Wen Challenge.More challenges in August

>>>> 😜😜😜 Github: 👉 github.com/black-ant CASE Backup: 👉 gitee.com/antblack/ca…

A. The preface

This section records the troubleshooting process of slow Oracle queries for future use.

After looking at some documents, the optimized scheme in Oracle is basically the same as that in Mysql, which usually includes the following directions:

Benchmark (throughput) : Includes Oracle’s own throughput and disk I/O throughput
Hardware analysis (resource usage) : check server CPU and hard disk usage
SQL analysis: Analyzes whether slow queries exist in SQL and whether indexes match
Configuration optimization: Analyze whether performance can be improved through environment configuration

Above several aspects, basically can locate the problem, through the problem to consider the solution

2. Troubleshooting Procedure

2.1 Querying Slow Querying logs

Unlike Mysql, which writes logs directly to the log, Oracle can use the statement Excle log@oracle to pull out slow queries.

Slow query timeselect *
 from (selectSQL_TEXT "SQL", sa.EXECUTIONS ", round(sa.elapsed_time)/ 1000000.2Total execution time, round(sa.elapsed_time/ 1000000 / sa.EXECUTIONS, 2) "average execution time ", sa.mand_type, sa.parsing_user_id" user ID", u.username "username ", sa.hash_valuefrom v$sqlarea sa
     left join all_users u
      on sa.PARSING_USER_ID = u.user_id
     where sa.EXECUTIONS > 0
     order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
 where rownum < = 50; # number of queriesSQL 
select *
 from (selectS.scql_text, s.utions "number of executions ", s.parsing_user_id" username ",rank(a)over(order by EXECUTIONS desc) EXEC_RANK
     from v$sql s
     left join all_users u
      on u.USER_ID = s.PARSING_USER_ID) t
 where exec_rank < = 100;
Copy the code

Interpretation of the results:

After getting the average execution time, you can obviously find that the query time is long SQL, but this type of SQL is not necessarily slow query, need to judge according to the situation, if there is a very unreasonable time, you need to analyze the index

2.2 Viewing Indexes

explain plan for
select * from t_call_records where t_bjhm='123456'# Check the execution resultselect * from table(dbms_xplan.display)
Copy the code

Index content supplement

You can clearly see that a full table scan has been performed, so you need to add indexes and validations as appropriate

Index unique scan (primary key index)
Index range scan (index range scan)
Index Full scan: indicates full index scan
index fast full scan: Quick index scan: scans all data blocks in an index, similar to full index scan.
- The obvious difference between the two is that the index quick scan does not sort the queried data, and the data is not sorted when returned.

2.3 Checking lock contention status

Step 1: Check background lock contention

SELECT
	SQ.INST_ID,
	SQ.SQL_TEXT, / * * / SQL text
	SE.SID, /* A unique identifier for a session. The SID of a session is usually required before a session can be analyzed. * /
	SE.BLOCKING_SESSION,
	SQ.OPTIMIZER_COST AS COST_,/ * * / COST value
	SE.LAST_CALL_ET CONTINUE_TIME,/* Execution time */
	SE.EVENT,/* Wait for events */
	SE.LOCKWAIT,/* Wait for LOCK(SE, P)*/
	SE.MACHINE,/* The machine name of the client. (WORKGROUP\PC-201211082055)*/
	SQ.SQL_ID,/*SQL_ID*/
	SE.USERNAME,/* The user name that created the session */
	SE.LOGON_TIME,/* Login time */
	'ALTER SYSTEM KILL SESSION ' || SE.SID || ', ' || SE.SERIAL #  -- If there is a lock, use the KILL lock to release ~
FROM
	gV$SESSION SE,/* Session information. Each session connected to the ORACLE database can have a record */ in this view
	gV$SQLAREA SQ /* Tracks the number of CURSOR executions, logical reads, physical reads, etc. */
WHERE
	SE.SQL_HASH_VALUE = SQ.HASH_VALUE    
	AND SE.STATUS = 'ACTIVE'    
	AND SE.SQL_ID = SQ.SQL_ID    
	AND SE.USERNAME = SQ.PARSING_SCHEMA_NAME       -- Filtration conditions
	AND SE.USERNAME = 'FWSB' - the user name
	AND se.BLOCKING_SESSION IS NOT NULL;
        
//Actually running the script======================   
SELECT
	SQ.INST_ID,
	SQ.SQL_TEXT,
	SE.SID,
	SE.BLOCKING_SESSION,
	SQ.OPTIMIZER_COST AS COST_,
	SE.LAST_CALL_ET CONTINUE_TIME,
	SE.EVENT,
	SE.LOCKWAIT,
	SE.MACHINE,
	SQ.SQL_ID,
	SE.USERNAME,
	SE.LOGON_TIME,
	'ALTER SYSTEM KILL SESSION ' || SE.SID || ', ' 
FROM
	gV$SESSION SE,
	gV$SQLAREA SQ 
WHERE
	SE.SQL_HASH_VALUE = SQ.HASH_VALUE 
	AND SE.STATUS = 'ACTIVE' 
	AND SE.SQL_ID = SQ.SQL_ID 
	AND SE.USERNAME = SQ.PARSING_SCHEMA_NAME 
	AND SE.USERNAME = 'FWSB' 
	AND SE.BLOCKING_SESSION IS NOT NULL;
Copy the code

Add: The related table structure can be queried in the Oracle official documentation

Step 2: Query results

Here you can use SID to look up the corresponding SQL, find the corresponding lock object

2.4 Other Lock statements

The following reference since: blog.csdn.net/u011019491/… You can read the original text

Query which users, manipulate which tables to lock up

SELECT
	s.username,
	decode(l.TYPE, 'TM'.'TABLE LOCK'.'TX'.'ROW LOCK'.NULL ) LOCK_LEVEL,
	o.owner,
	o.object_name,
	o.object_type,
	s.sid,
	s.terminal,
	s.machine,
	s.program,
	s.osuser
FROM
	v$session s,
	v$lock l,
	all_objects o
WHERE
	 l.sid = s.sid
	AND l.id1 = o.object_id(+)
	AND s.username is NOT Null
         
Copy the code

–> V$Lock

Locate the locked table and the session ID of the locked table

select a.session_id ,b.* from v$locked_object a,all_objects b where a.object_id=b.object_id
Copy the code

Check the corresponding SQL statement

SELECT
	vs.SQL_TEXT,
	vsess.sid,
	vsess.SERIAL #,
	vsess.MACHINE,
	vsess.OSUSER,
	vsess.TERMINAL,
	vsess.PROGRAM,
	vs.CPU_TIME,
	vs.DISK_READS 
FROM
	v$sql vs,
	v$session vsess 
WHERE
	vs.ADDRESS = vsess.SQL_ADDRESS 
	AND vsess.sid = 36
        
        
Copy the code

Supplementary statement:

Select * from V$DB_OBJECT_CACHE;
SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='Process owner'AND LOCKS! ='0';

Select * from V$ACCESS; select * from V$ACCESS;
SELECT * FROM V$ACCESS WHERE OWNER='Process owner' AND NAME='Name of procedure just checked';

SQL > select V$SESSION from V$PROCESS
SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='SID I just checked out'
SELECT SPID FROM V$PROCESS WHERE ADDR='PADDR';
Copy the code

Slow query optimization

3.1 SQL part

// Avoid the in operationOracle will attempt to convert in into a join of multiple tables. If the conversion fails, the subquery in will be performed first, and then the external query will be performed// Avoid not inRegardless of the database, this is generally not recommended and skips indexes (as well as is)nullAnd notnull)

// Avoid <>Similarly, don't go to the index// ** cannot use index ** for fields processed by functions

// Associated query- Use Where statements to minimize the result set of a single table, use aggregate functions to aggregate the result set before associating it with other tables - use right joins// Filter where instead of havingSQL > select * from 'where' where (select * from 'where'); SQL > select * from 'where' where (select * from 'where'); SQL > select * from 'where' where (select * from 'where')// like operatorThe like operation can be replaced by instr// the union operatorUse the union ALL operator instead of union, because the union ALL operation simply merges the two results and returns// SQL execution guarantees consistencyThe concept of SGA is involved// The order of conditions after where is affectedThis is not a full table index issue, but rather a CPU usage issue due to where multiple conditions are compared// Query table order effect- Table out of order can result in costly server data crossing// Other options include the following
@ https://www.jb51.net/article/97515.htm

@ https://www.jb51.net/article/23071.htm

@ https://www.jb51.net/article/40281.htm

Copy the code

4. Performance optimization

Pretty embarrassed!! Are copied from the book!! And most of them haven’t done it yet!!

Oracle after all, contact is limited, even if most of the PROBLEMS are SQL, performance optimization also encountered a few times, resulting in a lot of methods learned, actually used a few, but I have written down!! 😜 😜 😜

4.1 Overall performance optimization process

Here directly quote the results of other people’s articles, no test, for reference only!

// PS: initialization time 49.41

// Increase SGA Buffer Cache and SGA Shared Pool -> 48.57- Increasing the SGA buffer does not seem to improve performance significantly, only load time1.73%

// Redo Cache and Redo Log Files -> 41.39- Loading time has been improved17.35%, TPS also increased9.33%. Because loading and simultaneous inserts, updates, and deletions require more space than 8M - but it doesn't look like increasing memory performance will improve significantly// Increase Database Block Size (2K-4k) -> 17.35- Loading time has been improved138%! It has no significant effect on TPS valueTablespaces Local -> 15.07- TPS slightly improved// Database Block Size increases (4K-8K) -> 11.42- TPS continues to improve with a big differenceIo_slaves -> 10.48
dbwr_io_slaves 4\
lgwr_io_slaves (derived) 4

// Optimize the Linux kernel -> 9.40As you can see, the kernel version is optimized and the performance is improved// Adjust virtual child memory -> 5.58
- /ect/sysctl.cong
    -> vm.bdflush = 100 1200 128 512 15 5000 500 1884 2
   
    
    
Copy the code

This process can’t be used as a benchmark, but it can be used as an idea to optimize Oracle, and you can see the performance improvement

4.2 Hardware Optimization

This is the I/O Calibration, which can be used to measure the I/O performance of the database. Different strategies can be used by analyzing the I/O results

// Step 1: Determine the parallelism configuration (usually 2 times the number of cores)
show parameters parallel_thread

// Step 2: Determine the parallelism policy (auto: Oracle determines parallelism based on the nature of the operation to be performed and the size of the object)- Show parameters parallel_degree_policy - Alter session set PARALLEL_degree_policy ='auto'

// Step 3: Check parallelism data- Enable the output function set by default: set ServerOutput on - View details: set ServerOutput on DECLARE LAT INTEGER; iops INTEGER; mbps INTEGER; BEGIN -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (disk_count,max_latency , iops, mbps, lat); DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2.10, iops, mbps, lat);
 
  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
  dbms_output.put_line('max_mbps = ' || mbps);
end;
/


Ora-56708: could not find any data file with asynchronous I/O functionSync: show parameter filesystemio_options - Set sync: filesystemio_options - ASYNCH: Make Oracle support file Asynchronous (Asynchronous) io-directio: make Oracle support file Direct io-setall: make Oracle support file Asynchronous IO and Direct io-none: Cause Oracle to disable support for Asynchronous IO and Direct IO1> alter system set filesystemio_options=setall scope=spfile;
    2> shutdown immediate;
    3> startup
    // PS: Pay attention to the administrator permissions


alter system set filesystemio_options=none scope=spfile;
Copy the code

5. Conceptual supplement

5.1 SGA

The System Global Region (SGA) is a set of shared memory structures, called SGA components, that contain data and control information for an Oracle database instance. SGA is shared by all servers and background processes. Examples of data stored in SGA include cached blocks of data and shared SQL areas.

Components:

Database buffer cache: Data cache
- Before querying or modifying data stored in a database, data must be read from disk and stored in a buffer cache.
- All user processes connected to the database share access to the buffer cache.
- For best performance, the buffer cache should be large enough to avoid frequent disk I/O operations.
Shared pool: Shared pool Caches information shared by users, including the following information
- Reusable SQL statements
- Information from data dictionaries, such as user account data, table and index descriptions, and privileges
- Stored procedures, which are executable code stored in a database
Redo log Buffer: This buffer improves performance by caching Redo information until it can be written to a physical online Redo log file stored on disk
Large pool: This optional area is used to buffer Large I/O requests for various server processes
Java Pool: The Java pool is the area of memory used for all session-specific Java code and data in the Java Virtual Machine (JVM)
Streams Pool: Streams pools are the memory areas used by the Oracle Streams feature
Result cache: indicates the query Result of the cache buffer. If you run a query that stores the results in the results cache, the database will return the query results from the results cache rather than rerun the query.

conclusion

The author only conducted Oracle optimization based on business requirements, and did not go deep into Oracle business optimization. If you are interested, you can see “Oracle Database performance optimization Methodology and Best practices” for systematic optimization of the database

We will probably continue to dig into Oracle details.

Checking documentation is too bad, but Oracle documentation is perfect and easy to use

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Note: Troubleshooting steps for Oracle slow query

A. The preface

2. Troubleshooting Procedure

2.1 Querying Slow Querying logs

2.2 Viewing Indexes

2.3 Checking lock contention status

2.4 Other Lock statements

Slow query optimization

3.1 SQL part

4. Performance optimization

4.1 Overall performance optimization process

4.2 Hardware Optimization

5. Conceptual supplement

5.1 SGA

conclusion

Reference documentation (thank you very much)

Note: Troubleshooting steps for Oracle slow query

A. The preface

2. Troubleshooting Procedure

2.1 Querying Slow Querying logs

2.2 Viewing Indexes

2.3 Checking lock contention status

2.4 Other Lock statements

Slow query optimization

3.1 SQL part

4. Performance optimization

4.1 Overall performance optimization process

4.2 Hardware Optimization

5. Conceptual supplement

5.1 SGA

conclusion

Reference documentation (thank you very much)

Related Posts

Use Spring to integrate MyBatis to create the required Mapper object

HttpMessageConverter Message converter

How to use bitwise computing elegantly to fulfill product requirements?