Sunday, June 30, 2013

Database 12C is out!!!

A new version is always big news for DBAs, and the new 12C has been released for Linux and Solaris. Go to the oracle.com website to download it.

The documentation is here: http://www.oracle.com/pls/db121/homepage
and New Features here: http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm#NEWFTCH1

I've just dipped my toe in, downloaded and installed. The installation is almost identical to V11R2 (./runInstaller), but there is a new checkbox during database creation - "Create As Container Database".

I've just had a quick look through the new features and here are some highlights:



Containers and pluggable databases and multitenant– this is the big new feature that I haven’t looked into yet.
Move a datafile online
Dbms_utility_expand_sql_text – shows the underlying tables in a query that has views – useful for performance tuning.
Varchar(2) columns go from 4000 bytes to 32767 bytes. Not sure how useful that is really
Online move partition
Adaptive Query Optimization and Adaptive SQL Plan Management – this is a biggie – sort of automatic tuning while a query is being executed
Dynamic statistics, and statistics improvements generally
ILM (Information Lifecycle Management) – basically automatic archiving to different tiered storage and compression
In-database archiving – rows are marked invisible to the application if archived, improving performance
Some datapump enhancements

There are some new entries in the alert log that I noticed after install, not sure yet what they mean:

RECOMMENDATION:
  Total System Global Area size is 4818 MB. For optimal performance,
  prior to the next instance restart:
  1. Increase the number of unused large pages by
 at least 2409 (page size 2048 KB, total size 4818 MB) system wide to
  get 100% of the System Global Area allocated with large pages
Mon Jul 01 07:52:46 2013

  2. Large pages are automatically locked into physical memory.
 Increase the per process memlock (soft) limit to at least 4826 MB to lock
 100% System Global Area's large pages into physical memory

and

CELL communication is configured to use 0 interface(s):
CELL IP affinity details:
    NUMA status: non-NUMA system
    cellaffinity.ora status: N/A
CELL communication will use 1 IP group(s):
    Grp 0:
Picked latch-free SCN scheme 3

and, interestingly:

Autotune of undo retention is turned on.

I noticed a LOT more background processes:

oracle   16275     1  0 07:57 ?        00:00:00 ora_pmon_ANDYPLG
oracle   16277     1  0 07:57 ?        00:00:00 ora_psp0_ANDYPLG
oracle   16279     1  0 07:57 ?        00:00:00 ora_vktm_ANDYPLG
oracle   16283     1  0 07:57 ?        00:00:00 ora_gen0_ANDYPLG
oracle   16285     1  0 07:57 ?        00:00:00 ora_mman_ANDYPLG
oracle   16289     1  0 07:57 ?        00:00:00 ora_diag_ANDYPLG
oracle   16291     1  0 07:57 ?        00:00:00 ora_dbrm_ANDYPLG
oracle   16293     1  0 07:57 ?        00:00:03 ora_dia0_ANDYPLG
oracle   16295     1  0 07:57 ?        00:00:03 ora_dbw0_ANDYPLG
oracle   16297     1  0 07:57 ?        00:00:02 ora_lgwr_ANDYPLG
oracle   16299     1  0 07:57 ?        00:00:01 ora_ckpt_ANDYPLG
oracle   16301     1  0 07:57 ?        00:00:01 ora_lg00_ANDYPLG
oracle   16303     1  0 07:57 ?        00:00:00 ora_lg01_ANDYPLG
oracle   16305     1  0 07:57 ?        00:00:00 ora_smon_ANDYPLG
oracle   16307     1  0 07:57 ?        00:00:00 ora_reco_ANDYPLG
oracle   16309     1  0 07:57 ?        00:00:00 ora_lreg_ANDYPLG
oracle   16311     1  0 07:57 ?        00:00:06 ora_mmon_ANDYPLG
oracle   16313     1  0 07:57 ?        00:00:02 ora_mmnl_ANDYPLG
oracle   16315     1  0 07:57 ?        00:00:00 ora_d000_ANDYPLG
oracle   16317     1  0 07:57 ?        00:00:01 ora_s000_ANDYPLG
oracle   16329     1  0 07:57 ?        00:00:00 ora_tmon_ANDYPLG
oracle   16331     1  0 07:57 ?        00:00:00 ora_tt00_ANDYPLG
oracle   16333     1  0 07:57 ?        00:00:00 ora_smco_ANDYPLG
oracle   16401     1  0 07:57 ?        00:00:00 ora_aqpc_ANDYPLG
oracle   16405     1  0 07:57 ?        00:00:05 ora_p000_ANDYPLG
oracle   16407     1  0 07:57 ?        00:00:05 ora_p001_ANDYPLG
oracle   16409     1  0 07:57 ?        00:00:02 ora_p002_ANDYPLG
oracle   16411     1  0 07:57 ?        00:00:03 ora_p003_ANDYPLG
oracle   16413     1  0 07:57 ?        00:00:01 ora_p004_ANDYPLG
oracle   16415     1  0 07:57 ?        00:00:01 ora_p005_ANDYPLG
oracle   16417     1  0 07:57 ?        00:00:00 ora_p006_ANDYPLG
oracle   16419     1  0 07:57 ?        00:00:00 ora_p007_ANDYPLG
oracle   16421     1  0 07:57 ?        00:00:00 ora_p008_ANDYPLG
oracle   16423     1  0 07:57 ?        00:00:00 ora_p009_ANDYPLG
oracle   16425     1  0 07:57 ?        00:00:00 ora_p00a_ANDYPLG
oracle   16427     1  0 07:57 ?        00:00:00 ora_p00b_ANDYPLG
oracle   16429     1  0 07:57 ?        00:00:00 ora_p00c_ANDYPLG
oracle   16431     1  0 07:57 ?        00:00:00 ora_p00d_ANDYPLG
oracle   16433     1  0 07:57 ?        00:00:00 ora_p00e_ANDYPLG
oracle   16435     1  0 07:57 ?        00:00:00 ora_p00f_ANDYPLG
oracle   16463     1  0 07:57 ?        00:00:03 ora_cjq0_ANDYPLG
oracle   16468     1  0 07:58 ?        00:00:00 ora_w000_ANDYPLG
oracle   16663     1  0 07:58 ?        00:00:00 ora_qm02_ANDYPLG
oracle   16669     1  0 07:58 ?        00:00:00 ora_q003_ANDYPLG
oracle   18143     1  0 08:08 ?        00:00:00 ora_w001_ANDYPLG
oracle   24619     1  0 08:48 ?        00:00:00 ora_w002_ANDYPLG
oracle   25254     1  0 08:52 ?        00:00:00 ora_w003_ANDYPLG
oracle   27432     1  0 09:05 ?        00:00:00 ora_w004_ANDYPLG
oracle   27434     1  0 09:05 ?        00:00:00 ora_w005_ANDYPLG
oracle   28912 28911  0 09:13 ?        00:00:00 oracleANDYPLG (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   29087     1  0 09:14 ?        00:00:00 ora_p00g_ANDYPLG
oracle   29089     1  0 09:14 ?        00:00:00 ora_p00h_ANDYPLG
oracle   29091     1  0 09:14 ?        00:00:00 ora_p00i_ANDYPLG
oracle   29093     1  0 09:14 ?        00:00:00 ora_p00j_ANDYPLG
oracle   29095     1  0 09:14 ?        00:00:00 ora_p00k_ANDYPLG
oracle   29097     1  0 09:14 ?        00:00:00 ora_p00l_ANDYPLG
oracle   29111     1  0 09:14 ?        00:00:00 ora_q001_ANDYPLG

There are 4 CPUs on the host, so I don't know why there appear to be so many parallel processes, if that's what they are.

Still trying to get a handle on the containers - I created a container called ANDYPLG, then a pluggable database inside that using the dbca called ANDYPLG1.

As you can see from the background processes, that doesn't show up. 

There is a new v$services view which shows the database:

SQL> select pdb from v$services;

PDB
------------------------------
ANDYPLG1
CDB$ROOT
CDB$ROOT
CDB$ROOT
CDB$ROOT

I tried to create a user in ANDYPLG1, but got this:

ORA-65096: invalid common user or role name

Did a Google, and found that you can't do this in a container. It has to be in the pluggable database, so you need to switch to that first:

SQL> alter session set container=ANDYPLG1;

Session altered.
 
SQL> create user andy identified by andy;

User created.

I'll have a play over the next few weeks and post results here.

 


 








Monday, June 24, 2013

Fun with Spatial data

I'm working on some databases that use Spatial data - not the Spatial option, but Locator. Locator is the cut-down version of Spatial that doesn't need a licence. 

To install Locator:


1. Install Oracle JVM
    @?/javavm/install/initjvm.sql
    @?/rdbms/admin/catjava.sql
    @?/xdk/admin/initxml.sql
    @?/xdk/admin/xmlja.sql

2. Install Oracle XMLDB
    @?/rdbms/admin/catqm.sql SYSAUX TEMP NO

3. Install Oracle Multimedia
    @?/ord/admin/ordinst.sql SYSAUX SYSAUX
    @?/ord/im/admin/catim.sql

4. Check the install
    execute validate_ordim;
    select comp_name,status from dba_registry;


The application here has a check to see if Spatial is installed. If it doesn't find the entry in dba_registry, it exits. There is a way to install and de-install Spatial so that it shows up in dba_registry but is "REMOVED". For some reason there is an Oracle note on this - How To Deinstall Oracle Spatial Keeping Oracle Locator [ID 1070647.1]


In 11GR2, there are 2 scripts, one to install and one to remove:


     @?/md/admin/mdinst.sql
     @?/md/admin/mddins.sql


So, now it's been installed, here are some things to look out for.


You can't export / import spatial data.


The application needs to copy schemas around databases. But an export / import of spatial data fails. This is from the Oracle documentation : 


http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_index_query.htm#autoId6

4.1.6 Export and Import Considerations with Spatial Indexes and Data
If you use the Export utility to export tables with spatial data, the behavior of the operation depends on whether or not the spatial data has been spatially indexed:
If the spatial data has not been spatially indexed, the table data is exported. However, you must update the USER_SDO_GEOM_METADATA view with the appropriate information on the target system.
If the spatial data has been spatially indexed, the table data is exported, the appropriate information is inserted into the USER_SDO_GEOM_METADATA view on the target system, and the spatial index is built on the target system. However, if the insertion into the USER_SDO_GEOM_METADATA view fails (for example, if there is already a USER_SDO_GEOM_METADATA entry for the spatial layer), the spatial index is not built.
If you use the Import utility to import data that has been spatially indexed, the following considerations apply:
If the index on the exported data was created with a TABLESPACE clause and if the specified tablespace does not exist in the database at import time, the index is not built. (This is different from the behavior with other Oracle indexes, where the index is created in the user's default tablespace if the tablespace specified for the original index does not exist at import time.)

Which basically means you can't export / import spatial data.

This is a work-around:

1. Export the schema as normal.
2. Create the schema in the target database
3. Create a database link from the target database to the source database as the schema
4. Insert the data from the source into the target: 

    insert into user_sdo_geom_metadata select * from user_sdo_geom_metadata@database_link_name;

5. Commit

6. Perform the import.

However, I had a requirement to do this with 2 databases that weren't connected, so I couldn't create a database link. This meant jumping through some more hoops.


1. Create a temporary table in the source database (as system is fine, just drop it afterwards):


   create table geom_temp as select * from mdsys.sdo_geom_metadata_table where sdo_owner='XXXX';


  where XXXX is the schema name.


2. Export this table and get the dump file onto the target host somehow. Use your imagination.

3. Import the table into the target system schema.
4. Grant select on this table to the user schema.
5. Log into the database as the schema owner and then run the insert:
   
    insert into user_sdo_geom_metadata select SDO_TABLE_NAME,SDO_COLUMN_NAME,SDO_DIMINFO,SDO_SRID from system.geom_temp;

If you receive an error – ORA-13223: duplicate entry – delete the data first – delete from user_sdo_geom_metadata; - and run the insert again.


Another issue I had was that the spatial indexes didn't create because the source tablespace didn't exist in the target database - this is mentioned in the chunk of text from the documentation above. 


However, when I did a "select index_name from dba_indexes where index_name='YYYY';", it showed that it did exist. So I thought it was OK, but the user was getting this:


Caused by: java.sql.SQLException: ORA-13226: interface not supported without a spatial index

I had to drop and recreate the indexes.

Wednesday, June 5, 2013

I made something run faster

I'm not an expert tuner, but know the basics and where to look. Yesterday someone came to me with a problem - 2 databases, same data, same query. One was taking 2 seconds (that's good), one was taking 15 seconds (that's bad).

I checked the obvious things first and compared the results between the 2 servers:

Hardware
Database Parameters
Amount of data
Explain Plan.
Did a "top"
Ran awrrpt

The hardware on the bad server (memory and CPU) is actually a lot better on than on the good server:

Bad = 48GB of RAM, and 24 x 2.93GHz CPUs
Good = 12GB of RAM, and 16 x 2.93GHz CPUs

(just goes to show that throwing hardware at a problem isn't always the solution).

The database parameters on the bad server were beefed up to take advantage of the hardware.

The "top" command didn't show any runaway processes, or anything untoward.

The explain plan was the same for both servers, and showed the query using an index.

I created a 5 GB file on both - we've had issues with disk I/O on some of these hosts before. The bad server created the file a lot faster, so I ruled out the earlier issues we experienced.

I ran

"analyze index owner.index_name validate structure;"

then a

"select * from index_stats;"

on both servers.

The result (sorry about the formatting):

Bad:

HEIGHT     BLOCKS NAME              LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
    3      19272  DBQ_PROCESS_TIME  190128     18768   6004458     5524          18767      103     601529      8028       184802      5765005         190128        1                 104501316   6605987      7        1            4                   0        0            2              32


Good:

HEIGHT     BLOCKS NAME              LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS   PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
    3      17208  DBQ_PROCESS_TIME  9426       16048   401044      6052          16047      87      544198      8028       9392        399684          9426          1                 97820932    945242       1        1            4

The figures from the bad server are a lot higher.

I rebuilt the index on the bad server:

alter index owner.index_name rebuild online parallel 3;

Didn't take long, then re-ran the query - it then ran in 0.01 seconds.

I rebuilt the index the same way on the good server, same result.

I was later told that the application owner did a purge of the table the night before and removed about 10M rows.

If I'd known that before it would have saved me a bit of investigation.