Skip navigation.

Feed aggregator

ECO 2014 and Slides

DBASolved - Mon, 2014-11-10 07:30

Last week I attended the East Coast Oracle User Group conference, also known as ECO, in Raleigh, NC.  This being my first time at ECO, it was a good event for being a two day conference.  The low-key environment provided a nice, comfortable environment for interaction between the speakers and those in attendance.  If you ever have the chance to catch this conference, it would be a good one to attend.

What you can expect from ECO, is to see great speakers, both local to Raleigh and from around the country. There seems to be opportunities to see also see speakers that we all hear about and would like to see at some point.  As one of the speakers at this year’s conference, I have to say it was nice to have great attendance for my session on Oracle GoldenGate 12c Conflict Detection and Resolution.  My session was scheduled for 45 minutes; due to discussions throughout the session it lasted about 65 minutes.  Although the session ran over, it was exciting to see so many people wantiong to know more about Oracle GoldenGate and what benefits it provides to an organization.

If you would like to see the slides from my ECO session, they can be found here.

Lastly, I would like to say that ECO is one of the smaller user group conferences which seem to draw some great speakers.  Check it out next year!


Filed under: General
Categories: DBA Blogs

Oracle Database Last Logins with Oracle 12c

Tracking when database users last logged in is a common security and compliance requirement – for example to reconcile users and identify stale users. With Oracle 12c this analysis can now be done through standard functionality. New with Oracle12c, the SYS.DBA_USERS has a new column: last_login. 

select username, account_status, common, last_login

from sys.dba_users

order by last_login asc;






















If you have questions, please contact us at

Reference Tags: AuditingOracle Database
Categories: APPS Blogs, Security Blogs

Auditing OBIEE Presentation Catalog Activity with Custom Log Filters

Rittman Mead Consulting - Mon, 2014-11-10 01:49

A question that I’ve noticed coming up a few times on the OBIEE OTN forums goes along the lines of “How can I find out who deleted a report from the Presentation Catalog?”. And whilst the BI Server’s Usage Tracking is superb for auditing who ran what report, we don’t by default have a way of seeing who deleted a report.

The Presentation Catalog (or “Web Catalog” as it was called in 10g) records who created an object and when it was last modified, accessible through both OBIEE’s Catalog view, and the dedicated Catalog Manager tool itself:

But if we want to find out who deleted an object, or maybe who modified it before the most recent person (that is, build up an audit trail of who modified an object) we have to dig a bit deeper.

Presentation Services Log Sources

Perusing the OBIEE product manuals, one will find documented additional Logging in Oracle BI Presentation Services options. This is more than just turning up the log level en masse, because it also includes additional log writers and filters. What this means is that you can have your standard Presentation Services logging, but then configure a separate file to capture more detailed information about just specific goings on within Presentation Services.

Looking at a normal Presentation Services log (in $FMW_HOME/instances/instance1/diagnostics/logs/OracleBIPresentationServicesComponent/coreapplication_obips1/) you’ll see various messages by default – greater or fewer depending on the health of your system – but they all use the Location stack track, such as this one here:

