OraMagic: 2012

Monday, November 12, 2012

parallel query queuing in 11gr2

Oracle 11g release 2 introduced a number of enhancements related to parallel query processing.

One new parameter parallel_degree_policy has been introduced. Setting this parameter to MANUAL results in oracle 11g release1 behavior. Setting this parameter to Auto provides three facilities:
1. Each SQL statement will be considered for parallel execution according to a threshold.
2. in-memory parallel executions as well as
3. parallel statement queuing.

This section deals with parallel statement queuing feature.

With this feature , Oracle will queue parallel statement if required parallel server processes are not available. Only once the necessary resources becomes available, statement will dequeued and allowed to run. Prior to 11g release 2, if there were insufficient parallel servers available, then either the statement would be run with downgraded DOP or would serialize. But, in 11g release2 , statement will go into the queue if number of active parallel server process is greater or equal to the PARALLEL_SERVERS_TARGET parameter.
PARALLEL_SERVERS_TARGET is set lower to the PARALLEL_MAX_SERVER (which controls the maximum number of PX servers allowed on the system) , thus preventing the overloading of the system with PX servers and ensuring each parallel statement will get all of the required Px servers. Statement will be dequeued according to FIFO order. Here is the small demonstration for this feature.

Following are the parameters that needs to consider:

parallel_degree_limit string CPU
parallel_degree_policy string AUTO
parallel_max_servers integer 10
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_servers_target integer 6
parallel_threads_per_cpu integer 2

Here parallel_degree_policy is set to Auto. That means each statement will be considered for parallelism.

Now, Lets execute a query.

SQL> set autotrace on

SQL> select /*+ q1 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c group by cust_last_name;

//output not shown//

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2696793116

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | | | |

| 1 | PX COORDINATOR | | | | |
| | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10002 | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | P->S | QC (RAND) |

| 3 | HASH GROUP BY | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | PCWP | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 4 | PX RECEIVE | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,02 | PCWP | |

| 5 | PX SEND HASH | :TQ10001 | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,01 | P->P | HASH |

| 6 | HASH GROUP BY | | 908 | 11804 | 16M (17)| 5
4:07:31 | | | Q1,01 | PCWP | |

| 7 | MERGE JOIN CARTESIAN | | 50G| 617G| 13M (1)| 4

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
5:42:18 | | | Q1,01 | PCWP | |

| 8 | PX RECEIVE | | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,01 | PCWP | |

| 9 | PX SEND BROADCAST | :TQ10000 | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | P->P | BROADCAST |

| 10 | PX BLOCK ITERATOR | | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | PCWC | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 11 | TABLE ACCESS FULL| CUSTOMERS | 55500 | 433K| 225 (1)| 0
0:00:03 | | | Q1,00 | PCWP | |

| 12 | BUFFER SORT | | 918K| 4486K| 16M (17)| 5
4:07:28 | | | Q1,01 | PCWP | |

| 13 | PX BLOCK ITERATOR | | 918K| 4486K| 271 (2)| 0
0:00:04 | 1 | 28 | Q1,01 | PCWC | |

| 14 | TABLE ACCESS FULL | SALES | 918K| 4486K| 271 (2)| 0
0:00:04 | 1 | 28 | Q1,01 | PCWP | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2

While the query is running, from another terminal, view the number of active px processes. These can be identified by ora_pxxx.

[oracle@host1 dbs]$ ps -ef|grep ora_p
oracle 4730 1 0 10:09 ? 00:00:00 ora_pmon_orc11
oracle 4742 1 0 10:09 ? 00:00:00 ora_psp0_orc11
oracle 12314 1 0 12:10 ? 00:00:00 ora_pmon_db11
oracle 12326 1 0 12:10 ? 00:00:00 ora_psp0_db11
oracle 12765 1 0 12:18 ? 00:00:00 ora_p000_db11
oracle 12767 1 1 12:18 ? 00:00:00 ora_p001_db11
oracle 12769 1 0 12:18 ? 00:00:00 ora_p002_db11
oracle 12771 1 0 12:18 ? 00:00:00 ora_p003_db11
oracle 12788 11529 0 12:18 pts/2 00:00:00 grep ora_p

Currently, two sets of parallel server processes are running because DOP is 2, meaning 4 parallel server process are running at a time. Default DOP is 2 because of parallel_degree_limit parameter.

On the same time while the first query is executing, run another query from another terminal.

SQL> set autotrace on

SQL> select /*+ q2 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c group by cust_last_name;

[oracle@host1 ~]$ ps -ef|grep ora_p

oracle 4730 1 0 10:09 ? 00:00:00 ora_pmon_orc11

oracle 4742 1 0 10:09 ? 00:00:00 ora_psp0_orc11

oracle 14001 1 0 12:45 ? 00:00:00 ora_pmon_db11

oracle 14013 1 0 12:45 ? 00:00:00 ora_psp0_db11

oracle 14810 1 0 13:00 ? 00:00:00 ora_p000_db11

oracle 14812 1 0 13:00 ? 00:00:00 ora_p001_db11

oracle 14814 1 6 13:00 ? 00:00:27 ora_p002_db11

oracle 14816 1 6 13:00 ? 00:00:26 ora_p003_db11

oracle 15156 1 0 13:06 ? 00:00:00 ora_p004_db11

oracle 15158 1 0 13:06 ? 00:00:00 ora_p005_db11

oracle 15160 1 33 13:06 ? 00:00:20 ora_p006_db11

oracle 15162 1 0 13:06 ? 00:00:00 ora_p007_db11

oracle 15203 14835 0 13:07 pts/4 00:00:00 grep ora_p

See, number of active processes running has increased .

From the third terminal, run the parallel query again at the same time while the other two queries are active.
Now, you will see that your query is queued. You can monitor the status of SQL statement using V$SQL_MONITOR.

SQL>select status,sql_text from v$sql_monitor where sql_text like '%sh%';

STATUS
-------------------
SQL_TEXT
--------------------------------------------------------------------------------
QUEUED
select /*+ q3 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name

EXECUTING
select /*+ q1 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name

EXECUTING

select /*+ q2 */ cust_last_name, sum(amount_sold) from sh.sales,sh.customers c g
roup by cust_last_name

Additional Note: Statement is waiting on the event "PX QUEUING: statement queue". It is the first statement in the statement queue. All other statements in the queue will be waiting on "ENQ JX SQL statement queue". Only when a statement gets to the head of the queue will the wait event switch to "PX QUEUING: statement queue".
interesting !

Friday, October 19, 2012

change of IP-address in linux box running Oracle server

Most people face problem while working on the database whenever they changed the ip-address and/or hostname of the machine. Enterprise manager may not working properly.

I am mentioning this issue in this post with the problem that I face. My machine is configured with DHCP. Oracle recommends to use static IP address. So I also want to change this. To configure static address in rhel5, you need to follow these two steps:

1. Assigning static IP address

issue neat command

It will open network configuration GUI window.

Select eth0 here, go to edit. Select statically set IP address. Enter ip address and mention subnet mask. Click on OK button. After, click on File >> save to save your changes. After ,you will get the dialogue saying

Changes are saved. You may want to restart the network and network services or restart the computer.

click on OK. Exit the network configuration window. Then, logged in as root user, issue

service network restart.

Now, to change the host name(if you want), make modification in /etc/sysconfig/network. My /etc/sysconfig/network looks like:

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

NETWORKING=yes

NETWORKING_IPV6=yes

HOSTNAME=host2.oracle.com

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

2. Now, as a second step, make appropriate entry in /etc/hosts. My /etc/hosts after modification looks like:

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

# Do not remove the following line, or various programs that require network functionality will fail.

127.0.0.1 localhost.localdomain localhost

#::1 localhost6.localdomain6 localhost6

192.168.2.102 host2.oracle.com host2

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

Now, reboot the system.

Now, after changing ip address and /or hostname ,you may face certain problems while working on the database. Your listener and/or em console may not working properly.

Use netmgr to configure your listener again.

And to enable proper functioning of em console, start your listener after proper configuration, then start database. .

