Oracle Knowledge Table: 2013

Friday, October 18, 2013

DB12c - Session-private statistics for global temporary tables

In previous releases, the database did not maintain statistics for global temporary tables and non-global temporary tables differently.

The database maintained one version of the statistics shared by all sessions, even though data in different sessions could differ.

Starting in Oracle Database 12c, you can set the table-level preference GLOBAL_TEMP_STATS to make statistics on a global temporary table shared or session-specific. If set to session-specific, then you can gather statistics for a global temporary table in one session, and then use the statistics for this session only. Meanwhile, users can continue to maintain a shared version of the statistics.

During optimization, the optimizer first checks whether a global temporary table has session-specific statistics. If yes, the optimizer uses them. Otherwise, the optimizer uses shared statistics if they exist.

SQL> select banner from v$version where rownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

Session 1

SQL> create global temporary table new_gtt on commit delete rows

2 as

3 select * from all_objects where 1=2;

Table created.

SQL> insert into new_gtt select * from all_objects;

77381 rows created.

SQL> SELECT DBMS_STATS.GET_PREFS( 'GLOBAL_TEMP_TABLE_STATS','HORIA','NEW_GTT')

2 FROM DUAL;

DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','HORIA','NEW_GTT')

-------------------------------------------------------------------------------

SESSION

SQL>

