Pushing predicates into MySQL subqueries

I am just getting started looking at MySQL performance tuning but I thought I would share something that I may have figured out. I’m working with a query that does a count and a group by in a subquery and it runs too long. It seems that the query reads every row from a table instead of passing a predicate into the subselect and accessing only a couple of rows. MySQL 5.7 seems to behave (more...)

It’s All About the Data

Data quality and accessibility are the key items to the integrity of an application

Follow up from RMOUG Training Days talks

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

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

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

Click on the club to see details of (more...)

Early morning RMOUG post

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

I appreciate all the effort people put in making their presentations. Since I have done Toastmasters I’ve learned to appreciate more what goes into being an effective speaker. But, the nature of my work is that (more...)

Can’t locate ADX/util/Sysutil.pm in @INC

When  I am trying to change hostname for EBS environment,  I used below command to remove server, I am getting below issues

[oraprod@ebsprodcl appsutil]$ ls
bin html install java LEBPROD_ebsprodleb.xml log media out perl scripts sql temp template
[oraprod@ebsprodcl appsutil]$ perl ./bin/adgentns.pl appspass=hqa523AceGr0uP contextfile=/u01/app/oracle/db/tech_st/12c/appsutil/LEBPROD_ebsprodleb.xml -removeserver
Can’t locate ADX/util/Sysutil.pm in @INC (@INC contains: /u01/app/oracle/db/tech_st/12c/perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi /u01/app/oracle/db/tech_st/12c/perl/lib/site_perl/5.14.1 /u01/app/oracle/db/tech_st/12c/perl/lib/5.14.1/x86_64-linux-thread-multi /u01/app/oracle/db/tech_st/12c/perl/lib/5.14.1 .) at ./bin/adgentns.pl line 82.
BEGIN (more...)

Streaming flat File data into Database

For my little Webserver Logfile Analysis project I had to solve the problem of how to stream data from a flat file into a relational datamodel in my target database.

Since I used NodeJS in several projects over the past years, it came to my mind at first. On a second thought and by consulting some friends another, easier, solution materialized:

A simple shell command.

Our Webservers run some Linux, so a tail -f is (more...)

Authenticate APEX via Token

This requirement pops up every now and then, time to put it in a blogpost so I can find it myself again when needed :-)

In various integration scenarios it is required to have users swiftly move between applications written in various technologies. This is very common for legacy Forms applications where some functionality is moved to APEX. From the Forms menu one entry could open a Form, another one an APEX page.

But this (more...)

Is NFS on ZFS slowing you down?

If you think so, check out shell script “ioh.sh” from github at  https://github.com/khailey/ioh

Introduction and Goals

The goal of ioh.sh is to measure both the throughput and latency of the different code layers when using NFS mounts on a ZFS appliance. The ZFS appliance code layers inspected with the script are I/O from the disks, ZFS layer and the NFS layer. For each of these layers the script measures the throughput, latency and (more...)

pdb_to_apppdb.sql returns ORA-65021- A workaround

RSS content

While exploring Oracle Multitenant Application Containers, I learnt that in order to convert a regular PDB  to an application PDB

  • Clone a regular PDB  into an application root
  • Connect to the cloned  PDB and execute the $ORACLE_HOME/rdbms/admin/pdb_to_apppdb.sql to convert the cloned regular PDB  to an application PDB

However, when I connected to cloned PDB remotely using @… and executed the script pdb_to_apppdb.sql, I got ORA-65021 :

SQL>@$ORACLE_HOME/rdbms/admin/pdb_to_apppdb

 

 

.

.

.

.

 

SQL>create or replace view sys.cdb$common_root_objects&pdbid sharing=object as

2  select u.name  (more...)

Help text in APEX in-line with page

Setting up help text in APEX is not hard but I often see it not done at all or implemented using regions with static content and then toggled on or off. That is unfortunate when there is declarative support for providing help texts.

In the last post I show how to set up a specific page to be the landing page for showing help text for any page in the application. In this post I’ll (more...)

Help text in APEX on a dedicated page

