Lambda Expressions

Mark Reinhold, Oracle’s Chief Architect, gives a useful talk on Lambda Expressions at The JavaOne 2013 Technical Keynote.


Click here for the full article.


Keywords: java 1.8 lambda expressions

Overview of the .NET Framework

The .NET Framework is a technology that supports building and running the next generation of applications and XML Web services. This article also gives an explanation of Common Language Runtime (CLR) and .NET Framework class library.

 

Click here to read the full article.

Gather Statistics for the Cost Based Optimizer

The following article describes gathering statistics for the CBO (Cost Based Optimizer).

Goal

Provide pointers to the various articles outlining the recommended methods for gathering statistics for the Cost Based Optimizer for use by Database Administrators

See also

How To: Gather Statistics for the Cost Based Optimizer (Doc ID 1226841.1)

Keywords: oracle cbo stats statistics cost based optimizer

Explain Plans and DBMS_XPLAN

This post gives an overview of using DBMS_XPLAN and selecting a SQL_ID to use.


Run the following SQL query to obtain some information regarding the SQL_ID of interest..

=~=~=~=~=~=~=~=~=~=~=~= PuTTY log 2014.12.17 18:32:47 =~=~=~=~=~=~=~=~=~=~=~=
run
  1  SELECT DISTINCT sql_id,
  2     address,
  3     hash_value,
  4     child_number,
  5     plan_hash_value
  6  FROM   v$sql_plan
  7  WHERE  sql_id = '2x409g450hfyc'
  8* ORDER  BY child_number

SQL_ID      ADDRESS       HASH_VALUE CHILD_NUMBER      PLAN_HASH_VALUE
------------- ---------------- ---------- ------------ ---------------
2x409g450hfyc 00000002DCF6C808  168311756          0      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          1      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          2      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          3      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          4       240831378
2x409g450hfyc 00000002DCF6C808  168311756          5      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          6      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          7      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          8      1748903202
2x409g450hfyc 00000002DCF6C808  168311756          9      1748903202
2x409g450hfyc 00000002DCF6C808  168311756         10      1748903202

11 rows selected.

Or even:

SELECT sql_id,
       sql_text,
       hash_value,
       plan_hash_value,
       child_number
FROM   v$sql
WHERE  Regexp_like (sql_text, 'fred', 'i');

Now use the supplied Oracle package DBMS_XPLAN to obtain the explain plan.


SELECT * FROM table (
DBMS_XPLAN.DISPLAY_CURSOR('2x409g450hfyc',7,'ALL'));


where 2x409g450hfyc is the SQL_ID of interest and
7 is the child number.

By using different child numbers, you can select which plan hash value you wish to display. Child number 2, for example, displays the plan with a hash value of 1748903202 whilst child 4 gives you 240831378.


Keywords: oracle xplan explain plan sql_id

Tuning Inter-Instance Performance in RAC

Tuning Inter-Instance Performance in RAC and OPS (Doc ID 181489.1)


This note was written to help DBAs and Support Analysts understand Inter-Instance Performance and Tuning in RAC.

Real Application Clusters (RAC) uses the interconnect to transfer blocks and messages between instances. If inter-instance performance is bad, almost all database operations can be delayed. This note describes methods of identifying and resolving inter-instance performance issues.

See Oracle Support Doc ID 181489.1 for details.

RAC interconnect traffic

Summary


I was looking for RAC cluster interconnect throughput and so accessed to Oracle Database Reference 11g Release 2 (11.2) documentation and surprisingly, at least for me, I was not able to find a V$ view simply displaying it.


This is a nice article on the subject of RAC interconnect traffic.

Click here to read the article.

ADD_COLORED_SQL Procedure

From the Oracle supplied documentation:

This procedure adds a colored SQL ID. If an SQL ID is colored, it will be captured in every snapshot, independent of its level of activities (so that it does not have to be a TOP SQL). Capture occurs if the SQL is found in the cursor cache at snapshot time.

This can be very useful in the interests of fault finding or diagnosing problems.


SQL> execute dbms_workload_repository.add_colored_sql('6rj1tt5s6n497');

To confirm the SQL_ID has been colored, run the following SQL statement:
SQL> select * from sys.WRM$_COLORED_SQL

This will give you:

fred SQL> select * from sys.WRM$_COLORED_SQL
  2  /
      DBID SQL_ID        OWNER CREATE_TIME
---------- ------------- ----- --------------------
 413071501 6rj1tt5s6n497     1 11-Dec-2014 19:02:51
1 row selected.


and the following SQL will remove the color:
SQL> execute dbms_workload_repository.remove_colored_sql('6rj1tt5s6n497');

See also

AWR Colored SQL 11g

Oracle Support document
How to Determine the Execution Plan for a SQL Statement for a Range of AWR snapshots? (Doc ID 795204.1)

Oracle Support document
Information Center: SQL Query Performance Overview (Doc ID 1516494.2)



Keywords: oracle sql_id

11g Interactive Quick Reference

Oracle Database 11g: Interactive Quick Reference

