Bobby Durrett's DBA Blog

Subscribe to Bobby Durrett's DBA Blog feed
Oracle database performance
Updated: 4 days 3 hours ago

So Far So Good with Force Logging

Mon, 2019-10-28 18:55

I mentioned in my previous two posts that I had tried to figure out if it would be safe to turn on force logging on a production database that does a bunch of batch processing on the weekend: post1, post2. We know that many of the tables are set to NOLOGGING and some of the inserts have the append hint. We put in force logging on Friday and the heavy weekend processing ran fine last weekend.

I used an AWR report to check the top INSERT statements from the weekend and I only found one that was significantly slower. But the table it inserts into is set for LOGGING, it does not have an append hint, and the parallel degree is set to 1. So, it is a normal insert that was slower last weekend for some other reason. Here is the output of my sqlstatsumday.sql script for the slower insert:

Day        SQL_ID        PLAN_HASH_VALUE Executions 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
---------- ------------- --------------- ---------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
2019-09-22 6mcqczrk3k5wm       472069319        129         36734.0024     20656.8462    462.098677                  0                      0             38.8160385          666208.285         1139.86923             486.323077
2019-09-29 6mcqczrk3k5wm       472069319        130         44951.6935     27021.6031    573.245664                  0                      0             21.8764885           879019.29         1273.52672             522.083969
2019-10-06 6mcqczrk3k5wm       472069319        130         9624.33742     7530.07634    264.929008                  0                      0             1.26370992          241467.023         678.458015             443.427481
2019-10-13 6mcqczrk3k5wm       472069319        130         55773.0864      41109.542    472.788031                  0                      0             17.5326031          1232828.64         932.083969             289.183206
2019-10-20 6mcqczrk3k5wm       472069319        130         89684.8089     59261.2977    621.276122                  0                      0             33.7963893          1803517.19         1242.61069             433.473282
2019-10-27 6mcqczrk3k5wm       472069319        130         197062.591     144222.595    561.707321                  0                      0             362.101267          10636602.9         1228.91603             629.839695

It averaged 197062 milliseconds last weekend but 89684 the previous one. The target table has always been set to LOGGING so FORCE LOGGING would not change anything with it.

One of the three INSERT statements that I expected to be slowed by FORCE LOGGING was faster this weekend than without FORCE LOGGING last weekend:

Day        SQL_ID        PLAN_HASH_VALUE Executions 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
---------- ------------- --------------- ---------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
2019-09-22 0u0drxbt5qtqk       382840242          1         2610257.66         391635    926539.984                  0                      0              13718.453             5483472           745816.5                3689449
2019-09-29 0u0drxbt5qtqk       382840242          1         17127212.3        1507065    12885171.7                  0                      0             14888.4595            18070434          6793555.5             15028884.5
2019-10-06 0u0drxbt5qtqk       382840242          1         3531931.07         420150    2355139.38                  0                      0             12045.0115             5004273            1692754                5101998
2019-10-13 0u0drxbt5qtqk       382840242          1         1693415.59         180730    1250325.41                  0                      0               819.7725           2242638.5           737704.5                2142812
2019-10-20 0u0drxbt5qtqk       382840242          1         5672230.17         536115    3759795.33                  0                      0             10072.9125             6149731            2332038              2806037.5
2019-10-27 0u0drxbt5qtqk       382840242          1         2421533.59         272585    1748338.89                  0                      0               9390.821           3311219.5           958592.5              2794748.5

It ran 2421533 milliseconds this weekend and 5672230 the prior one. So clearly FORCE LOGGING did not have much effect on its overall run time.

It went so well this weekend that we decided to leave FORCE LOGGING in for now to see if it slows down the mid-week jobs and the web-based front end. I was confident on Friday, but I am even more confident now that NOLOGGING writes have minimal performance benefits on this system. But we will let it bake in for a while. Really, we might as well leave it in for good if only for the recovery benefits. Then when we configure GGS for the zero downtime upgrade it will already have been there for some time.

The lesson for me from this experience and the message of my last three posts is that NOLOGGING writes may have less benefits than you think, or your system may be doing less NOLOGGING writes than you think. That was true for me for this one database. It may be true for other systems that I expect to have a lot of NOLOGGING writes. Maybe someone reading this will find that they can safely use FORCE LOGGING on a database that they think does a lot of NOLOGGING writes, but which really does not need NOLOGGING for good performance.

Bobby

Categories: DBA Blogs

Impact of Force Logging

Mon, 2019-09-23 17:29

I am working on upgrading an Oracle database from 11.2.0.4 to 19c and migrating it from HP Unix to Linux. This 15-terabyte database is too large to copy from the old to the new system during our normal weekend downtime window. It also has a ton of weekend batch updates that overlap the normal weekend change window so it would be best for our business processing if the cut over from the old to the new system was as fast as possible.

I want to use GoldenGate to minimize the downtime for the cutover using an approach similar to what is described in this Oracle document:

Zero Downtime Database Upgrade Using Oracle GoldenGate

You start GoldenGate collecting changes on the current production system and then take your time copying the 15 TB of data from the old to new system. Once you are done with the initial load you apply the changes that happened in the meanwhile. Finally, you cut over to the new system. You could even switch the direction of the replication to push changes on the new production system back to the old system to allow for a mid-week back out several days after your upgrade. Pretty cool. A teammate of mine successfully used this approach on an important database some years back.

But the database that I am working on now, unlike the one that my colleague worked on, has a lot of tables set to nologging. Under the right conditions inserts into tables set to nologging are not written to the redo logs and will be missed by GoldenGate. This Oracle article recommends setting your database to FORCE LOGGING so GoldenGate will not miss any updates:

In order to ensure that the required redo information is contained in the Oracle redo logs for segments being replicated, it is important to override any NOLOGGING operations which would prevent the required redo information from being generated. If you are replicating the entire database, enable database force logging mode.

Oracle GoldenGate Performance Best Practices

We could also switch all our application tables and partitions in the source system to logging but we have so many I think we would set the whole database to force logging.

But the big question which I touched on in my previous post is whether force logging will slow down our weekend batch processing so much that we miss our deadlines for weekend processing to complete and affect our business in a negative way. The more I investigate it the more convinced I am that force logging will have minimal impact on our weekend jobs. This is an unexpected and surprising result. I really thought that our batch processing relied heavily on nologging writes to get the performance they need. It makes me wonder why we are using nologging in the first place. It would be a lot better for backup and recovery to have all our inserts logged to the redo logs. Here is a nice Oracle Support document that lays out the pros and cons of using nologging:

The Gains and Pains of Nologging Operations (Doc ID 290161.1)

I have an entry in my notes for this upgrade project dated 8/26/19 in which I wrote “Surely force logging will bog the … DB down”. Now I think the opposite. So, what changed my mind? It started with the graph from the previous post:

Graph From Previous Post with Little Direct Writes I/O

I was really surprised that the purple line was so low compared to the other two. But I felt like I needed to dig deeper to make sure that I was not just misunderstanding these metrics. The last thing I want to do is make some production change that slows down our weekend processes that already struggle to meet their deadlines. I was not sure what other metrics to look at since I could not find something that directly measures non-logged writes. But then I got the idea of using ASH data.

In my “Fast way to copy data into a table” post I said that to copy data quickly between two Oracle tables “you want everything done nologging, in parallel, and using direct path”. I may have known then and forgotten but working on this now has me thinking about the relationship between these three ways of speeding up inserts into tables. I think there are the following two dependencies:

  • Nologging requires direct path
  • Parallel requires direct path

Oracle document “Check For Logging / Nologging On DB Object(s) (Doc ID 269274.1)” says the first one. In the second case if you have a target table set to parallel degree > 1 and you enable parallel DML you get direct path writes when you insert into the target table.

From all this I got the idea to look for direct path write waits in the ASH views. I could use ASH to identify insert statements that are using direct path. Then I could check that the target tables or partitions are set to nologging. Then I would know they are doing non-logged writes even if I did not have a metric that said so directly.

directwritesql.sql looked at all the SQL statements that had direct write waits over the entire 6 weeks of our AWR history. The output looks like this:

     select
  2  sql_id,count(*) active
  3  from DBA_HIST_ACTIVE_SESS_HISTORY a
  4  where
  5  event = 'direct path write'
  6  group by sql_id
  7  order by active desc;

SQL_ID            ACTIVE
------------- ----------
2pfzwmtj41guu         99
g11qm73a4w37k         88
6q4kuj30agxak         58
fjxzfp4yagm0w         53
bvtzn333rp97k         39
6as226jb93ggd         38
0nx4fsb5gcyzb         36
6gtnb9t0dfj4w         31
3gatgc878pqxh         31
cq433j04qgb18         25

These numbers startled me because they were so low. Each entry in DBA_HIST_ACTIVE_SESS_HISTORY represents 10 seconds of activity. So over 6 weeks our top direct path write waiter waited 990 seconds. Given that we have batch processes running full out for a couple of days every weekend 990 seconds over 6 weekends is nothing.

I took the top SQL ids and dumped out the SQL text to see what tables they were inserting into. Then I queried the LOGGING column of dba_tables and dba_tab_partitions to see which insert was going into a table or partition set to nologging.

select logging,table_name
from dba_tables
where owner='MYOWNER' and
table_name in
(
... tables inserted into ...
)
order by table_name;


select logging,table_name,count(*) cnt
from dba_tab_partitions
where table_owner='MYOWNER' and
table_name in
(
... tables inserted into ...
)
group by logging,table_name
order by table_name,cnt desc;

