Bobby Durrett's DBA Blog

Subscribe to Bobby Durrett's DBA Blog feed
Oracle database performance
Updated: 12 hours 18 min ago

Had to set parallel_max_servers to 2 for Delphix clone

Mon, 2018-04-09 17:50

This is a quick note about a problem I had creating a Delphix virtual database clone of a production database.

This is an older 11.1.0.7 HP-UX Itanium database. I tried to make a new virtual database copy of the production database and it failed due to the following error:

ORA-07445: exception encountered: core dump [krd_flush_influx_buffers()+96] [SIGSEGV] [ADDR:0x10000000005D8] [PC
:0x400000000B415880] [Address not mapped to object] []

I only found one thing on Oracle’s support site about krd_flush_influx_buffers but it was not an exact match because it had to do with Data Guard.

So I tried various parameters and none worked. I tried setting parallel_max_servers to 0  down from 100 in production but that caused other issues. Then I remembered something about setting it to 2 so I tried that and it worked.

The strange things is that I still see a ORA-07445 krd_flush_influx_buffers error in the alert log for the successful clone. But, somehow changing the parallel_max_servers parameter to 2 allowed the various Delphix and Oracle processes to complete.

Bobby

Categories: DBA Blogs

Does an automated cloud data warehouse need partitioning and indexes?

Thu, 2018-03-08 16:33

This is a late post inspired by my time at RMOUG Training Days 2018. I attended two RMOUG Training Days presentations about automated cloud data warehouse database systems. The first was about Snowflake. The second was about Oracle’s coming Autonomous Data Warehouse Cloud offering. Both products seem to have the goal of automating as much as possible in a data warehouse database and neither seem to allow users to create indexes or to partition their data in the way they choose.

This raises the obvious question – do you need the ability to create indexes and define your own partitioning scheme in a data warehouse database? I have seen many situations on data warehouse databases where both are helpful. When we went to Exadata there was a push to drop all of our indexes but we did not. Also, we used partitioning to improve query performance. If we had to get rid of both features what would we have done on Exadata?

I don’t know the answer, but people say that we need more automation and less control. So, you could spin up a cloud based data warehouse database quickly and start using it. But how many knobs are left to turn when performance is slow? Do users need indexes and partitioning methods to get acceptable performance? Really, what is wrong with giving users the ability to create indexes and to partition tables in the way they want?

Time will tell whether index and partition free cloud based data warehouse database systems make it. But, for now I’m left to wonder what we are losing without these features.

Bobby

 

Categories: DBA Blogs

Pushing predicates into MySQL subqueries

Tue, 2018-03-06 13:16

I am just getting started looking at MySQL performance tuning but I thought I would share something that I may have figured out. I’m working with a query that does a count and a group by in a subquery and it runs too long. It seems that the query reads every row from a table instead of passing a predicate into the subselect and accessing only a couple of rows. MySQL 5.7 seems to behave this way normally. I have built a simple test case to show that MySQL 5.7 works differently than Oracle 12.1 in this situation.

I build a table called TEST with about 1,000,000 rows (1,1) and one row (2,2) and put an index on the first column.

create table test(a int,b int);

insert into test values(1,1);

insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;

-- 1048576 rows

select count(*) from test;

create index testi on test (a);

insert into test values (2,2);

analyze table test;

Then I have a subselect with a SUM and a GROUP by and a where clause condition outside of the subselect that would cause the subselect to look at only one row – the one with values (2,2).

select sum_b from
(select a,sum(b) sum_b
from test
group by a) inner_query
where 
a=2;

This takes almost 2 seconds running in an Amazon RDS instance:

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (1.53 sec)

The corresponding Oracle query running in an on-premises Linux VM runs faster:

SQL> select sum_b from
  2  (select a,sum(b) sum_b
  3  from test
  4  group by a) inner_query
  5  where
  6  a=2;

     SUM_B
----------
         2

Elapsed: 00:00:00.08

I realize that the hardware is not the same but I think they are reasonably close. Best I can tell the Oracle version is faster because Oracle pushes the a=2 predicate into the subselect but MySQL doesn’t. The Oracle execution plan shows the a=2 predicate in the inner index scan:

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |     6 |     4   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |       |     1 |     6 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |     1 |     6 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TESTI |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
...
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"=2)

I am still learning MySQL plans but it seems that MySQL does a full index scan:

As I said, I am new to MySQL so I may have missed some way around this. One simple solution would be to write your query without a subselect so that the predicate is in the group by query. This runs about as fast as the Oracle example above:

select sum(b) sum_b
from test
where 
a=2
group by a
--------------

+-------+
| sum_b |
+-------+
|     2 |
+-------+
1 row in set (0.10 sec)

This does a simple index lookup.

I have uploaded a zip of my Oracle and MySQL test here: zip

Maybe if you have used Oracle in the past and are now using MySQL 5.7 you should keep in mind that MySQL 5.7 does not push predicates into subqueries in the same situations that Oracle does and build your queries around this difference.

Bobby

 

 

Categories: DBA Blogs

Follow up from RMOUG Training Days talks

Fri, 2018-02-23 15:34

I have uploaded a zip of my two RMOUG Training Days talks here: zip

During the Toastmasters talk there was a question about finding a club in your area.

Here is how to find a Toastmasters club near you. Go to www.toastmasters.org and click on the FIND A CLUB button. Enter your address or zip and you get a map with club locations like this:

Click on the club to see details of when it meets and whether they are open to new members.

You can call the phone number and you can visit their website if they have one.

Toastmasters provides clubs with hosting for sites like this so it is easy to find out where they meet and to ask questions. In a public club like the one in this example you can just show up at a meeting and they will welcome you. A corporate club will often be just for employees so unless you work for the company that hosts the club you probably will have to look elsewhere. But there are a ton of clubs and most would love to have new members.

At the Python presentation people wanted to look at my PythonDBAGraphs source code. It is on GitHub here: PythonDBAGraphs. This is my largest example of Python code that an Oracle DBA might write. I think that the Oracle community may find PythonDBAGraphs more useful as an example than as a practical application. I use it every day for my work so it has value but it is more command line and less finished than a product that you would buy. Still, it shows how to use Python with Oracle databases and how to graph things using Python. And, it is a fairly simple yet still useful example of Python code.

Anyway, I wanted to publish the PowerPoint files and give these two follow ups based on the questions that I got after each talk.

Bobby

Categories: DBA Blogs

Early morning RMOUG post

Wed, 2018-02-21 06:44

Well, it is early Wednesday morning here at the Westin hotel in Denver where the RMOUG Training Days conference is being held. I can’t sleep anyway so I thought I would write-up some of my impressions of yesterday’s presentations.

I appreciate all the effort people put in making their presentations. Since I have done Toastmasters I’ve learned to appreciate more what goes into being an effective speaker. But, the nature of my work is that I have to be critical of everything people say about technology. Maybe I should say “I have to think critically” instead of “be critical”. The problem with the type of work we do is that it involves a lot of money and that inevitably obscures the truth about the technical details of how things work. So, I want to just sit back and applaud but my technical side wants to tear apart every detail.

A nice perk of being a RMOUG presenter is that I got to attend the pre-conference workshops for free as well as the rest of the talks. In past conferences that I have spoken at that was not the case. So, I went to a four-hour Snowflake workshop. I have read a fair amount on Snowflake so much that the speaker presented was familiar. I wonder how people who had no Snowflake background perceived the talk? Being a nuts and bolts Oracle person I would have liked to dig in more to Snowflake internals and discuss its limitations. Surely any tool has things it does better and things that it does not do so well because of the choices that the developers made in its design. I’m interested in how Snowflake automatically partitions data across files on S3 and caches data in SSD and RAM at the compute level. At least, that is what the information on the web site suggests. But with cloud computing it seems that people frown upon looking under the covers. The goal is to spin up new systems quickly and Snowflake is fantastic at that. Also, it seems to get great performance with little effort. No tuning required! Anyway, it was a good presentation but didn’t get into nuts and bolts tuning and limitations which I would have liked to see.

I spent the rest of the day attending hour-long presentations on various topics. AWS offered a 3 hour session on setting up Oracle on RDS but since I’ve played with RDS at work I decided to skip it. Instead I went to mostly cloud and Devops sessions. I accidentally went to an Oracle performance session which was amusing. It was about tuning table scans in the cloud. The speaker claimed that in Oracle’s cloud you get sub-millisecond I/O which raised a bunch of questions in my mind. But the session was more about using Oracle database features to speed up a data warehouse query. It was fun but not what I expected.

I was really surprised by the Devops sessions. Apparently Oracle has some free Devops tools in their cloud that you can use for on premise work. My office is working with a variety of similar tools already so it is not something we would likely use. But it could be helpful to someone who doesn’t want to install the tools yourself. I’m hopeful that today’s Devops session(s) will fill in more details about how people are using Devlops with databases. I’m mostly interested in how to work with large amounts of data in Devops. It’s easy to store PL/SQL code in Git for versioning and push it out with Flywaydb or something like it. It is hard to make changes to large tables and have a good backout. Data seems to be Devops’s Achilles heel and I haven’t seen something that handles it well. I would love to hear about companies that have had success handling data changes with Devops tools.

Well, I’ve had one cup of coffee and Starbucks doesn’t open for another half hour but this is probably enough of a pre-dawn RMOUG data dump. Both of my talks are tomorrow so today is another day as a spectator. Likely it will be another day of cloud and Devops but I might sneak an Oracle performance talk in for one session.

Bobby

Categories: DBA Blogs

Should I use SQL or Python?

Thu, 2018-01-18 13:57

We had an outage on an important application last Thursday. A particular SQL statement locked up our database with library cache: mutex X waits. I worked with Oracle support to find a bug that caused the issue and we came up with a good workaround. The bug caused a bunch of shared cursor entries. So, I wanted to run a test on a test database to recreate the excess shared cursor entries. I wanted to run the SQL query that caused the outage a bunch of times. Also, we embed the SQL query inside a PL/SQL procedure so I wanted to run the query by calling the procedure. So, I needed to come up with a bunch of calls to the procedure using realistic data as a test script. This blog post is about the decision I had to make about creating the test script. Would I use SQL or Python to quickly hack together my test script? I thought it would be interesting to write about my choice because I am working on my Python for the Oracle DBA talk that encourages Oracle DBAs to learn Python. In this situation I turned to SQL instead of Python so what does that say about the value of Python for Oracle DBAs?

Let me lay out the problem that I needed to solve. Note that I was trying to get this done quickly and not spend a lot of time coming up with the perfect way to do it. I had over 6000 sets of bind variable values that the problem query has used in the past. I used my bind2.sql script to get some sample bind variable values for the problem query. The output of bind2.sql was in this format:

2017-11-27 15:08:56 :B1 1
2017-11-27 15:08:56 :B2 ABC
2017-11-27 15:08:56 :B3 JAFSDFSF
2017-11-27 15:08:56 :B4 345
2017-11-27 15:08:56 :B5 6345
2017-11-27 15:08:56 :B6 10456775
2017-11-27 15:08:56 :B7 34563465
2017-11-27 15:08:56 :B8 433
2017-11-27 15:09:58 :B1 1
2017-11-27 15:09:58 :B2 JUL
2017-11-27 15:09:58 :B3 KSFJSDJF
2017-11-27 15:09:58 :B4 234
2017-11-27 15:09:58 :B5 234253
2017-11-27 15:09:58 :B6 245
2017-11-27 15:09:58 :B7 66546
2017-11-27 15:09:58 :B8 657576
2017-11-27 15:10:12 :B1 1
2017-11-27 15:10:12 :B2 NULL
2017-11-27 15:10:12 :B3 NULL
2017-11-27 15:10:12 :B4 45646
2017-11-27 15:10:12 :B5 43
2017-11-27 15:10:12 :B6 3477
2017-11-27 15:10:12 :B7 6446
2017-11-27 15:10:12 :B8 474747

I needed to convert it to look like this:

exec myproc(34563465,10456775,345,433,6345,'JAFSDFSF','ABC',1,rc);
exec myproc(66546,245,234,657576,234253,'KSFJSDJF','JUL',1,rc);
exec myproc(6446,3477,45646,474747,43,'NULL','NULL',1,rc);

I gave myself maybe a minute or two to decide between using SQL or Python. I choose SQL. All I did was insert the data into a table and then manipulate it using SQL statements. Note that the order of the arguments in the procedure call is not the same as the order of the bind variable numbers. Also, some are character and some are number types.

Here is the SQL that I used:

drop table bindvars;

create table bindvars
(datetime varchar2(20),
 varname varchar2(2),
 varvalue varchar2(40));

insert into bindvars values ('2017-11-27 15:08:56','B1','1');
insert into bindvars values ('2017-11-27 15:08:56','B2','ABC');
insert into bindvars values ('2017-11-27 15:08:56','B3','JAFSDFSF');
insert into bindvars values ('2017-11-27 15:08:56','B4','345');
insert into bindvars values ('2017-11-27 15:08:56','B5','6345');
insert into bindvars values ('2017-11-27 15:08:56','B6','10456775');
insert into bindvars values ('2017-11-27 15:08:56','B7','34563465');
insert into bindvars values ('2017-11-27 15:08:56','B8','433');
insert into bindvars values ('2017-11-27 15:09:58','B1','1');
insert into bindvars values ('2017-11-27 15:09:58','B2','JUL');
insert into bindvars values ('2017-11-27 15:09:58','B3','KSFJSDJF');
insert into bindvars values ('2017-11-27 15:09:58','B4','234');
insert into bindvars values ('2017-11-27 15:09:58','B5','234253');
insert into bindvars values ('2017-11-27 15:09:58','B6','245');
insert into bindvars values ('2017-11-27 15:09:58','B7','66546');
insert into bindvars values ('2017-11-27 15:09:58','B8','657576');
insert into bindvars values ('2017-11-27 15:10:12','B1','1');
insert into bindvars values ('2017-11-27 15:10:12','B2','NULL');
insert into bindvars values ('2017-11-27 15:10:12','B3','NULL');
insert into bindvars values ('2017-11-27 15:10:12','B4','45646');
insert into bindvars values ('2017-11-27 15:10:12','B5','43');
insert into bindvars values ('2017-11-27 15:10:12','B6','3477');
insert into bindvars values ('2017-11-27 15:10:12','B7','6446');
insert into bindvars values ('2017-11-27 15:10:12','B8','474747');

commit;

drop table bindvars2;

create table bindvars2 as
select 
b1.varvalue b1,
b2.varvalue b2,
b3.varvalue b3,
b4.varvalue b4,
b5.varvalue b5,
b6.varvalue b6,
b7.varvalue b7,
b8.varvalue b8
from 
bindvars b1,
bindvars b2,
bindvars b3,
bindvars b4,
bindvars b5,
bindvars b6,
bindvars b7,
bindvars b8
where
b1.datetime = b2.datetime and
b1.datetime = b3.datetime and
b1.datetime = b4.datetime and
b1.datetime = b5.datetime and
b1.datetime = b6.datetime and
b1.datetime = b7.datetime and
b1.datetime = b8.datetime and
b1.varname = 'B1' and
b2.varname = 'B2' and
b3.varname = 'B3' and
b4.varname = 'B4' and
b5.varname = 'B5' and
b6.varname = 'B6' and
b7.varname = 'B7' and
b8.varname = 'B8';

