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  |
 |
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
|
|
|
|
Current Time: Fri Apr 26 06:30:02 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
