I'm working with an Oracle-based data warehouse with Net8 using 2KB as the packetsize (SDU). Is this typical or...
is it usually set higher e.g 32KB? What are the effects of having it set at 2KB?
Typically, people leave the SDU and TDU Net8 parameters at their default of 2KB. For OLTP systems, this can be fine since many requests and results are small. Unfortunately, this doesn't always work well, even for data warehouses, where result sets can be much larger.
Before you attempt to raise SDU and TDU, you'll want to determine if you actually need to increase these parameters. To do that, query V$SYSTEM_EVENT or V$SESSION_EVENT depending on if you are looking at the system or session level. You will want to query for events named 'SQL*Net more data from client', 'SQL*Net more data to client'. If you are curious about performance over database links, substitute 'link' for 'client' in the above event names. The idea is to keep the number of waits for these events pretty low. If you have a high number of waits for these events, then consider raising SDU and TDU. If you have changed these parameters, then run your application again and see if the the number of waits for the above events decreases. This will tell you if you are making progress in tuning SDU/TDU.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.