I'm running an Oracle 12.1.0.2v12 database on an Amazon AWS RDS instance with the instance class of db.r5.2xlarge.
I have a table DEVICES which contains 63 columns (all alphanumeric/timestamps, no blobs)
create table DEVICES (
DEVICE_ID NUMBER(20,0) not null,
TENANT VARCHAR2(22) default 'TENANT1' not null,
AUTHID VARCHAR2(22),
REGID VARCHAR2(22),
SP_UID NUMBER(20,0),
SP_IID NUMBER(20,0),
PROFILE NUMBER(20,0) not null,
...
constraint PK_DEVICES primary key (DEVICE_ID)
);
The following query is causing me concern:
select count(DEVICE_ID) from DEVICES where TENANT='TENANT1' and PROFILE=1445797;
Upon running this in sqlplus with "set timing on" I get a minimum response time of .35 seconds
When I run the statement again I get a response time of .00 seconds
I understand that the query will have to go through all the parsing operations and will take time the first time it runs, but over 350ms seems quite a lot
on a system that is not under much stress. CPU is running at approximately 20% with less than 1,000 IOPS on General purpose SSD.
Some additional information:
The table has 3.4 million records.
The PROFILE value will almost always be unique in the table.
The TENANT value is always TENANT1.
16 indexes are defined on the table.
The TENANT column is indexed.
The PROFILE column is indexed.
The execution plan uses the PROFILE index.
I created an index on the combination of TENANT and PROFILE and the execution plan started to use this index with minimum impact to the execution time.
I have another Oracle 12.1.0.2v12 RDS running with a much smaller instance size of db.t2.small. The DEVICES table has 1.4 million records.
If I run the same query on sqlplus with timing on it returns with a response time of .01 seconds for the first execution.
I know the environments are not identical but it doesn't make sense to us that this query is taking over 350 ms to complete such a simple query?
Can you please advise on how we can troubleshoot this issue?
I'm seeing this behaviour in an oltp application system where the query is using a literal for the PROFILE instead of a bind variable. We plan to change the query to use a literal but is there anything we could do to improve the performance when using a literal?