11GR2 for HPUX now available

As the title states 11GR2 is now available for HPUX on Itanium. http://www.oracle.com/technology/software/products/database/index.html

Co-incidentally I had my first foray into VMware over the last few days and yesterday got an 11GR2 database built (OEL 5.3)

Initial impressions were that the installer is much cleaner but I have not done anything else with it as yet.

Now I need to find my list of 11GR2 new features that I wanted to investigate

Upcoming, my first SIG talk

I am making my first presentation to a SIG meeting in January when I talk about how my company has moved from a site where Oracle was almost non-existent less than two years ago to one that is now delivering what seems to be every product that Oracle has invented (or purchased). It won’t be a ‘how great we are’ approach but rather how making some simple but fundamental decisions have made it much easier to build and support a large and growing infrastructure.

I will be talking about having a set of standards that are very simple but are a fundamental building block in our aim of having the same look and feel across the estate and how a small number of setup scripts can make life so much easier.

I will probably diverge from the straight and narrow and discuss our experiences of running a centralised OEM solution and given we currently have 7 open SRs raised referencing Grid problems I haved an inkling of which way it may be slanted.  However I hope that by the time of the talk I can be much more positive because there is  lot to be said for OEM.

The date is 21st January 2010 in London and an abstract can be seen in the Unix SIG  agenda

I hope to see some of you there

Using Glance to see Oracle process usage

With a little configuration Glance can be made to show oracle process usage where the processes are grouped by SID or by user processes or even by applications such as agent usage. This is based on HPUX experience and whilst I know there are Glance ports for other platforms I do not know if they contain the same functionality. 

Using Glance select A (for Application) and you see how the applications have been grouped

Then S and select 2 which shows the breakdown of the background processes we have associated with the SID  PGSAWM1A

If we then want to see how many user processes are connected to that SID we select option 5 from the previous screen

We are using various versions of Glance all around the version 4.73 and the functionality seems to work in them all. The key to getting this to work is creating an applications file. Rules are held in a flat file(/var/opt/perf/parm) that can only be modified by the root user. Care should be taken to ensure that the configuration information remains unchanged. Applications are defined as follows

application = <Application Name>
<criteria> = <criteria rules>

Example

application = db_SID1A
file = ora_*SID1A*

In this example an application db_SID1A is defined that consists of all processes matching the file criteria ora_*SID1A*. It is also possible to define a user based criteria (see below) that will consolidate process information based on the owner of the UNIX process.

application = Appworx_User
user = apxprdco

In this example the Appworx_User application consists of all processes owned by the apxprdco user.

As stated above the Application criteria are hierarchical and therefore care needs to be taken to ensure that the application criteria are selective otherwise unexpected results can be obtained.

The file must terminate with a default application (see below) that is already part of the deployed file.

# Note: this generic root user entry should be specified at the end of your
# application definitions, if you choose to keep it, because it will
# pick up all processes owned by root which were not included in preceeding
# application definitions.
application = other_user_root
user = root

Impressions from UKOUG 2009

As I spent the 2 days of this week at UKOUG and I quite enjoyed myself. I thought I would do brief comments on some of the impressions I came away with.

How much effort many of the presenters put into preparing their work and how stressful it must be, especially to the first timers.

Did I go to a presentation by anybody who was not an Oak Table member, probably not and that was not because I chose sessions by OT members.

The organisers had definitely listened to feedback last year re the lack of seating in the exhibition area and just outside.

I am not sure if many of the exhibitors would have found it worthwhile as the exhibition seemed rather sparsely attended.

At last 50% of the exhibitors were selling E-Business Suite applications and about another 10% were selling license management solutions.

The two presentations I enjoyed the most were ‘SQL Plan Management’ by Harald van Breederode and surprisingly enough one entitled ‘The murky world of Database character sets’ by Paul Hancock. Perhaps the best presentor I saw was Lloyd Carpenter on Active Dataguard. He spoke for about an hour and I think he could have done 3 hours if he had been asked to. His enthusiasm just leapt out and grabbed the audience and the slides were very well prepared.

A couple of negative aspects. The first was perhaps the timing of the event relative to the release of 11GR2, however I did get a bit fed up of hearing about how good 11GR2 is and yet it is only available on couple of platforms at the moment and there are no signs for when it will be available on HPUX. I know that is personal to my situation and not a fault of the event but I did find it a bit irritating.

