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
5
votes
1 answer

Understanding explain plan through xmltype

I had a performance issue caused by a wrong xpath ('@' missing in attribute predicate) in a query like this: select extractvalue(field, '//item[attr="value"]') from table where field1 = :1; I expected an exception but seems that Oracle accept this…
Francesco Genta
  • 333
  • 1
  • 8
5
votes
1 answer

Interpreting HASH JOIN in Oracle query plan

When I see something like this in an Oracle query plan: HASH JOIN TABLE1 TABLE2 Which of these two tables is being hashed? The Oracle documentation refers to a "smaller" table usually being hashed, but is there a guarantee that the hashed…
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
5
votes
2 answers

Why is Postgres scanning a huge table instead of using my index?

I noticed one of my SQL queries is much slower than I expected it to be, and it turns out that the query planner is coming up with a plan that seems really bad to me. My query looks like this: select A.style, count(B.x is null) as missing, count(*)…
amalloy
  • 89,153
  • 8
  • 140
  • 205
5
votes
5 answers

SQL Server query execution plan shows wrong "actual row count" on an used index and performance is terrible slow

Today i stumbled upon an interesting performance problem with a stored procedure running on Sql Server 2005 SP2 in a db running on compatible level of 80 (SQL2000). The proc runs about 8 Minutes and the execution plan shows the usage of an index…
Jan
  • 15,802
  • 5
  • 35
  • 59
5
votes
3 answers

SQL Error: ORA-01039: insufficient privileges on underlying objects of the view

I am trying to get explain plan for a view using below query explain plan for select * from SCHEMA1.VIEW1; But i'm getting Error report - SQL Error: ORA-01039: insufficient privileges on underlying objects of the view 01039. 00000 - …
upog
  • 4,965
  • 8
  • 42
  • 81
5
votes
1 answer

create virtual index - just for the plan

I am in the process of optimizing a long running T-SQL query. Somehow, I remember there is a feature in SQL Server that allows checking how a plan would be if an index would exist. Something like CREATE VIRTUAL INDEX and then checking the plan. But…
FrankPl
  • 13,205
  • 2
  • 14
  • 40
5
votes
2 answers

Postgresql Explain Plan Differences

this is my first post.... I have a query that is taking longer than I would like (don't we all!) Depending on what I put in the WHERE clause...it MAY run faster. I am trying to understand why the query plan is different AND what i can do to speed…
iamtheoracle
  • 317
  • 2
  • 11
5
votes
7 answers

How to efficiently retrieve data in one to many relationships

I am running into an issue where I have a need to run a Query which should get some rows from a main table, and have an indicator if the key of the main table exists in a subtable (relation one to many). The query might be something like…
Martin
  • 3,018
  • 1
  • 26
  • 45
5
votes
2 answers

How this query generates two join operators in execution plan?

Table structure with a clustered unique index on empno. CREATE TABLE [dbo].[EMP]( [EMPNO] [int] NOT NULL, [ENAME] [varchar](10) NULL, [JOB] [varchar](9) NULL, [MGR] [int] NULL, [HIREDATE] [datetime] NULL, [SAL] [int] NULL, …
Registered User
  • 1,554
  • 3
  • 22
  • 37
5
votes
1 answer

T-SQL - Non optimal plan is used - WHERE clause should be shortcircuited

We have many "search stored procedures" that take multiple nullable parameters for searching rows of data in different tables. They're usually built like this: SELECT * FROM Table1 T1 INNER JOIN Table2 T2 ON T2.something =…
FrancoisCN
  • 75
  • 4
5
votes
4 answers

How does SQL server work out the estimated number of rows?

I'm trying to debug a fairly complex stored procedure that joins across many tabls (10-11). I'm seeing that for a part of the tree the estimated number of rows drasticly differs from the actual number of rows - at its worst SQL server estimates that…
Justin
  • 84,773
  • 49
  • 224
  • 367
5
votes
1 answer

Find out the slow running stored procedure

Possible Duplicate: How to find slowest queries In Sql Server 2008, is there any option to find out which stored procedure running slowly among all (When the no. of stored procedures is very large. Ex: 500)
gee'K'iran
  • 431
  • 2
  • 7
  • 21
5
votes
1 answer

Oracle explain plan:Cardinality returns a huge number but the query returns no records

I have written a complex oracle sql query and the explain plan stats look like this: Cost: 209,201 Bytes:187,944,150 Cardinality: 409,675 Now the DBA tuned the query and the stats look like this: Cost: 42,996 Bytes: 89,874,138 Cardinality:…
Victor
  • 16,609
  • 71
  • 229
  • 409
5
votes
1 answer

Analyising Implict CAST

I have an academic scenario, which I would like to know how to analyse. DECLARE @date DATETIME SET @date = getDate() SET @date = DATEADD(DAY, DATEDIFF(DAY, 0, @date-3), 3) This will round the date down to a Thursday. What I have been…
MatBailie
  • 83,401
  • 18
  • 103
  • 137
5
votes
1 answer

How to optimize mysql query based on explain. (Type: ALL

I run the following mysql query and see the type as ALL for the first query. mysql> EXPLAIN SELECT one.language_id as filter_id, one.language_name as filter_name, two.count as count …
beck03076
  • 3,268
  • 2
  • 27
  • 37
1 2 3
…
95 96