Home » RDBMS Server » Performance Tuning » Select with no conditions taking long time, too many blocks, pctused (Oracle 9.2.0.8 Solaris)
Select with no conditions taking long time, too many blocks, pctused [message #413630] Thu, 16 July 2009 17:06 Go to next message
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 #413632 is a reply to message #413630] Thu, 16 July 2009 17:12 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
Please join the 21st century & start using Locally Managed Tablespace & ASSM.
If so, you won't have to worry about last century details.

[Updated on: Thu, 16 July 2009 17:12]

Report message to a moderator

Re: Select with no conditions taking long time, too many blocks, pctused [message #413634 is a reply to message #413632] Thu, 16 July 2009 17:20 Go to previous messageGo to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
The upgrade to Oracle 10 failed last year.
Issues with RAC structure/ raw file system.

I do try.
Re: Select with no conditions taking long time, too many blocks, pctused [message #413635 is a reply to message #413630] Thu, 16 July 2009 17:21 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>I do try.
You don't try hard enough. V9.2 supports LMT

http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c04space.htm#4384

[Updated on: Thu, 16 July 2009 17:30]

Report message to a moderator

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 Go to previous messageGo to next message
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 #413649 is a reply to message #413630] Thu, 16 July 2009 22:54 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
The move_table_clause lets you relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, optionally in a different tablespace, and optionally modify any of its storage attributes.

>In oracle 9i, move tablespace and move back would solve the problem

I suggest tables be moved once from DMT to LMT.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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







Re: Select with no conditions taking long time, too many blocks, pctused [message #413714 is a reply to message #413706] Fri, 17 July 2009 05:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Before 10g, your only option was to rebuild or re-create the table.


Your first link refers to indexes and their structure - not what we're looking at here.

As far as the second part goes, we still have the concept of a high water mark in 10g as the limit up to which blocks must be read for a full table scan.
Re: Select with no conditions taking long time, too many blocks, pctused [message #413897 is a reply to message #413714] Sat, 18 July 2009 06:55 Go to previous message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
In perhaps one of my more clever ideas. (if it works)
I believe partition exchange with a table (actually partition) would empty the blocks and reset the high water mark without affecting any processes using the log procedure.
Could copy data without taking anything offline.

Will test one time to make sure.
Previous Topic: Force the optimizer to use the primary key index
Next Topic: Problem with query having order by , distinct clause
Goto Forum:
  


Current Time: Tue Dec 06 12:30:11 CST 2016

Total time taken to generate the page: 0.10123 seconds