Closed

Oracle DBA required to investigate performance anomolies in similar SQL

We have two SQL statements that run at radically different speeds. It seems to be based on whether a column is placed in the WHERE or in the SELECT list. We would like this investigated and explained. Remote access to our test server can be arranged.

The following statement runs slowly - 2.125 seconds:

SELECT COUNT(*) FROM SBC WHERE [login to view URL] = 1 AND [login to view URL] = 40088609 AND [login to view URL] = 798120609;

Despite it using the new index completely (including PLACED):

CREATE INDEX "BTS"."SBC_PLACEDTARGETSPOTS" ON "BTS"."SALES_BREAKS_COMMS" ("CONTITEMPRGDETTARGETID", "CONTITEMPRGDETID", "PLACED") TABLESPACE "BTS_INDEXES";

Yet this statement, with the PLACED in the SELECT list and NOT the WHERE clause, runs quickly (0.238 seconds):

SELECT [login to view URL] FROM SBC WHERE [login to view URL] = 40088611 AND [login to view URL] = 798120611;

Note: The IDs in the SQLs are changed to prevent Oracle caching the results.

Quick tests:

PLACED, CONTITEMPRGDETID and CONTITEMPRGDETTARGETID in the WHERE - slow

PLACED and CONTITEMPRGDETID but no CONTITEMPRGDETTARGETID in the WHERE - fast

CONTITEMPRGDETID and CONTITEMPRGDETID in the WHERE, PLACED in the SELECT list - fast

CONTITEMPRGDETID and CONTITEMPRGDETID in the WHERE, COUNT(*) in the SELECT list, no direct references to PLACED - fast

PLACED and CONTITEMPRGDETID in the WHERE, COUNT(*) in the SELECT list - fast

Skills: Database Administration, Database Development, Oracle, SQL

See more: performance tuning in oracle 11g with examples, oracle sql query performance tuning tips, sql tuning in oracle 11g step by step, real time sql monitoring sql developer, oracle vs sql server performance comparison, oracle 12c real time sql monitoring, oracle sql monitor report, how to tune long running queries in oracle, SQL,ORACLE DBA, SQL, ORACLE DBA, ms sql oracle dba freelance, online oracle dba trainer required, online trainer required oracle dba, training required oracle dba, required oracle dba trainer, oracle dba trainer required, sql dba oracle dba bangladesh, oracle dba sql plsql, oracle dba salary dubai, freelance oracle dba

About the Employer:
( 2 reviews ) Aberdulais, United Kingdom

Project ID: #16926631

22 freelancers are bidding on average £21/hour for this job

truongnguyen86

Hello there, i'm expert on Oracle especially with tuning complex queries. After seeing your queries and indexes you made, it seems you're applying the right index because the index is really good when you make the quer More

£15 GBP / hour
(161 Reviews)
6.6
schoudhary1553

Hello, I can help with you in your project Oracle DBA required to investigate performance anomolies in similar SQL . I have more than 5 years of experience in Database Administration, Database Development, Oracle, S More

£12 GBP / hour
(6 Reviews)
4.2
mdo19

A proposal has not yet been provided

£14 GBP / hour
(5 Reviews)
3.9
tangramua

Greetings. I`m ready to investigate performance anomolies in similar SQL. As for me, I'm a professional system admin with 10+ years of experience. My main specialization is LAMP stack of technologies (Linux, Apache, More

£13 GBP / hour
(1 Review)
3.8
havar2018

Hello, I am interested in this work. Do you want this issue be solved? Isn't it straightforward? slow when 3 fields in where clause and fast when 2 columns. Is that all? How can I help you? Please call me so th More

£14 GBP / hour
(3 Reviews)
2.4
rjh5879145e0c2df

I have an experience of more than 8 years in Software company and was working on Oracle as a DBA. I tuned the whole package which was running for 28hours and set it to 4 minutes. So, I have a good knowledge of tuning t More

£11 GBP / hour
(0 Reviews)
0.0
sharmaraj1982

My self oracle dba with 7yrs of exp in billing systems

£16 GBP / hour
(0 Reviews)
0.0
palandenikhil01

Oracle DBA with 7+ years exp

£10 GBP / hour
(0 Reviews)
0.0
Huafangwei

Hi, My name is Helen Wei. I am from Sydney Australia. I am now working for a Fortune 500 corporation as a senior DBA. I have over 20 years of professional DBA experience. I have got Oracle 11g and 12c OCM certificati More

£111 GBP / hour
(0 Reviews)
0.0
fgordonie

Hello, These days Oracle performance tuning is hard, especially with the Optimizer Automation in 12.2, 12.2 and 18c. Have you done explain plans for the 2 querys? What else is going on at the same time? I'd l More

£55 GBP / hour
(0 Reviews)
0.0
avChernov

Interesting situation. I'd like to take a look. PS I believe that I can solve this problem and it will cost you 1-2 hours of work = 10-20 euro.

£10 GBP / hour
(0 Reviews)
0.0
tersedevendra

I worked on performance tunning for more than 5 years in different MNCs. I can assure you to deliver project on time with good quality of work. Looking forward for your further discussion for this project.

£11 GBP / hour
(0 Reviews)
0.0
adriandutra

I think it so easy to solve this issue. You can use a subquery statement Relevant Skills and Experience I'm a DBA

£13 GBP / hour
(0 Reviews)
0.0
mithlesh1984

I have 12 years experience as Oracle DBA. I have real time experience in tuning database & SQL query slowness.

£11 GBP / hour
(0 Reviews)
0.0
GujjulaParamesh

A proposal has not yet been provided

£12 GBP / hour
(0 Reviews)
0.0
£16 GBP / hour
(0 Reviews)
0.0
Sky002

I've 5 years of experience working with Oracle Database. During that period I've optimized oodles of queries and scripts for the companies I've worked in. I feel, that experience will definitely come in handy while pro More

£11 GBP / hour
(0 Reviews)
0.0
£13 GBP / hour
(0 Reviews)
0.0
DBA4HireFederico

I worked on query optimization with several DBMSs, including Oracle. I held trainings on query performance and included this topic in my book "Mastering MariaDB". I will need to connect to the database to check the More

£48 GBP / hour
(0 Reviews)
0.0
kcarpan5

Hi, It looks like the INDEX is the cause. I'm happy to take a look at it. Will need to further investigate what's happening. Please send me a message if you'd me to take it further.

£15 GBP / hour
(0 Reviews)
0.0