Home » RDBMS Server » Performance Tuning » Oracle high "cost" SELECT query (Oracle 12)
Oracle high "cost" SELECT query [message #675163] Tue, 12 March 2019 16:59 Go to next message
greenstone90
Messages: 5
Registered: March 2019
Junior Member
Hi,

We have the following SELECT query that is reported to have a very high cost.

Thoughts on why a fairly simple SELECT on a single table, only fetching some integers in each record, would have such slow/costly performance?

note: Both the DOMAIN_ROOT_ID and GUID/DOMAIN_ROOT_ID indexes are on the table (the members of the WHERE clause)

SELECT NULL AS "AddedTimeStamp",
"TEST_DB_SCHEMA"."EMP_REPORT"."CREATED_USER_ID" AS "CreatedUserId",
"TEST_DB_SCHEMA"."EMP_REPORT"."DOMAIN_ROOT_ID" AS "DomainRootId",
"TEST_DB_SCHEMA"."EMP_REPORT"."EMPLOYEE_ID" AS "EmployeeId",
"TEST_DB_SCHEMA"."EMP_REPORT"."GUID" AS "Guid",
"TEST_DB_SCHEMA"."EMP_REPORT"."ID" AS "Id",
FROM "TEST_DB_SCHEMA"."EMP_REPORT"
WHERE ( ( "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p1
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p2
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p3
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p4
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p5
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p6
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p7
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p8
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p9
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p10
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p11
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p12
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p13
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p14
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p15
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p16
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p17
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p18
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p19
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p20
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p21
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p22
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p23
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p24
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p25
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p26
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p27
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p28
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p29
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p30
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p31
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p32
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p33
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p34
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p35
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p36
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p37
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p38
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p39
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p40
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p41
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p42
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p43
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p44
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p45
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p46
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p47
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p48
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p49
OR "TEST_DB_SCHEMA"."EMP_REPORT"."GUID" = :p50)
AND "TEST_DB_SCHEMA"."EMP_REPORT"."DOMAIN_ROOT_ID" = :p51);

EXPLAIN PLAN RESULTS:


Plan
SELECT STATEMENT ALL_ROWSCost: 13,159
2 TABLE ACCESS BY INDEX ROWID BATCHED TABLE TEST_DB_SCHEMA.EMP_REPORT Cost: 13,159 Bytes: 48,925 Cardinality: 475
1 INDEX RANGE SCAN INDEX TEST_DB_SCHEMA.EMP_REPORT_DOM_ROOT_ID_NN Cost: 13,159 Cardinality: 4,750,539
Re: Oracle high "cost" SELECT query [message #675164 is a reply to message #675163] Tue, 12 March 2019 17:38 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


Quantify slow performance.
How many rows in the table?
How many rows are being returned?

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Oracle high "cost" SELECT query [message #675176 is a reply to message #675163] Wed, 13 March 2019 05:54 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
What's wrong with using an IN clause instead of all those ORs?
Re: Oracle high "cost" SELECT query [message #675178 is a reply to message #675176] Wed, 13 March 2019 06:06 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
How many rows in the table?
How many rows match your GUIDs?
Re: Oracle high "cost" SELECT query [message #675180 is a reply to message #675178] Wed, 13 March 2019 08:03 Go to previous messageGo to next message
greenstone90
Messages: 5
Registered: March 2019
Junior Member
There are about 4 million rows in the table.

The GUIDs are unique, so there will be one row per GUID (so this SELECT will return 50 rows).

The SELECT statement seems to take many minutes.

An IN (instead of ORs) would be an option I could try. I wonder if it would make a difference.

Re: Oracle high "cost" SELECT query [message #675181 is a reply to message #675180] Wed, 13 March 2019 08:09 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
If guid is unique then shouldn't you have a unique constraint/primary key on guid?
Re: Oracle high "cost" SELECT query [message #675182 is a reply to message #675180] Wed, 13 March 2019 08:36 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
greenstone90 wrote on Wed, 13 March 2019 06:03
There are about 4 million rows in the table.

The GUIDs are unique, so there will be one row per GUID (so this SELECT will return 50 rows).

The SELECT statement seems to take many minutes.

An IN (instead of ORs) would be an option I could try. I wonder if it would make a difference.

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Oracle high "cost" SELECT query [message #675183 is a reply to message #675180] Wed, 13 March 2019 10:36 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
greenstone90 wrote on Wed, 13 March 2019 13:03

An IN (instead of ORs) would be an option I could try. I wonder if it would make a difference.
Test it with EXPLAIN PLAN.
Re: Oracle high "cost" SELECT query [message #675189 is a reply to message #675180] Thu, 14 March 2019 02:39 Go to previous messageGo to next message
John Watson
Messages: 8085
Registered: January 2010
Location: Global Village
Senior Member
greenstone90 wrote on Wed, 13 March 2019 13:03
There are about 4 million rows in the table.

The GUIDs are unique, so there will be one row per GUID (so this SELECT will return 50 rows).

The SELECT statement seems to take many minutes.

An IN (instead of ORs) would be an option I could try. I wonder if it would make a difference.

Have you declared GUID unique and indexed it? If so, perhaps use of the index is being suppressed. The column name suggests that it might be data type raw, like sys_guid. That would be a problem.
Re: Oracle high "cost" SELECT query [message #675194 is a reply to message #675189] Thu, 14 March 2019 08:02 Go to previous messageGo to next message
greenstone90
Messages: 5
Registered: March 2019
Junior Member
Thanks for the help.

I had a couple indexes:
1.) DOMAIN_ROOT_ID
2.) GUID and DOMAIN_ROOT_ID (composite index)

Oracle was choosing the index 1.) instead of index 2.), so it was doing a full table scan to find the records.

To alleviate the problem, provided a index "hint" to oracle in the SELECT statement and the oracle reported cost came down from 13,000 -to- 2.

Other than using Oracle's "index hint", suggestions on keeping this from happening? If I create an index just on GUID, would expect this would nudge oracle to use the desired indexes?
Re: Oracle high "cost" SELECT query [message #675195 is a reply to message #675194] Thu, 14 March 2019 08:12 Go to previous messageGo to next message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is GUID unique in the table?
Is it supposed to be unique in the table?

If yes, why isn't there a unique constraint/primary key on it?
This isn't a performance question, this is a data integrity question. But fixing your data integrity issue would probably speed up your query as well.
Re: Oracle high "cost" SELECT query [message #675196 is a reply to message #675195] Thu, 14 March 2019 08:16 Go to previous messageGo to next message
greenstone90
Messages: 5
Registered: March 2019
Junior Member
Combination of GUID and DOMAIN_ROOT_ID is unique in the table, so there is a unique key on the combination of those two columns.


Re: Oracle high "cost" SELECT query [message #675197 is a reply to message #675196] Thu, 14 March 2019 08:19 Go to previous messageGo to next message
greenstone90
Messages: 5
Registered: March 2019
Junior Member
I believe we're resolved the issue.

The keys were changed, then a large number of records were inserted (a few million), then right after a SELECT was run on the table.

There was not much time between the time when the keys had changed and the SELECT (the slow performing one) was changed, so it appears Oracle's statistics were not updated between. Because of this Oracle was using the old statistics (before the added key), and performing the SELECT based on this.

Once the statistics were forced to be updated, Oracle started using the correct index, and the performance sped up.

Thanks for all your feedback!
Re: Oracle high "cost" SELECT query [message #675198 is a reply to message #675197] Thu, 14 March 2019 08:46 Go to previous message
cookiemonster
Messages: 13759
Registered: September 2008
Location: Rainy Manchester
Senior Member
Updating statistics doesn't actually force a change of execution plan by default.

Whenever you run a select oracle looks to see if it's in the SGA. If it isn't then it generates a plan for the query, stores that in the SGA and executes. If it is in the SGA then it gets the existing plan and executes.

So normally a plan will stick until the existing plan either ages out of the SGA or is invalidated. Changing the objects the query references can invalidate it (though not necessarily) or you can force it - by using the no_invalidate parameter on dbms_stats.gether_table_stats (and gather_schema_stats and various others).


Previous Topic: Retrieving 8k rows from one table takes too long
Next Topic: Oracle CPU usage Analysis
Goto Forum:
  


Current Time: Fri Dec 13 21:02:31 CST 2019