Skip to Main Content
  • Questions

  • PCTFREE impact on query performances

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vineet.

Asked: February 25, 2016 - 5:33 am UTC

Last updated: February 26, 2016 - 10:17 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hi Tom,

Greetings !!

I have a table IA_GL_REVENUE in datawarehouse instance. This table is prone to updates more than inserts. On a daily basis the number of updates are 5 times the inserts.
Based on this I has set PCTFREE to 50. I have also set the INITRANS to 25 since there are many deadlocks occuring due to shortage of ITL slots. ASSM for the tablespace is AUTO.

Post setting the PCTFREE to 50, I have a couple of observations :

Number of blocks used by the table is doubled up.
earlier blocks :- 8681086
current blocks :- 17523678

Size of Table is almost twice the original size.
earlier size :- 67 GB
current size :- 135 GB

My question is will the sql query performance get hit which have index scans or full table scans.
Will it increase the query duration time as the number of database blocks for the table have been increased.

Need your advice on this.

Thanks
Vineet

and Connor said...

Yes it could.

The number of updates is not really important (although it might be related) - what really matters is how much your rows *grow* over time.

eg

insert into T values ( [100 bytes] );
update T set col = [a different 100 bytes];
update T set col = [a different 100 bytes];
update T set col = [a different 100 bytes];
update T set col = [a different 100 bytes];

then pctfree can still be nice and small, because I'm changing but not growing the row.

whereas

insert into T (col1) values ( [10 bytes] );
update T set col2 = [10 bytes];
update T set col3 = [10 bytes];
update T set col4 = [10 bytes];
update T set col5 = [10 bytes];

is a larger concern, because I started with 4 null columns and slowly populated them, each time making the row larger.

Take a look at your "table fetch continued row" statistics. Thats a reasonable indicator of how often you are having to follow rows because their pctfree setting perhaps wasn't appropriate.

But one more general observation... updates generally dont work out so well in a data warehouse, because you immediately lose the opportunity to aggressively compress data. Perhaps think about revisiting that approach if possible.

Similarly, if its a data warehouse, you would expect most queries to be large scans...so it may well be better to allow more row migration in order to keep the overall table sizes down to cater to those full scans.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Table fetch continued row

Vineet Kurrewar, February 26, 2016 - 4:02 am UTC

Thanks Tom.

Here are the stats :-

SQL> select a.sid, b.name, a.value from v$mystat a, v$statname b where a.statistic# = b.statistic# and lower(b.name) like 'table fetch%';

SQL> SELECT SUBSTR(name, 1, 30) "Parameter", value "Value"
FROM v$sysstat
WHERE name = 'table fetch continued row'; 2 3

Parameter Value
------------------------------ ---------------------------------------------
table fetch continued row 117755236620

The database is running since 15 months. Is that too big a number to be considered.

I did a normal count(*) on the table with default pctfree and on the table with pctfree 50.

pctfree 50 : select count(*) from ia_gl_revenue

this took 9 minutes to return the output

pctfree default : select count(*) from ia_gl_revenue_bkp

This took only took minutes.

Does this simple query check implies indirectly that setting pctfree to 50 will definitely impact the SQL query performance badly

Thanks
Vineet
Connor McDonald
February 26, 2016 - 5:03 am UTC

What does

select STARTUP_TIME from v$instance;

return. The sysstat figure is from that moment in time.

Table fetch continued row

Vineet Kurrewar, February 26, 2016 - 4:02 am UTC

pctfree default took 2 minutes.

Instance start time

Vineet Kurrewar, February 26, 2016 - 5:35 am UTC

The start time is from 22-November-2014

SQL> select instance_name, startup_time from v$instance;

INSTANCE_NAME STARTUP_T
---------------- ---------
DWP 22-NOV-14

Connor McDonald
February 26, 2016 - 10:17 am UTC

So that means

SQL> select 117755236620  / ( sysdate - date '2014-11-02' ) / 86400 from dual;

117755236620/(SYSDATE-DATE'2014-11-02')/86400
---------------------------------------------
                                   2829.02953


you are doing 2800 continued row fetches per second (since Nov 2014). That doesnt look good :-) although its not a proof that its all related to this particular table. But we'll assume its the main contributor based on your assessment so far.

So when it comes to query performance, obviously you've got a lot of read activity chasing down those row pieces. So you probably want to pursue a strategy of:

1) correcting some of the current data (perhaps via selective reorganisation via something like dbms_redefinition)

2) choosing a pctfree that best balances row migration versus query performance.

3) perhaps having a re-think on the repeated updates strategy for data warehouse usage.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.