Just a quick short post in the late hours ( :) ) on how to check the cluster name.

You can find out the cluster name with $ORA_CRS_HOME/bin/cemutlo -n

[grid@oel-ocm-pc2 ~]$ . oraenv
ORACLE_SID = [+ASM2] ? +ASM2
The Oracle base remains unchanged with value /u01/app/grid
[grid@oel-ocm-pc2 ~]$
[grid@oel-ocm-pc2 ~]$ cemutlo -n
ocm-cluster
[grid@oel-ocm-pc2 ~]$

Further, if you want to check the clusterware version installed,
run $ORA_CRS_HOME/bin/crsctl query crs softwareversion

[grid@oel-ocm-pc2 ~]$ crsctl query crs softwareversion
Oracle Clusterware version on node [oel-ocm-pc2] is [11.2.0.3.0]
[grid@oel-ocm-pc2 ~]$

 

Regards,
Ivica

As you may already know result cache represents nice feature which is used to store results of query execution in the result cache area  (part of the SGA).

From 11g you can also cache pl/sql function results in the SGA and its available to all sessions which runs the application.
It’s easy to use, what you need to do is just mark the pl/sql function that its results should be cached.

You enable result caching for a function by adding the RESULT_CACHE clause in the function definition.
Also you can specify the optionally RELIES_ON clause which specifies tables/views on which the function results depends – since in this example the results depend on the HR.EMPLOYEES table I’ve added the optional keyword RELIES_ON

SQL> create function getEmpSalary(p_email varchar2)
  2  return number
  3  result_cache relies_on (employees)
  4  is
  5  v_salary employees.salary%type;
  6  begin
  7  select salary into v_salary from employees where lower(email) = lower(p_email);
  8  return v_salary;
  9  end;
 10  /

Function created.

Since this is test environment I flushed the cache and confirm that there are no cached information by querying the V$RESULT_CACHE_OBJECTS dictionary view.

SQL> exec dbms_result_cache.flush;

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from v$result_cache_objects;

  COUNT(*)
----------
         0

SQL>

Execute the function.

SQL>
SQL> select HR.GETEMPSALARY('SKING') from dual;

HR.GETEMPSALARY('SKING')
------------------------
                   24000

SQL>

Now we should see some information in the V$RESULT_CACHE_OBJECTS regarding our PL/SQL function.

SQL> col name format a70
SQL> col namespace format a20
SQL> select id, type, namespace, status, name from v$result_cache_objects;

        ID TYPE       NAMESPACE            STATUS    NAME
---------- ---------- -------------------- --------- ----------------------------------------------------------------------
         2 Dependency                      Published HR.EMPLOYEES
         0 Dependency                      Published HR.GETEMPSALARY
         1 Result     PLSQL                Published "HR"."GETEMPSALARY"::8."GETEMPSALARY"#8440831613f0f5d3 #1

SQL>

 

There are some restriction for this feature that prevent caching, and those are:

If the function is pipelined table function
If there are IN OUT or OUT parameters
If the function has IN parameters of the following types (BLOB,CLOB,NCLOB,REF CURSOR,collection,object,record)
If the return type is one of the following (BLOB,CLOB,NCLOB,REF CURSOR,collection,object,record)
If the function is defined in a module that has the invoker’s rights or in an anonymous block

 

For more advanced topics about how to handle session-specific settings/application context check the online documentation:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS706

 

Regards,
Ivica

Just a little experiment with the DUAL table.
Someone might find it useful.

[oracle@rac1edu ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 21 08:21:59 2014

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


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

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select count(*) from dual;

 COUNT(*)
----------
 1

SQL> desc dual;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------
 DUMMY VARCHAR2(1)

SQL> drop table dual;

Table dropped.

SQL> select * from dual;
select * from dual
 *
ERROR at line 1:
ORA-01775: looping chain of synonyms


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 849530880 bytes
Fixed Size 1348244 bytes
Variable Size 524291436 bytes
Database Buffers 318767104 bytes
Redo Buffers 5124096 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01775: looping chain of synonyms
Process ID: 27181
Session ID: 129 Serial number: 3


SQL>

Re-create the dual table:

Start up the database in upgrade mode with replication_dependency_tracking= false


SQL> startup upgrade pfile='/home/oracle/pfile.ora';
ORACLE instance started.

Total System Global Area 849530880 bytes
Fixed Size 1348244 bytes
Variable Size 524291436 bytes
Database Buffers 318767104 bytes
Redo Buffers 5124096 bytes
Database mounted.
Database opened.
SQL>
SQL> create table dual
(dummy varchar2(1))
storage (initial 1)
/

Table created.

SQL> insert into dual values('X')
/

1 row created.

SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction - commit or rollback first
SQL>
SQL> commit;

Commit complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rac1edu ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 21 08:33:42 2014

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 849530880 bytes
Fixed Size 1348244 bytes
Variable Size 524291436 bytes
Database Buffers 318767104 bytes
Redo Buffers 5124096 bytes
Database mounted.
Database opened.
SQL> select count(*) from dual;

COUNT(*)
----------
 1

SQL>
SQL> grant select on dual to public;
Grant succeeded.
SQL>

If you want to create repeating baseline template from sqlplus you might struggle if you want to set the frequency to daily (every day in the week).
This is because in the documentation it is not specified what you should specify for the day_of_week parameter of the dbms_workload_repository.crete_baseline_template function.

What is specified is this:
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_workload_repos.htm#ARPLS69130

Parameter: day_of_week
Description: Day of week that the baseline should repeat on. Specify one of the following values: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY

If you want the baseline for specific day, then great just specify one of the available values.

But if you want day_of_week to be set to daily then you’ll have to specify ALL

begin
 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(day_of_week => 'ALL',
                                                   hour_in_day => 10,
                                                   duration    => 6,
                                                   start_time  => next_day(sysdate,'MONDAY'),
                                                   end_time    => to_date('01012015','ddmmyyyy'),
                                                   baseline_name_prefix  => 'TEST_',
                                                   template_name => 'TEMPLATE_FOR_TESTING',
                                                   expiration   => NULL,
                                                   dbid         => null);
end;
/
PL/SQL procedure successfully completed.
Follow

Get every new post delivered to your Inbox.

Join 49 other followers