Monday May 12, 2014

Speaking Tips

speaker Tips on Performing a birthday speech / presentation.

As a consultant I am frequently standing up and performing presentations to groups, meetings, customers or workshops both internally and externally.

However this weekend I had to do something different, I had to perform a 40th birthday speech
for my wife and so thought I would record some tips and advice for those in a similar position.

Please note that this is not 100% the complete guide but just some useful points I have found over the years of doing similar and I hope helpful. 

Speech construction

1.  The speech needs to be short.  Whenever speaking make sure the length of speech is appropriate.  For a party speech it should never exceed 2-3 minutes.

2.  The speech should always be broken into sections starting with an introduction  (thanks, purpose, set the scene).

3.  The speech should then move to main content  (if easier think of 2-3 short stories or points and build around this).

4.  Finally a catchy conclusion to sum up.  (use lines from a song or you might use a joke or big point to finish).

Speech Performance

1.  When performing the speech although it is useful to have initially write out the whole thing in full, you probably will never actually read it word for word / line for line.  The reason for this if reading it then you lose the effect of your voice and adopt a reading voice, you also will not be able to have eye contact and engage with your audience.

So after reading it through a few times just to practice, write out only the main point lines and have this as your cues to talk around.  You will find the summary lines keep you moving along and allow you to remember more of the initial detail than you think from what you practiced earlier.  Another point is if you get a little stuck or finish a point you can easily just pick up the next one and follow the cue.

2.  Remember that any cue cards you use need to suit the environment you are performing in.  For example if in a darker low light room then you need a larger font to be able to see!

3.  Remember you may have a microphone in one hand so need to be able to hold any materials like cue cards in the other.  Think about implementation! How can you turn pages with one hand?

4.  Always speak to the people at the rear of the room to be loud and heard.  This also helps with focus and eye contact issues.  Everyone will think you are looking at them and your voice will be the appropriate volume as you are focused to the people furthest away.

5.  Always try to speak clearly and take your time.  Nerves will always make us rush, so remember to breathe and slow yourself down to come across relaxed and control any mistakes.  If a mistake occurs just enjoy the moment and either pick up or move along.  We are all human and mistakes happen, it is how you deal with them that makes us all different.

6.  Always remember we all get nerves and these are actually a good thing not a bad thing.  We need the nerves to help us have adrenalin to actually perform, just remember to breathe and control the nerves so that they don't take over.

7.  With practice be aware of any bad habits such as rocking on the spot, waving hands or the classic "ummm's".  It can help to have someone else listen to you practice and look up the tricks on any obvious issues to stop these occurring.

If you ever do a formal presentation course as I have had to do previously you will find that they will make you present to one or more video cameras and then review with the instructor.  Nothing works better than by actually watching and listening to yourself as it means you see all those irritating habits and can then work on them. 

There are so many solutions to each issue or habit, for example people that rock or walk about or wave hands too much find it easier to hold onto something to prevent those issues.

8.  Finally and most importantly enjoy the attention and time, smile and relax, pretend it is a conversation with a friend and people will engage with you.

If you have to do something similar and are struggling, please let me know and I would be happy to assist you.  Although even now nerves occur for me in these situations due to the number of presentations and stand up roles I have had to perform it is like everything and practice brings confidence as well as ability.

Friday Mar 14, 2014

Oracle Database Consolidation

ConsolidationIn the industry today engineers are trying to implement consolidation that minimizes idle resources and to lower costs.  This paper discusses some important items to consider  when implementing a consolidated or Exadata engineered Oracle database platform.  Some of the items may seem obvious or common sense yet on frequent assignment at customer sites are not considered and have become major issues to solve.  This paper is not intended to provide all consolidation issues but give highlight to some and more importantly to provoke a deeper consideration and conversation when designing a consolidated or engineered platform.

Download The Full PDF here: Database_Consolidation.pdf

Tuesday Feb 11, 2014

12c Pre-Built Developer VMs (for Oracle VM VirtualBox)

12c Pre-Built Developer VMs

(for Oracle VM VirtualBox)

The Oracle Virtual box sample downloads have now been updated to Oracle 12c.  For those after  a quick 12c sandpit please follow the link below.  Other virtual machines are also available including a base admin image of Oracle Linux 6.

Download Virtual Machines here: Oracle Virtual Machines

