oracle sum up the reasons for the slow query

Jun 26 posted in Oracle (RSS), 554 views.

Inquiries are many reasons for slow, frequently asked the following:

1, there is no index or index is not used (this is the most common query slow problem is programming defects)

2, I / O throughput in a small, forming a bottleneck effect.

3, did not lead to the creation of calculations are shown in query optimization.

4, memory 5, slow the speed of the network 6, a data query too (many queries can be used, other ways to reduce the amount of data)

7, lock or deadlock (which is the most common query slow problem is programming defects)

8, sp_lock, sp_who, the activities of the user to view, read and write due to competition for resources.

9, to return to the unnecessary rows and columns 10, a bad query, there is no optimization

Through the following methods to optimize the query:

1, the data, log, index on different I / O devices to increase the read speed can be Tempdb ago RAID0 should be placed on, SQL2000 not support. The amount of data (size) the greater the increase the I / O more important.

2, vertical, horizontal partition table, reducing table size (sp_spaceuse)

3, 4 hardware upgrade, according to query, indexing, optimizing the index, and optimize access to restrict the result set the amount of data. Attention to the appropriate fill factor (the best is to use the default value 0). Index should be as small as possible, use a small number of bytes out to build a good index (with reference to the creation of the index), not to a limited number of field values to build a single index such as the gender field 5, to increase speed;

6, the expansion of server memory, Windows 2000 and SQL server 2000 to support 4-8G of memory. Configure virtual memory: virtual memory size should be based on complicated computer configured to run the service. Running Microsoft SQL Server? 2000 may be taken into account the size of the virtual memory settings for the computer installed in the 1.5 times the physical memory. If another installation of full-text search function, and intended to run the Microsoft search service for the implementation of full-text indexing and searching, may wish to consider: the size of virtual memory will be configured to at least installed on your computer 3 times the physical memory. The SQL Server max server memory server configuration option is configured to 1.5 times the physical memory (virtual memory size set to half).

7, increasing the number of server CPU; but it is necessary to understand the serial parallel processing resources such as the need to deal with more memory. The use of parallel or series itinerary is automatically evaluates MsSQL choice. A single task into multiple tasks, you can run in the processor. For example, to sort the query delay, connectivity, and GROUP BY scan everything at the same time, SQL SERVER load of the system determine the optimal level of parallelism, the complexity of the needs of the CPU consumption of a large number of inquiries from the most suitable for parallel processing. But the update Update, Insert, Delete not parallel processing.

8, if the query is to use words like, the simple use of index does not work, but the full-text index, consumption of space. like 'a%' use of the index like '% a' non-use of the index used like '% a%' query, the query time-consuming and in direct proportion to the total length of field values, so can not use CHAR type, but VARCHAR. Value for the field to build a long full-text index.

9, DB Server and APPLication Server separation; OLTP and OLAP separation 10, the distributed partition view can be used to achieve the Commonwealth of the database server. Commonwealth is a group of separate management server, but their mutual cooperation to deal with load-sharing system. Data through the formation of such a database server partition mechanism of the Commonwealth to expand a group of servers to support large-scale multi-processing needs of Web sites. For more information, see the design of a joint database servers. (See SQL Help file 'geographical view')

a, in achieving district view before the level of the partition table b, after the founding member, each member server in a distributed partition view definition, and each view has the same name. In this way, to quote the view of the distributed query partition on any one member can run the server. System server, as each member has a copy of the original table, but in fact there is only one of each member of the server table and a distributed partition view. The location of data on the application process is transparent.

11, the reconstruction of the index DBCC REINDEX, DBCC INDEXDEFRAG, contraction data and the log DBCC SHRINKDB, DBCC SHRINKFILE. Settings automatically shrink the log. For large databases do not automatically set the database growth, it will reduce the performance of the server. T-sql in the wording there is a lot of stress, common elements are listed below: First of all, DBMS query plan to deal with the process is as follows:

1, the lexical query, syntax check 2, the statement submitted to the DBMS query optimizer 3, do algebra Optimizer access path optimization, and optimization of 4, generated by the pre-compiled query planning module
15, as far as possible not to use the cursor, it uses a lot of resources. If you need row-by-row implementation, as far as possible the use of non-cursor techniques, such as: the client cycle, with temporary tables, Table variables, with subquery, and so on using Case statement. Cursor can be extracted in accordance with the options it supports classification: Progressive only in accordance with the first line from the last line of the order of the extraction line. FETCH NEXT is the only allowed the extraction operation, is also a default. Scrollable cursor can be of any arbitrary line extracted random places. The cursor in SQL2000 features become very strong, his aim is to support the circulation. There are four concurrent option READ_ONLY: Update is not allowed through the cursor positioning (Update), and in the composition of the result set is not locked rows. OPTIMISTIC WITH valueS: Optimistic Concurrency Control Panel control is a standard part of the theory. Optimistic concurrency control for such a situation, that is, in the open cursor and update the line spacing, only a very small opportunity for users to update a second line. This option when a cursor is open, there is no lock to control the line, which will help to maximize their processing power. If a user attempts to modify a line, then the current value of this trip will be the last time when extraction of the values obtained. If you change any value, the server will know that other people have updated this trip and will return an error. If the value is the same, the server on the implementation of modifications. The concurrent option to choose? OPTIMISTIC WITH ROW VERSIONING: optimistic concurrency control options in this line-based version control. The use of version control line, in which the table must have a version identifier, the server can use it to determine the time line after the cursor to change. In SQL Server, this performance provided by the timestamp data type, it is a binary digit, said the database of the relative order of changes. Each database has a global current timestamp value: @ @ DBTS. Each change in any way out with a timestamp of when, SQL Server timestamp column first store the current value of @ @ DBTS, and then increase the value of @ @ DBTS. If a table has a timestamp column, the timestamp will be credited to the line level. Server on a line can compare the current timestamp value and the last extraction of the timestamp value stored in order to determine whether the bank has been updated. Servers do not have to compare the value of all out, just to compare timestamp column. If an application does not timestamp out of the form requires line-based version of the optimistic concurrency control, the cursor defaults to values based on optimistic concurrency control. SCROLL LOCKS option to achieve this pessimistic concurrency control. In a pessimistic concurrency control, in the database is read into the cursor row result set, the application will attempt to lock the database row. Cursor in the use of the server, it will be read into the cursor line will be in its place an update lock. If in the affairs within the open cursor, the Service Update lock will remain to be committed or rolled back Affairs; when extracting the next line, the cursor will lock to remove. If things in the open cursor, then extract the next line, lock on to throw away. Therefore, whenever users need to complete a pessimistic concurrency control, the cursor should be opened within the Service. Update locks will prevent access to any other task to update or exclusive locks lock, thereby preventing other tasks to update the line. However, the update lock does not prevent the sharing of locks, so it will not read the line to stop other tasks, unless the requirements of the second task is also updated with the read lock. Scroll lock in accordance with the definition of the cursor specified in the Select statement prompted the lock, which can generate the cursor concurrency options scroll lock. Scroll lock in the extraction line in each acquisition, and maintain the cursor to the next extraction or closed, whichever occurs first. The next extraction, the extraction of the server for the new access to a rolling line of locks, and Bank of China released the last of the rolling lock extract. Scroll lock lock independent of the Service, and can be maintained to a submitted or after the rollback operation. If the author the option to close the cursor is off, the COMMIT statement is not to close any open cursor, and scroll lock to be retained after the submission in order to protect the data on the extraction of isolation. Obtained depends on the type of scroll lock cursor cursor concurrency options and lock in the Select statement prompted. Numerical lock prompted optimism optimistic read-only version of the control line is not a silent lock lock lock is not locked does not update the lock does not lock NOLOCK not lock not locked not sharing sharing sharing HOLDLOCK update update update update UPDLOCK error TABLOCKX error did not update the other lock is not locked not lock not locked does not specify NOLOCK locking update * will prompt the prompt specified in the cursor within the table is read-only.