Your Essential Guide to Oracle Database 11g Release 2

Oracle Database 11g Release 2 Enterprise Edition provides comprehensive features to easily manage the most demanding transaction processing, business intelligence, and content management applications.

Now you have easy access to information that you need to administer your Oracle Database with our downloadable Interactive Quick Reference. We have taken our traditional printed poster for Database Administrators, and created an electronic format packed with even more information.

Use this helpful reference as a cheat sheet for writing custom data dictionary scripts, locating views pertinent to a specific database component, and more.


Click here for the article.



Keywords: oracle 11g cheat sheet reference

Basics of SSIS and Creating Packages

SQL Server Integration Services (SSIS) – Part 1: Basics of SSIS and Creating Packages

In this article, we will see what a SQL Server Integration Services (SSIS) is; basics on what SSIS is used for, how to create an SSIS Package and how to debug the same.

Cick here for the article.

Looking at DBA_AUDIT_TRAIL

The view DBA_AUDIT_TRAIL displays all standard audit trail entries in an Oracle database. In my case, I was using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0.

The following SQL can be used to look at entries in this view.

SELECT os_username,
       username,
       terminal,
       To_char(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS tstamp,
       returncode,
       action_name
FROM   dba_audit_trail
order by tstamp asc;

Sample output:

OS_USERNAME  USERNAME  TERMINAL     TSTAMP   RETURNCODE ACTION_NAME

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

oracle     DBSNMP  ~       2014-10-17 18:32:10 1017 LOGON

root     RLBUSY  unknown      2014-10-17 18:32:15    0 LOGOFF

abc082     IMPREP  MACH-42    2014-10-17 18:32:35    0 LOGOFF

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGON

oracle     DBSNMP  ~       2014-10-17 18:32:57    0 LOGON

oracle     DBSNMP  ~       2014-10-17 18:32:57    0 LOGOFF

FRED       DUMMY  ~       2014-10-17 18:33:00 1017 LOGON

oracle     DBSNMP  ~       2014-10-17 18:33:29 1017 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:33:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:33:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:34:36    0 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:34:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  ~       2014-10-17 18:34:39 1017 LOGON

oracle     DBSNMP  ~       2014-10-17 18:34:53    0 LOGOFF

oracle     DBSNMP  ~       2014-10-17 18:34:53    0 LOGON

root     RLBUSY  unknown      2014-10-17 18:35:00    0 LOGON

The RETURNCODE column is the number associated with the ORA-????? error code that was returned to the client computer:

RETURNCODE=0 indicates success

RETURNCODE=1017 indicates bad password

RETURNCODE=28000 indicates account is locked out

For confirmation of the meaning of the returncode value, execute the following code:

SQL> execute dbms_output.put_line(sqlerrm(-1017)) ;

and you should see:

ORA-01017: invalid username/password; logon denied

or from the O/S prompt:

[oracle@host ~]$ oerr ora 01017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

See also

Oracle Support Note "Master Note For Oracle Database Auditing (Doc ID 1299033.1)"

Keywords: oracle audit trail failed login

PowerShell GetEnumerator

I was working with some PowerShell collections recently and forgot how to iterate over an object of the type System.Collections.ArrayList. So after a little digging around, I made some notes to remind myself.

The test code I used to create and populate the array was:


$myArray = New-Object -TypeName System.Collections.ArrayList(100);

$myArray.Add('hearts') | Out-Null;
$myArray.Add('clubs') | Out-Null;
$myArray.Add('diamonds') | Out-Null;
$myArray.Add('spades') | Out-Null;

Write-Host "Before trimming, capacity of the array: $($myArray.Capacity)";
$myArray.TrimToSize();
$myArray.Sort();
Write-Host "After trimming, capacity of the array: $($myArray.Capacity)";
Write-Host "Number of elements actually contained in the ArrayList: $($myArray.Count)";

I created myArray variable with a capacity of 100.  I didn't have to of course as I've only used the suit names found in a deck of playing cards, i.e., four names. This number is so trivial I could have created a new instance of the ArrayList class that is empty and has the default initial capacity. I'll mention later what the default initial capacity might be.

The return type of the ArrayList.Add Method is the type System.Int32, a 32-bit signed integer. Rather than see this value (which represents the ArrayList index at which the value has been added) I've suppressed the output by using the Out-Null Cmdlet. The Out-Null cmdlet sends output to NULL, in effect, deleting it.

Other ArrayList methods and properties have been used to give a little information regarding the array.

Property Capacity - in the first use of this property, the value returned is 100 which is the same value as the number of elements (capacity) used as an argument in the constructor.

In the second use of this property, we get a value of four which reflects the number of suits in our deck of cards. The value has changed because of the use of the TrimToSize method.

Method TrimToSize - sets the capacity to the actual number of elements in the ArrayList. In our example, four. Of course, we could have used a value four in our constructor knowing what we were going to add to the arraylist. But this is just to show what can be done. Especially if you might not know what the number of items you're going to load into the arraylist will be. It's easy to specify the correct value in small demonstrations like this.

Method Sort - sort the elements in the arraylist.

Property Count - confirms the number of elements that are actually in the ArrayList.

Enumerating over the collection - example 1

$myenum = $myArray.GetEnumerator();

while($myenum.MoveNext())

{

    Write-Host "Our playing card is now: $($myenum.Current)";

}

The property $myenum.Current represents the current element in the collection.

For details of the methods and properties of variable myenum, have a look at the IEnumerator interface.

Enumerating over the collection - example 2

$myArray.GetEnumerator() | ForEach-Object {

   Write-Host "Our playing card is now: $_";

}

Keywords: enum enumerator iterate

Aberdeen accommodation

I'm currently looking for accommodation in Aberdeen prior to starting work as an Oracle Database Administrator (DBA).


Please click here for a few more details.

RMAN things

I've put together some Oracle RMAN information in a Google document.


Click here to read the document.



Keywords: oracle rman ianm blog

Temporary Tablespaces

If you have reason to do some work on your temporary tablespaces, the following code may help.


-- Create the new temp tablespace.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/oradata/temp02.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Make this the default temp tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- Drop the original temp tablespace.
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

-- Create the original tablespace again.
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Make this the default temp tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

-- Drop the unwanted second temp tablespace.
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

-- If you have a temporary tablespace without a datafile as
-- sometimes happens with an RMAN restore, this will add
-- a datafile to the tablespace.
ALTER TABLESPACE TEMP
ADD TEMPFILE '/u01/oradata/temp01.dbf' SIZE 10000M
AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED;



Keywords: oracle temp tablespace database

Write-Host Considered Harmful

I read an interesting article written by Jeffrey Snover on the topic of Write-Host vs Write-Output. The article can be seen here.

In a nutshell:

The correct cmdlet to use is Write-Output. Using Write-Output will display the results to the screen when you run you script by itself but, it will also allow your script to be used in a pipeline (or foreach loop) and have the results used by other scripts/cmdlets.

See also

Why I Use Write-Host In PowerShell
This article gives some of the benefits of using Write-Host cmdlet.



Keywords: powershell write-host write-output

PowerShell enumerated types

The following code shows how to create your own enumerated types and a possible way of using the type.

$myenum = "
   Namespace myspace
   {
      public enum cards
      {
         hearts,
         clubs,
         diamonds,
         spades
      }
   }
";

try {
  [myspace.cards]
} catch {
  Add-Type -TypeDefinition $myenum -Language CSharpVersion3;
}

Write-Host "Assigning fred an enum value";
$fred=[myspace.cards]::hearts;

switch ($fred)
{
    ([myspace.cards]::hearts) {"Fred is now hearts"; Break}
    ([myspace.cards]::clubs) {"Fred is now clubs"; Break}
    ([myspace.cards]::diamonds) {"Fred is now diamonds"; Break}
    ([myspace.cards]::spades) {"Fred is now spades"; Break}
}

Write-Host "All done now!";

The try/catch block is used to avoid the error: Add-Type : Cannot add type. The type name 'myspace.cards' already exists. At line:1 char:1 + Add-Type -TypeDefinition $myenum -Language CSharpVersion3; + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (myspace.cards:String) [Add-Type], Exception + FullyQualifiedErrorId : TYPE_ALREADY_EXISTS,Microsoft.PowerShell.Commands.AddTypeCommand if the assembly is already loaded. A namespace is used in the creation of this enum because as the documentation reminds us:

Include a namespace declaration in your type definition. If you omit the namespace declaration, your type might have the same name as another type or the shortcut for another type, causing an unintentional overwrite. For example, if you define a type called "Exception", scripts that use Exception" as the shortcut for System.Exception will fail.

The following code can be used to look at the definitions for the enumerated type.

foreach ($m in [System.Enum]::GetValues([ConsoleColor]))
{ Write-Host ([int]$m): $m }

Keywords: powershell enum namespace

Loaded Assemblies

Use the following code to get a list PowerShell loaded assemblies.

$m = [System.AppDomain]::CurrentDomain.GetAssemblies();
$m.getName();

See also

GetAssemblies Method



Keywords: powershell assembly assemblies

PowerShell parameters

The following code shows two ways in PowerShell can check whether an optional parameter has been used or not.

This could be used in situations where most of the time the parameter is not used and on odd occasions we decide to use the parameter.

Of course, there are other ways in which we can check, but this is also a reminder of two ways to achieve this.

function foo {

[cmdletbinding()]
Param (
        [parameter(Mandatory=$false)]
        [String]
        $Param
      ) #end param

   Write-Host "This is function foo";

   # Method 1
   if ($Param) {
      Write-Host "(1)Parameter is use is: $Param";
   } else {
      Write-Host "No parameter used at one";
   }

   # Method 2
   if ($PSBoundParameters['Param']) {
      Write-Host "(2)Parameter is use is: $Param";
   } else {
      Write-Host "No parameter used at two";
   }

} #end of foo

foo some_info ;

Keywords: powershell parameters check