Finally, I left one presentation half way through because I had to control myself from jumping up and asking the presenter how many people would be likely to do what he was talking about. This was a presentation on RAC but it focused almost entirely about the availability of a couple of undocumented parameters that could be used to change the priority of processes. The presenter did say that be careful when to use this and only do it under the direction of Oracle support but each myth he mentioned seemed to end up with discussion of process priorities. I was told later that a number of other people had left early as well.

Overall a well organised and well attended event and I think the 3 day format worked well.

What does the SQL92_SECURITY parameter actually do?

Whilst looking at our Grid Control I noticed that we had a few policy violations in the security configuration stating that SQL92_SECURITY=FALSE. These were against some of our older databases as we set it to true on all new builds. I thought I knew what this parameter did but had a look around and determined the following :-

It stops anyone being able to update or delete rows from a table owned by another user if you were using a where clause and did not have select on that table. Fair enough I thought, it sounds like it is some form of ANSI 92 standard and it is all a bit meaningless. I could not see what the issue was but I was intrigued as to why it was an Oracle security recommendation.

I set up the following test case on both a 10G and 11G database where SQL92_SECURITY was set to TRUE. Very simple but I had no failures other than the select statement which had insufficient privileges.

create user usera identified by usera;
create user userb identified by userb;

grant create session,create table to usera;
grant create session to userb;
alter user usera quota unlimited on users;
alter user userb quota unlimited on users;
show parameter sql92_security
connect usera/usera
create table tab1 as select * from all_objects where rownum <101;
select count(*) from tab1;
grant update,delete on tab1 to userb;

connect userb/userb
update usera.tab1 set object_type = 'WAS PACK' where object_type='PACKAGE';
commit;
select distinct status from usera.tab1;
prompt fails as no select privilege on usera.tab1
prompt SQL92 set to true and yet update is allowed ??
prompt now let's try a delete
delete from usera.tab1 where rownum <21;

prompt delete with a where clause works as well

select count(*) from
prompt end of case study

So what is all that about, where was I going wrong and why were my updates and deletes allowed when they shouldn’t have been.

This parameter is meant to prevent you from deleting a table when you specify a condition based on table’s columns. Conditions that are not checking the values stored in the table are allowed(rownum < X, etc.)

In my testcase I deleted the table rows without a condition which read one of the the tables columns. If instead of executing delete from usera.tab1 where rownum =21 I had executed delete from usera.tab1 where object_name = ‘TEST’; I would get an error.

The purpose of this parameter is to allow a user one to delete table data without giving them the possibility of guessing what values are stored in that table. Imagine that there is a table with contacts and I only have delete rights on that table. If I am able to delete the table with a condition based on table columns I can find out via multiple attempts the contents of the table. I can for example find out whether ‘John Smith’ is a contact or not :

SQL> delete from contacts where contact_first_name='John' and contact_family_name='Smith'; 

1 row deleted
SQL> rollback;
Rollback complete.

Since the row was deleted I know now that John Smith is a contact. Then I do a rollback of the transaction and the table is as it was before the delete. If the parameter is set to TRUE I cannot make use of this trick and I can only delete the rows blindly. I will be able to delete without knowing what I am going to delete.

Once I understood what could be done I understood the reason for the init.ora parameter to be enabled. However there is very little information out on the web which explains the reasons and that is why I think the parameter can be misunderstood.

Prior to enabling it on a database that is in use I would check that nobody had update or delete privileges ona table that they did not own where they did not have select privilege.

select *
from dba_tab_privs a
where privilege in ('UPDATE','DELETE')
and not exists (select null
                from   dba_tab_privs b
                where  privilege = 'SELECT'
                and    a.grantor = b.grantor
                and    a.grantee = b.grantee
                and    a.table_name = b.table_name);

Maxing out CPUs – script

I have long subscribed to the  ORACLE-L mailing list  and I find it a great source of ideas and views on the management of Oracle databases. As a sidenote for anybody who used it in the past it seems to be much stronger now as a community than previously where there was too much RTFM and other flaming type responses.

In the last couple of days there has been a thread running entitled Stress my CPU’s started by Lee Robertson where he was asking for a way to ‘hammer the CPU’s on the box as we want to test dynamically allocating CPU’s from another partition to handle the increased workload’.

The strength of the list is that there were a number of quality responses but my hat goes off to Tom Dale for producing this gem

set serveroutput on

declare

l_job_out integer;

l_what dba_jobs.what%type;

l_cpus_to_hog CONSTANT integer :=4;

l_loop_count varchar2(10) := '500000000'; begin

/*

** Create some jobs to load the CPU

*/

for l_job in 1..l_cpus_to_hog loop

dbms_job.submit(

job => l_job_out

, what => 'declare a number := 1; begin for i in 1..'||l_loop_count||' loop a := ( a + i )/11; end loop; end;'

);

commit;

dbms_output.put_line( 'job - '|| l_job_out );

select what into l_what from dba_jobs where job = l_job_out;

dbms_output.put_line( 'what - '|| l_what );

end loop;

end;

/

Short, sweet and very effective. I will certainly be using it when I want to look at using resource management.

PS,  if you want to stop the jobs running, although they do finish in a few minutes using the default value of 500 million iterations, then use the following dynamic sql.

select 'execute dbms_job.remove('||job||');' from user_jobs where what like 'declare a number := 1%';

 

Problems with SGA being a multiple of 4Gb (and high cpu count)

 We had a server  – HPUX IA64 – 11.1.0.6 where we increased the number of CPUs to 32 and upped memory to 96Gb. After some performance testing we tried to set the parameters as follows

 alter system set sga_target=20G scope=spfile sid=’*';

alter system set sga_max_size=20G scope=spfile sid=’*';

alter system set shared_pool_size=800M scope=spfile sid=’*';

alter system set shared_pool_reserved_size=80M scope=spfile sid=’*';

alter system set pga_aggregate_target=4G scope=spfile sid=’*';

We couldn’t start the database because of lack of memory and we saw ORA-04030 errors

 ORA-04030: out of process memory when trying to allocate 840 bytes (kgsp-heap,kglss)

 However setting sga_target to 21G allowed the database to start.

 We found a note on Metalink suggesting that SGA_TARGET did not like being set to multiples of 4G which seemed to be proved in the above example. However that is an easily disprovable statement  as I am sure we all have databases with SGA_TARGET = 4,8,12G.

 On further analysis and discussions with Oracle we came up with the following information. Firstly the issue is associated with a high number of CPUs and it is a bit difficult for us to test as we don’t any servers we can test on with 32 or more CPUs

 Bug 6624011 – ORA-4030 / ORA-4031 on startup with > 4Gb (and large amount of CPU’s)

This is generic to all platforms and fixed in 11.1.0.7

current workaround is to set the SGA_TARGET to 19G.

Bug 8813366 – ORA-4031 ERRORS DUE TO EXCESSIVE GRANULE SIZE IN 11G

This is generic to all platforms and applicable to 11.1.0.6 and 11.1.0.7

It has the following symptons

 - Memory_target is used and set to a high value (8GB for example).
- The shared pool is divided in subpools due to high number of CPUs.
- ORA-4031 trace file  shows several subpools with no memory allocated.

 

Once we have upgraded Production to 11.1.0.7 another attempt to start up the DB with a multiple of 4G should be made.

However, the startup may fail with ORA-04031 unable to allocate 56 bytes of shared memory. This is documented in Metalink Note 831770.1 and fixed by applying patch 7441663 on top of 11.1.0.7 (recommended) or using one of the following workarounds:

Start the database having explicitly set:

_ksmg_granule_size=16777216

in the init.ora file.

or

Start the database with a reduced number of subpools in shared pool by explicitly setting:

_kghdsidx_count=2

 Hope this is useful heads up to anybody who has a similar issue

Automatically running sql_advisor tasks from ADDM reports

STOP PRESS – 17 Nov 2009  – updated with latest code which works against both 10g and 11g databases

 I am attaching scripts which I wrote a while ago to automatically pick any sql_ids reported in the latest ADDM and then run sql_advisor to report on any tuning advice. I am not suggesting that the information they provide is not available from EM or indeed every task reported needs resolving but it can be a good heads-up on a system you don’t know very well.

These are enabled every hour (can be less depending upon your snapshot interval) and they create a daily file which can be easily reviewed.

I find the real benefit is not on production databases but on dev and test databases that are being used for development prior to production implementation. This is for two reasons, firstly I hope that the team has a good handle on what is happening in production and are aware of issues and secondly we are most likely  to be able to add most value and benefit in development environments before the code is made live.

A couple of ‘issuettes’. The output from the ADDM report is different between 10g and 11g so I have amended the awk file to cater for both versions. I have been having an ongoing problem with tghe sql_advisor tuning task timing out on some systems and consequently leaving the task created for the next run. I have therefore amended the loop to drop the task at several points which looks untidy in the output file bit does seem to resolve the problem. 