16, use Profiler to track inquiries, inquiries have been the time required to identify the problem SQL; using index optimizer to optimize the index 17, the attention of UNion and UNion all the difference. UNION all good 18, pay attention to the use of DISTINCT, if necessary, in the absence of do not use, it will make inquiries with the same slow UNION. Duplicate records in the query is no problem in 19, do not return do not need to query the rows, columns 20, with sp_configure 'query governor cost limit', or to limit the query SET QUERY_GOVERNOR_COST_LIMIT depletable resources. When the assessment of inquiry beyond the restrictions on the consumption of resources, the server automatically cancel queries in the query out before they kill. SET LOCKTIME lock time setting
21, with select top 100 / 10 Percent to limit the users or the number of rows to return to SET ROWCOUNT to limit the operation of the line 22, in SQL2000 ago, the general should not use the following words: "IS NULL", "<>", "! = ", "!>", "!<"," NOT "," NOT EXISTS "," NOT IN "," NOT LIKE ", and" LIKE '% 500' ", because they do not take the index is a table scan. Where words are not listed in the Add function, such as Convert, substring, etc., if the time function must be created to create the index calculations are shown instead. Wording can also be flexible: Where SUBSTRING (firstname, 1,1) = ' m 'changed to Where firstname like' m% '(index scan), must be separately listed functions and. And the index can not be built too much and too much. NOT IN will repeatedly scan table, the use of EXISTS, NOT EXISTS, IN, LEFT OUTER JOIN to replace, especially the left to connect, and Exists faster than IN, it is NOT the slowest operation. If the column contains the value of air, before it The index does not work, now 2000 can handle the optimizer. Is the same as IS NULL, "NOT", "NOT EXISTS", "NOT IN" to optimize her, and "<>" and so can not be optimized, less than the index.

23, using the Query Analyzer, view the SQL statement query plan analysis and evaluation is to optimize the SQL. 20% of the general code accounted for 80% of the resources, we focused on optimizing these slow place.

24, if used, such as IN or OR query is not found following the index, use the show affirms the specified index: Select * FROM PersonMember (INDEX = IX_Title) Where processid IN ( 'M', 'F')

25, will need to calculate the results of inquiries well in advance on the table, when the re-query Select. This is the most important SQL7.0 ago means. For example, calculation of hospital costs.

26, MIN () and MAX () to use a suitable index.

27, there is a principle of the database code from the data the closer the better, so preference Default, followed by Rules, Triggers, Constraint (constraint, such as outside the main Kin Kin CheckUNIQUE ... ..., the maximum length of data type and so on are bound), Procedure. This will not only safeguard the work of small, high-quality programming and faster execution.

28, If you want to insert a large binary value to the Image list, use the stored procedure, do not use embedded Insert to insert (I do not know whether JAVA). Applications because it will first convert the binary value of the string (twice the size it), the server and then by his character into a binary value. Stored procedure there is no such action: method: Create procedure p_insert as insert into table ( Fimage) values (@ image), at the front desk to call the stored procedure parameters into binary, so that significant improvement in processing speed.

29, Between the time in some IN faster than, Between able to more quickly find the area under the index. Query optimization can be used to see the difference. select * from chineseresume where title in ( 'M', 'F') Select * from chineseresume where between 'male' and 'F' is the same. Because in many times in the comparison, it is sometimes more slowly.

30, where necessary, is a global or local temporary table to create the index, and sometimes can increase the speed, but not necessarily the case, because the index also spent a lot of resources. He's created the same as the actual table.

31, not to build useless things such as generate reports, the waste of resources. The use of things only when necessary to use it.

32, with OR of words can be decomposed into a number of inquiries, and to connect through a number of inquiries UNION. Their speed is only concerned with whether or not to use the index, if the query needs to use the joint index, with the implementation of the UNION all efficient. More than the words did not use OR index, read the form UNION try and match the index. A key problem is to use index.

