Home » SQL & PL/SQL » SQL & PL/SQL » how to find degree for parallel DML. (Oracle 11g)
how to find degree for parallel DML. [message #577032] Tue, 12 February 2013 01:44 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

I am inserting 50 million records into a table MAIL_LOG.
I am using the hint /*+ append parallel (MAIL_LOG, 12) */.
But for my table degree is 1.

SELECT table_name, degree
FROM   user_tables
WHERE   table_name = 'MAIL_LOG';


I have following clarifications.
1) What degree I should use.
2) On what basis I have to give the degree.
3) Have we use constant degree all the times.
4) How to check my insert statement is using parallel degree.
5) How to find the degree at session level.

Please help me.

Thanks in advance.
Re: how to find degree for parallel DML. [message #577037 is a reply to message #577032] Tue, 12 February 2013 02:01 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Begin by readng chapter 8 of the VLDB Guide. All of chapter 8!
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel.htm#i1009828

Then, with the current release, it is easy. Run the Resource Manager calibration routine, and enable the automatic parallel degree policy. Enable parallel DML for your session. Then let Uncle Oracle decide what degree to use.
Re: how to find degree for parallel DML. [message #577038 is a reply to message #577032] Tue, 12 February 2013 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) Depends on your hardware and concurrent workload
2) See 1)
3) Don't understand the question
4) Query v$px_session where qcsid=<your session>
5) There is no degree at session level, you can use "show parameter parallel" to know the parallel parameters for your session

