Exadata Storage Index Aging – Part 2B

This is the third blog in a series looking at Storage Cell Storage Index Predicate Offloads. Continued from previous post. And there’s more here: Analysis for Test 2B click here.

For Test 2B – This corrects the columns with very little offload so that they get between 60-100 percent offload from the storage index. See the previous 2 blogs for more background information.

Results:
Now we have offload for each test segment and 8 columns offloaded for each In Line (IL) test as expected. We can even see a pattern on when columns are released (aged out) after repeated queries on the 8 columns.

The pattern I see is that after 10 queries columns 1, and 7-21 seemed to keep their offload status without aging out. But what is going on with column 1 that keeps it from aging out for the entire test. And what about 2-6 that seem to age out fairly fast after their query run with a storage index offload.

I see some patterns developing, but may need more eyes info or tests to figure this out. Will continue when I can.

Let me hear your ideas.

Here’s the test sequence again:

Test 2 - Test Plan

Test 2 – Test Plan

Here’s the full output in excel format; this time run to completion:
dcox_exadata_storage_index_aging_test2b

Check out this new tool!

Download this new tool from Carlos Sierra at Carlos Sierra’s Blog via DropBox or EDB360 on Enkitec’s Web Page.

edb360_teaser

EDB360 – Too much stuff to put on one page, here’s a teaser of some of the output.

Does a great job summarizing the state of a database. “The edb360 tool started as a quick and dirty “script” to gather basic information about a database without knowing anything about it before hand. The first rule for edb360 was: it has to install nothing in the database. The second rule became: it has to provide some insight about a database.”, from his blog.

Download it now and try it out for yourself.

Change from global names to non-global names

Had some requests from friends on how to change the global names, there are lots of other posts on this same topic. Here’s another.

If you started up your database with global_names=true and want to change, here is how to do this.

Perform the following tasks.

Example – Global names is using “dbname.dbdomainname.com” as the DB_NAME and DB_DOMAIN combination, non-global names would use “dbname”, with no DB_DOMAIN.

Open up a command tool and Log in as sysdba or SYS.
cmd> sqlplus / as sysdba

Create the file initDB_NAMETEMP.ora (where DB_NAME is your DBNAME).
sql> create pfile=’initTHESHOWTEMP.ora’ from spfile;

Shutdown the database.
sql> shutdown immediate

Keep the command tool open, you’ll jump back to it later.

Now edit the initDB_NAMETEMP.ora file … C:\app\oracle\product\11.2.0\server\database\initDB_NAMETEMP.ora (or $ORACLE_HOME/dbs).

Remove the line:
db_domain=’dbdomainname.com’

Change the line with the *.global_names to false
*.global_name=FALSE

Save the file initDB_NAMETEMP.ora

Back to the command tool from earlier:

create spfile from pfile=’initTHESHOWTEMP.ora’;

Startup the database.
sql> startup;

sql> show parameter db_domain
Check db_domain – should be null.

sql> show parameter global_name
Check global_names – should be false.

sql> show parameter service_name
Check the service name to be sure it matches the db_name. This is just an extra check.

You can run the listener status from the command line to verify the database is correctly registered:
cmd&gt lsnrctl status

Look for the db_name without the domain name

Change any tnsnames.ora files to match accordingly.

Test with tnsping
tnsping db_name

Try to connect, and you’re done.