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>


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

Follow

Get every new post delivered to your Inbox.

Join 46 other followers