Friday, December 30, 2011

_trace_files_public

In an earlier post, I looked at the tracefile_identifier parameter. This post, tested on an Oracle 9 database, uses it in conjunction with _trace_files_public. First create a trace file without setting the _trace_files_public parameter:
 
  1* alter session set tracefile_identifier = before
SQL> /
 
Session altered.
 
SQL> select value from v$parameter
  2  where name = '_trace_files_public'
  3  /
 
no rows selected
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> select sysdate from dual
  2  /
 
SYSDATE
---------
30-DEC-11
 
SQL> alter session set sql_trace = false
  2  /
 
Session altered.
 
SQL>
 
This creates a trace file with permissions of 640 so, if you are not in the dba group, you will not be able to read it:
 
TEST9 > ls -ltr
total 8
-rw-r----- 1 oracle dba 2303 Dec 30 11:46 mvltst_ora_59105_BEFORE.trc
TEST9 >
 
Set _trace_files_public to true and repeat the test:
 
SQL> alter session set tracefile_identifier = after
  2  /
 
Session altered.
 
SQL> select value from v$parameter
  2  where name = '_trace_files_public'
  3  /
 
VALUE
--------------------
TRUE
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> select sysdate from dual
  2  /
 
SYSDATE
---------
30-DEC-11
 
SQL> alter session set sql_trace = false
  2  /
 
Session altered.
 
SQL>
 
This creates a trace file with permissions of 644, which anybody can read:
 
TEST9 > ls -ltr
total 24
-rw-r----- 1 oracle dba 2303 Dec 30 11:46 mvltst_ora_59105_BEFORE.trc
-rw-r--r-- 1 oracle dba 2302 Dec 30 12:07 mvltst_ora_56368_AFTER.trc
TEST9 >
 
This can be useful on a test machine, where developers want to read their trace files without DBA intervention.

Hidden Parameters

Oracle has several hidden initialisation parameters. These have names which begin with an underscore. You can see them using the following query, which I ran on an Oracle 9 database:
 
SQL> l
  1  select ksppinm
  2  from   SYS.X$KSPPI
  3* where  substr(KSPPINM,1,1) = '_'
SQL> /
 
 
KSPPINM
-------------------------------------------------------
_trace_files_public
_latch_recovery_alignment
_spin_count
_latch_miss_stat_sid
_max_sleep_holding_latch
_max_exponential_sleep
_use_vector_post
_latch_class_0
_latch_class_1
_latch_class_2
_latch_class_3
 
Etc.
 
KSPPINM
-------------------------------------------------------
_xsolapi_sql_use_bind_variables
_xsolapi_sql_prepare_stmt_cache_size
_xsolapi_sql_result_set_cache_size
_xsolapi_debug_output
_xsolapi_cursor_use_row_cache
_xsolapi_cursor_max_rows_to_cache_per_req
_xsolapi_cursor_max_time_for_partial_cache
_xsolapi_source_trace
 
613 rows selected.
 
SQL>
 
What does it mean when we say they are hidden?  We can see the answer by comparison with an ordinary parameter e.g. sql_trace:
 
SQL> l
  1  select name, value, isdefault
  2  from v$parameter
  3* where name = 'sql_trace'
SQL> /
 
NAME       VALUE      ISDEFAULT
---------- ---------- ---------
sql_trace  FALSE      TRUE
 
SQL>
 
In this case, sql_trace is set to its default value of FALSE but it still has an entry in V$PARAMETER. A hidden parameter, on the other hand, does not have an entry in V$PARAMETER:
 
  1  select name, value, isdefault
  2  from v$parameter
  3* where name = '_trace_files_public'
SQL> /
 
no rows selected
 
SQL>
 
... unless it has been set on purpose. The query below was run on a different Oracle 9 database, which had _trace_files_public set in its parameter file:
 
SQL> l
  1  select name, value, isdefault
  2  from v$parameter
  3* where name = '_trace_files_public'
SQL> /
 
NAME                 VALUE      ISDEFAULT
-------------------- ---------- ---------
_trace_files_public  TRUE       FALSE
 
SQL>
 
Documentation for hidden parameters is not freely available. Oracle will tell you when to implement them, usually when they are replying to a service request. Advice from Oracle to implement a hidden parameter relates to a given database running on a specific Oracle version. I went to an 11g release 2 seminar run by Oracle recently. They discussed upgrade policy there and said that hidden parameters should be removed before an upgrade.

Thursday, December 29, 2011

Automatic Parallel Execution

In 11g release 2, Oracle introduced the following new parameters:
 