This simple check for LOGGING or NOLOGGING status eliminated several of the top direct path write waiters. This process reduced the list of SQL ids down to three top suspects:

SQL_ID            ACTIVE
------------- ----------
cq433j04qgb18         25
71sr61v1rmmqc         17
0u0drxbt5qtqk         11

These are all inserts that are not logged. Notice that the most active one has 250 seconds of direct path write waits over the past 6 weeks. Surely enabling force logging could not cause more than about that much additional run time over the same length of time.

I got the idea of seeing what percentage of the total ASH time was direct path write waits for each of these SQL statements. In every case it was small:

cq433j04qgb18

TOTAL_SAMPLE_COUNT DW_SAMPLE_COUNT DW_SAMPLE_PCT
------------------ --------------- -------------
              2508              25    .996810207
 
71sr61v1rmmqc

TOTAL_SAMPLE_COUNT DW_SAMPLE_COUNT DW_SAMPLE_PCT
------------------ --------------- -------------
              1817              17    .935608145

0u0drxbt5qtqk

TOTAL_SAMPLE_COUNT DW_SAMPLE_COUNT DW_SAMPLE_PCT
------------------ --------------- -------------
              8691              11    .126567714

TOTAL_SAMPLE_COUNT was all the samples for that SQL_ID value for the past 6 weeks. DW_SAMPLE_COUNT is the same count of samples that are direct write waits that we already talked about. DW_SAMPLE_PCT is the percentage of the total samples that were direct write wait events. They were all around 1% or lower which means that write I/O time was only about 1% of the entire run time of these inserts. The rest was query processing best I can tell.

Also I used my sqlstat3 script to look at the average run time for these inserts:

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
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
71sr61v1rmmqc      3333358322 01-SEP-19 12.00.46.381 PM                1         2768879.28         892080    207085.624                  0                      0                  3.817             9028323            1045428               19337954
71sr61v1rmmqc      3333358322 08-SEP-19 10.00.43.551 AM                0         264428.594          98840     28257.339                  0                      0                  3.657              177736             143345                      0
71sr61v1rmmqc      3333358322 08-SEP-19 11.00.49.648 AM                1          2352509.9         767440    160933.191                  0                      0                      0             8729437             791837               19110340
71sr61v1rmmqc      3333358322 15-SEP-19 11.00.03.027 AM                1         3090070.21         904310    190593.062                  0                      0                  2.192             9095421             949579               19470026

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
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
0u0drxbt5qtqk       382840242 01-SEP-19 02.00.23.436 AM                1         29281391.6        3211050    16624311.7                  0                      0              99532.905            37076159           14440303               24479240
0u0drxbt5qtqk       382840242 08-SEP-19 02.00.11.424 AM                1         3871668.37         424670    2563007.61                  0                      0               1236.003             4622248            2457057                2468983
0u0drxbt5qtqk       382840242 15-SEP-19 03.00.12.349 AM                0         5161808.16         615520    3358994.55                  0                      0              20656.365             6251060            2801828                      0
0u0drxbt5qtqk       382840242 15-SEP-19 04.00.33.661 AM                1         2412910.02         240650    1741053.89                  0                      0                699.717             3050529            1542895                4638794

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
------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
cq433j04qgb18      1267318024 02-SEP-19 10.00.57.871 PM                1          77132.892          51180     10719.692                  0                      0                  2.003              460346              47055                 772468
cq433j04qgb18      1267318024 03-SEP-19 10.00.55.601 PM                1         116064.154          68350      9808.483                  0                      0              15746.609              911571              20422                1256808
cq433j04qgb18      1267318024 04-SEP-19 10.00.31.071 PM                1         106594.074          64030      6328.462                  0                      0              15603.102              777779              14945                1561172
cq433j04qgb18      1267318024 05-SEP-19 10.00.13.265 PM                0          44435.247          31810      2760.438                  0                      0                365.132              139637               5111                 257770
cq433j04qgb18      1267318024 09-SEP-19 10.00.18.346 PM                1         791385.928         324050    171504.931                  0                      0               7484.358             6430665             600703               14262960
cq433j04qgb18      1267318024 10-SEP-19 10.00.29.224 PM                0         1685763.14         676210    304045.354                  0                      0                283.296            11884045             838290               16268667
cq433j04qgb18      1267318024 11-SEP-19 10.01.00.800 PM                0         369644.825         172120     42679.357                  0                      0                  3.929             2443772             151369                3901044
cq433j04qgb18      1267318024 12-SEP-19 10.00.28.499 PM                0          30381.614          25630      1191.884                  0                      0                 422.55               98580               3389                 184812
cq433j04qgb18      1267318024 13-SEP-19 10.00.07.502 PM                0         173286.567         109990     11461.865                  0                      0                 359.37             1475324              63073                2360818
cq433j04qgb18      1267318024 16-SEP-19 10.00.52.175 PM                1         190203.822          93680     47585.666                  0                      0                122.658             1221886             348327                2955258

These queries run at most a couple of hours. If direct path writes are 1% of their total run time, I estimated that force logging would add about 1% to the elapsed time or about 2 minutes per execution.

The final step was to try to run one of these top nologging I/O inserts in a test environment with and without force logging to see if the test matches the expected performance slowdown. I was not able to run 0u0drxbt5qtqk without setting up a more elaborate test with the development team. My test of cq433j04qgb18 ran considerably faster with force logging than without it so I think other factors were hiding whatever effect force logging had. But 71sr61v1rmmqc had some nice results that matched my estimates well. This is on a Delphix clone of production so the data was up to date with prod but the underlying I/O was slower.

71sr61v1rmmqc results running 5 times normal 5 times force logging

The individual run times are in seconds and the averages are listed in seconds and in minutes. I ran the insert 5 times with no force logging and 5 times with it alternating. I dropped the primary key and unique index of the target table to keep from getting constraint errors. I rolled back the insert each time. It averaged about 1.2 minutes more out of 40 minutes of run time which is about a 3% increase. My estimate from ASH was about 1% so this test matches that well.

The final test remains. In some upcoming production weekend, I will put in a change to flip the database to force logging and see how it goes. My tests were run on a test system with a different storage system and with no other activity. We might see different results on a heavily loaded system with a queue for the CPU. But, after all this analysis and testing I feel confident that we won’t be able to tell that force logging is enabled. Unfortunately, we sometimes have performance issues anyway due to plan changes or data volume so the force logging switch might get blamed. But I feel confident enough to push for the final test and I think we ultimately will pass that test and be able to use force logging to enable GoldenGate to support a short cut over time for our migration and upgrade project.

Bobby

P.S. A good question came in as a comment about direct path write waits and asynchronous I/O. The system I am testing on does not support async I/O because it is HP Unix and a filesystem. This older blog post talks a bit about async and direct I/O on HP-UX:

https://www.bobbydurrettdba.com/2013/04/26/db_writer_processes-dbwr_io_slaves-with-no-asynch-io-on-hp-ux/

So, your mileage may vary (YMMV) if you do these same queries on a system with asynchronous writes. Linux filesystems support async writes and on HP-UX our RAC system on ASM supports it. It is one of the challenges of writing blog posts. Other people may be in different situations than I am.

Categories: DBA Blogs

Estimating how much write I/O is not logged

Thu, 2019-09-12 11:30

I am trying to figure out how much non-logged write I/O an Oracle database is doing. I want to run an ALTER DATABASE FORCE LOGGING command on the database so that I can use Oracle GoldenGate(GGS) which reads updates from Oracle’s logs. GGS will miss writes that are not logged. But if I turn on force logging it may slow down applications that depend on non-logged writes for good performance. So, I want to find some Oracle performance metrics that give me an idea about how much non-logged write I/O we have so I have an estimate of how much force logging will degrade performance.

I created SQL*Plus and PythonDBAGraphs reports based on DBA_HIST_IOSTAT_FUNCTION that gives some insight into the write I/O that is not logged. Here is the Python based graphical version of the report for one recent weekend:

Possible NOLOGGING Write I/O

The purple-blue line represents Direct Writes. These may or may not be logged. The red-orange line represents writes through the DBWR process. These are always logged. The light green line represents log I/O through the LGWR process. My theory is that if the purple line is above the green line the difference must be write I/O that is not logged. But if the green line is equal to or greater than the purple line you really do not know if there was any write I/O that was not logged. But if there is non-logged write I/O it cannot be more than the amount indicated by the purple line. So, this graph does not directly answer my question about how much write I/O was not logged but it does show some numbers that relate to the question.

I did some experiments with the V$IOSTAT_FUNCTION view that populates DBA_HIST_IOSTAT_FUNCTION to see what values it gives for Direct Writes, DBWR, and LGWR using different scenarios. Here is the zip of these scripts and their output: nologgingscriptsandlogs09122018.zip. I tested four scenarios:

  1. Insert append nologging
  2. Insert append logging
  3. Insert noappend logging
  4. Insert noappend nologging

1 and 2 did Direct Writes. 3 and 4 did DBWR writes. 2, 3, and 4 all did LGWR writes.

Here are the relevant sections of the output that correspond to these statements.

Insert append nologging:

FUNCTION_NAME      WRITE_DIFF_MEGABYTES
------------------ --------------------
Direct Writes                      4660
LGWR                                 46
DBWR                                 27

Insert append logging:

FUNCTION_NAME      WRITE_DIFF_MEGABYTES
------------------ --------------------
LGWR                               4789
Direct Writes                      4661
DBWR                                 37