Setting up help text in APEX is not hard but I often see it not done at all or implemented using regions with static content and then toggled on or off. That is unfortunate when there is declarative support for providing help texts.

In this post I’ll show how to set up a specific page to be the landing page for showing help text for any page in the application. It is the precursor to (more...)

Certified at last!

Today saw me trundling off to dear old Birmingham for the second time in 2 weeks (the first time being for the UKOUG Tech17 conference) and doing a fair impression of a nervous wreck.

That’s because I was heading to take both the SQL (1z0-061) and PL/SQL (1z0-144) Oracle certification exams.

I don’t like doing exams, mainly because I’m rubbish at revising (seriously; the most revision I did throughout all my university career consisted of (more...)

SCOTT/TIGER for SAP Hana

SAP Hana is becoming increasingly popular these days. It comes with its own demo schema, STUDENT. However, being an old Oracle hack, I ported the SCOTT schema from Oracle to SAP Hana. Every Oracle DBA has a bunch of queries against EMP and DEPT tables which he or she may test on the SAP Hana database. In case you’re interested, the export file is available here:

http://mgogala.byethost5.com/scott_hana.tgz

 


Finding the trace files

In Oracle versions before 12c, it was necessary to resort to complex magic to find out the location of the process trace file. One of the most famous recipes is probably the one invented by René Nyffenegger and available here:

http://www.adp-gmbh.ch/ora/misc/find_trace_file.html

Another trick included utilizing ORADEBUG command “tracefile_name” which would return trace file name.

However, in Oracle 12c the things have changed. Trace file is now available as a column in the V$PROCESS (more...)

Little things worth knowing: scheduler jobs and Instance Caging

While updating material for a training class about Database Resource Management I briefly wondered if Instance Caging should apply for scheduler jobs as well. The obvious answer is “yes”, but I wanted to find proof. I hope I did in the end, and wanted to share how I got there.

The test environment

My system is based on the shiny new AMD Ryzen 7 1700X Eight-Core Processor, and it shows as 1s8c16t. I really like (more...)

IP CIDR rules and address ranges

I always forget IP address range coverage rules and forget where to look.

It’s the wiki!

and for good reference here is the table:

https://en.wikipedia.org/wiki/Classless_Inter-Domain_Routing

 

Screen Shot 2017-11-03 at 11.28.31 AM

ORA-15040 ORA-15042 with EXTERNAL redundancy Diskgroup

A colleague was working on an ASM issue (Standalone one, Version 11.2.0.3 on AIX) at one of the customer sites. Later on, I also joined him. The issue was that the customer added few news disks to an existing diskgroup. Everything went well and the rebalance kicked in. After some time, something happened and all of a sudden the diskgroup was dismounted. While trying the mount the diskgroup again, it was giving

ORA-15032:  (more...)

Best method for tuning sub-optimal execution plans

How do you determine if the Oracle SQL optimizer has created a sub-optimal execution plan? re-run statistics and see what happens? wait for Oracle to fin other execution plans? What if neither method is helping? Do you read the execution plan? What do you look at? Differences in actual vs estimated? How successful is that? Look for full table scans?  Do you look at the 10053 trace? How much time and effort does that take?  (more...)

Database is the marquee feature again

So every year before and during Oracle Open World we all complain about how data and database is brushed to the side.

This year it is the feature. It is the one thing the big sign on Moscone West screams. “The Autonomous Database”

Here is a picture from showing it. It is all about database.

Every year there is talk about how Oracle needs to return to data and databases. Now that they do, I (more...)

Oaktable World 2017 @ Oracle Open World

 

http://www.oaktable.net/blog/oak-table-world-2017-oracle-open-world

The Oak Table members will be discussing their latest technical obsessions and research on Monday and Tuesday, (Oct. 2nd and 3rd, 2017).  The truth is, folks-  The Oak Table experts are an AWESOME group, (if I don’t say so myself! :)) as we could have easily done another day of incredible sessions, but alas, two days is all we have available for this year’s event.

 

Screen Shot 2017-09-29 at 11.10.53 AM

Screen Shot 2017-09-29 at 11.11.04 AM