parallel_degree_policy
parallel_min_time_threshold
 
The default setting of parallel_degree_policy is manual. With this value, the automatic parallel execution of SQL statements is disabled. This is how Oracle worked before 11g release 2.
 
Setting this parameter to auto enables this functionality. Oracle then parses an SQL statement, works out its execution plan and estimates how long it will take. If this value is greater than  parallel_min_time_threshold seconds, the statement runs in parallel. You can see what difference this makes in the example below, which runs the same query twice.
 
Automatic parallel execution is turned off for the first run:
 
SQL> select value from v$parameter
  2  where name = 'parallel_min_time_threshold'
  3  /
 
VALUE
--------------------
AUTO
 
SQL> select value from v$parameter
  2  where name = 'parallel_degree_policy'
  3  /
 
VALUE
--------------------
MANUAL
 
SQL> set timing on
SQL> select count(*) from
  2  (select a.table_name from
  3   dba_tables a, dba_tables b)
 4  /
 
  COUNT(*)
----------
   9247681
 
Elapsed: 00:02:00.41
SQL> set timing off
 
And the query takes just over 2 minutes. Automatic parallel execution is then turned on for the second run:
 
SQL> alter session set
  2  parallel_min_time_threshold = 1
  3  /
 
Session altered.
 
SQL> alter session set
  2  parallel_degree_policy = 'AUTO'
  3  /
 
Session altered.
 
SQL> set timing on
SQL> select count(*) from
  2  (select a.table_name from
  3   dba_tables a, dba_tables b)
  4  /
 
  COUNT(*)
----------
   9247681
 
Elapsed: 00:00:37.15
SQL> set timing off
 
And the query takes less than 40 seconds.

Long to LOB Conversion

I went on an Oracle 11g release 2 seminar recently. They said that LONG data types are still supported but that Oracle recommends converting them to LOB (i.e. CLOB or NCLOB). I’m not totally convinced by this as they are still using LONG columns themselves e.g. in table SYS.VIEW$, which is one of the underlying tables for the DBA_VIEWS view:
 
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 29 14:27:22 2011
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> desc sys.view$
Name                       Null?    Type
-------------------------- -------- ------------------
OBJ#                       NOT NULL NUMBER
AUDIT$                     NOT NULL VARCHAR2(38)
COLS                       NOT NULL NUMBER
INTCOLS                    NOT NULL NUMBER
PROPERTY                   NOT NULL NUMBER
FLAGS                      NOT NULL NUMBER
TEXTLENGTH                          NUMBER
TEXT                                LONG
 
SQL>
 
Converting a LONG to a CLOB is easy:
 
SQL> create table andrews_table
  2  (name varchar2(10),
  3   address long)
  4  /
 
Table created.
 
SQL> insert into andrews_table values
  2  ('Noddy', '10 High St, Anytown')
  3  /
 
1 row created.
 
SQL> alter table andrews_table modify (address clob)
  2  /
 
Table altered.
 
SQL> desc andrews_table
Name                       Null?    Type
-------------------------- -------- ------------------
NAME                                VARCHAR2(10)
ADDRESS                             CLOB
 
SQL> select * from andrews_table
  2  /
 
NAME       ADDRESS
---------- --------------------
Noddy      10 High St, Anytown
 
SQL>
 
But you need to be certain that you want to do this as there is no going back:
 
SQL> alter table andrews_table modify (address long)
  2  /
alter table andrews_table modify (address long)
                                  *
ERROR at line 1:
ORA-22859: invalid modification of columns
 
SQL>

Long Datatypes

I was looking at LONG datatypes in an Oracle 9 database recently. There are a couple of restrictions with them. You cannot do a create table as select or CTAS on a table or view which contains a LONG column:
  
SQL> desc dba_views
Name                       Null?    Type
-------------------------- -------- ------------------
OWNER                      NOT NULL VARCHAR2(30)
VIEW_NAME                  NOT NULL VARCHAR2(30)
TEXT_LENGTH                         NUMBER
TEXT                                LONG
TYPE_TEXT_LENGTH                    NUMBER
TYPE_TEXT                           VARCHAR2(4000)
OID_TEXT_LENGTH                     NUMBER
OID_TEXT                            VARCHAR2(4000)
VIEW_TYPE_OWNER                     VARCHAR2(30)
VIEW_TYPE                           VARCHAR2(30)
SUPERVIEW_NAME                      VARCHAR2(30)

SQL> create table andrews_views
  2  as select * from dba_views
  3  /
as select * from dba_views
          *