SQL> exec dbms_stats.gather_table_Stats('HORIA','NEW_GTT',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')

PL/SQL procedure successfully completed.

SQL> select /*+ gather_plan_statistics*/ count(*) from new_gtt;

COUNT(*)

----------

77381

SQL> select * from table (dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------

SQL_ID 8432nj7acjwpx, child number 0

-------------------------------------

select /*+ gather_plan_statistics*/ count(*) from new_gtt

Plan hash value: 1213234781

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1313 |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1313 |

| 2 | TABLE ACCESS FULL| NEW_GTT | 1 | 77381 | 77381 |00:00:00.01 | 1313 |

----------------------------------------------------------------------------------------

Note

-----

- Global temporary table session private statistics used

18 rows selected.

We can see that the session private statistics were used.

When querying the user_tab_Statistics we can see the entry specific to this session, showing correct number of rows.

SQL> select table_name, num_rows, scope

2 from user_tab_statistics

3 where table_name ='NEW_GTT';

TABLE_NAME NUM_ROWS SCOPE

------------------------- ---------- -------

NEW_GTT SHARED

NEW_GTT 77384 SESSION

Session 2

SQL> insert into new_gtt select * from all_objects where owner <> 'SYS';

42081 rows created.

SQL>

SQL> exec dbms_stats.gather_table_Stats('HORIA','NEW_GTT',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select /*+ gather_plan_statistics*/ count(*) from new_gtt;

COUNT(*)

----------

42081

SQL> select * from table (dbms_xplan.display_cursor(format=>'ALLSTATS LAST'))

2 ;

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------

SQL_ID 8432nj7acjwpx, child number 1

-------------------------------------

select /*+ gather_plan_statistics*/ count(*) from new_gtt

Plan hash value: 1213234781

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 722 |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 722 |

| 2 | TABLE ACCESS FULL| NEW_GTT | 1 | 42081 | 42081 |00:00:00.01 | 722 |

----------------------------------------------------------------------------------------

Note

-----

- Global temporary table session private statistics used

18 rows selected.

SQL>

We can see that this second session did not share the cursor it used new one.

The optimizer used session-specific statistics.

SQL> select table_name, num_rows, scope

2 from user_tab_statistics

3 where table_name ='NEW_GTT';

TABLE_NAME NUM_ROWS SCOPE

------------------------- ---------- -------

NEW_GTT SHARED

NEW_GTT 42084 SESSION

Reference

Oracle® Database

SQL Tuning Guide

12c Release 1 (12.1)

E15858-15

Friday, October 4, 2013

New background processes in DB12c

Name	Expanded Name	Description	External Properties
AQPC	AQ Process Coordinator	AQPC is responsible for performing administrative tasks for AQ Master Class Processes including commands like starting, stopping, and other administrative tasks. This process is automatically started on instance startup.	Database instances Advanced Queueing
ARSn	ASM Recovery Slave Process	The ASM RBAL background process coordinates and spawns one or more of these slave processes to recover aborted ASM transactional operations. These processes run only in the Oracle ASM instance.	Oracle ASM instances
BWnn	Database Writer Process	See the Description for the DBWn process in this table for more information about the BWnn process.	Database instances
FENC	Fence Monitor Process	CSS monitors RDBMS instances which are connected to the Oracle ASM instance and constantly doing I/Os. When the RDBMS instance terminates due to a failure, all the outstanding I/O’s from the RDBMS instance should be drained and any new I/O’s rejected. FENC receives and processes the fence request from CSSD.	Oracle ASM instances
GCRn	Global Conflict Resolution Slave Process	GCRn processes are transient slaves that are started and stopped as required by LMHB to perform synchronous or resource intensive tasks.	Database instances, Oracle ASM instances, Oracle RAC
IPC0	IPC Service Background Process	IPC0 handles very high rates of incoming connect requests, as well as, completing reconfigurations to support basic messaging and RDMA primitives over several transports such as UDP, RDS, InfiniBand and RC.	Oracle RAC
LDDn	Global Enqueue Service Daemon Helper Slave	LDDn processes are slave processes spawned on demand by LMDn processes. They are spawned to help the dedicated LMDn processes with various tasks when certain workloads start creating performance bottlenecks. These slave processes are transient as they are started on demand and they can be shutdown when no longer needed. There can be up to 36 of these slave processes (LDD0-LDDz).	Database instances, Oracle ASM instances, Oracle RAC
LGnn	Log Writer Worker	On multiprocessor systems, LGWR creates worker processes to improve the performance of writing to the redo log. LGWR workers are not used when there is a SYNC standby destination. Possible processes include LG00-LG99.	Database instances
LREG	Listener Registration Process	LREG notifies the listeners about instances, services, handlers, and endpoint.	Database instances, Oracle ASM instances, Oracle RAC
OFSD	Oracle File Server Background Process	This background process listens for new file system requests, both management (like mount, unmount, and export) and I/O requests, and executes them using Oracle threads.	Database instances, Oracle RAC
QMnn	AQ Master Class Process	Each of this type of process represents a single class of work item such as AQ notification, queue monitors, and cross process.	Database instances Advanced Queueing
RM	RAT Masking Slave Process	This background process is used with Data Masking and Real Application Testing.	Database instances
RMON	Rolling Migration Monitor Process	The RMON process is spawned on demand to run the protocol for transitioning an ASM cluster in and out of rolling migration mode.	Oracle ASM instance, Oracle RAC
RPOP	Instant Recovery Repopulation Daemon	The RPOP process is responsible for re-creating and repopulating data files from snapshots files. It works with the instant recovery feature to ensure immediate data file access. The local instance has immediate access to the remote snapshot file’s data, while repopulation of the recovered primary data files happens concurrently. Any changes in the data are managed between the instance’s DBW processes and RPOP to ensure the latest copy of the data is returned to the user.	Database instances
SAnn	SGA Allocator	A small fraction of SGA is allocated during instance startup. The SAnn process allocates the rest of SGA in small chunks. The process exits upon completion of SGA allocation.	Database instances
SCCn	ASM Disk Scrubbing Slave Check Process	SCCn acts as a slave process for SCRB and performs the checking operations. The possible processes are SCC0-SCC9.	Oracle ASM instances
SCRB	ASM Disk Scrubbing Master Process	SCRB runs in an Oracle ASM instance and coordinates Oracle ASM disk scrubbing operations.	Oracle ASM instances
SCRn	ASM Disk Scrubbing Slave Repair Process	SCRn acts as a slave process for SCRB and performs the repairing operations. The possible processes are SCR0-SCR9.	Oracle ASM instances
SCVn	ASM Disk Scrubbing Slave Verify Process	SCVn acts as a slave process for SCRB and performs the verifying operations. The possible processes are SCV0-SCV9.	Oracle ASM instances
TTnn	Redo Transport Slave Process	TTnn can run as multiple processes, where nn is 00 to ZZ.	Database instances, Data Guard

When the THREADED_EXECUTION initialization parameter is set to TRUE on Linux and UNIX, the DBW, PMON, PSP, and VKTM background processes run as operating system processes, and the other background processes run as operating system threads.

Reference:

http://docs.oracle.com/cd/E16655_01/server.121/e17615/bgprocesses.htm

Wednesday, October 2, 2013

Out-of-Place Materialized View Refresh Option in DB12c

There is new refresh option which has been introduced to improve materialized view refresh performance and availability in DB12c.
This is called out-of-place refresh. It uses outside tables during refresh instead of the existing in-place refresh that directly applies changes to the materialized view container table.

Out-of-place refresh is most useful in situations where there are large amounts of data changes and conventional DML statements do not scale well.

With this refresh option, the entire or affected portions of a materialized view are computed into one or more outside tables.

There are three types of out-of-place refresh:

Out-of-place Fast Refresh
Out-of-place PCT Refresh
Out-of-place Complete Refresh

Using DBMS_MVIEW package, with method = ? and out_of_place = true, out-of-place fast refresh are attempted first, then out-of-place PCT refresh, and finally out-of-place complete refresh.
Here is an example:

SQL> select banner from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> create table my_objects as select * from all_objects;

Table created.

SQL> create materialized view my_objects_mv
2 build immediate
3 refresh on demand
4 as
5 select * from my_objects where owner<>'SCOTT';

Materialized view created.

SQL> begin
2 dbms_mview.refresh('MY_OBJECTS_MV',
3 method => '?',
4 atomic_refresh => FALSE,
5 out_of_place => TRUE);
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select MVIEW_NAME, REFRESH_MODE, REFRESH_METHOD, FAST_REFRESHABLE, LAST_REFRESH_DATE
2 FROM all_mviews
3 WHERE MVIEW_NAME='MY_OBJECTS_MV';

MVIEW_NAME REFRESH_MODE REFRESH_METHOD FAST_REFRESHABLE LAST_REFRESH_DATE
----------------------------------------------------------- ------ -------- ------------------ --
MY_OBJECTS_MV DEMAND FORCE NO 02-OCT-13

Reference:

The Out-of-Place Refresh Option in 12c (Doc ID 1546437.1)

DB12c enabled Options during installation

In prior Oracle Database releases, during installation, you could deselect options which were not used.

In DB12c, you are not prompted the option to deselect and disable options any more - all are enabled by default with a few exceptions which we will show below.

The options not enabled are RAC, ASM, Unified Audit.
However, to name just a few, Active Data Guard, Partitioning, Data Mining for which you will have to pay the license are enabled.

Having these options enabled is a requirement for a CDB.

Full list below retrieved from v$option.

SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> select parameter,value from v$option order by value, parameter;

PARAMETER VALUE
---------------------------------------------------------------- ------
ASM Proxy Instance FALSE
Automatic Storage Management FALSE
I/O Server FALSE
Management Database FALSE
Oracle Database Vault FALSE
Oracle Label Security FALSE
Real Application Clusters FALSE
Unified Auditing FALSE
Active Data Guard TRUE
Adaptive Execution Plans TRUE
Advanced Analytics TRUE
Advanced Compression TRUE
Advanced replication TRUE
Application Role TRUE
Automatic Data Optimization TRUE
Backup Encryption TRUE
Basic Compression TRUE
Bit-mapped indexes TRUE
Block Change Tracking TRUE
Block Media Recovery TRUE
Cache Fusion Lock Accelerator TRUE
Change Data Capture TRUE
Coalesce Index TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Cross Transportable Backups TRUE
DICOM TRUE
Data Mining TRUE
Data Redaction TRUE
Database queuing TRUE
Database resource manager TRUE
Deferred Segment Creation TRUE
Duplexed backups TRUE
Enterprise User Security TRUE
Exadata Discovery TRUE
Export transportable tablespaces TRUE
Fast-Start Fault Recovery TRUE
File Mapping TRUE
Fine-grained Auditing TRUE
Fine-grained access control TRUE
Flashback Data Archive TRUE
Flashback Database TRUE
Flashback Table TRUE
Global Data Services TRUE
Heat Map TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Java TRUE
Join index TRUE
Managed Standby TRUE
Materialized view rewrite TRUE
OLAP TRUE
OLAP Window Functions TRUE
Objects TRUE
Online Index Build TRUE
Online Redefinition TRUE
Oracle Data Guard TRUE
Parallel backup and recovery TRUE
Parallel execution TRUE
Parallel load TRUE
Partitioning TRUE
Plan Stability TRUE
Point-in-time tablespace recovery TRUE
Privilege Analysis TRUE
Proxy authentication/authorization TRUE
Real Application Security TRUE
Real Application Testing TRUE
Result Cache TRUE
SQL Plan Management TRUE
Sample Scan TRUE
SecureFiles Encryption TRUE
Server Flash Cache TRUE
Snapshot time recovery TRUE
Spatial TRUE
Streams Capture TRUE
Table Clustering TRUE
Transparent Application Failover TRUE
Transparent Data Encryption TRUE
Trial Recovery TRUE
Unused Block Compression TRUE
XStream TRUE
Zone Maps TRUE

82 rows selected.

Monday, September 30, 2013

Compression Advisor script - from 9i to 12c

A compression advisor (through DBMS_COMPRESSION) comes packaged with Oracle Database 11g Release 2 and Oracle Database 12c.

Oracle has made available a package you can use with Oracle Database 9i Release 2 through Oracle Database 11g release 1 - this is DBMS_COMP_ADVISOR available for download here.

This script is made to run for all versions, 9i to 12c, and requires only the database version as an input.

DECLARE

v_owner VARCHAR2(60);

v_segment_name VARCHAR2(60);

v_execute112 varchar2(4000);

v_execute10 varchar2(4000);

v_execute varchar2(4000);

BEGIN

v_execute112 := q'[DECLARE

l_blkcnt_cmp pls_integer;

l_blkcnt_uncmp pls_integer;

l_row_cmp pls_integer;

l_row_uncmp pls_integer;

l_cmp_ratio NUMBER;

l_comptype_str VARCHAR2(60);

begin dbms_compression.get_compression_ratio(

-- input parameters

scratchtbsname => 'SYSAUX', -- scratch tablespace

ownname => :v_owner, -- owner of the table

tabname => :v_segment_name, -- table name

partname => NULL, -- partition name

comptype => dbms_compression.COMP_FOR_OLTP, -- compression algorithm

-- output parameters

blkcnt_cmp => l_blkcnt_cmp, -- number of compressed blocks

blkcnt_uncmp => l_blkcnt_uncmp, -- number of uncompressed blocks

row_cmp => l_row_cmp, -- number of rows in a compressed block

row_uncmp => l_row_uncmp, -- number of rows in an uncompressed block

cmp_ratio => l_cmp_ratio, -- compression ratio

comptype_str => l_comptype_str -- compression type

);

dbms_output.put_line('SegmentOwner: '||:v_owner||' SegmentName: '||:v_segment_name||' - '||' Ratio: '||to_char(l_cmp_ratio,'99.99'));

end;]';

v_execute10 := q'[declare

l_compression NUMBER:= 10;

begin dbms_comp_advisor.getratio(:v_owner,:v_segment_name,l_compression);

end;]';

if substr('&db_version',1,4)='11.2' or substr('&&db_version',1,2)='12' then

v_execute := v_execute112;

else

v_execute := v_execute10;

end if;

FOR rec IN (select segment_name, owner

from (

select * from

(select dba_tables.owner, segment_name, segment_type, dba_extents.tablespace_name, round(sum(dba_extents.bytes/1024/1024),2) SizeMB,

dba_tables.num_rows

from dba_extents, dba_tables

where segment_type = 'TABLE'

and dba_tables.table_name = dba_extents.segment_name

and dba_tables.compression = 'DISABLED'

group by dba_tables.owner, segment_name, segment_type, dba_extents.tablespace_name,

dba_tables.num_rows,

to_char(last_analyzed, 'DD-MON-RRRR')

having sum(dba_extents.bytes/1024/1024) > 10

)

order by SizeMB desc)

where rownum <= 50)

LOOP

v_owner := rec.owner;

v_segment_name := rec.segment_name;

execute immediate v_execute using in v_owner,v_segment_name;

END LOOP;

END;

Upgrade to DB12c

This shows the minimum steps I took to upgrade an 11gR2 instance to 12c.

The full reference can be found under this blog entry - Mike Dietrich - he maintains 500 pages of guidelines and best practices to follow for your upgrade.

My database to migrate has 10GB.

1) Install new software in new oracle home
2) Purge recycle bin
3) Check for Invalid objects - recompile if you have invalid objects in SYS or SYSTEM using $ORACLE_HOME/rdbms/admin/utlrp.sql before running the upgrade.
4) Remove outdated parameters, underscores and events from init.ora
5) Necessary components
a) XDB component is mandatory in DB12c
b) Remove components which don't exists any more in DB12c
i. EM DB Control Repository
ii. OLAP AMD
6) Collect fresh stats
7) Parameter recommendations
a) Deprecated in 12c
i. SEC_CASE_SENSITIVE_LOGON=TRUE (this defaults to TRUE in 11g)
ii. This must be aged out
b) Obsolete in 12c
i. _app_ctx_vers
ii. _log_io_size