Regards
Michel
Re: how to find degree for parallel DML. [message #577076 is a reply to message #577038] Tue, 12 February 2013 03:24 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your reply.

AS per your suggestion I have enabled DOP is to AUTO.

ALTER SESSION SET PARALLEL_DEGREE_POLICY = AUTO
SELECT /*+ parallel(auto) */  * FROM APPS_GLOBAL.mail_log
SELECT * FROM v$px_session--why it's not returning any records.


show parameter parallel

parallel_max_servers                 integer  1
parallel_min_percent                 integer  0
parallel_min_servers                 integer  0
parallel_min_time_threshold          string   AUTO
parallel_server                      boolean  TRUE
parallel_server_instances            integer  5
parallel_servers_target              integer  1
parallel_threads_per_cpu             integer  2
recovery_parallelism                 integer  0


How to check my select statement is using parallel hint or not.

Please help me.
Thanks.
Re: how to find degree for parallel DML. [message #577080 is a reply to message #577076] Tue, 12 February 2013 03:33 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
I have to congratulate you on reading that chapter so quickly. It took me a very long time to understand it.
You will need to investigate the results of your Resource Manager calibration, your system statistics, the object statistics, and of course the execution plan that the CBO came up with.
Re: how to find degree for parallel DML. [message #577086 is a reply to message #577076] Tue, 12 February 2013 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
parallel_max_servers                 integer  1

If you can have only 1 parallel server then you have no parallelism.

Regards
Michel
Re: how to find degree for parallel DML. [message #577100 is a reply to message #577086] Tue, 12 February 2013 06:47 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
How to make it parallelism.
Re: how to find degree for parallel DML. [message #577102 is a reply to message #577100] Tue, 12 February 2013 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Change the parameter value.

Regards
Michel
Re: how to find degree for parallel DML. [message #577138 is a reply to message #577032] Tue, 12 February 2013 14:25 Go to previous messageGo to next message
xtender
Messages: 33
Registered: January 2011
Location: Russia
Member

Just little addition:
ajaykumarkona wrote on Tue, 12 February 2013 11:44
3) Have we use constant degree all the times.

You "force" parallel dml:
alter session force parallel dml parallel 8;

But this does not guarantee that you will receive all the necessary slaves or even if they are started, they can "do nothing"(for example work with parallel pipelined functions working with global temporary tables).
ajaykumarkona wrote on Tue, 12 February 2013 11:44
4) How to check my insert statement is using parallel degree.

If the query runs for a long time(otherwise you need to add hint "monitor" into query) then you can get stats with "Real-time sql monitoring" at run time or after execution:
select 
    m.SQL_EXEC_ID
   ,m.SQL_EXEC_START
   ,m.PX_QCSID
   ,m.PX_MAXDOP
   ,m.PX_SERVERS_REQUESTED
   ,m.PX_SERVERS_ALLOCATED
from v$sql_monitor m
where 
  m.SQL_ID = :sql_id
-- or: m.sql_exec_id = :sql_exec_id
-- or: m.PX_QCSID = :qc_sid
/

ajaykumarkona wrote on Tue, 12 February 2013 11:44
5) How to find the degree at session level.

select * 
from v$ses_optimizer_env e
where e.NAME like 'parallel%'
and e.sid=userenv('SID')


Best regards,
Sayan Malakshinov
Re: how to find degree for parallel DML. [message #577173 is a reply to message #577138] Wed, 13 February 2013 01:32 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your reply.
However I am confusing with this.
Can you please tell what is the degree I have to use in my insert statement.
AND what is the statement I have to use to enable parallel DML.

ALTER SESSION ENABLE PARALLEL DML
(or)
ALTER SESSION PARALLEL DML PARALLEL 32;


Ultimately me aim is to improve performance.

Please help me.

Thanks in advance.
Re: how to find degree for parallel DML. [message #577177 is a reply to message #577173] Wed, 13 February 2013 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you please tell what is the degree I have to use in my insert statement.


Quote:
1) Depends on your hardware and concurrent workload


Do you think it will be the same thing if you have 1 or 20 CPU, if you have 1 or 20 disks, if you are alone or with 20 concurrent processes?

Regards
Michel
Re: how to find degree for parallel DML. [message #577179 is a reply to message #577173] Wed, 13 February 2013 02:03 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Can you please tell what is the degree I have to use in my insert statement.
AND what is the statement I have to use to enable parallel DML.
For your first question, as I suggested before: trust the optimizer. Enable automatic DOP. For your second question, you have quoted two commands. One works, the other doesn't:
orcl> ALTER SESSION PARALLEL DML PARALLEL 32;
ALTER SESSION PARALLEL DML PARALLEL 32
              *
ERROR at line 1:
ORA-00922: missing or invalid option


orcl> ALTER SESSION ENABLE PARALLEL DML;

Session altered.

orcl>
Re: how to find degree for parallel DML. [message #577183 is a reply to message #577179] Wed, 13 February 2013 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
(just a point) FORCE keyword is missing:
SQL> alter session FORCE parallel dml parallel 8;

Session altered.

Regards
Michel

[Updated on: Wed, 13 February 2013 02:32]

Report message to a moderator

Re: how to find degree for parallel DML. [message #577192 is a reply to message #577102] Wed, 13 February 2013 04:53 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
How to Change the parameter value to make parallel.
Re: how to find degree for parallel DML. [message #577194 is a reply to message #577192] Wed, 13 February 2013 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which parameter?

Regards
Michel
Re: how to find degree for parallel DML. [message #577199 is a reply to message #577194] Wed, 13 February 2013 05:45 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have posted the output of show parameter parallel

After that you replied

parallel_max_servers integer 1
If you can have only 1 parallel server then you have no parallelism.

Then I asked you how to make it parallelism.

you replied Change the parameter value.

I want to know how to Change the parameter value to make parallel.

Please help me.

Thanks.
Re: how to find degree for parallel DML. [message #577200 is a reply to message #577199] Wed, 13 February 2013 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about ALTER SYSTEM as any other parameters?

Regards
Michel
Re: how to find degree for parallel DML. [message #577307 is a reply to message #577200] Thu, 14 February 2013 03:07 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Compared to direct insertion in the table using DB LINK is taking long time.

INSERT /*+ append parallel(auto) */ INTO MAIL_LOG@db_apsp t
select * from mail_log;


Please help me how to improve the performance.

Thanks in advance.
Re: how to find degree for parallel DML. [message #577323 is a reply to message #577307] Thu, 14 February 2013 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Parallel hint is useless until you changed the parameter, this is the talk we have, please read the COMPLETE topic before posting.
So, in short, no it will NOT help.

Regards
Michel
Re: how to find degree for parallel DML. [message #577340 is a reply to message #577323] Thu, 14 February 2013 05:21 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your reply.

I tried the following but it's giving error.

ALTER SYSTEM parallel_max_servers=5;

And also what is the value I should give.

Please help me.

Thanks.
Re: how to find degree for parallel DML. [message #577341 is a reply to message #577340] Thu, 14 February 2013 05:26 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
it's giving error.

Is it? Which one?
Re: how to find degree for parallel DML. [message #577344 is a reply to message #577341] Thu, 14 February 2013 05:43 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have posted the output of show parameter parallel

After that you replied

parallel_max_servers integer 1
If you can have only 1 parallel server then you have no parallelism.

Then I asked you how to make it parallelism.

you replied Change the parameter value.

So that I have tried to change the parameter value.

I got the error ORA-02065: illegal option for ALTER SYSTEM.

Thanks
Re: how to find degree for parallel DML. [message #577345 is a reply to message #577344] Thu, 14 February 2013 05:47 Go to previous messageGo to next message
Littlefoot
Messages: 18822
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
(Nope, I never replied here before, Michel & John discussed that with you. I just thought that you should have provided more info).
Re: how to find degree for parallel DML. [message #577350 is a reply to message #577344] Thu, 14 February 2013 06:00 Go to previous messageGo to next message
John Watson
Messages: 4081
Registered: January 2010
Location: Global Village
Senior Member
Ajay, what you say if one of your people gave you so little information when reporting a problem? You would tell them "stop wasting my time". I have just looked up the error message you reported:
db121a orcl$ oerr ora 2065
02065, 00000, "illegal option for ALTER SYSTEM"
// *Cause: The option specified for ALTER SYSTEM is not supported
// *Action: refer to the user manual for option supported
db121a orcl$
the message is clear: your ALTER SYSTEM command was wrong. But what was the command? I do not know, because you have not told me.

In general, though, if you do not know how to set parameters, I would advise delaying any attempt to use parallel processing. Better to study basic database administration first.
Re: how to find degree for parallel DML. [message #577352 is a reply to message #577350] Thu, 14 February 2013 06:56 Go to previous message
Michel Cadot
Messages: 57603
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition:
- all SQL statements are described in Database SQL Reference
- All parameters are described in Database Reference

Regards
Michel
Previous Topic: need help..
Next Topic: Execute Immediate
Goto Forum:
  


Current Time: Wed Apr 16 18:34:44 CDT 2014

Total time taken to generate the page: 0.06179 seconds