Friday, May 8, 2015

Working with Oracle Database Parallelizm Issues, specially in RAC environment

Ref:
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel006.htm
http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm
http://docs.oracle.com/cd/E14072_01/server.112/e10837/parallel.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm
Query to check parallel executions on instance:
----------------------------------------------
SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
DEGREE "Degree", REQ_DEGREE "Req Degree"
FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';

SELECT px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst",
       px.SERVER_GROUP "Group", px.SERVER_SET "Set",
       px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event"
FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w
WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND
      s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND
      s.paddr = p.addr (+) AND s.inst_id = p.inst_id (+)
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;

SELECT * from v$pq_sysstat;
SELECT * FROM v$px_process;
SELECT * FROM v$px_sesstat;
SELECT * FROM v$px_process_sysstat;
Examples
========
ALTER TABLE table_name PARALLEL (DEGREE 8);
SELECT /*+PARALLEL(table_alias, degree, nodes)*/ *
FROM table_name ...
OR
SELECT /*+PARALLEL */ * FROM table_name/View name ...

Quick Oracle Database Tunning Tips without OEM(Enterprice Manager)

http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm
http://www.pythian.com/news/584/wouldnt-automatic-sql-plan-management-be-the-11g-killer-app/

create a tunning task
---------------------
variable stmt_task VARCHAR2(64);
variable sts_task VARCHAR2(64);
variable spa_tune_task VARCHAR2(64);
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => 'SELECT S.EMPLID ,S.EMPL_RCD , PDE.EFFDT ,INS.PER_ORG ,INS.ORIG_HIRE_DT ,PDE.SEX ,PRS.BIRTHDATE ,PRS.BIRTHPLACE ,PRS.BIRTHCOUNTRY ,PRS.BIRTHSTATE ,PRS.DT_OF_DEATH ,PDE.HIGHEST_EDUC_LVL ,PDE.FT_STUDENT ,PDE.LANG_CD ,PDE.ALTER_EMPLID ,PDE.MAR_STATUS ,ASG.HOME_HOST_CLASS ,ASG.CMPNY_SENIORITY_DT ,ASG.SERVICE_DT ,ASG.PROF_EXPERIENCE_DT ,ASG.LAST_VERIFICATN_DT ,ASG.OWN_5PERCENT_CO ,ASG.PROBATION_DT ,ASG.ORG_INSTANCE_ERN ,ASG.SENIORITY_PAY_DT FROM PS_PERSON PRS ,PS_PER_ORG_ASGN ASG ,PS_PER_ORG_INST INS ,PS_PERS_DATA_EFFDT PDE ,PS_GP_PYE_STAT_WRK S WHERE S.RUN_CNTL_ID ='ABS_JAN2012_IND' AND S.OPRID = 12345 AND S.EMPLID BETWEEN 1000 AND 19999 AND S.EMPLID = PRS.EMPLID AND S.EMPLID = PDE.EMPLID AND S.FRST_JOB_SEG_IND = 'Y' AND S.EMPLID = ASG.EMPLID AND S.EMPL_RCD = ASG.EMPL_RCD AND ASG.EMPLID = INS.EMPLID AND ASG.ORG_INSTANCE_ERN = INS.ORG_INSTANCE_ERN ORDER BY S.EMPLID, S.EMPL_RCD, PDE.EFFDT DESC ');
---or-----
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(task_name=>'hello1223',sql_id => '4qh14ksgd4dd8',scope='COMPREHENSIVE');
---or-----
DECLARE
sql_text clob;
sqlprofile_name varchar2(30);
task_name varchar2(100);
BEGIN
sql_text :=
task_name:=dbms_sqltune.create_tuning_task(sql_text,task_name=>'TASKREQ01',scope=>'COMPREHENSIVE');
END;
/
execute a tunning task
----------------------
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);
display a tunning task
----------------------
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.report_tuning_task('TASK_4188') AS recommendations FROM dual
SELECT DBMS_SQLTUNE.report_tuning_task(:stmt_task) AS recommendations FROM dual
accept a recommendation sql profile
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_4256',task_owner => 'MD86506', replace => TRUE);
disable sql profile
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('SYS_SQLPROF_013c237dab800000','STATUS','DISABLED');