Insert noappend logging:

FUNCTION_NAME      WRITE_DIFF_MEGABYTES
------------------ --------------------
DBWR                               6192
LGWR                               4528
Direct Writes                         2

Insert noappend nologging:

FUNCTION_NAME      WRITE_DIFF_MEGABYTES
------------------ --------------------
DBWR                               6213
LGWR                               4524
Direct Writes                         2

This pattern is similar to that in a Ask Tom post that I wrote about a while back. That post showed the different situations in which writes were logged or not. I also got some ideas about direct writes and logging from this Oracle support document:

Check For Logging / Nologging On DB Object(s) (Doc ID 269274.1)

It sounds like inserts into tables that go through the normal processing eventually get written to disk by DBWR but inserts with the append hint write directly to the datafiles and may or may not be logged and written out by LGWR.

These tests and documents gave me the idea of building a report and graph based on DBA_HIST_IOSTAT_FUNCTION showing the values for the Direct Writes, DBWR, and LGWR FUNCTION_NAME values. The graph above shows an example of a real system. I was surprised to see how high the DBWR and LGWR values were and how low the Direct Writes were. That made me think that it would be safe to try turning on FORCE LOGGING because it likely will have minimal impact on the overall weekend processing. It gave me enough evidence to push for approval to do a controlled test of enabling FORCE LOGGING in production over an upcoming weekend. I will update this post with the results if we move forward with the test.

Bobby

Categories: DBA Blogs

September 27 Arizona Oracle User Group Meeting

Wed, 2019-09-04 10:30

The Arizona Oracle User Group (AZORA) is cranking up its meeting schedule again now that the blazing hot summer is starting to come to an end. Our next meeting is Friday, September 27, 2019 from 12:00 PM to 4:00 PM MST.

Here is the Meetup link: Meetup

Thank you to Republic Services for allowing us to meet in their fantastic training rooms.

Thanks also to OneNeck IT Solutions for sponsoring our lunch.

OneNeck’s Biju Thomas will speak about three highly relevant topics:

  • Oracle’s Autonomous Database — “What’s the Admin Role?”
  • Oracle Open World #OOW 19 Recap
  • Let’s Talk AI, ML, and DL

I am looking forward to learning something new about these areas of technology. We work in a constantly evolving IT landscape so learning about the latest trends can only help us in our careers. Plus, it should be interesting and fun.

I hope to see you there.

Bobby

Categories: DBA Blogs

Analog Detour

Mon, 2019-08-26 23:04

Definition of analog: of, relating to, or being a mechanism or device in which information is represented by continuously variable physical quantities

Merriam Webster Dictionary Introduction My Analog Tools Before The Detour

I just finished going off on a major tangent away from my normal home computer pursuits such as studying algorithms, messing with Nethack source code, or practicing Python programming. Prior to this diversion I pursued these computer-related activities for learning and fun in my free time outside of work. But I spent the last three months pursuing activities related to pens, pencils, paper, and notebooks. Some people like to call using a pen and notebook an “analog” activity, so I used that word for the post title.

For several years my primary analog tools have been cheap wide ruled composition notebooks and Pilot G-2 gel pens. For example, I used a composition notebook to keep track of the homework and tests for my OpenCourseWare algorithms classes. I also used a composition notebook for non-technical purposes such as taking notes about things I read or heard, or just writing about things going on in my life. But otherwise most of what I wrote down was in computer form. I use Microsoft Outlook for my calendar at work and keep notes and tasks in Microsoft Word documents and text files, both in the office and on my home laptop. A lot of information was just kept in email. I have stuff on my iPhone.

But back in May I started looking at better ways to use pens, pencils and paper. I started looking on the internet for reviews and recommendations of what to buy and I ended up spending a lot of time and probably a few hundred dollars trying out different products and coming up with my own preferences. Finally, a couple or three weeks back I finally stepped back from going deeper and deeper into this exploration of pens, pencils, paper, and notebooks. I had spent enough money and time researching the best options. Now it was time for me to just use the tools I had and not buy any more and not read any more about them.

Now that I have stopped exploring these analog tools, I thought I should write a blog post about what I learned. I have a bunch of bookmarks of interesting web sites that I found helpful. I also have the results of my own use of the tools. Clearly, I am not a pen, pencil, paper, or notebook expert. This is an Oracle database blog and my strongest skills are in the database arena. Also, would people who read this blog for Oracle tuning scripts and information care about pens and paper? I am not sure. But the information that I learned and gathered has been helpful to me and it has been fun. Maybe others can benefit from my experience and if they want more expert advice, they can follow the links in this post to people who specialize in these areas.

I have decided to break this post, which is almost surely going to be my longest to date, into sections that alternate between things you write with and things you write on. Here is the outline:

  1. Introduction
  2. Pilot G-2 Gel Pens
  3. Graph Paper
  4. Pen Party
  5. Bullet Journal
  6. Fountain Pens
  7. Rhodia Dot Pads
  8. Pencils
  9. Conclusion

Lastly, I want to end the introduction with a caveat that is like those I find in a lot of the pen and paper blogs. I will have links to various businesses that sell products like pens or notebooks. I have not received any money to advertise these products, nor have I received any products for free. I bought all the products with my own money. As I mentioned in my privacy page, this blog is completely non-commercial and does not generate income in any way. I do not sell advertising or people’s emails to spammers or anything like that. My only income is from my job doing Oracle database work. I like having a blog and I cough up a couple hundred dollars a year for hosting, a domain name, and a certificate so I can put these posts out on the internet. So, don’t worry that I’m trying to sell you something because I am not.

Pilot G-2 Gel Pens Pilot G-2 Gel Pens of each size

I have been using Pilot G-2 gel pens for several years but did not realize that they came in different widths before I started researching all of these analog tools. I was using my gel pen with my composition notebook and kept accidentally smearing the ink. So, I finally Google searched for something like Pilot G-2 review and that really kicked off all of this research into types of pens and related tools. I found out that Pilot G-2 pens came in four width tips and that I had accidentally bought the widest tip pens which are the worst at smearing. They just put the most ink on the paper. I looked back at my Amazon orders and found that I bought a dozen “Fine Point” G-2 pens in 2015, but when I ran out and reordered 12 more in 2017, I got the “Bold Point” which has a thicker line. If you look at the pictures on Amazon, the boxes look very similar. You must know what the widths are named. So, as simple as this sounds, it was helpful to learn about the different tip sizes of my favorite gel pens and to try out each size to find which one I wanted. Here is a table of the four sizes:

Clip #MillimetersName# Colors0.38.38Ultra Fine405.5Extra Fine507.7Fine16101.0Bold8

The clips of the pens have numbers on them but only the .38 millimeter tip has a decimal point so that can be confusing. The .38 mm pen writes a very thin line. Evidently several manufacturers compete for the very thin line gel pen market. I have tried all four G-2 sizes and right now my favorite is the .5 mm extra fine.

I got the number of colors from Pilot’s site. It looks like the .7 mm fine has the most colors. I pretty much just use black, but I like red for editing.

A key thing that I learned about testing a new pen is that you must write a lot with it before you really know if you like it. Gel pens seem to start out a little uneven when you first write with them. Maybe they have been on a shelf somewhere for a long time. But after you write say 5 pages the ink starts to really flow well.

As I said in the introduction, I liked the Pilot G-2 gel pen before all this investigation began. But I know so much more about my favorite pen such as the different sizes. I will talk about this later, but one result of all this research is that I have started to like pens that write thinner lines. I accidentally bought a dozen of the thickest line G-2 pens in 2017 and it almost threw me off the pen. Now I have settled down with a G-2 pen with a tip half the size of the one I struggled with and it has really helped me out.

Graph Paper Graph paper with pseudo-code

I got the idea of switching to graph paper from the handwritten lecture notes from an algorithms class I was working through. Evidently the professor wrote out his notes using several color pens on graph paper. It made me wonder if I should use graph paper too.

I had already started using lined loose leaf filler paper in addition to composition notebooks. I think part of the problem is that it has been 30 years since I was a student in school, and I am out of practice using paper and 3 ring binders and all. My daughters use all these tools in school without thinking about it but for me the idea of using some loose filler paper is a revelation. Maybe some of this is not so much learning but relearning how to use these analog tools that I used regularly during my school days.

The lined filler paper was great as scratch paper when I was working on a problem for my online class but then I could clean up the answer and write it in my sturdy composition notebook to keep long term. But it made sense to get graph paper instead of lined paper because it would help to line things up along the vertical lines. If I need to write some code or pseudo-code and line up blocks within loops or if statements, I can use the vertical lines. The horizontal lines just separate the lines of code.

I ended up buying a nice pad of graph paper from Amazon but didn’t realize that some of the lines were darker than others. I prefer the lines to all be the same darkness. Then I ended up returning something that I bought from Office Depot with a gift card and they would not give me cash back. So, I used the gift card that was the refund to buy several pads of loose three-hole punched graph paper as well as two or three composition books with graph paper instead of lines. The composition books are kind of neat but they seem a little cheap and flimsy.

Of the three types of graph paper that I tried I like the Office Depot loose paper the best. All the lines are the same darkness and it is already three-hole punched so I can throw it in a binder if I want to keep it. I can use it for scratch paper, or I can save what I write down. So, at this point I like my loose paper to be graph paper but I still like the wide rule lined composition notebooks over the ones with graph paper.

Pen Party Pen Party Favorites

After reading the Pilot G-2 review I started reading reviews about other pens. Several web sites have best pen lists. The Gentleman Stationer has a nice best pens for 2019 page. The Pen Addict has lists of top 5 pens in different categories. Lastly, the online store JetPens, which specializes in Japanese pens, has their list of 33 best pens for 2019. One challenge that I had when I was looking at the different widths of G-2 pens is that I ended up buying packs of 4 or more pens on Amazon when I really just wanted one black pen to test. JetPens.com sells individual pens. If you spend $25 or more, you get free shipping. So, to test out some of the pens I had been reading about in the top pen lists I ordered several different kinds of pens, pencils, and an eraser, one of each kind.

Here was what I bought in my first order:

Uni-ball Signo UM-151 Gel Pen - 0.38 mm - Black         $2.85
Pentel EnerGel Euro Needle-PoinT - 0.35 mm - Black      $2.50
Platinum Preppy Fountain Pen - Black - 05 Medium Nib    $4.00
Tombow Mono 100 Pencil - HB                             $2.35
Zebra Sarasa Dry Gel Pen - 0.7 mm - Black               $2.95
Sakura Foam Eraser W 80                                 $1.50
Sakura Pigma Micron Pen - Size 02 - 0.3 mm - Black      $2.50
Tombow Mono Drawing Pen - 03 - Black                    $2.89
Tombow Fudenosuke Brush Pen - Hard - Black              $2.75
Uni Jetstream Sport Ballpoint Pen - 0.7 mm - Black Ink  $3.15

Some of these were suggested by Bullet Journal enthusiasts but I will talk about that in a later section. I will also talk about fountain pens and pencils later. The point here is that JetPens.com is cool. You must chunk up your purchase to be $25 or more but it is nice picking out individual pens to try them. One of the reasons I wanted to write this post is that I wanted to share about fun vendors like JetPens for people like me who had never heard of them before.

I took these things from my JetPens order and other pens and pencils that I already had and convinced my wife and three daughters to join me for a “pen party” to try them out. We sat around out kitchen table passing around groups of similar pens and pencils and tried each one out. After everyone finished trying them, we all picked our favorites.

My favorite was the .38 mm UM-151 or Signo DX gel pen. I have been using the Signo DX pen for tiny writing in a smaller notebook where it helps to have the smallest possible lines. I think it is slightly better than its G-2 .38 mm competitor. But I prefer the .5 mm G-2 gel pen for writing on full size 8 1/2 x 11 inch paper. So, my favorite gel pens are the .5 mm G-2 for normal writing and the .38 mm UM-151 for extra small writing.

My wife and two of my daughters preferred the Zebra Sarasa Dry .7 mm gel pen because it had the thick line of the .7 mm G-2 pen but with fast drying ink so that it did not smear. I’m not as big of a fan of the Sarasa Dry because the clip kind of sticks out and gets in my way. I may also just be a Pilot G-2 loyalist. Also, I have moved toward the thinner lines so the fast dry ink is not as important to me. We have a few of these Sarasa Dry pens in our pen cup in our kitchen.

My youngest daughter liked the Uni Jetstream Sport .7 mm ballpoint pen. This pen draws a finer line than you would think a .7 mm pen would because it is a ballpoint and not a gel pen. It also does not smear and is waterproof. We got her a bunch of these to take off to college as an incoming freshman this year.

We did not have this pen for our pen party but I wanted to mention the space pen that my family got me for Father’s Day. I carry it around in my pocket with my iPhone 8. I do not like the way the ink looks nearly as well as that of my gel pens, but the space pen is supposed to write at any angle and even on wet paper and it is mostly waterproof. Plus, it comes in a just under 4-inch-long bullet shape that fits comfortably in my pocket and cannot leak. The space pen gives me a way to write on just about anything when I am away from my home or office.

I am not a pen expert, but I thought I would pass along my own experiences as well as the links to the much more knowledgeable bloggers and vendors with their top pens lists. Someone out there might enjoy the top pens lists and trying out individual pens as much as we did. It was fun and even practical.

Bullet Journal Bullet Journal Supplies

At some point in this analog detour I started a Bullet Journal or BuJo for short. The main web site describes a BuJo as “The Analog Method for the Digital Age”. It sounds a little pretentious to call it “the” analog method instead of “an” analog method as if the Bullet Journal cures all ills. But I took the term analog from there to name this post. This post focuses on the analog tools and not the underlying philosophy or psychology behind the Bullet Journal. If you want to go deeper into that I recommend starting on the web site and then reading the book. I do not endorse everything in the book and web site or know if this method of journal writing really has all the claimed benefits. But I do endorse the tools that I have used such as the notebook which is very cool.

The Little Coffee Fox blog has a nice list of Bullet Journal supplies. I bought the black Leuchtturm1917 A5 Dotted Hardcover Notebook for my journal in May. A lot of the BuJo enthusiasts recommend this notebook. Even if you never get into bullet journals you might want to try one of these notebooks. The pages have dots instead of lines which is kind of like graph paper but less intrusive. The paper is nice quality and the notebook is hardbound and sturdy. The pages are numbered. I am writing this on August 25th, so I have been using my notebook for over 3 months. I like it a lot. Even if all the BuJo philosophy/psychology/method does not appeal to you the notebook itself is worth checking out.

I have tried several of the other BuJo supplies but I mainly use my Signo DX UM-151 .38 mm gel pen with my Leuchtturm A5 notebook along with a ruler. I got a foot long metal ruler with cork backing. I probably could have used any old straight edge just as well. I use it to draw lines and make boxes. I have not gotten into drawing and lettering as some BuJo enthusiasts do but I have purchased a couple of pens and a stencil to try. But I cannot really endorse something I do not use.

The Bullet Journal is all about using paper and pens instead of a computer which is really what this blog post is all about. What tools have I checked out to use offline? Can they work together with my computer tools to make me more productive and to help me have more fun?

Fountain Pens My First Two Fountain Pens – Preppies

I added a $4 fountain pen to my first JetPens order on a whim. I had to get the order up to $25 to get free shipping and $4 was reasonable for a fountain pen. If you look at the top pen lists above you will see that beginner fountain pens tend to run around $15 and yet my $4 Platinum Preppy was still considered a good pen. I got a second Preppy with my second JetPens order. The first was a medium nib and the second a fine nib. The medium has a .5 mm tip and the fine .3 mm. I had a lot of fun playing with these two pens. I ended up getting two matching converters so that I could use them with bottled ink and then I bought a nice bottle of black ink. Before I bought the full bottle of ink I got an ink sample and a pair of ink syringes so I could test out the ink.

While I experimented with my two Preppies I got a lot of helpful advice from the Reddit fountain pens group. Also vendors like JetPens and Goulet Pens have helpful videos and pages such as how to fill a fountain pen, how to clean a fountain pen, and how to use an ink sample. I think it makes good sense to start with a less expensive fountain pen and learn the ropes. The stereotypical experience of a new fountain pen user is that they do not learn how to take care of the pen, it stops working, and ends up in the back of a drawer unused. For example, I had trouble with the ink flow in my Preppies, so it helped to get advice on cleaning them and getting the ink flowing better.

After playing with my Preppies for a while I decided to get a nicer pen. If you read the top pen lists the break fountain pens into price ranges like “under $50”, “$50 to $100”, and “over $100”. I tried to be good and get a pen in the middle range, but I had my eye on several gold nib pens in the over $100 range. Japanese fountain pens mess up these neat price ranges because some pens that cost over $150 in the US can be purchased for less than $100 if you buy them directly from Japan. So, I told myself that I could get a $170 Platinum 3776 gold nib pen from Japan for under $100 and that is still in the middle range. This led to a lot of stress and frustration. I tried eBay first. A week after eBay charged over $80 to my credit card, I got an email from the seller saying that my pen was not in stock and did I want a blue one instead of the black one I ordered. I cancelled the order, but it took several days to get my money back. Then I ordered the same pen from a seller on Amazon and that was a total scam. Criminals broke into some poor unsuspecting inactive Amazon seller’s account and redirected the account to their bank account. Then they put out a bunch of bogus products at bargain prices including the fountain pen that I ordered. It took me a little over two weeks to get my money back.

After three or four weeks of frustration trying to buy an expensive fountain pen at a discount directly from Japan, I decided that it made more sense to buy from a reputable dealer in the US. I bought my 3776 at Pen Chalet and the buyer experience could not have been more different from my eBay and Amazon experiences. I got the black pen with gold trim and a fine nib. Lots of people on the fountain pens group on Reddit swear by buying fountain pens from Japan and they have more experience than I do. I suggest that wherever you buy your expensive fountain pen that you contact the seller first and ask them if they have the pen in stock. If they do not respond to your message, then run away very fast. Also, you probably should not try to get the best deal. Pay $20 more to buy the pen from a well-known dealer in Japan that sells a lot of fountain pens instead of going for the lowest price. Or just forget shopping for bargains from Japan and go with a well-regarded US vendor like Pen Chalet. I did contact Pen Chalet about something else before buying my pen and they responded quickly. A quick response to a question is a good sign for fountain pen sellers.

My Platinum 3776 pen writes like a dream. I have the matching gold colored converter and I use Aurora Black ink. It writes a nice thin black line, kind of like the ones my .5 mm G-2 and .38 mm Signo DX gel pens write. The big question is why spend $179 (including sales tax) for a fountain pen when a $3 gel pen makes pretty much the same line? I am not sure. Some people argue that fountain pens are better for the environment because you can fill them with bottled ink but with other pens you use them once and throw them away filling up landfills with plastic. Someone on the internet said, “gel pens are for work and school and fountain pens are spiritual”. I am not sure what they meant by spiritual but using a high-quality fountain pen is a nice experience. I keep mine at home, so I do not lose it somewhere like all my other pens. It is kind of nice to sit down at my kitchen table and write with my fountain pen. Maybe someone will read this post and find enjoyment in fountain pens themselves.