Wednesday Nov 20, 2013

OEM12c Password Change for SYSMAN and Weblogic Administration Accounts

password_reset.jpgHaving recently created an OEM12c virtual box install for a proof of concept (POC) at a customer site, another opportunity arose to reuse the same virtual machine. Alas the SYSMAN and WEBLOGIC administration passwords were unknown and required to be reset before reuse. Initially it was considered that this would be an easy task and it referenced some Oracle metalink published notes to complete the operation. However these notes and other published material were found to be missing some of the more critical steps, they were found to be lacking also in clarity and detail leading to a complex and error prone process that had to be retried many times. The purpose of this document is to give a clear step by step process to an administrator in a similar position that covers all the steps and not just some of the steps in detail with examples to follow.

Download The Full PDF here: OEM12c_Reset_Admin_Passwords.pdf

Friday Oct 18, 2013

Shrinking a Linux OEL 6 virtual Box image (vdi) hosted on Windows 7

Recently for a customer demonstration there was a requirement to build a virtual box image with Oracle Enterprise Manager Cloud Control 12c. This meant installing OEL Linux 6 as well as creating an 11gr2 database and Oracle Enterprise Manager Cloud Control 12c on a single virtual box. Storage was sized at 300Gb using dynamically allocated storage for the virtual box and about 10Gb was used for Linux and the initial build.

After copying over all the binaries and performing all the installations the virtual box became in the region of 80Gb used size on the host operating system, however internally it only really needed around 20Gb. This meant 60Gb had been used when copying over all the binaries and although now free was not returned to the host operating system due to the growth of the virtual box storage '.vdi' file.  Once the ‘vdi’ storage had grown it is not shrunk automatically afterwards.

Space is always tight on the laptop so it was desirable to shrink the virtual box back to a minimal size and here is the process that was followed.

Install 'zerofree' Linux package into the OEL6 virtual box

The RPM was downloaded and installed from a site similar to below;

A simple internet search for ’zerofree Linux rpm’ was easy to perform and find the required rpm.

Execute 'zerofree' package on the desired Linux file system

To execute this package the desired file system needs to be mounted read only. The following steps outline this process.

As root: # umount /u01

As root:# mount –o ro –t ext4 /u01

NOTE: The –o is options and the –t is the file system type found in the /etc/fstab.

Next run zerofree against the required storage, this is located by a simple ‘df –h’ command to see the device associated with the mount.

As root:# zerofree –v /dev/sda11


NOTE: This takes a while to run but the ‘-v’ option gives feedback on the process.

What does Zerofree do?

Zerofree’s purpose is to go through the file system and zero out any unused sectors on the volume so that the later stages can shrink the virtual box storage obtaining the free space back.

When zerofree has completed the virtual box can be shutdown as the last stage is performed on the physical host where the virtual box vdi files are located.

Compact the virtual box ‘.vdi’ files

The final stage is to get virtual box to shrink back the storage that has been correctly flagged as free space after executing zerofree.

On the physical host in this case a windows 7 laptop a DOS window was opened.

At the prompt the first step is to put the virtual box binaries onto the PATH.

C:\ >echo %PATH%


The above shows the current value of the PATH environment variable.

C:\ >set PATH=%PATH%;c:\program files\Oracle\Virtual Box;


The above adds onto the existing path the virtual box binary location.

C:\>cd c:\Users\xxxx\OEL6.1


The above changes directory to where the VDI files are located for the required virtual box machine.

C:\Users\xxxxx\OEL6.1>VBoxManage.exe modifyhd zzzzzz.vdi compact


NOTE: The zzzzzz.vdi is the name of the required vdi file to shrink.

Finally the above command is executed to perform the compact operation on the ‘.vdi’ file(s). This also takes a long time to complete but shrinks the VDI file back to a minimum size. In the case of the demonstration virtual box OEM12c this reduced the virtual box to 20Gb from 80Gb which was a great outcome to achieve.

Friday Aug 16, 2013

IT Operations Architecture

