| Oracle high "cost" SELECT query [message #675163] |
Tue, 12 March 2019 16:59  |
 |
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 #675183 is a reply to message #675180] |
Wed, 13 March 2019 10:36  |
gazzag
Messages: 1114
Registered: November 2010
Location: Bedwas, 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 |
John Watson
Messages: 8805
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
greenstone90 wrote on Wed, 13 March 2019 13:03There 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 #675195 is a reply to message #675194] |
Thu, 14 March 2019 08:12 |
cookiemonster
Messages: 13904
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 #675198 is a reply to message #675197] |
Thu, 14 March 2019 08:46 |
cookiemonster
Messages: 13904
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).
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