Firstly, I issued

emca -config dbcontrol db

Command ran successfully. But after getting enterprise manager console , homepage shows

Error : java.lang.Exception: UnknownHostException sending request :: host1.oracle.com.

The solution is to issue below command , not above.

[Below command won't work if you do not start the database]

[oracle@host2 ~]$ emca -config dbcontrol db -repos recreate

And, answer the questions that it asks. Here is the output:

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

STARTED EMCA at Oct 19, 2012 11:27:22 AM

EM Configuration Assistant, Version 10.2.0.1.0 Production

Enter the following information:

Database SID: orcl

Database Control is already configured for the database orcl

You have chosen to configure Database Control for managing the database orcl

This will remove the existing configuration and the default settings and perform a fresh configuration

Do you wish to continue? [yes(Y)/no(N)]: y

Listener port number: 1521

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional): sys

Invalid email.

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

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

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/10.2.0/db_1

Database hostname ................ host2.oracle.com

Listener port number ................ 1521

Database SID ................ orcl

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

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

Do you wish to continue? [yes(Y)/no(N)]: y

Oct 19, 2012 11:27:45 AM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/orcl/emca_2012-10-19_11-27-22-AM.log.

Oct 19, 2012 11:27:46 AM oracle.sysman.emcp.util.DBControlUtil stopOMS

INFO: Stopping Database Control (this may take a while) ...

Oct 19, 2012 11:27:48 AM oracle.sysman.emcp.EMReposConfig dropRepository

INFO: Dropping the EM repository (this may take a while) ...

Oct 19, 2012 11:28:56 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Oct 19, 2012 11:28:56 AM oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) ...

Oct 19, 2012 11:32:41 AM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully created

Oct 19, 2012 11:32:44 AM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) ...

Oct 19, 2012 11:34:22 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: Database Control started successfully

Oct 19, 2012 11:34:22 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: >>>>>>>>>>> The Database Control URL is http://host2.oracle.com:5501/em <<<<<<<<<<<

Enterprise Manager configuration completed successfully

FINISHED EMCA at Oct 19, 2012 11:34:31 AM

[oracle@host2 ~]$ emctl start dbconsole

TZ set to Asia/Calcutta

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

http://host2.oracle.com:5501/em/console/aboutApplication

- An instance of Oracle Enterprise Manager 10g Database Control is already running.

And Enjoy..............

Thursday, October 11, 2012

Resolving Autotrace error

I was working in 10.2.0 version. When I tried to set autotrace to on. I got SP2-0618 error. And here is a small illustration and solution.

SQL> conn hr/hr
Connected.

SQL> set autotrace on;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> conn / as sysdba
Connected.

SQL> set autotrace on;

SQL>select * from hr.demo1;

ID
----------
1
2
3
4
5
6
7
8
9
10
11

ID
----------
12
13
----------output truncated----------------------------

Execution Plan
----------------------------------------------------------
Plan hash value: 970289426

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 195K| 21 (5)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEMO1 | 50000 | 195K| 21 (5)| 00:00:01 |
---------------------------------------------------------------------------

Statistics not shown.

SQL> conn / as sysdba
Connected.
SQL> alter user scott identified by tiger account unlock;

User altered.

SQL> conn scott/tiger;
Connected.

SQL> set autotrace on;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> conn / as sysdba
Connected.

SQL> grant plustrace to public;
grant plustrace to public
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

Execute plustrce.sql from $ORACLE_HOME/sqlplus/admin folder.

SQL> @?/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

After the execution of the script,

SQL> grant plustrace to public;

Grant succeeded.

SQL> conn hr/hr
Connected.

SQL> set autotrace on;

No error shown.
From hereon, autotrace facility will output properly.

SQL>conn / as sysdba
Connected.
SQL> set autotrace on;

select * from hr.demo1;

ID
----------
1
2
3
4
5
6
7
8
9
10
11

ID
----------
12
13
----------output truncated----------------------------

