Stack Overflow
Stack Overflow

Questions tagged [sql-execution-plan]

A execution, or query plan, is the breakdown of steps the database engine uses to get a result.

A execution, or query plan, is the breakdown of steps the database engine uses to get a result. Specifically it is the plan that the optimizer determines is the best, most efficient, way to get the data.

The execution plan can be analyzed to determine what is happening and how to improve efficiency. Including finding if an index is being scanned or missing an index altogether. There is also many points of analytic data that can be acquired via the plan including things such as estimated and actual number of rows.

1429 questions
8
votes
5 answers

How to inline a variable in PL/SQL?

The Situation I have some trouble with my query execution plan for a medium-sized query over a large amount of data in Oracle 11.2.0.2.0. In order to speed things up, I introduced a range filter that does roughly something like this: PROCEDURE…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
8
votes
1 answer

Hive explain plan understanding

Is there any proper resource from where we can understand explain plan generated by hive completely? I have tried searching it in the wiki but could not find a complete guide to understand it. Here is the wiki which briefly explains how explain plan…
Vishnu Vardhan
  • 93
  • 1
  • 7
8
votes
2 answers

Postgresql index seq scan 100 million rows

I'm having an issue where a query that's indexed refuses to use the index, because it's not selective enough (let's say 60 out of 130 million rows meet the condition) and so decides to use a seqscan. The issue I'm facing is that the seqscan is…
Cristiano Coelho
  • 1,675
  • 4
  • 27
  • 50
8
votes
3 answers

Execution plan not as expected

I've encountered something weird which I can't explain . I'm using the following query: MERGE INTO Main_Table t USING Stg_Table s ON(s.site_id = t.site_id) WHEN MATCHED THEN UPDATE SET t.arpu_prev_period = s.arpu_prev_period …
sagi
  • 40,026
  • 6
  • 59
  • 84
8
votes
2 answers

SQL Developer explain plan broken

Trying to generate an explain plan in SQL Developer, the program puts up a message box with title "failed to query plan_table" complaining "invalid column name". The plan is not generated or displayed. How to fix?
Barett
  • 5,826
  • 6
  • 51
  • 55
8
votes
6 answers

Not Exists vs Not In: efficiency

I've always been under the assumption that not exists is the way to go instead of using a not in condition. However, I doing a comparison on a query I've been using, I noticed that the execution for the Not In condition actually appears to be…
tchock
  • 245
  • 4
  • 13
8
votes
1 answer

What is the significance of the order of statements in mysql explain output?

This is mysql explain plan for one of the query I am looking into. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows…
Prafulla
  • 832
  • 1
  • 7
  • 17
8
votes
5 answers

Same query, different execution plans

I am trying to find a solution for a problem that is driving me mad... I have a query which runs very fast in a QA Server but it is very slow in production. I realized that they have different execution plans... so I have try recompiling, cleanning…
A..
  • 81
  • 1
  • 2
8
votes
14 answers

MySQL Query performance - huge difference in time

I have a query that is returning in vastly different amounts of time between 2 datasets. For one set (database A) it returns in a few seconds, for the other (database B)....well I haven't waited long enough yet, but over 10 minutes. I have dumped…
Damo
  • 11,410
  • 5
  • 57
  • 74
8
votes
7 answers

What are SQL Execution Plans and how can they help me?

I've been hearing a lot lately that I ought to take a look at the execution plan of my SQL to make a judgment on how well it will perform. However, I'm not really sure where to begin with this feature or what exactly it means. I'm looking for…
Daniel
  • 10,864
  • 22
  • 84
  • 115
8
votes
2 answers

PostgreSQL: The lower the LIMIT, the slower the query

I have the following query SELECT translation.id FROM "TRANSLATION" translation INNER JOIN "UNIT" unit ON translation.fk_id_unit = unit.id INNER JOIN "DOCUMENT" document ON unit.fk_id_document = document.id WHERE document.fk_id_job =…
twoflower
  • 6,788
  • 2
  • 33
  • 44
7
votes
2 answers

Why is performance increased when moving from a derived table to a temp table solution?

I'm reading "Dissecting SQL Server Execution Plans" from Grant Fritchey and it's helping me a lot to see why certain queries are slow. However, I am stumped with this case where a simple rewrite performs quite a lot faster. This is my first attempt…
buckley
  • 13,690
  • 3
  • 53
  • 61
7
votes
2 answers

Entity Framework cached query plan performance degrades with different parameters

I have the following problem. Background I'm trying to implement an autocomplete selector with MVC3, EF4 and jquery over a table wit 4.5 million records. This is the table: CREATE TABLE [dbo].[CONSTA] ( [afpCUIT] nvarchar(11) COLLATE…
Jerónimo Vargas
  • 101
  • 2
  • 8
7
votes
7 answers

Is there a way to make Oracle recalculate a query plan for each query invocation?

I have a parameterized query. Depending on parameter values optimal query plan varies significantly. Here is the trouble: Oracle uses the plan from the first query invocation for subsequent invocations resulting in bad performance. I deal with it by…
Sergey Skoblikov
  • 5,811
  • 6
  • 40
  • 49
7
votes
2 answers

Why would the exact same SQL query result with a different execution plan when executed via the sp_executeSQL procedure?

As the title states, I don't understand why the sp_executeSQL would generate a completely different execution plan than running the query from Sql Management Studio. My query in question will take 3 seconds when run from SQL management Studio, where…
Nathan Tregillus
  • 6,006
  • 3
  • 52
  • 91
1 2 3
…
95 96