Skip navigation.

DBA Blogs

Log Buffer #447: A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2015-11-02 09:34

 

This Log Buffer Edition covers the weekly blog posts of Oracle, SQL Server and MySQL.

Oracle:

  • An Index or Disaster, You Choose (It’s The End Of The World As We Know It).
  • SQL Monitoring in Oracle Database 12c.
  • RMAN Full Backup vs. Level 0 Incremental.
  • Auto optimizer stats after CTAS or direct loads in #Oracle 12c.
  • How to move OEM12c management agent to new location.

SQL Server:

  • Automate SQL Server Log Monitoring.
  • 10 New Features Worth Exploring in SQL Server 2016.
  • The ABCs of Database Creation.
  • Top 10 Most Common Database Scripts.
  • In-Memory OLTP Table Checkpoint Processes Performance Comparison.

MySQL:

  • The Client Library, Part 1: The API, the Whole API and Nothing but the API.
  • Performance of Connection Routing plugin in MySQL Router 2.0.
  • MariaDB 10.0.22 now available.
  • Semi-synchronous Replication Performance in MySQL 5.7.
  • MySQL and Trojan.Chikdos.A.

 

Learn more about Pythian’s expertise in Oracle SQL Server & MySQL.

Categories: DBA Blogs

SSL Support

Hemant K Chitale - Mon, 2015-11-02 00:45
This blog now supports SSL connections.

Browse this blog using https://   instead of http://   for a secure connection when accessing a specific page.

...
Categories: DBA Blogs

Does your business need to become data-driven?

Pythian Group - Sun, 2015-11-01 18:52

Find out how the experts answered at Pythian’s Velocity of Innovation event in NYC

This summer I had the pleasure of moderating panel discussion that brought some of IT’s most innovative thought leaders together with some of North America’s top CIOs. This is the second in our series of posts that outline the discussions that took place at our Velocity of Innovation event this summer in New York. Our panel of experts consisted of: Paul Vallé, Pythian’s founder and CEO; Gene Leganza, vice-president, principal analyst serving enterprise architecture professionals at Forrester Research; and Otto Toth, CTO at Huffington Post. Event attendees included IT leaders from across a range of industries who supplied the questions that formed the discussion.

This series of blog posts focuses on topics covered in the New York Velocity of Innovation discussion this past summer. This post concentrates on a discussion between Gene and Paul about the importance of data as a driver of success. The question was: Do we think that every organization must become data-driven or is this just for customer facing marketing organizations?”

Here’s just a sample of the discussion:
Paul: Gene is one of the world’s leading experts on that exact subject. I’m just dying to hear what he’s going to say about this.

Gene: I’m a believer. It’s a really interesting space. But the question is how to get there, and the culture that needs to exist. I’ve had a lot of discussions with vendors doing interesting things with data. It’s funny, if I ask what their software technology does — how it’s being implemented, and the success their customer are having — somehow they always end up saying, “Well, but that’s a technology, the culture is a lot slower to change.”
Somehow the culture needs to change in order to implement a lot of these technologies and make organizations really data-driven. It is not something that’s going to change overnight. It’s largely why, I think, organizations are not yet embracing the notion of a chief data officer role. When I first did surveys on this maybe two years ago, four or five percent of organizations said they had or were planning on hiring a chief data officer in the next year. Now, it’s up to 50 to 60 percent depending on the industry and region. People don’t always do what they say they’re going to do in surveys. But I think what’s behind these responses is an understanding that someone has to lead the charge, not only on the technology and analytic strategy side, but on the cultural side — making the move to change the organization.

There’s so much data out there, and increasingly so, that there’s probably data to support any decision you want to make relating to your business. If you’re not using data to make the decision, you’re just going to be behind the curve. For a while, you can say, “Oh, we have really smart people who have been in the industry forever. They go by their gut and I really trust that, and that’s really cool.” That will be good a little while longer, but sooner or later as your competition starts getting better at using data to understand what to do and get predictive about what to do, you’re just going to be behind the curve. It takes a while to get there so I think people need to get started now.

When you’re writing business decisions, having business conversations, wondering which way the market was going, what people are buying, what the competition is buying, what your customer friends’ friends are buying, are all things you can now find out. Making assumptions about that is really unfounded. I mean, there’s just too much data. It’s really a race to see who can be better at finding the relevant information and getting the answers out of data than the next guy. It’s what it’s coming down to, making it easier and easier for the business people to do that without having to ask IT.

The old school here is if you need to analyze something, you need to ask IT to provision something in the data warehouse so we can do analytics. Now, you need to play with the data for a while before you know what you really want to look at. The ability to monkey with data from multiple sources to explore the answers to questions has become easy to do. If you don’t do it, your competition will, and you’ll be behind the curve. I think that’s really just the bottom line.
But also, you have to be an evangelist and a cheerleader. Being opportunistic and having a cheerleader explicitly lead the charge to get the people to think differently about it so that you can eventually change processes. It eventually gets done.

I recently I talked with someone who felt extremely behind the curve and they only recently did things like looking at data design as integral to the design phase of their system development. They had always, of course, reviewed design of the applications before implementing them, but data was always an afterthought. Applications create data, so what?

Now that they’re trying to get better at data, they had to actually explicitly make sure you got a data person in there designing the data, looking at the impact of what this application’s doing with the landscape or the information architecture they have and reviewing that before going off and actually writing a code.
That was new for them. It’s important for them that they instantiate that in the process. That starts to win over the hearts and minds of the developers who are doing this sort of stuff. In terms of getting all the various players to get it and do their part, using that data instead of flying off the seat of their pants, now we have a lot of socializing and conversation, but baking data-driven things into processes.

It’s not something that you can do upfront and necessarily just expect a process to take over because then it looks like bureaucracy and people hate you for slowing down their life. You have to be very careful about introducing ideas into the organization and then try to bake them into the fabric of the organization before people see the actual value and they’re actually bought into the concept.

Paul: I have something to chime in, it’s actually in contradiction to Gene, but it will prompt, I think, an interesting discussion right along the lines of your question.

I find that one of the nicest ways to look at a business at a holistic level is to put a lens in front of it. I’m just going to use Fresh Direct as an example because I’m in New York City and I’m seeing the trucks all the time. You all live here, you might order their groceries. It’s useful to think of FreshDirect and look at the business through the lens of a grocer. You’re going to talk about the thickness of the steaks, you’re going to talk about the freshness of the vegetables and that’s great. It’s a great way to look at that business. You can also look at that business and think of it as a technology business. To what degree is FreshDirect a technology business that happens to deliver groceries the way Amazon.com is a technology business that happens to deliver books?

That’s a useful way to look at the business. To me, this whole data idea and the chief data scientist idea is the new lens and that’s what’s powerful about it. It lets you look at FreshDirect and say, “Hey, to what degree is Fresh Direct a data business?”

As for Huffington Post, it’s a media business. You need to be able to look through the media lens to you look at the quality of the content. You need to look at your reach and your audience, but to what degree is the Huffington Post a technology business? That’s a new lens. To what degree is the Huffington Post a data business?

That’s where sometimes, not always and not for every business, but sometimes you realize, “Holy cow! I am a data business.” Sometimes you realize that the most game-changing investments that you can make in your business involve adopting insights that emerge from the data. If you look at Fresh Direct and you say, “Well, Fresh Direct is a logistics business.” It is. Maybe when you look at Fresh Direct through that lens, you have a ‘holy cow’ moment and you realize that the investment in your supply chain management or your logistics allows you to compete better. That’s where your most efficient investments are. That’s okay.
What’s powerful about data is that we never used to talk about the power of our data as a lens through which to look at our businesses holistically and then to quantify our investments. To rephrase your question, I think it’s a really good question, but I wanted to just disagree with Gene just a little bit. I don’t know whether the crowd will lie, but in general, you should listen to Gene.