I hoped to attach a zip file containing 4 scripts but cannot see how to do it without a plug-in which is a problem on my works PC. so in the meantime I have pasted the code of each of 4 files.

tuning_recommendations.ksh which is the controlling script

#! /bin/ksh
# loop though the file produced from get_addm_report.sql and put the gathered sql-ids into a flat file
# awk the file to get just the SQL_ID
# for each sql_id create a task, execute that task, run the report and then delete the task
#
# The delete tuning task job is run an additional twice because if the tuning task times out then it does not clean up properly
# Better to see a few failures in this job that not run the sql_tuning_advisor at all.
#if [ -d /home/oracle/logs ]
then
   rm  /home/oracle/logs/tmp*.log
   else
   mkdir /home/oracle/logs
   exit
fi
if [ $# -ne 1 ]
then
    echo "No ORACLE SID  - exiting"
    exit
fi
# execute ORACLE's .profile
#
#. ~/.profile
unset ORAENV_ASK
#
# set up environment variables.
#

ORACLE_SID=$1
. /usr/local/bin/oraenv ${ORACLE_SID}
export ORACLE_HOME=`cat /etc/oratab | grep $ORACLE_SID | awk -F: '{print $2 }`
export PATH=$ORACLE_HOME/bin:$PATH
export ORAENV_ASK=NO
today=`date +%d-%b-%Y`; export today
LOGDIR=$HOME/logs
LOGFILE=$LOGDIR/get_addm_${today}.log
REPORTFILE=$LOGDIR/sql_advisor_report_${ORACLE_SID}_${today}.log

#
#
sqlplus -s /nolog  <<SQLEND
connect / as sysdba
     spool $LOGDIR/tmp_${ORACLE_SID}_1.log
     @/shared/oracle/performance/get_addm_report.sql
     spool off
     exit
SQLEND

<p>&nbsp;</p>

cat $LOGDIR/tmp_${ORACLE_SID}_1.log|awk -f /shared/oracle/performance/tuning_recommendations.awk |awk '!a[$0]++' > $LOGDIR/tmp_${ORACLE_SID}_2.log
cat  $LOGDIR/tmp_${ORACLE_SID}_2.log | awk '$0!~/^$/ {print $0}' > $LOGDIR/tmp_${ORACLE_SID}_3.log

for PLAN in  `cat $LOGDIR/tmp_${ORACLE_SID}_3.log`
do
sqlplus -s /nolog  <<SQLEND >> $REPORTFILE
connect / as sysdba
        begin
        DBMS_SQLTUNE.drop_tuning_task('test_task1');
        end;
        /
     SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'test_task1';
     @/shared/oracle/performance/sql_advisor.sql $PLAN
     exit
SQLEND
done

# tidy up the report file
# tidy up reports > 14 days old
find  $LOGDIR -name "sql_advisor_repor*.log" -mtime +14 -print -exec rm -f {} \;

get_addm_report.sql which gets each task from the last snapshot from dba_advisor_tasks

set long  10000000
set pagesize 50000
column get_clob format a80

select dbms_advisor.get_task_report (task_name) as ADDM_report
from dba_advisor_tasks
where task_id = (
        select max(t. task_id)
        from dba_advisor_tasks t, dba_advisor_log l
        where t.task_id = l.task_id
        and t.advisor_name = 'ADDM'
        and l.status = 'COMPLETED');

tuning_recommendations.awk is a short awk script used to process the output from get_addm_report.sql

BEGIN{
#start at the first line
#OUTFILE="$HOME/logs/outfile.log"
}
{
        {
                if (($1=="RATIONALE:") && ($2=="SQL")) #10G ADDM format
                {
                        F1=$6
                }
                if (($1=="Run") && ($2=="SQL") && ($3=="Tuning") && ($4=="Advisor") && ($7=="SQL") && ($10=="SQL_ID")) #11G ADDM format
                {
                        F1=$11
                }

        }
VAR1=substr(F1,2,13)
print VAR1
}
END{
}

sql_advisor.sql runs the sql_advisor package against each task found.

DECLARE
my_task_name   VARCHAR2 (30);
my_sqltext     CLOB;
my_sqlid        varchar2(30);

BEGIN
my_sqlid := '&1';
my_task_name := dbms_sqltune.create_tuning_task (sql_id=> my_sqlid,
       scope         => 'COMPREHENSIVE',
       time_limit    => 300,
       task_name     => 'test_task1',
       description   => 'test_task1'
    );
END;
/

BEGIN
dbms_sqltune.execute_tuning_task (task_name => 'test_task1');
END;
/

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'test_task1';
SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
set pages 60
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'test_task1')
FROM DUAL;
begin
DBMS_SQLTUNE.drop_tuning_task('test_task1');
end;
/