8) Put the db you want to upgrade in mount mode
9) Go to the new software location (12c) and invoke DBUA (Database Upgrade Assistant) from $ORACLE_HOME/bin/dbua

In step 1 we select the operation to perform.

In step 2 we select the source Oracle Home, and the SID that we plan the upgrade on

In step 2 we are prompted the validation and what action we need in order to comply.

As we have invalid object we fix this in a terminal and click on Check Again

In Oracle DB12c we can do the upgrade using parallelism. This is NEW feature in the upgrades to 12c! This can save up to 40% time.

We are prompted the option to configure EM Express. We can do this at a later time as well by setting the port.

In step 6 we have several options on moving the database files. Read carefully and act accordingly to your needs

We choose to migrate the Listener.

We are prompted to options to recover in case of problems. We choose RMAN as an option to recover.

In step 9 we are prompted with all the details of the Upgrade. This is logical and concise.

We are prompted with a progress of the steps. We can monitor this activity and also monitor the alert log.

The upgrade completes in approximately 2 hours. This was a 10GB database but it had enabled lots of options and this is also a factor which dictates the duration.

In the end we are prompted full details of the upgrade.

That is all.

Thursday, September 26, 2013

Report changes with ENABLE_DDL_LOGGING=TRUE

The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of the Database Lifecycle Management Pack when set to TRUE.

