Feed aggregator

Few Steps to use EMC – SAN (LUNs) in Oracle Solaris – Part 01 Unisphere Host Agent?

Online Apps DBA - Mon, 2017-09-11 05:59

Step # 1 : Installation of Unisphere Host Agent. Download and Install the Package, as per OS Version e.g. HostAgent-Solaris-32-sparc-en_US-1.3.9.1.0184-1.pkg # cd /var/share/pkg/repositories # pkgadd -d ./HostAgent-Solaris-32-sparc-en_US-1.3.9.1.0184-1.pkg Make sure that the required package is installed successfully. Step # 2: Add the VNX Storage Servers in the /etc/hosts file. Step # 3: Add the environment specific […]

The post Few Steps to use EMC – SAN (LUNs) in Oracle Solaris – Part 01 Unisphere Host Agent? appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

fetch the latest total value

Tom Kyte - Sun, 2017-09-10 21:06
Hi, We want to fetch the latest total value for emp id = 2 but its summing total_value for all the rows for emp_id = 2. we want the result(total_value) only for the latest row of emp_id = 2. the statements and query are shown as below, cre...
Categories: DBA Blogs

Creation of Composite sub-partitions on multiple columns using template

Tom Kyte - Sun, 2017-09-10 21:06
Hi, Can we create Composite sub-partitions on multiple columns using template Example CREATE TABLE sgs.part_test ( order_date_time SYSTIMESTAMP, modulo_store_id NUMBER, recurring_flag CHAR (1) ) PARTITION BY RANGE (orde...
Categories: DBA Blogs

Why is there a limit on DBMS_OUTPUT?

Tom Kyte - Sun, 2017-09-10 21:06
I always run into an error that the dbms output exceeds 2000 and I can increase to max of 1000000. My question is, why is there a limit on this? I have gigs of space, why does oracle have to be so stingy with the output log?
Categories: DBA Blogs

12c Access Control Lists

Yann Neuhaus - Sun, 2017-09-10 14:39

There is already enough information about the new simplified 12c way to define Access Control Lists, such as in oracle-base.
I’m just posting my example here to show how it is easy.

If, as a non-SYS user you want to connect to a host with TCP, you get an error:

SQL> connect DEMO1/demo@//localhost/PDB1
Connected.
SQL>
SQL>
SQL> declare
2 c utl_tcp.connection;
3 n number:=0;
4 begin
5 c:=utl_tcp.open_connection(remote_host=>'towel.blinkenlights.nl',remote_port=>23);
6 end;
7 /
 
Error starting at line : 27 File @ /media/sf_share/122/blogs/12cacl.sql
In command -
declare
c utl_tcp.connection;
n number:=0;
begin
c:=utl_tcp.open_connection(remote_host=>'towel.blinkenlights.nl',remote_port=>23);
end;
Error report -
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 19
ORA-06512: at "SYS.UTL_TCP", line 284
ORA-06512: at line 5
24247. 00000 - "network access denied by access control list (ACL)"
*Cause: No access control list (ACL) has been assigned to the target
host or the privilege necessary to access the target host has not
been granted to the user in the access control list.
*Action: Ensure that an access control list (ACL) has been assigned to
the target host and the privilege necessary to access the target
host has been granted to the user.
SQL>

Here are the ACLs defined by default:

SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
 
SQL> select * from dba_host_acls;
 
HOST LOWER_PORT UPPER_PORT ACL ACLID ACL_OWNER
---- ---------- ---------- --- ----- ---------
* NETWORK_ACL_4700D2108291557EE05387E5E50A8899 0000000080002724 SYS
 
SQL> select * from dba_host_aces;
 
HOST LOWER_PORT UPPER_PORT ACE_ORDER START_DATE END_DATE GRANT_TYPE INVERTED_PRINCIPAL PRINCIPAL PRINCIPAL_TYPE PRIVILEGE
---- ---------- ---------- --------- ---------- -------- ---------- ------------------ --------- -------------- ---------
* 1 GRANT NO GSMADMIN_INTERNAL DATABASE RESOLVE
* 2 GRANT NO GGSYS DATABASE RESOLVE

So, I add an ACL to access to towel.blinkenlights.nl on telnet port (23) for my user DEMO1:

SQL> exec dbms_network_acl_admin.append_host_ace(host=>'towel.blinkenlights.nl',lower_port=>23,upper_port=>23,ace=>xs$ace_type(privilege_list =>xs$name_list('connect'),principal_name=>'DEMO1',principal_type =>xs_acl.ptype_db));
 
PL/SQL procedure successfully completed.
 
SQL> select * from dba_host_acls;
 
HOST LOWER_PORT UPPER_PORT ACL ACLID ACL_OWNER
---- ---------- ---------- --- ----- ---------
towel.blinkenlights.nl 23 23 NETWORK_ACL_5876ADC67B6635CEE053684EA8C0F378 000000008000281F SYS
* NETWORK_ACL_4700D2108291557EE05387E5E50A8899 0000000080002724 SYS
 
SQL> select * from dba_host_aces;
 
HOST LOWER_PORT UPPER_PORT ACE_ORDER START_DATE END_DATE GRANT_TYPE INVERTED_PRINCIPAL PRINCIPAL PRINCIPAL_TYPE PRIVILEGE
---- ---------- ---------- --------- ---------- -------- ---------- ------------------ --------- -------------- ---------
* 1 GRANT NO GSMADMIN_INTERNAL DATABASE RESOLVE
* 2 GRANT NO GGSYS DATABASE RESOLVE
towel.blinkenlights.nl 23 23 1 GRANT NO DEMO1 DATABASE CONNECT

Now I can connect from my user:

SQL> connect DEMO1/demo@//localhost/PDB1
Connected.
 
SQL> declare
2 c utl_tcp.connection;
3 n number:=0;
4 begin
5 c:=utl_tcp.open_connection(remote_host=>'towel.blinkenlights.nl',remote_port=>23);
6 end;
7 /
 
PL/SQL procedure successfully completed.

If you don’t know why I used towel.blinkenlights.nl, then just try to telnet to it and have fun…

 

Cet article 12c Access Control Lists est apparu en premier sur Blog dbi services.

Thoughts on RMAN Backup Strategy

Michael Dinh - Sun, 2017-09-10 12:56

Do you label your backupset to provide meaningful name?

rman_l0_gosctvna_1_1
rman_arc_h4scu0u7_1_1
rman_arc_h9scu4en_1_1
DBNAME_c-593812450-20170827-07.ctl

For Level 0 backup, do you perform database backup separately from archivelog and tag them differently, e.g. LEVEL0, ARCHIVELOG?

How do you tag your backup?

Do you backup controlfile to trace or backup init parameter or backup password file?

Just some thoughts after have been through hell with restore and lucky came out of hell.

Of course, there are pros and cons to everything.
With too much customization, cannot simply use RMAN configure to set it and forget it.

FYI: 12c syntax for SQL


connect target;
set echo on 
set command id to "LEVEL0";
show all;
run {
set controlfile autobackup format for device type disk to '/oradata/backup/%d_%I_%F.ctl';
allocate channel d1 device type disk format '/oradata/backup/%d_%I_%T_%U.LEVEL0' maxopenfiles 1;
backup as compressed backupset 
incremental level 0 check logical database 
#filesperset 1 tag="LEVEL0"
tag="LEVEL0"
include current controlfile
plus archivelog not backed up 1 times
#filesperset 8 tag="LEVEL0"
tag="LEVEL0"
;
alter database backup controlfile to trace as '/oradata/backup/cf_@.sql' REUSE RESETLOGS;
create pfile='/oradata/backup/init@.ora' from spfile;
create pfile from spfile;
}
report schema;
list backup summary;
exit

 


Reporting Tools and Dashboard for Operations Management

Nilesh Jethwa - Sun, 2017-09-10 12:44

As an operations manager, you play the role of an overseer. You are monitoring the day-to-day operations of your store or business, keeping track of factory efficiency, optimizing logistics and safety. You are practically keeping watch of everything that happens in the business and you find yourself just everywhere.

Being a highly functional entity in your business requires that you have a continuous feed of important information about your business. You need data to tell you everything is running smoothly, or tell you something is not going well. This is where a dashboard can help.

What can a dashboard give you?

With the use of reporting tools, such as dashboards, you can create a data reporting system that can help you have an overall view of your operations whenever you need it.

With a dashboard, you can drill down into important details and identify issues before they spin out of control. Once issues have been identified, you can quickly alert stakeholders about them and help them determine necessary measures to take.

Most dashboards systems are customizable and can be operated through a self-service platform. You can design yours in such a way that you can combine multiple data from different sources and look for correlations among data to help you come up with better decisions for your business.

Operational Dashboards

There are three types of dashboards: strategic, operational and analytic dashboards. The kind of dashboard for people running business’s daily operations is the operational type.

Whereas strategic dashboards are used to monitor key performance indicators and analytic dashboards are used for analyzing huge volumes of data to identify trends and predict outcomes, operational dashboards are used to monitor day-to-day processes.

These processes obviously change minute by minute and the dashboard is designed to capture KPIs and metrics associated with these processes. You expect this type of dashboard to be able to give you very frequent updates. Used to monitor short-time progress towards identified goals, dashboards like this are engineered in such a way that they can be viewed several times during the day giving users different data for each view.

 

Read more at http://www.infocaptor.com/dashboard/reporting-tools-and-dashboard-for-operations-management

Partner Webcast - Oracle Identity Management: Journey to the Cloud

From large organizations to small organizations, a journey to using more cloud-based applications is under way. Oracle extends its leadership in IAM by introducing the next generation...

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

Hey Mum, I am a Citizen Data Scientist with Oracle Data Visualization Cloud (and you can be one too)

Amis Blog - Sun, 2017-09-10 03:42

One of the Oracle Public Cloud Services I have seen mouthwatering demos with but have not actually tried out myself is Oracle Data Visualization Cloud. I had several triggers to at last give it a try – and I am glad I did. In this article a brief report of my first experiences with this cloud service that aims to provide the business user – aka citizen data scientist – with the means that explore data and come up with insights and meaningful visualizations that can easily be shared across the team, department or enterprise.

I got myself a 30-day trial to the cloud service, uploaded a simple Excel document with a publicly available dataset on the countries of the world and started to play around. It turned out to be quite simple – and a lot of fun – to come up with some interesting findings and visualizations. No technical skills required – certainly not any beyond an average Excel user.

Steps:

  • get a trial account – it took about 5 hours from my initial request for a trial to the moment the trial was confirmed and the service had been provisioned
  • enter the Data Viz CS and add a new Data Source (from the Excel file with country data)
  • do a little data preparation (assign attributes to be used as measures) on the data source
  • create a new project; try out Smart Insights for initial data exploration, to get a feel for attributes
  • create some visualizations – get a feel for the data and for what DVCS can do ; many different types of visualizations, various options for filtering, embellishing, highlighting; many dimensions to be included in a visualization
  • try a narrative – a dossier with multiple visualizations, to tell my story

In a few hours, you get a very good feel for what can be done.

 

Create Countries as my new data source

First steps: download countries.csv from https://www.laenderdaten.info/downloads/  . Create Excel workbook from this data. Note: I first tried to upload the raw csv file format, but that ended with an internal error.

 

image

image

The data that was imported is shown. Now is a good moment to set the record straight – any meta data defined at this point is inherited in projects and visualizations that use this data. For example: if we want to calculate with attributes and use them as values – for the size of bubbles and stacks and to plot a line – we have to identify those attributes as measures.

image

Once the data source is set up – we can create our first project based on that data source by simply clicking on it. Note: the PROPOSED_ACTS and ACT_ALBUMS data sources are based on database table in an Oracle DBaaS instance to which I have first created a connection (simply with host, port, service name and username & password).

image

 

My First Project – Data Preparation & Initial Exploration

Here is the Data Preparation page in the project. We can review the data, see what is there, modify the definition of attributes, prescribe a certain treatment (conversion) of data, add (derived) attributes etc.

image

If we click on the Visuals icon in the upper right hand corner, we get a first stab at visualization of some of the data in this data source. Out of the box – just based on how DVCS interprets the data and the various attributes:

image

For example the number of countries per continent. Note how we can select different measures from the dropdownlist – for example area:

image

This tells us that Asia is the largest continent in landmass, followed by Africa, Russia is the largest country and the size of all countries using a currency called Dollar put together is the largest, with Rubel using countries (probably just one) as a runner up. Note: all of this – out of the box. I am 10 minutes into my exploration of DVCS!

 

First Visualizations – Let’s try a Few Things

Go to the second tab – Visualize.

Drag an attribute – or multiple attributes – to the canvas.

image

 

The default visualization is a pivot table that presents the data for the selected attribute. Drag two more attributes to the canvas:

image

 

The result is a matrix of data – with the measure (area) in the cells:

image

In order to prepare the visualization for presentation and sharing, we can do several things – such as removing columns or rows of data that is not relevant, setting a color to highlight a cell:

image

 

Define range filters on selected attributes – for example filter on countries with at least a 45M population or

image

image

When the filter has been set, the matrix adapts:

image

We can try out different styles of visualization – what about a map?

image

image

DVCS recognizes the names of continents and countries as geographical indications and can represent them on a map, using color for total area. Let’s remove continent from the Category dimensions, and let’s set bubble size for population:

image

If we are interested in population density, we can add a calculated value:

image

Some more examples:

Select countries by size per continent – in a horizontal stack chart:

image

and a treemap – with population added in as additional attribute represented through color:

image

 

Any visualization we like and want to include in our final narrative can be saved as an insight:

image

On the Narrate tab – we can include these insights in a meaningful order to tell our story through the data visualizations. Also see Building Stories.

 

Resources

Oracle Data Visualization Cloud Service: https://cloud.oracle.com/en_US/data-visualization  (at $75.00 / Named User / Month with a minimum of 5 users a fairly friendly priced offering)

The country data is downloaded from https://www.laenderdaten.info/downloads/

Documentation on Oracle Data Visualization Cloud Service: http://docs.oracle.com/en/cloud/paas/data-visualization-cloud/bidvc/getting-started-oracle-data-visualization.html 

Documentation on Building Stories: http://docs.oracle.com/en/cloud/paas/data-visualization-cloud/bidvc/building-stories.html#GUID-9D6282AA-C7B7-4F7E-9B9E-873EF8F1FB5D

The post Hey Mum, I am a Citizen Data Scientist with Oracle Data Visualization Cloud (and you can be one too) appeared first on AMIS Oracle and Java Blog.

KPI Dashboards for Retail Industry

Nilesh Jethwa - Sat, 2017-09-09 12:20

The retail industry is highly competitive. It is one of the fastest growing industries worldwide.

Based on the 2015 Global Powers of Retailing Report, the US has

seventy-six of the world’s largest retailing companies. With the growing number of retailers, staying on top will be a tough job.

Retailers who want to rise above their competitors must know all the details about their company. This is where a Key Performance Indicator (KPI) comes to place.

KPIs are helpful in evaluating and monitoring a company’s performance in achieving certain targets or goals. KPIcan also help retailers come up with right business decisions.

Having a KPI dashboard which contains the right data at the right time can help improve the productivity and performance of your company.

While there are many KPIs that a retailer can keep track of, we have compiled ten of the most helpful KPIs for any retail business.

Read more at http://www.infocaptor.com/dashboard/kpi-dashboards-for-retail-industry

Bind capturing

Tom Kyte - Sat, 2017-09-09 08:26
Dear Tom How can I find out if a SQL used bind variable or not? I know you can check v$sql and get a list of distinct SQLs after translating the constants in the predicates. But if it's using bind variable, shouldn't V$SQL_BIND_CAPTURE have that S...
Categories: DBA Blogs

Oracle Database Server Utility UTL_FILE

Tom Kyte - Sat, 2017-09-09 08:26
Dear Mr. Tom, Greetings!. In my NT Server, I want to generate files onto a Mapped network drive. You have already mailed me telling it's impossible on NTOS. But how to accomplish that task?.. Is there any oracle tool to generate files an...
Categories: DBA Blogs

Partner Webcast – Moving Oracle Workloads to the Cloud: Oracle Applications

When customers using E-Business Suite, JD Edwards, PeopleSoft or Siebel move their applications to the Oracle Cloud, they get not only greater enterprise scalability and agility, and cost and risk...

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

Python for the Oracle DBA – Outline/Intro

Bobby Durrett's DBA Blog - Fri, 2017-09-08 18:48

I want to put together a talk about how useful the Python programming language is for an Oracle database administrator or DBA. I thought that I would start by putting my thoughts down in a blog post as a possible starting point for a speech. I think the best way to organize this post is around a series of questions.

First off, why does an Oracle DBA need a programming language? We are not developers. DBAs do backup and recovery, performance tuning, installations, upgrades and patching, etc. We may write the occasional small script or program to help with our work but we are not full-time heads down 40 hours a week plus programmers. A lot of what I do uses simple SQL scripts that I run through Oracle’s SQL*Plus command line utility. A handful of these scripts include Oracle’s PL/SQL programming language so I have done some programming but it is not my full-time job. Our database servers have Unix and Linux shell scripts that do things like exports and trace file cleanups. In addition I have graphical tools like Toad or Oracle Enterprise Manager that I use in my job. I can do most of my job with SQL, PL/SQL, shell scripts, and GUI database tools. Why do a need a general purpose programming language like Python in my role as an Oracle DBA?

A language like Python helps me in my DBA job because it is easy to use and connects to a lot of things.

Why would a smart Oracle database administrator need a programming language that is easy to use? We are very technical people, are we not? I think DBAs need an easy to use programming language because they are not full-time developers. I don’t know about you, but I can only hold so many details in the front of my mind. I have a lot of Oracle database syntax and details rolling around in my head. I know how to join V$SESSION and V$PROCESS without looking it up. That’s not surprising after using Oracle for 20 plus years. But, I can only remember so much. Since programming is not my primary focus I do not think I can remember a language’s details very well. So, I think it makes sense for a DBA to have an easy to use programming language like Python. I can quickly look up details that I forget if it has been a while since I wrote a Python program, so I don’t need all the details in the front of my brain.

What do I mean when I say that Python connects to a lot of things? There are all kinds of libraries or modules that you can use with Python. One of Python’s claims to fame is that you can use it as the glue to tie varies components together. I use a graphics module that helps me make plots of Oracle performance metrics. I get the data using an Oracle database module. My SQL*Plus and Unix shell scripts did not let me connect to a client side graphics library. Plus, I use Python to connect to SQL*Plus and to ssh into Unix systems to run shell scripts. So, Python can connect to pretty much any type of system or resource that I could need in my job as a database administrator. On top of all that, cloud providers such as Amazon Web Services use Python. I have used Python to connect to AWS. Also, I have tested Python with the cloud based Snowflake database. I have also connected Python to a MySQL database. It connects to a lot of stuff! Contrast Python to PL/SQL. PL/SQL is great for Oracle database programming. But it doesn’t connect to other stuff very easily. You aren’t going to connect to Amazon Web Services or to a client side graphics library through PL/SQL. It is easy to connect to these things with Python.

So, to summarize my two points for why Python is a good programming language for Oracle DBAs :

  1. Python is easy to use and DBAs are not full-time programmers.
  2. Python connects to everything a DBA uses.

In my talk I want to go into more depth on each point. What makes Python easy to use? What are some things it connects to? It would be helpful for an audience to see evidence to support each point. I could include code examples or quotes from web sites.

Maybe to keep this post from being excessively long I can make this one an intro or outline and delve into the supporting material in follow-up posts. If anyone reading this has questions or criticisms of this material I would be happy to hear it. I’m presenting my own thoughts about Python’s usefulness in my job based on my experience. If other people have good reasons why Python is not so useful to an Oracle DBA or see problems with my reasoning I would be happy to hear your opinion.

Bobby

 

Categories: DBA Blogs

Oracle E-Business Suite 12.2.7 Now Available

Steven Chan - Fri, 2017-09-08 18:16

I am pleased to announce that Oracle E-Business Suite Release 12.2.7 is now available.

 

Instructions for downloading and applying this latest release update pack (RUP) for the EBS 12.2 codeline can be found here:

What Does Release 12.2.7 Include?

As a consolidated suite-wide patchset, this RUP includes new features, statutory and regulatory updates, and enhancements for stability, performance, and security.

Release 12.2.7 is cumulative. That means that as well as providing new updates for this release, it also includes updates that were originally made available as one-off patches for earlier 12.2 releases.

For a complete list of new features, refer to:

Common Questions and Answers About Upgrading

  • Q: Is there a direct upgrade path from Release 11i to Release 12.2.7?
    A: No. Release 11i customers must first upgrade to Release 12.2 before applying 12.2.7.
  • Q: Is there a direct upgrade path from EBS 12.0 to 12.2.7?
    A: No. Release 12.0 customers must first upgrade to Release 12.2 before applying 12.2.7.
  • Q: Is there a direct upgrade path from EBS 12.2 to 12.2.7?
  • A: Yes. Release 12.2 customers can apply 12.2.7 directly to their
    environments. EBS 12.2.7 is an online patch, so it can be applied while an existing Release 12.2 system is running.
Additional References Related Articles
Categories: APPS Blogs

Digital Transformation Instead of Technology Evolution: Cox Enterprises’ Digital Workplace Success with Oracle WebCenter Portal

WebCenter portal devicesIn 2014, Fishbowl Solutions engaged with Cox Enterprises to build its employee digital workplace. Prior to that engagement, Fishbowl delivered numerous employee, customer, and partner portals/intranets, but this was our first project where the word portal wasn’t being used to describe what Cox Enterprises would be building and delivering to its employees. Instead, the phrase “digital workplace” detailed how Cox envisioned a consumer-like digital experience that promoted collaboration, sparked innovation, and helped employees get their jobs done – regardless of time, space, or device.

Now neither the term nor concept of a digital workplace was new in 2014. Tech vendors and analysts had been discussing such a workplace environment since around 2008, but you may may remember it being called Enterprise 2.0. What stands out to me regarding Enterprise 2.0 was how much collaboration or social capabilities in the workplace became the focus. Such collaboration capabilities as instant messaging, blogs, wikis, and document sharing were thought to be the catalyst for more information sharing, which would lead to more innovation and better employee engagement. However, the place where all this collaboration was supposed to take place – the employee portal or intranet – did not offer the experience or performance that users needed to get work done. Furthermore, the technology and associated features really drove conversations and platform decisions, and not what users needed from the portal or how they wanted to work.

Contrast the above with how Cox Enterprises decided which portal platform they would use for their employee digital workplace. For them, this started with a focus on the workplace they wanted to provide to their employees. A workplace where employees could collaborate and access relevant information from one system – regardless of device. They invested time and money to learn as much about their eventual portal users (personas) before they decided on the technology with the associated features that could support employee work streams and how they would use the portal.

This focus on the user was part of much larger “digital transformation” initiative the company was undertaking. This initiative really centered on making sure Cox’s 50,000 employees, which are scattered across several divisions and geographic locations, were engaged and informed. To enable this, Cox leaders wanted to provide them with a similar experience to access company, department, and personal information. After doing this persona analysis and user flow mapping, they decided that Oracle WebCenter Portal would be the system for their employee digital workplace. They based their decision on WebCenter Portal’s tight integration with WebCenter Content, which was key for their overall digital transformation initiative to consolidate as much content within one system. They also needed a system that could handle 1,500+ concurrent users, and WebCenter’s underlying architecture, including WebLogic Server and Oracle Database, exceeded their performance metrics.

I encourage you to learn more about Cox’s digital transformation initiative by attending the webinar they are partnering with Fishbowl on next Thursday, September 14th. Come hear from Dave Longacre, one of Cox’s project managers for the digital workplace project, detail the vision, steps, and resulting benefits for Cox’s employee digital workplace. Please click on the link below to register. Also, check out our employee digital workplace page on our website for more resources.

Webinar – How Cox Enterprises Built a Digital Workplace for 50,000 Employees using Oracle WebCenter Portal

The post Digital Transformation Instead of Technology Evolution: Cox Enterprises’ Digital Workplace Success with Oracle WebCenter Portal appeared first on Fishbowl Solutions.

Categories: Fusion Middleware, Other

Create constraints in your datawarehouse – why and how

Yann Neuhaus - Fri, 2017-09-08 14:13

We still see some developers not declaring referential integrity constraints in datawarehouse databases because they think they don’t need it (integrity of data has been validated by the ETL). Here is a small demo I did to show why you need to declare them, and how to do it to avoid any overhead on the ETL.

Test case

I create 3 dimension tables and 1 fact table:

21:01:18 SQL> create table DIM1 (DIM1_ID number, DIM1_ATT1 varchar2(20));
Table DIM1 created.
 
21:01:19 SQL> create table DIM2 (DIM2_ID number, DIM2_ATT1 varchar2(20));
Table DIM2 created.
 
21:01:20 SQL> create table DIM3 (DIM3_ID number, DIM3_ATT1 varchar2(20));
Table DIM3 created.
 
21:01:21 SQL> create table FACT (DIM1_ID number, DIM2_ID number, DIM3_ID number,MEAS1 number);
Table FACT created.

I insert 10 million rows into the fact table:

21:01:22 SQL> insert into FACT select mod(rownum,3),mod(rownum,5),mod(rownum,10),rownum from xmltable('1 to 10000000');
10,000,000 rows inserted.
 
Elapsed: 00:00:18.983

and fill the dimension tables from it:

21:01:42 SQL> insert into DIM1 select distinct DIM1_ID,'...'||DIM1_ID from FACT;
3 rows inserted.
 
Elapsed: 00:00:01.540
 
21:01:52 SQL> insert into DIM2 select distinct DIM2_ID,'...'||DIM2_ID from FACT;
5 rows inserted.
 
Elapsed: 00:00:01.635
 
21:01:57 SQL> insert into DIM3 select distinct DIM3_ID,'...'||DIM3_ID from FACT;
10 rows inserted.
 
Elapsed: 00:00:01.579
 
21:01:58 SQL> commit;
Commit complete.

Query joining fact with one dimension

I’ll run the following query:

21:01:58 SQL> select count(*) from FACT join DIM1 using(DIM1_ID);
 
COUNT(*)
--------
10000000
 
Elapsed: 00:00:01.015

Here is the execution plan:

21:02:12 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 4pqjrjkc7sn17, child number 0
-------------------------------------
select count(*) from FACT join DIM1 using(DIM1_ID)
 
Plan hash value: 1826335751
 
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7514 (100)| |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10M| 253M| 7514 (2)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DIM1 | 3 | 39 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| FACT | 10M| 126M| 7482 (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Actually, we don’t need that join. A dimension table has two goals:

  • filter facts on the dimension attributes. Example: filter on customer last name
  • add dimension attributes to the result. Example: add customer first name

Here, there is no WHERE clause on DIM1 columns, and no columns from DIM1 selected. We don’t need to join to DIM1. However, we often see those useless joins for two reasons:

  • We query a view that joins the fact with all dimensions
  • The query is generated by a reporting tool which always join to dimensions
Join elimination

The Oracle optimizer is able to remove those kinds of unnecessary joins. But one information is missing here for the optimizer. We know that all rows in the fact table have a matching row in each dimension, but Oracle doesn’t know that. And if there is no mathing row, then the inner join should not return the result. For this reason, the join must be done.

Let’s give this information to the optimizer: declare the foreign key from FACT to DIM1 so that Oracle knows that there is a many-to-one relationship:

21:02:17 SQL> alter table DIM1 add constraint DIM1_PK primary key(DIM1_ID);
Table DIM1 altered.
 
Elapsed: 00:00:00.051
 
21:02:20 SQL> alter table FACT add constraint DIM1_FK foreign key(DIM1_ID) references DIM1;
Table FACT altered.
 
Elapsed: 00:00:03.210

I’ve spent 3 seconds here to create this foreign key (would have been much longer with a real fact table and lot of columns and rows) but now, the optimizer is able to eliminate the join:

21:02:24 SQL> select count(*) from FACT join DIM1 using(DIM1_ID);
 
COUNT(*)
--------
10000000
 
21:02:25 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 4pqjrjkc7sn17, child number 0
-------------------------------------
select count(*) from FACT join DIM1 using(DIM1_ID)
 
Plan hash value: 3735838348
 
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7488 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| FACT | 10M| 126M| 7488 (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter("FACT"."DIM1_ID" IS NOT NULL)
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

No join needed here, the query is faster. This is exactly the point of this blog post: to show you that declaring constraints improve performance of queries. It adds information to the optimizer, like statistics. Statistics gives estimated cardinalities. Foreign keys are exact cardinality (many-to-one).

No validate

When loading a datawarehouse, you usually don’t need to validate the constraints because data was bulk loaded from a staging area where all data validation has been done. You don’t want to spend time validating constraints (the 3 seconds in my small example above) and this is why some datawarehouse developers do not declare constraints.

However, we can declare constraints without validating them. Let’s do that for the second dimension table:

21:02:34 SQL> alter table DIM2 add constraint DIM2_PK primary key(DIM2_ID) novalidate;
Table DIM2 altered.
 
Elapsed: 00:00:00.018
%nbsp;
21:02:35 SQL> alter table FACT add constraint DIM2_FK foreign key(DIM2_ID) references DIM2 novalidate;
Table FACT altered.
 
Elapsed: 00:00:00.009

That was much faster than the 3 seconds we had for the ‘validate’ constraint which is the default. Creating a constraint in NOVALIDATE is immediate and do not depend on the size of the table.

However this is not sufficient to get the join elimination:

21:02:39 SQL> select count(*) from FACT join DIM2 using(DIM2_ID);
 
COUNT(*)
--------
10000000
 
21:02:40 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 4t9g2n6duw0jf, child number 0
-------------------------------------
select count(*) from FACT join DIM2 using(DIM2_ID)
 
Plan hash value: 3858910383
 
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7518 (100)| |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | HASH JOIN | | 10M| 253M| 7518 (2)| 00:00:01 |
| 3 | INDEX FULL SCAN | DIM2_PK | 5 | 65 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| FACT | 10M| 126M| 7488 (1)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

The constraint ensures that no rows will be inserted without a matching row in the dimension. However, because Oracle has not validated the result itself, it does not apply the join elimination, just in case a previously existing row has no matching dimension.

Rely novalidate

If you want the optimizer to do the join elimination on a ‘novalidate’ constraint, then it has to trust you and rely on the constraint you have validated.

RELY is an attribute of the constraint that you can set:

21:02:44 SQL> alter table DIM2 modify constraint DIM2_PK rely;
Table DIM2 altered.
 
Elapsed: 00:00:00.016
 
21:02:45 SQL> alter table FACT modify constraint DIM2_FK rely;
Table FACT altered.
 
Elapsed: 00:00:00.010

But this is not sufficient. You told Oracle to rely on your constraint, but Oracle must trust you.

Trusted

The join elimination is a rewrite of the query and, by default, rewrite is enabled but only when integrity is enforced by Oracle:

21:02:50 SQL> show parameter query_rewrite
NAME TYPE VALUE
----------------------- ------ --------
query_rewrite_enabled string TRUE
query_rewrite_integrity string ENFORCED

Let’s allow our session to have rewrite transformations to trust our RELY constraints:

21:02:52 SQL> alter session set query_rewrite_integrity=trusted;
Session altered.

Now, joining to DIM2 without using DIM2 columns outside of the join allows join elimination:

21:02:57 SQL> select count(*) from FACT join DIM2 using(DIM2_ID);
 
COUNT(*)
--------
10000000
 
Elapsed: 00:00:00.185
21:02:58 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 4t9g2n6duw0jf, child number 0
-------------------------------------
select count(*) from FACT join DIM2 using(DIM2_ID)
 
Plan hash value: 3735838348
 
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7494 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| FACT | 10M| 126M| 7494 (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter("FACT"."DIM2_ID" IS NOT NULL)
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- rely constraint used for this statement

In 12.2 the execution plan has a note to show that the plan depends on RELY constraint.

From this example, you can see that you can, and should, create RELY NOVALIDATE constraints on tables where you know the existing data is valid. They are immediately created, without any overhead on the load process, and helps to improve queries generated on your dimensional model.

Rely Disable

I said that a NOVALIDATE constraint has no overhead when created, but you may have further inserts or updates in your datawarehouse. And then, those constraints will have to be verified and this may add a little overhead. In this case, you can go further and disable the constraint:

21:03:04 SQL> alter table DIM3 add constraint DIM3_PK primary key(DIM3_ID) rely;
Table DIM3 altered.
 
Elapsed: 00:00:00.059
 
21:03:05 SQL> alter table FACT add constraint DIM3_FK foreign key(DIM3_ID) references DIM3 rely disable novalidate;
Table FACT altered.
 
Elapsed: 00:00:00.014

Note that I had to set the referenced constraint DIM3_PK to RELY here, even if it is enable and validate, or I would get: ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY.

My session still trusts RELY constraints for query rewrite:

21:03:07 SQL> show parameter query_rewrite
 
NAME TYPE VALUE
----------------------- ------ -------
query_rewrite_enabled string TRUE
query_rewrite_integrity string TRUSTED

Now, the join elimination occurs:

21:03:08 SQL> select count(*) from FACT join DIM3 using(DIM3_ID);
 
COUNT(*)
--------
10000000
 
21:03:09 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 3bhs523zyudf0, child number 0
-------------------------------------
select count(*) from FACT join DIM3 using(DIM3_ID)
 
Plan hash value: 3735838348
 
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7505 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| FACT | 11M| 138M| 7505 (1)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter("FACT"."DIM3_ID" IS NOT NULL)
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- rely constraint used for this statement

So, we can still benefit from the query optimization even with the RELY DISABLE NOVALIDATE.

But I would not recommend this. Be careful. Here are my foreign key constraints:

21:03:15 SQL> select table_name,constraint_type,constraint_name,status,validated,rely from all_constraints where owner='
DEMO' and table_name='FACT' order by 4 desc,5 desc,6 nulls last;
 
TABLE_NAME CONSTRAINT_TYPE CONSTRAINT_NAME STATUS VALIDATED RELY
---------- --------------- --------------- ------ --------- ----
FACT R DIM1_FK ENABLED VALIDATED
FACT R DIM2_FK ENABLED NOT VALIDATED RELY
FACT R DIM3_FK DISABLED NOT VALIDATED RELY

For DIM1_FK and DIM2_FK the constraints prevent us from inconsistencies:

21:03:17 SQL> insert into FACT(DIM1_ID)values(666);
 
Error starting at line : 1 in command -
insert into FACT(DIM1_ID)values(666)
Error report -
ORA-02291: integrity constraint (DEMO.DIM1_FK) violated - parent key not found

But the disabled one will allow inconsistencies:

21:03:19 SQL> insert into FACT(DIM3_ID)values(666);
1 row inserted.

That’s bad. I rollback this immediately:

21:03:20 SQL> rollback;
Rollback complete.

Star transformation

Join elimination is not the only transformation that needs to know about the many-to-one relationship between fact tables and dimensions. You usually create a bitmap index on each foreign key to the dimension, to get the higher selectivity when looking at the table rows from the combination of criteria on the dimension attributes.

21:03:24 SQL> create bitmap index FACT_DIM1 on FACT(DIM1_ID);
Index FACT_DIM1 created.
 
21:03:29 SQL> create bitmap index FACT_DIM2 on FACT(DIM2_ID);
Index FACT_DIM2 created.
 
21:03:33 SQL> create bitmap index FACT_DIM3 on FACT(DIM3_ID);
Index FACT_DIM3 created.

Here is the kind of query with predicates on each dimension attributes:

21:03:35 SQL> select count(*) from FACT
2 join DIM1 using(DIM1_ID) join DIM2 using(DIM2_ID) join DIM3 using(DIM3_ID)
3 where dim1_att1='...0' and dim2_att1='...0' and dim3_att1='...0';
 
COUNT(*)
--------
333333

By default, the optimizer applies those predicates on the dimension and do a cartesian join to get all accepted combinations of dimension IDs. Then the rows can be fetched from the table:

21:03:37 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 01jmjv0sz1dpq, child number 0
-------------------------------------
select count(*) from FACT join DIM1 using(DIM1_ID) join DIM2
using(DIM2_ID) join DIM3 using(DIM3_ID) where dim1_att1='...0' and
dim2_att1='...0' and dim3_att1='...0'
 
Plan hash value: 1924236134
 
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5657 (100)| |
| 1 | SORT AGGREGATE | | 1 | 114 | | |
| 2 | NESTED LOOPS | | 55826 | 6215K| 5657 (1)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 1 | 75 | 9 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 50 | 6 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DIM1 | 1 | 25 | 3 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 1 | 25 | 3 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | DIM2 | 1 | 25 | 3 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 1 | 25 | 6 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | DIM3 | 1 | 25 | 3 (0)| 00:00:01 |
| 10 | BITMAP CONVERSION COUNT | | 55826 | 2126K| 5657 (1)| 00:00:01 |
| 11 | BITMAP AND | | | | | |
|* 12 | BITMAP INDEX SINGLE VALUE| FACT_DIM3 | | | | |
|* 13 | BITMAP INDEX SINGLE VALUE| FACT_DIM2 | | | | |
|* 14 | BITMAP INDEX SINGLE VALUE| FACT_DIM1 | | | | |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
5 - filter("DIM1"."DIM1_ATT1"='...0')
7 - filter("DIM2"."DIM2_ATT1"='...0')
9 - filter("DIM3"."DIM3_ATT1"='...0')
12 - access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")
13 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
14 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")

Here rows are fetched from the fact table through a nested loop from the cartesian join on the dimensions, using the bitmap index access for each loop. If there are lot of rows to fetch, then the optimizer will chose a hash join and then will have to full scan the fact table, which is expensive. To lower that cost, the optimizer can add a ‘IN (SELECT DIM_ID FROM DIM WHERE DIM_ATT)’ for very selective dimensions. This is STAR transformation and relies on the foreign key constraints.

It is not enabled by default:

21:03:43 SQL> show parameter star
NAME TYPE VALUE
---------------------------- ------- -----
star_transformation_enabled string FALSE

We can enable it and then it is a cost based transformation:

21:03:45 SQL> alter session set star_transformation_enabled=true;
Session altered.

Here is my example:

21:03:47 SQL> select count(*) from FACT
2 join DIM1 using(DIM1_ID) join DIM2 using(DIM2_ID) join DIM3 using(DIM3_ID)
3 where dim1_att1='...0' and dim2_att1='...0' and dim3_att1='...0';
 
COUNT(*)
--------
333333

The star transformation, changing a join to an ‘IN()’ is possible only when we know that there is a many-to-one relationship. We have all constraints for that, disabled or not, validated or not, but all in RELY. Then Star Transformation can occur:

21:03:51 SQL> select * from dbms_xplan.display_cursor();
 
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 01jmjv0sz1dpq, child number 1
-------------------------------------
select count(*) from FACT join DIM1 using(DIM1_ID) join DIM2
using(DIM2_ID) join DIM3 using(DIM3_ID) where dim1_att1='...0' and
dim2_att1='...0' and dim3_att1='...0'
 
Plan hash value: 1831539117
 
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 68 (100)| |
| 1 | SORT AGGREGATE | | 1 | 38 | | |
|* 2 | HASH JOIN | | 2 | 76 | 68 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DIM2 | 1 | 25 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_ST_62BA0C91 | 8 | 104 | 65 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 8 | 608 | 56 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION TO ROWIDS| | 8 | 427 | 22 (5)| 00:00:01 |
| 7 | BITMAP AND | | | | | |
| 8 | BITMAP MERGE | | | | | |
| 9 | BITMAP KEY ITERATION | | | | | |
|* 10 | TABLE ACCESS FULL | DIM1 | 1 | 25 | 3 (0)| 00:00:01 |
|* 11 | BITMAP INDEX RANGE SCAN| FACT_DIM1 | | | | |
| 12 | BITMAP MERGE | | | | | |
| 13 | BITMAP KEY ITERATION | | | | | |
|* 14 | TABLE ACCESS FULL | DIM2 | 1 | 25 | 3 (0)| 00:00:01 |
|* 15 | BITMAP INDEX RANGE SCAN| FACT_DIM2 | | | | |
| 16 | BITMAP MERGE | | | | | |
| 17 | BITMAP KEY ITERATION | | | | | |
|* 18 | TABLE ACCESS FULL | DIM3 | 1 | 25 | 3 (0)| 00:00:01 |
|* 19 | BITMAP INDEX RANGE SCAN| FACT_DIM3 | | | | |
| 20 | TABLE ACCESS BY USER ROWID | FACT | 1 | 25 | 43 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - access("ITEM_1"="DIM2"."DIM2_ID")
3 - filter("DIM2"."DIM2_ATT1"='...0')
10 - filter("DIM1"."DIM1_ATT1"='...0')
11 - access("FACT"."DIM1_ID"="DIM1"."DIM1_ID")
14 - filter("DIM2"."DIM2_ATT1"='...0')
15 - access("FACT"."DIM2_ID"="DIM2"."DIM2_ID")
18 - filter("DIM3"."DIM3_ATT1"='...0')
19 - access("FACT"."DIM3_ID"="DIM3"."DIM3_ID")
 
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- star transformation used for this statement
- this is an adaptive plan

Here, each dimension drives a range scan on the bitmap index: the predicate on the dimension table returns the dimension ID for the the index lookup on the fact table. The big advantage of bitmap indexes here is that when this access path is used for several dimensions, the bitmap result can be combined before going to the table. This transformation avoids the join and then you must be sure that there is a many-to-one relationship.

In summary

As you should rely on the integrity of data in your datawarehouse, you should find the following parameters to query on fact-dimension schemas:

NAME TYPE VALUE
---------------------------- ------- ------
query_rewrite_enabled string TRUE
query_rewrite_integrity string TRUSTED
star_transformation_enabled string FALSE

And you should define all constraints. When you are sure about the integrity of data, then those constraints can be created RELY ENABLE NOVALIDATE. If some processing must be optimized by not enforcing the constraint verification, then you may create those constraints as RELY DISABLE NOVALIDATE but the gain will probably minimal, and the risk high. But remember that there are not only the well-controlled processes which update data. You may have one day to do a manual update to fix something, and enabled constraint can prevent terrible errors.

I have not covered all optimizer transformations that rely on constraints. When using materialized views you, the rewrite capability also relies on constraints. Relationship cardinality is one of the most important information of database design, this information must be known by the optimizer.

 

Cet article Create constraints in your datawarehouse – why and how est apparu en premier sur Blog dbi services.

Need to generate the output in incremental loops

Tom Kyte - Fri, 2017-09-08 14:06
Hi Tom, I have a query which returns the below output. ONE TWO THREE I need to get the out as ONE ONE TWO ONE TWO THREE ONE TWO THREE FOUR i.e like incremental sets (by step 1). ONE ONE TWO ONE TWO THREE ONE ...
Categories: DBA Blogs

When will a datafile increment

Tom Kyte - Fri, 2017-09-08 14:06
Hi Team, I have checked the tablespace is autoextend on and i wanted to checked - Q.1 When it will grow ? Q.2 How much it will grow ? Q.3 Can we do anytime auotextend ON or Off in live production ? Will it impact on DB ? Q.4 If the size of au...
Categories: DBA Blogs

Dataguard setup

Tom Kyte - Fri, 2017-09-08 14:06
I have One PC and I also install VMWare on my pc. So I have oracle database 12c on my pc. I want to setup dataguard in my vmware workspace. Is it possible to setup dataguard environment from my pc to vmware pc also same in my pc.
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator