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%';

 

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

The ASM script of all ASM scripts !

The asm information script I use which gives me everything I think I need in one go.

If there are any queries that others find useful please comment on them and I will add them to the script.

 

Credit where credit is due. I think Alan Cooper wrote the original version, although it has been amended since then.

 

set wrap off

set lines 120

set pages 999

col “Group Name”   form a25

col “Disk Name”    form a30

col “State”  form a15

col “Type”   form a7

col “Free GB”   form 9,999

 

prompt

prompt ASM Disk Groups

prompt ===============

select group_number  “Group”

,      name          “Group Name”

,      state         “State”

,      type          “Type”

,      total_mb/1024 “Total GB”

,      free_mb/1024  “Free GB”

from   v$asm_diskgroup

/

 

prompt

prompt ASM Disks

prompt =========

 

col “Group”          form 999

col “Disk”           form 999

col “Header”         form a9

col “Mode”           form a8

col “Redundancy”     form a10

col “Failure Group”  form a10

col “Path”           form a19

 

select group_number  “Group”

,      disk_number   “Disk”

,      header_status “Header”

,      mode_status   “Mode”

,      state         “State”

,      redundancy    “Redundancy”

,      total_mb      “Total MB”

,      free_mb       “Free MB”

,      name          “Disk Name”

,      failgroup     “Failure Group”

,      path          “Path”

from   v$asm_disk

order by group_number

,        disk_number

/

 

prompt

prompt Instances currently accessing these diskgroups

prompt ==============================================

col “Instance” form a8

select c.group_number  “Group”

,      g.name          “Group Name”

,      c.instance_name “Instance”

from   v$asm_client c

,      v$asm_diskgroup g

where  g.group_number=c.group_number

/

 

prompt

prompt Current ASM disk operations

prompt ===========================

select *

from   v$asm_operation

/

 

prompt

prompt free ASM disks and their paths

prompt ===========================

select header_status , mode_status, path from V$asm_disk

where header_status in (‘FORMER’,'CANDIDATE’)

/

 

clear columns

Script to tidy archivelogs from ASM and RMAN

We have a pre-production RAC cluster that is kept in archivelog mode to allow true performance monitoring (and to be used for Streams and DataGuard testing). However we do not need the archivelogs for recovery purposes and as we perform high-volume testing the +FRA diskgroup (on solid-state disk) gets full very quickly.

I wrote a script that can be run to quickly free up space. It connects to the ASM instance and removes the logfiles.

It sets the SID and ORACLE_HOME to that of the RAC instance then runs RMAN to perform a crosscheck and delete of the archivelogs.

Not particularly complex but efficient.

export ORACLE_SID=+ASM1
export ORACLE_HOME=/u00/app/asm/product/11.1.0/db_1

asmcmd -p << EOF

ls FRA/RACCLUSTER/ARCHIVELOG/*2008*/*
rm -rf FRA/RACCLUSTER/ARCHIVELOG/*
ls FRA/RACCLUSTER/ARCHIVELOG/*
EOF

export ORACLE_SID=RACSID
export ORACLE_HOME=/u00/app/oracle/product/11.1.0/db_2

rman target / catalog username/password@catdb << EOF1

CHANGE ARCHIVELOG ALL VALIDATE;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
EOF1

RMAN backup script – example – logging output

To answer a question about writing the output from RMAN commands to a logfile I posted a copy of a shell script I use to the Oracle-L newsgroup. It is a script I use for testing which does explain the multiple RMAN commands, much of which are commented out.

I

#!/bin/ksh

export ORACLE_HOME=/u00/app/oracle/product/11.1.0/db_2

export ORACLE_SID=TEST1

export PATH=$PATH:$ORACLE_HOME/bin

export NLS_DATE_FORMAT=’DD-MON-YY HH24:MI:SS’

export DATE=$(date +%Y-%m-%d)

rman target backup_admin/xxxxxxx catalog rman/xxxxxxx@server msglog /export/backups/rman/11g/logs/rman_full_backup_db_online_TEST1_${DATE}.log <<EOF

#backup AS COMPRESSED BACKUPSET database ;

#backup AS COMPRESSED BACKUPSET archivelog all delete input ;

backup AS COMPRESSED BACKUPSET database plus archivelog delete input;

#backup AS COMPRESSED BACKUPSET database;

#backup database;

#backup archivelog all delete input;

#BACKUP INCREMENTAL LEVEL 0 TAG = WEEKLY DATABASE;

#delete noprompt force obsolete;

#Change archivelog all validate

exit;

EOF

exit

 

From the Oracle documentation I also found the following notes

Using RMAN with Command Files
A command file is a text file which contains RMAN commands as you would enter them at the command line. You can run the a command file by specifying its name on the command line. The contents of the command file will be interpreted as if entered at the command line. If the LOG command line argument is specified, RMAN directs output to the named log file. Command files are one way to automate scheduled backups through an operating system job control facility.

In this example, a sample RMAN script is placed into a command file called commandfile.rcv. You can run this file from the operating system command line and write the output into the log file outfile.txt as follows:

% rman TARGET / CATALOG rman/cat@catdb CMDFILE commandfile.rcv LOG outfile.txt
Directing RMAN Output to a Log File
When you run RMAN in command line mode, it sends the output to the terminal. If you specify the LOG option, then RMAN writes the output to a specified log file instead.

Output for currently executing RMAN jobs is also stored in the V$RMAN_OUTPUT view, which reads only from memory (that is, the information is not stored in the control file). The V$RMAN_STATUS view contains metadata about jobs in progress as well as completed jobs. The metadata for completed jobs is stored in the control file.