Operating System - HP-UX
1748232 Members
3699 Online
108759 Solutions
New Discussion юеВ

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

 
SOLVED
Go to solution
Eric Antunes
Honored Contributor

Any feedback for init.ora parameter discrete_transaction_enabled?

Does it works well on Oracle 8.0.5?

Thanks,

Eric Antunes
Each and every day is a good day to learn.
19 REPLIES 19
Indira Aramandla
Honored Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

Hi Eric,

I think Discrete transaction has become obselete from version 8.1.3. So you can use it with 8.0.5

This is what oracle says about its usage

Discrete transaction processing is useful for transactions that:

- Modify only a few database blocks

- Never change an individual database block more than once per transaction

- Do not modify data likely to be requested by long-running queries

- Do not need to see the new value of data after modifying the data

- Do not modify tables containing any LONG values

The Tuning guide includes quite a bit of information on when and how to use these efficiently.


IA
Never give up, Keep Trying
Eric Antunes
Honored Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

Thanks Indira!

I'm checking again all the init.ora parameters.

What value do you recommend for db_file_multiblock_read_count on OLTP environments? I have it set to 32 but it may be high, what do you think??

Thanks,

Eric Antunes
Each and every day is a good day to learn.
Yogeeraj_1
Honored Contributor
Solution

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

hi Eric,

As mentioned by Indira, this parameter implements a simpler, faster rollback mechanism that, under certain conditions, can improve performance. You can obtain greater efficiency in this mode, but the qualification criteria for what kind of transactions can take advantage of discrete transactions are quite strict.

Hence, when you use a discrete transaction, all the changes made to any data will be deferred until the transaction commits. Redo information is generated but undo information isn't generated. The redo information is stored in a separate location in memory and gets written to the redo log when the transaction commits.

see also: http://docs.rinet.ru/O8/ch22/ch22.htm
for more details about its usage.

However, not that there is a cautionary note on orafaq.

NOTE: This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.


see: http://www.orafaq.com/parms/parm304.htm


regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

hi again!

The Multiblock read count threshold depends on your operating system and hardware. You may wish to run a "test" many times, on as many devices as you can, to get an overall view of what might be best for your system. Also, this points out the usefulness of gathering system statistics on your own machine (with oracle 9i release 2 and later). Each machine will have its owne unique fingerprint as far as performance profiles go.

Details about the "test" i mention above is available in the latest book by Thomas Kyte - Effective Oracle by Design pp 449.

Using this "test", he shows how to draw a DB_FILE_MULTIBLOCK_READ_COUNT Value Effect table which allows you to draw a corelation between the number of times you wait and the number of blocks divided by the multiblock read count, up to a point...

if you need further details, do let us know

regards
yogeraj

No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

Hi Yogeeraj,

The goal is to minimize I/O during table scans, right? So may be I can get some clues running the following query at the end of the day (the database shuts down at night):

select n.name, s.value
from v$statname n , v$sysstat s
where n.statistic# = s.statistic# and
n.name like 'table scans (%tables)'

I need do minimize scans on long tables, don't you think?

Thanks,

Eric

Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

I'm still runing on 8.0.5...
Each and every day is a good day to learn.
Fred Ruffet
Honored Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

"to minimize I/O during table scans" is not really right. It will try to reduce I/O cost on sequential reads.

Assuming 8K block and 16 as db_file_multiblock_read_count : If you try to read 8K, Oracle will read 128K. If you try to access the next 120K, they will already be in the cache.

Now, if you set it too high, 64 for example, it will try 512K reads. Even if these blocks are physically sequential on disks, at least 2 IOs will be performed as long as IO size for HP is 256K. Depending on what as already been read, an average of 3 IOs is necessary. So 16 is a good thing, as long as it will produce an average of 1 IO. Now, it can go more complicated, if you consider your OS block of 64K... ;-) Easiest way is to try out different values. As you already have 32, try to set it back to 16, and see if things are better or not.

If you don't wan't to deal with OS side of the things, let's consider a last thing : Increasing your db_file_multiblock_read_count parameter will make oracle think sequential reads are less expensive and change their cost in optimizer choices. So CBO will more often go for full scans. But maybe are you using RBO (8.0.5 version is rather old, so maybe apps were thought this way).

Regards,

Fred

--

"Reality is just a point of view." (P. K. D.)
Eric Antunes
Honored Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

Hi Fred,

Thanks for you reply.

I've changed db_file_multiblock_read_count to 16 to see if I've improvements but I'm not really sure how to know that?

You said that IO size for HP is 256K but I've read threads here at the forum refering as scsi_maxphys as the maximum I/O size...??

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

scsi_maxphys is a kernel parameter...
Each and every day is a good day to learn.