view task information
--------------------
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'MD86506';

primary_gl_tb

SELECT DBMS_SQLTUNE.report_tuning_task('primary_gl_tb') AS recommendations FROM dual ;
execute dbms_sqltune.accept_sql_profile(task_name => 'primary_gl_tb',task_owner => 'MD86506', replace => TRUE);

USER_ADVISOR_TASKS <-- table which have task information
•DBA_ADVISOR_TASKS
•DBA_ADVISOR_FINDINGS
•DBA_ADVISOR_RECOMMENDATIONS
•DBA_ADVISOR_RATIONALE
•DBA_SQLTUNE_STATISTICS
•DBA_SQLTUNE_BINDS
•DBA_SQLTUNE_PLANS
•DBA_SQLSET
•DBA_SQLSET_BINDS
•DBA_SQLSET_STATEMENTS
•DBA_SQLSET_REFERENCES
•DBA_SQL_PROFILES
•V$SQL
•V$SQLAREA
•V$ACTIVE_SESSION_HISTORY
enable/dislabe/delete sql profile
=================================

SELECT *
FROM DBA_SQL_PROFILES prof,
DBMSHSXP_SQL_PROFILE_ATTR attr
WHERE prof.NAME=attr.PROFILE_NAME
ORDER BY prof.name,
attr.attr_number;
Enable/Disable
Locate the name of the SQL Profile you would like to disable and plug it in to the following statement:
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE('PROFILE_NAME','STATUS','DISABLED');
Dropping a SQL Profile
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('PROFILE_NAME');

SQL for Tunning task

Ref:
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel006.htm
http://docs.oracle.com/cd/E11882_01/server.112/e16638/hintsref.htm
http://docs.oracle.com/cd/E14072_01/server.112/e10837/parallel.htm
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm
Query to check parallel executions on instance:
----------------------------------------------
SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",
DEGREE "Degree", REQ_DEGREE "Req Degree"
FROM GV$PX_SESSION ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

SELECT NAME, VALUE FROM GV$SYSSTAT
WHERE UPPER (NAME) LIKE '%PARALLEL OPERATIONS%'
OR UPPER (NAME) LIKE '%PARALLELIZED%' OR UPPER (NAME) LIKE '%PX%';

SELECT px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst",
       px.SERVER_GROUP "Group", px.SERVER_SET "Set",
       px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event"
FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w
WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND
      s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND
      s.paddr = p.addr (+) AND s.inst_id = p.inst_id (+)
ORDER BY DECODE(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID), px.QCSID,
DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;

SELECT * from v$pq_sysstat;
SELECT * FROM v$px_process;
SELECT * FROM v$px_sesstat;
SELECT * FROM v$px_process_sysstat;

Examples
========
ALTER TABLE table_name PARALLEL (DEGREE 8);
SELECT /*+PARALLEL(table_alias, degree, nodes)*/ *
FROM table_name ...
OR
SELECT /*+PARALLEL */ * FROM table_name/View name ...

CONSUMING WEB SERVICE FROM ORACLE DATABASE 11G USING UTL_HTTP PACKAGE

Contents

1. Create access control list and assign host

1.1 Create access control list

1.2 Create Role and assign to access control list

1.3 Add hosts

1.4 Test Access

2. Write procedure to call web service

1. Create access control list and assign host

1.1 Create access control list

BEGIN

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (

ACL => 'STREAM_TEST.XML',

DESCRIPTION => 'PERMISSIONS TO ACCESS ESUPPORT WEBSITE',

PRINCIPAL => 'MD86506',

IS_GRANT => TRUE,

PRIVILEGE => 'CONNECT');

COMMIT;

END;

1.2 Create Role and assign to access control list

