Oracle Archives » Page 12 of 15 » Musings of an IT Implementor

Oracle Documentation Is a Moving Target

I appreciate that internet content is better when it’s dynamic, but sometimes there’s a need for static content; or should I say more accurately, static links to content.

I’m right in the middle of compiling a new tutorial on installing Oracle 11gR2 on Oracle Enterprise Linux 6.1.
The Oracle docs in HTML format have been on-line for years now, but I’ve never really referenced them over a period of time with such specific requirements.
I’ve been using the software pre-requisites section of the Oracle 11gR2 docs and have bookmarked them in my browser of choice. At least I thought I had. Until today, when I click the link I get an Oracle web page stating “404 not found”.
What happened? What caused the move of the actual URL?
Here’s what I was using before:
https://download.oracle.com/docs/cd/E11882_01/install.112/e16763/pre_install.htm#CIHIDJAH

Here’s the same content today:
https://download.oracle.com/docs/cd/E11882_01/install.112/e24321/pre_install.htm#CIHFICFD

Looks like the e16763 has been replaced with e24321 and the internal bookmark (anchor) in the page (CIHIDJAH) has been changed to (CIHFICFD).

All links internally on the page are relative links and not absolute, and when you try and navigate up the path, you get a 404. So I guess that Oracle themselves don’t want you to bookmark the links.

Lesson learned, always capture what you need elsewhere and duplicate everything in case you can’t find it or it gets removed…

Oracle Storage Sub-system Load Stress Testing

Whilst looking around for an Oracle equivalent stress testing freebie (like SQLIO for SQL Server), I found out about ORION (ORacle I/O Numbers).
It’s been about for a while and can be used to stress test storage systems.
It’s a simple single binary file that generates I/O load on a storage system using Oracle’s I/O call stack.
No need to install Oracle at all!

Unfortunately for me, it only works with file systems that have Async I/O enabled.
I did not, and therefore could not use ORION.
When I finally get time to install Oracle on my new Oracle Enterprise Linux 6.1 environment, I’ll give it a go.

Flushing Cursor SQL Plan Out of Library Cache

I can never remember how to do this.

I wanted to flush a specific SQL execution plan out of the Oracle 11g SQL Library Cache so that I could try and compare a before and after SQL execution scenario using SQL trace and then tkprof’ing it.
Here’s the link to the blog that helped me remember again:

https://prutser.wordpress.com/2009/04/19/flushing-a-cursor-out-of-the-library-cache/

Thanks.

SAP on HP-UX

There are various methods of tuning an SAP system, but sometimes the operating system vendor will permit specific tuning so that the SAP system can take better advantage of the available resources.
Usually, you can find whitpapers on the O/S or hardware vendor’s web site, but also SAP will themselves develop a range of SAP notes that will offer avice and guidance when running on certain approved hardware partners.
If you’re using HP-UX 11iv3 (11.31) and you are running SAP, then here are some gems direct from SAP themselves:

Note:
172747 HP-UX OS Parameter Recommendations
798194 HP-UX async IO trace files
837670 HP-UX OS Patch Requirements
918279 HP-UX SAPOSCOL Versions
1077887 HP-UX filesystem options
1329878 Using non-default pagesize on HP-UX
1351051 Support of Oracle on HPVM
1457063 HP-UX Consolidating SAP Systems
1575609 Future HP-UX support for SAP on Oracle

I would highly recommend the OS Parameters note.
If you’re suffering I/O performance issues, make sure that you have set the correct block size as per 1077887.
A huge (>10%) reduction in memory usage can be obtained following 1329878, but only if you are using Oracle 11g.

Using the direct I/O option alone on a VxFS 5.0 environment decreased Data Protector backup times by over 1 quarter e.g. a 1.5 hour backup reduced to ~1 hour.

I haven’t investigated the HPUX_SCHED_NOAGE O/S parameter option yet (I need the audacity to recommend it to the server team!), but according to this excellent blog post by Christian Bilien, it should help your environment significantly if you’re running more than one Oracle database on a SMP system.
My reasoning is that there will be more than one CPU hungry Oracle thread and they could each be battling against each other (ageing each other out) even more so in a well tuned system with less I/O (large SGA/PGA and well tuned SQL) meaning less voluntary context switches and more forced ones. It’s possible that in this situation, without HPUX_SCHED_NOAGE, you could start to see CPU bottlenecks.

HP also produce their own performance recommendations for running Oracle.
Here’s one on the HP recommended filesystem IO options.
Notice that it says if you’re using VxFS v5.0, you don’t need to worry about 1KB block sizes on Redo and Archive Log partitions.

WARNING: Take heed in the warning about Progress databases on file system partitions with directio enabled on the mount options. I have personally experienced issues where Progress applications have had a 20x decrease in performance! It’s a fact, Progress is not very good without the file system cache.

This one is specific to tuning HP-UX TCP/IP. Very useful if you’re seeing networking bandwidth problems in you environment.

Finally, the HP-UX Performance Cookbook provides an excellent source of information.
I have noticed that some of the SAP Oracle recommended parameters (830576 “Parameter Recommendations for Oracle 10g”) are specific to HP-UX, I have found that a good set of descriptions for some of these are covered in this HP Oracle paper: The Oracle Database on HP Integrity servers.

It’s really a trade off between what SAP say and what Oracle say.
Obviously Oracle know there own database system, so anything Oracle says can also be included.
I generally follow the top down rule, where SAP overrides anything that is contradictory from HP or Oracle, Oracle overrides anything contradictory from HP, and I only implement any parameters from HP if neither Oracle or SAP have anything to say on the matter:
– HP (OS)
– Oracle (RDBMS)
– SAP

Monitoring Index Usage In Oracle 10G Using V$OBJECT_USAGE

Have you got some large indexes creeping around?
Some of my systems have 10GB indexes.
These take valuable resources away from the Oracle database:
– DML time (INSERT, UPDATE etc).
– Stats generation time.
– Segment space (hard disk).
– Structure validation time.
– Recovery time.

Removing the index may not be an option. But what if it’s not actually used!!

How can we tell if it’s used?
Well you could monitor all SQL in the shared pool using the AWR capture script https://www.remote-dba.net/oracle_10g_tuning/t_oracle_index_usage_tracking.htm, or you could use Jonathan’s script https://jonathanlewis.wordpress.com/segment-scans/ to see if the index segment(s) has been scanned (full scan). But these don’t comprehensively give you a definitive answer.
There could be holes in your monitoring.

Instead, you could use the V$OBJECT_USAGE view to monitor index usage.
Although a very basic method, if you only want to know definitively if an index has been used or not, then it gives you the answer.
It is described in great detail here: https://wiki.oracle.com/page/Monitor+Index+Usage

Turn on:
alter index <INDEX> monitoring usage;

ALTER INDEX SAPSR3."JCDS~Z01" monitoring usage;

Turn off:
alter index <INDEX> nomonitoring usage;

ALTER INDEX SAPSR3."JCDS~Z01" nomonitoring usage;

Check usage (must be as owner of monitored objects):
select count(1) from v$object_usage;

Or use query below to see all monitored objects:

SELECT u.name owner , io.name index_name , t.name table_name , DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring , DECODE(bitand(ou.flags, 1), 0, 'NO', 'YES') used , ou.start_monitoring start_monitoring , ou.end_monitoring END_MONITORING FROM sys.user$ u , sys.obj$ io , sys.obj$ t , sys.ind$ i , sys.object_usage ou WHERE i.obj# = ou.obj# AND io.obj# = ou.obj# AND t.obj# = i.bo# AND u.user# = io.owner#;