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.

Yesterday, one of the members on Oracle Community forum posted that his query was not executing in parallel.
The query he used:

SELECT /*+ PARALLEL(r) */
r.c1 , r.c2
FROM table1 r

This was his output from show parameter parallel

SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     4096
parallel_force_local                 boolean     FALSE
parallel_instance_group              string      op015pdb01
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     192
parallel_min_percent                 integer     0
parallel_min_servers                 integer     8
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_servers_target              integer     192
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     64

It turned out that the database he was using is Oracle RAC and what I found interesting is that parallel_instance_group was set to op015pdb01.
The docs clearly say that:

If the value assigned to PARALLEL_INSTANCE_GROUP is the name of a service or group that does not exist, then the operation runs serially. No parallelism is used.

I’ve tested this on my enviorement, and as the documentation say I wasn’t able to execute the query in parallel.

SQL> show parameter parallel_instance_group;

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
parallel_instance_group              string      ora1

SQL> show parameter instance_groups;

NAME                                 TYPE        VALUE
------------------------ ----------- --------------------
instance_groups                      string

SQL> explain plan for select /*+ parallel(r) */ object_id from table1 r;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 963482612

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        | 61519 |   781K|   283   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| TABLE1 | 61519 |   781K|   283   (1)| 00:00:04 |
----------------------------------------------------------------------------

As you can see I got full table scan.
Now lets clear the parallel_instance_group and see what happens.

SQL> alter session set parallel_instance_group='';

Session altered.

SQL> explain plan for select /*+ parallel(r) */ object_id from table1 r;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1574891232

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          | 61519 |   781K|   283   (1)| 00:00:04 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 | 61519 |   781K|   283   (1)| 00:00:04 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          | 61519 |   781K|   283   (1)| 00:00:04 |  Q1,00 | PCWC |            |
|   4 |     TABLE ACCESS FULL| TABLE1   | 61519 |   781K|   283   (1)| 00:00:04 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Always, check your parameters in order to be sure that you haven’t overrode the default behavior :).

You can find the discussion here https://community.oracle.com/message/12499025

Kind Regards,
Ivica

673_bgoug - small 672_ioug

Next Friday on 21th February the Real World Performance Tour 2014 will take place in Sofia Event Center.
The Bulgarian Oracle Users Group, with the support of the Independent Oracle User Group (IOUG) and the partnership of the Romanian Oracle Users Group, is organizing a one day seminar in Sofia.

Speakers on this event are:
- Tom Kyte, Senior Technical Architect ORACLE Server Technology Division
- Graham Wood, Architect Server Technologies
- Andrew Holdsworth, Vice President Real World Performance Server Technologies

More information about the event and registration you can find at http://www.bgoug.org/en/events/details/91.html

One of the Oracle Database 12c new features is the ability of the database to move datafiles to a new location while they are online and being accessed.

From the Oracle 12c documentation:

move_datafile_clause

Use the MOVE DATAFILE clause to move an online data file to a new location. The database can be open and accessing the data file when you perform this operation. The database creates a copy of the data file when it is performing this operation. Ensure that there is adequate disk space for the original data file and the copy before using this clause.

move_datafile_clause

Example:

Database files:

SQL> col name format a50
SQL> select file#, status, name from v$datafile where file# = 6;

FILE# STATUS NAME
---------- ------- --------------------------------------------------
6 ONLINE /u01/app/oracle/oradata/orcl/users01.dbf

6 rows selected.

ASM DATA diskgroup configuration:

SQL> col name format a10
SQL> select name,total_mb,free_mb,usable_file_mb from v$asm_diskgroup

NAME         TOTAL_MB    FREE_MB USABLE_FILE_MB
---------- ---------- ---------- --------------
DATA            61436      61374          61374

Move the datafile to +DATA diskgroup

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/users01.dbf' to '+DATA';

Database altered.

Confirm that datafile new location is +DATA diskgroup

SQL> select file#, status, name from v$datafile where file# = 6;

     FILE# STATUS  NAME
---------- ------- --------------------------------------------------
         6 ONLINE  +DATA/ORCL/DATAFILE/users.256.837699483

6 rows selected.

Here is way to list all hidden parameters and their current session/system values.
You should be careful when changing these parameters and is highly recommended to make change only after consultation with Oracle Support.

select t.indx,
       t.ksppinm parameter_name,
       t.ksppdesc parameter_desc,
       t1.ksppstvl param_sess_value,
       t2.ksppstvl param_system_value

from   x$ksppi t,
       x$ksppcv t1,
       x$ksppsv t2

where  t.indx = t1.indx
       and t.indx = t2.indx
       and t.ksppinm like '_%' escape ''

order by t.indx
/
Follow

Get every new post delivered to your Inbox.

Join 40 other followers