When set to TRUE, the database reports schema changes in real time into the DDL alert log.

SQL> show con_id

CON_ID

------------------------------

SQL> show parameter enable_ddl_logging

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

enable_ddl_logging boolean FALSE

SQL> alter system set enable_ddl_logging=TRUE scope=memory;

System altered.

SQL> show parameter enable_ddl_logging;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

enable_ddl_logging boolean TRUE

The alert log show the change as expected:

Thu Sep 26 15:39:47 2013

ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=MEMORY;

SQL> alter session set container=pdb1;

Session altered.

SQL> create user newuser identified by newuser;

User created.

SQL> grant connect, resource, create table, unlimited tablespace to newuser;

Grant succeeded.

SQL> create table newuser.table1 (col1 number(9));

Table created.

SQL> alter table newuser.table1 add col2 varchar2(10);

Table altered.

SQL> drop user newuser cascade;

User dropped.

SQL> create user abc identified by abc;

User created.

SQL> drop user abc cascade;

User dropped.

[oracle@magenta log]$ pwd

/u01/app/oracle/diag/rdbms/orcl12c/orcl12c/log

[oracle@magenta log]$ ll

total 16

drwxr-x---. 2 oracle oinstall 4096 Sep 26 15:46 ddl

-rw-r-----. 1 oracle oinstall 203 Sep 26 15:51 ddl_orcl12c.log

drwxr-x---. 2 oracle oinstall 4096 Jul 29 14:07 debug

drwxr-x---. 2 oracle oinstall 4096 Jul 29 14:07 test

[oracle@magenta log]$ tail -f ddl_orcl12c.log

Thu Sep 26 15:46:13 2013

diag_adl:create table newuser.table1 (col1 number(9))

diag_adl:alter table newuser.table1 add col2 varchar2(10)

diag_adl:drop user newuser cascade

diag_adl:drop user abc cascade

When ENABLE_DDL_LOGGING is set to true, the following DDL statements are written to the DDL alert log:

ALTER/CREATE/DROP/TRUNCATE CLUSTER

ALTER/CREATE/DROP FUNCTION

ALTER/CREATE/DROP INDEX

ALTER/CREATE/DROP OUTLINE

ALTER/CREATE/DROP PACKAGE

ALTER/CREATE/DROP PACKAGE BODY

ALTER/CREATE/DROP PROCEDURE

ALTER/CREATE/DROP PROFILE

ALTER/CREATE/DROP SEQUENCE

CREATE/DROP SYNONYM

ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE

ALTER/CREATE/DROP TRIGGER

ALTER/CREATE/DROP TYPE

ALTER/CREATE/DROP TYPE BODY

DROP USER

ALTER/CREATE/DROP VIEW

Thursday, September 5, 2013

DB12c - when THREADED_EXECUTION=TRUE native authentication is not supported

When threaded execution is enabled (threaded_execution=TRUE) in the initialization parameter file on UNIX platforms, native authentication (that is, connect / as sysdba or connect /) is not supported.

Workaround: Use password-based authentication. Native authentication will not work for connections where the server execution is a thread.

SQL> select banner from v$version where rownum<=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> select platform_name from v$database;

PLATFORM_NAME

--------------------------------------------------------------------------------

Linux x86 64-bit

SQL> show parameter threaded_execution;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

threaded_execution boolean FALSE

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@xxxxx]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Thu Sep 5 19:14:48 2013

SQL> connect / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> alter system set threaded_execution=TRUE scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-01017: invalid username/password; logon denied

SQL> connect / as sysdba

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> exit

[oracle@xxxxx]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Thu Sep 5 19:17:18 2013

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> show user

USER is "SYS"

SQL> show parameter threaded_execution

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

threaded_execution boolean TRUE

This is logged as Bug 13877504

Monday, September 2, 2013

Gather statistics of SYS objects

There are couple interesting notes on Metalink:
GATHER_FIXED_OBJECTS_STATS Considerations [ID 798257.1]
Also, Metalink note How to Gather Statistics on SYS Objects and ‘Fixed’ Objects? [ID 457926.1]
mentions that “statistics gathering should be done when database has a representative load so that the statistics reflect the normal database activity”

There are some fixed tables that are simply very volatile by nature and it will be extremely hard to get accurate statistics on.
Still, better with than without.

The place I would look is how much time is spent for recursive sql, which basically touches the dictionary tables.

So how often to gather??
It depends. It depends on the load profile, if you regularly add more users (concurrency) in your application,
then gathering system statistics shall be in accordance to that.