BEGIN

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (

ACL => 'STREAM_TEST.XML',

PRINCIPAL => 'DBA',

IS_GRANT => TRUE,

PRIVILEGE => 'CONNECT',

POSITION => NULL);

COMMIT;

END;

1.3 Add hosts

BEGIN

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (

ACL => 'STREAM_TEST.XML',

HOST => '*.satyam.com);

COMMIT;

END;

BEGIN

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (

ACL => 'STREAM_TEST.XML',

HOST => '172.16.187.25');

COMMIT;

END;

1.4 Test Access

CREATE OR REPLACE PROCEDURE GETTITLE (PURL VARCHAR2)

VRESULT CLOB;

BEGIN

VRESULT := REPLACE(UTL_HTTP.REQUEST(PURL),CHR(10),' ');

VRESULT := REGEXP_REPLACE(VRESULT,'.*<TITLE> ?(.+) ?</TITLE>.*','\1',1,1,'I');

DBMS_OUTPUT.PUT_LINE (VRESULT);

END;

SET SERVEROUTPUT ON

EXECUTE GETTITLE ('HTTP://172.16.187.119:8050');

2. Write procedure to call web service

2.1 UTL_HTTP

1. UTL_HTTP.BEGIN_REQUEST : UTL_HTTP.BEGIN_REQUEST('http://172.16.187.25:8000/sap/xi/adapter_plain?bs=FILE'||'&'||'namespace=http%3A//file2file.com'||'&'||'interface=FILEHTML'||'&'||'qos=EO','POST',NULL,NULL);

Opens http request to the specified URL

2. UTL_HTTP.SET_HEADER:

UTL_HTTP.SET_HEADER (req,'Content-Type','text/xml');

UTL_HTTP.SET_HEADER (req,'Content-Length','200');

Set communication parameters for the http request

3. UTL_HTTP.WRITE_TEXT

UTL_HTTP.WRITE_TEXT (req,'<?xml version="1.0" encoding="UTF-8"?><ns0:FILEHTML xmlns:ns0="http://file2file.com"><row><EMPLID>865072</EMPLID><EMPLANAME>fromDB4</EMPLANAME></row></ns0:FILEHTML>');

Write data into http stream

4. UTL_HTTP.RESPONSE:

-resp := UTL_HTTP.GET_RESPONSE (req);

Capture response of the http stream

5. UTL_HTTP.END_RESPONSE:

Close request and response http connections

6. UTL_HTTP.END_REQUEST:

Close request http connections

2.2 UTL_DBWS

Oracle Fusion Application Installation brief overview

Background
Oracle Fusion Application is best feature mix of e-business suite, jd edwards and peoplesoft integrated with Fusion Middleware products.

Installation of Fusion Application would mean installation of Fusion application components and all the fmw products.

Oracle has simplified install and configure by means of automated installation and configuration along with install validation. Oracle call this automation as provisioning.

Installation process.
So Provisioning basically means installing various oracle products, configuration them to work with each other using oracle supplied special software(framework/life cycle managment tools)

A Framework software executes the installation, configuration and validation of all the required software components.

All the required inputs for installation/configuration and gathered in step called "creating responce file", This is the first step after installation of framework/life cycle managment tools software installation.

you would invoke resonponce file creation step using provisiongWizard and supply inputs in interview phase, after which a responce file is created. This will will be supplied during Applicaiton provisioning phase.

Application provisiong phase runs in below order.

Preverify
Install
Preconfigure
Configure
Configure Secondary
Post Configure
Startup
Validation.

Regards
Manoj

Oracle Producsts Knowledge Base

Friday, May 8, 2015

CONSUMING WEB SERVICE FROM ORACLE DATABASE 11G USING UTL_HTTP PACKAGE

1. Create access control list and assign host

1.1 Create access control list

1.2 Create Role and assign to access control list

1.3 Add hosts

1.4 Test Access

2. Write procedure to call web service

2.1 UTL_HTTP

2.2 UTL_DBWS

Oracle E-business suite logs clean up

My Blog List