I think one of the interesting twist on your question is which of us, which of you are looking at your businesses through the data lens and seeing opportunities for investments that you feel that pressure in your belly, in your gut that you need to claim that opportunity?

Attendee # 1: Data can make an enormous difference to your business. Deciding which data to pay attention to, which to collect, where to source data, and what insights you’re looking for is almost more of an art than a science at this point.
That’s why the whole data scientist’s movement, it’s like the most sought-after position, it’s a huge growth space and it’s because there isn’t any prescriptive method. You have to bring judgement to the table. Choosing the data that’s important is probably the harder part right now. If you choose the wrong data, then it doesn’t make sense.

Attendee # 2: I think you need to invest not just in good talent, but also in good knowledge and optimization especially for a legacy business model.

Gene: That’s a really big challenge. We just got some results from a survey that asked what biggest obstacle was to getting more data-driven? There were a bazillion answers that all have some weight. At the top of the list is hiring the right data talent. Obviously, that’s a big deal for everybody. It’s not just data scientists. It’s always interesting to look at how you define what a data scientist is. The best definition of data scientist is somebody that would totally impossible to find because they would bring together PhD level education, a hard science level of math and algorithm knowledge, combined with the skills of a software engineer. All this would be combined with someone who has a domain knowledge we were just talking about it, it’s not just numbers.

This is just a sample of the our discussion on data at the New York event this summer. More of these sessions are planned for the coming weeks. To request an invitation to a Velocity of Innovation event in a city near you, visit http://www.pythian.com/velocity-of-innovation-2015/.

Velocity of Innovation is a series of thought-leadership events for senior IT management hosted by Pythian.The format is a panel discussion in which Pythian leads conversations around today’s disruptive technologies. Topics range from big data and cloud to advanced analytics and DevOps. These events are by invitation only. If you’re interested in attending one of our upcoming Velocity of Innovation events, contact events@pythian.com

Categories: DBA Blogs

Trace Files -- 6 : Multiple Executions of the same SQL

Hemant K Chitale - Sun, 2015-11-01 08:06
In the previous two posts, I demonstrated a single execution of an SQL query and interpreting the raw trace and the tkprof summary for it.  The demonstration also showed the Hard Parsing for the first execution of the query.

What if
(a) We have multiple executions of the same SQL ?  Likely the first execution, if the Plan doesn't already exist in the Shared Pool, requires a Hard Parse  (Also, the first execution immediately after enabling Tracing itself requires a Hard Parse).
(b) Row Counts change for subsequent executions.
(c) Something happens that causes a fresh Hard Parse for a subsequent execution.  That something could be a DDL against the source table causing Invalidation of the SQL.  Possibly even a change of the Execution Plan


I'll create a demo here.  I am taking exactly the same SQL as in the previous demo.

SQL> connect hr/oracle
Connected.
SQL> exec DBMS_SESSION.SESSION_TRACE_ENABLE(waits=>TRUE,binds=>FALSE);

PL/SQL procedure successfully completed.

SQL> select count(*) from employees;

COUNT(*)
----------
107

SQL> select count(*) from employees;

COUNT(*)
----------
214

SQL> select count(*) from employees;

COUNT(*)
----------
214

SQL> exec DBMS_SESSION.SESSION_TRACE_DISABLE;

PL/SQL procedure successfully completed.

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3119.trc

SQL>


What are the changes I made for the second and third executions ?  Quite obviously, I have duplicated the number of rows before the second execution of the query.  Then, some other change before the third execution.
These are the two changes made from another session :

SQL> insert into employees
2 select employee_id+1000, first_name||'_A', last_name||'_B', email||'.XYZ', phone_number, hire_date+1, job_id, salary, commission_pct,
3 manager_id, department_id
4 from employees
5 /

107 rows created.

SQL> commit;

Commit complete.

SQL> alter table employees drop constraint emp_email_uk;

Table altered.

SQL> drop index emp_email_uk;
drop index emp_email_uk
*
ERROR at line 1:
ORA-01418: specified index does not exist


SQL>


The first change added 107 rows to the table. The second change dropped the index that was being used by the initial Execution Plan.

Let's look at a tkprof of the trace file.  Here, I use the option aggregate=NO to separately report each execution of the query.

[oracle@ora11204 Desktop]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3119.trc \
> My_3_Queries.PRF aggregate=NO sys=NO

TKPROF: Release 11.2.0.4.0 - Development on Sun Nov 1 21:27:15 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


[oracle@ora11204 Desktop]$


The aggregate=NO separates the three executions of the same SQL_ID. Extracting the lines from the tkprof, we see that each individual execution is reported separately

SQL ID: 7c1rnh08dp922 Plan Hash: 3580537945

select count(*)
from
employees


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 1 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 1 1 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=1 pr=1 pw=0 time=235 us)
107 107 107 INDEX FULL SCAN EMP_EMAIL_UK (cr=1 pr=1 pw=0 time=412 us cost=1 size=0 card=107)(object id 16404)


SQL ID: 7c1rnh08dp922 Plan Hash: 3580537945

select count(*)
from
employees


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 1 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 1 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=1 pr=0 pw=0 time=66 us)
214 214 214 INDEX FULL SCAN EMP_EMAIL_UK (cr=1 pr=0 pw=0 time=447 us cost=1 size=0 card=107)(object id 16404)


SQL ID: 7c1rnh08dp922 Plan Hash: 1426549735

select count(*)
from
employees


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 1 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 1 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=1 pr=0 pw=0 time=63 us)
214 214 214 INDEX FULL SCAN EMP_EMP_ID_PK (cr=1 pr=0 pw=0 time=554 us cost=1 size=0 card=107)(object id 16405)



We can see that the first execution read 107 Rows (ROWIDs) from the Index EMP_EMAIL_UK (a Unique Key Index on the EMAIL_ADDRESS).  This also matched the expected cardinality of 107 rows (shown by "card=107").

However, the second execution, reading the same EMP_EMAIL_UK index and expected cardinality of 107 actually read 214 Rows (ROWIDs).  (Because of the additional 107 rows inserted and committed by the separate session)

The third execution used a different Index EMP_EMP_ID_PK (because the EMP_EMAIL_UK index was no longer present).  Here, too, there was a mismatch between the expected cardinality ("card=107") and the actual Rows (ROWIDs) count of 214.

The mismatch between cardinality and actual Rows (ROWIDs) from the indexes in the 2nd and 3rd executions occur because although 107 new rows are inserted, index statistics are not updated.

Did you also note that the second execution had a Parse and a "Misses in library cache during parse:" ?  The trace file (not presented in this post) shows that the cursor for the first execution had been closed but does not show Hard Parsing (no recursive SQLs).  That parse was a Soft Parse.



What if I had *NOT* used the aggregate=NO option in tkprof ?  The default is aggregate=YES.  This is what it would present  (I can take the same raw trace file and generate a new tkprof output file) :

[oracle@ora11204 Desktop]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3119.trc \
> My_3_Queries_AGGREGATED.PRF sys=NO

TKPROF: Release 11.2.0.4.0 - Development on Sun Nov 1 21:51:51 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


[oracle@ora11204 Desktop]$

SQL ID: 7c1rnh08dp922 Plan Hash: 3580537945

select count(*)
from
employees


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 4 0.00 0.00 1 2 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 1 2 0 2

Misses in library cache during parse: 2
Optimizer mode: ALL_ROWS
Parsing user id: 43
Number of plan statistics captured: 2

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=1 pr=0 pw=0 time=150 us)
107 160 214 INDEX FULL SCAN EMP_EMAIL_UK (cr=1 pr=0 pw=0 time=430 us cost=1 size=0 card=107)(object id 16404)


SQL ID: 7c1rnh08dp922 Plan Hash: 1426549735