[2014-11-10T06:33:19.000-00:00] [OBIPS] [WARNING:16] [] [saw.soap.soaphelpers.writeiteminfocontents] [ecid: 11d1def534ea1be0:15826b4a:14996b86fbb:-8000-0000000000001ede,0:1] [tid: 2569512704] Resolving and writing full ACL for path /shared/Important stuff/Sales by brand[[
Path: /shared/Important stuff/Sales by brand
AuthProps: AuthSchema=UidPwd-soap|PWD=******|UID=weblogic|User=weblogic
ecid: 11d1def534ea1be0:15826b4a:14996b86fbb:-8000-0000000000001ede,0:1
ThreadID: 2569512704

And it is the Location that is of interest to us here, because it’s what gives hints about the types of log messages that can be emitted and that we may want to filter. For example, the one quoted above is evidently something to do with the Presentation Catalog and SOAP, which I’d guess is a result of Catalog Manager (which uses web services/SOAP to access OBIEE).

To get a full listing of all the possible log sources, first set up the BI command line environment with bi-init:

source $FMW_HOME/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/

and then run:

sawserver -logsources

(If you get an error, almost certainly you didn’t set up the command line environment properly with bi-init). You’ll get an list of over a thousand lines (which gives you an idea of quite how powerful this granular logging is). Assuming you’ll want to peruse it at your leisure, it makes sense to write it to disk which if you’re running this on *nix you can simply do thus:

sawserver -logsources > sawserver.logsources.txt

To find what you want on the list, you can just search through it. Looking for anything related to “catalog” and narrowing it down further, I came up with these interesting sources:

[oracle@demo ~]$ sawserver -logsources|grep catalog|grep local

Configuring granular Presentation Services logging

Let us see how to go and set up this additional logging. Remember, this is not the same as just going to Enterprise Manager and bumping the log level to 11 globally – we’re going to retain the default logging level, but for just specific actions that occur within the tool, capture greater information. The documentation for this is here.

The configuration is found in the instanceconfig.xml file, so like all good sysadmins let’s take a backup first:

cd $FMW_HOME/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/
cp instanceconfig.xml instanceconfig.xml.20141110

Now depending on your poison, open the instanceconfig.xml directly in a text editor from the command line, or copy it to a desktop environment where you can open it in your favourite text editor there. Either way, these are the changes we’re going to make:

  1. Locate the <Logging> section. Note that within it there are three child entities – <Writers>, <WriterClassGroups> and <Filters>. We’re going to add an entry to each.

  2. Under <Writers>, add:

    <Writer implementation="FileLogWriter" name="RM Presentation Catalog Audit" disableCentralControl="true" writerClassId="6" dir="{%ORACLE_BIPS_INSTANCE_LOGDIR%}" filePrefix="rm_pres_cat_audit" maxFileSizeKb="10240" filesN="10" fmtName="ODL-Text"/>

    This defines a new writer than will write logs to disk (FileLogWriter), in 100MB files of which it’ll keep 10. If you’re defining additional Writers, make sure they have a unique writerClassId See docs for detailed syntax.

  3. Under <WriterClassGroups> add:

    <WriterClassGroup name="RMLog">6</WriterClassGroup>

    This defines the RMLog class group as being associated with writerClassId 6 (as defined above), and is used in the Filters section to direct logs. If you wanted you could log entries to multiple logs (eg both file and console) this way.

  4. Under <Filters> add:

    <FilterRecord writerClassGroup="RMLog" disableCentralControl="true" path="saw.catalog.local.moveItem" information="32" warning="32" error="32" trace="32" incident_error="32"/>
    <FilterRecord writerClassGroup="RMLog" disableCentralControl="true" path="saw.catalog.local.deleteItem" information="32" warning="32" error="32" trace="32" incident_error="32"/>

    Here we’re defining two event filters, with levels turned up to max (32), directing the capture of any occurences to the RMLog writerClassGroup.

After making the changes to instanceconfig.xml, restart Presentation Services:

$FMW_HOME/instances/instance1/bin/opmnctl restartproc ias-component=coreapplication_obips1

Here’s the completed instanceconfig.xml from the top of the file through to the end of the <Logging> section, with my changes overlayed the defaults:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!-- Oracle Business Intelligence Presentation Services Configuration File -->
<WebConfig xmlns="">

      <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><CatalogPath>/app/oracle/biee/instances/instance1/SampleAppWebcat</CatalogPath>



            <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><Writer implementation="FileLogWriter" name="Global File Logger" writerClassId="1" dir="{%ORACLE_BIPS_INSTANCE_LOGDIR%}" filePrefix="sawlog" maxFileSizeKb="10240" filesN="10" fmtName="ODL-Text"/>
            <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><Writer implementation="CoutWriter" name="Console Logger" writerClassId="2" maxFileSizeKb="10240"/>
            <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><Writer implementation="EventLogWriter" name="System Event Logger" writerClassId="3" maxFileSizeKb="10240"/>
            <!--  The following writer is not centrally controlled -->
            <Writer implementation="FileLogWriter" name="Webcat Upgrade Logger" disableCentralControl="true" writerClassId="5" dir="{%ORACLE_BIPS_INSTANCE_LOGDIR%}" filePrefix="webcatupgrade" maxFileSizeKb="2147483647" filesN="1" fmtName="ODL-Text"/>
            <Writer implementation="FileLogWriter" name="RM Presentation Catalog Audit" disableCentralControl="true" writerClassId="6" dir="{%ORACLE_BIPS_INSTANCE_LOGDIR%}" filePrefix="rm_pres_cat_audit" maxFileSizeKb="10240" filesN="10" fmtName="ODL-Text"/>

            <WriterClassGroup name="All">1,2,3,5,6</WriterClassGroup>
            <WriterClassGroup name="File">1</WriterClassGroup>
            <WriterClassGroup name="Console">2</WriterClassGroup>
            <WriterClassGroup name="EventLog">3</WriterClassGroup>
            <WriterClassGroup name="UpgradeLogFile">5</WriterClassGroup>
            <WriterClassGroup name="RMLog">6</WriterClassGroup>

            <!--  These FilterRecords are updated by centrally controlled configuration -->
            <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><FilterRecord writerClassGroup="File" path="saw" information="1" warning="31" error="31" trace="0" incident_error="1"/>
            <!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control--><FilterRecord writerClassGroup="File" path="saw.mktgsqlsubsystem.joblog" information="1" warning="31" error="31" trace="0" incident_error="1"/>

            <!--  The following FilterRecords are not centrally controlled -->
            <FilterRecord writerClassGroup="UpgradeLogFile" disableCentralControl="true" path="saw.subsystem.catalog.initialize.upgrade" information="1" warning="32" error="32" trace="1" incident_error="32"/>
            <FilterRecord writerClassGroup="UpgradeLogFile" disableCentralControl="true" path="saw.subsystem.catalog.upgrade" information="1" warning="32" error="32" trace="1" incident_error="32"/>
            <FilterRecord writerClassGroup="RMLog" disableCentralControl="true" path="saw.catalog.local.moveItem" information="32" warning="32" error="32" trace="32" incident_error="32"/>
            <FilterRecord writerClassGroup="RMLog" disableCentralControl="true" path="saw.catalog.local.deleteItem" information="32" warning="32" error="32" trace="32" incident_error="32"/>



Granular logging in action

Having restarted Presentation Services after making the above change, I can see in my new log file whenever an item from the Presentation Catalog is deleted, by whom, and from what IP address:

[2014-11-10T07:13:36.000-00:00] [OBIPS] [TRACE:1] [] [saw.catalog.local.deleteItem] [ecid: 11d1def534ea1be0:15826b4a:14996b86fbb:-8000-0000000000002cf1,0:1] [tid: 2458068736] Succeeded with '/shared/Important stuff/Sales by brand 2'[[
Path: /shared/Important stuff/Sales by brand 2
SessionID: p8n6ojs0vkh7tou0mkstmlc9me381hadm9o1fui
AuthProps: AuthSchema=UidPwd|PWD=******|UID=r.mellie|User=r.mellie
ecid: 11d1def534ea1be0:15826b4a:14996b86fbb:-8000-0000000000002cf1,0:1
ThreadID: 2458068736
HttpCommand: CatalogTreeModel
HttpArgs: action='rm',_scid='QR5zMdHIL3JsW1b67P9p',icharset='utf-8',urlGenerator='qualified',paths='["/shared/Important stuff/Sales by brand 2"]'

And the same for when a file is moved/renamed:

[2014-11-10T07:28:17.000-00:00] [OBIPS] [TRACE:1] [] [saw.catalog.local.moveItem] [ecid: 11d1def534ea1be0:15826b4a:14996b86fbb:-8000-0000000000003265,0:1] [tid: 637863680] Source '/shared/Important stuff/copy of Sales by brand', Destination '/shared/Important stuff/Sales by brand 2': Succeeded with '/shared/Important stuff/copy of Sales by brand'[[
Path: /shared/Important stuff/copy of Sales by brand
SessionID: ddt6eo7llcm0ohs5e2oivddj7rtrhn8i41a7f32
AuthProps: AuthSchema=UidPwd|PWD=******|UID=f.saunders|User=f.saunders
ecid: 11d1def534ea1be0:15826b4a:14996b86fbb:-8000-0000000000003265,0:1
ThreadID: 637863680
HttpCommand: CatalogTreeModel
HttpArgs: path='/shared/Important stuff/copy of Sales by brand',action='ren',_scid='84mO8SRViXlwJ*180HV7',name='Sales by brand 2',keepLink='f',icharset='utf-8',urlGenerator='qualified'

Be careful with your logging

Just because you can log everything, don’t be tempted to actually log everything. Bear in mind that we’re crossing over from simple end-user logging here into the very depths of the sawserver (Presentation Services) code, accessing logging that is extremely diagnostic in nature. Which for our specific purpose of tracking when someone deletes an object from the Presentation Catalog is handy. But as an example, if you enable saw.catalog.local.writeObject event logging, you may think that it will record who changed a report when, and that might be useful. But – look at what gets logged every time someone saves a report:

[2014-11-10T07:19:32.000-00:00] [OBIPS] [TRACE:1] [] [saw.catalog.local.writeObject] [ecid: 11d1def534ea1be0:15826b4a:14996b86fbb:-8000-0000000000002efb,0:1] [tid: 2454759168] Succeeded with '/shared/Important stuff/Sales 01'[[
Path: /shared/Important stuff/Sales 01
SessionID: p8n6ojs0vkh7tou0mkstmlc9me381hadm9o1fui
AuthProps: AuthSchema=UidPwd|PWD=******|UID=r.mellie|User=r.mellie
ecid: 11d1def534ea1be0:15826b4a:14996b86fbb:-8000-0000000000002efb,0:1
ThreadID: 2454759168
HttpCommand: CatalogTreeModel
HttpArgs: path='/shared/Important stuff/Sales 01',action='wr',_scid='QR5zMdHIL3JsW1b67P9p',repl='t',followLinks='t',icharset='utf-8',modifiedTime='1415600931000',data='<saw:report xmlns:saw="" xmlns:xsi="" xmlns:xsd="" xmlns:sawx="" xmlVersion="201201160"><saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;A - Sample Sales&quot;" withinHierarchy="true"><saw:columns><saw:column xsi:type="saw:regularColumn" columnID="c1dff1637cbc77948"><saw:columnFormula><sawx:expr xsi:type="sawx:sqlExpression">"Time"."T05 Per Name Year"</sawx:expr></saw:columnFormula></saw:column></saw:columns></saw:criteria><saw:views currentView="0"><saw:view xsi:type="saw:compoundView" name="compoundView!1"><saw:cvTable><saw:cvRow><saw:cvCell viewName="titleView!1"><saw:displayFormat><saw:formatSpec/></saw:displayFormat></saw:cvCell></saw:cvRow><saw:cvRow><saw:cvCell viewName="tableView!1"><saw:displayFormat><saw:formatSpec/></saw:displayFormat></saw:cvCell></saw:cvRow></saw:cvTable></saw:view><saw:view xsi:type="saw:titleView" name="titleView!1"/><saw:view xsi:type="saw:tableView" name="tableView!1" scrollingEnabled="false"><saw:edges><saw:edge axis="page" showColumnHeader="true"/><saw:edge axis="section"/><saw:edge axis="row" showColumnHeader="true"><saw:edgeLayers><saw:edgeLayer type="column" columnID="c1dff1637cbc77948"/></saw:edgeLayers></saw:edge><saw:edge axis="column" showColumnHeader="rollover"/></saw:edges></saw:view></saw:views></saw:report>',sig='queryitem1'

It’s the whole report definition! And this is a very very small report – real life reports can be page after page of XML. That is not a good level at which to be recording this information. If you want to retain this kind of control over who is saving what report, you should maybe be looking at authorisation groups for your users in terms of where they can save reports, and have trusted ‘gatekeepers’ for important areas.

As well as the verbose report capture with the writeObject event, you also get this background chatter:

[2014-11-10T07:20:27.000-00:00] [OBIPS] [TRACE:1] [] [saw.catalog.local.writeObject] [ecid: 0051rj7FmC3Fw000jzwkno0007PK000000,0:200] [tid: 3034580736] Succeeded with '/users/r.mellie/_prefs/volatileuserdata'[[
Path: /users/r.mellie/_prefs/volatileuserdata
ecid: 0051rj7FmC3Fw000jzwkno0007PK000000,0:200
ThreadID: 3034580736
task: Cache/Sessions

volatileuserdata is presumably just that (user data that is volatile, constantly changing) and not something that it would be of interest to anyone to log – but you can’t capture actual report writes without capturing this too. On a busy system you’re going to be unnecessarily thrashing the log files if you capture this event by routine – so don’t!


The detailed information is there for the taking in Presentation Services’ excellent granular log sources – just be careful what you capture lest you bite off more than you can chew.

Categories: BI & Warehousing

Oracle assists professionals looking for recovery programs

Chris Foot - Mon, 2014-11-10 01:40

Between natural disasters, cybercrime and basic human error, organizations are looking for tools that support disaster recovery endeavors, as well as the professionals capable of using them.

A fair number of administrators often use Oracle's advanced recovery features to help them ensure business continuity for database-driven applications. The database vendor recently unveiled a couple of new offerings that tightly integrate with their Oracle Database architecture.

Restoration and recovery
IT-Online acknowledged Oracle's Zero Data Loss Recovery Appliance, which is the first of its kind in regard to its ability to ensure critical Oracle databases retain their information even if the worst should occur. The source maintained that Oracle's new architecture can protect thousands of databases using a cloud-based, centralized recovery appliance as the target.

In other words, the Recovery Appliance isn't simply built to treat databases as information repositories that need to be backed up every so often.  The appliance's architecture replicates changes in real time to ensure that the recovery databases are constantly in sync with their production counterparts.  Listed below are several features that make the architecture stand out among conventional recovery offerings:

  • Live "Redo" data is continuously transported from the databases to the cloud-based appliance protecting the most recent transactions so that servers don't sustain data loss in the event of a catastrophic failure
  • To reduce the impact on the production environment, the Recovery Appliance architecture only delivers data that has been changed, which reduces server loads and network impact
  • The appliance's automatic archiving feature allows backups to be automatically stored on low cost tape storage
  • Data stored on the appliance can be used to recreate a historical version of the database

Simplifying database management and availability 
The second application that Oracle hopes will to enhance entire database infrastructures Oracle Database Appliance Management. The appliance manager application allows administrators to create rapid snapshots of both database and virtual machines, enabling them to quickly create and allocate development and test environments.

"With this update of Oracle Database Appliance software, customers can now reap the benefits of Oracle Database 12c, the latest release of the world's most popular database right out of the box," said Oracle Vice President of Product Strategy and Business Development Sohan DeMel. "We added support for rapid and space-efficient snapshots for creating test and development environments, organizations can further capitalize on the simplicity of Oracle engineered systems with speed and efficiency." 

The post Oracle assists professionals looking for recovery programs appeared first on Remote DBA Experts.

SQL Developer and Big Data Appliance (sort of)

DBASolved - Sun, 2014-11-09 20:55

Recently, Enkitec received an Oracle Big Data Appliance (BDA) for our server farm in Dallas (Thanks Accenture!).  With this new addition to the server farm, I’m excited to see what the BDA can do and how to use it.  Since I use Oracle SQL Developer for a lot of things, I figure I better see if I can connect to it…. wait I don’t have access yet, darn!  Simple solution, I’ll just use the Oracle Virtual Box VM (Big Data Lite) to make sure my that my SQL Developer can connect when I eventually get access.

The first thing I needed is download the Big Data Lite VM.  It can be downloaded from the Oracle Technology Network (here). The second thing I needed to do was download the connectors for HIVE from Cloudera, use the version for the platform you need (here).

After downloading the Cloudera connectors for HIVE, these needed to be unzipped in a location that can be accessed by SQL Developer. Since I’m on a MacBook Pro, I unzipped them in this location:

$ cd ~/Downloads
$ unzip ./ -d /Users/Bobby/Oracle/connectors
$ cd /Users/Bobby/Oracle/connectors
$ ls -ltr
total 21176
-rw-r--r--@ 1 Bobby  staff  5521341 Sep 10 15:16
-rw-r--r--@ 1 Bobby  staff  5317239 Sep 10 15:16
$ unzip ./ -d ./Hive
$ cd ./Hive
$ ls -ltr
-r--r--r--@ 1 Bobby  staff  1083758 Sep  8 17:28 Cloudera - Simba JDBC Driver for Hive Install Guide.pdf
-rw-r--r--@ 1 Bobby  staff     9679 Sep  8 23:28 slf4j-log4j12-1.5.8.jar
-rw-r--r--@ 1 Bobby  staff    23445 Sep  8 23:28 slf4j-api-1.5.8.jar
-rw-r--r--@ 1 Bobby  staff   367444 Sep  8 23:28 log4j-1.2.14.jar
-rw-r--r--@ 1 Bobby  staff   347531 Sep  8 23:28 libthrift-0.9.0.jar
-rw-r--r--@ 1 Bobby  staff   275186 Sep  8 23:28 libfb303-0.9.0.jar
-rw-r--r--@ 1 Bobby  staff   294796 Sep  8 23:28 ql.jar
-rw-r--r--@ 1 Bobby  staff   596600 Sep  8 23:28 hive_service.jar
-rw-r--r--@ 1 Bobby  staff  7670596 Sep  8 23:28 hive_metastore.jar
-rw-r--r--@ 1 Bobby  staff  2972229 Sep  8 23:28 TCLIServiceClient.jar
-rw-r--r--@ 1 Bobby  staff  1656683 Sep  8 23:29 HiveJDBC4.jar

Once the connectors are extracted, SQL Developer needs to know which HIVE connector to use.  In this case the JDBC4 connector is required.  Unzipped the JDBC4 set of files into a directory, in my case I’m using a directory called Hive.

In order to tell SQL Developer which connector to use, it needs to be specified in the interface by doing the following:

  1. Start SQL Developer
  2. Oracle SQL Developer -> Preferences
  3. Database -> Third Party JDBC -> Add Entry
  4. Restart SQL Developer

After restarting SQL Developer, we now see an option on the connection screen for Hive.





Now SQL Developer is ready to connect to a Big Data Appliance, oh I mean to my VM for Big Data Lite :), lets setup a connection and see if we can connect.  Since I’m connecting to a Virtual Box VM, I need to setup some ports to be used between my MacBook and the VM.  In this case, I have setup a SQL port on 15211 which maps to the standard database port of 1521.  For the Hive connection I’ve setup 10001 which maps to port 10000.



With the ports put in place, now I can setup SQL Developer to connect to the Hive on the Big Data Lite VM.  You will notice that on the username, password, server name and port is needed.  The database parameter is optional when connecting to a Bid Data Hive.





Once the connection is configured, I can login to the Hive and review what tables are listed in the Big Data Lite VM.









The end result is that now I can visualize the data that is in a Big Data Appliance/Lite VM and begin to interact with objects defined within.


Filed under: BigData
Categories: DBA Blogs

rmoug 2015 presentation ready to go ... makes me ( not ) grumpy

Grumpy old DBA - Sun, 2014-11-09 19:04
All ready to roll with RMOUG 2015 Training days presentation "OS Truth, little white lies, and the Oracle Wait Interface".

Best of course to come to RMOUG 2015 Training days ... but this is link to pdf version of the presentation here: John Hurley RMOUG 2015

If you are out there ( very excited personally my first time at this conference ) please kick me and say hello.  Ok maybe skip the kicking part ...

Categories: DBA Blogs

handy ash queries to look at blocked sessions ( how many when for what event ) ...

Grumpy old DBA - Sun, 2014-11-09 15:13
Licensing may be required ... please check if applicable.

A query like this can be used to check how much blocking and what session it was ... ( so then you can drill into those blockers sessions ).  Probably can be done easily with some kind of rollup query?

Adjust SAMPLE_TIMEs in where clause below.

select ash_data.*, substr(sqlinfo.sql_text,1,70)
(SELECT to_char(ash.sample_time,'MM/DD/YYYY HH24:MI:SS') what_time,  count(*) sessions_blocked, ash.event, ash.blocking_session, ash.blocking_session_serial#, ash.sql_id, ash.sql_opname
WHERE ash.SAMPLE_TIME >= TO_DATE('01-NOV-2014 13:00', 'DD-MON-YYYY HH24:MI')
  and ash.sample_time <= to_date('08-NOV-2014 17:00', 'DD-MON-YYYY HH24:MI')
-- and ash.event like 'enq: TX - row%'
and blocking_session is not null
group by to_char(ash.sample_time,'MM/DD/YYYY HH24:MI:SS'), ash.event, ash.sql_id, ash.sql_opname, ash.blocking_session, ash.blocking_session_serial#
order by 1) ash_data,
v$sqlarea sqlinfo
where ash_data.sql_id = sqlinfo.sql_id
and sessions_blocked >= 1
order by what_time


For example once you have narrowed it down to something interesting looking ( who is blocked / what sql_id / what event ) ... you can use something like this.  I am now looking at any active history information on what the blockers were doing or waiting on.

select * from DBA_HIST_ACTIVE_SESS_HISTORY where ( session_id, session_serial# ) in (
SELECT blocking_session, blocking_session_serial# FROM DBA_HIST_ACTIVE_SESS_HISTORY
and event like 'enq: TX - row%'
and sql_id = '0kbzgn17vbfc5' )
and SAMPLE_TIME > TO_DATE('06-NOV-2014 14:30', 'DD-MON-YYYY HH24:MI')
order by sample_time
Categories: DBA Blogs

Elevated Task Manager Shortcut on Windows 7

Mike Rothouse - Sun, 2014-11-09 07:05
Received a replacement laptop so now I have to perform some software installs and configure it the way I had my old laptop.  To get Task Manager to display All Processes without having to select this option every time, it must run as Administrator.  Searched Google and found this link which describes how to setup […]

Oracle SQL Profile: why multiple OPT_ESTIMATE?

Yann Neuhaus - Sat, 2014-11-08 15:48

In a previous blog I'v shared my script to retrieve the OPT_ESTIMATE hints from a SQL Profile. In the example I made, I had two lines for each table:

--- PROFILE HINTS from dbiInSite (1) statement 4fz1vtn0w8aak:

The reason is that when the optimizer do some transformations to the query, then the query block identifiers can change. And when you adjust a cardinality estimation, you must do it for all transformations or you will completely mess up the optimizer choice.

When I do an explain plan which show the query blocks, I have only the SEL$58A6D7F6 one:

SQL> explain plan for
  2  select distinct DEPARTMENT_NAME  from DEPARTMENTS join EMPLOYEES
  3  using(DEPARTMENT_ID)  where DEPARTMENT_NAME like '%ing' and SALARY>20000 ;


SQL> select * from table(dbms_xplan.display(format=>'basic +alias'));

Plan hash value: 3041748347
| Id  | Operation                             | Name              |
|   0 | SELECT STATEMENT                      |                   |
|   1 |  HASH UNIQUE                          |                   |
|   2 |   NESTED LOOPS SEMI                   |                   |
|   3 |    TABLE ACCESS FULL                  | DEPARTMENTS       |
|   5 |     INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |

Query Block Name / Object Alias (identified by operation id):

   1 - SEL$58A6D7F6
   4 - SEL$58A6D7F6 / EMPLOYEES@SEL$1
   5 - SEL$58A6D7F6 / EMPLOYEES@SEL$1

In order to confirm that the duplicate OPT_ESTIMATE are coming from different transformations, I've generated a 10053 trace and searched for SEL$6AE97DF7:

Registered qb: SEL$6AE97DF7 0x851d8eb8 (DISTINCT PLACEMENT SEL$58A6D7F6; SEL$58A6D7F6; "EMPLOYEES"@"SEL$1")
  signature (): qb_name=SEL$6AE97DF7 nbfros=2 flg=0
    fro(0): flg=0 objn=92595 hint_alias="DEPARTMENTS"@"SEL$1"
    fro(1): flg=1 objn=0 hint_alias="VW_DTP_43B5398E"@"SEL$43B5398E"

that's the Distinct Placement.
let's try the PLACE_DISTINCT hint:

SQL> explain plan for
  3  using(DEPARTMENT_ID)  where DEPARTMENT_NAME like '%ing' and SALARY>20000 ;


SQL> select * from table(dbms_xplan.display(format=>'basic +alias'));

Plan hash value: 2901355344

| Id  | Operation                              | Name              |
|   0 | SELECT STATEMENT                       |                   |
|   1 |  HASH UNIQUE                           |                   |
|   2 |   NESTED LOOPS SEMI                    |                   |
|   3 |    TABLE ACCESS FULL                   | DEPARTMENTS       |
|   4 |    VIEW PUSHED PREDICATE               | VW_DTP_43B5398E   |
|   6 |      INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |

Query Block Name / Object Alias (identified by operation id):

   1 - SEL$6AE97DF7
   4 - SEL$9B757045 / VW_DTP_43B5398E@SEL$43B5398E
   5 - SEL$9B757045 / EMPLOYEES@SEL$1
   6 - SEL$9B757045 / EMPLOYEES@SEL$1

Here is where the

makes sense. The same cardinality adjustment must be done for each transformation that the optimizer is evaluating.

That observation brings me to the following: what happens to your profiles when you upgrade to a version that brings new optimizer transformations? The optimizer will compare some plans with adjusted cardinalities, compared with some plans with non-adjusted cardinalites. And that is probably not a good idea.

In my opinion, SQL Profiles are just like hints: a short term workaround that must be documented and re-evaluated at each upgrade.

PeopleSoft's paths to the Cloud - Part III

Javier Delgado - Sat, 2014-11-08 14:53
In my previous posts on this series, I have covered how cloud computing could be used to reduce costs and maximize the flexibility of PeopleSoft Development and Production environments. In both cases, I focused on one specific area of cloud computing, Infrastructure as a Service (IaaS).

Today I will explain what kind of benefits can be expected by using another important area: Database as a Service (DBaaS). Instead of using an IaaS provisioned server to install and maintain your database, DBaaS providers take responsibility for installing and maintaining the database.

There are many players in this market, including Amazon, Microsoft and Oracle. The service features may differ, but in a nutshell, they normally offer these capabilities:

  • Backups: the database backups are automated, and you can decide to restore point-in-time backups at any moment. You can also decide when to take a snapshot of your database, which may be eventually be used to create another database instance (for example, to copy your Production database into the User Acceptance environment).
  • High Availability: while some IaaS provider do not support high-availability database solutions such as Oracle RAC (for instance, it is not supported by Amazon EC2), many DBaaS providers include high availability by default.
  • Contingency: some providers maintain a standby copy of your database in another data center. This allows you to quickly restore your system in the case the original data center's services are lost.
  • Patching: although you can decide when to apply a database patch, the DBaaS will do that for you. In many case, you can turn on automatic patching, in order to make sure your database engine is always up to date.
  • Monitoring: providers give the system administrators access to a management console, in which they can monitor the database behavior and add or remove resources as needed.
  • Notifications: in order to simplify the monitoring effort, you normally have the possibility of setting up notifications to be received by email and/or SMS upon a list of events, which may include CPU usage, storage availability, etc.

Under my point of view, these services offer significant advantages for PeopleSoft customers, particularly if your current architecture does not support all the previously mentioned services or you do not have the right DBA skills in-house. Even if your organization does not fall in these categories, the scalability and elasticity of DBaaS providers is very difficult to match by most internal IT organizations.

In any case, if you are interested in using Database as a Service for your PeopleSoft installation, make sure you correctly evaluate what each provider can give you.

A New e-Literate TV Series is in Production

Michael Feldstein - Sat, 2014-11-08 13:09

We have been quiet about e-Literate TV lately, but it doesn’t mean that we have been idle. In fact, we’ve been hard at work filming our next series. In addition to working with our old friends at IN THE TELLING—naturally—we’re also collaborating with EDUCAUSE Learning Initiative (ELI) and getting funding and support from the Bill & Melinda Gates Foundation.

As we have discussed both here and elsewhere, we think the term “personalized learning” carries a lot of baggage with it that needs to be unpacked, as does the related concept of “adaptive learning.” The field in general is grappling with these broad concepts and approaches; an exploration of specific examples and implementations should sharpen our collective understandings about the promise and risks of these concepts and approaches. The Gates foundation has funded the development of an ETV series and given us a free editorial hand to explore the topics of personalization and adaptive learning.

The heart of the series will be a series of case studies at a wide range of different schools. Some of these schools will be Gates Foundation grantees, piloting and studying the use of “personalized learning” technology or product, while others will not. (For more info about some of the pilots that Gates is funding in adaptive learning, including which schools are participating and the evaluation process the foundation has set up to ensure an object review of the results, see Phil’s post about the ALMAP program.) Each ETV case study will start by looking at who the students are at a particular school, what they’re trying to accomplish for themselves, and what they need. In other words who are the “persons” for whom we are trying to “personalize” learning? Hearing from the students directly through video interviews will be a central part of this series. We then take a look at how each school is using technology to support the needs of those particular students. We’re not trying to take a pro- or anti- position on any of these approaches. Rather, we’re trying to understand what personalization means to the people in these different contexts and how they are using tools to help them grapple with it.

Because many Americans have an idealized notion of what a personalized education means that may or may not resemble what “personalized learning” technologies deliver, we wanted to start the series by looking at that ideal. We filmed our first case study at Middlebury College, an elite New England liberal arts college that has an 8-to-1 student/teacher ratio. They do not use the term “personalized learning” at Middlebury, and some stakeholders there expressed the concern that technology, if introduced carelessly, could depersonalize education for Middlebury students. That said, we heard both students and teachers talk about ways in which even an eight-person seminar can support more powerful and personalized learning through the use of technology.

The second school on our list was Essex County College in Newark, New Jersey, where we are filming as of this writing (but will be finished by publication time). As you might imagine, the students, their needs, and their goals and aspirations are different, and the school’s approach to supporting them is different. Here again, we’ll be asking students and teachers for their stories and their views rather than imposing ours. We intend to visit a diverse handful of schools, with the goal of releasing a few finished case studies by the end of this year and more early next year.

With the help of the good folks at ELI, we will also be bringing together a panel at the ELI 2015 conference, consisting of the people from the various case studies to have a conversation about what we can learn about the idea of personalized learning by looking across these various contexts and experiences. This will be a “flipped” panel in the sense that the panelists (and the audience) will have had the opportunity to watch the case study videos before sitting down and talking to each other. The discussion will be filmed and included in the ETV series.

We’re pretty excited about the series and grateful, as always, to the support of our various partners. We’ll have more to say—and show—soon.

Stay tuned.

The post A New e-Literate TV Series is in Production appeared first on e-Literate.

StatsPack and AWR Reports -- Bits and Pieces -- 3

Hemant K Chitale - Sat, 2014-11-08 09:48
This is the third post in this series.
Post 1 is here
Post 2 is here

Note : Some figures / details may be slightly changed / masked to hide the real source.

Identifying Unusual Events / Behaviours / Applications

Here I find a few "unusual" events and mark them out.

Extract A : 11.2 AWR

Snap IdSnap TimeSessionsCursors/SessionBegin Snap:.... 03:00:57107.7End Snap:.... 07:00:07114.9Elapsed:239.17 (mins)DB Time:22.61 (mins)
Top 5 Timed Foreground Events

    EventWaitsTime(s)Avg wait (ms)% DB timeWait ClassDB CPU1,33298.16SQL*Net more data to client49,7012001.50NetworkSQL*Net more data from client213,915500.34Networkdb file scattered read1,159110.08User I/Odb file sequential read7,547100.07User I/O
    The two "SQL*Net more data" sets of waits are the unusual events.The Time on SQL*Net more data to/from client is negligible isn't it ?  So, should I be concerned ?  Over a 4 hour period, only 20seconds were on "SQL*Net more data to client".  Time on "SQL*Net more data from client" is much lower at a total time of 5seconds only.  So "Time based" tuning would ignore these two waits.Foreground Wait EventsEventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn% DB timeSQL*Net more data to client49,701020037.781.50SQL*Net more data from client213,915050162.620.34db file scattered read1,1580110.880.08db file sequential read7,5500105.740.07

    SQL*Net message to client652,102000495.890.04

    SQL*Net message from client652,1020183,327281495.89
    Not that Oracle treats "SQL*Net message from client" as an idle wait so the 183,327seconds of wait time do NOT appear in the Top 5 Timed Foreground Events list.

    I would draw attention to the high number of "more data from client" waits and the correlation with the "message from client" waits.  Either extremely large SQL statements or PLSQL blocks are being submitted very frequently or row inserts with very large array sizes (number of rows per insert) are being received.  In this case, further investigation reveals an ETL loader that does bulk inserts of a number of rows per array.  If we need tuning, tuning the SDU may help.
    Similarly the "more data to client" indicates large data sets are being returned.  The numbers of columns and rows per every "send" are high.
    Instance Activity Stats (from the same AWR)StatisticTotalper Secondper Trans

    Requests to/from client654,73845.63497.90

    SQL*Net roundtrips to/from client654,74045.63497.91

    bytes received via SQL*Net from client1,793,072,463124,950.541,363,553.20bytes sent via SQL*Net to client552,048,24738,469.57419,808.55

    logical read bytes from cache762,514,227,20053,135,924.61579,858,727.91

    physical read total bytes8,772,479,488611,311.626,671,087.06

    physical write total bytes25,334,243,3281,765,420.7619,265,584.28

    redo size6,373,204,848444,117.794,846,543.61

    1.793billion bytes received in 654K SQL*Net trips is 2741bytes per trip received at 45 messages per second.  Given that it is still only 2,741bytes per trip, possibly the array size could also be tuned with the SDU and TDU.
    So, this is an AWR that doesn't call for tuning but reveals information about how the database is being used.  Large number of rows (large number of columns) being inserted and retrieved in each call.  The Performance Analyst needs to be aware of the nature of the "application" --- here it is not OLTP users but an ETL job that is the "application".  Although the database had more than a hundred sessions a very small number of sessions (possibly 4 ?) were active doing ETL and checking the status of ETL tables during this window.

    Would you care to analyze the other statistics I've listed -- bytes read and bytes written ?

    Extract B : 10.2 AWR
    Snap IdSnap TimeSessionsCursors/SessionBegin Snap:
     00:00:072284.5End Snap:
     23:00:062324.3Elapsed:1,379.97 (mins)DB Time:11,543.24 (mins)
    I know.  It is really bad and most likely meaningless to get an AWR for a 24hour range.  (I'll not go into the details about why the AWR is for 24hours -- save that I did NOT ask for a 24hour AWR report).
    Top 5 Timed Events
    EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait ClassCPU time258,10137.3db file sequential read62,150,655208,148330.1User I/Odb file scattered read28,242,529141,638520.4User I/ORMAN backup & recovery I/O1,597,42137,137235.4System I/Oenq: TX - row lock contention22,27634,9421,5695.0ApplicationThe RMAN Backup load is expected.  (Why ? Given a 24hour report, I expect RMAN to have run at least once during the day).For performance tuning, I would look at the "db file ... read" events and identify and analyze SQL statements and the schema.
    What is the "unusual" event here ?  It is the "enq: TX - row lock contention".  Over a period of 1,380minutes, there were 22,276 Row-Lock Contention waits.  Actually, this application does not have the same load throughout the 23hours.  Most likely, it had load for 15hours only.  So, we had 22,276 Row-Lock Contention waits over 15hours.  That translates to 1,485 waits per hour or one Row-Lock Contention wait every 2.5seconds.  Now, that is a very high frequency.  Either users are locking each other out for a very short while (1.569seconds per wait on average) or there is/are one or more jobs that run at a very high frequency and update a common "reference" table.  I won't reveal my findings here but analysis of the SQL statements indicates what the "problem" is.
    Now, should "Time based performance tuning" be worried about the 5% of time lost on these waits ?  Probably not.  But they do indicate something peculiar in the design of this system.  There are less than 250 user sessions in this OLTP database but there is/are one or more jobs that is/are locking itself every 2.5seconds -- so there is some point of serialisation occurring.  Is that job also accounting for CPU time or 'db file read' time ?  That needs further analysis.
    Both these cases show how a Performance Analyst needs to know how the database is being used.  What sort of jobs are submitted, besides OLTP users ?

    Categories: DBA Blogs

    Singapore Oracle Sessions - Beginnings

    Doug Burns - Fri, 2014-11-07 20:31

    Last Monday evening we had the first Singapore Oracle Sessions - an informal meetup of Oracle professionals thrown together at the last minute by a few of us.

    Morten Egan (or as I believe he is called in Denmark now - The Traitor ;-)) mentioned to me months ago that if there was no user group when we arrived in Singapore, then we should start one. At the time he was the current (now retired) chairman of the Danish Oracle User Group (DOUG, strangely enough) and, as I've presented at and supported various Oracle user events over the years and am an ACE Director, it seemed fitting that we should try to build something for the Singapore Oracle community.

    The fact that the Oracle ACE Hemant Chitale works for the same company and that the ACE Director Bjoern Rost would be spending a few days at my place before continuing on to the OTN APAC Tour was too much of an opportunity. After a short chat on Twitter we decided to bite the bullet and I started researching venues and contacted some of the locals. We only had 6 days to arrange it so it was either brave or stupid!

    As it came together and (through a few very good contacts) we had more and more attendees registering it started to seem like a reality and eventually Bjoern, Madeleine and I found ourselves walking along to the Bugis area on Monday, hoping for the best. Despite some initial problems finding the venue, we arrived to find the extremely helpful Sean Low of Seminar Room who took excellent care of us. 

    Within the matter of 15 minutes or so, 33 of the 36 or so who had registered were safely settled in their seats (including my other half Madeleine who *never* attends Oracle stuff!) for my brief introduction during which Sean insisted I try out the hand-held microphone.

    My big Sinatra moment (Not)

    My big Sinatra moment (not).

    First up was Bjoern Rost of Portrix with "Change the way you think about tuning with SQL Plan Management" which, as those who've seen me present on the subject at Openworld, BGOUG or UKOUG would know is a subject dear to my heart. However, Bjoern seems to have had much more success with it than my failed attempts that were damned by literal values and Dynamic SQL. (I've since had a little more success, but mainly as a narrow solution to very specific problems.)

    Bjoern and attentive audience

    As you can see, the room was pretty full and the audience very attentive (except for a few people who appear to be mucking around with their phones!). They weren't afraid to ask some interesting and challenging questions too, which I always find very encouraging. 

    Early in Bjoern's presentation we suffered what I would say was the only significant disappointment of the night as both the drinks and the pizza turned up early! It was nice of the delivery companies not to be late, but my stupid expectation that 7pm meant 7pm ensured that I was standing at the back of the room surrounded by obviously gorgeous pizza that was slowly going cold, not knowing whether I should stop Bjoern in his tracks or not. Manners dictated not (particularly as there were so many people in a small room) but the pizza experience later suggests I was wrong. Lesson learned! (Note that I had to ask others about the pizza as it's on my extensive list of things I don't eat.)

    What obviously didn't go wrong at all was the social interaction between all of the attendees and speakers. It probably helped that there were a few attendees from some organisations and that people from different organisations had worked with each other in the past but it's a *long* time since I've felt such a vibrant energy during a break.

    Attendees enjoying pizza and conversation

    I was on next, presenting on "Real Time SQL Monitoring" and apart from a few hiccups with the clicker I borrowed from Bjoern and a couple of slide corrections I need to make, I think it went reasonably well and people seemed as enthused by SQL Mon reports as I've come to expect! With that done, and a quick smoke (I *love* organising an agenda :-)), it was time for Morten with his "Big Data Primer" 

    Morten doing his thing

    I think this might have been lots of peoples favourite presentation because it wasn't just about Oracle and Morten packed in plenty of the humour I've come to expect from him. Better still, it seemed to work for a quite cosmopolitan audience, so good work!

    Afterwards he said a few words asking for people's feedback and whether there was a desire to setup a local user group or just continue with these informal sessions (sponsors permitting) and all of the feedback I heard later showed that people are very keen for a repeat run. 

    Overall, Monday night felt like a great success. 

    The passion and enthusiasm of the attendees was very encouraging and reflected in the subsequent feedback which has been consistently positive but also thoughtful so far. There's no question that a decent minority of the local Oracle community are looking for regular opportunities to hear decent speakers on subjects that interest them, meet and discuss issues with each other and also offer to present themselves, which is a great start for any Oracle User Group.

    Strangely, I discovered a day or so later that there are already plans for a User Group and the Singapore launch event is next Wednesday. Coincidentally this is only 9 days after SOS! You can look into the APOUG website here and a number of colleagues and I will attend the launch event. I suppose it's a small shame that it's an APAC-wide user group, rather than specific to Singapore, which the number of attendees at such short notice would suggest Singapore can justify, but I'll be interested to see what APOUG has planned.

    Big thanks for Alvin from Oracle for endless supplies of fine pizza and Bjoern Rost of Portrix Systems for the room hire (I bought the drinks, which some would say was appropriate but I couldn't possibly comment) and thanks again to all the attendees for making it a fun night!

    I didn't notice until I was about to post this that Bjoern had already blogged about the evening and I think he's captured it perfectly.

    Securing Oracle GoldenGate Database Login in a Credential Store

    DBASolved - Fri, 2014-11-07 17:44

    Security is always a big deal.  In setting up Oracle GoldenGate the capture (extract) and apply (replicat) parameter files need to be configured to log in to the database which they will perform operations.  In order to do this the Oracle GoldenGate User name and password need to be provided in the parameter files.  Example 1 shows how the database login is traditionally done in a extract or replicat parameter file.

    Example 1:

    --Oracle Login
    USERID ggate, PASSWORD ggate

    To make this process login information more secure, we can create a userid alias that the extract or replicat process can use to log into the database.  In order to create a login alias, a credential store needs to be create.  Below are the steps to create the credential store and associated aliases.

    After logging into the GoldenGate Service Command Interface (GGSCI), a credential store needs to be created.  By default the credential store will be kept in the “dircrd” directory undert the $OGG_HOME.

    Create the credential store:

    GGSCI ( 1> add credentialstore
    Credential store created in ./dircrd/.

    With the credential store created, now an alias can be created for the gguser.

    GGSCI ( 2> alter credentialstore add user ggate, password ggate alias aggate
    Credential store in ./dircrd/ altered.

    The extract or replicat parameter files need to be updated to use the new alias.  Once the update is done the associated process needs to be restarted.

    --Oracle Login
    USERIDALIAS aggate

    After restarting the process, the Oracle GoldenGate login is secure.

    Note: If the password for the Oracle GoldenGate User changes, the alias in the credential store will need to be updated.


    Filed under: Golden Gate
    Categories: DBA Blogs

    Partner Webcast – Oracle R Enterprise: Bringing the power of R to the enterprise

    For those of you just getting interested in Big “Data” and/or “Advanced Analytics”, the addition of R through Oracle R Enterprise could leave them wondering "What is R?" R is an...

    We share our skills to maximize your revenue!
    Categories: DBA Blogs

    SQL Translation Framework in Oracle Database 12c

    One of the new Oracle Database 12c features is the SQL Translation Framework. A key part of migrating non-Oracle databases to Oracle Database involves the conversion of non-Oracle SQL statements...

    We share our skills to maximize your revenue!
    Categories: DBA Blogs

    Oracle SQL Profiles: Check what they do before accepting them blindly

    Yann Neuhaus - Fri, 2014-11-07 16:28

    People often ask me if I use the SQL Tuning Advisor. The answer is no, at least not through the end of it. But don't take me wrong. SQL Tuning Advisor is a really nice feature. If you like the CBO then you must love the Tuning Advisor, because it is just the CBO with more time to optimize, and more ways to optimize. The fact is that when a customer calls me, they expect a bit more than running an advisor and implement the result. I'm there to fix the root cause, not just workaround some missing stats. And this is why I use it very rarely.

    However when I have a big query, with a plan that covers several pages, it takes a lot of time to find what is wrong. The method is often based on comparing the estimated cardinalities with the actual ones. But If I have access to the Tuning Pack, then the SQL Tuning Advisor can help to find very quickly where the estimations are going wrong.

    The SQL Tuning Advisor proposes a SQL Profile to adjust the estimations. Then I just have to check the biggest adjustment and I can focus where the estimations are wrong. However, that information is not exposed. The SQL Tuning Advisor report shows the new plan, but not the ways it gets to it.

    The goal of this post is to give you the query I use to show exactly what the profile will implement when you accept it.

    Even if you're going to accept the profile, It's a good idea to check it before. It will help to choose if you need to enable 'force matching' or not. And if it is a good fix or if there are more sustainable ways to achieve the same.

    You probably know that a profile implements the estimation adjustment with the OPT_ESTIMATE hints which adjust it with a 'scale_rows' factor that can apply to tables, index selectivity or joins. They is very well explained on the Pythian blog

    So, let's take an exemple. My favorite query to show bad estimations on the HR schema is:

    alter session set current_schema=HR;
    select distinct DEPARTMENT_NAME
     where DEPARTMENT_NAME like '%ing' and SALARY>20000;
    For the simple reason that I know that lot of the department names are ending with 'ing' (Marketing, Purchasing,...) but the CBO doesn't know that. And I can't give that information through column statistics or histograms:
    SQL> select * from table( dbms_xplan.display_cursor(format=>'rowstats last ') );
    SQL_ID  4fz1vtn0w8aak, child number 0
    select distinct DEPARTMENT_NAME  from DEPARTMENTS join EMPLOYEES
    using(DEPARTMENT_ID)  where DEPARTMENT_NAME like '%ing' and SALARY>20000
    Plan hash value: 3041748347
    | Id  | Operation                             | Name              | Starts | E-Rows | A-Rows |
    |   0 | SELECT STATEMENT                      |                   |      1 |        |      0 |
    |   1 |  HASH UNIQUE                          |                   |      1 |      1 |      0 |
    |   2 |   NESTED LOOPS SEMI                   |                   |      1 |      1 |      0 |
    |*  3 |    TABLE ACCESS FULL                  | DEPARTMENTS       |      1 |      1 |      7 |
    |*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |      7 |      1 |      0 |
    |*  5 |     INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |      7 |     10 |     55 |
    Predicate Information (identified by operation id):
       3 - filter("DEPARTMENTS"."DEPARTMENT_NAME" LIKE '%ing')
       4 - filter("EMPLOYEES"."SALARY">20000)
       - this is an adaptive plan
    Look at E-Rows and A-Rows: I have 7 departments ending with 'ing' but the optimizer thinks that there is only one. In 11g you need a profile to help the optimizer or you're going into an expensive nested loop. This example has few rows, but imagine the consequence when a nested loop is choosen but must execute on millon of rows. In 12c - and if you are in Enterprise Edition - the adaptive plan will help to avoid that situation. As soon as a threshold is reached another plan will be executed.

    But even with adaptive plan, there may be a better plan that is possible only with accurate estimations. Let's see what the SQL Tuning Advisor will find.

    Running SQL Tuning Advisor

    I create and execute the tuning task:

     dbms_output.put_line('task id: '||
      description=>'dbi InSite workshop Oracle Tuning',

    And show the report:

    SQL> set long 1000000 longc 1000000
    SQL> select dbms_sqltune.report_tuning_task('dbiInSite') FROM dual;
    Tuning Task Name   : dbiInSite
    Tuning Task Owner  : SYS
    Workload Type      : Single SQL Statement
    Scope              : COMPREHENSIVE
    Time Limit(seconds): 30
    Completion Status  : COMPLETED
    Started at         : 11/08/2014 00:03:22
    Completed at       : 11/08/2014 00:03:23
    Schema Name: HR
    SQL ID     : 4fz1vtn0w8aak
    SQL Text   : select distinct DEPARTMENT_NAME
                  from DEPARTMENTS join EMPLOYEES using(DEPARTMENT_ID)
                  where DEPARTMENT_NAME like '%ing' and SALARY>20000
    FINDINGS SECTION (1 finding)
    1- SQL Profile Finding (see explain plans section below)
      A potentially better execution plan was found for this statement.
      Recommendation (estimated benefit: 33.67%)
      - Consider accepting the recommended SQL profile.
        execute dbms_sqltune.accept_sql_profile(task_name => 'dbiInSite',
                task_owner => 'SYS', replace => TRUE);
      Validation results
      The SQL profile was tested by executing both its plan and the original plan
      and measuring their respective execution statistics. A plan may have been
      only partially executed if the other could be run to completion in less time.
                               Original Plan  With SQL Profile  % Improved
                               -------------  ----------------  ----------
      Completion Status:            COMPLETE          COMPLETE
      Elapsed Time (s):             .005964           .000177      97.03 %
      CPU Time (s):                 .005999             .0002      96.66 %
      User I/O Time (s):                  0                 0
      Buffer Gets:                       13                 9      30.76 %
      Physical Read Requests:             0                 0
      Physical Write Requests:            0                 0
      Physical Read Bytes:                0                 0
      Physical Write Bytes:               0                 0
      Rows Processed:                     0                 0
      Fetches:                            0                 0
      Executions:                         1                 1
      1. Statistics for the original plan were averaged over 10 executions.
      2. Statistics for the SQL profile plan were averaged over 10 executions.
    1- Original With Adjusted Cost
    Plan hash value: 3041748347
    | Id  | Operation                             | Name              | Rows  | Bytes | Cost (%CPU)| 
    |   0 | SELECT STATEMENT                      |                   |     1 |    23 |     7  (15)| 
    |   1 |  HASH UNIQUE                          |                   |     1 |    23 |     7  (15)| 
    |   2 |   NESTED LOOPS SEMI                   |                   |     1 |    23 |     6   (0)| 
    |*  3 |    TABLE ACCESS FULL                  | DEPARTMENTS       |     7 |   112 |     3   (0)| 
    |*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES         |     1 |     7 |     1   (0)| 
    |*  5 |     INDEX RANGE SCAN                  | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 
    Predicate Information (identified by operation id):
       3 - filter("DEPARTMENTS"."DEPARTMENT_NAME" LIKE '%ing')
       4 - filter("EMPLOYEES"."SALARY">20000)
    2- Using SQL Profile
    Plan hash value: 2473492969
    | Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    |   0 | SELECT STATEMENT              |             |     1 |    23 |     5  (20)| 00:00:01 |
    |   1 |  HASH UNIQUE                  |             |     1 |    23 |     5  (20)| 00:00:01 |
    |   2 |   NESTED LOOPS                |             |     1 |    23 |     4   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS               |             |     1 |    23 |     4   (0)| 00:00:01 |
    |*  4 |     TABLE ACCESS FULL         | EMPLOYEES   |     1 |     7 |     3   (0)| 00:00:01 |
    |*  5 |     INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
    |*  6 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
    Predicate Information (identified by operation id):
       4 - filter("EMPLOYEES"."SALARY">20000)
       6 - filter("DEPARTMENTS"."DEPARTMENT_NAME" LIKE '%ing')

    So even if I have adaptive plan, a better plan is possible. When the optimizer know that there are more rows from DEPARTMENTS than EMPLOYEES, it's better to start with EMPLOYEES, for nested loop as well as for hash join.

    Showing the OPT_ESTIMATE hints

    Then I don't want to accept the profile yet, but want to see all those OPT_ESTIMATE hints that has been determined by the SQL Tuning Advisor and that will be added to the query when the profile is accepted. Here my script. Note that this script is for 11g and 12c. In 10g the information was stored elsewhere. You can go to Jonathan Levis post for the 10g query.

    set serveroutput on echo off
      -- input variables
      input_task_owner dba_advisor_tasks.owner%type:='SYS';
      input_task_name dba_advisor_tasks.task_name%type:='dbiInSite';
      input_show_outline boolean:=false;
      -- local variables
      task_id  dba_advisor_tasks.task_id%type;
      outline_data xmltype;
      benefit number;
      for o in ( select * from dba_advisor_objects where owner=input_task_owner and task_name=input_task_name and type='SQL')
              -- get the profile hints (opt_estimate)
              dbms_output.put_line('--- PROFILE HINTS from '||o.task_name||' ('||o.object_id||') statement '||o.attr1||':');
              for r in (
                select hint,benefit from (
                 select case when attr5 like 'OPT_ESTIMATE%' then cast(attr5 as varchar2(4000)) when attr1 like 'OPT_ESTIMATE%' then attr1 end hint,benefit
                 from dba_advisor_recommendations t join dba_advisor_rationale r using (task_id,rec_id)
                 where t.owner=o.owner and t.task_name = o.task_name and r.object_id=o.object_id and t.type='SQL PROFILE'
                 and r.message='This attribute adjusts optimizer estimates.'
                ) order by to_number(regexp_replace(hint,'^.*=([0-9.]+)[^0-9].*$','\1'))
              ) loop
               dbms_output.put_line('   '||r.hint); benefit:=to_number(r.benefit)/100;
              end loop;
              -- get the outline hints
              select outline_data into outline_data from (
                  select case when other_xml is not null then extract(xmltype(other_xml),'/*/outline_data/hint') end outline_data
                  from dba_advisor_tasks t join dba_sqltune_plans p using (task_id)
                  where t.owner=o.owner and t.task_name = o.task_name and p.object_id=o.object_id  and t.advisor_name='SQL Tuning Advisor' --11gonly-- and execution_type='TUNE SQL'
                  and p.attribute='Using SQL profile'
              ) where outline_data is not null;
              exception when no_data_found then null;
              exit when not input_show_outline;
              dbms_output.put_line('--- OUTLINE HINTS from '||o.task_name||' ('||o.object_id||') statement '||o.attr1||':');
              for r in (
                  select (extractvalue(value(d), '/hint')) hint from table(xmlsequence(extract( outline_data , '/'))) d
              ) loop
               dbms_output.put_line('   '||r.hint);
              end loop;
              dbms_output.put_line('--- Benefit: '||to_char(to_number(benefit),'FM99.99')||'%');
      end loop;

    And here is the output:

    --- PROFILE HINTS from dbiInSite (1) statement 4fz1vtn0w8aak:
    PL/SQL procedure successfully completed.

    This is very interesting information. It says that the actual number of employees in this query (with that specific where clause) is 2 times what is estimated from statistics. And that the estimated number of departments is 5 times what is estimated.

    So what it is used for?

    That gives me several ways to improve, even without implementing the profile.

    First, the number of employees for a 'SALARY>2000' predicate can be improved with histograms. That is better than a profile because it will improve all queries that filter on employee salary.

    Then, for DEPARTMENTS, histograms will not help because the bad estimation comes from the LIKE '%ing' predicate and I've no way to give that information with statistics. Ok, let's go for the profile. If you want to implement the profile, will you choose 'force matching' or not? Of course not. The estimation adjustment makes sense only with our '%ing' values. This is were looking at the OPT_ESTIMATE is very important, or you can't do the right choice.

    And you may choose something else than profile. Dynamic sampling makes sense in this case. Another solution may be to add OPT_ESTIMATE or CARDINALITY hints in the query.

    Conclusion SQL Tuning Advisor is powerful, when used intelligently. It gives ideas about what is wrong and proposes a way to fix it. But you can have more when retrieving the internal hints that the profile generate. Better choice to implement the profile, or alternative solutions. As usual, if you see something wrong or to improve in my query, please comment. Quizz

    The answer is left for another post, but if you have an idea, please don't hesitate to comment.
    Question is: In the above output, I've two OPT_ESTIMATE rows for each table. what is the reason for that?

    Emphasis on Practical Usability Research at HFES Annual Meeting in Chicago

    Usable Apps - Fri, 2014-11-07 15:16

    By Anna Wichansky

    Senior Director, Oracle Applications User Experience

    HFES 2014 Meeting in Chicago

    The 2014 International Annual Meeting of the Human Factors and Ergonomics Society (HFES) was recently held in Chicago, on October 27-31, 2014. This conference deals with all the latest research and issues in the field of human factors, the study of human-machine systems. Some 1450 professionals in human factors, user experience (UX), and related fields attended the event.

    Anna Wichansky and Ultan O’Broin (@usableapps) of the Oracle Applications User Experience (OAUX) team presented a half-day workshop on How to Create User Requirements for Software to HFES members and students, including industry consultants and end-user customers. This is their third presentation of the workshop, which features a highly interactive format with small groups engaged in hands-on role-playing exercises.

    In this unique workshop, students prepared a business case about a fictitious auto parts company requiring a financial software upgrade to a more efficient, effective, and satisfying application. They worked in small groups and played the roles of UX consultant, user, and stakeholders in the implementation. Ultan and Anna facilitated the groups, played stakeholder roles as needed, and presented relevant concepts and methods for setting UX requirements based on the NIST IR 7432 Common Industry Format for Requirements. Students left with a completed template of user requirements for the workshop business case.

     The fundamental layer of user requirements. Picture of people coding together

    Understanding the context of use (the who, what, where, how, and with whom) dimension of software user requirements gathering is fundamental to a successful implementation. The HFES workshop explored context of use thoroughly as an underlying layer of the Common Industry Format for Usability Requirements.

    In other highlights of the conference, Deborah Hersman, President and CEO of the U.S. National Safety Council and former head of the National Transportation Safety Board, gave an invited speech on the importance of human factors in promoting safety. One particular theme was computer-distracted operators of transportation vehicles. She related examples of the Northwest Airlines pilots who overflew their destination while reading rosters on a laptop, a texting engineer responsible for a train collision in Chatsworth, California, and the Delaware River tug boat mate in charge of towing a barge that collided with another vessel because he was distracted by his cell phone. Her clear message is that we need to use technology thoughtfully to ensure the benefits outweigh any detrimental effects. Automated cars, for example, could have many benefits in providing a very safe ride, possibly decreasing the effects of driver distraction, fatigue, and aging on highway accidents.

    The fastest growing technical group in HFES is Healthcare, with many papers and sessions presented on testing medical devices, the design and human factors of electronic medical records, and online consumer information systems for patient compliance and support.

    A symposium on research being conducted to support the NASA manned extra-planetary missions was also presented, with many relevant findings for life here on Earth, including the effects of sleep deprivation and sleep inertia (when you are suddenly awakened in the middle of sleep) on human performance.

    BMW presented research on the optimal design for augmented displays in automated driving scenarios. The research found that drivers’ reactions to the displayed alerts and warnings as they attempted to avoid hazards in simulated driving tasks were often unpredictable, depending on features of the visual design.

    About the Human Factors and Ergonomics Society 

    Human Factors and Ergonomics Society logo

    The Human Factors and Ergonomics Society is a 4500-member professional organization dedicated to the study of human-machine systems. Anna Wichansky is a Fellow of the HFES.

    Related Information

    Dropbox Database Infiltrated by Hackers [VIDEO]

    Chris Foot - Fri, 2014-11-07 12:57


    While providing database security services to cloud storage providers is possible, many such companies aren't taking the necessary precautions to ensure customer data remains protected. 

    According to 9 to 5 Mac, Dropbox recently announced that a database holding 7 million logins for its users was infiltrated. The environment was operated by a third party, which was hired by Dropbox to store its customer data. To the company's relief, many of the stolen passwords were outdated. 

    While Dropbox is taking steps to mitigate the situation, the enterprise advised its customers to change their login information as an extra precaution. 

    The best way to prevent breaches from occurring is to install automated intrusion detection software. In addition, regularly auditing existing systems for vulnerabilities is considered a best practice. 

    Thanks for watching! 

    The post Dropbox Database Infiltrated by Hackers [VIDEO] appeared first on Remote DBA Experts.

    Quiz night

    Jonathan Lewis - Fri, 2014-11-07 12:37

    Prompted by an email from Yves Colin (who’ll be presenting on the Tuesday of UKOUG Tech14) I was prompted to dig out a little script I wrote some years ago and re-run an old test, leading to this simple question:  what’s the largest size array insert that Oracle will handle ?

    If you’re tempted to answer, watch out – it’s not exactly a trick question, but there is a bit of a catch.


    There is an internal limit of 255 on the size of array that Oracle can insert as a unit. I leave it as an exercise to the reader to decide whether or not this makes any significant difference to performance, since the effects of row size, number of indexes maintained, and disk I/O requirements may make the effects of the limit virtually undetectable. To demonstrate the limit all we need do is insert a few hundred rows into a table and do a block dump, optionally followed by a dump of the redo log.

    create table t1 (n1 number not null) segment creation immediate;
    alter system switch logfile;
    insert into t1 select rownum from all_objects where rownum <= 1000;
    execute dump_log
    start rowid_count t1
    ---------- ---------- -------------
             5        180           660
             5        184           340
    2 rows selected.
    ------------- ----------
              340          1
              660          1
    2 rows selected.
    alter system flush buffer_cache;
    -- SQL> alter system dump datafile 5 block 180;

    The dump_log procedure is simply a pl/sql wrapper for a call to ‘alter system dump logfile {current log}’; I the script rowid_count.sql extracts the file and block numbers from rowids in the given table and aggregates them in different ways.  The reason for running the script is to find a table block with a lot of rows in it; the block I dumped actually held the first 660 rows of the insert. Here’s a tiny extract from the block dump (with one little comment added):

    tab 0, row 0, @0x1904			-- 6,400 dec
    tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
    col  0: [ 2]  c1 02
    tab 0, row 1, @0x190a
    tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
    col  0: [ 2]  c1 03
    tab 0, row 2, @0x1910
    tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
    col  0: [ 2]  c1 04
    tab 0, row 3, @0x1916
    tl: 6 fb: --H-FL-- lb: 0x1  cc: 1

    This shows the first four rows inserted, and you can see that the offset to the first row is 6,400 bytes, and then each subsequent row is 6 bytes further down the block (when the numbers get larger the row lengths will increase to 7 bytes). The positioning of these rows is, at first sight, a little odd – you might have guessed that they would either start at offset “zero” and work down to offset “8K”, or start at “8K” and work backwards up to zero – why is the first row 3/4 of the way down the block ?

    Rather than show you more row dumps, I’ll give you a carefully selected extract from the row directory:

    0x12:pri[0]     offs=0x1904
    0x14:pri[1]     offs=0x190a
    0x20c:pri[253]  offs=0x1f8a
    0x20e:pri[254]  offs=0x1f91		End of first 255 rows
    0x210:pri[255]  offs=0x120e		Start of second 255 rows
    0x212:pri[256]  offs=0x1215
    0x40a:pri[508]  offs=0x18f6
    0x40c:pri[509]  offs=0x18fd		End of second 255 rows
    0x40e:pri[510]  offs=0xdf5		Start of last 150 rows
    0x410:pri[511]  offs=0xdfc
    0x536:pri[658]  offs=0x1200		End of last 150 rows
    0x538:pri[659]  offs=0x1207

    The first 255 rows inserted are stacked at the bottom of the block at offsets 0×1904 to 0x1f91.
    The second 255 rows inserted are stacked above them at offsets 0x120e to 0x18fd (note 0x18fd + 6 = 0×1903)
    The last 150 rows inserted are stack above them at offsets 0xdf5 to 0×1207 (note 0×1207 + 6 = 0x120d)

    No matter how large your attempted array insert, the maximum number of rows (or index entries) Oracle can insert into a block in a single internal array operation is 255.

    Further corroboration comes from the redo log dump – here’s a tiny bit of a single change vector (i.e. a single atomic change to a single Oracle block) from the redo generated while this insert was going on:

    CHANGE #18 CON_ID:0 TYP:0 CLS:1 AFN:5 DBA:0x014000c7 OBJ:95876 SCN:0x0000.007528dd SEQ:1 OP:11.11 ENC:0 RBL:0 FLG:0x0000
    KTB Redo
    op: 0x01  ver: 0x01
    compat bit: 4 (post-11) padding: 1
    op: F  xid:  0x0007.007.000009f2    uba: 0x01000bda.02a9.14
    KDO Op code: QMI row dependencies Disabled
      xtype: XA flags: 0x00000000  bdba: 0x014000c7  hdba: 0x01400083
    itli: 1  ispac: 0  maxfr: 4858
    tabn: 0 lock: 1 nrow: 255
    slot[0]: 0
    tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
    col  0: [ 2]  c1 02
    slot[1]: 1
    tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
    col  0: [ 2]  c1 03
    slot[2]: 2
    tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
    col  0: [ 2]  c1 04

    Although the CON_ID in the line 1 tells you this dump is from 12c the same limit holds across all (modern) versions of Oracle. The operation (OP 11.11 – in line 1) is “Insert Multiple Rows”, and at line 9 you can see: “nrow: 255″. I haven’t included the rest of the change vector, but all it does is show the remaining 252 rows.