| Select with no conditions taking long time, too many blocks, pctused [message #413630] |
Thu, 16 July 2009 17:06  |
goo321
Messages: 28
Registered: June 2008
Location: houston
|
Junior Member |
|
|
select * from at_journal;
is taking 15 seconds, which is absurd. I have similar situations with other log tables in this system.
(selecting with rownum < 5 and with first_rows hint still takes 15 seconds)
Suspicious parameters:
PCT_FREE 1
PCT_USED 99
(Some other log tables have PCT_FREE 5, PCTUSED 90)
The tables have frequent inserts, once every day all records older than 90 days get deleted.
After computing statistics:
Blocks: 42,304 (seems absurdly high)
Size: 663 Mb
NUM_ROWS: 505,966
After copying data without compression to new table:
Blocks: 3,785
Size: 60 Mb
NUM_ROWS: 505,966
The select on the new table is instantaneous.
On a side note, in general is compression for a table with frequent deletes OK? It seems OK to me.
Storage parameters of table creation script:
PCTUSED 99
PCTFREE 1
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
I imagine that most blocks for the table are empty. In my mind pctused of 99 means most of the block space should be full. I have read several explanations of PCTUSED/pctfree including oracle documentation and it seems pctused of 99 means the block should be 99% full.
Much thanks,
goo
|
|
|
|
|
|
|
|
|
|
| Re: Select with no conditions taking long time, too many blocks, pctused [message #413641 is a reply to message #413635] |
Thu, 16 July 2009 22:29  |
goo321
Messages: 28
Registered: June 2008
Location: houston
|
Junior Member |
|
|
I will check the tablespace management tomorrow.
It looks like alter table xxx shrink space would work in oracle 10 and greater.
In oracle 9i, move tablespace and move back would solve the problem but might take table offline while moving.
Or copying data, truncate, and reload, with same problem of off-line for half a minute or some similar time.
Thanks.
|
|
|
|
|
|
| Re: Select with no conditions taking long time, too many blocks, pctused [message #413703 is a reply to message #413630] |
Fri, 17 July 2009 03:14 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
On the surface, this looks like a simple high watermark problem - your Table has, in the past, had 42,000 blocks of data in it, which is the high water mark.
Every time that a full table scan is run against the table, it wil l read all 42,000 blocks.
By moving the table, you move only the blocks that have data, so the high watermark gets reset, drastically decreasing the amount of data that needs to be read.
|
|
|
|
| Re: Select with no conditions taking long time, too many blocks, pctused [message #413706 is a reply to message #413703] |
Fri, 17 July 2009 03:41 |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
Thanks Jrowbottom sir...
So, the option is to rebuild the table (alter table move statements)/ copy the data into a new table.
Please let me know if I am wrong here...
99-1 block splits:
http://bbs.wisky.org/doc/internal.docs/Richard%20Foote-Oracle%20Index%20Internal.pdfn Page #31-33
| Quote: |
Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark,
it is called segment shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segement Space Management.
In such a tablespace a table does not really have a High watermark!
It uses two watermarks instead:
- the High High Watermark referred to as HHWM, above which alle blocks ar unformatted.
- the Low High Watermark referred to as LHWM below which all blocks are formatted.
We now can have unformatted blocks in the middle of a segment!
ASSM was introduced in Oracle 9iR2 and it was made the default for tablespaces in Oracle 10gR2.
With the table shrinking feature we can get Oracle
to move rows which are located in the middle or at the end of a segment
further more down to the beginning of the segment and by
this make the segment more compact.
For this we must first allow ORACLE to change the ROWIDs of these rows by issuing
ALTER TABLE emp ENABLE ROW MOVEMENT;
|
Source: http://sysdba.wordpress.com/2006/04/28/how-to-adjust-the-high-watermark-in-oracle-10g-alter-table-shrink/
Regards,
Prachi
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