select count(*)
from
employees


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 1 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 1 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=1 pr=0 pw=0 time=63 us)
214 214 214 INDEX FULL SCAN EMP_EMP_ID_PK (cr=1 pr=0 pw=0 time=554 us cost=1 size=0 card=107)(object id 16405)



Here it shows the SQL twice.

The first entry is for *2* executions, with the same Plan Hash Value.  The Rows statistics shows statistics for the 1st execution, the average and the last execution.  Thus, we can discern that not all the executions had the same Row Statistics.  The first execution read 107 Rows (ROWIDs) from the Index, the last execution read 214 from the same Index (using the same Execution Plan).  Therefore, it is important to understand that execution statistics for the same Execution Plan can vary !  If you suspect that execution statistics are varying, I recommend that you use the aggregate=NO option to tkprof.  {I suggest that you create a test case where you have 3 or more executions with the same Plan Hash Value and Execution Plan and yet have different statistics (e.g. simply Row Counts) and see how the Plan / Row statistics are reported}
Note : Earlier versions of Oracle wouldn't report "Rows (1st) Rows (avg) Rows (max)" but would likely have reported Row Source statistics only for the last execution.

The second entry is for the 3rd execution, with a different Plan Hash Value and Execution Plan (using the EMP_EMP_ID_PK index).

Conclusion :  Thus, we have one SQL with one SQL_ID, two different Execution Plans and three sets of Execution Statistics.  Simply because the underlying data or DDL can change between executions of the same SQL in the same session.

.
.
.



Categories: DBA Blogs

Webinar: Histograms: Pre-12c and Now

Oracle in Action - Fri, 2015-10-30 23:49

RSS content

To improve optimizer estimates in case of skewed data distribution , histograms can be created. Prior to 12c, based on No. of distinct values (NDV) in a column two types of histograms could be created :

if no. of buckets >= NDV, frequency histogram is created and the optimizer makes accurate estimates.

If no. of buckets < NDV, height balanced histogram is created and accuracy of optimizer estimates depends on whether a key value is an endpoint or not.

The problem of optimizer mis-estimates in case of height balanced histograms is resolved to a large extent in Oracle Database 12c by introducing top-frequency and hybrid histograms which are created if no. of buckets < NDV.

I will present a webinar on “Histograms: Pre-12c and now” on Saturday, November 7th  at 10:00 AM – 11:00 AM (IST) organized by All India Oracle User Group – North India Chapter.

This webinar explores Pre as well post-12c histograms while highlighting the top-frequency and hybrid histograms introduced in Oracle Database 12c.

Everyone can join this Live Webinar @

https://plus.google.com/u/0/events/cgrgqlm5f7nuecdpjoc85d1u6eo
or
https://www.youtube.com/watch?v=xfwbDczWFXo

Hope to meet you at the webinar!!!



Tags:  

Del.icio.us
Digg

Comments:  3 comments on this item
You might be interested in this:  
Copyright © ORACLE IN ACTION [Webinar: Histograms: Pre-12c and Now], All Right Reserved. 2015.

The post Webinar: Histograms: Pre-12c and Now appeared first on ORACLE IN ACTION.

Categories: DBA Blogs

Installing Oracle #GoldenGate Studio 12.2.1

DBASolved - Fri, 2015-10-30 01:03

At Oracle Open World this year, Oracle announced the release of Oracle GoldenGate Studio 12.2.1. This is a graphical design tool for Oracle GoldenGate. It will allow you to design your replication flows, map tables, and generate your parameter files based on these items.

To install Oracle GoldenGate Studio, you will need access to Java 8 (1.8) JDK. Java should be mapped to the PATH as well for easier access when installing. Additionally, you need to download the Oracle GoldenGate Studio jar files from OTN or eDelivery once it is generally available.

Note: This installation steps outlined here, were done using the latest release I had access to (beta). Some things may or may not change with the general availability release.

To begin the installation of Oracle GoldenGate Studio, you need to navigate to the directory where the jar files for installation are located and execute the java installation.

cd /media/sf_Oracle/GGATE/ggstudio/OGGS\ Latest
$ java -jar ./fmw_12.2.1.0.0_oggstudio_generic.jar

When it starts, you will be met with the Oracle Universal Installer (OUI) splash screen, which will eventually open the OUI.

After the OUI opens up, you will be on the welcome screen. On this screen, just push next to progress through the OUI.

Just like any other OUI process, you have the option to check for updates. Being that this is a new product, there are not any updates for it yet. Select the “Skip Auto Updates” and click next.

The next screen is the installation location. Provide where you would like to install Oracle GoldenGate Studio. The drop down menu on this screen, reads the OraInventory file and highlights any middle tier home that is on the machine. You cannot (and should not) install on top of an existing middleware home.

The only option for the installation is a Complete Install, which is listed on the Installation Type screen. Just click next here.

Next, the OUI will check to verify that all the prerequisites are met. If anything is not met, you will need to correct the problem. Click next.

After the verification of prerequisites have been met, the next screen that is shown is the Installation Summary screen. This screen shows you key information for where Oracle GoldenGate Studio is going to be installed and the resources that are going to be used. Click install to begin the installation.

Once the installation starts, just monitor the process until it completes. Click next or finish when it completed. If clicking finish, this will close the OUI.

With the installation complete, the final screen will provide details of the installation. Click finish when done reviewing.

Now, you can start Oracle GoldenGate Studio. In order to do this, you have to go to the $GGSTUDIO_HOME/bin/oggstudio.

Hope this gives you a sense of how easy it is to install Oracle GoldenGate Studio.

Enjoy!

about.me: http://about.me/dbasolved


Filed under: Golden Gate
Categories: DBA Blogs

An Index or Disaster, You Choose (It’s The End Of The World As We Know It)

Richard Foote - Thu, 2015-10-29 22:42
This come up recently with a customer and I saw a related question on OTN that made me realize I haven’t previously discussed the SKIP_UNUSABLE_INDEXES parameter. Let me start by making a very simple point. All it potentially takes to make your entire database effectively inoperable or indeed to maybe bring down a RAC instance is […]
Categories: DBA Blogs

Final day – OpenWorld and Delphix Sync

Bobby Durrett's DBA Blog - Thu, 2015-10-29 20:54

This morning was my last day of Oracle OpenWorld sessions and this afternoon and evening finished off my day with Delphix Sync.

The first talk was my only NoSQL talk. It was interesting because the claim was that NoSQL was good for large numbers of simple transactions. This seems to be a theme across a couple of sessions. The funny thing is that the NoSQL code reminded me of my pre-SQL mainframe Datacom DB database programming days. You specified the table and the index and fetched rows etc. You are the optimizer! Of course, you can do the same with simple one table queries in SQL. But, Oracle’s NoSQL may have some concurrency modes that Oracle’s main RDBMS doesn’t have for what that’s worth. The fun thing was that they had examples using Python and I’ve taken Python on Edx so I could read the code. Also, they talked about the REST API and I had done a few REST commands with JSON working through a demo of the Oracle database cloud a few weeks back. So, there were synergies with things I already know.

Next I went to this packed session by someone from Tumbler describing their approach to sharding and scaling. The two packed sessions I went to this week were both MySQL sessions and both by internet companies – Tumbler and Ticketmaster. They were in kind of small rooms and it was a little warm and stuffy. But, I found both very interesting. Supporting large web apps is a pretty cool proposition. Something that in another life would be fun to work on.

Next I went to a PeopleSoft session. I’ve done PeopleSoft for 20 years and I’m bored with it but I figure I should keep up with the latest. It was actually more of a functional presentation on modules that I have never used so most of the information was of no use to me. But, the new Fluid User Interface that I had never seen before interested me so I stayed long enough to get a feel for it. It seems that Oracle built it for tablets and maybe smart phones.