select 'exec myproc('||
B7||','||
B6||','||
B4||','||
B8||','||
B5||','''||
B3||''','''||
B2||''','||
B1||',rc);'
from bindvars2;

I hacked the insert statements together with my Textpad text editor and then loaded the rows into a little table. Then I built a new table which combines the 8 rows for each call into a single row with a column for each bind variable. Finally I queried the second table generating the procedure calls with single quotes, commas and other characters all in the right place.

Now that the rush is past and my testing is done I thought I would hack together a quick Python script to do the same thing. If I had chosen Python how would have I done it without spending a lot of time making it optimal? Here is what I came up with:

Instead of insert statements I pulled the data into a multi-line string constant. Then I split it into a list of strings with each string representing a line. Then I split each line into space delimited strings so each line would have date,time,bind variable name, bind variable value. Finally I looped through each set of 8 lines extracting the bind variable values and then printing the bind variables in the correct order and format.

These are two quickly hacked together solutions. I think the key point is how I stored the data. With SQL I used tables. With Python I used lists. I’m not sure which I like better in this case. I’ve been doing SQL longer but Python wasn’t really harder. I guess my decision under pressure to use SQL shows that I still have more comfort with the SQL way of doing things, but my after the fact Python hacking shows that the Python solution was not any harder. FWIW.

Bobby

 

Categories: DBA Blogs

RMOUG Training Days 2018 early registration deadline rapidly approaching

Fri, 2018-01-05 10:02

The deadline for early registration for RMOUG Training Days 2018 is rapidly approaching. The deadline is January 12. The early registration fee is $385 for RMOUG members and $450 for non-members. There is a packed agenda over the three-day event. The hotel costs are reasonable at $159/night which is very good compared to the hotel prices at the vendor conferences in San Francisco. RMOUG is reasonably priced and high quality training. With all of the change going on with cloud computing and some of the database systems that are competing with Oracle now I am looking forward to hearing from other people about how they are adapting in this changing climate.

I am giving two talks which are both about personal development for DBAs. One is about whether a DBA should learn the Python programming language. The other is about whether DBAs could benefit from communication and leadership training through Toastmasters. Here are the dates and times for my two talks:

Toastmasters for the Oracle DBA
Thursday, February 22
Session 12, 1:30 pm – 2:30 pm

Python for the Oracle DBA
Thursday, February 22
Session 14, 4:00 pm – 5:00 pm

As things change it makes sense for DBAs to continue to develop themselves and I think that my talks and the conference in general will help us to move forward by preparing us to meet the challenges that the future will bring. I hope to see you in Denver and don’t forget to register by January 12.

B0bby

 

Categories: DBA Blogs

Both talks accepted for Collaborate 18

Wed, 2017-12-13 10:35

IOUG accepted both my Toastmasters and Python talks for Collaborate 18. RMOUG also accepted them both so I will be doing these two talks in both February and April. I am a little surprised because I have had talks rejected by IOUG in the past. There are a lot of great speakers competing for speaking slots. This is my first time for RMOUG so I did not know how hard it would be to get a talk accepted. I put both talks in for both conferences not knowing if either would be accepted and both were at both conferences!  So, 2018 will be a busier year than normal for me in terms of speaking at conferences. My last conference was two years ago at Oracle OpenWorld where I spoke about Delphix, a tool that I use with the Oracle database. Next year I’m talking about two things that I feel passionate about. The talks are not about the Oracle database but they are about things I have learned that have helped me in my Oracle database work. They are about how Toastmasters has helped me improve my speaking and leadership skills and about why the Python programming language has become my favorite general purpose scripting tool. I am looking forward to giving the talks. If you are able to attend one of the conferences maybe you could check out one of my talks. Fun.

Bobby

Categories: DBA Blogs

Arizona Oracle User Group meeting Thursday

Tue, 2017-11-14 13:25

The Arizona Oracle User Group (AZORA) is meeting this Thursday, November 16th, 2017 from 12:30 to 3:30 pm Arizona time.

Here is the link to the Meetup:

#meetup_oembed .mu_clearfix:after { visibility: hidden; display: block; font-size: 0; content: " "; clear: both; height: 0; }* html #meetup_oembed .mu_clearfix, *:first-child+html #meetup_oembed .mu_clearfix { zoom: 1; }#meetup_oembed { background:#eee;border:1px solid #ccc;padding:10px;-moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;margin:0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 12px; }#meetup_oembed h3 { font-weight:normal; margin:0 0 10px; padding:0; line-height:26px; font-family:Georgia,Palatino,serif; font-size:24px }#meetup_oembed p { margin: 0 0 10px; padding:0; line-height:16px; }#meetup_oembed img { border:none; margin:0; padding:0; }#meetup_oembed a, #meetup_oembed a:visited, #meetup_oembed a:link { color: #1B76B3; text-decoration: none; cursor: hand; cursor: pointer; }#meetup_oembed a:hover { color: #1B76B3; text-decoration: underline; }#meetup_oembed a.mu_button { font-size:14px; -moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;border:2px solid #A7241D;color:white!important;text-decoration:none;background-color: #CA3E47; background-image: -moz-linear-gradient(top, #ca3e47, #a8252e); background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0, #a8252e), color-stop(1, #ca3e47));disvplay:inline-block;padding:5px 10px; }#meetup_oembed a.mu_button:hover { color: #fff!important; text-decoration: none; }#meetup_oembed .photo { width:50px; height:50px; overflow:hidden;background:#ccc;float:left;margin:0 5px 0 0;text-align:center;padding:1px; }#meetup_oembed .photo img { height:50px }#meetup_oembed .number { font-size:18px; }#meetup_oembed .thing { text-transform: uppercase; color: #555; }
AZ-ORA November Meetup – Dan Morgan live!

Thursday, Nov 16, 2017, 12:30 PM

Republic Services – 2nd Floor Conference Rooms
14400 N 87th St (AZ101 & Raintree) Scottsdale, AZ

13 AZORAS Attending

It’s time to learn more about Oracle and Oracle products! Dan Morgan from Forsythe Meta 7 will be speaking at our November 16 Arizona Oracle User Group (AZ-ORA) meeting on two topics: “Channeling Oracle OpenWorld 2017” for DBAs and SQL & PL/SQL Developers and “Security for Oracle DBAs and Developers” – more details to come…When: Thursday Novembe…

Check out this Meetup →

Here is a link to the user group site with more information:

AZORA Meeting Nov 16, 2017

I’m looking forward to it. I hope to see you there!

Bobby

Categories: DBA Blogs

SQL Profile to fix slow inserts

Mon, 2017-11-06 14:27

I was on call Sunday and got paged about a job that normally runs for 10 to 15 minutes but had run for 5 hours already. I used the coe_xfr_sql_profile.sql script from SQLT to speed up an insert statement that was part of the job. We restarted the job and it completed in a few minutes.

I have written a number of posts about the use of coe_xfr_sql_profile.sql. Sunday’s issue was most like the post on using hints with coe_xfr_sql_profile.sql.

The first thing I did was look at the execution history of the problem insert statement which had sql_id = ‘ba9w9cjy87hd8’. I used my sqlstat.sql script.

SQL_ID        PLAN_HASH_VALUE END_INTERVAL_TIME         EXECUTIONS_DELTA Elapsed Average ms CPU Average ms IO Average ms Cluster Average ms Application Average ms Concurrency Average ms Average buffer gets Average disk reads Average rows processed
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
ba9w9cjy87hd8      3341942963 05-NOV-17 07.00.03.091 AM                2         1302824.53        1162165      572.8985                  0                  2.271                61.1125             6077761               1021                    8.5
ba9w9cjy87hd8      3341942963 05-NOV-17 10.00.23.889 AM                3          846842.04     844443.333       108.995                  0                      0                      0          4475478.33         196.333333             5.66666667

Even though this job runs every weekend and we keep 6 weeks of history in the AWR ba9w9cjy87hd8 only showed up yesterday. I checked the SQL and it did not have any constants. It uses bind variables so the same sql_id should apply every week. My guess is that it normally runs so fast that it did not get recorded in the AWR on previous weeks but this week it chose a terrible plan. I see this a lot with SQL statements that have bind variables. I think that the bad execution plan gets chosen based on some abnormal bind variable value and then it sticks in memory.

The other thing I noticed while looking at the session for this job is that it was not using any parallel processes. I looked at the plan and there were no parallel steps. This system uses a lot of parallel query so I suspected that a parallel plan was the normal plan and the bad plan somehow came out with serial execution.

I checked our test databases to see if they had any execution history for this SQL and they did not. So, I was stuck trying to get a better plan and then using a SQL profile to force that plan on the insert statement. I ended up running the explain plan statement without giving the bind variable a value and it came out with a parallel plan. I used the outline hint from that plan to force the insert statement to run the parallel plan against a test database. I ran the insert with a rollback statement so it wouldn’t commit the new rows. Also I picked a bind variable value from the production database’s AWR to run with my test query.

Next I decided to run my test insert statement against production with the rollback statement. The first time I tried the statement it hung on a TM enqueue. I think this was because the insert was using an append hint and the running job was holding a TM lock. After we killed the production job I could run my test insert with the rollback statement.

I had to get the parallel plan into memory or into the AWR so that coe_xfr_sql_profile.sql would pick it up based on the plan hash value. It felt weird running an insert statement for a production table but I tested it on a test database first and made sure it would be rolled back.

I ran this select after the insert and rollback to get the plan for the insert in production:

select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

Part of the output of the select included the plan hash value of the plan that my test insert used:

Plan hash value: 3809184161

Finally, I forced the good plan which had hash value 3809184161 onto the sql_id ba9w9cjy87hd8  using coe_xfr_sql_profile.sql. Then the job ran in a few minutes.

Bobby

Categories: DBA Blogs

Two Talks Accepted for RMOUG Training Days

Thu, 2017-11-02 14:01

I got two talks accepted for RMOUG Training Days in February. I mentioned these two titles in a earlier post:

  • Python for the Oracle DBA
  • Toastmasters for the Oracle DBA

These two talks are about topics that interest me so I am glad that RMOUG thinks that they are valuable to the conference attendees.

I plan to do the two talks for my DBA coworkers and shorter versions at Toastmasters so I should get some constructive feedback and practice before the conference.

Should be fun. Hope to see you in Denver next February.

My Python posts: url

My Toastmasters posts: url

Bobby

Categories: DBA Blogs

Submitted two talks to Collaborate 18

Fri, 2017-10-20 19:52

I submitted my two talks to Collaborate 18 through IOUG. These are the same two that I submitted to RMOUG Training Days. Hopefully one of the talks will get accepted at one of the two conferences but I don’t know. The nice thing is that they are both professional development talks and a user group meeting might be more open to that sort of talk than a vendor (i.e. Oracle OpenWorld) conference. But, there is a lot of competition out there so we will see. Hopefully I will get to put some of my new Toastmaster skills to work at a conference early next year. 🙂

Bobby

Categories: DBA Blogs

Python sortedcontainers has me thinking

Thu, 2017-10-19 16:23

I was looking at the Python sortedcontainers package and it got me thinking. It is a long convoluted story and I am not sure that I can explain it clearly in a short blog post. I tried to explain all this to my wife in the last few minutes as we were driving up to a friend’s house last night and I’m sure it was confusing the way I explained it. But, I’m hoping that I can capture some of the ideas that I have thought about in a way that will be useful to others.

I have studied some computer science topics that do not directly relate to my work with Oracle databases and my review of the sortedcontainers implementation documentation tied together several things that I have studied and related them back to my work with Oracle performance tuning. I have not tested sortedcontainers to make sure that it does everything the web site says it does. But, I think it is the best Python package for doing balanced tree type of structures in memory. An AVL tree or B-tree keeps data ordered so you can quickly search for a range of key values and get them out in sorted order. Normal Oracle indexes are a type of B-tree but on disk with blocks cached in memory when queries access them. AVL trees are binary trees so each node points to at most 2 children. B-tree nodes can have many children. Sortedcontainers seem to work like a balanced tree with 1000 or so max children per node. I think it makes efficient used of Python’s built-in list objects. It seems to work well with caching within the CPU. I have not carefully reviewed the theory and tested all this out to prove that it is right but it seems likely that it is. I think it seems convincing because it ties back to other computer science topics that I have studied and to my experience with Oracle performance tuning.

I have been slowly working through an algorithms class on MIT’s OCW website. I am on a section about AVL trees. So, I was looking around at AVL trees in Python. I noticed that Rosetta Code had an AVL tree topic but no Python example until I added one. I also looked around on PyPI for an AVL tree Python package. Based on my search, I thought that bintrees was the most mature, but its web page has a note saying “Use sortedcontainers instead”. So, that made me think that sortedcontainers was the best balanced tree option in Python. The algorithms class talks about how to prove that you can work with AVL trees in O(log n) time. The sortedcontainers performance documentation has a complex explanation of its big O complexity. Also, I think that my class will discuss some of the concepts used in the sortedcontainers analysis in future lessons. So, that motivates me to go forward.

The assembly language book that I worked through helped me understand how to write programs that run faster because they make better use of the x86-64 processor’s cache and registers. Its creator seems to have designed sortedcontainers with CPU caches in mind. Right or wrong, in my mind this ties back to memory caches that affect Oracle database performance. How much of Oracle tuning relates back to how systems cache database blocks in RAM and where? You have the database block cache of course. You also have operating system filesystem cache which you might bypass with direct I/O. You may have high-speed memory cache within your SAN’s storage server. I don’t know about today but in the past disk controller cards and even disk drives themselves had memory caches. You might say, joking, that “cache is king” in database performance. At least, you have to say it is important to understand when and where database systems cache disk blocks in memory to understand why you are getting the performance you are seeing.

So, I guess my mind connected sortedcontainers with my algorithms class and assembly language book. I also connected sortedcontainers back to Oracle performance tuning. It makes me feel that digging into some computer science training is not a waste of time. It helps me to step back from Oracle database specific study and get a little theory. Also, my database work is focusing more and more on the x86-64 architecture and the Linux platform so looking at computer science on the same platform that I use for work has clear benefits.

So, I’m concerned that I have not made this post helpful to people who read it. Is it just about my experience or does it have a point for other people? Maybe the point is that it can’t hurt for an Oracle DBA to learn some computer science. Maybe you are like me and studied C.S. in school many years ago. Maybe you have learned Oracle on the job and don’t have a C.S. background. Maybe the message for both of us from my story about sortedcontainers and my “Aha!” moment is that there are benefits to studying a little computer science even if it does not directly relate to your job. There is only so much time in an Oracle DBA’s day and you can’t learn everything, but maybe it is worth putting some time into learning some C.S. theory if you can.

Bobby

Categories: DBA Blogs

Submitted two abstracts to RMOUG Training Days 2018

Fri, 2017-10-06 18:05

I finally broke down and submitted my two talks to the RMOUG Training Days 2018. I’m not sure how likely I am to get my talks accepted but I learned a lot just through constructing my two abstracts. I have two talks that I have processed in my mind for a while:

  • Python for the Oracle DBA
  • Toastmasters for the Oracle DBA

These are just two things I’ve worked with and that I think have helped me in my job. Plus, they are two topics that interest me. Why talk about something boring just because you think other people want you to?

Anyway, it was fun to write the abstracts for two topics that I am passionate about. I have never been to RMOUG Training Days but I have heard a lot of good things. Hopefully I will get accepted and be able to get these talks out to the public.

I’m off next week so I don’t expect to blog until I get back to work. Maybe after I get back I can start to outline my Toastmasters talk in a few posts. I have given a couple of Toastmasters talks with similar material but this would be geared specifically for people who are more like me in the sense that they are Oracle DBAs.

Anyway, it was fun to get the abstracts turned in.

Bobby

Categories: DBA Blogs

Duplicate blog to laptop VM

Fri, 2017-09-22 18:48

I started to write this blog post and then realized that in the process of making a copy of my blog on my laptop I whacked my connection to WordPress. But, it was easy to resolve it by disconnecting Jetpack from WordPress, clearing my browser cache, and then reconnecting. Whew! I worried that messing with this stuff would cause some problem but the blog still seems to work.

My idea was to bring up a copy of this blog site on a VirtualBox VM on my laptop. I have thought about doing this in the past but did not have any good reason to do so. I have mainly acted like an end user for this blog software and to iPage, the company that hosts it. I have not tried to delve into the underlying PHP code or mess with the MySQL database. But, my database team has recently started supporting MySQL and I am trying to train myself up a bit so it made sense to play with duplicating this small MySQL app.

I duplicated the blog in three main steps:

  1. Setup a VirtualBox VM
  2. Setup a MySQL database
  3. Setup the web site

STEP 1 – SETUP A VIRTUALBOX VM

I am still using the VirtualBox NAT networking that I described in an earlier post. I created a VM called Blog with this ip and port forwarding details:

Blog ip

10.0.2.17

Blog port forwarding

61014 to 22
61015 to 80

I duplicated an existing Oracle Enterprise Linux 7 VM and modified the ip and setup the port forwarding. Then I did a yum update to catch up all the existing packages. I also disabled the firewall so that it would not get in the way.

yum -y update
systemctl disable firewalld
systemctl stop firewalld

STEP 2 – SETUP MYSQL DATABASE

I downloaded mysql57-community-release-el7-11.noarch.rpm from http://dev.mysql.com/downloads/repo/yum/ and ran the following commands to install MySQL:

yum localinstall mysql57-community-release-el7-11.noarch.rpm
yum install mysql-community-server
service mysqld start
service mysqld status

Then I ran a series of SQL commands logged into MySQL to setup the user and database for the blog:

grep 'temporary password' /var/log/mysqld.log

mysql -uroot -p
use temporary root password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Bl0gC0py!';

CREATE DATABASE blogdb;

GRANT ALL PRIVILEGES ON blogdb.* TO "blogdbuser"@"localhost"
IDENTIFIED BY "Bl0gC0py!";

FLUSH PRIVILEGES;

EXIT

Next I took a backup of my blog database that I got from iPage and made the following string replacements in the SQL commands:

Replace all instances of

http://www.bobbydurrettdba.com

with 

http://localhost:61015

Also

bobby@bobbydurrettdba.com

replace with

bobby@blogvm.com

and

@bobbydurrettdba

with

@blogvm

Finally

bobbydurrettdba

with 

blogvm

I was trying to make sure that I didn’t whack anything in my real blog and that it worked with my localhost:61015 web site host name and port. I had to add two lines to the beginning of the sql script to make it work:

use blogdb
SET sql_mode = '';

I ran the script like this:

mysql -v --force -ublogdbuser -pBl0gC0py! < database.sql > database.out 2> database.err

I checked database.err and it only had a warning about using the password on the command line.

STEP3 – SETUP WEB SITE

Now that the database was setup and loaded with data I worked on the web site.

First, I installed the Linux packages for php which pulled in the web server as a dependency:

yum install php php-common php-mysql php-gd php-xml php-mbstring php-mcrypt 

Edited httpd.conf to setup web server:

vi /etc/httpd/conf/httpd.conf

Replace all instances of AllowOverride None or none
with AllowOverride all

Added VirtualHost lines at the end:

Added host to /etc/hosts

vi /etc/hosts

add this line 

127.0.0.1  blog

I forgot to mention in step 1 that when I created the VM I renamed it to blog using hostnamectl.

Next I created the web site directory and populated it with files from my ftp backup of my website.

mkdir /var/www/html/wordpress

copy my blog files to /var/www/html/wordpress

Next I edited the WordPress configuration file to work with the database:

cd /var/www/html/wordpress

vi wp-config.php

/** The name of the database for WordPress */
define('DB_NAME', 'blogdb');

/** MySQL database username */
define('DB_USER', 'blogdbuser');

/** MySQL database password */
define('DB_PASSWORD', 'Bl0gC0py!');

/** MySQL hostname */
define('DB_HOST', 'localhost');

Finally I restarted the web server and set the web server to automatically start on reboot:

systemctl restart httpd.service 

systemctl enable httpd.service

Finally, I tested the web site at http://localhost:61015 and it looked a lot like this blog site.

Bobby

Categories: DBA Blogs

Quick Python script to backup remote directory using ftp

Wed, 2017-09-20 18:47

I looked around for some other ways to do this but decided to just code this up in Python. It connects to a remote Linux server using ftp and recursively copies all the files and directories back to a Windows machine.

Here is the source:

.gist table { margin-bottom: 0; }

This is an example of the ease of use of Python for quick scripting. It uses a low-level ftp library called ftplib.

Bobby

Categories: DBA Blogs

Added save and restore data function to PythonDBAGraphs

Mon, 2017-09-18 18:30

I pushed out a quick change to PythonDBAGraphs to automatically save the data for any graph that you make so that you can redraw the graph later. This is better than saving an image file because the redrawn graph lets you see details about the points on the graph when you hover the mouse over the points.

Now when you generate a graph you get a line like this:

Saving data in C:\temp\ASH active session count for MYDB database.txt

When you want to see the graph again you run show_saved.py like this:

python show_saved.py
Enter name of data file to be restored: C:\temp\ASH active session count for MYDB database.txt

Bobby

Categories: DBA Blogs

Python for the Oracle DBA – Connects to everything

Wed, 2017-09-13 15:43

In my introduction to my Python for the Oracle DBA topic I said that Python can connect to everything that an Oracle DBA needs. I want to use this post to expand on that and talk about the Python modules and packages that I have used.

As you might expect, Python includes built-in modules and ones that you can add. Python comes with a large built-in library called the Python Standard Library. To use these modules you just import them into your scripts. There is nothing to install. You can also install new modules from PyPI, the Python Package Index. You install a PyPI package using a tool called pip that acts a lot like Red Hat Linux’s yum utility. Here is the pip command line help:

D:\>pip

Usage:
  pip  [options]

Commands:
  install                     Install packages.
  download                    Download packages.
  uninstall                   Uninstall packages.
 ... etc. ...

The plotting module that I use, Matplotlib,  is part of a larger group of modules called SciPi which I assume stands for Scientific Python. I have only used Matplotlib and Numpy but SciPi includes a number of other modules. You can get the various SciPy modules from PyPI using pip as described above.

Obviously an Oracle DBA wants to connect to Oracle databases. The cx_Oracle package lets you connect to an Oracle database. I am still using version 5.3 but a newer version is out and supports a variety of features. cx_Oracle requires an Oracle client. I have been mainly using a 32 bit 11.2 Oracle Windows client with cx_Oracle 5.3 on my Windows 7 corporate laptop. There is a Python standard for database APIs so if you install modules to connect to various types of databases they will hopefully follow this standard and have similar interfaces. I have connected to Snowflake, MySQL, and Big Query databases using Python as well but have not done anything significant with them.

I have written a couple of scripts on Linux that I would have written using bash shell scripting in the past. I describe in an earlier post how you can use the built-in subprocess module to run SQL*Plus from Python. More recently I wrote a script to test all of my Unix passwords with the Paramiko package which does ssh. Here is the script:

.gist table { margin-bottom: 0; }

It just tries to log into a host using ssh and prints out a useful message if the login fails. It runs the hostname command after it logs in. Note the import statements at the top of the program. I installed the paramiko module from PyPI but sys and socket are part of the standard library.

The last thing I want to mention is how you can use Python with Amazon Web Services or AWS. I have started to learn how to work with AWS for my job and have done some very basic Python scripts to just connect to AWS and run simple commands. Amazon calls their main Python module Boto 3. We have production Python scripts that other people in my company have written so Python is alive and well in Amazon’s cloud. It probably would not hurt for an Oracle DBA to learn some Python just because we might need it when working with cloud services. I have already mentioned the cloud based databases Snowflake and Big Query so those are also examples of Python working with the cloud.

So, I have mainly given an overview of the Python packages and modules that I have used. I have only played with some of them. I have used others such as cx_Oracle, Matplotlib, Paramiko, and subprocess for real work. Based on my experience so far I think Python can connect to just about anything I would need as an Oracle DBA.

Bobby

Categories: DBA Blogs

Python for the Oracle DBA – Easy to use

Tue, 2017-09-12 16:55

In my earlier post I said that Python is easy to use. I want to fill in some details in this post. I have two main points:

  1. Python is not easy to learn
  2. Python is easy to use after you learn it

My experience supports these points. I don’t really have time to research what other people say and somehow prove these points with scientific evidence. But, I do have my own recent experience with the language and I have thought about what I wanted to say about it to other people.

I had to work pretty hard to learn Python. I’m a reasonably technical person with experience programming in a number of languages. I mean, I have a CS degree from Harvard and I’ve been working with databases for over 20 years. But, when I started working through the Python Tutorial it did not come very easily. Python’s documentation on docs.python.org is very good but the tutorial didn’t work that well for me. Maybe it was too fast for someone who was new to the language. I thought that their explanation of list slices was hard to follow. Also, the range function in for loops seemed weird compared to other languages. When they started talking about list comprehensions and lambda expressions it was too much. I think the tutorial just covers too much ground too quickly. I have not seen a need for a lot of the features that are in the tutorial anyway. It probably makes sense to learn the language some other way. I really learned Python by taking two edX classes. The edX classes included programming assignments and a slower pace than the tutorial. There are even easier classes than the ones I took so probably someone who is new to Python should find a class that is at their pace and not bang their head against the wall trying to work through the tutorial.

When I say that Python is easy for an Oracle DBA to use I think I mean that once you learn the language it is easy to keep using it. I think that the built-in list and dictionary data structures are the strongest features of Python. It takes some effort to learn the syntax and what it means but once you know it they are great building blocks for your Python scripts. If you forget some detail the documentation is always there. Just Google Python list or Python dictionary. You will find most of the answers on Python.org’s documentation site and Stack Overflow. But, Google brings up all kinds of helpful information. I think the other thing that makes Python easy to use is its dynamic typing. You don’t have to declare a variable’s type. Just use the variable. It is nice for quick and dirty scripts. Just put a number or a string or a list in a variable and then use it. The other nice feature is that Python requires indentation. If you have an if statement or loop you have to indent the statements inside the if or loop. The style guide recommends 4 spaces. This is great for hacking together simple scripts. Python tells you right away if your spaces are off so you can make sure that you really have the things where you want them. Also, you do not have to use the more complicated features. I’ve used a bit of the object-oriented features but not much. I’ve never used a list comprehension or lambda expression. I have just used plan old ifs, loops, and functions and done everything I needed.

There is a lot more I can say about this and a lot more examples that I could give but the key point is that in my opinion and my experience Python is an easy language to come back to after you have been away from it for a while. It took work for me to learn the language at first, but once I had done so it was pretty easy to keep coming back to it. I think this is true because you can do so much with the main easy to use features of the language and because of the high quality documentation and other resources available.

Bobby

 

Categories: DBA Blogs

Python for the Oracle DBA – Outline/Intro

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

Pages