ERROR at line 2:
ORA-00997: illegal use of LONG datatype

SQL>


And you cannot have more than 1 LONG column in a table:

SQL> create table andrews_table
  2  (long1 long)
  3  /


Table created.

SQL> alter table andrews_table add
  2  (long2 long)
  3  /
(long2 long)
*
ERROR at line 2:
ORA-01754: a table may contain only one column of type LONG

SQL>

Wednesday, December 28, 2011

CPU_COUNT

According to Oracle’s own documentation for 10g release 1:
 
On most platforms, Oracle automatically sets the value of CPU_COUNT to the number of CPUs available to your Oracle instance. Do not change the value of CPU_COUNT.
 
The following test was done on an Oracle 9 database running on Tru64:
 
Tru64 > psrinfo -n
number of processors on system = 1
Tru64 > psrinfo -v
Status of processor 0 as of: 12/02/11 15:19:04
  Processor has been on-line since 02/19/2011 17:29:11
  The alpha EV6.7 (21264A) processor operates at 618 MHz,
  has a cache size of 2097152 bytes,
  and has an alpha internal floating point processor.
 
Tru64 > sqlplus '/ as sysdba'
 
SQL*Plus: Release 9.2.0.5.0 - Production on Fri Dec 2 15:19:28 2011
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
 
SQL> col value format a10
SQL> col isdefault format a9
SQL> select value, isdefault from v$parameter
  2  where name = 'cpu_count'
  3  /
 
VALUE      ISDEFAULT
---------- ---------
1          TRUE
 
SQL>
 
The following test was done on an Oracle 9 database running on Linux:
 
Linux > cat /proc/cpuinfo | grep -i 'processor' | wc -l
2
Linux > sqlplus '/ as sysdba'
 
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Dec 2 15:40:12 2011
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
SQL> col value format a10
SQL> col isdefault format a9
SQL> select value, isdefault from v$parameter
  2  where name = 'cpu_count'
  3  /
 
VALUE      ISDEFAULT
---------- ---------
2          TRUE
 
SQL>
 
The following test was done on an Oracle 10 database running on Solaris:
 
Solaris > psrinfo -p -v
The physical processor has 2 virtual processors (0 16)
  UltraSPARC-IV+ (portid 0 impl 0x19 ver 0x22 clock 1500 MHz)
The physical processor has 2 virtual processors (2 18)
  UltraSPARC-IV+ (portid 2 impl 0x19 ver 0x22 clock 1500 MHz)
Solaris > sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Dec 2 15:52:40 2011
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
SQL> col isdefault format a9
SQL> col value format a5
SQL> select value, isdefault from v$parameter
  2  where name = 'cpu_count'
  3  /
 
VALUE ISDEFAULT
----- ---------
4     TRUE
 
SQL>
 
So it looks as if it works as intended. I went to an Oracle seminar recently, where they said that cpu_count works differently in 11g release 2. Once I have investigated, I will cover this in a future post.

ORA-01950

I was in a hurry to get something finished today. I created a user and specified its default tablespace. I logged in as that user then tried to create a table. This failed with an ORA-01950 as the user had no quota on its default tablespace. I granted the user a quota of 5 megabytes and it was then able to create the table. You can see what I mean in the example, which was tested on an Oracle 9 database:
 
SQL> conn / as sysdba
Connected.
SQL> create user andrew
  2  identified by reid
  3  default tablespace user_data
  4  /
 
User created.
 
SQL> grant create session, create table
  2  to andrew
  3  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> create table my_table
  2  (col1 varchar2(10))
  3  /
create table my_table
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USER_DATA'
 
SQL> conn / as sysdba
Connected.
SQL> alter user andrew
  2  quota 5m on user_data
  3  /
 
User altered.
 
SQL> conn andrew/reid
Connected.
SQL> create table my_table
  2  (col1 varchar2(10))
  3  /
 
Table created.
 
SQL>

Saturday, December 24, 2011

Oracle Managed Files (Part 1)

Oracle managed files were introduced in version 9. You can implement them using initialisation parameters. These can be set:
  1. In the init.ora or server parameter file.
  2. In an alter session or alter system statement.
The parameters specify directories which Oracle should use for datafiles in subsequent DDL statements such as create tablespace etc. You can see what I mean in the example below, which I ran on Oracle 9.2.0.4.0:

First, specify the directory where files should be created using the db_create_file_dest parameter:

SQL> alter session set db_create_file_dest = '/mnt/redhat';
 
Session altered.
 
SQL>
 
Now create a tablespace. Oracle is managing the creation of datafiles so no filename is required:
 
SQL> create tablespace andrew
  2  datafile size 10m
  3  /
 
Tablespace created.
 
SQL>
 
Check the name(s) of the datafile(s) in the tablespace. There is only one and Oracle has created it in the location specified by the db_create_file_dest parameter:
 
SQL> l
  1  select file_name, bytes from dba_data_files
  2* where tablespace_name = 'ANDREW'
SQL> /
 
FILE_NAME                                     BYTES
---------------------------------------- ----------
/mnt/redhat/o1_mf_andrew_7fh2qylt_.dbf     10485760
 
SQL>
 
Add a datafile to the tablespace and check the name(s) of the datafile(s) again:
 
SQL> alter tablespace andrew add datafile size 5m
  2  /
 
Tablespace altered.
 
SQL> select file_name, bytes from dba_data_files
  2  where tablespace_name = 'ANDREW'
  3  /
 
FILE_NAME                                     BYTES
---------------------------------------- ----------
/mnt/redhat/o1_mf_andrew_7fh2qylt_.dbf     10485760
/mnt/redhat/o1_mf_andrew_7fh2zndg_.dbf      5242880
 
SQL>
 
Look at the files at the Linux level:
 
TEST9 > pwd
/mnt/redhat
TEST9 > ls -1
o1_mf_andrew_7fh2qylt_.dbf
o1_mf_andrew_7fh2zndg_.dbf
TEST9 >
 
Drop the tablespace:
 
SQL> drop tablespace andrew
  2  /
 
Tablespace dropped.
 
SQL>
 
Oracle deletes managed files once they are no longer required.Check that the files have gone at the Linux level:
 
TEST9 > pwd
/mnt/redhat
TEST9 > ls -l
total 0
TEST9 >

Tuesday, December 06, 2011

ORA-01507

I had to put a database into archivelog mode today. The problem when I do this is that I can never remember if the database has to be mounted or not. The answer is that it does. If it isn't, you get an ORA-01507:

ORACLE10 > sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 6 15:23:22 2011
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
Connected to an idle instance.
 
SQL> startup nomount
ORACLE instance started.
 
Total System Global Area  176160768 bytes
Fixed Size                  2028688 bytes
Variable Size             134220656 bytes
Database Buffers           33554432 bytes
Redo Buffers                6356992 bytes
SQL> alter database archivelog
  2  /
alter database archivelog
*
ERROR at line 1:
ORA-01507: database not mounted
 
SQL> alter database mount
  2  /
 
Database altered.
 
SQL> alter database archivelog
  2  /
 
Database altered.
 
SQL> alter database open
  2  /
 
Database altered.
 
SQL> select log_mode from v$database
  2  /
 
LOG_MODE
------------
ARCHIVELOG
 
SQL>

Saturday, December 03, 2011

Oracle Database 11g Release 2 Upgrade Seminar

I went to an Oracle seminar at CPC Venues in London recently. It explained how and why you should move to Oracle 11g release 2. The speakers were Mike Appleyard, John Nangle and Peter Alsop. It was extremely well done and, if you get the chance, you should go on it.

When I returned to work, I asked for a copy of the slides from the presentation and they sent me a link to download them a few hours later.

I decided to try out a new feature, which allows you to use solid state disk storage to extend your database's buffer cache. I then found a test database running on 11g release 2. Unfortunately I did not have a solid state disk to hand so I had to make do with an ordinary one.

To use this feature, you need to add two new parameters to your init.ora file. This is the first one:

db_flash_cache_file='/export/home/oracle/andrew/ssd'

It gives the name of a file on a solid state disk, which will be used as the buffer cache extension. Oracle creates it if it does not exist.

The second parameter gives the size of the file:

db_flash_cache_size=10m

Then I bounced the database:

11gR2 > sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 2 18:24:58 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup force
ORA-01078: failure in processing system parameters
ORA-12427: invalid input value for db_flash_cache_size parameter

SQL>

This failure did not come as much of a surprise as the notes from the presentation suggested a size of 120 gigabytes! I tried again with a size of 100m and the database opened OK.

This is the file which Oracle created:
 
11gR2 > pwd
/export/home/oracle/andrew
11gR2 > ls -l ssd
-rw-r-----   1 oracle   dba      104857600 Dec  2 18:08 ssd
11gR2 >

That's as far as I can go with this new feature for now. Once I have a solid state disk, I will try it out properly and report my findings.

In the meantime, I will read through the slides from the presentation, try out some other new features and let you know how I get on.