Next it was off to the hip (or should I say hipster :)) Hotel Zetta for Delphix Sync. It was a very cool event with a fun venue and lots of good snacks. No dinner for me tonight. I got a chance to do a ten minutes lightning talk that I built from three slides from Tuesday’s presentation. I got positive feedback but I felt kind of intimidated by all the Delphix techies. There were a lot of Delphix leaders and developers present as well as a number of people from larger customers. I heard a great talk on Delphix performance and other customers and Delphix employees spoke as well. I learned a lot and it makes me think I need to delve back into our Delphix environment and give it a thorough check out.

So, my OpenWorld/Delphix Sync week is over and I am beat. Like always these conferences leave me with information overload. I’m back to the prioritization thing that always dogs my step. There is just too much to learn and do. Where do I put my time? We shall see.

Bobby

Categories: DBA Blogs

RMAN Full Backup vs. Level 0 Incremental

Pythian Group - Thu, 2015-10-29 11:23

Perhaps you’ve wondered about this. What is the difference between taking an RMAN full backup and a level 0 incremental backup?

If you read the documentation the following explanation will be found here: Incremental Backups

The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy. Thus, an incremental level 0 backup is a full backup that happens to be the parent of incremental backups whose level is greater than 0.

Logically that is true, but for many folks that is not enough. Are they different physically, and if so exactly how are they different physically?

If you google for an explanation you will find a number of ideas, most based on what the documentation says.

Here are some examples of what I found:

  • The only difference between level 0 and full is that a level 0 is marked in the RMAN catalog as a level 0.
  • Level 0 incremental and full backups are physically identical.

If you are an experienced IT professional then by now you have learned not to assume that articles found on the WWW are always correct.It seemed like it might be an interesting exercise to find out what the differences might be.

This called for some experimentation; here is the general procedure followed:

  • Put the database in mount state so there is no activity in the database.
  • Create two RMAN ‘full’ backups.
  • Create two RMAN incremental level 0 backups.
  • Open the database and perform some DML and/or DDL.
  • Create an incremental level 1 backup.
  • Do more DML/DDL activity.
  • Create another incremental level 1 backup.

This will provide some backup files to examine. You may be wondering why two of each backup type was made. The reason for that is to filter out changes in the backup file that are not relevant to the difference in backup types. That should become more clear with examples.

The test environment is as follows:

  • Test database: Oracle 12.1.0.2 CDB on Linux 6.5.
  • RMAN Catalog database: Oracle 11.2.0.2 on Linux 5.5.

The following backup commands were used to create two full and two level 0 backups:

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

rman target / catalog rman12c/rman12c@oravm &amp;amp;lt;&amp;amp;lt;-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/full-01/%U';
backup database tag 'full-01' plus archivelog tag 'full-01';
}
EOF

rman target / catalog rman12c/rman12c@oravm &amp;amp;lt;&amp;amp;lt;-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/full-02/%U';
backup database tag 'full-02' plus archivelog tag 'full-02';
}
EOF

rman target / catalog rman12c/rman12c@oravm &amp;amp;lt;&amp;amp;lt;-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/lvl0-01/%U';
backup incremental level 0 database tag 'lvl0-01' plus archivelog tag 'lvl0-01';
}
EOF

rman target / catalog rman12c/rman12c@oravm &amp;amp;lt;&amp;amp;lt;-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/lvl0-02/%U';
backup incremental level 0 database tag 'lvl0-02' plus archivelog tag 'lvl0-02';
}
EOF

Let’s take a look at the size of the files:


[root@lestrade ora12c]# ls -l full-01/*
-rw-r----- 1 root root    6878208 Oct 27 15:15 full-01/0sqkp2dl_1_1
-rw-r----- 1 root root  761749504 Oct 27 15:16 full-01/0tqkp2dn_1_1
-rw-r----- 1 root root 1696112640 Oct 27 15:17 full-01/0uqkp2eq_1_1
-rw-r----- 1 root root  735043584 Oct 27 15:17 full-01/0vqkp2g7_1_1
-rw-r----- 1 root root  623837184 Oct 27 15:17 full-01/10qkp2h0_1_1

[root@lestrade ora12c]# ls -l full-02/*
-rw-r----- 1 root root    6878208 Oct 27 15:18 full-02/12qkp2hm_1_1
-rw-r----- 1 root root  761749504 Oct 27 15:18 full-02/13qkp2ho_1_1
-rw-r----- 1 root root 1696112640 Oct 27 15:19 full-02/14qkp2ir_1_1
-rw-r----- 1 root root  735043584 Oct 27 15:19 full-02/15qkp2k8_1_1
-rw-r----- 1 root root  623837184 Oct 27 15:20 full-02/16qkp2l2_1_1

[root@lestrade ora12c]# ls -l lvl0-01/*
-rw-r----- 1 root root    6878208 Oct 27 15:20 lvl0-01/18qkp2lm_1_1
-rw-r----- 1 root root  761749504 Oct 27 15:21 lvl0-01/19qkp2lo_1_1
-rw-r----- 1 root root 1696112640 Oct 27 15:21 lvl0-01/1aqkp2mr_1_1
-rw-r----- 1 root root  735043584 Oct 27 15:22 lvl0-01/1bqkp2o8_1_1
-rw-r----- 1 root root  623837184 Oct 27 15:22 lvl0-01/1cqkp2p2_1_1

[root@lestrade ora12c]# ls -l lvl0-02/*
-rw-r----- 1 root root    6878208 Oct 27 15:23 lvl0-02/1eqkp2q4_1_1
-rw-r----- 1 root root  761749504 Oct 27 15:23 lvl0-02/1fqkp2q6_1_1
-rw-r----- 1 root root 1696112640 Oct 27 15:24 lvl0-02/1gqkp2r9_1_1
-rw-r----- 1 root root  735043584 Oct 27 15:25 lvl0-02/1hqkp2sm_1_1
-rw-r----- 1 root root  623837184 Oct 27 15:25 lvl0-02/1iqkp2tf_1_1

The number and sizes of the files appear the same regardless of the backup type, lending some credence to the idea that they may be physically identical. So now let’s dump some of the files to determine the difference.

For purposes of this experiment we are going to examine the backup files that contain datafile 1 from the database, the SYSTEM tablespace datafile.

We can find these backup files with the RMAN list command:


RMAN> list backup of datafile 1;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1259    Full    1.58G      DISK        00:00:39     2015-10-27 15:15:13
        BP Key: 1267   Status: AVAILABLE  Compressed: NO  Tag: FULL-01
        Piece Name: /mnt/oracle-backups/ora12c/full-01/0uqkp2eq_1_1
  List of Datafiles in backup set 1259
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 50957811   2015-10-27 18:08:29 +DATA/JS03/DATAFILE/system.258.856612879

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1341    Full    1.58G      DISK        00:00:39     2015-10-27 15:17:22
        BP Key: 1349   Status: AVAILABLE  Compressed: NO  Tag: FULL-02
        Piece Name: /mnt/oracle-backups/ora12c/full-02/14qkp2ir_1_1
  List of Datafiles in backup set 1341
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 50957811   2015-10-27 18:08:29 +DATA/JS03/DATAFILE/system.258.856612879

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1433    Incr 0  1.58G      DISK        00:00:39     2015-10-27 15:19:30
        BP Key: 1441   Status: AVAILABLE  Compressed: NO  Tag: LVL0-01
        Piece Name: /mnt/oracle-backups/ora12c/lvl0-01/1aqkp2mr_1_1
  List of Datafiles in backup set 1433
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 50957811   2015-10-27 18:08:29 +DATA/JS03/DATAFILE/system.258.856612879

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1531    Incr 0  1.58G      DISK        00:00:42     2015-10-27 15:21:55
        BP Key: 1539   Status: AVAILABLE  Compressed: NO  Tag: LVL0-02
        Piece Name: /mnt/oracle-backups/ora12c/lvl0-02/1gqkp2r9_1_1
  List of Datafiles in backup set 1531
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 50957811   2015-10-27 18:08:29 +DATA/JS03/DATAFILE/system.258.856612879

