Feed aggregator

How does Oracle know whether the index belongs to the primary key?

Tom Kyte - Thu, 2017-02-09 20:46
Consider the following two tables and their primary keys: <code> create table testuser.test1 ( col1 number not null, col2 number not null, col3 number not null ); alter table testuser.test1 add constraint test1_pk primary key ...
Categories: DBA Blogs

Query on time overlaps

Tom Kyte - Thu, 2017-02-09 20:46
I am struggling to merge continuous time ranges to one.Here is my record set. STAFF_NUMBER SHIFT_DATE TASK_START_TIME TASK_END_TIME 123 12/10/2016 12/10/2016 17:14 12/10/2016 20:10 123 12/10/2016 12/10/2016 20:08 12/10/2016 21:08 1...
Categories: DBA Blogs

Update query taking long time to execute

Tom Kyte - Thu, 2017-02-09 20:46
<code>dear tom, i am using a query that update a column of one table. But the query is very costly and taking huge time to complete. though i am using it parallel. The query is: UPDATE LOANACNTS l set l.LNACNT_RTMP_LAST_DATE=(SELECT /*+paralle...
Categories: DBA Blogs

Produce matrix result as table

Tom Kyte - Thu, 2017-02-09 20:46
Is it possible to create table like structure with sql what i want is display output in matrix format like if i have 3 rows and 6 columns then it should provide desire output for same. below are the scripts: create table t1 (id number, name varchar...
Categories: DBA Blogs

Shuffle values randomly in columns

Tom Kyte - Thu, 2017-02-09 20:46
Note - this is more of an academic question as I have a resolution, I am just keen to see whether my alternative approach is possible. I have a HR table with a list of names. For demo purposes I needed to shuffle these around to make them a bit mo...
Categories: DBA Blogs

WITH's work, but join on WITH's runs forever. Is there a way to force WITH tables to fully materialize perhaps?

Tom Kyte - Thu, 2017-02-09 20:46
with a as (), b as (), c as (), d as () select count(*) from a 138 select count(*) from c 138 select * from a left join c on a.val=c.val -this never finishes, runs forever Is there a way to add a hint to force it to work? My 2 ...
Categories: DBA Blogs


Tom Kyte - Thu, 2017-02-09 20:46
I have a clob column in a table and the inserts to this table is taking hours. How can I speedup the insert. Please suggest asap.
Categories: DBA Blogs


Tom Kyte - Thu, 2017-02-09 20:46
<code>Hello folks - Here's my database & server info: Oracle Database 12c Enterprise Edition Release - 64bit Production PL/SQL Release - Production CORE Production ...
Categories: DBA Blogs

Dataguard vs Shareplex for DR

Tom Kyte - Thu, 2017-02-09 20:46
Hello Tom, I am looking for an advise on the DR setup. I am with a company where the design of the database and DR setup is done by the vendor and they are using shareplex instead of dataguard. I am having hard time convincing the management to c...
Categories: DBA Blogs

Fixing blank charts on ambari home page (Hortonworks Data Platform)

Jeff Moss - Thu, 2017-02-09 15:54

I created a 4 node container based (Proxmox LXC) Hortonworks Data Platform 2.5 Hadoop cluster recently and all went well apart from all the charts on the Ambari homepage were blank or showing “N/A”, like this:

An outline of the environment:

  • 4 node cluster of LXC containers on Proxmox host
  • Centos 7 Linux OS
  • Nodes are called bishdp0[1-4], all created from same template and identical configuration
  • All containers are on network
  • DNS Server also available on same network and all hosts can resolve each other via DNS
  • Hortonworks Data Platform version 2.5
  • Proxmox host sits on a corporate network and the host has iptables set to allow the containers on to reach the internet via the corporate proxy server, e.g. for yum access
  • Other than the blank charts everything appears to be working fine

After much reading around it turns out that I hadn’t quite set up the proxy serving correctly, specifically that I hadn’t told Ambari to ignore some hosts, namely the bishdp0[1-4] hosts on the network, when proxying. I can’t find a 2.5 HDP version of the document for setting up the proxy serving for Ambari but the 2.2 instructions worked.

Steps I took to fix the problem:

First stop the services on the cluster. Log on to the node with the Ambari Server where I have a script called ambari-stop-all-services.sh which I created based on part of this article. Thanks slm.

Run the script:


Now stop the Ambari agent on all the servers:

pdsh -w bishdp0[1-4] service ambari-agent stop

Now stop the Ambari Server:

service ambari-server stop

Now edit the Ambari environment script:

vi /var/lib/ambari-server/ambari-env.sh

Look for the line that begins “export AMBARI_JVM_ARGS” and ensure it has entries for the following parameters:

  • http.proxyHost
  • http.proxyPort
  • http.proxyUser
  • http.proxyPassword
  • http.nonProxyHosts

It’s the last one that was missing in my case, which meant that Ambari was trying to go to the proxy server even for these containers on the network.

After editing, the line looked like this (I’ve redacted the specifics – just replace the entries with values suited to your environment):

export AMBARI_JVM_ARGS=$AMBARI_JVM_ARGS’ -Xms512m -Xmx2048m -XX:MaxPermSize=128m -Dhttp.proxyHost=<proxy IP> -Dhttp.proxyPort=<proxy port> -Dhttp.proxyUser=<user> -Dhttp.proxyPassword=<password> -Dhttp.nonProxyHosts=<*.domain> -Djava.security.auth.login.config=$ROOT/etc/ambari-server/conf/krb5JAASLogin.conf -Djava.security.krb5.conf=/etc/krb5.conf -Djavax.security.auth.useSubjectCredsOnly=false’

Now restart everything, Ambari server first:

service ambari-server start

…then the agents on all nodes (pdsh is great – thanks Robin Moffatt for your notes!)

pdsh -w bishdp0[1-4] service ambari-agent start

And finally start the services on the cluster using the ambari-start-all-services.sh script.


After I did this, the charts started showing details:

Alliance 2017

Jim Marion - Thu, 2017-02-09 14:07

We are in the final countdown for Alliance 2017. I am really excited about this conference. It is such a great opportunity to meet up with old friends as well as make new ones. The Alliance session content and delivery is extremely high caliber. HEUG is a very engaged community. The MGM is a pretty amazing facility as well.

At GreyHeller our week starts with an amazing Monday workshop. On Monday, February 27th from 10:00 AM to 2:30 PM, Larry Grey and I will be hosting a pre-conference workshop titled Advanced PeopleTools Development Workshop with Jim Marion (session 4378). Our objective is to give you hands on experience with all of the new PeopleTools including Fluid and the Event Mapping Framework. But wait, there's more... Fluid itself is a new development paradigm with a lot of flexibility. In this session you will learn how to use CSS and JavaScript to further enhance the PeopleSoft user experience. For more details and registration, visit the Monday Workshops page on the Alliance conference site.

On Tuesday morning at we join our partner MODO LABS at 8:30 AM to present the session A Student for Life - Engaging prospective, new, current, and past students has never been easier. In this session you will see how MODO LABS partnered with GreyHeller makes it trivial to embed PeopleSoft content in a native, secure user experience giving users access to native, on-device capabilities such as maps, notifications, etc.

On Tuesday, February 28th from 09:45 AM to 10:45 AM, our friends from the University of Massachusetts will be sharing about their experience mobilizing and modernizing the Student Center (session 4036) at their UMass Boston, Dartmouth and Lowell campuses using our PeopleMobile™ product. It really is amazing how our product transforms the PeopleSoft user experience. Definitely a "must see."

On Tuesday, February 28th from 1:15 PM to 3:15 PM, Larry and I will be leading the PeopleSoft Cloud to Ground workshop – Cloud Adoption Strategies and Best Practices (session 4381). In the ERP space, Hybrid "is the new black." There are a lot of great cloud enhancements to a traditional ERP. Anyone thinking about implementing cloud is also thinking about backend data integrations. But what about the user experience? You don't have to settle for a disjointed user experience. In this session, Larry and I will show you how your organization can integrate the UX layer into a single, common user experience.

On Thursday, March 2nd at 9:15 AM, my friend Felicia Kendall from UCF will be sharing about their highly publicized breach (including costs) and their experiences with securing PeopleSoft after a highly publicized breach. This should prove to be a very valuable session. The session is titled University of Central Florida: Post-breach Mitigation & Prevention Strategy (session 4108).

While attending Alliance, be sure to wander through the demo grounds. Our booth (#301) will be right beside the Oracle booth. I'm looking forward to wandering through and visiting with my friends from Oracle, Ciber, Deloitte, Gideon Taylor, Intrasee, Smart ERP, Accenture, Presence of IT, MODO LABS, Huron, Sierra-Cedar, and many more.

See you on the floor!

PeopleTools CPU analysis and supported versions of PeopleTools (update for Jan 2017 CPU)

PeopleSoft Technology Blog - Thu, 2017-02-09 10:30

Questions often arise on the PeopleTools versions for whichCritical Patch Updates have been published, or if a particular PeopleToolsversion is supported. 

The table in the attached page shows the patch number matrix for PeopleToolsversions associated with a particular CPU publication. This information will help you decide which CPU to apply and when to consider upgrading to a more current release.

The link in "CPU Date" goes to the landing page forCPU advisories, the link in the individual date, e.g. Apr-10, goes to theadvisory for that date.

The page also shows the CVE's addressed in the CPU, a synopsisof the issue and the Common Vulnerability Scoring System (CVSS) value.

 Introduced in the April 2016 CPU, CVSS 3.0 is being used from this CPU going forward.

 For more information on how CVSS 3.0 is calculated, Risk Matrix Glossary – Terms and Definitions for Critical Patch Update Risk Matrices http://www.oracle.com/technetwork/topics/security/advisorymatrixglossary-101807.html

CVSS Version 3.0 Announced: https://blogs.oracle.com/security/entry/cvss_version_3_0_announced

To find more details on any CVE, simply replace the CVE numberin the sample URL below.: http://www.cvedetails.com/cve/CVE-2010-2377

Common Vulnerability Scoring System Version 3 Calculator: https://www.first.org/cvss/specification-document

This page shows the components of the CVSS score

If you are considering creating a response policy, this page provide a good sample template: http://www.first.org/_assets/cvss/cvss-based-patch-policy.pdf. Note this is a useful template.

All the details in this page are available on My Oracle Support and public sites.

Please NOTE: The RED column in the Attached Page indicates the last patch for any PeopleToolsversion and effectively the last support date for that version.

Applications Unlimited support does NOT apply to PeopleToolsversions.


Linux – Securing your important files with XFS extendend attributes

Yann Neuhaus - Thu, 2017-02-09 09:19

Let’s say, the tnsnames.ora is a quite important file on your system, and you want to make sure that you notice when someone changes the file. Taking a look at the modification time of that file would be good idea, or not?

Per default, the ls -l command show only the (mtime) modification time. In my case, I know that the tnsnames.ora was changed on “Feb 9 11:24″.

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] ls -l tnsnames.ora
-rw-r--r-- 1 oracle oinstall 1791 Feb  9 11:24 tnsnames.ora

But in reality, more time stamps are stored. The atime, the ctime and the mtime.

  • atime is the access time (only stored in filesystem is not mounted with the noatime option)
  • ctime is the change time, meaning the inode was change, e.g. with the chmod command
  • mtime is the modification time, meaning the content changed

The ctime is often misinterpreted as “creation time”, but this is not the case. The creation time of a file is not recorded with XFS. There are other file systems that can do it, like ZFS, but XFS does not support “creation time”. You can use the stat command to see all time stamps in one shot.

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] stat tnsnames.ora
  File: ‘tnsnames.ora’
  Size: 2137            Blocks: 8          IO Block: 4096   regular file
Device: fb02h/64258d    Inode: 163094097   Links: 1
Access: (0644/-rw-r--r--)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Access: 2017-02-09 11:24:00.243281419 +0100
Modify: 2017-02-09 11:24:00.243281419 +0100
Change: 2017-02-09 11:24:00.254281404 +0100
 Birth: -

Ok. Now someone comes along and changes the tnsnames.ora

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] vi tnsnames.ora

A change was done, and the modification time of that file changed immediately.

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] ls -l tnsnames.ora
-rw-r--r-- 1 oracle oinstall 2136 Feb  9 11:31 tnsnames.ora

And also other timestamps might have changed like the atime and ctime.

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] stat tnsnames.ora
  File: ‘tnsnames.ora’
  Size: 2136            Blocks: 8          IO Block: 4096   regular file
Device: fb02h/64258d    Inode: 161521017   Links: 1
Access: (0644/-rw-r--r--)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Access: 2017-02-09 11:31:06.733673663 +0100
Modify: 2017-02-09 11:31:06.733673663 +0100
Change: 2017-02-09 11:31:06.738673656 +0100
 Birth: -

Cool, now I know that the file was changed at “Feb 9 11:31″. But how reliable is that information? With the touch command, I can easily change the modification time to any value I like. e.g. I can set it to the same date as beforehand.

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] touch -m --date="Feb  9 11:24" tnsnames.ora

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] ls -l tnsnames.ora
-rw-r--r-- 1 oracle oinstall 2136 Feb  9 11:24 tnsnames.ora

Now I have set the modification time to almost the same value, as it was beforehand. (Almost, because the microseconds are different) Besides that, the access and the change time are different.

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] stat tnsnames.ora
  File: ‘tnsnames.ora’
  Size: 2136            Blocks: 8          IO Block: 4096   regular file
Device: fb02h/64258d    Inode: 161521017   Links: 1
Access: (0644/-rw-r--r--)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Access: 2017-02-09 11:31:06.733673663 +0100
Modify: 2017-02-09 11:24:00.000000000 +0100
Change: 2017-02-09 11:36:51.631671612 +0100
 Birth: -

No problem, I can make it even more precise by specifying  the whole date format including microseconds and time zone.

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] touch -m --date="2017-02-09 11:24:00.243281419 +0100" tnsnames.ora

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] stat tnsnames.ora
  File: ‘tnsnames.ora’
  Size: 2136            Blocks: 8          IO Block: 4096   regular file
Device: fb02h/64258d    Inode: 161521017   Links: 1
Access: (0644/-rw-r--r--)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Access: 2017-02-09 11:31:06.733673663 +0100
Modify: 2017-02-09 11:24:00.243281419 +0100
Change: 2017-02-09 11:39:41.775993054 +0100
 Birth: -

And if I want to, I can even change the access time.

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] touch -a --date="2017-02-09 11:24:00.243281419 +0100" tnsnames.ora

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] stat tnsnames.ora
  File: ‘tnsnames.ora’
  Size: 2136            Blocks: 8          IO Block: 4096   regular file
Device: fb02h/64258d    Inode: 161521017   Links: 1
Access: (0644/-rw-r--r--)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Access: 2017-02-09 11:24:00.243281419 +0100
Modify: 2017-02-09 11:24:00.243281419 +0100
Change: 2017-02-09 11:42:22.935350329 +0100
 Birth: -

Only the ctime (change time) is not so easy to change. At least not with the touch command. For changing the ctime you need to invoke the file system debugger or stuff like that. In the end, monitoring my tnsnames.ora file changes by time is not so precise. So why not using the XFS extend attribute feature to help me. e.g. I could create md5 check sums and when the check sum differs, I know that the content was changed. Let’s do it with the root user.

As root:

[root@dbidg03 admin]# getfattr -d tnsnames.ora
[root@dbidg03 admin]#

[root@dbidg03 admin]# md5sum tnsnames.ora
d135c0ebf51f68feda895dac8631a999  tnsnames.ora

[root@dbidg03 admin]# setfattr -n user.md5sum -v d135c0ebf51f68feda895dac8631a999 tnsnames.ora
[root@dbidg03 admin]#
[root@dbidg03 admin]# getfattr -d tnsnames.ora
# file: tnsnames.ora

But this is also not so secure. Even if done with root, it can easily be removed by the oracle user.

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] getfattr -d tnsnames.ora
# file: tnsnames.ora

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] setfattr -x user.md5sum tnsnames.ora
oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] getfattr -d tnsnames.ora

To overcome this issue, XFS uses 2 disjoint attribute name spaces associated with every filesystem object. They are the root (or trusted) and user address spaces. The root address space is accessible only to the superuser, and then only by specifying a flag argument to the function call. Other users (like the oracle user in my case) will not see or be able to modify attributes in the root address space. The user address space is protected by the normal file permissions mechanism, so the owner of the file can decide who is able to see and/or modify the value of attributes on any particular file.

Ok. So let’s do it again by using the root (trusted) address space.

[root@dbidg03 admin]# setfattr -n trusted.md5sum -v "d135c0ebf51f68feda895dac8631a999" tnsnames.ora
[root@dbidg03 admin]# getfattr -n trusted.md5sum tnsnames.ora
# file: tnsnames.ora

However, from the oracle user point of view, no attributes exist, even if you know the attribute you are looking for.

oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] getfattr -d tnsnames.ora
oracle@dbidg03:/u01/app/oracle/network/admin/ [rdbms112] getfattr -n trusted.md5sum tnsnames.ora
tnsnames.ora: trusted.md5sum: No such attribute

You can take it even further, but adding another root attribute, e.g. the time when you created the md5 checksum.

[root@dbidg03 admin]# setfattr -n trusted.md5sumtime -v "09.02.2018 13:00:00" tnsnames.ora
[root@dbidg03 admin]# getfattr -n trusted.md5sumtime tnsnames.ora
# file: tnsnames.ora
trusted.md5sumtime="09.02.2018 13:00:00"

[root@dbidg03 admin]# getfattr -n trusted.md5sum tnsnames.ora
# file: tnsnames.ora

Now you have a good chance to find out if the file content was changed or not, by simply checking if the file has a different check sum.


XFS extended attributes are quite powerful features and you can use them in a lot of scenarios. Take care that you have a backup solution that support extended attributes, else you will lose all the information once you restore your data.


Cet article Linux – Securing your important files with XFS extendend attributes est apparu en premier sur Blog dbi services.

Index bouncy scan

Jonathan Lewis - Thu, 2017-02-09 07:05

There’s a thread running on OTN at present about deleting huge volumes of duplicated data from a table (to reduce it from 1.1 billion to about 22 million rows). The thread isn’t what I’m going to talk about, though, other than quoting some numbers from it to explain what this post is about.

An overview of the requirement suggests that a file of about 2.2 million rows is loaded into the table every week with (historically) no attempt to delete duplicates. As a file is loaded into the table every row gets the same timestamp, which is the sysdate at load time. I thought it would be useful to know how many different timestamps there were in the whole table.  (From an averaging viewpoint, 1.1 billion rows at 2.2 million rows per week suggests about 500 dates/files/weeks – or about 9.5 years – but since the table relates to “customer accounts” it seems likely that the file was originally smaller and has grown over time, which means the hiostory may be rather longer than that.)

Conveniently there is an index on the “input_user_date” column in the table so we might feel happy running a query that simply does:

        distinct input_user_date
order by

We might then refine the query to do a count(*) aggregate, or do some analytics to find any strange gaps in the timing of the weekly loads. However, all I’m really interested in is the number of dates because I’ve suggested we could de-duplicate the data by running a PL/SQL process that does a simple job for each date in turn, and I want to get an idea of how many times that job will run so that I can estimate how long the entire process might take.

The trouble with the basic query is that the table is (as you probably noticed) rather large, and so is the index. If we assume 8 bytes (which includes the length byte) for a date, 7 bytes for the rowid, 4 bytes overhead, and 100% packing we get about 420 index entries per leaf blocks, so with 1.1 billion entries the index is about 2.6 million leaf blocks. If the index had been built with compression (which means you’d only be recording a date once per leaf block) it would still be about 1.6 million leaf blocks. Fortunately we wouldn’t have to do much “real” sorting to report just a list of distinct values, or even the count(*) for each date, if we made Oracle use an index full scan – but it’s still a lot of work to read 1.6 million blocks (possibly using single block reads) and do even something as simple as a running count as you go. So I whipped up a quick and dirty bit of PL/SQL to do the job.

        m_d1 date := to_date('01-Jan-0001');
        m_d2 date := to_date('01-Jan-0001');
        m_ct number := 0;
                        input_user_date > m_d1

                exit when m_d2 is null;

                m_ct := m_ct + 1;
                dbms_output.put_line('Count: ' || m_ct || '  Date: ' || m_d2);
                m_d1 := m_d2;

        end loop;

The code assumes that the input_user_date hasn’t gone back to a silly date in the past to represent a “null date” (which shouldn’t exist anyway; if you want to use code like this but have a problem with a special “low-value” then you would probably be safest adding a prequel SQL that selects the min(columnX) where columnX is not null to get the starting value instead of using the a constant as I have done.

The execution path for the SQL statement should be an index-only: “index range scan (min/max)” which typically requires only 3 or 4 logical I/Os to find the relevant item for each date (which compares well with the estimated 2,200,000 / 420 = 5,238 leaf blocks we would otherwise have to scan through for each date). Here’s the path you should see:

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |     8 |            |          |
|   2 |   FIRST ROW                  |       |     1 |     8 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| CA_I1 |     1 |     8 |     3   (0)| 00:00:01 |

Predicate Information (identified by operation id):
   3 - access("INPUT_USER_DATE">:B1)

I did build a little data set as a proof of concept – and produced a wonderful example of how the scale and the preceding events makes a difference that requires you to look very closely at what has happened. I used a table t1 in my example with a column d1, but apart from the change in names the PL/SQL block was as above.Here’s the code I used to create the data and prepare for the test:

create table t1 nologging
        trunc(sysdate) + trunc((rownum - 1)/100) d1,
        rpad('x',100)   padding
        rownum <= 50000

execute dbms_stats.gather_table_stats(user,'t1')
alter table t1 modify d1 not null;

create index t1_i1 on t1(d1) nologging pctfree 95

select index_name, leaf_blocks from user_indexes;

alter system flush buffer_cache;

alter session set events '10046 trace name context forever, level 8';

My data set has 500 dates with 100 rows per date, and the pctfree setting for the index gives me an average of about 8 leaf blocks per date (for a total of 4,167 leaf blocks). It’s only a small index so I’m expecting to see just 2 or 3 LIOs per date, and a total of about 500 physical reads (one per date plus a handful for reading branch blocks). Here’s the output from the running tkprof against the trace file:

 T1 WHERE D1 > :B1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    501      0.00       0.01          0          0          0           0
Fetch      501      0.08       0.18       4093       1669          0         501
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1003      0.09       0.19       4093       1669          0         501

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=3 pr=64 pw=0 time=9131 us)
         1          1          1   FIRST ROW  (cr=3 pr=64 pw=0 time=9106 us cost=3 size=8 card=1)
         1          1          1    INDEX RANGE SCAN (MIN/MAX) T1_I1 (cr=3 pr=64 pw=0 time=9089 us cost=3 size=8 card=1)(object id 252520)

I’ve done a physical read of virtually every single block in the index; but I have done only 3 buffer gets per date – doing fewer buffer gets than physical reads.

I’ve been caught by two optimisations (which turned out to be “pessimisations” in my test): I’ve flushed the buffer cache, so the Oracle runtime engine has decided to consider “warming up” the cache by reading extra blocks from any popular-looking objects that I’m accessing, and the optimizer may have given the run-time engine enough information to allow it to recognise that this index is subject to range scans and could therefore be a suitable object to use while warming up. As you can see from the following extracts from session events and session activity stats – we’ve done a load of multiblock reads through the index.

Event                                             Waits   Time_outs           Csec    Avg Csec    Max Csec
-----                                             -----   ---------           ----    --------    --------
db file sequential read                               1           0           0.03        .031           6
db file scattered read                              136           0          13.54        .100           1

Name                                                                     Value
----                                                                     -----
physical reads                                                           4,095
physical reads cache                                                     4,095
physical read IO requests                                                  137
physical reads cache prefetch                                            3,958
physical reads prefetch warmup                                           3,958

This isn’t likely to happen, of course, in the production system where we’ll be starting with a fully loaded cache and the leaf blocks we need are (logically) spaced apart by several thousand intervening blocks.


I can’t remember who first brought this strategy to my attention – though I’m fairly sure it was one of my Russian colleagues, who has blogged about ways to work around what is effectively a limitation of the “index skip scan”. Apologies to the originator, and if you recognise your work here please add a comment with URL below.

Working with OBIEE Data in Excel using ODBC

Rittman Mead Consulting - Thu, 2017-02-09 04:09

Look at this picture. I'm sure you've recognised the most favourite data analysis tool of all times - Excel.


But what you can't see in this picture is the data source for the table and charts. And this source is OBIEE's BI Server. Direct. Without exports or plugins!

Querying OBIEE Directly from Excel? With No Plugins? What Is Going On!

The OBIEE BI Server (nqsserver / OBIS) exposes an ODBC interface (look here if you live in a world full of Java and JDBC) which is used by Presentation Services and Administration tool. But a lesser-known benefit of this is that we can utilise this ODBC interface for own needs. But there is a little problem with the OBIEE 12c client installation - its size. Full (and the only possible actually) client OBIEE installation is more than 2 gigabytes and consists of more than 31 thousand files. Not a huge problem considering HDD sizes and prices but something not so good if you have an average-sized SSD.

And the second point to consider. We don’t want to give a full set of developer tools to an end-user. Even if our security won’t let them break anything, why would we stuff his head with unnecessary things? Let's keep things simple.

So what I had in mind with this research was to make a set of OBIEE ODBC libraries as small as possible. And the second aim was avoiding a full installation with cutting out redundant pieces. I need a small "thing" I can easily copy to any computer and then use it.

Disclaimer. Everything below is a result of our investigation. It’s not a supported functionality or Oracle’s recommendation.

I will not describe in full details the process of the investigation as it is not too challenging. It's less a detective thriller and more a tedious story. But anyways the main points will be highlighted.

Examine Working Copy

The first thing I needed to know what changes Oracle's installer does during an installation. Does it copy something to the Windows folder or everything stays in its installation folder? Does it make any registry changes (apparently it does but what exactly)?

For this task, I took a fresh Windows, created a dump of the registry and folders structure of the Windows folder, then installed OBIEE client using normal installation process, made the same dumps and compared them once again.

There were no surprises. OBIEE installer doesn't copy a single byte to the Windows folder (and it's a good news I think) but it creates a few registry keys (what was expected). Anyone who has ever tried to play around Windows ODBC won't be surprised with it at all.

I deleted some keys in order to make this screenshots more clear and readable.

So now I know names of the DLLs and their places. A good point to start. A small free utility Dependency walker helped me to find out a set of DLLs I need. This tool is very easy to use and very useful for finding a missing DLL. Just give it a DLL to explore and it will show all DLLs used by it and mark all missing.

Dependency walker

And a bit of educated guess helped to find one more folder called locale which stores all language files.

So, as a result, we got a tiny ODBC-related OBIEE client. It's very small. With only English locale it has a size about 20 megabytes and consists of 75 files. Compare it to 31 thousand files of the full client.

So that was a short story of looking and finding things. Now goes some practical result.

Folders Structure.

It seems that some paths are hard-coded. So we can't put DLLs to any folder we like. It should be something\bi\bifoundation\server. C:\BI-client\bi\bifoundation\server for example.

The List of DLLs

I tried to find the minimum viable set of the libraries. The list has only 25 libraries but it takes too much place on the screen so I put them into a collapsible list in order to keep this post not too long. These libraries should go under bin folder. C:\BI-client\bi\bifoundation\server\bin for example.

The list of ODBC DLLs

  • BiEndPointManagerCIntf64.dll
  • mfc100u.dll
  • msvcp100.dll
  • msvcr100.dll
  • nqcryptography64.dll
  • nqerrormsgcompiler64.dll
  • nqmasutility64.dll
  • nqperf64.dll
  • nqportable64.dll
  • nqsclusterapi64.dll
  • nqsclusterclient64.dll
  • nqsclusterutility64.dll
  • nqsodbc64.dll
  • nqsodbcdriverconndlg64.dll
  • nqssetup.dll
  • NqsSetupENU.dll
  • nqstcpclusterclient64.dll
  • NQSTLU64.4.5.dll
  • nqutilityclient64.dll
  • nqutilitycomm64.dll
  • nqutilitygeneric64.dll
  • nqutilitysslall64.dll
  • perfapi64.dll
  • samemoryallocator864.dll
  • xerces-c_2_8.dll

Or you may take the full bin folder. Its size is about 240 megabytes. You won't win the smallest ODBC client contest but will save a few minutes of your time.


The second folder you need is locale, it is located near bin. C:\BI-client\bi\bifoundation\server\locale, for example. Again if you agree with not the smallest client in the world, you may take the whole locale. But there are 29 locales and I think most of the time you will need only one or two of them. Every locale is about 1.5 megabytes and has 48 files. A good place for some optimisation in my opinion.

Registry Key

And the last part is registry keys. I need to tell my Windows what is my driver name and what is its path and so on. If it was a usual part of the registry I'd created a file anything.reg, put a code like this into it and imported it into the registry.

Windows Registry Editor Version 5.00

"Oracle BI Server"="Installed"


But luckily there is a small console utility which makes the task easier and more elegant - scripted. Microsoft provides us a tool called odbcconf.exe located in C:\Windows\System32 folder. And its syntax is not very obvious but not too hard also. Generally the syntax is the following: odbcconf.exe /a {action "parameters"}. In this case the call is odbcconf.exe {installdriver "Oracle BI Server|Driver=C:\BI-client\bi\bifoundation\server\bin\nqsodbc64.dll|Setup=C:\BI-client\bi\bifoundation\server\bin\nqssetup.dll|APILevel=2|SQLLevel=2|ConnectionFunctions=YYN|DriverODBCVer=03.52|Regional=Yes"}. Here installdriver is the action and the long string is the set of parameters divided by |. It may look a bit complicated but in my opinion it leaves less space for manual work and therefore less space for error. Just one note: don't forget to start a cmd windows as administrator.

Visual C++ Redistributable

If your computer is fresh and clean, you need to install a Visual C++ 2010 redistributable package. It's included in Oracle's client and placed in 'Oracle_Home\bi' folder. The file name is vcredist_x64.exe.


And as a result I got an ODBC driver I can use as I want. And not obvious but pleasant bonus is that I can give it any name I like. OBIEE version, path, whatever I want.

And I can create an ODBC DSN in a normal way using ODBC Data source Administrator. Just like always. No matter this is a hand-made driver. It was properly registered and it is absolutely legitimate.

So just a brief intermediate summary. We can take a full 2+ gigabytes OBIEE client. Or we can spend some time to:
1. Create a folder and put into it some files from the Oracle OBIEE client;
2. Create a few registry keys;
3. Install a Visual C++ 2010 redistributable
And we will get a working OBIEE ODBC driver which size is slightly above 20 megabytes.


So now we have a working ODBC connection, what can it give us?

Meet the most beloved by end users all around the world tool - Excel.

At this point of the story, some may tell me "Hey, stop right there! Where have you got that SQL? And why is it so strange? That's not an ANSI SQL". The evil part of me wants to simply give you a link to the official documentation: Logical SQL Reference and run away. But the kind one insists that even while documentation has done no harm to anyone, that's not enough.

In a nutshell, this is an SQL that Presentation services send to BI Server. When anyone builds an analysis or runs a dashboard, Presentation services create and send logical queries to BI Server. And we can use it for our own needs. Create an analysis as usual (or open an existing one), navigate to the Advanced tab, and then copy and paste analysis' Logical SQL. You may want to refine it, maybe remove some columns, or change aliases, or add a clause or two from the evil part's documentation, but for the first step just take it and use it. That simple.

And of course, we can query our BI server using any ODBC query tool.

And all these queries go directly to the BI Server. This method doesn't use Presentation Services, OBIEE won't build a complex HTML which we have to parse later. We use a fast and efficient way instead.

Categories: BI & Warehousing

Partner Webcast – Announcing Oracle CASB Cloud Service, an API-based Cloud Access Security Broker

On September 18, 2016, Oracle announced that it signed an agreement to acquire Palerra, extending Oracle Identity Cloud Service with an innovative Cloud Access Security Broker (CASB). The transaction...

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

How to change the DBID after restore the database on other server

Tom Kyte - Thu, 2017-02-09 02:26
Hi Tom, I am in the process to migrating the databases from existing server to the new server. I did restore my controlfile using RMAN catalog and after that I logged in locally ?rman target /? and restored the database. My question is how can...
Categories: DBA Blogs

How Sorts (Disk) in query works

Tom Kyte - Thu, 2017-02-09 02:26
I have two queries - 1. I see sorts(Disk) in the autotrace output for a query.What is actually sort(Disk) and how it works. Is the rowsets are brought in memory in chunks, sorted and written back to temp tablespace. After which the chunks are merg...
Categories: DBA Blogs

When to replace the hash-cluster for an in-memory table

Tom Kyte - Thu, 2017-02-09 02:26
At the moment we was a database with a dual timeline. Transaction timeling and validity timeline. All the valid records in the current transaction timeline are duplicated in hash cluster for performance. Now Oracle 12 is coming along with it's in...
Categories: DBA Blogs

Does Dataguard apply ddl on user tables

Tom Kyte - Thu, 2017-02-09 02:26
Hello, If I alter an user table (add a new column) in a dataguard environment from the active node, will that be reflected on the mounted DB of the passive node? Regards, Daniel
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator