Feed aggregator

CREATE AN INVISIBLE INDEX ON A TABLE

Learn DB Concepts with me... - Tue, 2016-06-07 12:50
INVISIBLE INDEX:

Oracle 11g gives us ability to create indexes that can be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level

CREATE AN INVISIBLE INDEX:

CREATE INDEX INV_IDX_OS_USR ON TEST_IDX (ID) INVISIBLE;

lets check the newly created index :

SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,VISIBILITY from all_indexes where index_name='INV_IDX_OS_USR';

OWNER         INDEX_NAME            TABLE_OWNER         TABLE_NAME      VISIBILITY       
--------      -----------                                    ----------               -----------                  --------
ATOORPU       INV_IDX_OS_USR       ATOORPU            TEST_IDX               INVISIBLE 


USER CAN'T MAKE USE OF INVISIBLE INDEX UNTIL HE MAKES IT VISIBLE IN THAT SESSION LETS SEE IF WE CAN USE INVISIBLE INDEX WITH OUT ENABLING IT IN OPTIMIZER:

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;




MAKING AN INDEX VISIBLE IN CURRENT SESSION:

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;







MAKING AN INDEX INVISIBLE IN CURRENT SESSION:

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;

-->> you will not have to provide any hints to use index. I have provided hint just to make sure  oracle uses it.

select  * from TEST_IDX where ID=284;        -->> Same as above



MAKING AN INDEX INVISIBLE OR VISIBLE:


Indexes can be created as invisible by using the INVISIBLE keyword at the end, and their visibility can be managed using the ALTER INDEX command

TO MAKE AN EXISTING INDEX INVISIBLE USE BELOW SYNTAX: 

ALTER INDEX index_name INVISIBLE;

TO MAKE AN EXISTING INDEX VISIBLE USE BELOW SYNTAX: 


ALTER INDEX index_name VISIBLE;


Categories: DBA Blogs

CREATE AN INVISIBLE INDEX ON A TABLE

Learn DB Concepts with me... - Tue, 2016-06-07 12:50
INVISIBLE INDEX:

Oracle 11g gives us ability to create indexes that can be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level

CREATE AN INVISIBLE INDEX:
 
CREATE INDEX INV_IDX_OS_USR ON TEST_IDX (ID) INVISIBLE;

lets check the newly created index :

SQL> select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,VISIBILITY from all_indexes where index_name='INV_IDX_OS_USR';

OWNER         INDEX_NAME            TABLE_OWNER         TABLE_NAME      VISIBILITY       
--------      -----------                                    ----------               -----------                  --------
ATOORPU       INV_IDX_OS_USR       ATOORPU            TEST_IDX               INVISIBLE   


USER CAN'T MAKE USE OF INVISIBLE INDEX UNTIL HE MAKES IT VISIBLE IN THAT SESSION LETS SEE IF WE CAN USE INVISIBLE INDEX WITH OUT ENABLING IT IN OPTIMIZER:

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;




MAKING AN INDEX VISIBLE IN CURRENT SESSION:

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284;






 
MAKING AN INDEX INVISIBLE IN CURRENT SESSION:
 
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;

select /*index (TEST_IDX INV_IDX_OS_USR)*/ * from TEST_IDX where ID=284; 

-->> you will not have to provide any hints to use index. I have provided hint just to make sure  oracle uses it.

select  * from TEST_IDX where ID=284;        -->> Same as above

 

MAKING AN INDEX INVISIBLE OR VISIBLE:


Indexes can be created as invisible by using the INVISIBLE keyword at the end, and their visibility can be managed using the ALTER INDEX command

TO MAKE AN EXISTING INDEX INVISIBLE USE BELOW SYNTAX: 

ALTER INDEX index_name INVISIBLE;

TO MAKE AN EXISTING INDEX VISIBLE USE BELOW SYNTAX: 


ALTER INDEX index_name VISIBLE;


Categories: DBA Blogs

“Why 'Fast Follower' Is Yesterday's Tech And Business Strategy”

Linda Fishman Hoyle - Tue, 2016-06-07 11:29
In this Forbes article, Oracle's Rob Preston, Editorial Director (pictured left), challenges companies to consider whether they can afford to be fast followers, rather than first movers, when in comes to innovating and adopting technology.

It used to be that being first to adopt, innovate, or change was too dicey for many companies. Being a follower reduced one's risk while others worked out the bugs; however, today, according to Sonny Singh, SVP and general manager of Oracle’s financial services global business unit, that strategy is “incomprehensible” because of all the disruption going on around us.

No Need to Boil the Ocean

Preston says being a first mover is not about being the very first to innovate. It's about investing in the “tools, infrastructure, processes, and talent" to create a steady flow of innovation. There’s still plenty of room (and advantages) to be a first mover in areas such as mobile and data analytics. Otherwise, Singh contends you could miss “an entire generation of technology, an entire customer base, or an entire trend."

Takeaway: Being a fast follower isn’t really an option in today’s fast-paced digital world.

Read the article: Why 'Fast Follower' Is Yesterday's Tech And Business Strategy

Oracle JET Executing Dynamic ADF BC REST URL

Andrejus Baranovski - Tue, 2016-06-07 10:56
I'm going to explain how to call ADF BC REST service from JET API and how to control parameters passed through to REST URL. Most of the time, REST should return data based on different parameter values, URL is not static. I will be exploring two cases - search functionality, where parameter value is entered by the user and LOV functionality, where current employee key is submitted to fetch colleagues list.

You can go through the slides from my AMIS25 session - Oracle JET CRUD and ADF BC REST. I explain similar concepts:


Download sample application (you must run ADF BC REST application in JDEV 12.2.1 and JET in NetBeans 8) - JETCRUDApp_v9.zip.

1. Filter implementation

We should take a look first, how filter/search functionality is implemented. User enters keyword for Last Name and filters data in JET table:


User can reset search results, change filter criteria. Filtering is done using startswith operator, by ADF BC VO (it creates temporary bind variable from REST URL parameter q=LastName LIKE value):


Filter block is implemented in a separate HTML div. Filter value is mapped with observable variable registered in JET View Model:


Table collection is based on collection with dynamic REST URL. URL is calculated through customURL property (referencing JavaScript method) in the collection. On filter event, we clear up all dependent data and call JET collection API method - refresh(). This ensures reload for collection and REST URL re-calculation. Similar is done on reset:


Method getURL is responsible to read filter value and construct REST URL accordingly (this will be used to re-fetch collection content):


Here you can see generated REST request URL logged by NetBeans Network Monitor. This URL contains filter information supplied through dynamic REST URL defined for the collection:


2. LOV data fetch implementation

There is LOV in ADF BC, which returns current employee colleagues (employees from the same job, except employee himself). ADF BC REST encapsulates parameters complexity and allows to simplify REST request. JET UI renders LOV data in a chart:


Each time when employee record is selected, we execute dynamic REST call (with new employee ID) to fetch colleagues collection:


This is chart definition in JET UI, it references observable variables for series/groups from JET View Model:


Similar as in the case with Filter, we supply dynamic URL for the collection, using property customURL:


Differently than in the Filter implementation, here we dont refresh collection. We execute JET API method fetch(...), to retrieve new collection and we push contents into observable variables, referenced by chart component in the UI:

Export your APEX Interactive Report to PDF

Dimitri Gielis - Tue, 2016-06-07 10:36
Interactive Reports (and Grids in 5.1) are one of the nicest features of Oracle Application Express (APEX) as it allows an end-user to look at the data the way they want, without needing a developer to change the underlying code. End-users can show or hide columns, do calculations on columns, filter etc.

Here's an example of an interactive report where highlighting, computation and aggregation is used.


More than once I get the question, how can I export this to PDF or print this Interactive Report?

Here're 3 ways of doing this:

1. Use your browser to Print to PDF

The challenge here's that you would need to add some specific CSS to get rid of the items you don't want to be printed, e.g. the menu, the header and footer and some other components like buttons.
Also if you have many columns, they might not fit on the page and the highlighting is not working when printed, but if you can live with that, it might be an option for you.


Here's the CSS you can use:

@media print {

  .t-Body-nav {
    display:none
  }
}


2. Use the download feature of the Interactive Report itself (Actions > Download > PDF)

This feature is build-in APEX and relies on a print server supporting XSL-FO; when using ORDS it will automatically work. If you're using Apache, you will need to configure a print server like BI Publisher or Apache-FOP.


When downloading to PDF, the result looks like this:

The PDF contains the data and we can specify a header, footer and how the columns look like, but we lost many features of the Interactive Report; no highlighting, no computation or aggregation.


3. Use APEX Office Print to print the Interactive Report in your own template defined in MS Word.

One of the unique features of APEX Office Print is that it's tightly integrated with Oracle Application Express and that it understands Interactive Reports as the source of your data.

Here're the steps:

- Create your template in MS Word and add {&interactive} tag where you want the Interactive Report to be


- Give your Interactive Report a static id:



- Add the APEX Office Print Process Plugin to your page and specify the template and the static id: 


And here's the result: 


I'm biased as we created APEX Office Print (AOP), but I just find it awesome :)
In your Word template you just add one tag, that's it!

In all seriousness, we would really want to hear from you if this feature works for your Interactive Report. You can try AOP for free for 100 days. We're trying to be smart and are doing automatic calculations of the column width, but we probably can improve it even more. We introduced this feature with AOP v2.0 (MAR-16) and improved it in v2.1 (MAY-16).

Categories: Development

database option - partitioning part 2

Pat Shuff - Tue, 2016-06-07 02:07
Yesterday we looked at partitioning. Today we are going to continue this evaluation but actually execute code rather than talk in abstracts. If we want to create a partition, this is easily done by appending partitioning to a table create. It is important to remember that this option cost money when done on-premise and is typically done either to improve performance by having a smaller table to bring into memory or done to split storage so that higher speed disk can be assigned to more relevant data and lower speed and lower cost disk can be assigned to data we typically don't need to read regularly. If we are looking at using partitioning in the cloud, tiering storage is not an option. We get one disk, one type of disk, and can't assign higher speed storage to that disk partition with PaaS or DBaaS. We pay $50/TB/month to attach a disk to a compute engine and that stores our data. The tablespaces are stored in either the USER tablespace or the SYSTEM tablespace based on who creates the tablespace. To quickly review we have tables that contain our data. This data is stored in a tablespace. The tablespace might contain multiple tables or parts of tables if partitioning is used. We can assign tablespaces to different directories and typically do with on-premise systems. This allows us to put data that we need fast access to in flash memory and historic data that we might read once a year in lower cost network storage and not have to backup the historic data on a daily basis. With DBaaS we get a /u02 directory that contains the oradata folder. All tablespaces are created in this area by default. Theoretically we could mount an nfs file share if we ran the storage cloud appliance on a compute instance and pay $30/TB/month for this storage. We would have to install the nfs client on our database instance, install OSCSA on a compute instance and share the nfs directory, create a cloud storage container to hold our historic tablespaces, and point our historic partitions to our nfs mounted directories. We are not going to do this in this blog but it is an interesting thought on how to reduce the cost of storage as well as expand the amount of data that you can support with a DBaaS instance.

Let's create a few tablespaces and a partitioned table to see how it works. Most of these examples are liberally hijacked from other blogs and tutorials on the internet.

We need to note that the DBaaS that we provisioned needs to be High Performance Edition or Extreme Performance Edition. This option does not work with Standard Edition or Enterprise Edition and will fail when you try to create the table. We begin by creating a few tablespaces as well as a partitioned table that stores data into these tablespaces. It is important to note that we can easily do this because consuming storage only happens when we insert data and not create a table. We can play with creation all we want at very little cost. First, let's look at our layout using SQL Developer. If we connect to our database as a sys user we can see that by default we have the following tablespaces defined in our PDB1 pluggable container. The same is true for an 11g instance or container database. We are going to look at pluggable because it is easy to make sure that what we are creating is for this instance and not someone else playing with the system. If we add our database instance to the DBA view in SQL Developer we notice that Tablespaces appears as one of the line entries under our database. We can click on this and look at the tablespaces and files associated with them provisioned in our instance. To see the file allocation and which file system the tablespace is allocated in we need to scroll across the screen to see the information on the right.

We are going to create a few tablespaces then create a table and allocate provisions into these tablespaces. Note that these commands might not work on Amazon RDS because you need to have system level access to the database to create a tablespace and assign the file name. If we let the system use the default oradata area the create works fine. If we want to create the tablespace in /nfs/historic_oradata then the create will fail and is not allowed with RDS. Let's look at a simple example