Now dump the backup pieces to hex files. Just the first 100M is being dumped from each, resulting in ~300M text files.


DUMPDIR=hexdump
mkdir -p $DUMPDIR

for f in full-01/0uqkp2eq_1_1 full-02/14qkp2ir_1_1 lvl0-01/1aqkp2mr_1_1 lvl0-02/1gqkp2r9_1_1
do
        tag=$(dirname $f)
        filename=$(basename $f)
        filename="${DUMPDIR}/${tag}_${filename}.txt"
        echo $filename
        # dump first 100M
        dd if=$f bs=512 count=204800 2>/dev/null | hexdump -C > $filename
done

[root@lestrade hexdump]# ls -l full* lvl0*
-rw-r--r-- 1 root root 305807309 Oct 27 16:11 full-01_0uqkp2eq_1_1.txt
-rw-r--r-- 1 root root 305807309 Oct 27 16:11 full-02_14qkp2ir_1_1.txt
-rw-r--r-- 1 root root 305807309 Oct 27 16:11 lvl0-01_1aqkp2mr_1_1.txt
-rw-r--r-- 1 root root 305807309 Oct 27 16:11 lvl0-02_1gqkp2r9_1_1.txt

We can compare the dump of the two full backup files. As there were no changes to the database during this time (you may recall the database is in MOUNT state) the only difference in the files should be changes to metadata.

[root@lestrade hexdump]# diff full-01_0uqkp2eq_1_1.txt full-02_14qkp2ir_1_1.txt
7,8c7,8
< 00002010  46 7d 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |F}...........)..|
< 00002020  4a 53 30 33 00 00 00 00  de 19 01 00 00 00 00 00  |JS03............|
---
> 00002010  dd 7e 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |.~...........)..|
> 00002020  4a 53 30 33 00 00 00 00  fd 19 01 00 00 00 00 00  |JS03............|
12,13c12,13
< 00002060  01 00 00 00 da 89 4c 35  1e 00 00 00 04 00 00 00  |......L5........|
< 00002070  01 00 00 00 46 55 4c 4c  2d 30 31 00 00 00 00 00  |....FULL-01.....|
---
> 00002060  01 00 00 00 5b 8a 4c 35  24 00 00 00 04 00 00 00  |....[.L5$.......|
> 00002070  01 00 00 00 46 55 4c 4c  2d 30 32 00 00 00 00 00  |....FULL-02.....|

These two full backup files differ only the by these four lines. These differences are in the 17th OS block of the files and can be identified as metadata by the tags FULL-0[12] and the database name JS03. There are other differences that do not appear as printable characters. I did spend a little time trying to decode their meanings; they may be backup piece#, backup piece keys, backup set keys, and similar. Determining the meaning of these didn’t really seem necessary to understand the difference between full and incremental level 0 backups, so I did not continue. If you do know what these values represent, please, leave a comment.

Doing a diff on the level 0 hex dump files shows changes in the same lines:

[root@lestrade hexdump]# diff lvl0-01_1aqkp2mr_1_1.txt lvl0-02_1gqkp2r9_1_1.txt
7,8c7,8
< 00002010  bc 02 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |.............)..|
< 00002020  4a 53 30 33 00 00 00 00  1a 1a 01 00 00 00 00 00  |JS03............|
---
> 00002010  34 03 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |4............)..|
> 00002020  4a 53 30 33 00 00 00 00  39 1a 01 00 00 00 00 00  |JS03....9.......|
12,13c12,13
< 00002060  01 00 00 00 db 8a 4c 35  2a 00 00 00 04 00 00 00  |......L5*.......|
< 00002070  01 00 00 00 4c 56 4c 30  2d 30 31 00 00 00 00 00  |....LVL0-01.....|
---
> 00002060  01 00 00 00 69 8b 4c 35  30 00 00 00 04 00 00 00  |....i.L50.......|
> 00002070  01 00 00 00 4c 56 4c 30  2d 30 32 00 00 00 00 00  |....LVL0-02.....|

Now it is time to compare a full backup file to a level 0 backup file:

[root@lestrade hexdump]# diff full-01_0uqkp2eq_1_1.txt lvl0-01_1aqkp2mr_1_1.txt
7,8c7,8
< 00002010  46 7d 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |F}...........)..|
< 00002020  4a 53 30 33 00 00 00 00  de 19 01 00 00 00 00 00  |JS03............|
---
> 00002010  bc 02 00 00 00 02 10 0c  00 02 10 0c c8 29 05 07  |.............)..|
> 00002020  4a 53 30 33 00 00 00 00  1a 1a 01 00 00 00 00 00  |JS03............|
12,13c12,13
< 00002060  01 00 00 00 da 89 4c 35  1e 00 00 00 04 00 00 00  |......L5........|
< 00002070  01 00 00 00 46 55 4c 4c  2d 30 31 00 00 00 00 00  |....FULL-01.....|
---
> 00002060  01 00 00 00 db 8a 4c 35  2a 00 00 00 04 00 00 00  |......L5*.......|
> 00002070  01 00 00 00 4c 56 4c 30  2d 30 31 00 00 00 00 00  |....LVL0-01.....|
15c15
< 00002090  00 00 00 00 04 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
---
> 00002090  00 00 00 00 05 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|

Notice the new line 2090? This is the line that contains the difference between a full and a level 0 backup file.

Before continuing it is time to create a couple of level 1 backups:


SQL> create user dilbert identified by phb;

User created.

SQL> alter user dilbert quota 10m on users;

User altered.

SQL> create table dilbert.t1 as select systimestamp t1 from dual;

Table created.

====
rman target / catalog rman12c/xxx@oravm <<-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/lvl1-01/%U';
backup incremental level 1 database tag 'lvl1-01' plus archivelog tag 'lvl1-01';
}
EOF
===

Create another table in dilbert schema

SQL> create table dilbert.t2 as select * from dba_users;

Table created.

rman target / catalog rman12c/xxx@oravm <<-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/lvl1-03/%U';
backup incremental level 1 database tag 'lvl1-03' plus archivelog tag 'lvl1-03';
}
EOF

Note:  lvl1-02 backup was discarded as I forgot to make db changes before making it.

If we were to go on and compare all of the backup files that contain datafile 1:

[root@lestrade hexdump]# grep ^00002090 *.txt
full-01_0uqkp2eq_1_1.txt:00002090  00 00 00 00 04 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
full-02_14qkp2ir_1_1.txt:00002090  00 00 00 00 04 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
lvl0-01_1aqkp2mr_1_1.txt:00002090  00 00 00 00 05 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
lvl0-02_1gqkp2r9_1_1.txt:00002090  00 00 00 00 05 04 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
lvl1-01_1mqkp7jk_1_1.txt:00002090  00 00 00 00 05 00 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|
lvl1-03_22qks49l_1_1.txt:00002090  00 00 00 00 05 00 00 00  5a 08 00 00 c3 2c 04 00  |........Z....,..|

A bitmask emerges that is peculiar to each backup type:

Starting at offset 0x2090
     00 01 02 03 04 05 06 07
Full 00 00 00 00 04 04 00 00  
LVL0 00 00 00 00 05 04 00 00
LVL1 00 00 00 00 05 00 00 00

What might these mean?
Here’s a possible explanation:

0x2094 = 0x04 = FULL
0x2094 = 0x05 = Incremental

0x2095 = 0x04 = level 0 if the previous byte is 0x05
0x2095 = 0x00 = level 1 if the previous byte is 0x05

Of course this doesn’t explain the purpose of 0x04 at 0x205 if the backup is ‘FULL’.

To fully understand these flags would require dumping and examining many more backup files, not just of datafiles but of archive logs, controlfiles, etc.

One thing is clear from this experiment; there are differences in the metadata contained in the file headers when comparing a full backup to a level 0 incremental backup. It is not simply a matter of the one backup being cataloged differently by RMAN; the backup pieces can be identified as either full or incremental level backups based on the metadata in the file header.

So what good is all this effort for a little bit of knowledge?

How about the next time you are asked about the difference between a level 0 and full backup? Now you can say something other that quoting the documentation or some article on the internet (one that is lacking anything to backup the assertions made).

If the question is posed to you during an interview, this information becomes even more valuable.

Now another question comes to mind: Can backup files made with a full backup be modified to work as incremental level 0 backups?

That would be an interesting experiment. This is not something one would normally do, but it is not too hard to imagine a scenario where only a full backup, an incremental level 1 backup and the following archive logs were available to recover a database. This would be a desperate measure and a last resort, but sometimes last resorts are necessary.

Do you think this can be done? If so please give it a try, but only test it on an expendable test system please.

Please be sure to leave a comment if you try this experiment, it would be quite interesting to discuss.

 

Discover more about our expertise in Oracle.

Categories: DBA Blogs

Auto optimizer stats after CTAS or direct loads in #Oracle 12c

The Oracle Instructor - Thu, 2015-10-29 07:43

Less famous 12c New Feature: If you do Create Table As Select (CTAS) or a direct load into an empty table, we will automatically collect optimizer statistics:

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> create table nu as select * from sales where rownum<=100000; 
Table created.
 
SQL> select num_rows,blocks from user_tables where table_name='NU';

  NUM_ROWS     BLOCKS
---------- ----------
    100000        695

SQL> truncate table nu;
Table truncated.

SQL> insert /*+ append */ into nu  select * from sales where rownum<=200000; 
200000 rows created. 

SQL> commit;
Commit complete.

SQL> select num_rows,blocks from user_tables where table_name='NU';

  NUM_ROWS     BLOCKS
---------- ----------
    200000       1379

If the table is not empty, the statistics are not updated:

SQL> insert /*+ append */ into nu  select * from sales where rownum<=100000; 
100000 rows created. 

SQL> commit;
Commit complete.

SQL> select num_rows,blocks from user_tables where table_name='NU';

  NUM_ROWS     BLOCKS
---------- ----------
    200000       1379

You see that the previous statistics have not been changed here.
If it is a conventional insert:

SQL> truncate table nu; 
Table truncated. 
SQL> insert into nu select * from sales where rownum<=100000; 
100000 rows created. 
SQL> commit; 
Commit complete. 
SQL> select num_rows,blocks from user_tables where table_name='NU'; 
NUM_ROWS BLOCKS 
---------- ---------- 
    200000       1379

Again, the statistics have not been maintained automatically either. Can be done manually still, of course:

SQL> exec dbms_stats.gather_table_stats('ADAM','NU')

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from user_tables where table_name='NU';

  NUM_ROWS     BLOCKS
---------- ----------
    100000        748

So knowing that, you may be able to skip some manual statistics collections that you may have done in the past after CTAS or bulk loads :-)


Tagged: 12c New Features
Categories: DBA Blogs

Wednesday OpenWorld

Bobby Durrett's DBA Blog - Wed, 2015-10-28 21:27

Well, it was a long day but it ended in a fun way.

Today I was back to the normal OpenWorld sessions starting with the general session. It was eye-opening because the speakers described a new CPU chip that they were using in their latest servers.  It had some custom elements to support database processing.  It was strange because I have recently been studying the latest Intel x86 documentation and it was interesting to compare Intel’s chips with the latest Sun/Oracle chip.  I had read about the specialized SIMD instructions in the x86 family that Intel uses to speed up graphics. So, I was not surprised that Oracle is including more complex additions to their new chip with specialized instructions.  Still, I question whether people are really going to buy anything but Intel x86 at this point due to the price/performance.

Next I went to a session describing the way a company used a tool called Chef to manage their Weblogic deployments.  The session topic interested me because we also use Weblogic at US Foods.  But it was a little hard to follow.  Maybe it would have helped if I had been exposed to Chef before hearing the talk. Still, it was good to know that there are tools out there to help automate deployment of new systems.

Next I caught a PeopleSoft in the Cloud talk. It seems that you will install the latest version of PeopleTools in a very different way than you did in the past.  I got the feeling that this was just a part way step toward fully setting up PeopleSoft to run in the cloud.

Then I went to a really cool talk about how Ticketmaster sells 20,000 tickets in one minute. It was about their MySQL architecture.  They have a large farm of MySQL servers supporting the queries behind their ticketing web site.  But, they use Oracle for the updates related to ticket purchases.

Then I went to a talk on Oracle ZFS.  I get the feeling that I need to learn more about ZFS. It seems that ZFS is an ancestor of Delphix and I know that there is a free OpenZFS that I might play with. I think that Tim Gorman, who works for Delphix, mentioned something about OpenZFS  at his Ted talk at Oak Table World Tuesday so there may be some relationship.

Lastly I went to a talk about how you can use Oracle’s Enterprise Manager to support both on site and cloud databases.  It sounds good but I think it still need to mature over time to support the cloud systems more fully.

Then at 5:30 pm I went to a fun bloggers party sponsored by Pythian and the Oracle Technology Network (OTN).  I’m not a big party person but I had a good time.  It was easy to strike up a conversation with people since we had a lot in common.

Anyway, enough for today.  One more day Thursday and then my brain will overflow. :)

Bobby

Categories: DBA Blogs

Oracle GoldenGate Studio, It is here and so are my thoughts

DBASolved - Wed, 2015-10-28 18:44

Ever since Oracle purchased GoldenGate, the biggest complaint about Oracle GoldenGate (OGG) has been around the fact that it is a command line tool. I like the command line interface of OGG, the simplicity of it and what can be done by just editing the parameter files from the command line. However, for those of you that require a graphical interface, Oracle has listened and provided a new tool. This tool is Oracle GoldenGate Studio!

This year at Oracle Open World 2015, Oracle announced Oracle GoldenGate Studio Release 1 (12.2.1). This is Oracle’s first step into bringing Oracle GoldenGate into the GUI environment. Studio is a design tool that complements the monitoring tools that Oracle provides for GoldenGate.

As a design tool, you can do a range of things now. A few of the thing are as follows:

  • Build out a wide range of Oracle GoldenGate architectures
  • Synced parameter files to systems where Oracle GoldenGate is running or dump the files to a directory for later installation
  • Export/Import GoldenGate Solutions
  • Define mappings in either local or global settings

When you first start Oracle GoldenGate Studio, anyone who has worked with Oracle Data Integrator will notice some similarities. This is because the initial framework for Oracle GoldenGate Studio is built around the ODI framework. This is good because if provides a familiar looking interface for the end user.

Just like ODI, Oracle GoldenGate Studio uses a repository. This repository can be the same repository as many other Oracle products. In my environment, I’ve got Oracle GoldenGate Studio running against a 12.1.0.2 database. The repository is used to keep track of all the mappings needed to keep track of the architectures that is designed within the studio.

Overall, my initial thoughts on this product are:

  • For a first release, it is good but has some limited functionality
  • I like the interface and it helps to make quick work of designing a flow; however, initially there will be a learning curve for most GoldenGate Admins (no more Visio diagrams need to track)
  • Not thrilled with having another repository to keep up with; nice that is can be combined into other repositories if needed, just need to make sure schema names do not overwrite

 

With this tool being announced at Oracle Open World, expect some great blogs about this tool being road tested as well. In the mean time, once it becomes available (after OOW15) give it a try. It will make your life a bit easier.

Enjoy!!


Filed under: Golden Gate
Categories: DBA Blogs

Trace Files -- 5.2 : Interpreting the SQL Trace Summary level

Hemant K Chitale - Wed, 2015-10-28 09:08
Picking up the same SQL Trace file from my previous post, I run (the well known) utility tkprof on it.

[oracle@ora11204 Desktop]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3039.trc My_Query.PRF sys=no

TKPROF: Release 11.2.0.4.0 - Development on Wed Oct 28 22:53:46 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


[oracle@ora11204 Desktop]$
[oracle@ora11204 Desktop]$ cat My_Query.PRF

TKPROF: Release 11.2.0.4.0 - Development on Wed Oct 28 22:53:46 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Trace file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3039.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: dc03x7r071fvn Plan Hash: 0

BEGIN DBMS_SESSION.SESSION_TRACE_ENABLE(waits=>TRUE,binds=>FALSE); END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.03 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.03 0 0 0 1

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 11.45 11.45
********************************************************************************

SQL ID: 7c1rnh08dp922 Plan Hash: 3580537945

select count(*)
from
employees


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 1 1 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.03 1 1 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=1 pr=1 pw=0 time=14407 us)
107 107 107 INDEX FULL SCAN EMP_EMAIL_UK (cr=1 pr=1 pw=0 time=14577 us cost=1 size=0 card=107)(object id 16404)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
Disk file operations I/O 1 0.00 0.00
db file sequential read 1 0.00 0.00
SQL*Net message from client 2 13.27 13.27
********************************************************************************

SQL ID: 9wuhwhad81d36 Plan Hash: 0

BEGIN DBMS_SESSION.SESSION_TRACE_DISABLE; END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.02 0 0 0 0
Execute 3 0.00 0.03 0 0 0 2
Fetch 2 0.00 0.01 1 1 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.01 0.06 1 1 0 3

Misses in library cache during parse: 2
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 13.27 24.73
db file sequential read 1 0.00 0.00
Disk file operations I/O 1 0.00 0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 43 0.00 0.00 0 0 0 0
Fetch 90 0.00 0.01 3 165 0 68
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 133 0.00 0.01 3 165 0 68

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 3 0.01 0.01

3 user SQL statements in session.
12 internal SQL statements in session.
15 SQL statements in session.
********************************************************************************
Trace file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3039.trc
Trace file compatibility: 11.1.0.7
Sort options: default

1 session in tracefile.
3 user SQL statements in trace file.
12 internal SQL statements in trace file.
15 SQL statements in trace file.
15 unique SQL statements in trace file.
284 lines in trace file.
24 elapsed seconds in trace file.


[oracle@ora11204 Desktop]$


With the "sys=no" command-line flag, I have excluded reporting the recursive (SQL) calls although the summary statistics on them do appear.  There were a total of 43 executions of recursive calls.  The SQL*Net message from client is a wait of 13.27seconds (which is normally considered as an "idle wait").  We see this also in the previous post on the raw trace as the wait event before Tracing is disabled.
The only significant wait event was "db file sequential read" which is less than 1centisecond so is reported as 0.00second.  However, from the previous post, we can see that the raw trace file shows a wait time of 6,784 microseconds
.
.
.


Categories: DBA Blogs

Trace Files -- 5.1 : Reading an SQL Trace

Hemant K Chitale - Wed, 2015-10-28 08:53
Here's a short introduction to reading an SQL Trace.

First I execute these in my sqlplus session :

SQL> connect hr/oracle
Connected.
SQL> exec DBMS_SESSION.SESSION_TRACE_ENABLE(waits=>TRUE,binds=>FALSE);

PL/SQL procedure successfully completed.

SQL> select count(*) from employees;

COUNT(*)
----------
107

SQL> exec DBMS_SESSION.SESSION_TRACE_DISABLE;

PL/SQL procedure successfully completed.

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3039.trc

SQL>



Now, I extract selective portions of the trace file.

The header of the trace file gives me inormation about the platform and session/module :

Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3039.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/orcl
System name: Linux
Node name: ora11204
Release: 2.6.39-400.17.1.el6uek.x86_64
Version: #1 SMP Fri Feb 22 18:16:18 PST 2013
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 27
Unix process pid: 3039, image: oracle@ora11204 (TNS V1-V3)


*** 2015-10-28 22:19:42.291
*** SESSION ID:(141.7) 2015-10-28 22:19:42.291
*** CLIENT ID:() 2015-10-28 22:19:42.291
*** SERVICE NAME:(SYS$USERS) 2015-10-28 22:19:42.291
*** MODULE NAME:(SQL*Plus) 2015-10-28 22:19:42.291
*** ACTION NAME:() 2015-10-28 22:19:42.291


The trace file tells me that I am running 64bit Oracle 11.2.0.4 on a speciic Linux (UEK) kernel on a host called "ora11204".  This is useful information if I have to provide the trace file to Oracle Support.  (True, it doesn't list all the Oracle patches that may have been applied).
It then identifies my Service Name and Module (and Action if I had set it with DBMS_APPLICATION_INFO).

Next, the trace file tells me when and how I have enabled tracing.

PARSING IN CURSOR #140174891232936 len=73 dep=0 uid=43 oct=47 lid=43 tim=1446041982290677 hv=3228613492 ad='99446cc8' sqlid='dc03x7r071fvn'
BEGIN DBMS_SESSION.SESSION_TRACE_ENABLE(waits=>TRUE,binds=>FALSE); END;
END OF STMT
EXEC #140174891232936:c=1000,e=31608,p=0,cr=0,cu=0,mis=1,r=1,dep=0,og=1,plh=0,tim=1446041982290670
WAIT #140174891232936: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=13246 tim=1446041982291265


It tells me that I have used DBMS_SESSION to enable tracing.

That cursor is then closed.

*** 2015-10-28 22:19:53.745
WAIT #140174891232936: nam='SQL*Net message from client' ela= 11453710 driver id=1650815232 #bytes=1 p3=0 obj#=13246 tim=1446041993745004
CLOSE #140174891232936:c=0,e=38,dep=0,type=0,tim=1446041993745175


We can identify the CURSOR Number (140174891232936-- this numbering format was introduced in 11g, if I am correct) that was closed.  This number is NOT the SQL_ID (which is dc03x7r071fvn -- yes even PLSQL blocks and procedures have SQL_IDs).  This number is NOT the SQL Hash Value either (the SQL Hash Value is present as hv=3228613492).

After that we start seeing the recursive (SYS) calls executed during the parsing of the actual user query (the query on EMPLOYEES).

PARSING IN CURSOR #140174891988920 len=202 dep=1 uid=0 oct=3 lid=0 tim=1446041993745737 hv=3819099649 ad='997c03e0' sqlid='3nkd3g3ju5ph1'
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
EXEC #140174891988920:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2853959010,tim=1446041993745733
FETCH #140174891988920:c=999,e=88,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,plh=2853959010,tim=1446041993745957
CLOSE #140174891988920:c=0,e=11,dep=1,type=3,tim=1446041993746025
=====================
PARSING IN CURSOR #140174891955728 len=493 dep=1 uid=0 oct=3 lid=0 tim=1446041993746138 hv=2584065658 ad='997a6590' sqlid='1gu8t96d0bdmu'
select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.clucols,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.blkcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.property,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl(t.trigflag, 0),nvl(t.spare1,0),nvl(t.spare2,0),t.spare4,t.spare6,ts.cachedblk,ts.cachehit,ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
END OF STMT
EXEC #140174891955728:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3526770254,tim=1446041993746134
FETCH #140174891955728:c=0,e=53,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=3526770254,tim=1446041993746225
CLOSE #140174891955728:c=0,e=8,dep=1,type=3,tim=1446041993746254


I have shown only the first two recursive calls.  These are identified by "dep=1"  (the parent call by the user we shall see is at depth level 0) and also by uid=0, lid=0  (schema id and privilege userid being 0 for SYS).  len=493 is the length of the SQL statement.

The elapsed time in microseconds is the "e=" value while the CPU time is "c=" in microseconds.  The "p=" value is Physical Reads, the "cr=" value is Consistent Gets, "cu=" is Current Gets, "r=" is Number of Rows.  "og=" shows the Optimizer Goal (og=4 is "Choose").  "tim=" is an ever-increasing timestamp (i.e. to calculate elapsed time between two SQL calls you can deduct the first "tim" value from the second.

Following these two are a number of other recursive SQL calls that I shall skip over until I come to my user SQL.

PARSING IN CURSOR #140174891232936 len=30 dep=0 uid=43 oct=3 lid=43 tim=1446041993782992 hv=282764354 ad='99446f28' sqlid='7c1rnh08dp922'
select count(*) from employees
END OF STMT
PARSE #140174891232936:c=8998,e=37717,p=3,cr=165,cu=0,mis=1,r=0,dep=0,og=1,plh=3580537945,tim=1446041993782979
EXEC #140174891232936:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3580537945,tim=1446041993783188
WAIT #140174891232936: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=13246 tim=1446041993783286
WAIT #140174891232936: nam='Disk file operations I/O' ela= 76 FileOperation=2 fileno=4 filetype=2 obj#=16404 tim=1446041993790690
WAIT #140174891232936: nam='db file sequential read' ela= 6784 file#=4 block#=211 blocks=1 obj#=16404 tim=1446041993797581
FETCH #140174891232936:c=7000,e=14409,p=1,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=3580537945,tim=1446041993797727
STAT #140174891232936 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=1 pr=1 pw=0 time=14407 us)'
STAT #140174891232936 id=2 cnt=107 pid=1 pos=1 obj=16404 op='INDEX FULL SCAN EMP_EMAIL_UK (cr=1 pr=1 pw=0 time=14577 us cost=1 size=0 card=107)'
WAIT #140174891232936: nam='SQL*Net message from client' ela= 486 driver id=1650815232 #bytes=1 p3=0 obj#=16404 tim=1446041993819989
FETCH #140174891232936:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3580537945,tim=1446041993820032
WAIT #140174891232936: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=16404 tim=1446041993820057


The "dep=0" and "uid" and "lid" not being 0 indicate that this is a User SQL.  "oct" is the Oracle Command Type.
Here in addition to information on the PARSE call, the EXEC call, the individual WAITs and the FETCH call, we can also see Row Source Statistics indicated by the STAT lines.
The Parse was 37,717 microseconds and the FETCH time was 14,409 microseconds.  (This is a very quick running SQL but since it had never been parsed in ths instance, the Parse time exceeded the FETCH time),  The "mis=1" for the Parse indicates a Miss in the Library Cache -- so Oracle had to do a Hard Parse.
I would look at EXEC for INSERT/UPDATE/DELETE statements but here for a simple SELECT, I look at the FETCH time.
For the 'db file sequential read' wait of 6,784microseconds, we can also see the File Number ("file#"), the Block ID ("block#"), number of Blocks (1 for this wait event) and Object ID ("obj#")


The STAT lines have additional information about the position ("pos")  and parent id ("pid") in the Execution Plan.  The Object ID is indicated by "obj" and the operation by "op". STAT lines show the Consistent Gets ("cr"), Physical Reads ("pr") , the Time ("time") in microseconds, the cost ("cost") and expected cardinality ("card") for each step of the Execution Plan. Note that the expected cardinality is "card" but the actual count of rows is "cnt".

Next, the cursor is closed, and tracing disabled.

*** 2015-10-28 22:20:07.096
WAIT #140174891232936: nam='SQL*Net message from client' ela= 13276436 driver id=1650815232 #bytes=1 p3=0 obj#=16404 tim=1446042007096511
CLOSE #140174891232936:c=0,e=19,dep=0,type=0,tim=1446042007096668
=====================
PARSING IN CURSOR #140174891232936 len=48 dep=0 uid=43 oct=47 lid=43 tim=1446042007097875 hv=2592126054 ad='96fff810' sqlid='9wuhwhad81d36'
BEGIN DBMS_SESSION.SESSION_TRACE_DISABLE; END;
END OF STMT
PARSE #140174891232936:c=999,e=1129,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1446042007097870
EXEC #140174891232936:c=0,e=313,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1446042007098285


Note how the CURSOR ID get's reused by the next SQL if the previous SQL cursor was closed.  Thus "140174891232936" was used by the PLSQL call  that enabled Tracing, then closed, then by the user SQL query on EMPLOYEES, then Closed, then by the  PLSQL call that disabled Tracing.
(The recursive SQLs in between had different CURSOR IDs).

(As an observation : Notice how the "obj#" doesn't get cleared even when the next wait is an "SQL*Net message to client" or "SQL*Net message from client")
.
.
.

Categories: DBA Blogs

FMW & PaaS Webcasts Series For Partners in November

Are you able to attend Oracle OpenWorld sessions? Even though you are, you might not have enough time to visit all the sessions. Join us for the FY16 Oracle Cloud Platform and FMW Webcast Series...

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

Tuesday OakTable World – brain fried!

Bobby Durrett's DBA Blog - Tue, 2015-10-27 19:08

Instead of going to the normal OpenWorld events today I went to OakTable World.  Now my brain is fried from information overload. :)

It started at 8 am with a nice talk about hash joins and Bloom filters.  Toon Koppelaars had some nice moving graphics showing how bloom filters work.  I’ve studied Bloom filters before but I’m not sure I understood it with the clarity that I had after this talk.

Then I did my talk at 9 am.  The best part for me was that we had a number of questions.  I ended up skipping several slides because of time but I felt like we helped people get what they wanted out of it by having the questions and discussion.  In retrospect my talk could have used more of an introduction to Delphix itself for this audience but I think we covered the essentials in the end.

Next Kellyn Pot’Vin-Gorman did more of a community service type of talk which was a change of pace.  She had a Raspberry Pi project which was a stuffed bear that would take your picture and post it on Twitter.  It was an example of the type of project that kids could do to get them interested in computer technology.

My brain began to turn to mush with Marco Gralike’s talk on XML and 12c In-Memory Column store.  I’m sure I’m absorbed something but I’m not that familiar with Oracle’s XML features.  Still, at least I know that there are in memory features for XML which I can file away for the future.

Several amusing 10 minute Ted talks followed.  Most notable to me was Jonathan Lewis’ talk about how virtual columns and constraints on virtual columns could improve cardinality estimates and thus query performance.

Cary Millsap talked about a variety of things including things like what he covered in his book.  He shared how he and Jeff Holt were hacking into what I assume is the C standard library to diagnose database performance issues, which was pretty techy.

Gwen Shapira’s talk on Kafka was a departure from the Oracle database topics but it was interesting to hear about this sort of queuing or logging service.  Reminds me in some ways of GGS and Tibco that we use at work but I’m sure it has different features.

Alex Gorbachev gave a high level overview of Internet of Things architectures.  This boiled down to how to connect many possibly low power devices to something that can gather the information and use it in many ways.

Lastly, we went back to the Oracle database and my brain slowly ground to a halt listening to Chris Antognini’s talk on Adaptive Dynamic Sampling.  I had studied this for my OCP but it has slowly leaked out of my brain and by 4 pm I wasn’t 100% efficient.  But, I got a few ideas about things that I can adjust when tuning this feature.

Anyway, brief overview.  I’m back to normal OpenWorld tomorrow but it was all OakTable today.  It was a good experience and I appreciated the chance to speak as well as to listen.

Bobby

Categories: DBA Blogs

Oracle Fusion Middleware 12c (12.2.1.0.0) now Generally Available

Oracle Fusion Middleware is the leading business innovation platform for the enterprise and the cloud. It enables enterprises to create and run agile, intelligent business applications while...

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