Explain Why An Oracle Database Materialized View Does Not Rewrite

Using Oracle Database materialized views for query rewrite, when used in the right way, can really help aid performance with specific queries. It’s one of my favorite ways to quickly help increase performance of specific queries – often queries issued by front end reporting tools.

Materialized views, however, can be finicky in which queries they re-write, if any and the optimiser output doesn’t give away many clues.

Luckily, help is at hand with a small amount of setup and the DBMS_MVIEW.EXPLAIN_REWRITE package.

Setup

First, create the table to store the output created by the DBMS_MVIEW.EXPLAIN_REWRITE package:

CREATE TABLE REWRITE_TABLE(
  statement_id          VARCHAR2(30),   -- id for the query
  mv_owner              VARCHAR2(30),   -- owner of the MV
  mv_name               VARCHAR2(30),   -- name of the MV
  sequence              INTEGER,        -- sequence no of the msg
  query                 VARCHAR2(2000), -- user query
  query_block_no        INTEGER,        -- block no of the current subquery
  rewritten_txt         VARCHAR2(2000), -- rewritten query
  message               VARCHAR2(512),  -- EXPLAIN_REWRITE msg
  pass                  VARCHAR2(3),    -- rewrite pass no
  mv_in_msg             VARCHAR2(30),   -- MV in current message 
  measure_in_msg        VARCHAR2(30),   -- Measure in current message 
  join_back_tbl         VARCHAR2(30),   -- Join back table in message 
  join_back_col         VARCHAR2(30),   -- Join back column in message
  original_cost         INTEGER,        -- Cost of original query
  rewritten_cost        INTEGER,        -- Cost of rewritten query
  flags                 INTEGER,        -- associated flags
  reserved1             INTEGER,        -- currently not used 
  reerved2              VARCHAR2(10))   -- currently not used;

Rewrite output

Once the output table is in place we can run the DBMS_MVIEW.EXPLAIN_REWRITE package to begin to understand why queries are/ are not being rewritten.

Run the package and substitute the following parameters:

  • arg0 is the query you’re trying to re-write with the materialized view.
  • arg1 is the materialized view itself.
declare
begin  
    DBMS_MVIEW.EXPLAIN_REWRITE (
      query=>q'[SELECT SUM(value) val FROM performance.bill_detail]',
      mv=>'performance_mvs.mv_bills');
end;
/

Finally, view the result

SELECT * FROM REWRITE_TABLE

Move Proxmox Container to Different Storage (Updated for LXC)

2015-03-05 00_18_04-Proxmox Virtual Environment storageThe Proxmox Web GUI does not give us the ability to migrate a container from one storage device to another directly. To move a container onto different storage we have to take a backup of the container and restore it to the same ID with a different storage device specified. This can be time laborious when working with several containers.

This is an update to the OpenVZ script found here.

The below script allows you to move an LXC container from one storage device to another. The process requires that the container be stopped, which the script will handle.

Save the below script into a file called migrate.

vi migrate
#!/bin/bash
#
# Filename : migrate
# Description : Migrate Proxmox OpenVZ container from one storage to another
# Author : James Coyle
#
# Version:
# -Date       -Author      -Description
# 20-11-2013  James Coyle  Initial
# 13-12-2017  James Coyle  Changes for LXC
#
#

# Variables
TMP=/tmp      #Location to use to create the backup for transferring to new storage. This needs to be big enough to store the backup archive for the container.

# Do not edit
usage() { 
	echo "Usage: $0" 
	echo "          [-c Required: Container ID to migrate <int>] "
	echo "          [-s Required: Target storage ID <string>]"
	echo "          [-d Optional: Delete the backup file after CT restoration <boolean>]"
	echo ""
	echo "Example: $0 -c 100 -s nasarray"
	echo ""
	exit 1; 
}

while getopts "c:s:d" o; do
  case "${o}" in
    c)
      CT=${OPTARG}
      ;;
    s)
      TARGET_STORAGE=${OPTARG}
      ;;
    d)
      DELETE=true
      ;;
    *)
      usage
      ;;
    esac
done
shift $((OPTIND-1))

# Check mandatory fields
if [ -z "${CT}" ] || [ -z "${TARGET_STORAGE}" ]; then
  usage
fi

RUNNING=false

set -e
set -o pipefail

echo "Moving $CT to $TARGET_STORAGE..."
if pct list| fgrep -w -q "$CT" | grep "running"
then
    RUNNING=true
fi

if $RUNNING
then
    pct stop $CT
fi

vzdump --dumpdir $TMP $CT

ARCHIVE=$(ls -t $TMP/vzdump-lxc-$CT-*.tar | head -n 1)

pct restore $CT $ARCHIVE -force -storage $TARGET_STORAGE

if $RUNNING
then
    pct start $CT
fi

if $DELETE
then
    LOG=$(ls -t $TMP/vzdump-lxc-$CT-*.log | head -n 1)
    echo "Deleting $LOG and $ARCHIVE"
    rm -rf $ARCHIVE $TMP/$LOG
fi

Set execution permissions on the script:

chmod + x migrate

The script has several parameters which are detailed below:

  • -d is specified if you would like the script to delete the temporary backup after the process has completed. Leave this out if you would like the backup tar file to be kept, just in case anything goes wrong.
  • -s is required to specify the name of the target storage. You can find this from the Proxmox Web GUI.
  • -c is required for the container ID to migrate.

In addition, the script contains the variable TMP. This will be the location of the backup tar created as part of the migration process and must contain enough space to store the content of the container being migrated. You can change this to suit your environment.

Example command:

./migrate -d -s newstorage -c 101

 


Reduce Proxmox LXC Backup Size and Time

Category : How-to

Proxmox backs up guests byte-for-byte in a compressed archive. Looking at LXC backups specifically, the file system is compressed into the target backup file with just a few exceptions – temp files aren’t included. You can also add your own exceptions by editing the vzdump.conf to exclude specific file patterns.

All that said, one of the biggest disk space wasters is the cache directory for apt which caches the installation packages for software you have installed. This can generally be safely removed on internet connected machines which will reduce your overall backup size.

For example, a newly created Debian LXC that’s been recently updated shows a total of 206MB of disk used.

du -hs /var/cache/apt/
206M    /var/cache/apt/

After clearing this with the command apt-get clean we can see the space has mostly been freed.

apt-get clean
du -hs /var/cache/apt/
28K    /var/cache/apt/

Considering this whole container is only consuming approximately 1GB of disk space, 200MB is quite significant.

vzdump hooks

Now we can see how much space we can save, we need to make Proxmox issue the apt-get clean command before it creates the backup of our container.

vzdump, the utility which Proxmox uses to perform backups has the ability to call a script for various stages of the backup process – these stages are:

  • backup-start
  • backup-end
  • backup-abort
  • log-end
  • pre-stop
  • pre-restart
  • post-restart

We can use these hooks to run our own commands at any of these points of the backup. For the goal of this blog post, we want to run the apt-get clean command at the point of backup-start.

Create a script on your Proxmox host with the following content:

#!/bin/bash
 
if [ "$1" == "backup-start" ] && [ ${VMTYPE} == "lxc" ]; then
    echo "Running pre backup guest cleanup for $3"
    pct exec "$3" -- bash -c "apt-get clean"
fi

Now edit your vzdump.conf file and add the following line to point to your new script. Remember to change the location of where your script is – I’ve just saved mine in /root/.

script: /root/backup-hook.sh

 


Visit our advertisers

Quick Poll

How often do you change the password for the computer(s) you use?

Visit our advertisers