Stack Overflow
Stack Overflow

Questions tagged [sql-tuning]

SQL tuning commonly involves finding more efficient ways to process the same workload. It is possible to change the execution plan of the statement without altering the functionality to reduce the resource consumption.

328 questions
1
vote
1 answer

Dropping Unused Indexes

I am referring the Unused Index Script blog. I got to know that if Seek = 0, Scan = 0, Lookup = 0, User Update = 0 then we should delete index as it is not needed. The unused script gives me many indexes but should I delete all those indexes? Can…
Yatin
  • 21
  • 5
1
vote
1 answer

Oracle skip the index when using PARALLEL hint

I'm using Oracle Database "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 64bit" I'm facing a behavior I don't know if its right or there is something wrong with that. for example the below query SELECT * FROM (SELECT x, y, z,…
Mohamed El-Touny
  • 347
  • 1
  • 4
  • 14
1
vote
1 answer

Writing dirty-blocks to the discs is only performed on commit?

As far as I know, DBWs write the dirty blocks to the disc and unlocks the blocks on commit operation takes place. However, does it really happen as I believe DBWn doesn't directly related to commit issue? If not, how does it perform this write…
general46
  • 800
  • 6
  • 16
1
vote
2 answers

What is the difference between the UNION and CONCATENATION operators in terms of performance?

Sometimes, it can be seen CONCATENATION step in the Explain Plan. I wonder what is the difference between union and concatenation operators in terms of performance tuning?
general46
  • 800
  • 6
  • 16
1
vote
1 answer

How to know the statistics are up to date in Oracle Database?

How to know the statistics are up to date, is there any way to find when the last statistics were calculated? Do I need to just look at the stale_stats and last_analyzed column of the dba_tab_statistics view?
general46
  • 800
  • 6
  • 16
1
vote
1 answer

Query Tuning and rewrite - SQL Server

Could you help to optimize the below query to perform better? Can I reduce the cost? SELECT this_.id AS id1_20_0_, this_.version AS version2_20_0_, this_.domain AS domain4_20_0_, …
Dothertechie
  • 27
  • 6
1
vote
1 answer

Sql Server select requests tuning

We have a SQL Server database table with about 60million records. These are records of names and addresses of specific entities. Table contains following columns: [Name] [nvarchar](425) NOT NULL, [Street] [nvarchar](900) NULL, [City] [nvarchar](900)…
Stanislav
  • 165
  • 1
  • 15
1
vote
1 answer

Why there are current block reads in my TKPROF output on a simple select statement? Why there are two parses in one query?

In the query below, I used the sample SH schema in my query in Oracle. When I get the TKPROF output for that query, I see that, there are some current block reads. But as far as I know, current block reads happen when that block has a change on it.…
oramas
  • 881
  • 7
  • 12
1
vote
2 answers

Query optimization on Oracle

I am not a query performance expert and I am learning how Oracle optimizer works on different queries and tune them for its use. Below is such query from my project where I am stuck on optimizing it for the large data set (it's slowing down for…
witswickey
  • 35
  • 1
  • 2
1
vote
0 answers

Which one is the driving table on an execution plan?

In Oracle database, when I get the execution plan, or explain plan, which row source is considered as the driving table? Is it the one above the other one? Or does this change in nested joins, merge joins, etc? Besides, does it change if I get the…
oramas
  • 881
  • 7
  • 12
1
vote
3 answers

how to make SQL having many union faster for high speed

I have a query which takes quite a long time for selecting data. Let me share my data schema and do you have any idea for high speed instead of my SQL? --------------------------------------------------- time | statA | statB | statC |…
sunsets
  • 401
  • 5
  • 23
1
vote
1 answer

Oracle SQL Tuning Advisor with changing query

We have an C# app which will search the DB for activity in a couple of tables. The user can specify to search for certain criteria, which in turn build upon the base SQL query. This query takes a very long time to execute and we wanted to use…
Jimenemex
  • 3,104
  • 3
  • 24
  • 56
1
vote
2 answers

SQL Query Takes 15 to 20 sec

The below Query is taking 15 to 20 Sec which is not expected. Please help in refactoring this query SELECT upin.bipuserid userid, upin.orgn_entity_id entityId, COUNT(upin.ORGN_ENTITY_ID) over (partition BY upin.bipuserid)…
Gagan Deep
  • 21
  • 2
1
vote
2 answers

Performance of OR operator vs. COALESCE

Comparing these statements SELECT * FROM Table WHERE (field IS NULL OR field = 'empty_value') vs. SELECT * FROM Table WHERE COALESCE(field, 'empty_value') = 'empty_value' in terms of performance, which one is better? Is there any…
Diego Queiroz
  • 3,198
  • 1
  • 24
  • 36
1
vote
2 answers

Oracle 12c performance tuning - delete query against a global temp table

Oracle Performance Gurus, I have a task of tuning a beast of a sql that runs for more than 18 hours at times depending on the number of rows it tries to delete from a global temp table. There is an index defined on the table but the optimizer is…
Brendon
  • 57
  • 1
  • 7
1 2 3
…
21 22