Oracle Database Discussions

Hello Guys,

I just facing a strange problem. I have a very small table (only ~ 400 rows). A select on that table (SELECT * FROM TABLE_NAME) last about 9 sec (sometimes even more [highest amount was 35 sec])!!! How is that possible? I looked at the autotrace output and saw a massive amount of read bytes. The amount will not be smaller if I run the query again and again. Does anyone know why a simple select lasts about 9-35 sec?

Here's the output from autotrace....

bytes received via SQL*Net from client 330
bytes sent via SQL*Net to client 58651
calls to get snapshot scn: kcmgss 20
consistent gets 81641
consistent gets from cache 81641
CPU used by this session 296
CPU used when call started 296
DB time 725
enqueue releases 1
enqueue requests 1
execute count 2
free buffer inspected 81664
free buffer requested 81616
hot buffers moved to head of LRU 1
no work - consistent read gets 81623
opened cursors cumulative 2
OS Block input operations 657
OS Characters read/written *668657669*
OS Integral unshared data size 918994
OS Involuntary context switches 1749
OS Socket messages received 3
OS Socket messages sent 32
OS System time used 30
OS User time used 266
OS Voluntary context switches 645
parse count (hard) 1
parse count (total) 2
physical read bytes *668598272*
physical read IO requests 661
physical read total bytes *668598272*
physical read total IO requests *661*
physical read total multi block requests 660
physical reads *81616*
physical reads cache *81616*
physical reads cache prefetch 80955
recursive calls 1
session cursor cache hits 1
session logical reads 81641
sorts (memory) 2
sorts (rows) 762
SQL*Net roundtrips to/from client 2
table scan blocks gotten 81623
table scan rows gotten *2861001 (why that?)*
table scans (cache partitions) 1
user calls 4
user I/O wait time 434~

Here the output of the explain plan

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 322 | 59570 | 22347 (2)| 00:09:29 |
| 1 | TABLE ACCESS FULL| LOG_LOGBOOK | 322 | 59570 | 22347 (2)| 00:09:29 |
---------------------------------------------------------------------------------

And if you need to know the ddl for the table...

--------------------------------------------------------
-- DDL for Table LOG_LOGBOOK
--------------------------------------------------------

CREATE TABLE "SCHEMA"."LOG_LOGBOOK"
( "LOG_ID" NUMBER(10,0),
"LOG_LEVEL" VARCHAR2(255 BYTE),
"LOG_COLLECTION" VARCHAR2(1024 BYTE),
"LOG_EVENT" VARCHAR2(255 BYTE),
"LOG_MESSAGE" VARCHAR2(4000 BYTE),
"LOG_EXCEPTION" VARCHAR2(4000 BYTE),
"LOG_EVENT_ID" VARCHAR2(255 BYTE),
"LOG_SESSION_ID" VARCHAR2(255 BYTE),
"LOG_HOST" VARCHAR2(255 BYTE),
"LOG_USER" VARCHAR2(255 BYTE),
"LOG_TIMESTAMP" TIMESTAMP (6) DEFAULT SYSTIMESTAMP
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SCHEMADB1_DATA"
CACHE ;
--------------------------------------------------------
-- DDL for Index LOGBOOK-LOG_LVL_COL-IDX
--------------------------------------------------------

CREATE INDEX "SCHEMA"."LOGBOOK-LOG_LVL_COL-IDX" ON "SCHEMA"."LOG_LOGBOOK" ("LOG_LEVEL", "LOG_COLLECTION")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SCHEMADB1_DATA" ;
--------------------------------------------------------
-- DDL for Index SYS_C0012324
--------------------------------------------------------

CREATE UNIQUE INDEX "SCHEMA"."SYS_C0012324" ON "SCHEMA"."LOG_LOGBOOK" ("LOG_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SCHEMADB1_DATA" ;
--------------------------------------------------------
-- Constraints for Table LOG_LOGBOOK
--------------------------------------------------------

ALTER TABLE "SCHEMA"."LOG_LOGBOOK" MODIFY ("LOG_ID" NOT NULL ENABLE);

ALTER TABLE "SCHEMA"."LOG_LOGBOOK" MODIFY ("LOG_LEVEL" NOT NULL ENABLE);

ALTER TABLE "SCHEMA"."LOG_LOGBOOK" MODIFY ("LOG_COLLECTION" NOT NULL ENABLE);

ALTER TABLE "SCHEMA"."LOG_LOGBOOK" MODIFY ("LOG_EVENT" NOT NULL ENABLE);

ALTER TABLE "SCHEMA"."LOG_LOGBOOK" MODIFY ("LOG_MESSAGE" NOT NULL ENABLE);

ALTER TABLE "SCHEMA"."LOG_LOGBOOK" MODIFY ("LOG_TIMESTAMP" NOT NULL ENABLE);

ALTER TABLE "SCHEMA"."LOG_LOGBOOK" ADD PRIMARY KEY ("LOG_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SCHEMADB1_DATA" ENABLE;
--------------------------------------------------------
-- DDL for Trigger LOGBOOK-LOG_ID-TRG
--------------------------------------------------------

CREATE OR REPLACE TRIGGER "SCHEMA"."LOGBOOK-LOG_ID-TRG" BEFORE INSERT ON "LOG_LOGBOOK" FOR EACH ROW
BEGIN
SELECT "LOGBOOK-LOG_ID-SEQ".nextval INTO :new.LOG_ID from dual;
END;

/
ALTER TRIGGER "SCHEMA"."LOGBOOK-LOG_ID-TRG" ENABLE;