Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

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
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.
Fred Ruffet
Honored Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

I can not certify this, and maybe someone may say it's right or wrong, but here is what I saw when working on the subject :
When issuing dd with different block sizes, and monitoring with glance, I can see that transfered size divided by IO count is equal to dd's bs, while bs inferior to 256KB. If bs is larger than 256KB, IOs seems broken and are always 256KB large.
I never managed to find a parameter to increase that size. scsi_maxphys default value is set to 1MB as a default, so it may not be the blocking factor.
Note that my tests were only for reads.

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,

From what I understood scsi_maxphys is the overall limit (must not be changed) but there is a hardware limit, depending on the device and its associated drivers.

To you use a different value than the default 1 (for 1 CPU) for spin_count?

Regards,

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

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

Hi Eric,

Here's a script which tells you a lot about tunable parameters. Also physical reads and writes. Read it carefully, execute it several times at the same time of the day and try to change the parameters positively.
you can always use Oracle Enterprise Manager to view things graphically.

Cheers,

Renarios
Nothing is more successfull as failure
Eric Antunes
Honored Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

Hi renarios,

You missed the script...

Regards,

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

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

Eric,

What is spin_count ?

Renarios,

Oracle version is 8.0.5. OEM of such old versions (DBA studio) is rather buggy and less complete than nowadays. If he uses last OEM version, I doubt that parameters descriptions are accurate.

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?

Fred,

spin_count is "Amount to spin waiting for a latch". But I think it is only usefull to be set with a different value than de default (1) for multiprocessor systems...
Each and every day is a good day to learn.
renarios
Trusted Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

Sorry Eric,

Here's take two:

Cheers,

Renarios
Nothing is more successfull as failure
Fred Ruffet
Honored Contributor

Re: Any feedback for init.ora parameter discrete_transaction_enabled?

Sorry for the question, but this parameter has been obsoleted in 8i. It's been a long time since I use it, and I don't remembre about its setting... :-/ (Have you read Note 30832.1 in metalink ?)

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?

Thanks Renarios!

Fred,

yes I've readed it. I'm reviewing all init.ora parameters that are not derived...

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?

I'm closing this thread.

Thanks to all!
Each and every day is a good day to learn.