OraMagic

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 !

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..............

OraMagic

Monday, November 12, 2012

parallel query queuing in 11gr2

Friday, October 19, 2012

change of IP-address in linux box running Oracle server

Followers