Indexing good practices

Needing to build a table and create indexes, or add indexes, or just want some good practices on changing them, read here…

a challenge with returning a table and testing with pgTAP

I had a nice little challenge getting a Set Equals test with pgTAP working recently. My function returns a table that contains data from across multiple tenants and I needed to be able to validate the result-set, but I ran into a problem with pgTAP set_seq VALUES being able to test it because the table is a “pseudo-type”. Here is how I got it working…

Impact of WHERE clause function(column) use

Often performance issues are the result of using functions against columns in your query. I’ve seen this often where it made sense to write the WHERE clause in this way, but unfortunately it can negate the use of an index, or in this case simply add extra work that does not need to be done. To learn more take a look …

Using a concatenated index to eliminate table access

Recently I dealt with a major performance issues that was causing so much impact on a system that customers could not use it. After identifying the worst query and not finding other indexes or profiles I realized I could try a concatenated index containing all the columns needed from the worst table. This mitigated the load on the CPU so much that the system became usuable again. This can be done with most RDBMS systems even though in this case it was Oracle. Here is what happened and how it was resolved …

Moving SQL Plan baselines

SQL Plan Baselines are a known plan for a given SQL statement. Oracle stores these plans and then uses them to ensure optimal query performance. SQL Plan Baselines often significantly improve query performance without making any changes to your schema or database configuration. Once you’ve got these performance improving plans stored in the SMB you don’t want to leave them behind. The purpose of this paper is to point out some situations when you would want to migrate your SMB and how to do it.

Selecting a range of dates when your “group by” query is missing dates.

I had a developer who needed a report showing “group by” data from a table over a range of dates. The developer needed to show a range of dates, every date included, with the group by statistics even if there was no data for that day. Here is my experience…

My Oracle Data Guard HowTo

Just my notes on using Data Guard, I also have some for Data Guard Broker.

Bad interface, bad interface (vSphere UI)

So, I’m using vSphere for the first time, and I don’t see any info where I expect it to be (actually because I didn’t have privileges to see it). I’m thinking maybe I just need to refresh the screen for the VM I’m looking at and click the refresh button.

Bad, bad, vSphere, don’t you know what the “refresh” button looks like … seriously, rebooting a VM is not a good thing to do by accident and you aren’t helping with that problem by using what looks like a refresh button for a reboot button.

 

Am I wrong here? Let me know.

How to reference a shell variable named by another shell variable

Have you ever had the need to get the value of a shell variable, but the name was all, or partly, determined by another shell variable. That happened to me this week and once you know how to do it it is pretty simple, but it is hard to figure it out.

I’ll try to clarify. I have a shell variable that has been set with a password. To find that variable I have another variable. The second variable is the User Name and is called USER. The first is the password and is called USERPW. (Note: I’m not saying this is the best way to handle passwords, it is what I had to work with.)

In a given situation I’ll need the password for a user that is provided by the variable USER. I thought I could get this in a variety of ways:

$${USER}pw}

${${USER}PW}

etc…

To end the story and give you what you came looking for. Here is the solution (in a test script):

->cat deref.ksh
#!/bin/bash
export USER=MYUSER
export USERPW=
. /somewhere/pwds.sh # this script sets environment variables with passwords
echo echo deference
eval USERPW=\$${USER}PW
echo ${USER}
echo ${USERPW}
sqlplus -s ${USER}/${USERPW} << eof
show user
eof

Here is the output that shows it worked. The last line is the output from the Oracle database.

->./deref.ksh
echo deference
MYUSER 
mysupersecret
USER is "NDTC_DBA"

Selecting rows when the data does not exist

This just came up again at a job. The customer needs to select a month worth of results for a particular object in their system, but the data does not exist for a whole month. Even if it doesn’t they need to show a row for it. So here is how you can Select rows when the data does not exist