What should I do if Oracle query is slow?

Solutions to slow Oracle queries: 1. Put data, logs, and indexes on different I and O devices to increase reading speed; 2. Upgrade hardware; 3. Increase network speed; 4. Vertical , Split the table horizontally to reduce the size of the table.

Solution to slow Oracle query:

1. Put data, logs, and indexes in different I /O device, to increase the reading speed, Tempdb could be placed on RAID0 in the past, but SQL2000 no longer supports it. The larger the data volume (size), the more important it is to improve I/O.

2. Split the table vertically and horizontally to reduce the size of the table (sp_spaceuse)

3. Upgrade the hardware

4. Based on the query conditions, create indexes, optimize indexes, optimize access methods, and limit the amount of data in the result set. Note that the fill factor should be appropriate (it is best to use the default value of 0). The index should be as small as possible. It is better to use columns with smaller bytes to build indexes (refer to index creation). Do not build a single index for fields with a limited number of values, such as gender fields.

5. Improve network speed;

6. Expand the memory of the server. Windows 2000 and SQL server 2000 can support 4-8G of memory. Configure virtual memory: The virtual memory size should be configured based on the services running concurrently on the computer. When running Microsoft SQL Server® 2000, consider setting the virtual memory size to 1.5 times the physical memory installed in the computer. If you have additionally installed the full-text retrieval feature and plan to run the Microsoft Search service to perform full-text indexing and queries, consider configuring the virtual memory size to be at least 3 times the physical memory installed in the computer. Configure the SQL Server max server memory server configuration option to 1.5 times the physical memory (half the virtual memory size setting).

7. Increase the number of server CPUs; but you must understand that parallel processing and serial processing require more resources such as memory. Whether to use parallel or serial processing is automatically evaluated by MsSQL. A single task is broken down into multiple tasks that can be run on the processor. For example, the sorting, connection, scanning and GROUP BY clauses of delayed queries are executed at the same time. SQL SERVER determines the optimal parallel level according to the load of the system. Complex queries that require a large amount of CPU are most suitable for parallel processing. However, the update operations Update, Insert, and Delete cannot be processed in parallel.

8. If you use like to query, simply using index will not work, but full-text indexing consumes space. like 'a%' Use index like '%a' When querying like '%a%' without using index, the query time is proportional to the total length of the field value, so the CHAR type cannot be used, but VARCHAR. Build a full-text index for fields with very long values.

9. Separation of DB Server and APPLication Server; separation of OLTP and OLAP

10. Distributed partition view can be used to implement database server union. A federation is a group of servers that are managed separately but work together to share the processing load of the system. This mechanism of partitioning data to form a database server complex can scale a set of servers to support the processing needs of a large, multi-tier Web site. For more information, see Designing a Federated Database Server. (Refer to the SQL help file 'Partitioned View')

a. Before implementing the partitioned view, you must first partition the table horizontally

b. After creating the member table, define it on each member server A distributed partitioned view, with each view having the same name. In this way, queries that reference the distributed partitioned view name can be run on any member server. The system operates as if there is a copy of the original table on each member server, but in fact there is only one member table and one distributed partitioned view on each server. The location of the data is transparent to the application.

11. Rebuild indexes DBCC REINDEX, DBCC INDEXDEFRAG, shrink data and logs DBCC SHRINKDB, DBCC SHRINKFILE. Set automatic shrink logs. For large databases, do not set automatic database growth, as it will reduce server performance.

Related learning recommendations: oracle database learning tutorial

The above is the detailed content of What should I do if Oracle query is slow?. For more information, please follow other related articles on the PHP Chinese website!