Rhodia Dot Pads Blog Post Outline on Rhodia Paper with Platinum 3776 Fountain Pen

Once I got into fountain pens, I needed some nice paper to write on. Many paper brands advertise as fountain pen friendly but I focused on Rhodia Dot Pads. These have dots like my Leuchtturm bullet journal notebook, but the pages are perforated so they can be removed. I started with the 6 x 8 1/4 inch pad because it was the best deal. I ended up writing on both sides of all 80 sheets and trying out different kinds of pens and pencils on it. We used these sheets in our family pen party. When I finished off this pad I bought the more expensive 8 1/4 by 11 3/4 inch pad and I really like it. I three-hole punch the pages after I rip them off and save them as fountain pen writing samples. I get a lot of enjoyment writing with my gold nib Platinum 3776 fountain pen on my full size Rhodia dot pad.

Before I started this analog detour, I wrote in a composition book with a gel pen. Today I write on my Rhodia pad with a fountain pen. One thing about the Rhodia paper is that it is smoother and less absorbent than cheaper paper. As a result, pens draw thinner lines on Rhodia paper. This probably would be more important with really wide fountain pen nibs, but it is nice that my fine nib pen leaves a nice sharp thin black line. The Rhodia paper is more expensive. At this instant you can get a full size Rhodia pad for $14.99. It has 80 sheets so that is about 19 cents per sheet. A 5 pack of Mead composition books will run you $16.75 for 500 sheets which is less than 4 cents per sheet. Is the Rhodia pad worth over 4 times as much? Why not stick with my Pilot G-2 .5 mm gel pen and write on Mead wide ruled composition books instead of using my Platinum 3776 fountain pen on a Rhodia Dot Pad? I think I could be happy with either. There is a small advantage to the more expensive pair. The fountain pen does not show through very much on the Rhodia paper. The gel pen shows through on the Mead composition book in my testing. At the end of the day, I just enjoy the Rhodia pad like I enjoy the nice fountain pen. It goes beyond simple practicality even though there are some practical benefits of the more expensive Rhodia pads.

Pencils My Favorite Pencil

The last type of analog tool that I checked out was pencils. I have not been using pencils at all in my work or in my computer science study at home. But I remember back in college writing my Pascal code in my first CS class all on paper. The TA that graded my programs said that I had the third least amount of CPU usage of anyone in the class and that the other two with less CPU usage than me dropped the class. I had been programming in BASIC and FORTRAN before coming to college so learning Pascal was not that hard. But I liked to write code out with pencil and edit using an eraser, so I did not spend a lot of time on the computer screen. These days I mainly do things on the screen. I think I need to get back to using pencils and erasers for writing and editing code and pseudo-code both in my online class and for work. I guess that goes along with the analog way of thinking like the bullet journal philosophy of using a notebook and pen instead of a program or app for your planner and journal.

My favorite pencil that I tried was the Tombow Mono 100 HB pencil that I bought in my first JetPens order. It is a pretty thing. It is basically a drawing pencil. It writes a nice dark line and is very smooth. When I was trying out various pencils, I found a fantastic pencil store called CW Pencil Enterprise. You can buy individual pencils of all kinds including types from countries all over the world. I only bought two or three pencils, but they were great to order from. They included a nice handwritten note written with a pencil. Vendors like CW Pencil motivated me to write this blog. JetPens, Goulet Pens, Pen Chalet, and CW Pencil were all very nice stores to buy from. I am sure that they are not perfect, but I had a great experience with them all and I wanted to share that with other people.

In addition to pencils I also looked at erasers. The Sakura Foam Eraser that I got from JetPens is a step up from the traditional pink hand held eraser. It erases all the pencil marks and leaves less stuff behind. A couple of my pencils like the Tombow Mono 100 did not have erasers on the end so I got a pack of Pentel Hi-Polymer Eraser Caps. These convert a drawing pencil into a more conventional writing pencil with eraser. When I use pencils for programming I alternate between the erasers on the end of the pencil and the stand-alone foam eraser.

As much fun as I had looking at pencils, I really did not find much difference between them when I used them for hand coding. The much less expensive Dixon Ticonderoga pencils that my children and wife swear by worked really well for me. I can barely tell the difference between them and the Tombow Mono 100. The Tombow is a little darker and smoother but it really does not matter much for what I need. So, I splurged on the expensive fountain pen but at this point I’m pretty much sold on the affordable and quite nice Dixon Ticonderoga standard in pencils.

Conclusion Rest of my analog stuff

I went back on forth on whether I should write this post and what to put in it. There is a lot more that I could talk about that goes beyond just the tools themselves and I thought about writing multiple posts. But, really, this is a DBA blog and not a pen, pencil, paper, and notebook blog so one post is probably enough. I thought about making this a lot shorter and just having links to the various web sites and products without explanation – bullet points with URLs behind them. I settled on a pretty long single post that weaved my personal experiences from the past 3 months or so in with links to the sites and products.

My exploration of these “analog” tools is like a breadth first search of a very large search tree of products and information. For example, there are many kinds of fountain pens and each pen has multiple nib sizes. How many pens would you have to buy to really try them all? If you look at Platinum’s 3776 site there are many different colors and designs of this one pen, plus multiple nib sizes for each. Then there are the other manufacturers each with multiple pens. It is a huge search tree. I got just a little way into this massive search and pulled the plug. This post documents the results of how far I got. I thought about waiting a year before writing a post about this to see if I am still using these tools and what benefits I got from them. But, by then I would have forgotten much of what I learned in my initial search. Maybe a year from now I can follow up with a post about whether this detour has had a lasting positive impact on my work and personal life.

Thanks to everyone who checks out this post. If you have questions or comments, it would be great if you left them below. I hope that something in here will be helpful to others. I had a lot of fun and learned a few useful things.

Bobby

Categories: DBA Blogs

Rolling out patched 19c home using gold image

Thu, 2019-08-08 18:49

For Oracle versions 11.2 through 18 I have been applying quarterly patches to a test database and then gathering the Oracle home into a tar file. When we want to roll out a new database server with the latest patches we untar the file and clone the Oracle home with a command like this:

$ORACLE_HOME/oui/bin/runInstaller -clone -silent ...

This command no longer works for 19c so now I am using a new command like this:

$ORACLE_HOME/runInstaller -silent -responseFile ...

Before running this command I had to unzip my gold image zip file into $ORACLE_HOME. I created the gold image zip file using a command like this:

./runInstaller -createGoldImage -destinationLocation ...

I ran this through MobaXterm to use their X server. I created the response file when I initially installed 19c on this test server. Then I patched the Oracle home with the July 2019 PSU and finally ran the above command to create the gold image.

Some useful links that I ran into:

Franck Pachot’s post about doing a silent 18c install using the new runInstaller

Oracle support document that says the old Oracle home cloning does not work in 19c:

19.x:Clone.pl script is deprecated and how to clone using gold-image (Doc ID 2565006.1)

Oracle documentation about installing silent with response file

DBA Stackexchange post about how you have to use a response file because the command line options don’t work

This is kind of terse but it has some of the key information. I may update it later if I can.

Bobby

Categories: DBA Blogs

Python Script To Backup Linux Directory To Windows

Thu, 2019-07-25 18:32

I found out that my blog backup script was failing so I had to rewrite it to handle dropped connections to my remote sftp server. In the process I broke out as much of the code as I could into a module that I could share. The module is backupremote.py in my miscpython repository. Might be helpful to someone else. It copies the directory tree on a remote Linux server down to a directory on a Windows machine (i.e. a laptop). Uses sftp.

The earlier version of this script was in this blog post: https://www.bobbydurrettdba.com/2018/05/30/python-script-to-backup-remote-directory-using-sftp/

Bobby

Categories: DBA Blogs

Batch Query Reduced from 12 hours to 45 Minutes

Thu, 2019-06-13 16:38

I was asked to look at a batch SQL query that ran for 12 hours on June 4th. I messed around with some other ideas and then went back to my best method for tuning long running batch queries. I ripped it apart. My code ran in 45 minutes.

This only works if you have the code and can change it. I work with a lot of PL/SQL code in Unix shell scripts running SQL*Plus. If someone comes to me with a SQL statement in this kind of code, I do not tear my hair out trying to tune it in some clever way. Also, this assumes that I cannot use a SQL Profile. SQL Profiles are my first choice for production performance problems. But for long running SQL that I have full control over and cannot use a SQL Profile I tear it apart.

Ripping or tearing a SQL statement apart means that I become the optimizer. In this case the problem query joined 5 tables. It summarized 3 months of data from a large fact table and the other 4 tables were joined together with the fact table. I replaced the one query with 5 queries each of which saved their results in a table. This first query summarized the fact table and the remaining four joined one more table to the current results. Something like this:

  • Summarize 3 months of fact table data – table 1
  • Join table 2 on surrogate key
  • Join table 3 on surrogate key
  • Join table 4 on natural keys
  • Join table 5 on natural keys

So, I created 5 tables each of which held the results of the previous joins. I dropped the tables as I was done with them to save space.