Execution Plan
----------------------------------------------------------
Plan hash value: 970289426

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50000 | 195K| 21 (5)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DEMO1 | 50000 | 195K| 21 (5)| 00:00:01 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3415 consistent gets
0 physical reads
0 redo size
923122 bytes sent via SQL*Net to client
37048 bytes received via SQL*Net from client
3335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50000 rows processed

Statistics also shown.
I also tested the same thing in 11g release 2 and found that that is also true for 11g.

Thats all.

Friday, March 9, 2012

login as sysoper

I want get to know about the role of oper group that has always been created during installation of oracle10g on linux box. 3 groups are always created as a prequisite for oracle 10g installation on linux box. They are oper,dba and oinstall.
I was knowing the purpose of all these groups. For ex, dba group is used so that an os user can logged in as / as sysdba. oinstall is the owner for oracle inventory. And in my case, there is an os user named oracle which is the member of both these groups, oinstall as well as dba. A user which is the member of dba group can logged in as / as sysdba as well as / as sysoper, and that is what oracle user can does.
Now I wanted to understand the purpose of oper group then. It was clear to me that oper group is created so that an os user can logged in as / as sysoper to work as system operator and to perform day-to -day basic administrative tasks such as startup, shutdown, backup ,recovery,etc. And the user who can logged in as sysdba has more powerful privilege that sysoper. That means there must be a second os user who can perform the role of system operator by logging as / as sysoper and not as / as sysdba.

Then I started with creating a second user, besides oracle, who can acess oracle database to perform basic administrative operations. After various tries and errors , I succeded. It is how I did.

$useradd geet
$ chmod -R 777 /u01
$usermod -G oper geet
$ls -l /u01
total 20
drwxrwxrwx 8 oracle oinstall 4096 Mar 1 15:40 app
--------rest truncated-------------

ls -ld /u01
drwxrwxrwx 8 oracle oinstall 4096 Feb 10 10:16 /u01

ls -l /u01/app
total 24
drwxrwxrwx 8 oracle dba 4096 Mar 1 10:32 admin
drwxrwxrwx 6 oracle dba 4096 Mar 1 10:36 flash_recovery_area
drwxrwxrwx 2 oracle dba 4096 Mar 1 16:20 logminer_dir
drwxrwxrwx 6 oracle dba 4096 Mar 1 10:35 oradata
drwxrwxrwx 7 oracle dba 4096 Mar 1 10:21 oraInventory
drwxrwxrwx 3 oracle oinstall 4096 Feb 8 12:37 product

Check the permission for $ORACLE_HOME/bin/oracle. It should be

$ls -l oracle
-rwsrwsrwx 1 oracle dba 93362259 Mar 7 11:26 oracle
Here is two s.

$id geet
uid=503(geet) gid=506(geet) groups=506(geet),501(oper),503(oinstall)

$su - geet
[geet@new-host-15 ~]$ export ORACLE_HOME=/u01/app/product/10.2.0/db_1
[geet@new-host-15 ~]$ export ORACLE_SID=orcl
[geet@new-host-15 ~]$ cd $ORACLE_HOME
[geet@new-host-15 db_1]$ cd bin
[geet@new-host-15 bin]$ ./sqlplus / as sysoper

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 7 14:12:19 2012

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

Connected.

If this fails:

$ ./sqlplus / as sysoper

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 6 14:17:26 2012

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

ERROR:
ORA-01031: insufficient privileges

Then check $ORACLE_HOME/rdbms/lib config.c which looks something like this:

/* SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access. */
/* Refer to the Installation and User's Guide for further information. */
#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oper"
char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};

if second #define do not contain oper, but dba, then u have to modify this file as above. After modification you have to relink this executable. To relink I used:
$relink all

But after relinking, I am able to issue

conn / as sysoper

successfully.

but when issuing
SQL> conn scott/tiger
ERROR:
ORA-00600: internal error code, arguments: [17069], [0x30AD70A4], [], [], [],
[], [], []
ERROR:
ORA-00600: internal error code, arguments: [17069], [0x30AD70A4], [], [], [],
[], [], []

Error accessing package DBMS_APPLICATION_INFO
Connected.

This error is solved by running utlirp.sql script by logging as sysdba.