Dear Readers,
In this article, we will see the Oracle : Important Queries for Parallel Feature (SecureCRT).
Menubar Name : Parallel
Tab Name : Create table
show parameter parallel_max_servers
|
1 |
create table owner.tablename_bkp as select /*+ parallel(tablename,64) */ * from owner.tablename ; |
|
1 |
create table vinod.tablename_bkp as select /*+ parallel(EMP,4) */ * from SCOTT.EMP; |
Tab Name : Finding_Parallel_Queries
|
1 2 3 4 5 6 |
set linesize 300 col username for a19 col terminal for a15 col osuser for a15 col program for a45 col time for a25 |
|
1 2 3 4 5 6 7 8 9 |
select inst_id,sid,sql_id,program,username,to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') time,TERMINAL,OSUSER from gv$session where program like '%P0%' and sql_id is not null; INST_ID SID SQL_ID PROGRAM USERNAME TIME TERMINAL OSUSER ---------- ---------- ------------- --------------------------------------------- ------------------- ------------------------- --------------- --------------- 1 58 29d991q9rq5kn oracle@awsserver003 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle 1 59 29d991q9rq5kn oracle@awsserver003 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle 2 37 29d991q9rq5kn oracle@awsserver004 (P001) SYS 27-FEB-2021 21:08:38 pts/1 oracle 2 207 29d991q9rq5kn oracle@awsserver004 (P000) SYS 27-FEB-2021 21:08:38 pts/1 oracle |
Tab Name : Select Query with Parallel Option
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
--select /*+ parallel( UTIL_SESSIONS,4) */ * from scott.UTIL_SESSIONS; -- select /*+ parallel(EMP,4) */ count(1) from SCOTT.EMP; 21:18:57 SQL> select /*+ parallel(EMP,4) */ count(1) from SCOTT.EMP; COUNT(1) ---------- 8886209 Elapsed: 00:00:00.73 -- With out Parallel HINT 21:19:03 SQL> select count(1) from SCOTT.EMP; COUNT(1) ---------- 8886209 Elapsed: 00:00:02.29 |
Tab Name : Find SQL Text for SQL ID
|
1 2 |
set long 100000 select distinct sql_text FROM gv$sql WHERE sql_id= '&SQL_ID'; |
Tab Name : Enable Session with Parallel
|
1 |
--alter session enable parallel dml; |
Tab Name : Disable Session for Parallel
|
1 |
--alter session disable parallel dml; |
Tab Name : Insert with parallel option
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
--alter session enable parallel dml; --insert /*+ append */ into scott.DEPT1 select /*+ parallel(DEPT,4) */ * from scott.dept; 21:34:23 SQL> 21:34:23 SQL> insert /*+ append */ into VINOD.EMP select /*+ parallel(EMP,4) */ * from SCOTT.EMP; 8886209 rows created. Elapsed: 00:00:19.92 21:36:53 SQL> rollback; Rollback complete. Elapsed: 00:00:00.00 21:37:03 SQL> insert into VINOD.EMP select * from SCOTT.EMP; 8886209 rows created. Elapsed: 00:01:52.67 21:39:01 SQL> rollback; Rollback complete. Elapsed: 00:01:28.90 |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Note: Please test scripts in Non Prod before trying in Production.
Loading...