33, to minimize the use of view, it is inefficient. View the operation of direct-to-table operation than slow stored procedure can be used to replace her. In particular do not use nested view, an increase of nested view of the difficulty to find the original information. We see the essence of view: it is stored in the server was already well optimized query plan generated SQL. Retrieve data on a single table, do not use the point of view of multiple tables, or search directly from the table that contains only the view of the table read, or an increase of unnecessary expenses, inquiries, be interfered with. In order to speed up the query view, MsSQL increase the view of index functions.

34, there is no need not to use DISTINCT and ORDER BY, these actions can be changed on the client implementation. Their additional costs. This is the same as UNION and UNION ALL truth.

select top 20 ad.companyname, comid, position, ad.referenceid, worklocation, convert (varchar (10), ad.postDate, 120) as postDate1, workyear, degreedescription FROM jobcn_query.dbo.COMPANYAD_query ad where referenceID in ( 'JCNAD00329667' , 'JCNAD132168', 'JCNAD00337748', 'JCNAD00338345',
'JCNAD00333138', 'JCNAD00303570', 'JCNAD00303569',
'JCNAD00303568', 'JCNAD00306698', 'JCNAD00231935', 'JCNAD00231933',
'JCNAD00254567', 'JCNAD00254585', 'JCNAD00254608',
'JCNAD00254607', 'JCNAD00258524', 'JCNAD00332133', 'JCNAD00268618',
3,% Privileged Time refers to non-idle processor time percentage for the privilege mode. (Privilege mode for the operating system to manipulate hardware components and drivers designed a processing mode. It allows direct access to hardware and all memory. The other model for the user mode, which is a procedure for the application, the environment subsystem and integral subsystems designed to deal with a limited model. threaded operating system will convert the application to access the operating system privilege mode services). Privileges of the% of time for uninterrupted provision of services and DPC time. Time the ratio of the high privileges may be due to the failure of a large number of equipment caused by the interval. This counter will be the average busy time as part of the sample shows.

4,% User Time that the CPU cost of database operations, such as sorting, aggregate functions, such as the implementation. If the value of high, may consider increasing the index of the table as far as possible using a simple connection, the level of division of large forms, such as ways to reduce the value. Physical Disk: Curretn Disk Queue Length counter value should not exceed the number of disk 1.5 ~ 2 times. To enhance performance, increase the disk. SQLServer: Cache Hit Ratio counter to the value the higher the better. If less than 80% sustained, should consider increasing the memory. Note that the parameter values from the SQL Server to start after the counting has been cumulative, so after running after a period of time, the value will not be able to reflect the current value system.

47, analysis of select emp_name form employee where salary> 3000 if this statement is the Float type of salary, the optimizer to optimize for its Convert (float, 3000), because 3000 is an integer, we should use in programming 3000.0 such as run-time rather than to allow for conversion DBMS. The same character and integer data conversion.

48, the query associated with the written order of select a.personMemberID, * from chineseresume a, personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' (A = B, B = 'number')

select a.personMemberID, * from chineseresume a, personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' (A = B, B = 'number', A = ' Number ')

select a.personMemberID, * from chineseresume a, personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' (B = 'number', A = 'number')

49,

(1) IF person in charge did not enter the code THEN code1 = 0 code2 = 9999 ELSE code1 = code2 = head END IF implementation code for the SQL statement: Select name FROM P2000 Where responsible people in charge of code> =: code1 AND head of the code < =: code2

(2) IF there is no input THEN Select responsible person in charge of code names FROM P2000 ELSE code = code Select responsible person in charge is responsible for code FROM P2000 Where people code =: code END IF The first method used a SQL statement, and the second Ways to use the two SQL statements. Enter the responsible person in the absence of the code, the second approach than the first method to implement high efficiency, because it does not limit the conditions; the head of the input code, the second method than the first method is still efficient, not only a less restrictive conditions, but also because the same computing is the fastest computing query. We do not trouble to write the procedure 50, on page JOBCN now new method of inquiry (see below), using Performance Optimizer analyzes the performance bottleneck, if the I / O or network speed, as an effective way to optimize, if CPU or memory on the way to better use now. Please distinguish between the following methods that index the smaller the better.

begin

DECLARE @ local_variable table (FID int identity (1,1), ReferenceID varchar (20))

insert into @ local_variable (ReferenceID)

select top 100000 ReferenceID from chineseresume order by ReferenceID

select * from @ local_variable where Fid> 40 and fid <= 60

end and begin

DECLARE @ local_variable table (FID int identity (1,1), ReferenceID varchar (20))

insert into @ local_variable (ReferenceID)

select top 100000 ReferenceID from chineseresume order by updatedate

select * from @ local_variable where Fid> 40 and fid <= 60

begin the different end

create table # temp (FID int identity (1,1), ReferenceID varchar (20))

insert into # temp (ReferenceID)

select top 100000 ReferenceID from chineseresume order by updatedate

select * from # temp where Fid> 40 and fid <= 60 drop table # temp

end

Separate: the experience of stored procedures and optimization of the preparation of measures From: Teaching Network website

First, for readers: database development programmers, a lot of database data, involves the SP (stored procedure) to optimize the project developers, the database of people interested.

Second, introduced: in the database development process, often encountered in complex business logic and database operations, this time will be used to encapsulate database operations SP. If the project more of the SP, writing is not necessarily the norm, it will affect future system maintenance difficult and the difficult to understand the logic of SP, while the amount of data if the database is large or the project's performance requirements for the SP is, it will encounter optimization problem, otherwise there may be very slow speed, after first hand experience, an optimized performance of the SP than a poor efficiency of the SP high and even hundreds of times.

Three elements:

1, developers use other libraries if the Table or View, be sure to establish a library in the current View to achieve inter-bank operations, it is best not to direct the use of "databse.dbo.table_name", as shown sp_depends not used by the SP Cross-database table or view, check inconvenient.

2, the developer submitted to SP must have the use of set showplan on analysis of query plan, query optimization has its own inspection.

3, high process efficiency, and optimize applications, in preparation of the SP should note the following:

a) SQL regulate the use of:

i. to avoid operation of large affairs, holdlock clause be used with caution and raise the capacity of the system complicated.

ii. as far as possible to avoid repeatedly visit the same one or a few tables, in particular, the larger the amount of data table, it can consider under the conditions of extraction of data to temporary table, and then connect again.

iii. as far as possible avoid the use of the cursor, because the poor efficiency of the cursor, if the cursor operated by more than 10,000 lines of data, then it should be rewritten; if you are using a cursor, it is necessary to avoid a further cycle in the cursor table join operation.

iv. pay attention to where the words written, the order must take into account statements, the index should be based on the order of size to determine the order of conditions before and after the clause, as far as possible, so that the field order is consistent with the order index, ranging smallest.

v. not in where clause of the "=" left to function, or other expressions Arithmetic operations, otherwise the system will use the index may not be able to correct.

vi. to make full use exists instead of select count (1) to determine the existence of records, count function only in the number of rows in all tables in use, and count (1) over count (*) more efficient.

vii. ">=", Do not use as much as possible the use of ">."

viii. pay attention to some clause or clause and the union between the replacement

ix. attention to form connections between the data type, to avoid different types of data connections.

x. Note stored procedure parameters and data types in the relationship.

xi. pay attention to insert, update the amount of data operations to prevent conflicts with other applications. If the amount of data more than 200 data pages (400k), then the system will be upgraded locks, page-level lock will be upgraded into a table-level lock.

b) regulate the use of index:

i. the creation of the index combination and application to consider the proposals of the OLTP table index not more than six.

ii. as far as possible the use of the index field as a query, especially the clustering index, if necessary, to force the adoption of the designated index index_name index

iii. to avoid a great deal to carry out inquiries, table scan, when necessary, to consider the new index.

iv. in the use of the index field as a condition, if the index is the combined index, it must use the index as the first field conditions in order to ensure the use of the index, otherwise the index will not be used.

v. to pay attention to the maintenance of the index, index of cyclical renewal, re-compile the stored procedure.

c) tempdb use of norms:

i. to avoid the use of distinct, order by, group by, having, join, cumpute, because these statements will add to the burden of tempdb.

ii. to avoid the frequent creation and deletion of temporary tables, system tables to reduce resource consumption.