My new book coming out - Oracle Exadata Expert's Handbook

I am pleased to announce that my new book on Exadata is becoming available on Amazon very soon.


Considering that Exadata was the hot new kid on the Oracle block, I had been contemplating and reaching out to a lot of folks about writing a book on Exadata for over a year, before the stars got aligned and we started working on this project. From inception to writing to technical review to production, authoring a book is a complex, labor-intensive, lengthy, and at times painful process; - this project took more than 2+ years to complete and I along with my co-authors are very proud of the outcome.


You can order the book online at:


Amazon - Oracle Exadata Expert's Handbook


I have copied the Preface from the "Publisher's Website: Addison Wesley" to provide a brief overview to the Reader:

Oracle Exadata Expert's Handbook - Addison Wesley - Preface

Blazingly fast, Exadata is Oracle’s complete database machine—with unparalleled performance brought about by engineering hardware and software technologies from Oracle and Sun. Exadata has been widely embraced by enterprise users worldwide, including government, military, and corporate entities.

Authored by a world-renowned veteran author team of Oracle ACEs/ACE directors with a proven track record of multiple bestselling books and an active presence on the Oracle speaking circuit, this book is a blend of real-world, hands-on operations guide and expert handbook for Exadata Database Machine administrators (DMAs).

Targeted for Oracle Exadata DBAs and DMAs, this expert’s handbook is intended to serve as a practical, technical, go-to reference for performing administration operations and tasks for Oracle’s Exadata Database Machine. This book is a

Practical, technical guide for performing setup and administration of Oracle’s Exadata Database Machine

Expert, pro-level Exadata handbook

Real-world, hands-on Exadata operations guide

Expert deployment, management, administration, support, and monitoring guide and handbook

Practical, best-practices advice from real-life Exadata DMAs

The authors have written this handbook for an audience of intermediate-level, power, and expert users of the Exadata Database Machine.

This book covers both 11g and 12c versions of the underlying Exadata software.

Setting PeopleSoft-specific init.ora parameters for the Oracle database

In this post, i have compiled a list of the common custom parameters set for the Oracle database in PeopleSoft applications, some of which can have a dramatic performance impact on the system. Some of the parameters mentioned are also applicable for general Non-PeopleSoft database applications. Please note that not all of them may apply in your current setup/version - i have included MOS Notes for reference and further reading/exploration.
alter system set "_unnest_subquery"=FALSE scope=spfile sid='*';
alter system set "fast_start_parallel_rollback"=false scope=spfile sid='*';
alter system set "_gby_hash_aggregation_enabled"=FALSE scope=spfile sid='*';
alter system set "_ignore_desc_in_index"=TRUE scope=spfile sid='*';
alter system set "_smu_debug_mode"=4 scope=spfile sid='*';
alter system set "optimizer_adaptive_features"=false scope=spfile sid='*';
alter system set "_optimizer_skip_scan_enabled"=false scope=spfile sid='*';
alter system set "nls_length_semantics"=char scope=spfile sid='*';
alter system set "cursor_sharing"=exact scope=spfile sid='*';
alter system reset "db_file_multiblock_read_count" scope=spfile sid='*';
alter system reset "_optimizer_cost_based_transformation" scope=spfile sid='*';
alter system reset "_complex_view_merging" scope=spfile sid='*';
MOS Notes/Documents:

• E-ORA Advice for the PeopleSoft Oracle DBA (Doc ID 1445965.1)
  From 10gR2, HASH UNIQUE Operation Returns Results in UNSORTED ORDER by Default
  (Doc ID 341838.1)
• Required Interim Patches for the Oracle Database with PeopleSoft (Doc ID 1100831.1)
• Operating System, RDBMS & Additional Component Patches Required for Installation
  PeopleTools - Master List (Doc ID 756571.1)
• ANNOUNCEMENT: Deprecating the cursor_sharing = 'SIMILAR' setting. (Doc ID 1169017.1)
• E-ORA: How to convert an Oracle Non-Unicode Database to a Unicode Database (Doc ID 1437384.1)
• Init.ora Parameter "FAST_START_PARALLEL_ROLLBACK" Reference Note (Doc ID 68932.1)
• Removing Descending Indices for PeopleSoft Databases (Doc ID 1909646.1)
• Master Note: Troubleshooting guide for Automatic Undo Management (Doc ID 1579081.1)

 

INITRANS and MAXTRANS in Oracle - Kickstarter Resources

The INITRANS parameter allows you to configure concurrent access to a block in Oracle, defining the initial number of Interested Transaction List (ITL). MAXTRANS defines the maximum value for concurrent access=255. In this post, i have compiled a list of MOS documents shedding light on INITRANS and MAXTRANS, how to configure them and so forth.

• INITRANS Relationship with DB_BLOCK_SIZE. (Doc ID 151473.1)
• Master Note: Oracle Transaction Management (Local) Overview (Doc ID 1506115.1)
• Create Index Initrans Does Not Allocate the Number Specified (Doc ID 729445.1)
• Unable To Increase Initrans For LOB Index Segment (Doc ID 1617125.1)
• How To Modify The Physical Attribute INITRANS For An Existing Table Or Index? (Doc ID 549074.1)
• How to Re-Organize a Table Online (Doc ID 177407.1)
• How To Modify The Physical Attribute INITRANS For An Existing Table Or Index? (Doc ID 549074.1)
• When a storage parameter such as; Initrans is modified DBA_TABLES view is not updated (Doc ID 1484309.1)
• DataPump Export/Import Generate Messages "The Value (30) Of Maxtrans Parameter Ignored" in Alert Log (Doc ID 455021.1)