I have a feeling that I could use some clever hint to force the join order and access methods to match my 5 queries. But my time is short and this works, so I did not bother trying. When you have a query that runs for 12 hours it’s not very motivating to try various hints to get it to run faster. How long do you wait for each try before you give up? Working on one table at a time is nice. I have had this approach work for me time and time again. It is almost a mechanical process without a lot of painful thinking.

Anyway, I pass this on to the internet. People may think that breaking up a 5 table join into 5 queries is ugly, but it works.

Bobby

Categories: DBA Blogs

Another On Call Week, Another SQL Profile (or two)

Thu, 2019-06-13 16:14

I was on call again last week and Sunday night I used a SQL Profile to fix a performance problem. We rolled out some updates Saturday night and the modified query ran fine in some cases, but others ran for hours. When I got on there were 60 sessions running the bad plan and the load on the system was very high. It took me a while to identify the good plan, but then I found it, put in a SQL Profile, killed the 60 bad sessions, and the problem jobs ran in 2 minutes each. A similar situation came up Monday morning after I went off on call and a coworker took over and he ended up applying another SQL Profile on a similar query.

I spent the past couple of hours doing my typical SQL tuning exercise to see if I could figure out why Sunday’s query sometimes chose the bad plan.

The typical scenario includes these elements:

  1. Partitioned table with some near empty partitions and a lot of full partitions
  2. Bind variables used to determine partition choice

In our case we have certain tables that partition data by a code number. Certain numbers were used in the past or for other reasons are not active now. My guess is that the bad plans that we see come from bind variable values that point to the empty partitions. The bad plan works fine with empty partitions but then the optimizer uses it on full partitions and the query spins for hours.

I started to research this further to see if I could come up with a better fix than putting in SQL Profiles but did not get very far. I thought I would just pass this post along as is.

Bobby

P.S. I originally wrote this June 4th, but decided to just publish as is today.

Categories: DBA Blogs

Slides and My Impressions from May 17th AZORA Meetup

Tue, 2019-06-11 18:44

We have the slides from the two talks at our May 17th AZORA Meetup.

Here are Stephen Andert’s slides: Networking is NOT just cables and fiber!

Here are Doug Hood’s slides in three parts:

  1. Using SQL and PLSQL for Mid-Tier Database Caching
  2. Oracle TimesTen Scaleout – World’s Fastest OLTP DB
  3. Oracle In-Memory Data Processing

I wanted to share my impressions of the two talks and the meeting as well as share the slides. Of course, these are my opinions shaped by my own experiences and not universally true of everyone!

This meetup had two very different types of talks. I thought of Stephen’s talk as a career development or soft skills sort of talk. I have been to a number of talks like that at national Oracle user group conferences such as Collaborate. They help balance out the purely technical Oracle database content. Once Stephen got into his talk, I really started to appreciate the quality of the content. To me he was talking about keeping in touch with people in an intentional but sincere way. I like the idea of planning on contacting people a certain number of times per year for example.

Years ago, in my first job I worked for Campus Crusade for Christ (now Cru) and I raised money to support myself and my family. I networked with people that I met through churches and friends and family members. It was different than networking as part of a DBA career because I was directly asking for money instead of making career-oriented connections. But the core idea that I remember from Stephen’s talk applied then. Stephen’s idea was to genuinely seek to help the folks in your network without focusing on what they could do for you. In my CCC days the support raising training told us that we were not “fundraising” but instead “friend raising”. I had some great experiences meeting people and getting to know them and I think it was best when my focus was on how to inspire and encourage the people I met rather than to anxiously think about whether they could give money to support what I did.

The other less serious connection I saw between Stephen’s presentation and my Cru days is that Stephen has a hand-written database setup to keep track of his people. Back in the day I had a Paradox database from Borland running on MS-DOS to do a lot of the same things. So, hearing Stephen talk about his contact database was a blast from the past for me.

I am not really doing much in the way of networking myself these days. I write this blog. I speak at conferences every couple of years or so. I help with the local Oracle user group AZORA. But I am not intentionally making and maintaining relationships with other technical people in the way Stephen described so his talk gave me something to think about.

Doug Hood’s talk was at the other end of the spectrum with some cool technology. Doug spoke on several things and with a lot of detail so I cannot do a good job of summarizing what he said. Check the slides for more details. But I do want to write down my impressions. Listening to Doug’s talk reminded me of some of the computer science study that I have been doing on my own recently. I have sort of gone back to school as an independent learner. When Doug talked about the memory hierarchy and caching it tied right back to the assembly language and algorithms study I have been doing.

Doug presented some cool hardware that puts persistent memory close enough to the CPU that it changes the way we think about memory hierarchy. What if you replace your RAM with persistent RAM that did not get cleared when you power off your computer? Evidently in some architectures (maybe all the modern ones these days I don’t know) the RAM is closely connected to the CPU and does not have to be accessed over the bus in the way I/O is. So, persistent RAM would be much faster than some solid-state disk being read over the bus no matter how fast the SSD is. Anyway, see Doug’s slides. I am sure that I am butchering the details, but I am giving my impression and my memory so keep that in mind.

In general database work and database performance has a lot to do with caching. I have heard a little bit about how algorithms can be designed to work well with CPU caches. I recently read a chapter about the B-Tree data structure that is used in databases and it was a big contrast to the other data structures I had studied because it took disk reads and memory accesses into consideration. Anyway, at a high level I took away from Doug’s talk notions about memory and caching and different ways people can tweak the memory hierarchy to get better database performance.

I had to leave a little early to head for the mountains for the weekend but as always, I valued the time I spent at AZORA, my local Oracle user group. I appreciate Stephen and Doug stepping up and giving their presentations. I hope that my links and the way I characterized their talks is accurate enough. I am sure that I made mistakes, but I got something out of my time and appreciate their efforts.

AZORA is taking a break for the hot Arizona summer but coming back strong with our next meeting on September 27th. The details are being finalized so keep an eye on our Meetup page.

Bobby

Categories: DBA Blogs

MySQL Source Installs for each RDS version

Wed, 2019-05-22 18:26

I have been doing a lot of Oracle and PeopleSoft work this year, but I am trying to continue to develop my MySQL and Amazon Web Services (AWS) knowledge at the same time. My goal is to learn some new thing about MySQL and AWS each month and then document it either on this blog or on my company’s internal web site.

This month I decided to focus on building a Linux virtual machine on VirtualBox that has the source code for each version of MySQL that we support on AWS through RDS. I already had an Oracle Linux VM with MySQL 5.7.20 installed from source code from the MySQL GitHub site. So, all I had to do was get the source code to the correct release in git and then recompile it and create a test database. Then I could save a VirtualBox snapshot for that release.

I don’t want to spend time here describing how I did the initial MySQL 5.7.20 install except to say that I followed the steps in the 5.7 reference manual section titled “2.9 Installing MySQL from Source“. The GitHub specific instructions were in the section titled “2.9.3 Installing MySQL Using a Development Source Tree“. I can’t remember why it was a problem, but I could not get Boost installed correctly for CMake to pick it up, so I pass the path to Boost to CMake using the following command:

cmake . -DWITH_BOOST=/home/bobby/boost_1_59_0

instead of what is in the manual. Otherwise I just followed the manual.

I looked at our AWS MySQL RDS databases and found 4 versions: 5.5.46, 5.6.34, 5.7.17, and 5.7.25. So, I wanted to install each of these from source. My idea is that if we hit a bug or unexpected behavior, I can try different versions and see if that behavior is version specific. We could also dive into the source if needed and have the correct version.

Here are the steps that I put together that worked for our 5.7 databases:

cd /home/bobby/mysql-server

make clean

rm CMakeCache.txt

git checkout 5.7

git pull

git checkout mysql-5.7.17

cmake . -DWITH_BOOST=/home/bobby/boost_1_59_0

make

su - root

cd /home/bobby/mysql-server

make install

cd /usr/local/mysql/data

rm -rf *

cd ..

bin/mysqld --initialize --user=mysql

bin/mysql_ssl_rsa_setup

mysqld_safe --user=mysql &

mysql -p

use default password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'My!Password1234';

mysql -p

use My!Password1234

My git repository was /home/bobby/mysql-server and my Linux username was bobby. The database is in /usr/local/mysql/data. The 5.6 and 5.5 databases had a different way to create the database and change the password:

Replace these lines:

bin/mysqld --initialize --user=mysql

bin/mysql_ssl_rsa_setup

mysqld_safe --user=mysql &

mysql -p

use default password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'My!Password1234';

with

scripts/mysql_install_db --user=mysql

./bin/mysqld_safe &

./bin/mysqladmin -u root password 'My!Password1234'

./bin/mysqladmin -u root -h mysqlsrc password 'My!Password1234'

Here are some pictures from the 5.7.17 final working install:

Clean up from previous install Switch to 5.7 branch Go to 5.7.17 release End of cmake step to setup options for make End of make End of make install Database initialized after old database deleted Setup keys Up on correct version 5.7.17 All four versions on their own snapshots

Time will tell if this setup really helps us during some sort of problem, but I like having the source code in case we hit a bug or unexpected behavior. The great thing about open source is that we can see the code, so why not use it?

Bobby

Categories: DBA Blogs

Tried Live SQL

Thu, 2019-05-09 18:13

Quick post. I tried https://livesql.oracle.com/ for the first time.

Looks like you can try out the latest version of Oracle for free. You can type in SQL statements in your web browser.

There seem to be a boatload of scripts and tutorials to try out. Might be good for someone who is new to SQL.

Bobby

Categories: DBA Blogs