We have a read only NFS mounted disk available on all database servers and the files are placed in there and initiated by a cron entry for each SID on an hourly basis
40 * * * * /shared/oracle/performance/tuning_recommendations.ksh SID >/dev/null 2>&1

Output is created in a folder $HOME/logs and 14 days worth of reports are kept.

A sample output report (only one task shown but certainly on this Peoplesoft database it would show many tasks)

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_TASK1')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : test_task1
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 11/12/2009 07:43:33
Completed at                      : 11/12/2009 07:44:16
Number of Statistic Findings      : 1
Number of Index Findings          : 1

-------------------------------------------------------------------------------
Schema Name: SYSADM
SQL ID     : cy3fmjha2sjnr
SQL Text   : SELECT M.EMPLID, M.EMPL_RCD, M.SCH_PRIM_ALT_IND,
             TO_CHAR(M.DUR,'YYYY-MM-DD'), M.SEQ_NO, M.CHNG_PRIMARY,
             M.SCHEDULE_GRP, M.SETID, M.WRKDAY_ID, M.SHIFT_ID, M.SCHED_HRS,
             M.SCH_CONFIG1, M.SCH_CONFIG2, M.SCH_CONFIG3, M.SCH_CONFIG4,
             TO_CHAR(M.START_DTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'),
             TO_CHAR(M.END_DTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'),
             M.SCHED_SOURCE, M.OFFDAY_IND, A.TIMEZONE, A.SCH_CATEGORY From
             PS_SCH_MNG_SCH_TBL M, PS_SCH_ADHOC_DTL A Where M.EMPLID = :1 and
             M.EMPL_RCD = :2 and M.SCH_PRIM_ALT_IND = :3 and M.DUR between
             TO_DATE(:4,'YYYY-MM-DD') and TO_DATE(:5,'YYYY-MM-DD') and
             A.EMPLID = M.EMPLID and A.EMPL_RCD = M.EMPL_RCD and
             A.SCH_PRIM_ALT_IND = M.SCH_PRIM_ALT_IND and A.DUR = M.DUR and
             A.SEQ_NO = M.SEQ_NO and A.SEQNUM = 1 Order By M.DUR Asc,
             M.SCHED_SOURCE Desc, M.SEQ_NO Desc

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Optimizer statistics for index "SYSADM"."PS_SCH_MNG_SCH_TBL" are stale.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this index.
    execute dbms_stats.gather_index_stats(ownname => 'SYSADM', indname =>
            'PS_SCH_MNG_SCH_TBL', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE);

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the index in order to
    select a good execution plan.

2- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 94.67%)
  ------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_TASK1')
----------------------------------------------------------------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index SYSADM.IDX$$_21C0F0001 on
    SYSADM.PS_SCH_ADHOC_DTL("EMPLID","SEQNUM","EMPL_RCD","SCH_PRIM_ALT_IND","DU
    R");

  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index SYSADM.IDX$$_21C0F0002 on
    SYSADM.PS_SCH_MNG_SCH_TBL("EMPLID","DUR");

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 2070933151

----------------------------------------------------------------------------------------------------
------------------------
| Id  | Operation                             | Name               | Rows  | Bytes | Cost (%CPU)| Ti
me     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
------------------------
|   0 | SELECT STATEMENT                      |                    |     1 |   103 |   387   (1)| 00
:00:01 |       |       |
|*  1 |  FILTER                               |                    |       |       |            |
       |       |       |
|   2 |   SORT ORDER BY                       |                    |     1 |   103 |   387   (1)| 00
:00:01 |       |       |
|   3 |    NESTED LOOPS                       |                    |     1 |   103 |   386   (1)| 00
:00:01 |       |       |
|   4 |     PARTITION RANGE ITERATOR          |                    |    10 |   350 |   371   (1)| 00
:00:01 |   KEY |   KEY |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID| PS_SCH_ADHOC_DTL   |    10 |   350 |   371   (1)| 00
:00:01 |   KEY |   KEY |
|*  6 |       INDEX RANGE SCAN                | PS_SCH_ADHOC_DTL   |    10 |       |   369   (1)| 00
:00:01 |   KEY |   KEY |
|   7 |     PARTITION RANGE ITERATOR          |                    |     1 |    68 |     2   (0)| 00
:00:01 |   KEY |   KEY |
|   8 |      TABLE ACCESS BY LOCAL INDEX ROWID| PS_SCH_MNG_SCH_TBL |     1 |    68 |     2   (0)| 00
:00:01 |   KEY |   KEY |
|*  9 |       INDEX UNIQUE SCAN               | PS_SCH_MNG_SCH_TBL |     1 |       |     1   (0)| 00
:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------
------------------------

Predicate Information (identified by operation id):

DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_TASK1')
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   1 - filter(TO_DATE(:4,'YYYY-MM-DD')<=TO_DATE(:5,'YYYY-MM-DD'))
   6 - access("A"."EMPLID"=:1 AND "A"."EMPL_RCD"=TO_NUMBER(:2) AND "A"."SCH_PRIM_ALT_IND"=:3 AND
              "A"."DUR">=TO_DATE(:4,'YYYY-MM-DD') AND "A"."SEQNUM"=1 AND "A"."DUR"<=TO_DATE(:5,'YYYY
-MM-DD'))
       filter("A"."SEQNUM"=1)
   9 - access("M"."EMPLID"=:1 AND "M"."EMPL_RCD"=TO_NUMBER(:2) AND "M"."SCH_PRIM_ALT_IND"=:3 AND
              "A"."DUR"="M"."DUR" AND "A"."SEQ_NO"="M"."SEQ_NO")
       filter("M"."DUR">=TO_DATE(:4,'YYYY-MM-DD') AND "M"."DUR"<=TO_DATE(:5,'YYYY-MM-DD'))

2- Using New Indices
--------------------
Plan hash value: 1209469329

----------------------------------------------------------------------------------------------------
------------------------
| Id  | Operation                             | Name               | Rows  | Bytes | Cost (%CPU)| Ti
me     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
------------------------
|   0 | SELECT STATEMENT                      |                    |     1 |   103 |    21  (10)| 00
:00:01 |       |       |
|*  1 |  FILTER                               |                    |       |       |            |
       |       |       |
|   2 |   SORT ORDER BY                       |                    |     1 |   103 |    21  (10)| 00
:00:01 |       |       |
|*  3 |    HASH JOIN                          |                    |     1 |   103 |    20   (5)| 00
:00:01 |       |       |
|   4 |     TABLE ACCESS BY GLOBAL INDEX ROWID| PS_SCH_ADHOC_DTL   |    10 |   350 |    10   (0)| 00
:00:01 | ROWID | ROWID |
|*  5 |      INDEX RANGE SCAN                 | IDX$$_21C0F0001    |    10 |       |     4   (0)| 00
:00:01 |       |       |
|*  6 |     TABLE ACCESS BY GLOBAL INDEX ROWID| PS_SCH_MNG_SCH_TBL |    13 |   884 |     9   (0)| 00
:00:01 | ROWID | ROWID |
|*  7 |      INDEX RANGE SCAN                 | IDX$$_21C0F0002    |    13 |       |     3   (0)| 00
:00:01 |       |       |
----------------------------------------------------------------------------------------------------
------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(:4,'YYYY-MM-DD')<=TO_DATE(:5,'YYYY-MM-DD'))
   3 - access("A"."EMPLID"="M"."EMPLID" AND "A"."EMPL_RCD"="M"."EMPL_RCD" AND
              "A"."SCH_PRIM_ALT_IND"="M"."SCH_PRIM_ALT_IND" AND "A"."DUR"="M"."DUR" AND
              SYS_OP_DESCEND("A"."DUR")=SYS_OP_DESCEND("M"."DUR") AND "A"."SEQ_NO"="M"."SEQ_NO")
   5 - access("A"."EMPLID"=:1 AND "A"."SEQNUM"=1 AND "A"."EMPL_RCD"=TO_NUMBER(:2) AND "A"."SCH_PRIM_
ALT_IND"=:3 AND
              "A"."DUR">=TO_DATE(:4,'YYYY-MM-DD') AND "A"."DUR"<=TO_DATE(:5,'YYYY-MM-DD'))
   6 - filter("M"."SCH_PRIM_ALT_IND"=:3 AND "M"."EMPL_RCD"=TO_NUMBER(:2))
   7 - access("M"."EMPLID"=:1 AND "M"."DUR">=TO_DATE(:4,'YYYY-MM-DD') AND "M"."DUR"<=TO_DATE(:5,'YYY
Y-MM-DD'))

-------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

The routine above works well but I am happy to consider any changes or improvements.

PS If anybody knows how to use a code tag and not have those horrible green wraparound marks please let me know

PSU for 11.1.0.7.1

One of the most read entries on this site is about the PSU release for 10.2.0.4 http://jhdba.wordpress.com/2009/07/28/applying-the-10-2-0-4-1-patch-set-update-psu/

On 20th October, 2009 the first PSU for 11.1.0.7 was released. This also incorporates the Oct 2009 CPU as well as a Dataguard Broker patchset bundle. Bug 7628357 – 11.1.0.7 Data Guard Recommended Patch Bundle #1.

 Oracle have maintained consistency with the 10.2.0.4 PSU by retaining the misinformation in the patch instructions. I did originally think that there was no need to update opatch as there was in 10g but I was wrong. Opatch should be upgraded but the PSU  does apply even if it is not upgraded.

Ensure that the latest Opatch version is installed

 

opatch version

Invoking OPatch 11.1.0.6.2

 

OPatch Version: 11.1.0.6.2

 

If it is not 11.1.0.6.7 or later then upgrade

 

cd $ORACLE_HOME

cp /shared/oracle/rdbms_patches/PSU_patches/11.1.0.7/p6880880_111000_HPUX-IA64.zip .

unzip p6880880_111000_HPUX-IA64.zip

rm p6880880_111000_HPUX-IA64.zip

 

opatch version

Invoking OPatch 11.1.0.6.8

 

OPatch Version: 11.1.0.6.8

 

 

Basic steps for a new build server are as follows

1) Install 11.1.0.6 and upgrade to 11.1.0.7

2) ensure opatch is in the path, an Oracle Home is set and the patcheset (8833297) is unzipped

3) Run the pre-check

Following the README notes I would use this command

$cd /shared/oracle/rdbms_patches/PSU_patches/11.1.0.7

$opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./8833297

which then fails

Oracle Interim Patch Installer version 11.1.0.6.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /app/oracle/product/11.1.0/db_1
Central Inventory : /app/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 11.1.0.6.2
OUI version       : 11.1.0.7.0
OUI location      : /app/oracle/product/11.1.0/db_1/oui
Log file location : /app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2009-10-28_14-16-33PM.log

Invoking prereq "checkconflictagainstohwithdetail"
<span style="color: #ff0000;">The location "./8833297/README.html" is not a directory or a valid patch zip file.
Prereq "checkConflictAgainstOHWithDetail" not executed
PrereqSession failed: Invalid patch location.</span>

<span style="color: #ff0000;">OPatch failed with error code 73

Then use the corrected syntax

opatch prereq CheckConflictAgainstOHWithDetail -ph ./8833297

Invoking OPatch 11.1.0.6.2

Oracle Interim Patch Installer version 11.1.0.6.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /app/oracle/product/11.1.0/db_1
Central Inventory : /app/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 11.1.0.6.2
OUI version       : 11.1.0.7.0
OUI location      : /app/oracle/product/11.1.0/db_1/oui
Log file location : /app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2009-10-28_14-16-57PM.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.  

This is very quick, less than one minute

4) Now apply the PSU

$cd 8833297
$opatch apply

The patchset took 28 minutes to apply

Afterwards an opatch lsinventory command shows

Oracle Home       : /app/oracle/product/11.1.0/db_1
Central Inventory : /app/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 11.1.0.6.2
OUI version       : 11.1.0.7.0
OUI location      : /app/oracle/product/11.1.0/db_1/oui
Log file location : /app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2009-10-28_15-55-41PM.log

Lsinventory Output file location : /app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2009-10-28_15-55-41PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 11g                                                  11.1.0.6.0
Oracle Database 11g Patch Set 1                                      11.1.0.7.0
There are 2 products installed in this Oracle Home.
Interim patches (1) :

Patch  8833297      : applied on Wed Oct 28 14:54:39 GMT 2009
   Created on 7 Oct 2009, 23:52:06 hrs PST8PDT
   Bugs fixed:
     6870937, 7627743, 7652888, 7299153, 8242410, 6059178, 8563946, 6955744
     7497788, 6840740, 8244217, 8702276, 6981690, 8450529, 7432556, 7719143
     7523787, 8251486, 8367827, 7613481, 8341623, 7515145, 7348847, 8416414
     8250643, 8284633, 8230457, 8563948, 6900214, 7044551, 7318049, 8940197
     7013124, 7432514, 7393258, 7553884, 7639121, 8563944, 8860821, 7606362
     7426959, 7330434, 7708340, 7352414, 6452375, 7356443, 7341598, 8213302
     7196532, 7446163, 8409848, 8236851, 8342506, 7593835, 7340448, 7309458
     8290478, 8391256, 7462112, 7013817, 8499600, 7411865, 7331867, 7527650
     6977167, 8855565, 6501490, 6598432, 7524944, 7706138, 6941717, 8402555
     7494333, 7586451, 8402551, 7499353, 8408887, 7496908, 7511040, 7719148
     7311601, 7497640, 7373196, 7424804, 7452373, 7597354, 6882739, 7366290
     8543737, 6851669, 7318276, 8284438, 8324760, 7420394, 7834195, 7350127
     7475055, 8563942, 7122161, 8361398, 7451927, 7705669, 7676737, 8301559
     8224083, 8658581, 8211920, 8462173, 7206858, 8563945, 8855553, 8402637
     8257122, 8199266, 7454752, 7516536, 7345904, 8352304, 7416901, 7426336
     8352309, 6812439, 7219752, 8534338, 8542307, 8413059, 7572069, 7436280
     7432601, 7311909, 7506785, 7460818, 7276960, 8855559, 7013835, 7378322
     8402548, 7189645, 8563947, 8306933, 7477246, 7263842, 7480809, 8855575
     8433270, 7556778, 8836375, 7330611, 8339352, 7225720, 8563943, 7036453
     7628387, 8419383, 6970731, 7719668, 7203349, 8402562, 7680907, 7438445
     8855577, 8243648, 7630416, 6851110, 6980597, 6618461, 7357609, 8855570
     8369094, 8318050, 8306934, 7434194, 8833297, 7486595, 7716219, 8362693
     6599920, 7628866, 7183523, 7412296, 7135702, 7720494, 7436152, 7175513
     7602341, 6679303, 8339404, 7393804, 8856696, 7650993, 6980601, 7830065
     7462709, 8563941, 8226397, 7515779

Note that if you already have databases built then you need to run a catbundle script and check for invalid objects. Check the documentation for full details that but a quick summary is to run
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL CONNECT / AS SYSDBA
SQL STARTUP
SQL @catbundle.sql psu apply
SQL QUIT

Oracle Performance Management with Gaja Vaidyanatha

I attended this 2 day workshop ,held in London, last week.

I already owned both of Gaja’s book, Tales of the Oak Table and 101 Performance Tuning so I was reasonably familiar with  his background.

The course started of badly as Gaja was late arriving due to travel delays and so we did not kick off until 11:30 which was either 1.5 or 2 hours depending on whom you asked. He hit the ground running with a quick introduction and then a round the room ‘what do you want to get from this’ session.

The sessions covered everything set out in the advertising blurb, namely architecture, tracing, use of the OWI interface and a planned methodology for dealing with problems. One key point , apart from the availability of RAT in 11g was that the newer release did not add much to what Oracle 10g already provided with regard to performance tuning. 

The key methodology thoughts were to clearly define a goal, run a simple diagnostic routine and then repeat. With regard to SQL optimisation the 2 things to remeber were

1. Reducing logical I/O is primary in any performance tuning engagement
2. Hand in hand reducing elapsed time is equally important

The repeatable process is to find ‘interesting’ PIDS from glance or top, map them to a SPID (SID and SERIAL#) to find a session then run a 10046 trace and find the most expensive step (often the  most indented call).

Fix the code (one change at a time) and repeat.  If a 10046 trace does not help then get down to the OS level and look at how resources are being used.

That all sounds pretty simple and indeed it is.  I could write more about the methodology but I am concious that it is someone’s livelyhood and not to say too much.

Without sounding too knowledgeable or clever I was aware of most of the content that was discussed in the 2 days. Where I gained real benefits was in the storage aspects that Gaja covered very well and offered real insights into CPU, hyper threading, HBA usage and loading and suchlike. Various RAID levels were well covered and we had  a very good discussion on the value of placing redo logs on RAID 1+0 and suchlike.

Most of the second day was taken up with running through some real world examples, some where the problem was obvious, others where I had no clue at all. Several people had brought in 10046 traces and AWR snapshots and we all gained real insight as Gaja looked at them for the first time and showed us the key points worthy of investigation.

All in all a very worthwhile 2 days. The target audience needs to have a reasonable Oracle knowledge beforehand as a couple of students were saying that they had found it very difficult to follow. Oh and be prepared for long days as on day one Gaja was still talking strongly upto 18:00 despite being deadbeat from travelling and the second day went on up to 17:00.

Executive summary. Highly recommended