Dev OPS triangleRecently I have been involved with working as a Technology Operations Architect for a banking customer. This work involved assisting with Oracle product knowledge and reviewing the IT operational requirements being raised by the customer. After reviewing the customers requirements the next stage of the process was to perform a product fit-gap analysis (product does it out the box, 'fit', product needs work around, 'partial gap' or product has no capability today, 'full gap'. Finally each requirement was worked through to agree high level solutions and a way forward for IT operations to function as desired.

Having been involved in the 'Run the business' side of IT for many years and seen customers at various levels of technology maturity as well as underlying technology and standards change significantly, it is really interesting work to be involved in.

The challenge is not only trying to display to the customers what the products can do but also applying the experience of actual real use.  To help avoid the difficulties of implementing and more importantly ongoing management. Obviously it is always better to learn off other people’s mistakes and positive experiences rather than take the same journey alone. Working for a vendor it allows us to bring this to the table which is our defrentiator. The use of ITIL standards over recent years where implemented correctly has also helped operations architecture significantly improve and I personally am a keen follower / promoter of this.

The goal in the requirements gathering is to capture what is really required to provide IT operations the ability to run the infrastructure / applications avoiding outage, increased costs, etc. What is the pain that in future the customer wants to avoid? I always believe that due to the nature of the different technology operations have to manage with versions, platforms etc it is really about KISS, (keep it simple stupid).

In the old days scripts would be written for everything as this guaranteed the stability, control and ownership. However there is a cost involved with maintaining that approach and moving it forward as technology required to support changes. The inherent danger is that this too becomes an unsupportable mess, creating a job for life for those who wrote the scripts.

A lot is pushed towards products to solve these issues and make life better, however these applications need to be treated as applications in their own right and require careful management. There is no silver bullet or product that just out the box switch it on and there is the answer. Unfortunately the configuration requirements of these products is not included in the total cost of ownership and often left aside.  This leads to a poor product implementation and most probably an operations failure or rejection of the product.

To me the real answer is about those responsible for support standing up with governance, standards and policy as a first step. After all when it goes wrong it is those support people that are held accountable. It is about working out the right solution required to allow pro-active administration and the cheaper / best way of identifying an issue before the business does.   Even so far as having the means to fix or prevent the bad things happening in the first place.  Sounds very simple, firstly work out the 'what' followed by looking at the how later in solution development.

The hardest aspect for the customer is in defining operational requirements, 'the what'.  Below is a great link from the US government department of homeland security.  It's where a customer or group has given serious thought about when it comes to dealing with vendors and how to shortcut the process to give good requirement definitions.


Another trend worth researching which has quickly taken off is the development operations triangle and matrix. Above is a great pictorial representation of how the ideal solution is about IT operations getting in a sweet spot between what a solution is against the cost, effort and value associated to obtaining it. This is where as a solution it may be that a script is the way to go or a product or combination thereof.

Cost Benefit Matric

In summary it is more important to define what needs to be there as a priority in operations and then to work through how to provide expected solutions.  From an experienced veteran of support even though the 'How' may have changed over time due to technology the basic needs of the 'what' from an operations perspective have not.

Friday Jun 14, 2013

Oracle Service Bus (OSB) Selective Project Deployments

OSB BoxThe Oracle Service Bus provides a number of deployment options using the following tools:

  • Eclipse IDE
  • OSB Web console
  • Scripting via Weblogic Language Scripting Tool (WLST)

The Eclipse IDE allows the users to build and deploy OSB projects directly from source or to directly deploy a SBConfig jar file to the target Instance

The OSB Web Console allows the import of the SBConfig Jar file into the target environment or the code can be directly updated within the target Instance.

However once you step out of the development cycle the preferred approach is using the WLST/Java scripting, to ensure a consistent deployment across different environments (ie: system test, Integration test, ect). For this the deployment team are generally provided with a pre-build SBConfig Jar file, containing all the OSB projects to be deployed.

Within my last project we had a situation where the offshore development environments did not reflect the final state at the customer site. The development environment contained a single OSB instance that supported all the services, however the onsite model split these across three separate OSB Instances, thus we needed to selectively deploy the OSB projects across the different instances from a single SBConfig Jar File.

Oracle provides an API for the ALSBImportOperation, this allows us to set an indicator as to how the import operation should be handled for a nominated resource. Using this API we were able to navigate through the default deployment plan for the SBConfig Jar file and flag the projects that we did not want within the selected OSB domain with a skip option, effectively removing them from the deployment plan.

Please Note the following is not a complete scripts, this sub section has been provided for demonstration purposes only.

A command line parameter is passed into the script that provides a list of OSB Projects to be deployed.
(ie. SOURCE_PROJECT_NAME=<OsbProject>,<OsbProject>,<OsbProject>,<OsbProject> )

sourceProjectNameList = SOURCE_PROJECT_NAME.split(',')
if not errorInProcessing:
   sbJarFiles = glob.glob(global_variables.CONFIGURATION_DIRECTORY + "/*.jar" )
   for configurationFile in sbJarFiles:
       # Process ALSB Configuration File
       # Read ALSB Configuration File into Buffer
       f = open(configurationFile,"rb" )
       print "Reading ALSB configuration file: " + configurationFile + "..."
       buffer =

       # Upload ALSB Configuration File
       print "Uploading ALSB configuration file..."

       # Then get the default import plan and modify the plan if required
       print "Importing ALSB configuration file..."
       jarInfo = alsbSession.getImportJarInfo()

       #Get the Import Plan from the SB Config Jar
       importPlan = jarInfo.getDefaultImportPlan()

       #Get the Operations from the ImportPlan
       operations = importPlan.getOperations()
       operationsKeySet = operations.keySet()

       for operationKey in operationsKeySet:
           alsbOperation = operations.get(operationKey)

           #Get the Project Name from the Operation Reference
           operationProjectName = alsbOperation.getRef().getProjectName();

           # If the Operation Project Name is not in the Source Project Name List
           # Mark the operation to be skiped.
           if not (operationProjectName in sourceProjectNameList):
              System.out.println('Skiping operation for project ' + operationProjectName )

       result = alsbSession.importUploaded(importPlan)
       if result.getFailed().size() > 0:
          errorInProcessing = true
          SESSION_DESCRIPTION = "Automated SB Deployment: " +
   buffer = None

Tuesday May 28, 2013

Oracle SQL Profile Internals

Black Box

It is very easy to pick a piece of poor performing code either by SQL text or SQL ID and then execute the Oracle SQL tuning advisor against that code. When a SQL profile is recommended though, how many people know what has actually been suggested by the profile internally?

This update covers a little known database view DBMSHSXP_SQL_PROFILE_ATTR which when supplied with the SQL profile name displays the internal hints that the profile is suggesting in XML format. This provides the missing piece to understanding what the SQL profile.

As always, here is an example;

1.  Create SQL tuning task on the statement. This can be done by SQL_ID or by SQL text.


                  l_sql_tune_task_id varchar2(100);
                  l_sql_tune_task_id := dbms_sqltune.create_tuning_task(
                                  sql_id=> '9yycxkm1kb8jn',
                                  description=>'Tuning Example by SQL ID');


l_sql_tune_task_id      varchar2(100);
my_sqltext              CLOB;
     my_sqltext := ‘select count(*) from scott.emp’;

     l_sql_tune_task_id := dbms_sqltune.create_tuning_task(
                          sql_text=> my_sqltext,
                          description=>'Tuning Example by SQL ID');

2.  Now Execute the SQL tuning task created in Step 1.

SQL>exec dbms_sqltune.execute_tuning_task(task_name=>’SQLID’);


            SQL>exec dbms_sqltune.execute_tuning_task(task_name=>’SQLTEXT’;

3.  Now run the report of the SQL tuning task recommendations. -

SQL>spool results.out

                       set long 999999999
                       set lines 80
                       set pages 0

SQL> select dbms_sqltune.report_tuning_task(‘SQLTEXT’) as recommended from dual;

See below some sample output recommending a SQL profile for the statement.

3- SQL Profile Finding (see explain plans section below)
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.05%)
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'SQLTEXT', task_owner => 'SYS', replace => TRUE);