Here is an example command on how to change the INITRANS for an existing table.

ALTER TABLE SCOTT.EMPTABLE INITRANS 64 MAXTRANS 255;

How to query SET undocumented (Hidden) INIT.ora parameters in Oracle?

This post is fairly simple - shows how to query the SET undocumented (Hidden) INIT.ora parameters in your Oracle database.  Each one of these undocumented parameters should only be set with Oracle's consent and approval. The following shows an example query.
SELECT name,description,value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
_smu_debug_mode
<debug-flag> - set debug event for testing SMU operations
4
_ignore_desc_in_index
ignore DESC in indexes, sort those columns ascending anyhow
TRUE
_unnest_subquery
enables unnesting of complex subqueries
FALSE
_gby_hash_aggregation_enabled
enable group-by and aggregation using hash scheme
FALSE
_trace_files_public
Create publicly accessible trace files
FALSE

Getting the Best Performance on Oracle for PeopleSoft Applications: Kickstarter Resources

I have compiled MOS Resources on how to go about getting the best performance on Oracle for PeopleSoft Applications - this includes Best Practices as well as resources about the PeopleSoft Performance Monitor.

• PeopleSoft Performance Monitor Red Paper (Doc ID 747510.1)
• E-ORA PeopleSoft Enterprise Performance on Oracle 11g Database (Doc ID 1460735.1)
• E-ORA Red Paper: PeopleSoft Enterprise Performance on Oracle 10g Database (Doc ID 747254.1)
• PeopleSoft Performance Monitor Database Schema and Use Cases (Doc ID 704808.1)
• PeopleSoft Enterprise DFW Plug-In - Performance Monitor Configuration Information (Doc ID 1324755.1)
• E-PerfMon: Performance Monitor System Performance Page Loads Slow (Doc ID 970485.1)
• E-Perfmon: Performance Monitor No Longer Shows User Sessions When Using Coherence in a Domain (Doc ID 1678612.1)
• If Performance Monitor Is Down Will It Collect Data In a Repository About the Monitored Systems? (Doc ID 2104397.1)
• E-Perfmon: Stale Agent Data Detected and no User Sessions in Performance Monitor (Doc ID 622919.1)
• Top Solutions - PeopleTools Server Tools: Application Server, Performance Monitor, REN, Tuxedo, Weblogic, Websphere, Verity, PSEM (Doc ID 1304972.1) 
• -PHC: PeopleSoft Health Center vs Performance Monitor (Doc ID 2127778.1)
• Prevent and Resolve Performance Issues Advisor: PeopleSoft (PSFT) (Doc ID 1437143.1) 
• Information Center: Optimizing Performance for PeopleSoft Purchasing 9.1 and 9.2 (Doc ID 1454383.2)
• Information Center: Optimizing Performance for PeopleSoft SCM Strategic Sourcing 9.1 (Doc ID 1509869.2)
• Information Center: Optimizing Performance for PeopleSoft SCM Cost Management 9.1 (Doc ID 1509864.2)
• EAR8.8+: Troubleshooting Performance Issues in FSCM PeopleSoft Applications (Doc ID 984534.1)
• E-AE: F.A.Q. on ReUse (or ReUse Statement) Option of SQL Sections in PeopleTools 8.5x Application Engine Programs (Doc ID 1936139.1)

Oracle Real Application Clusters (RAC): Jumbo Frames Notes/Recommendations

In this post, i shall quickly summarize recommendations for implementing Jumbo Frames on the Oracle Real Application Clusters (RAC) Private Cluster Interconnect.

Notes/Recommendations:

• It is recommended to implement Jumbo Frames for the RAC Cluster Interconnect for better overall performance.
• Interconnect Latency is significantly reduced by implementing Jumbo Frames.
• Implementing Jumbo Frames reduces the UDP, TCP, Ethernet overhead.
• The ifconfig -mtu 9000 shall implement Jumbo Frames with an MTU of 9000 (1500 for smaller frames).
• Test the RAC cluster thoroughly after configuring Jumbo Frames.

References: 

• Recommendation for the Real Application Cluster Interconnect and Jumbo Frames (Doc ID 341788.1)
• How to Validate Network and Name Resolution Setup for the Clusterware and RAC (Doc ID 1054902.1)
• Problem - Dropped segments and bad checksums caused by Jumbo frames. (Doc ID 1073610.1)
• System experiences memory shortage when using nxge with jumbo frames and NFS (Doc ID 1353254.1)

Granting Privileges for Oracle Data Pump Operations in a Database Vault (DBV) Environment

Shown briefly in this post are the privileges that need to be granted to an Oracle User for performing Data Pump operations in a Database Vault (DV) enabled Environment.

Sign in as the DV Owner and run the following command to authorize Data Pump Operations for the user.
EXEC DVSYS.DBMS_MACADM.AUTHORIZE_DATAPUMP_USER(‘username’);

To authorize Transportable Tablespace operations, run the following command:
exec dbms_macadm.authorize_tts_user(uname,tsname);

References:

• How To Export / Import Objects In Database Vault Environment (Doc ID 822048.1)