Installed 19.3 on Linux on VirtualBox

Thu, 2019-05-02 15:14

I noticed that Oracle database 19.3 was available to download so I installed it on a Linux VM under VirtualBox.

I cloned my base Oracle Linux 7 VM and did a yum -y update to get it up to the latest Linux version.

I installed the preinstall RPM:

yum install oracle-database-preinstall-19c

Created the Oracle home, set some variables, and disabled the firewall.

mkdir -p /home/oracle/product/db/19.0.0
chgrp oinstall /home/oracle/product/db/19.0.0
cd /home/oracle/product/db/19.0.0

export ORACLE_BASE=/home/oracle/product
export ORACLE_HOME=/home/oracle/product/db/19.0.0

[root@ora19 ~]# systemctl stop firewalld
[root@ora19 ~]# systemctl disable firewalld

Edited my .bashrc

export ORACLE_BASE=/home/oracle/product
export ORACLE_HOME=/home/oracle/product/db/19.0.0
export ORAENV_ASK=NO
export ORACLE_SID=orcl
. oraenv

Unzipped the downloaded file LINUX.X64_193000_db_home.zip in my ORACLE_HOME. Then I ran runInstaller from the ORACLE_HOME through MobaXterm. For some reason this decided to put the install window overlapping my two screens so I couldn’t tell what I was typing, and it didn’t allow me to move it. Fun times.

It seemed to hang on the last step, but it eventually finished. The process named ora_mz00_orcl was spinning on the CPU for part of the time that it seemed hung. I guess it was 5 or 10 minutes, but I didn’t time it.

Seems to be up and working:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 2 13:00:59 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Thu May 02 2019 12:51:54 -07:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>

Bobby

Categories: DBA Blogs

May 17th AZORA Meetup – Last until September!

Tue, 2019-04-30 14:28
#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; }
AZORA Meetup before summer heats up! Two presentations – Friday, May 17th

Friday, May 17, 2019, 12:00 PM

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

8 AZORAS Attending

AZORA Meetup before the summer heats up! We will be featuring two presentations on Friday May 17th meeting. Logistics are the same at friendly Republic Services, an afternoon with other Oracle types between Noon till 4pm. We will follow typical agenda of Lunch – Learn – Learn some more – Let us call it a weekend! Here are the details: Meeting Agend…

Check out this Meetup →

This is our last Meetup until our Summer break. Come check out two great presentations.

Doug Hood from Oracle will talk about the Oracle In-Memory database feature. We appreciate Oracle providing us with this technical content to support AZORA.

AZORA’s own Stephen Andert will be sharing a non-technical presentation on Networking. He just gave the same talk at the national Oracle user group meeting called Collaborate 19 so it will be great to have him share with his local user group.

Looking forward to seeing you there.

Bobby

P.S. AZORA is the Arizona Oracle User Group, and we meet in the Phoenix, Arizona area.

Categories: DBA Blogs

Example of coe_xfr_sql_profile force_match TRUE

Wed, 2019-04-17 10:57

Monday, I used the coe_xfr_sql_profile.sql script from Oracle Support’s SQLT scripts to resolve a performance issue. I had to set the parameter force_match to TRUE so that the SQL Profile I created would apply to all SQL statements with the same FORCE_MATCHING_SIGNATURE value.

I just finished going off the on-call rotation at 8 am Monday and around 4 pm on Monday a coworker came up to me with a performance problem. A PeopleSoft Financials job was running longer than it normally did. Since it had run for several hours, I got an AWR report of the last hour and looked at the SQL ordered by Elapsed Time section and found a number of similar INSERT statements with different SQL_IDs.

The inserts were the same except for certain constant values. So, I used my fmsstat2.sql script with ss.sql_id = ’60dp9r760ja88′ to get the FORCE_MATCHING_SIGNATURE value for these inserts. Here is the output:

FORCE_MATCHING_SIGNATURE 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
------------------------ ------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
     5442820596869317879 60dp9r760ja88         3334601 15-APR-19 05.00.34.061 PM                1         224414.511     224412.713         2.982                  0                      0                   .376             5785269                 40                   3707

Now that I had the FORCE_MATCHING_SIGNATURE value 5442820596869317879 I reran fmsstat2.sql with ss.FORCE_MATCHING_SIGNATURE = 5442820596869317879 instead of ss.sql_id = ’60dp9r760ja88′ and got all of the insert statements and their PLAN_HASH_VALUE values. I needed these to use coe_xfr_sql_profile.sql to generate a script to create a SQL Profile to force a better plan onto the insert statements. Here is the beginning of the output of the fmsstat2.sql script:

FORCE_MATCHING_SIGNATURE 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
------------------------ ------------- --------------- ------------------------- ---------------- ------------------ -------------- ------------- ------------------ ---------------------- ---------------------- ------------------- ------------------ ----------------------
     5442820596869317879 0yzz90wgcybuk      1314604389 14-APR-19 01.00.44.945 PM                1            558.798        558.258             0                  0                      0                      0               23571                  0                    812
     5442820596869317879 5a86b68g7714k      1314604389 14-APR-19 01.00.44.945 PM                1            571.158        571.158             0                  0                      0                      0               23245                  0                    681
     5442820596869317879 9u1a335s936z9      1314604389 14-APR-19 01.00.44.945 PM                1            536.886        536.886             0                  0                      0                      0               21851                  0                      2
     5442820596869317879 a922w6t6nt6ry      1314604389 14-APR-19 01.00.44.945 PM                1            607.943        607.943             0                  0                      0                      0               25948                  0                   1914
     5442820596869317879 d5cca46bzhdk3      1314604389 14-APR-19 01.00.44.945 PM                1            606.268         598.11             0                  0                      0                      0               25848                  0                   1763
     5442820596869317879 gwv75p0fyf9ys      1314604389 14-APR-19 01.00.44.945 PM                1            598.806        598.393             0                  0                      0                      0               24981                  0                   1525
     5442820596869317879 0u2rzwd08859s         3334601 15-APR-19 09.00.53.913 AM                1          18534.037      18531.635             0                  0                      0                      0              713757                  0                     59
     5442820596869317879 1spgv2h2sb8n5         3334601 15-APR-19 09.00.53.913 AM                1          30627.533      30627.533          .546                  0                      0                      0             1022484                 27                    487
     5442820596869317879 252dsf173mvc4         3334601 15-APR-19 09.00.53.913 AM                1          47872.361      47869.859          .085                  0                      0                      0             1457614                  2                    476
     5442820596869317879 25bw3269yx938         3334601 15-APR-19 09.00.53.913 AM                1         107915.183     107912.459         1.114                  0                      0                      0             2996363                 26                   2442
     5442820596869317879 2ktg1dvz8rndw         3334601 15-APR-19 09.00.53.913 AM                1          62178.512      62178.512          .077                  0                      0                      0             1789536                  3                   1111
     5442820596869317879 4500kk2dtkadn         3334601 15-APR-19 09.00.53.913 AM                1         106586.665     106586.665         7.624                  0                      0                      0             2894719                 20                   1660
     5442820596869317879 4jmj30ym5rrum         3334601 15-APR-19 09.00.53.913 AM                1          17638.067      17638.067             0                  0                      0                      0              699273                  0                    102
     5442820596869317879 657tp4jd07qn2         3334601 15-APR-19 09.00.53.913 AM                1          118948.54      118890.57             0                  0                      0                      0             3257090                  0                   2515
     5442820596869317879 6gpwwnbmch1nq         3334601 15-APR-19 09.00.53.913 AM                0          48685.816      48685.816          .487                  0                      0                  1.111             1433923                 12                      0
     5442820596869317879 6k1q5byga902a         3334601 15-APR-19 09.00.53.913 AM                1            2144.59        2144.59             0                  0                      0                      0              307369                  0                      2

The first few lines show the good plan that these inserts ran on earlier runs. The good plan has PLAN_HASH_VALUE 1314604389 and runs in about 600 milliseconds. The bad plan has PLAN_HASH_VALUE 3334601 and runs in 100 or so seconds. I took a look at the plans before doing the SQL Profile but did not really dig into why the plans changed. It was 4:30 pm or so and I was trying to get out the door since I was not on call and wanted to get home at a normal time and leave the problems to the on-call DBA. Here is the good plan:

Plan hash value: 1314604389

------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                |                    |       |       |  3090 (100)|          |
|   1 |  HASH JOIN RIGHT SEMI           |                    |  2311 |  3511K|  3090   (1)| 00:00:13 |
|   2 |   VIEW                          | VW_SQ_1            |   967 | 44482 |  1652   (1)| 00:00:07 |
|   3 |    HASH JOIN                    |                    |   967 | 52218 |  1652   (1)| 00:00:07 |
|   4 |     TABLE ACCESS FULL           | PS_PST_VCHR_TAO4   |    90 |  1980 |    92   (3)| 00:00:01 |
|   5 |     NESTED LOOPS                |                    | 77352 |  2417K|  1557   (1)| 00:00:07 |
|   6 |      INDEX UNIQUE SCAN          | PS_BUS_UNIT_TBL_GL |     1 |     5 |     0   (0)|          |
|   7 |      TABLE ACCESS BY INDEX ROWID| PS_DIST_LINE_TMP4  | 77352 |  2039K|  1557   (1)| 00:00:07 |
|   8 |       INDEX RANGE SCAN          | PS_DIST_LINE_TMP4  | 77352 |       |   756   (1)| 00:00:04 |
|   9 |   TABLE ACCESS BY INDEX ROWID   | PS_VCHR_TEMP_LN4   | 99664 |   143M|  1434   (1)| 00:00:06 |
|  10 |    INDEX RANGE SCAN             | PSAVCHR_TEMP_LN4   | 99664 |       |   630   (1)| 00:00:03 |
------------------------------------------------------------------------------------------------------

