how to optimize sql query using execution plan

Basic elements (time, user, problem) the user in the 11G environment has a section of SQL statements in the program is very inefficient execution, but in plsql execution is very fast, by looking at the execution plan, found that the use of different indexes caused, the progr

Tags: order experience various types of linked list resource management share picture ODI context resource allocation2. Can optimize the execution of the statementThe common feature of the optimized statements is that they are processed by the query compiler to generate an inquiry plan tree, which is handled by the exe

providing better performance, is slow performance because it does not apply to the current query. Basic knowledge of execution plan caching 1, what is the execution plan cache After the SQL Server

Tags: arc requires IDE number Server index execute execution plan log The source of this article: http://www.cnblogs.com/wy123/p/6008477.html For statistics on the number of rows of data estimates, previously written on the non-related columns (separate or separate index columns) of the algorithm, refer to here. 　　Today, let's write the statistical information on the calculation methods and potential probl

Three Join strategies in the execution planThere are three join strategies for SQL Server: Hash join,merge join,nested Loop join.hash Join: Used to handle data that is not ordered/not indexed, it creates a hash table in memory of the data (the associated key) on both sides of the Join. For example, with the following query statement, the associated two tables are

100336 lines, we remember this number Another feature of statistics is that statistics are automatically created on query columns (non-indexed columns), as followsDuring query execution, a statistic named: _wa_sys_00000003_24e8431a is created automatically.This statistic is the statistic for the COL3 column, and you can find the number of rows that are greater

The performance of SQL query statements affects the performance of the entire database to a certain extent. In many cases, poor database performance is almost caused by poor SQL statements. SQL statement executionThe Plan determines how the

Mysql5.6 provides a trace for SQL. the trace file can be used to better understand why Execution Plan A is selected rather than execution plan B, which helps us better understand and optimize its behavior. Usage: first, enable tra

inevitably lead to a large number of execution plans. This may not only cause heavy CPU consumption for recompilation, but also cause memory pressure, even if the execution plan is cached too much than the BUCKET limit, it takes more time to match the execution plan in the

further discussion, the preferred clarification was that the scan was not always bad, and the lookup was not always good, but in most cases, the lookup would have a better performance when it returned a small amount of data, especially in large tables. Similarly, there is not always a way to remove a scan operation in each query. If the query performance problem is because of scanning, then the removal of

Brief introduction The nature of the SQL statement we write is simply the logic to get the data, not the physical path to get the data. When we write SQL statements to SQL Server, Query Analyzer parses statements sequentially (Parse), bindings (BIND), Query optimizations (o

the physical memory. If the maximum memory is set, it depends on the maximum memory ). The internal pressure is because the objects in the execution plan cache exceed a certain threshold. For example, the 32-bit SQL Server has a threshold of 40000, And the 64-bit SQL Server has a threshold of 160000. The cached iden

Background:A table suddenly appears query very slow situation, cost 100+ seconds or more, seriously affect production.Original sql:Explain plan for SELECT * FROM (select ID id,ret_no retno, From_sys fromsy, To_sys Tosys, Command_code Commandcode, Comman D, STATUS, Ext_code, Orign_code origncode,error_message errormessage, Re_f, ret_msg retmsg from Interface_table where (co Mmand_code in (' Aasss ') and sta

consider this parameter or option, which you can use hint as shown in the query in Listing 1.SELECT * from Sales.CustomerWHERE customerid>20000 and TerritoryID = 4OPTION (RECOMPILE)Code Listing 1. Using recompileExcept we can manually prompt for SQL Server recompilation, SQL Server will also automatically recompile un

often. In addition, a optimize for unknown parameter has been added since SQL Server 2008, which makes it possible to probe the values of local parameters as predicate conditions in the process of optimizing queries, rather than probing statistics based on the initial values of local variables. Using local variables instead of stored procedure parameters in st

Preface: In most cases, a table scan or index scan is not an efficient way to return a small amount of data, especially from a large table. These must be found and resolved to improve performance because the scan traverses each row, finds the eligible data, and returns the results. This kind of processing is quite time-consuming and resource-consuming. In the performance optimization process, generally focused on: 1, CPU 2, Network 3. Disk IO The scan operation increases the cost of these three

compilation costs. Therefore, you can consider this parameter or option, you can use Hint as shown in the query in code list 1. SELECT * FROM Sales.Customer WHERE CustomerID>20000 AND TerritoryID = 4 OPTION (recompile) Code List 1. Use Recompile In addition to manual prompts for SQL Server re-compilation, SQL Server will automatically re-compile under

needs. This is why creating a nonclustered index with a containing column facilitates the query. In fact, if you put your mouse on the "merge Connection" icon of the graphical execution plan, you will receive a "match from two input tables that have already been sorted, using their sort order." This tells us that the

Auto: http://kenchell.blog.163.com/blog/static/26088309200941035417557/ I was useless when I first started using SQL Server.Show execution planTo analyze the query. I have always thought that all the SQL queries I submitted are optimal, while ignoring the

I was useless when I first started using SQL Server.Show execution planTo analyze the query. I have always thought that all the SQL queries I submitted are optimal, while ignoring the query performance, so I did not pay enough att