- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Any feedback for init.ora parameter discrete_t...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-29-2005 04:07 AM
тАО04-29-2005 04:07 AM
Thanks,
Eric Antunes
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-01-2005 09:05 PM
тАО05-01-2005 09:05 PM
Re: Any feedback for init.ora parameter discrete_transaction_enabled?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2005 12:10 AM
тАО05-02-2005 12:10 AM
Re: Any feedback for init.ora parameter discrete_transaction_enabled?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2005 12:17 AM
тАО05-02-2005 12:17 AM
SolutionAs 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2005 12:43 AM
тАО05-02-2005 12:43 AM
Re: Any feedback for init.ora parameter discrete_transaction_enabled?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2005 01:51 AM
тАО05-02-2005 01:51 AM
Re: Any feedback for init.ora parameter discrete_transaction_enabled?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2005 01:52 AM
тАО05-02-2005 01:52 AM
Re: Any feedback for init.ora parameter discrete_transaction_enabled?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2005 03:21 AM
тАО05-02-2005 03:21 AM
Re: Any feedback for init.ora parameter discrete_transaction_enabled?
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.)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2005 08:44 PM
тАО05-02-2005 08:44 PM
Re: Any feedback for init.ora parameter discrete_transaction_enabled?
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-02-2005 08:45 PM
тАО05-02-2005 08:45 PM