|
| |
| | Query plan - Wikipedia, the free encyclopedia |
 | | When a query is submitted to the database, the query optimizer evaluates all of the different possible plans for executing the query and returns what it considers the best alternative. | | | The query optimizer will not always choose the best query plan for a given query. | | | In some databases the query plan can be reviewed, problems found, and then the query optimizer given hints on how to improve it. |
|
http://en.wikipedia.org/wiki/Query_plan
|
|
| |
| | CA Open Source Project Site - Ingres Query Execution Facility |
| | The execution algorithms of Ingres query plans are, of course, dictated by the structure of the query plans. | | | Query plans consist of query trees corresponding to the separately optimized subqueries identified by OPF and action headers that tie the query trees together. | | | Action headers operate on data returned from query trees and can perform complex operations such as aggregate computations and the update/delete of identified rows or as simple as projecting the result row image requested by the query from the query tree. |
|
http://opensource.ca.com/projects/ingres/documents/technical/qef
|
|
| |
| | SQL Server Query Execution Plan Analysis |
| | The Query Analyzer is not the only tool that can generate and display query execution plans for queries. | | | But if it does, then the Query Optimizer will be unable to create an execution plan for the query and an error will be generated. | | | When you examine a graphical SQL Server query execution plan, one of the more useful thing to look for is how indexes were used (if at all) by the query optimizer to retrieve data from tables from a given query. |
|
http://www.sql-server-performance.com/query_execution_plan_analysis.asp
|
|
| |
| | MER Systems Inc. |
| | But in optimizing a query, the user must be cognizant of the base table(s) participating in the view. | | | Thus the PLAN writer must be familiar with the query which defines the view, and tell the optimizer how to retrieve the base tables in the view. | | | The plan clause itself can then be placed at the end of the query, guaranteeing that the plan will always be used by the query in retrieving the result set. |
|
http://www.mers.com/DJ1.HTML
|
|
| |
| | [No title] |
| | A query optimizer in a database system is responsible for transforming an SQL query into an execution plan. | | | The cost of a query plan depends on many parameters such as predicate selectivities, available memory, and presence of access paths, whose values may not be known at optimization time. | | | This solution is minimally intrusive in the sense that an existing query optimizer can be used as a subroutine with minor modifications: the solution invokes the conventional query optimizer multiple times, with different parameter values. |
|
http://www.cse.iitb.ac.in/~aru/Thesis/Abstract.txt
|
|
| |
| | Performance Tips |
| | In these queries, the time for the top plan node essentially is the time spent computing the new tuples and/or locating the old ones, but it doesn't include the time spent making the changes. | | | In some query plans, it is possible for a subplan node to be executed more than once. | | | Choosing the right plan to match the query structure and the properties of the data is absolutely critical for good performance. |
|
http://www.ica.org.ve/guidox/bd/postgres/performance-tips.html
|
|
| |
| | [No title] |
| | A canonicalized query plan consists of nodes which are subclasses of the Node class. | | | The logical query plan manager gets a canonicalized query and translates it into a logical query plan. | | | Thu Aug 14 10:08:31 PDT 1997 Construction of the logical query plans ======================================= This document is valid as of 8/14/97. |
|
http://www-db.stanford.edu/~mchughj/loreimpl/queryproc.txt
|
|
| |
| | BUG: SQL Server Books Online Incorrectly States That the Age Field of a Query Plan Is Incremented by Compilation Cost ... |
| | For example, if a query plan has a cost factor of 8, the age factor of the query plan is reset to 8 every time the query plan referenced for reuse. | | | The "Aging Execution Plans" section in the "Execution Plan Caching and Reuse" topic of SQL Server Books Online incorrectly states that each time a query plan that resides in the procedure cache is referenced by a connection, the age field of the query plan object is incremented by the compilation cost factor. | | | Each query plan and execution context has an associated cost factor that indicates how expensive the structure is to compile. |
|
http://www.sunsite.org.uk/sites/ftp.microsoft.com/MISC/KB/en-us/825/728.HTM
|
|
| |
| | Query plan not using index for some reason. Free discussion |
| | The query is on two tables, both of which have indexes. | | | EXPLAIN for the query without the OR clause the planner uses the index. | | | QAIX > PostgreSQL database development > Query plan not using index for some reason. |
|
http://www.qaix.com/postgresql-database-development/217-517-query-plan-not-using-index-for-some-reason-read.shtml
|
|
| |
| | The Tukwila Execution Interface |
| | Once a query has been submitted to the execution system, it is expected that the optimizer will hold the socket open for the duration of query execution. | | | This status should be used as the basis for the optimizer's decision to terminate successfully, terminate with error, or re-optimize a query. | | | The Tukwila query processing components are designed to be self-contained modules that can be swapped out as needed. |
|
http://data.cs.washington.edu/integration/tukwila/tukwila_query.html
|
|
| |
| | Why is Stored Proc plan slower than query plan |
| | Apparently, the reasonn for the difference in query plan was caused by the non-standard handling of NULLs. | | | The only non-standard thing I noticed about the query was the NULL comparison (= NULL instead of IS NULL, and <> NULL instead of IS NOT NULL). | | | The query is complex and has multiple sub-queries that contain aggregates so I assume that may be a contributing factor. |
|
http://www.mcse.ms/message306009.html
|
|
| |
| | Query optimization: articles and papers |
| | It is shown how this optimization considerably improves the efficiency of evaluating SQL queries, especially queries that have query graphs with a large number of query blocks (which is a typical situation when queries are defined in terms of multiple views and sub-queries). | | | We consider the problem for views and workloads that consist of equality-selection, project and join queries, and show that the complexity of the problem depends crucially on the quality of the estimates that a query optimizer has on the size of the views it is considering to materialize. | | | When a query optimizer has good estimates of the sizes of the views, we show that an optimal choice of views may involve a number of views that is exponential in the size of the database schema. |
|
http://www.sqlsummit.com/Optimization.htm
|
|
| |
| | What exactly is a query plan (or a SQL plan for that matter) |
| | The plan is akin to you being told where in a book your answer is. If you are told it is on page 23, then you would probably "plan" the binary searching algorithm. | | | If it had to recompile the plan each time the query was run and you ran this query several tims a second you would be losing significant CPU speed. | | | Now if you only run this query a couple times an hour you are not going to benefit as much from having the plan cached. |
|
http://www.codecomments.com/message377227.html
|
|
| |
| | PRB: Access Violation Exception Occurs When Optimized Query Plan Uses Hash Match Team Operator |
| | If you have a query that fails with an access violation exception, verify the query plan to determine if a Hash Match Team operator is involved. | | | If the query plan uses Hash Team operators during query optimization, an access violation exception may occur in a SELECT statement. | | | Trace flag 8679 prevents the SQL Server optimizer from using a Hash Match Team operator during query optimization. |
|
http://support.microsoft.com/default.aspx?scid=kb;en-us;818671
|
|
| |
| | [No title] |
| | A plan to execute a query where at least one indexable predicate must match the first column of an index (known as matching predicate, matching index). | | | In-list is special because it is considered a sequence of equal matching predicates that the query optimizer agrees to bridge in the access plan C1 in (6, 8, 10) and C2 = 5 and C3 = 20 is like C1 = 6 and. | | | It is an interesting query optimization problem whether this is worth it. |
|
http://www.cs.umb.edu/~poneil/dbppp/notes/chap9.doc
|
|
| |
| | Microsoft SQL Server: SQL Query Plan |
| | We have a couple of queries on our servers that when a specific parameter is passed is queried on the query will go into a state of hanging the server for ever. | | | When I use 47 on the query the query will display that it is going do some sort of parrallelism, when I run the query with any other number it doesn't do that. | | | One of the queries is something like give me the invoices for a customer where the salesman is a specific number. |
|
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20330539.html
|
|
| |
| | EXPLAIN |
| | The execution plan shows how the table(s) referenced by the query will be scanned---by plain sequential scan, index scan, etc.---and if multiple tables are referenced, what join algorithms will be used to bring together the required tuples from each input table. | | | The most critical part of the display is the estimated query execution cost, which is the planner's guess at how long it will take to run the query (measured in units of disk page fetches). | | | Keep in mind that the query is actually executed when ANALYZE is used. |
|
http://www.ica.org.ve/guidox/bd/postgres/sql-explain.html
|
|
| |
| | lab2 |
| | Range query processing stratagies are discussed in section 18.2.2 of the text book. | | | Let Q1 be the POPULATION based query which retrieves a couple of tuples and Q2 be the POPULATION based query which retrieve most of the tuples. | | | Derive an algebric condition on the selectivity of range query in terms of record size, block size, key-value size, pointer value size and depth of secondary index to quantify your arguement. |
|
http://www-users.cs.umn.edu/~shekhar/5708/labs/lab2.html
|
|
| |
| | dBforums - dropping constraint changes query plan |
| | Otherwise post the query and plan and we'd be able to give you better answer. | | | query plan, but we aren't, so if you are asking why SQL Server chose a specific query | | | analyzying an execution plan for a long-running query, we were able to force the |
|
http://www.dbforums.com/t407098.html
|
|
| |
| | Analysing a query-plan |
| | Complex queries produce complex query plans - pay careful attention to the rows part. | | | A query is running much slower than you expected. | | | The EXPLAIN command displays the query plan that Postgres uses. |
|
http://www.archonet.com/pgdocs/query-plan.html
|
|
| |
| | Looking for query plan determination information |
| | I was just helping a coworker optimize a query. | | | The second query was performing horribly until I noticed the ALL keyword in the GROUP BY, which I didn't think was necessary. | | | He had two versions: one which used UNION for each value for which he was tallying results and another query which used GROUP BY. |
|
http://www.mailarchive.ca/lists/comp.databases.ms-sqlserver/2003-09/0609.html
|
|
| |
| | dBforums - Locking Plan part of Query Plan? |
| | Is this locking strategy part of the query plan, or will it be | | | If it is part of the query plan, then my next question would be: can this be |
|
http://dbforums.com/t379496.html
|
|
| |
| | dBforums - changed query plan depending on inner subquery |
| | all the simpler queries are working fine, except for this combination. | | | I have the following structure query in a datawarehouse: | | | then the query plan shows table access by index rowid on the fact table |
|
http://www.dbforums.com/t1074097.html
|
|
| |
| | Query Plan Differences |
| | query to run faster on PS, I would not expect a different query plan. | | | The same query returns in 40 minutes on the RS. | | | A query using the MAX view returns in 10 - 15 seconds on |
|
http://www.sqlmonster.com/uwe/Forum.aspx/sql-server/4431/Query-Plan-Differences
|
|
| |
| | [No title] |
| | A: (4) Many others, especially with aggregates * Since rewrites are not guaranteed to be improvements, could generate alternatives and send all of them to physical query plan generator. | | | Passing information between physical operators ---------------------------------------------- Example physical plan: Two modes of information passing: (1) TEMPORARY TABLES Create table for each interior node in the query plan. | | | (Implication: enumeration of physical plans considers only those with some correspondence to the input logical plan) (1) Push selections down - Can be bad in the (unusual) scenario of very selective join, unselective selection condition. |
|
http://www-db.stanford.edu/~ullman/dbsi/win99-346/query2.txt
|
|
| |
| | The 5-A-Day Query Plan By Christina Spence-Maharajh |
| | Your query letter is your 'handshake' to the editor - it's the best way you have of introducing yourself to them. | | | Allot a time every day for your queries, and stick to it. | | | Every day make it your goal to send five queries. |
|
http://www.writersweekly.com/articles/122601-01.html
|
|
| |
| | dBforums - Query plan or a bug |
| | query, but is it possible that the rows that aren't inserted | | | We have a query where we try to insert data from Table A | | | The above query sometimes does not select some of the |
|
http://dbforums.com/t883260.html
|
|
| |
| | Query Plan |
| | If PG is expecting say 50 rows in the first query but 150 in the second then it might decide a sequential scan is quicker. | | | I have a basic statement This query uses the Index Scan: explain analyse select * from tablea where columna_id < 57 This query uses Seq Scan: explain analyse select * from tablea where columna_id < 58 There are about 5000 records in this table, and columna_id is the primary key, which is Indexed... | | | 09-09-04 07:47 AM Have a question regarding when a Query Plan uses an Index. |
|
http://www.webservertalk.com/message385006.html
|
|
| |
| | How to access the query plan without running the query |
| | There are two ways of seeing the plan without running the query. | | | Problem: Is there a way to get the query plan without actually running the query in WISQL? | | | How to access the query plan without running the query |
|
http://community.borland.com/article/0,1410,25634,0.html
|
|
| |
| | FIX: SELECT Statement May Generate Incorrect Results When Hash Join Is Used in the Parallel Query Plan (274765) |
| | NOTE: Research indicates that we cannot tell what kind of query will encounter the problem based on the query syntax, indexes, and query plan. | | | Disable the parallel plan for this particular query by using a hint: | | | This hint overrides the max degree of parallelism configuration option (of sp_configure) only for the query specifying this option, and suppresses parallel plan generation. |
|
http://wwwtest.sunsite.org.uk/sites/ftp.microsoft.com/MISC/KB/en-us/274/765.HTM
|
|
| |
| | Printing the Query Plan on Developer Newsgroups |
| | Am in the process of analysing the query plans for poor performing Stored | | | You can save this information in a table, query it, etc. | | | As the font size increases - the number of query plans printed |
|
http://www.devnewsgroups.net/group/microsoft.public.sqlserver.tools/topic5367.aspx
|
|
| |
| | Re: [HACKERS] wrong query plan in 7.1beta3 |
| | The query you can see > > below worked fast in 7.0.2 but in 7.1beta3 is rather slow. | | | Therefore the row > estimate is way off (1000 vs 6) and thus a sequential scan is (correctly) > thought to be faster. | | | On Sat, 27 Jan 2001, Peter Eisentraut wrote: > Kovacs Zoltan writes: > > > There seems to be an optimizer problem in 7.1beta3. |
|
http://postgres.pike.ru/mhonarc/pgsql-sql/2001-01/msg00431.html
|
|
| |
| | dBforums - Different query plan, same basic query |
| | It is strange however that the optimizer chooses two different plans for these almost similar queries... | | | Since the two queries pull from the same view, I do find it odd that one performs so differently than the other. | | | I've seen several posts that mention avoiding hints in queries until you've found them to be the appropriate approach. |
|
http://www.dbforums.com/t1120695.html
|
|
| |
| | query plan |
| | I ran a sql query plan on query analizer, I need to send to someone by | | | Or you can send the textual outupt of the "query plan" by | | | >I ran a sql query plan on query analizer, I need to send to someone by |
|
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/2769/query-plan
|
|
| |
| | Optimizing Parameterization for Query Plan Reuse |
| | These two statements would benefit from reusing the same query plan; however, because the statements’ text is different, SQL Server doesn’t consider the statements to be the same and doesn’t reuse the plan. | | | By definition, stored procedures and their query plans are reusable. | | | Several techniques let you write applications in a way that lets the query optimizer reuse the same plan for similar statements. |
|
http://www.itcareerpath.com/Article/ArticleID/44558/44558.html
|
|
| |
| | Re: query plan is bad when it is run inside a pl/sql stored |
| | Re: query plan is bad when it is run inside a pl/sql stored | | | Is the sql really "the same query is run from a stored procedure" or is it perhaps using in place of the '%TATA.COM' a plsql variable (which is set to %TATA.COM)? | | | Home -> Community -> Mailing Lists -> Oracle-L -> Re: query plan is bad when it is run inside a pl/sql stored |
|
http://www.orafaq.net/maillist/oracle-l/2004/01/27/0468.htm
|
|
| |
| | MySQL Lists: mysql: query plan |
| | Hi all, How can I get the query plan output? | | | query problem after transfer from 4.0.x to 3.23.x - Salzgeber Olivier, August 20 2004 10:49am | | | Re: query plan - Egor Egorov, August 23 2004 2:30pm |
|
http://lists.mysql.com/mysql/171358
|
|
| |
| | [No title] |
| | // stores computed query plan, executor uses this to process query | | | * entry for the local query it uses. | | | A PlanProxyOperator 'covers' the query operator and holds index |
|
http://guir.berkeley.edu/projects/confab/javadoc/src-html/edu\berkeley\guir\confab\query\QueryPlan.html
|
|
| |
| | SQL Server - Local partitioned view changes query plan |
| | Explain plan shows that the optimizer chooses the correct index on the | | | I have am querying a local partitioned view in a data warehouse type | | | When I execute the same query directly on the partitioned table |
|
http://www.webservertalk.com/archive132-2004-2-126578.html
|
|
| |
| | Display of sort info on Query Plan |
| | Problem: I noticed that in InterBase 5.0, I now can see information about sorts in the query plan. | | | Is this just info displayed or does the optimizer consider the costs of sorting when it figures out a strategy for executing a query? | | | Solution: /*******************************************************************************/ Note: The information in this article applies to InterBase v5.x /********************************************************************************/ Yes, the optimizer does consider the cost of sorts when devising the plan for running a query. |
|
http://www.coadletter.com/article/0,1410,25470,00.html
|
|
| |
| | ??? how to interpret query plan |
| | I know how to generate the query plan but how to interplate the query plan, really? | | | However, if the query plan shows me something else, I don't know what to improve. | | | The only thing I know is if there is a FULL TABLE SCAN, that most probably is bad, need to build an index. |
|
http://www.orafaq.net/maillist/oracle-l/2001/05/11/1126.htm
|
|
| |
| | Cannot create query plan file with V8 - Pervasive |
| | run the query from SQL Data Manager the.QPF file is not created. | | | I am attempting to use the Query Plan Viewer but cannot create the | | | have also tried running the query with the PDAC components and with the |
|
http://www.database-forum.com/ntopic1981.html
|
|
| |
| | iAnywhere.com - QUERY_PLAN_ON_OPEN option [compatibility] |
| | For this reason, computing and returning the query plan on an OPEN is needed only for compatibility with old applications. | | | In early versions of the software, each time an OPEN was done on a cursor, the server would return in the SQLCA sqlerrmc field a string representing the query plan (limited to 70 bytes). | | | Controls whether a plan is returned when a cursor is opened. |
|
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0901/en/html/dbdaen9/00000727.htm
|
|
| |
| | Re: Looking for query plan determination information |
| | > that explain how the query optimizer choices its query paths? | | | > Before I had noticed that I was scouring over the query plans and | | | : Thomas R. Hummel: "Re: Looking for query plan determination information" |
|
http://www.mailarchive.ca/lists/comp.databases.ms-sqlserver/2003-09/0645.html
|
|
|