To actually accept the SQL profile

 SQL>execute dbms_sqltune.accept_sql_profile(task_name => 'SQLTEXT',task_owner => 'SYS', replace => TRUE);

check that profile has been saved in the data dictionary

WHERE SQL_TEXT LIKE 'select count(*) from Scott.emp%';

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

SYS_SQLPROF_014c9e57e27g0006 select count(*) from scott.emp

To drop the sql tuning session if not applicable, or if the profile is implemented and it is wanted to remove, then  dbms_sqltune.drop_sql_profile can be executed also.

SQL> execute dbms_sqltune.drop_tuning_task(task_name=>’SQLTEXT’);

 4.  Finally to display the hints suggested by the profile for further analysis

 NOTE: Under Oracle 10g SQL profiles were stored in : SQLPROF$ and SQLPROF$ATTR.  However in 11g SQL profiles are stored in SQLOBJ$ and SQLOBJ$DATA and DBMSHSXP_SQL_PROFILE_ATTR. It is also necessary to convert the format as they are stored in XML format.



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

 SYS_SQLPROF_014c9e57e27g0006 <outline_data><hint><![CDATA[PARALLEL]]></hint></outline_data>

So it can be seen by analysis of the above that the Parallel hint has been suggested by the profile. However if other recommendations were made these in turn can be analyzed for relevance and understanding for further tuning.

  Some examples of other types of hint are:


  The scale_rows figure is multiplied by the number of rows in the table to get an estimate on the number of rows to be retrieved by the query and supply to the optimizer.

 COLUMN_STATS("SCOTT"."EMP", "ID", scale, length=3)]]>

  The above is storing the estimate on the average column length of a field.

 TABLE_STATS("SCOTT"."EMP", scale, blocks=10123 rows=988.542)]]>

  The above is displaying the number of rows and blocks to the optimizer.


  The above tells the optimizer to ignore any hints given in the statement itself.