Here is the bad plan:

Plan hash value: 3334601

---------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |                    |       |       |  1819 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID       | PS_VCHR_TEMP_LN4   |  2926 |  4314K|  1814   (1)| 00:00:08 |
|   2 |   NESTED LOOPS                     |                    |  2926 |  4446K|  1819   (1)| 00:00:08 |
|   3 |    VIEW                            | VW_SQ_1            |     1 |    46 |     4   (0)| 00:00:01 |
|   4 |     SORT UNIQUE                    |                    |     1 |    51 |            |          |
|   5 |      TABLE ACCESS BY INDEX ROWID   | PS_PST_VCHR_TAO4   |     1 |    23 |     1   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                 |                    |     1 |    51 |     4   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                |                    |     1 |    28 |     3   (0)| 00:00:01 |
|   8 |         INDEX UNIQUE SCAN          | PS_BUS_UNIT_TBL_GL |     1 |     5 |     0   (0)|          |
|   9 |         TABLE ACCESS BY INDEX ROWID| PS_DIST_LINE_TMP4  |     1 |    23 |     3   (0)| 00:00:01 |
|  10 |          INDEX RANGE SCAN          | PS_DIST_LINE_TMP4  |     1 |       |     2   (0)| 00:00:01 |
|  11 |        INDEX RANGE SCAN            | PS_PST_VCHR_TAO4   |     1 |       |     1   (0)| 00:00:01 |
|  12 |    INDEX RANGE SCAN                | PSAVCHR_TEMP_LN4   |   126K|       |  1010   (1)| 00:00:05 |
---------------------------------------------------------------------------------------------------------

Notice that in the bad plan the Rows column has 1 in it on many of the lines, but in the good plan it has larger numbers. Something about the statistics and the values in the where clause caused the optimizer to build the bad plan as if no rows would be accessed from these tables even though many rows would be accessed. So, it made a plan based on wrong information. But I had no time to dig further. I did ask my coworker if anything had changed about this job and nothing had.

So, I created a SQL Profile script by going to the utl subdirectory under sqlt where it was installed on the database server. I generated the script by running coe_xfr_sql_profile gwv75p0fyf9ys 1314604389. I edited the created script by the name coe_xfr_sql_profile_gwv75p0fyf9ys_1314604389.sql and changed the setting force_match=>FALSE to force_match=>TRUE and ran the script. The long running job finished shortly thereafter, and no new incidents have occurred in future runs.

The only thing that confuses me is that when I run fmsstat2.sql now with ss.FORCE_MATCHING_SIGNATURE = 5442820596869317879 I do not see any runs with the good plan. Maybe future runs of the job have a different FORCE_MATCHING_SIGNATURE and the SQL Profile only helped the one job. If that is true, the future runs may have had the correct statistics and run the good plan on their own.

I wanted to post this to give an example of using force_match=>TRUE with coe_xfr_sql_profile. I had an earlier post about this subject, but I thought another example could not hurt. I also wanted to show how I use fmsstat2.sql to find multiple SQL statements by their FORCE_MATCHING_SIGNATURE value. I realize that SQL Profiles are a kind of band aid rather than a solution to the real problem. But I got out of the door by 5 pm on Monday and did not get woken up in the middle of the night so sometimes a quick fix is what you need.

Bobby

Categories: DBA Blogs

Check your hints carefully

Tue, 2019-04-16 16:32

Back in 2017 I wrote about how I had to disable the result cache after upgrading a database to 11.2.0.4. This week I found one of our top queries and it looked like removing the result cache hints made it run 10 times faster. But this did not make sense because I disabled the result cache. Then I examined the hints closer. They looked like this:

/*+ RESULT CACHE */

There should be an underscore between the two words. I look up hints in the manuals and found that CACHE is a real hint. So, I tried the query with these three additional combinations:

 
/*+ RESULT */
 
/*+ CACHE */
 
/*+ RESULT_CACHE */

It ran slow with the original hint and with just the CACHE hint but none of the others. So, the moral of the story is to check your hints carefully because they may not be what you think they are.

Bobby

Categories: DBA Blogs

Huge Pages Speeds Startup of Web Servers

Tue, 2019-04-02 10:50

We had issues deploying changes to a large web farm a few weeks back. We had automated the startup of the 25 web servers using a DevOps tool but when it tried to bring them all up at once it took forever and the web servers got timeout errors. We resolved this by putting in huge pages on the metadata database.

I mentioned the unexpectedly large impact of huge pages on login time in a previous post. But, we had not realized that a small metadata database with a 5 gigabyte SGA would need huge pages. I guess I should mention that this is 11.2.0.3 Oracle on Linux. The web farm has 25 servers with 25 database connections each. I think the number of connections from each server should be 5 or less but I have not convinced others to reduce the number. If you have a metadata database with 4 cores and 625 connections from 25 web servers, the web servers cannot use all of those connections. A 4-core database server can only process 10 or 20 SQL statements at once if they are I/O centric. If they are all CPU you are looking at 4 concurrent active SQL statements. If you kick off 625 SQL statements at once the load will go through the roof and everything will timeout. I thought about putting in shared servers on the metadata database to force the 625 sessions to funnel through 12 shared servers so the database server would not be overwhelmed should all 625 connections choose to run at once. But the only problem we had was with logins. All 625 were logging in at the same time when the web farm was started by our DevOps tool. Huge pages resolved this issue by making the login time 10-20 times faster.

The database was in a 16 gigabyte Linux VM with 4 cores and a 5 gigabyte SGA. Without huge pages each login took about 2.5 seconds. With it the logins took about .15 seconds. Without huge pages the load on the database server went over 100 when all 625 sessions started at once. With huge pages the load never exceeded 1. Without huge pages the web farm never came up cleanly and with it the farm came up quickly without error. So, if you have a Linux VM with several gigabytes in your SGA you might want to use huge pages.

Bobby

Categories: DBA Blogs

High Processes Cause Semaphore Errors

Thu, 2019-03-28 10:38

I could not create a new Delphix virtual database because I kept getting semaphore limit errors. I ended up reducing the processes parameter value to resolve the issue.

I got these errors creating the new VDB:

ERROR : SQL> ORA-27154: post/wait create failed 
ERROR : ORA-27300: OS system dependent operation:semget failed with status: 28
ERROR : ORA-27301: OS failure message: No space left on device
ERROR : ORA-27302: failure occurred at: sskgpcreates

The processes variable was set to 3000 on production so Delphix tried to create the VDB with the same value. I reduced processes to 100 and the VDB came up cleanly.

I have been living with this problem for a year or two but yesterday I really needed to resolve it and I finally figured it out. I got the idea of reducing processes from this Oracle support document:

Instance Startup Fails With Error ORA-27154,ORA-27300,ORA-27301,ORA-27302 (Doc ID 314179.1)

I looked these errors up on Oracle’s support site several times in the past and never saw the idea of reducing processes so I thought I would mention it here if only to remind myself.

Bobby

Categories: DBA Blogs

Python Practice Produced Pretty Pictures

Sat, 2019-03-23 18:57

I wrote a Python program that made some fun pictures so I thought I would share them even though this is not really a database post.

I practice Python programming by doing Rosetta Code programming tasks that no one has implemented in Python. This is a fun way of keeping up my Python skills. My most recent contribution made pretty pictures so I thought I would show them here. The code takes a cube and breaks up the faces into smaller and smaller pieces that change the cube into a rounder shape.

Here is the input:

Input to the program, a cube

Here is the output after one subdivision:

After one subdivision. Chunky.

Here is the output after two subdivisions:

Two subdivisions. Pretty round.

Note that it is getting rounder. Lastly, after four subdivisions it is remarkably round considering that it started as a cube:

Four subdivisions. Quite round.

The main point of this post was to show the pretty pictures. But, to be more serious, if someone is reading this blog and looking for a programming task to do for practice you can do what I do and find a Rosetta Code task for the language you are learning and you can get some good practice.

Bobby

Categories: DBA Blogs

Speed of Light

Wed, 2019-03-20 16:30

Looking at cloud databases has me thinking about the speed of light. Wikipedia says that the speed of light is about 186,000 miles per second. If my calculations are correct that is 5.37 microseconds per mile. The United States is about 2680 miles wide so it would take light about 14.4 milliseconds to cross the US. If I ping one of my favorite web sites it takes tens of milliseconds to ping so that kind of makes sense because those sites are in other cities and I am going through various routers. I did some tests with my company’s storage and found that reading from our storage when the data is cached in the storage server takes around 200 microseconds. That is 200 microseconds for a round trip. I’m sure that our database servers and storage are a lot less than a mile apart so most of that time has nothing to do with the speed of light. I heard about a cloud vendor whose fast network connection took 100 microseconds plus the speed of light. I guess 100 microseconds is the cost of getting your data to fiber and light does the rest. If your cloud database was on the other side of the country, I guess it could take 14 milliseconds each way at least for each SQL request. If the cloud database was in your own city and say 10 miles away that would only tack on about 53.7 microseconds each way to the 100 microseconds overhead. I guess it makes sense. Maybe 100 microseconds plus the speed of light is the cost of moving data in the best case?

Bobby

Categories: DBA Blogs

Pages