Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query very slow when querying by a specific value in a specific column

Astro313Jun 24 2021

So here I have this very simple table, designed not in the best way imo but that's a different topic.
SQL> describe batch_log
Name Null? Type


REQUEST_ID NOT NULL NUMBER(7)
LINE_NO NOT NULL NUMBER
TEXT VARCHAR2(4000)
LOG_DATE DATE
This table has an INDEX based on REQUEST_ID and LINE_NO columns.
When I run Q1 result comes back in a whim. When I run the query based on another REQUEST_ID the query takes 5+ minutes.
Q1: SELECT REQUEST_ID as "REQUEST_ID" , LINE_NO as "LINE_NO" , TEXT as "TEXT" , LOG_DATE as "LOG_DATE" FROM BATCH_LOG WHERE REQUEST_ID=5061 ORDER BY line_no;
Q2: SELECT REQUEST_ID as "REQUEST_ID" , LINE_NO as "LINE_NO" , TEXT as "TEXT" , LOG_DATE as "LOG_DATE" FROM BATCH_LOG WHERE REQUEST_ID=5060 ORDER BY line_no;
Execution plan looks exactly the same for both in num or rows and CPU cost. I ran the query with autotrace and also ran gather_stats on that particular table but still I don't understand why when I have REQUEST_ID=5060 there's millions of physical reads on the disk.
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("REQUEST_ID"=5060)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2465841 consistent gets
2465833 physical reads
0 redo size
980 bytes sent via SQL*Net to client
516 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

Could it be the DB_CACHE size is undersized (400M)? I mean, I do think that SGA (2G) and PGA (2G) are tremendously undersized but I'm new to the company and I'm trying to push changes as quickly (and "politely") as I can.
Thoughts on DB_CACHE being undersized causing this problem?

This post has been answered by Jonathan Lewis on Jun 28 2021
Jump to Answer

Comments

Post Details

Added on Jun 24 2021
10 comments
5,383 views