The above tells the optimizer to execute the statement with features enabled in version 9.0.0

Monday May 06, 2013

Database Partitioning A Source Table With Golden Gate Replication In Place

GoldenGateAt a client site there was a requirement for performance of a SIEBEL database to partition an existing source table that happened to be also replicated with golden gate. The requirement was only to partition the source table and NOT the replicated target table.  During this process an issue occurred with duplicate data being replicated after the database partitioning occurred.  This article covers the issue found in the process and a workaround to that issue using a simple test case to demonstrate.  It firstly recreates the issue then secondly explains the root cause and solution.

Please note that in this example of golden gate, 'DDL' replication is NOT used.  As stated in this case the partitioning is only at the source side only.  It is a simple extract of the table SCOTT.EMP containing 14 rows and replicating to a new schema / table in the same database called REP.EMP.  The primary key is defined on the EMP table at both sites based on the EMPNO field as is default in the SCOTT sample schema.

The document also does not cover set up for this replication as it focuses on what happens when the source table is changed into a partitioned table from a non partitioned table and replication is in place.  However it does cover some basic outline steps for setting up the test-case.

Please Note these scripts were written only for demonstration purposes. They are not optimized and they have almost no error checking, so be careful!

Download The Full PDF here: Partitioning A Source_database_Table With_Golden_Gate_Replication.pdf

Thursday Apr 25, 2013

'IS NULL' SQL Tuning

NULLFrequently at client sites some very slow queries contain the SQL involving the ‘IS NULL’ criteria in the where clause. It is commonly known Oracle does not store NULL values in indexes so even though an index may exist for the column it is NOT usable in this case. However there is a solution to this situation and FULL table scans can be avoided!

Example: SQL> Select name from emp where dept_id is null;

So in the above example even if the dept_id is indexed on the table emp, due to the ‘IS NULL’ criteria it will NOT be used.

Many developers believe that this type of statement cannot be tuned and that a FULL table scan is the only option. However here is a solution to this issue, simply create an appropriate index but add in an additional constant value to the end of the index so that now NULL values are actually stored!

Example: SQL> create index E1 on EMP (DEPT_ID, -1);

Running an explain plan and executing the SQL it can be seen that the new index will now be used as NULL values are now indexed.

Real life example: At a particular customer site there was a situation where a table had over 11 million rows. A SQL statement had a lot of table joins but only 2 constraints in the where clause as below.



From analyzing the table almost every row had the PRIV_FLG = ‘N’ so that was not going to help, however looking at the integration_id from the 11 million rows, only 51,167 were null values. The field 'integration_id' was already indexed however this would not be used due to only wanting NULL values. By indexing as in the above example the new index helped optimize the query significantly as the number of rows in the driving table had been significantly reduced without any full table scans!


About Me Image
Andy Baker, Senior Principal Consultant for Oracle Consulting Services (@Bakers_byte), shares his news, views and ideas about the Oracle Database with a focus on innovation and emerging technologies.


« July 2016