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.
Questions tagged [sql-tuning]
328 questions
3
votes
1 answer
Performance tuning or alternate to distinct clause
I have this query with a distinct clause:
SELECT -- /*+ first_rows */
distinct a.sub_id, b.status,
pkg_sp_subbrief.get_sub_typ (a.sub_id) sub_type,
c.svc_provider_nm, fn_sp_get_svc_plan (a.sub_id) svc_plan,
…
mradul
- 509
- 4
- 12
- 28
3
votes
3 answers
comparing a column with itself in WHERE clause of oracle SELECT
I have a multi-table SELECT query which compares column values with itself like below:
SELECT * FROM table1 t1,table2 t2
WHERE t1.col1=t2.col1 --Different tables,So OK.
AND t1.col1=t1.col1 --Same tables??
AND t2.col1=t2.col1 …
cctk11
- 75
- 2
- 5
3
votes
1 answer
T-SQL alternatives to nested CASE for better performance?
I have a T-SQL query that is performing very poorly to the point that it times out. The culprits are these two nested CASE statements with embedded queries:
SELECT
CASE
WHEN b.month_type = (CASE
WHEN dbo.CURRENT_BUSINESSDAY(GETDATE())…
user2891330
- 81
- 5
3
votes
1 answer
PL/SQL Developer runs queries faster when contains comments
I'm trying to improve performance on an SQL query against an Oracle 10g database. I have the following two queries:
Query 1
SELECT DISTINCT
WMS_EVENT_LOG.LOG_ID,
WMS_EVENT_LOG.EVENT_ARG4,
WMS_EVENT_LOG.EVENT_TYPE,
…
Dodzi Dzakuma
- 1,406
- 2
- 21
- 39
3
votes
1 answer
What are the performance implications of Oracle IN Clause with no joins?
I have a query in this form that will on average take ~100 in clause elements, and at some rare times > 1000 elements. If greater than 1000 elements, we will chunk the in clause down to 1000 (an Oracle maximum).
The SQL is in the form of
SELECT *…
cmdematos
- 883
- 4
- 12
- 24
3
votes
3 answers
INSERT into temporary table GTT very slow from PL/SQL
I have a query that performs fantastic when executed from SQL.
It is a join between a table and a query. Both the tables are having close to 4 mn records.
There are bitmap indexes on the doc table that i am trying to provide hint on. The Explain…
user2275460
- 339
- 3
- 14
3
votes
2 answers
Query Tuning - Advice
I need advice on the attached Query. The query executes for over an hour and has full table scan as per the Explain Plan. I am fairly new to query tuning and would appriciate some advice.
Firstly why would I get a full table scan even though…
Venus D'souza
- 135
- 1
- 8
2
votes
2 answers
SQL Query Performence is too bad for MySQL
I run the following SQL Query on a MySQL platform.
Table A is a table which has a single column (primary key) and 25K rows.
Table B has several columns and 75K rows.
It takes 20 minutes to execute following query. I will be glad if you could…
noway
- 2,585
- 7
- 41
- 61
2
votes
1 answer
How to force Index usage on columns in Merge Statement in Oracle
I am working on Oracle 10gR2
I have a MERGE statement for a table, TBL_CUSTOMER. TBL_CUSTOMER contains a column USERNAME, which contains email addresses. The data stored in this table is case insensitive, as in, the incoming data can be in upper…
Incognito
- 2,964
- 2
- 27
- 40
2
votes
2 answers
Why Access and Filter Predicates are the same here?
When I get the autotrace output of the query above using the Oracle SQL Developer, I see that the join condition is used for access and filter predicates. My question is, does it read all the department_ids from the DEPT_ID_PK and then use these…
oramas
- 881
- 7
- 12
2
votes
1 answer
Why to use Correlated Subqueries?
As far as I've seen, correlated subqueries can be re-written using multiple-column subqueries or joins. And they usually perform better than correlated subqueries.
So in which possible scenarios a correlated subquery can be a better option or the…
oramas
- 881
- 7
- 12
2
votes
0 answers
How lower bound selectivity and high bound selectivity calculates in adaptive cursor sharing?
How lower bound selectivity and high bound selectivity calculate in adaptive cursor sharing?
I've searched this on Google, but couldn't find a proper answer.
general46
- 800
- 6
- 16
2
votes
2 answers
SQL Plan change reasons
One of the job schedulers is running in the production environment on a daily basis which use to take only 20 mins based past execution history, but today it's been more than 2 hours still not completed.
a) How to check whether the SQL plan has…
general46
- 800
- 6
- 16
2
votes
3 answers
How can I see the query that the query transformer produced in Oracle
As I know, the query transformer transforms our queries into better ones if possible. So the query I executed and the query database executed at the end can be different.
How can I see the final query that the database executed? I mean the result of…
oramas
- 881
- 7
- 12
2
votes
2 answers
Oracle SQL tuning based on hints- any good on recent versions?
I read that oracle's CBO(on recent versions) is so good that even if worst possible join order is given, CBO automagically takes the best join order. So will hints like ORDERED do any good on recent versions(10,11)?. Is it possible for the CBO to…
komedit1
- 255
- 1
- 7
- 14