CREATE TABLESPACE T1;
CREATE TABLESPACE T2;
CREATE TABLESPACE T3;
CREATE TABLESPACE T4;
CREATE TABLE credential_evaluations
( eval_id VARCHAR2(16) primary key
, grad_id VARCHAR2(12)
, grad_date DATE
, degree_granted VARCHAR2(12)
, degree_major VARCHAR2(64)
, school_id VARCHAR2(32)
, final_gpa NUMBER(4,2))
PARTITION BY RANGE (grad_date)
( PARTITION grad_date_70s
VALUES LESS THAN (TO_DATE('01-JAN-1980','DD-MON-YYYY')) TABLESPACE T1
, PARTITION grad_date_80s
VALUES LESS THAN (TO_DATE('01-JAN-1990','DD-MON-YYYY')) TABLESPACE T2
, PARTITION grad_date_90s
VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')) TABLESPACE T3
, PARTITION grad_date_00s
VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')) TABLESPACE T4 )
ENABLE ROW MOVEMENT;
The create tablespace t1 is needed prior to creating the partition that stores data in the tablespace t1 or the create table command will fail. We have to have the tablespace created before we allocate a partition into it. After we create the tablespace, we can look at the tablespace allocation with SQL Developer by going to the DBA view and looking at PDB1, tablespaces.

Note that the file /u02/app/oracle/oradata/ORCL/339C06AF452F1EB6E0531635C40AD41B/datafile/o1_mf_t1_co5fjnr3_.dbf was created for us. If we change our tablespace create command to

CREATE TABLESPACE T1 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t1.dbf' size 2G;
CREATE TABLESPACE T2 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t2.dbf' size 2G;
CREATE TABLESPACE T3 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t3.dbf' size 2G;
CREATE TABLESPACE T4 datafile '/u02/app/oracle/oradata/ORCL/PDB1/t4.dbf' size 2G;
we drop the files into the directory that we want and have control over the file name and location. It is important to note that this will fail on Amazon RDS because we do not have access to the filesystem and can't specify the filename or location.

When we execute this command it takes significantly longer than our first execution because the system creates a 2 GB file before creating our tablespace and table. We would typically want to add other options like how to grow our partitions, limits on the size, and other dynamic commands. We are primarily concerned with where the file is created and not post maintenance at this point.

We need to make sure that we are running on High Performance Edition or Extreme Performance Edition because Standard Edition and Enterprise Edition fail during the create table command.

In summary, we looked a little deeper at partitioning by looking at the create tablespace and where it creates the files in the file system. We also looked at how we can control the naming as well as location with the create statement options. We briefly touch on two of the advantages that partitioning brings, speed and cost and talked about how to reduce cost by using an nfs share to store more data than a typical DBaaS provides as well as using $30/TB/month storage rather than $50/TB/month storage in the cloud. Hopefully this code example will allow you to play with partitioning and speed up select statements using the High Performance Edition of DBaaS.

OCFS2 Certified with EBS 12.2 Shared File System Configurations

Steven Chan - Mon, 2016-06-06 18:18

Oracle Cluster File System 2 (OCFS2) is now certified for use with E-Business Suite 12.2 when sharing a single file system between multiple application tier server nodes. 

Load-balancing E-Business Suite 12.2

You can improve your E-Business Suite 12.2 environment's fault tolerance, availability, and performance by distributing traffic across multiple application tier server nodes. For information about using multinode configurations, see:

EBS 12.2 load-balanced multinode architecture

Reducing your patching requirements with a shared file system

If you have multiple application tier server nodes, you need to apply an identical set of patches to each of them.  You can reduce your patching overhead by sharing a single file system between the individual application tier servers. Applying a patch to the shared file system allows it to be used by all application tier server nodes.  For information about using shared file system configurations in EBS 12.2, see:

What file system should you use for EBS 12.2?

Generally speaking, any fast, standards-compliant file system should work with Oracle E-Business Suite.  "Standards-compliant" can be interpreted to mean "any file systems that do not require code-level changes to the E-Business Suite." 

We do not test specific file systems with EBS 12.2, so we lack the data needed to recommend specific file systems.  For related information about NFS, GFS, ACFS, and what we use internally at Oracle, see: 

Why are we specifically certifying OCFS2?

EBS 12.2 uses two different file systems as part of its Online Patching infrastructure.  We have found that EBS 12.2 is sensitive to the performance characteristics of the underlying file system.

File system performance can sometimes be optimized by changing a few key mount options. We don't have the resources to test all of the available third-party file systems, but we can test Oracle's own file systems to ensure that you have the smoothest experience using them.  

What did we find when we tested OCFS2?

We tested EBS 12.2 under a variety of conditions, including applying patches using Online Patching while the EBS environment was under load.  We found that you should use the default block size for OCFS2, but aside from that, we didn't find any requirements for special mount options for OCFS2. 

Customers should also follow:

Related Articles

Categories: APPS Blogs

Amsterdam to Zeist

Tim Hall - Mon, 2016-06-06 16:01

I had a lazy morning, then it was time to start the journey to Zeist. It was a pretty straight forward journey from Amsterdam Central, via Utrecht to Driebergen-Zeist station. I had planned to get a bus from Driebergen-Zeist station to the hotel, but looking on the map it was only a couple of miles so I walked it. It was a little annoying dragging my suitcase, but no big drama. You can check out the journey and the hotel in the video below.

I had a quick walk around part of the town, near the hotel, where I bumped into my new stepmom.

In the evening I got a message from Dad asking if I wanted to go out to a family dinner. It was just as you would expect. Me crying and screaming, “You can’t tell me what to do, you’re not my mother!” and her saying, “I’m not trying to replace your mother. I just want to be friends!” I got back to my hotel room to find some brochures for posh, but secluded boarding schools. I’m not sure what I should be thinking at this point…

Tomorrow I’m speaking at the OGH Oracle DBA and SQL Celebration Day. Dad is doing the keynote and my new stepmom is going to visit some relatives that live near. I have a sneaking suspicion it’s going to be like “The Princess Diaries” and she will find she’s actually next in line to the Dutch Throne. Does that make me a step-prince?

Cheers

Tim…

Amsterdam to Zeist was first posted on June 6, 2016 at 10:01 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Now Available: Oracle Digital Experience Infowall!

WebCenter Team - Mon, 2016-06-06 15:35
Oracle Corporation Engage People Anytime, Anywhere from Any Device Connect Faster. Simplify Self-Service. Re-Define Engagement.
Are you looking to improve IT productivity, drive efficiency and foster innovation? View this InfoWall to learn how you can enhance employee productivity and mobility through self-service, engagement and application extensions. Discover how the Oracle Cloud Platform for Content and Process reduces IT administration overhead, enforces enterprise wide security and drives agility and innovation. The results -- effective customer engagement directly impacting revenue growth.

Learn how you can:
  • Collaborate anywhere, anytime and from any device
  • Simplify business process automation
  • Communicate and collaborate more effectively
Red Button Top View the InfoWall Red Button Bottom Infowall View the InfoWall to discover how to enable the next wave of productivity and innovation.
Integrated Cloud Applications and Platform Services Copyright © 2016, Oracle Corporation.
All rights reserved.
Contact Us | Legal Notices and Terms of Use | Privacy Statement

June 23: Credit Acceptance Corporation—Oracle Sales Cloud Customer Forum

Linda Fishman Hoyle - Mon, 2016-06-06 09:38

Join us for an Oracle Sales Cloud Customer Reference Forum on Thursday, June 23, 2016, at 8:00 a.m. PT / 11:00 a.m. ET.

Noah Kotch, SVP of IT of Credit Acceptance Corporation will talk about the company's desire to transform its sales organization to leverage a standardized approach and marketing organization to procure high-value leads. Register now to attend the live Forum and learn more about Credit Acceptance Corporation’s experience with Oracle Sales Cloud.

Merge Precision

Jonathan Lewis - Mon, 2016-06-06 06:39

This note is about a little detail I hadn’t noticed about the merge command until a question came up on the OTN database forum a few days ago. The question was about the impact of the clustering_factor on the optimizer’s choice of execution plan – but the example supplied in the question displayed an oddity I couldn’t explain. Here’s the code and execution plan as originally supplied:


MERGE INTO gtt_ord t1
    USING X t2 ON (t1.global_ext_id = t2.ext_id)
    WHEN MATCHED THEN
    UPDATE SET t1.ord_id = t2.ord_id;
 
-------------------------------------------------------------------------------------------
| Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |                    |       |       |   832 (100)|          |
|   1 |  MERGE               | GTT_ORD            |       |       |            |          |
|   2 |   VIEW               |                    |       |       |            |          |
|*  3 |    HASH JOIN         |                    |  1156 |   706K|   832   (2)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| GTT_ORD            |  1152 |   589K|    36   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| X                  |   188K|    18M|   794   (2)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("t1"."GLOBAL_EXT_ID"="t2"."EXT_ID")

The plan doesn’t seem at all surprising so far but the OP had also told us that the X table had an index on (ext_id, ord_id) for which the stored statistics reported 699 leaf blocks. Look carefully at the query, and especially the columns used from table X, and ask yourself: why has the optimizer chosen a full tablescan at a cost of 794 when it could have done an index fast full scan on an index with only 699 leaf blocks.

Naturally I had to build a model (using 11.2.0.4, because that’s what the OP declared) to see if the behaviour was typical:


rem
rem     Script:         merge_precision.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2016
rem     Purpose:
rem
rem     Last tested
rem             12.1.0.2
rem             11.2.0.4
rem

create  table ord(
        ord_id          number(8,0),
        global_ext_id   number(8,0),
        v1              varchar2(10),
        padding         varchar2(100)
)
;


create table x (
        ord_id          number(8,0),
        ext_id          number(8,0),
        v1              varchar2(10),
        padding         varchar2(100)
);

alter table x add constraint x_pk primary key(ord_id);

create index x_idx1 on x(ext_id);
create unique index x_idx2 on x(ext_id, ord_id);

insert into x
select
        rownum,
        trunc(dbms_random.value(0,5e5)),
        lpad(rownum,10,'0'),
        rpad('x',100,'x')
from
        dual
connect by
        level <= 1e5
;

insert into ord
select
        to_number(null),
        trunc(dbms_random.value(0,5e5)),
        lpad(rownum,10,'0'),
        rpad('x',100,'x')
from
        dual
connect by
        level <= 1e3 ; execute dbms_stats.gather_table_stats(user,'x',method_opt=>'for all columns size 1')
execute dbms_stats.gather_table_stats(user,'ord',method_opt=>'for all columns size 1')

explain plan for
merge
into    ord
using   x
on      (ord.global_ext_id = x.ext_id)
when matched then
        update set ord.ord_id = x.ord_id
;

select * from table(dbms_xplan.display(null,null,'projection'));

I’ve modified the table names a little (and I’m not using the global temporary table hinted at by the original table name), and I’ve made a couple of cosmetic changes to the merge statement. The three indexes I’ve created model the three indexes reported by the OP (with the assumption that the index with PK” in its name was the primary key and that any index including the primary key column would have been declared unique).

You’ll notice that in my call to dbms_xplan I’ve included the ‘projection’ formatting option – and that’s quite important in this case, even though I hardly ever find a need to use it. Here’s the plan I got:


-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |      |  1100 | 28600 |   234   (4)| 00:00:02 |
|   1 |  MERGE               | ORD  |       |       |            |          |
|   2 |   VIEW               |      |       |       |            |          |
|*  3 |    HASH JOIN         |      |  1100 |   256K|   234   (4)| 00:00:02 |
|   4 |     TABLE ACCESS FULL| ORD  |  1000 |   114K|     4   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| X    |   100K|    11M|   228   (4)| 00:00:02 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD"."GLOBAL_EXT_ID"="X"."EXT_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[96], SYSDEF[32720]
   2 - "X"."ORD_ID"[NUMBER,22]
   3 - (#keys=1) "ORD"."GLOBAL_EXT_ID"[NUMBER,22],
       "X"."EXT_ID"[NUMBER,22], "ORD".ROWID[ROWID,10],
       "ORD"."ORD_ID"[NUMBER,22], "ORD"."PADDING"[VARCHAR2,100],
       "ORD"."V1"[VARCHAR2,10], "X"."ORD_ID"[NUMBER,22],
       "X"."PADDING"[VARCHAR2,100], "X"."V1"[VARCHAR2,10]
   4 - "ORD".ROWID[ROWID,10], "ORD"."ORD_ID"[NUMBER,22],
       "ORD"."GLOBAL_EXT_ID"[NUMBER,22], "ORD"."V1"[VARCHAR2,10],
       "ORD"."PADDING"[VARCHAR2,100]
   5 - "X"."ORD_ID"[NUMBER,22], "X"."EXT_ID"[NUMBER,22],
       "X"."V1"[VARCHAR2,10], "X"."PADDING"[VARCHAR2,100]

The anomaly appeared in my model. I have a statement that could have been satisfied by a fast full scan of the x_idx2 index but Oracle did a full tablescan instead. That’s where the projection information shows its value. Look at the columns projected at operation 5 – it’s the full set of columns in the table including some that I definitely don’t need. I had never checked the details of a merge command before, but if you had asked me I would have assumed that the optimizer would have worked out which columns were actually needed and optimized for those columns – but it doesn’t seem to do that here. My next step was to tell Oracle which columns I needed by expanding my ‘using’ clause:


explain plan for
merge
into    ord ord
using   (select ext_id, ord_id from x) x
on      (ord.global_ext_id = x.ext_id)
when matched then
        update set ord.ord_id = x.ord_id
;

select * from table(dbms_xplan.display(null,null,'projection'));

----------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | MERGE STATEMENT         |        |  1100 | 28600 |    55  (10)| 00:00:01 |
|   1 |  MERGE                  | ORD    |       |       |            |          |
|   2 |   VIEW                  |        |       |       |            |          |
|*  3 |    HASH JOIN            |        |  1100 |   136K|    55  (10)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | ORD    |  1000 |   114K|     4   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| X_IDX2 |   100K|   976K|    49   (7)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD"."GLOBAL_EXT_ID"="EXT_ID")
   
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[96], SYSDEF[32720]
   2 - "X"."ORD_ID"[NUMBER,22]
   3 - (#keys=1) "ORD"."GLOBAL_EXT_ID"[NUMBER,22], "EXT_ID"[NUMBER,22],
       "ORD".ROWID[ROWID,10], "ORD"."ORD_ID"[NUMBER,22],
       "ORD"."PADDING"[VARCHAR2,100], "ORD"."V1"[VARCHAR2,10],
       "X".ROWID[ROWID,10], "ORD_ID"[NUMBER,22]
   4 - "ORD".ROWID[ROWID,10], "ORD"."ORD_ID"[NUMBER,22],
       "ORD"."GLOBAL_EXT_ID"[NUMBER,22], "ORD"."V1"[VARCHAR2,10],
       "ORD"."PADDING"[VARCHAR2,100]
   5 - "X".ROWID[ROWID,10], "ORD_ID"[NUMBER,22], "EXT_ID"[NUMBER,22]

Surprise, surprise! If you do the projection manually in the using clause you get the column elimination you need and the optimizer can take advantage of the covering index. I’ve no doubt that other people have discovered this in the past – and possibly even written about it – but when I checked the merge command in the SQL Reference manual there was no indication that it was a good idea to be as precise as possible in the using clause.

It seems likely that this observation isn’t going to be useful in many “real-life” examples of using the merge command – I think I’ve only ever seen it used when most of the columns in the source table are used, and I don’t often seen cases of people creating indexes that hold a large fraction of the columns in a table – but it’s worth knowing about, especially when you realize that you’re not just giving the optimizer the option for using “an index” or “an index fast full scan” in the execution plan, you’re making it possible for several other plans to appear. For example, if I didn’t have the x_idx2 covering index, here’s a possible plan for the statement:


----------------------------------------------------------------------------------------------
| Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT           |                  |  1106 | 28756 |   598   (4)| 00:00:03 |
|   1 |  MERGE                    | ORD              |       |       |            |          |
|   2 |   VIEW                    |                  |       |       |            |          |
|*  3 |    HASH JOIN              |                  |  1106 |   137K|   598   (4)| 00:00:03 |
|   4 |     TABLE ACCESS FULL     | ORD              |  1000 |   114K|     4   (0)| 00:00:01 |
|   5 |     VIEW                  | index$_join$_006 |   100K|   976K|   591   (3)| 00:00:03 |
|*  6 |      HASH JOIN            |                  |       |       |            |          |
|   7 |       INDEX FAST FULL SCAN| X_PK             |   100K|   976K|   240   (3)| 00:00:02 |
|   8 |       INDEX FAST FULL SCAN| X_IDX1           |   100K|   976K|   329   (2)| 00:00:02 |
----------------------------------------------------------------------------------------------


More on an anomaly with this plan later, though. It should have appeared automatically when I dropped the x_idx2 index, but it didn’t.

Footnote:

The answer to the OP’s original questions are: the clustering_factor for the indexes starting with ext_id was probably high because of the order and degree of concurrency with which  the different values for ext_id arrived, combined with the effects of ASSM. If the ext_id values were arriving in a fairly well ordered fashion then setting the table preference table_cached_blocks to a value around 16 (or 16 x N for an N-node RAC cluster) and re-gathering stats on the indexes would probably produce a much more realistic clustering_factor that might persuade the optimizer to use an indexed access path into his table X.

The plans shown above were produced on an instance of 11.2.0.4; but the information is also accurate for 12.1.0.2

Update (about an hour after publication)

It didn’t take long for someone to point out that Alexander Anokhin had written about this phenomenon nearly four years ago, and had further commented on the fact that it wasn’t just the USING (source) table that projected irrelevant columns – the target table did as well, as did the join. Take a look at the projection on operations 3 and 4 in the original plan: you’ll see ord.padding and ord.v1 appearing in both of them (as well as x.padding and x.v1 appearing in operation 3).

Alexander showed the same workaround that I have above – but also highlighted the fact that it could be (and ought to be) applied to BOTH tables.


explain plan for
merge
into    (select ord.ord_id, ord.global_ext_id from ord) ord
using   (select ext_id, ord_id from x) x
on      (ord.global_ext_id = x.ext_id)
when matched then
        update set ord.ord_id = x.ord_id
;

select * from table(dbms_xplan.display(null,null,'outline projection'));

----------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | MERGE STATEMENT         |        |  1100 | 28600 |    45  (12)| 00:00:01 |
|   1 |  MERGE                  | ORD    |       |       |            |          |
|   2 |   VIEW                  |        |       |       |            |          |
|*  3 |    HASH JOIN            |        |  1100 | 44000 |    45  (12)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | ORD    |  1000 | 30000 |     4   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN| X_IDX2 |   100K|   976K|    39   (8)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD"."GLOBAL_EXT_ID"="EXT_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[96], SYSDEF[32720]
   2 - "X"."ORD_ID"[NUMBER,22]
   3 - (#keys=1) "ORD"."GLOBAL_EXT_ID"[NUMBER,22], "EXT_ID"[NUMBER,22],
       "ORD".ROWID[ROWID,10], "ORD"."ORD_ID"[NUMBER,22], "X".ROWID[ROWID,10],
       "ORD_ID"[NUMBER,22]
   4 - "ORD".ROWID[ROWID,10], "ORD"."ORD_ID"[NUMBER,22],
       "ORD"."GLOBAL_EXT_ID"[NUMBER,22]
   5 - "X".ROWID[ROWID,10], "ORD_ID"[NUMBER,22], "EXT_ID"[NUMBER,22]

Apart from the change in the list of column names in the projection content for operations 3 and 4, take note of the reduction in the Bytes column of the execution plan body.


What Are Oracle SQL Analytic Functions?

Complete IT Professional - Mon, 2016-06-06 06:00
In this article, I explain what Oracle SQL analytic functions are, how they are different from other functions, and show you some examples. What Are Oracle SQL Analytic Functions? Analytic functions are functions that calculate an aggregate value based on a group of rows. A recent article I wrote on SQL aggregate functions mention that […]
Categories: Development

PeopleSoft Security

Throughout the summer, Integrigy will be releasing new research on PeopleSoft security. This research focuses on the secure configuration of PeopleSoft and includes both the application and the major technical components such as the database (Oracle RDBMS), WebLogic and Jolt/Tuxedo. Hopefully, these blog posts will be useful.

If you have questions, please contact us at info@integrigy.com

Michael A. Miller, CISSP-ISSMP, CCSP

Oracle PeopleSoft
Categories: APPS Blogs, Security Blogs

Any Questions

Jonathan Lewis - Mon, 2016-06-06 03:00

I’ve popped this to the top of the stack because OUG Scotland is only a couple of weeks away:

I’m going to be at the OUG Scotland conference on 22nd June, and one of my sessions is a panel session on Optimisation where I’ll be joined by Joze Senegacnik and Card Dudley.

The panel is NOT restricted to questions about how the cost based optimizer works (or not), we’re prepared to tackle any questions about making Oracle work faster (or more efficiently – which is not always the same thing). This might be configuration, indexing, other infrastructure etc.; and if we haven’t got a clue we can always ask the audience.

To set the ball rolling on the day it would be nice to have a few questions in advance, preferably from the audience but any real-world problems will be welcome and (probably) relevant to the audience. If you have a question that you think suitable please email it to me or add it as a comment below. Ideally a question will be fairly short and be relevant to many people; if you have to spend a long time setting the scene and supplying lots of specific detail then it’s probably a question that an audience (and the panel) would not be able to follow closely enough to give relevant help.

Update 29th April

I’ve already had a couple of questions in the comments and a couple by email – but keep them coming.


database option - partitioning

Pat Shuff - Mon, 2016-06-06 02:07
Database partitioning has been around since 8i version of the database over ten years ago. The initial features of partitioning were manual processes that allowed you to split data by range like dates or sequences like zip codes. Tablespaces were able to be split into multiple files and indexes applied to each file. If a select statement were executed with a where clause that met the partition boundary, a full table scan was not necessary. Splitting the data into different tablespaces allows us not only to read only the relevant data into memory but we can split our database into storage tiers. We can keep the most used data in high speed disk and historic data in slower lower cost storage. Not only can we use lower cost storage but we can compress the data that is not changing and take up less space. We keep our frequently used data in a high speed disk (or memory if we are lucky) and our older data in lower cost storage. This is only available with partitioning and the reason why many customer purchase this as an option. The return on the software investment significantly reduces the cost of our database storage. We can use flash drives for our current quarter/month data, 10K rpm drives for last quarter/month data, 7.5K rpm drives the rest of the years data, and nfs mounts for data greater than a year old. The cost savings on storage more than pays for the cost of partitioning. Unfortunately, this does not correlate into cloud services since you really don't get into tiered storage behind a database when you consume DBaaS or PaaS. We need to focus on improving performance by helping subpartitions into the available memory to speed up select statements.

Some places to learn more about partitioning include

Before we go down the rabbit hole and dive deep into partitioning, let's review how a select statement works and how data is stored. Say for example we have a database that contains addresses for customers. The table contains an id number, a first name, last name, address, phone number, city, state, zip code, credit card number, credit card expiration, and email address. We have a second table for our on-line catalog that contains part numbers, a title, a description, and a file link for photos. We have a third table for our orders and it contains a customer id number, a part number, an order quantity, and order date. We would create our tables with the following commands

create table customers (
  customer_id number(8),,
  first_name varchar2(32),
  last_name varchar2(32),
  address varchar2(64),
  phone_number varchar2(10),
  city varchar2(32),
  state varchar2(16),
  zip_code varchar2(16),
  credit_card_number varchar2(16),
  credit_card_expiration varchar2(8)
  email_address varchar2(64)
);
create table catalog (
  part_number number(8),
  title varchar2(32),
  description varchar2(128),
  part_image blob
);
create order_entry(
  order_number number(8),
  customer_id number(8),
  part_number number(8),
  part_quantity number(8),
  order_date date
);

If we have ten million items in our catalog we potentially consume 128 + 32 + 8 + 16 bytes times 10,000,000. This makes our table roughly 2 TB in size. If we have two million orders we have about 0.5 TB for the order_entry table. When we create a database we have the option of defining not only the storage type that we want our table to reside in but we can define how and where to store the data associated with this table. By default all tables that we create as a user are stored in the SYSTEM tablespace. All three of these tables will be stored in the DATA area under the SYSTEM tablespace since we did not specify a storage area or tablespace to hold the tables. For the database that we created in previous blog entries using Oracle DBaaS, these files are stored in /u02. We can dive down the /u02/app/oracle/oradata/ORCL/PDB1 and see that there is a system01.dbf file. This correlates to the SYSTEM tablespace in the PDB1 pluggable database. As tables are added, they are added to the system01.dbf file. If we are in the container database ORCL the files are stored in /u02/app/oracle/oradata/ORCL/system01.dbf file.

To help with database performance, index are created into tables so that a reference to a table knows where in the system01.dbf file the table customers and catalog are located. We can also create an index on the table. This index is also stored in the system01.dbf file so that we can look up common queries as they are executed. For example, if we are looking for all orders that happened in February we can select this data quicker with an index by presorting all of the data related to order_date. The index allows us to directly access the table entries in the system01.dbf table by creating an index link to the entries. This index is also stored in the system01.dbf file and re-created when we enter new data into the order_entry table. Hopefully our indexes are small enough to stay resident in memory and we don't need to go to storage to reload and reindex our indexes. Partitioning helps keep indexes smaller as well and unused indexes can be aged out to disk to free up memory. If we never look at data that is two years old, we don't need to keep an index on our two year old data in memory but pull it in from disk when needed.

To reduce the access time and select time we can pre-sort the data in a different way. We can partition the data and store the table information in different files. Rather than storing everything in system01.dbf, we can store February order data in february.dbf. When an update to a table is done the insert is done into the system01.dbf file or the january.dbf, february.dbf, or march.dbf file. When we transition into April an april.dbf file is created and the january.dbf data is moved into q1_2016.dbf file. The key advantage to this is when we perform a select statement and look for data in March and April, we only look in the march.dbf and april.dbf files. The rest of the data is not loaded because we know that the data is not in the other table extents. This reduces the amount of data that is loaded into memory and reduces the amount of disk operations that are performed for every select statement. If everything was stored in the system01.dbf file, we would need to load all two million orders just to find the one or two hundred that happened in April. We basically read then throw away 97% of the data read because it does not match our request. True, the index would help but this requires multiple writes to the disk when an insert happens. With partitioning enabled for the order_date column, all order entries are stored pre-sorted by date in the different table extents. Since 11g interval partitioning automatically creates new partition tables. As we cross from February to March, the march.dbf is created and all writes corresponding to march orders are written to the new partition.

There are a variety of partition types that you can use to divide data

  • Range partitioning - typically based on date columns, months, quarters, a range of numberic or character values. You can specify a value less than or value greater than when defining the partition. The value that you compare can be relative or specific to a current date or number.
  • List partitioning - this describes a discrete value and assigns them to their own tablespace. We might split our catalog into plumbing products, lawn and garden products, or appliances. This helps searches into our catalog for a specific item. Note when you do a search at HomeDepot or Amazon you have the option of search in department. They are using list partitions on the back end.
  • Hash partitioning - this is good if you don't have a specific range that will split the data. If you want to sort by address for example, it is difficult to list all of the addresses or sort them into a range. The hash partition allows you to split your data into 16 different partitions and the database will split the data with a best effort to spread all of the data between the number of partitions you define.
  • Composite partitioning - this is a combination of two of the types described above. Composite partitioning is accomplished with the subpartition command where we first sort by one method then sub-sort by another. We could use a list-list or a list-range. We can use two of any of the above to help manage a large tablespace into smaller chunks.
  • Reference partitioning - this allows you to partition data based on referential constraints. If, for example, you want to create a constraint in a table creation and sort on that constraint, you can do this with partition by reference. If we create a table and add a constraint that an order_id must be tied to a customer_id in our customers table we can partition by this constraint which effectively splits the orders table into orders by customer_id which is not defined in this table.
  • Virtual column-based partitioning - virtual column partitioning allows us to split a table based on part of a column value. If, for example, we index our parts in our catalog by sequence numbers with 1-3 representing plumbing and 4-6 representing appliances, we can partition based on the first number in our part_id and effectively split the catalog based on departments without having to define the department as a column in our table. We just need to make sure that all part numbers that are inserted into our catalog follow our numbering convention and not put a gas range into the catalog staring with a 2 as the part number.

If we change the customers table described and append a partition by range statement with the command

create table customers ( .... ) partition by range (state);
we divide the table into potentially fifty different tables. As a new customer is added, they are added to the new state table. Inserts happen quicker, selects happen quicker, backups happen quicker unless all of our customers are located in one state.

If we group our customers into regions and want to store data not in fifty states but in three regions we could do this wist a list range. Note that we can define the table name when we define the partition.

create table customers (....) partition by list (state)
(partition part1 values ('Texas', 'Louisiana', 'Oklahoma', 'Arkansas') tablespace tola_ts ,
 partition part2 values ('California', 'Oregon', 'Washington', 'Hawaii') tablespace pac_ts,
 partition category_other values (default));
In this example we create the tola_ts, pac_ts, and default tablespace. We split seven of the states into three buckets and store customers into the three areas. This make reporting simpler and optimizes for select statements looking for customers in or around Texas or along the Pacific Ocean. Note that we could also subpartition this data to separate the big cities from rural areas
create table customers (.....) partition by list (state)
(partition part1 values ('Texas") tablespace texas_ts
 subpartition big_cities 
  (partition texas_cities values('Houston', 'Dallas', 'San Antonio', 'Austin', 'Fort Worth', 'El Paso') tablespace big_texas_ts,
   partition category_other values(default) tablespace small_texas_ts),
 partition part2 values ('California', 'Oregon', 'Washington', 'Hawaii') tablespace pac_ts,
 partition category_other values (default));
);
This will create four tablespaces. One for Texas big cities, one for Texas small cities, one for Pacific rim states, and one for all other states.

Database 12c added a few new commands to help manage and maintain partitions. We can now alter partitions and add, truncate, drop, split, and merge. The add and merge are very valuable functions that allow us to update ranges. If, for example, we paid a consultant two years ago to define a partition by range and they went out four years with the following

create table sales (.....) partition by range (salesdate)
(
 partition part_2015 values less than (TO_DATE('01-Jan-2016', 'DD-MON-YYYY')),
 partition part_2016 values less than (TO_DATE('01-Jan-2017', 'DD-MON-YYYY'))
)
ENABLE ROW MOVEMENT;
But we want to start saving data by quarter rather than by year we could execute the following
alter table sales add
 partition p_q1_2016 values less than (TO_DATE('01-Apr-2016', 'DD-MON-YYYY')),
 partition p_q2_2016 values less than (TO_DATE('01-Jul-2016', 'DD-MON-YYYY'));
This would slide in quarterly reporting and allow us to handle a larger volume than was created before. If at the end of the year we want to aggregate everything back into a year rather than a quarter basis we can do this with a merge command
alter table sales
merge partitions p_q1_2016, p_q2_2016, p_q3_2016, p_q45_2016
into partition part_2016;

Fortunately, Enterprise Manager has a partition advisor that looks at the history of your select statements and suggests how you should divide your tables into partitions. It notices that you do a lot of select by state or select by zip code and recommends partitioning by list or by hash based on your usage patterns. This was a new feature added with Enterprise Manager 11 and has gotten more robust and reliable with 13c. We should see a significant speed up if we get the right combination of partitions and indexes and could potentially take a select statement from 45 seconds to sub seconds as shown in the Enterprise Manager screen shots below.

In summary, partitioning is very powerful. It helps you split up your larger tables so that they fit into the memory that you have allocated. The return on investment is difficult to do because the cost for partitioning vs the cost of memory and resulting speed up for queries is hard to measure. Enterprise Manager has tools to help you with this analysis but it is difficult to put into future dollars and what if analysis. It would be nice if you could say that splitting your table into partitions would reduce your buffer cache and allow you to shrink your SGA size by 25%. The tools are not quite there. They do tell you that you can reduce your select times by partitioning the data and predict relatively accurately how much faster a select statement will be with partitioning based on your current hardware configuration. All of these functions should work on Amazon RDS with the exception of manipulating a tablespace. This requires a different command syntax since manipulation of a tablespace requires system access. Typically the command would be alter database default tablespace users2 but with Amazon RDS you have to execute exec rdsadmin.rdsadmin_util.alter_default_tablespace('users2') instead. Given that this is not done very often, it is up to you to decide how and where you deploy your large table database.

Amsterdam : A long weekend that didn’t quite happen.

Tim Hall - Sun, 2016-06-05 16:51

My plan was to spend a long weekend in Amsterdam before heading off to the next conference…

I got off the train at Amsterdam Central and felt hot, very tired, sick and had the start of a headache. By the time I got to my hotel room I was feeling about 3/4 Machu Picchu. I took some Lemsip capsules and crashed out. I woke up about 20 minutes later still feeling bad, but knowing I would survive.

The next day came and went. I spent most of the day in bed. I ventured out of my room to film some bits of the hotel and get some food, which I promptly threw up, then went back to bed. If nothing else, I did manage to edit some videos between bouts of sleep. If you want to see where I spent Friday evening and all day Saturday, check out the video below.

Sunday morning was significantly better. After my Machu Picchu incident last year, a couple from the US gave me some electrolyte replacement sachets. I didn’t use them at the time, but found them in my bag and took one as soon as I got up. I decided to go and have a walk around, but it quickly became apparent I had zero energy. I didn’t feel ill, but just couldn’t walk any sort of distance. After about 2 miles I was totally done.

To make sure the day wasn’t a total right-off, I got onto a Sightseeing Boat and did one of the three possible loops (Green). I then picked up some food and headed back to the hotel. No puking this time, which was good. Once I was sure I wouldn’t be seeing lunch again, I headed back out and did another loop in the boat (Blue). You can see the photos and videos from the day here.

It wasn’t exactly what I had in mind for the weekend, but what ya gonna do?

So tomorrow I have to get a train down to Zeist for the OGH Oracle DBA and SQL Celebration Day on the following day.

Cheers

Tim…

PS. The “My Glamorous Life” title for these type of videos was always meant to be a joke, but in this case it is really funny. There was nothing glamorous about this weekend.

Oracle Database Cloud (DBaaS) Performance Consistency - Part 1

Randolf Geist - Sun, 2016-06-05 15:58
As Oracle ACE Director I got an extended trial license for Oracle's Cloud offerings, in particular the "Database as a Service" offering. As part of the (ongoing) evaluation I try to get an idea how consistent the performance of such an service is, which might be one of the concerns one might have when considering cloud offerings in general.

For my tests I've set up a 11.2.0.4 single instance database using "4 OCPUs" (Oracle CPUs) which ends up as an Oracle Linux 6 system showing 8 CPUs *and* 8 cores of type "Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz".

As edition for the database I've chosen the "Extreme Performance" Enterprise Edition which also shows up at the version banner (note the difference to regular database installations, not sure this might break some applications that don't know this banner):

BANNER
-------------------------------------------------------------------------
Oracle Database 11g EE Extreme Perf Release 11.2.0.4.0 - 64bit Production

I personally find the names of the different available editions not really a good choice - as far as I understand the different editions, although being called "Standard Edition", "Enterprise Edition", "Enterprise Edition - High Performance" and "Enterprise Edition - Extreme Performance" only differentiate in the number of available and usable options, like the Diagnostics and Tuning Pack license or the In-Memory Column Store (12c), but not in the basic performance profile, like CPU speed or I/O performance (although Oracle also has an Exadata Service Cloud offering for higher demands in terms of performance). Note that Oracle also offers a "High Memory" configuration that doubles the available memory for the same number of available CPU cores.
For evaluating the performance consistency I plan to run different flavors of tests on this test environment - the first and most simple one is a straight CPU burning PL/SQL loop spawned as many times as CPUs available (so eight times for this test system here). I deliberately disabled the database Resource Manager for this test.
For comparison I ran exactly the same test on a physical test system of my own to see how the two systems compare in terms of performance consistency. The physical test system is older and slower and only has four cores available, so the test script was only spawned four times in this case.
The loop executed n times concurrently was simply this, and this was kept running for several days to get also an idea if there are noticeable differences on different days of the week:

set echo on

define thread_id = &1;

declare
  n number;
begin
  loop
    n := 0;
    for i in 1..1000000000 loop
      n := n + 1;
    end loop;
    insert into timings(testtype, thread_id, ts) values ('PLSQL', &thread_id, systimestamp);
    commit;
  end loop;
end;
/

These were the results for the DBaaS service:
And these the results for the physical host:

The graph is supposed to show how many of the runs deviated how much from the overall median runtime, so the Y axis represents the percentage, and the X axis represents the deviation from the median runtime, for example 0.5 on the Y axis means 0.5 percent deviation from the median value
Looking at the results the following becomes obvious:
- The physical host provides a slightly more consistent performance (as expected), which means that it has 73% of the runs with 0.5 percent or less deviation from the median runtime, whereas the DBaaS service had "only" 64% - and it should be noted that I tried on that physical system to minimize any "noise" generated by other processes on the system. I didn't attempt to do the same for the DBaaS service, although in principle I could have tried because you get root access for this kind of service and can do whatever you want. For my physical host though in this case here it is interesting to see that there is a much more significant number of runs that deviate more than one percent from the median value, which is not the case for the DBaaS service. This also means if I change above analysis to the number of runs with 1 percent or less deviation from the median runtime, the DBaaS service (85%) actually is more consistent than my physical host (still 73%).
- The DBaaS service has some more and some more extreme outliers, but not really significant
- The overall performance consistency of the DBaaS service is pretty close to the physical host
The same graph on a per day basis (DBaaS service):
and physical host:

- The DBaaS service after a couple of days showed a very stable consistency pattern, only during the first four days the pattern was different:
First days:
Remaining days:
- For this test run no significant differences between different days could be spotted, except for the noticeable difference between the first few days and the remaining days
One interesting point is that in the DBaaS service the different threads showed consistent, but different runtimes, something that couldn't be seen for the physical host, where all threads showed similar runtimes. Whether this might have to do with some NUMA configuration or similar I can't tell, but it is at obvious for this test run - the Y axis represents the median duration per run per day:
DBaas service:
 Physical host:
The next test will be a CPU bound SQL operation that performs logical I/Os only, so no physical I/O involved, whereas as final test run I plan a physical I/O bound test set up.

Challenges in anomaly management

DBMS2 - Sun, 2016-06-05 12:35

As I observed yet again last week, much of analytics is concerned with anomaly detection, analysis and response. I don’t think anybody understands the full consequences of that fact,* but let’s start with some basics.

*me included

An anomaly, for our purposes, is a data point or more likely a data aggregate that is notably different from the trend or norm. If I may oversimplify, there are three kinds of anomalies:

  • Important signals. Something is going on, and it matters. Somebody — or perhaps just an automated system — needs to know about it. Time may be of the essence.
  • Unimportant signals. Something is going on, but so what?
  • Pure noise. Even a fair coin flip can have long streaks of coming up “heads”.

Two major considerations are:

  • Whether the recipient of a signal can do something valuable with the information.
  • How “costly” it is for the recipient to receive an unimportant signal or other false positive.

What I mean by the latter point is:

  • Something that sets a cell phone buzzing had better be important, to the phone’s owner personally.
  • But it may be OK if something unimportant changes one small part of a busy screen display.

Anyhow, the Holy Grail* of anomaly management is a system that sends the right alerts to the right people, and never sends them wrong ones. And the quest seems about as hard as that for the Holy Grail, although this one uses more venture capital and fewer horses.

*The Holy Grail, in legend, was found by 1-3 knights: Sir Galahad (in most stories), Sir Percival (in many), and Sir Bors (in some). Leading vendors right now are perhaps around the level of Sir Kay.

Difficulties in anomaly management technology include:

  • Performance is a major challenge. Ideally, you’re running statistical tests on all data — at least on all fresh data — at all times.
  • User experiences are held to high standards.
    • False negatives are very bad.
    • False positives can be very annoying.
    • Robust role-based alert selection is often needed.
    • So are robust visualization and drilldown.
  • Data quality problems can look like anomalies. In some cases, bad data screws up anomaly detection, by causing false positives. In others, it’s just another kind of anomaly to detect.
  • Anomalies are inherently surprising. We don’t know in advance what they’ll be.

Consequences of the last point include:

  • It’s hard to tune performance when one doesn’t know exactly how the system will be used.
  • It’s hard to set up role-based alerting if one doesn’t know exactly what kinds of alerts there will be.
  • It’s hard to choose models for the machine learning part of the system.

Donald Rumsfeld’s distinction between “known unknowns” and “unknown unknowns” is relevant here, although it feels wrong to mention Rumsfeld and Sir Galahad in the same post.

And so a reasonable summary of my views might be:

Anomaly management is an important and difficult problem. So far, vendors have done a questionable job of solving it.

But there’s a lot of activity, which I look forward to writing about in considerable detail.

Related link

  • The most directly relevant companies I’ve written about are probably Rocana and Splunk.
Categories: Other

5.1 EA

Denes Kubicek - Sun, 2016-06-05 05:08
It is incredibly quiet around APEX release 5.1. Just a couple of days ago I was talking to Dietmar and we were asking ourselves when is APEX 5.1 going to be released because of our training schedule for the rest of the year. On Tuesday this week Markus visited the EA site and this is what he found there. Many exciting things to appear in the next versions. Read the details and you will get an idea about what is comming.
Categories: Development

Pages

Subscribe to Oracle FAQ aggregator