Oracle Database Discussions

Findings and Recommendations
----------------------------

Finding 1: Top SQL by DB Time
Impact is .04 active sessions, 35.19% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found.

Recommendation 1: SQL Tuning
Estimated benefit is .01 active sessions, 13.92% of total activity.
-------------------------------------------------------------------
Action
Tune the PL/SQL block with SQL_ID "6jbrg916bjmqc". Refer to the "Tuning
PL/SQL Applications" chapter of Oracle's "PL/SQL User's Guide and
Reference".
Related Object
SQL statement with SQL_ID 6jbrg916bjmqc.
DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME
ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) :=
:job_name; job_subname VARCHAR2(30) := :job_subname; job_owner
VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE :=
:job_start; job_scheduled_start TIMESTAMP WITH TIME ZONE :=
:job_scheduled_start; window_start TIMESTAMP WITH TIME ZONE :=
:window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end;
BEGIN DECLARE
ename VARCHAR2(30);
BEGIN
ename := dbms_sqltune.execute_tuning_task(
'SYS_AUTO_SQL_TUNING_TASK');
END; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END
IF; END;
Rationale
SQL statement with SQL_ID "6jbrg916bjmqc" was executed 1 times and had
an average elapsed time of 645 seconds.

Recommendation 2: SQL Tuning
Estimated benefit is .01 active sessions, 10.27% of total activity.
-------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "2m7mj8zdz552a".
Related Object
SQL statement with SQL_ID 2m7mj8zdz552a.
select segment1 , vendor_id, vendor_name, hzp.party_number
registry_id, vendor_type_lookup_code, type_1099, pv.employee_id,
num_1099, awt_group_id, allow_awt_flag, hold_all_payments_flag,
hzp.party_id, /*bug 8225059*/ emp.employee_num employee_number from
ap_suppliers pv, hz_parties hzp, /*bug 8225059*/ per_employees_x emp
where (SEGMENT1 LIKE :1) AND ( pv.party_id = hzp.party_id and /*bug
8225059*/pv.employee_id = emp.employee_id(+) and enabled_flag = 'Y'
and trunc(sysdate) < nvl(trunc(end_date_active), trunc(sysdate+1))
and nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
and po_vendors_ap_pkg.get_num_active_pay_sites(vendor_id, :2)>0 )
order by segment1
Action
Investigate the SQL statement with SQL_ID "2m7mj8zdz552a" for possible
performance improvements.
Related Object
SQL statement with SQL_ID 2m7mj8zdz552a.
select segment1 , vendor_id, vendor_name, hzp.party_number
registry_id, vendor_type_lookup_code, type_1099, pv.employee_id,
num_1099, awt_group_id, allow_awt_flag, hold_all_payments_flag,
hzp.party_id, /*bug 8225059*/ emp.employee_num employee_number from
ap_suppliers pv, hz_parties hzp, /*bug 8225059*/ per_employees_x emp
where (SEGMENT1 LIKE :1) AND ( pv.party_id = hzp.party_id and /*bug
8225059*/pv.employee_id = emp.employee_id(+) and enabled_flag = 'Y'
and trunc(sysdate) < nvl(trunc(end_date_active), trunc(sysdate+1))
and nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
and po_vendors_ap_pkg.get_num_active_pay_sites(vendor_id, :2)>0 )
order by segment1
Rationale
SQL statement with SQL_ID "2m7mj8zdz552a" was executed 50 times and had
an average elapsed time of 6.8 seconds.

Recommendation 3: SQL Tuning
Estimated benefit is .01 active sessions, 5.3% of total activity.
-----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "06qdv20nktq2m".
Related Object
SQL statement with SQL_ID 06qdv20nktq2m.
select segment1, vendor_name, num_1099, vat_registration_num,
vendor_id, '','',hold_all_payments_flag, pv.party_id, 'SUPPLIER'
party_type, /*bug 8225059*/ emp.employee_num employee_number from
ap_suppliers pv , /*bug 8225059*/per_employees_x emp where (SEGMENT1
LIKE :1) AND ( pv.employee_id = emp.employee_id(+) /*bug 8225059*/ )
order by upper(segment1)
Action
Investigate the SQL statement with SQL_ID "06qdv20nktq2m" for possible
performance improvements.
Related Object
SQL statement with SQL_ID 06qdv20nktq2m.
select segment1, vendor_name, num_1099, vat_registration_num,
vendor_id, '','',hold_all_payments_flag, pv.party_id, 'SUPPLIER'
party_type, /*bug 8225059*/ emp.employee_num employee_number from
ap_suppliers pv , /*bug 8225059*/per_employees_x emp where (SEGMENT1
LIKE :1) AND ( pv.employee_id = emp.employee_id(+) /*bug 8225059*/ )
order by upper(segment1)
Rationale
SQL statement with SQL_ID "06qdv20nktq2m" was executed 17 times and had
an average elapsed time of 10 seconds.

Recommendation 4: SQL Tuning
Estimated benefit is .01 active sessions, 5.09% of total activity.
------------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "arsw8990yh3wh".
Related Object
SQL statement with SQL_ID arsw8990yh3wh.
SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sampling(0)
no_sql_tune no_monitoring optimizer_features_enable(default)
OPT_ESTIMATE(@"innerQuery", TABLE, "PAP#2", SCALE_ROWS=99.91557619)
*/ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS(
"PAP#2") */ 1 AS C1 FROM "HR"."PER_PERIODS_OF_SERVICE" "B#0",
"HR"."PER_ALL_ASSIGNMENTS_F" "SYS_ALIAS_1#1", "HR"."PER_ALL_PEOPLE_F"
"PAP#2" WHERE (DECODE("APPS"."HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"A
PPS"."HR_SECURITY"."SHOW_PERSON"("PAP#2"."PERSON_ID","PAP#2"."CURRENT
_APPLICANT_FLAG","PAP#2"."CURRENT_EMPLOYEE_FLAG","PAP#2"."CURRENT_NPW
_FLAG","PAP#2"."EMPLOYEE_NUMBER","PAP#2"."APPLICANT_NUMBER","PAP#2"."
NPW_NUMBER"))='TRUE') AND
("PAP#2"."EFFECTIVE_START_DATE"<=TRUNC(SYSDATE@!)) AND
("PAP#2"."EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!)) AND
("PAP#2"."EMPLOYEE_NUMBER" IS NOT NULL) AND
("PAP#2"."BUSINESS_GROUP_ID"=DECODE("APPS"."HR_GENERAL"."GET_XBG_PROF
ILE"(),'Y',"PAP#2"."BUSINESS_GROUP_ID","APPS"."HR_GENERAL"."GET_BUSIN
ESS_GROUP_ID"())) AND
("SYS_ALIAS_1#1"."PERSON_ID"="PAP#2"."PERSON_ID") AND
("SYS_ALIAS_1#1"."PRIMARY_FLAG"='Y') AND
("SYS_ALIAS_1#1"."ASSIGNMENT_TYPE"='E') AND
("SYS_ALIAS_1#1"."PERIOD_OF_SERVICE_ID"="B#0"."PERIOD_OF_SERVICE_ID")
) innerQuery
Action
Investigate the SQL statement with SQL_ID "arsw8990yh3wh" for possible
performance improvements.
Related Object
SQL statement with SQL_ID arsw8990yh3wh.
SELECT /* DS_SVC */ /*+ cursor_sharing_exact dynamic_sampling(0)
no_sql_tune no_monitoring optimizer_features_enable(default)
OPT_ESTIMATE(@"innerQuery", TABLE, "PAP#2", SCALE_ROWS=99.91557619)
*/ SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS(
"PAP#2") */ 1 AS C1 FROM "HR"."PER_PERIODS_OF_SERVICE" "B#0",
"HR"."PER_ALL_ASSIGNMENTS_F" "SYS_ALIAS_1#1", "HR"."PER_ALL_PEOPLE_F"
"PAP#2" WHERE (DECODE("APPS"."HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"A
PPS"."HR_SECURITY"."SHOW_PERSON"("PAP#2"."PERSON_ID","PAP#2"."CURRENT
_APPLICANT_FLAG","PAP#2"."CURRENT_EMPLOYEE_FLAG","PAP#2"."CURRENT_NPW
_FLAG","PAP#2"."EMPLOYEE_NUMBER","PAP#2"."APPLICANT_NUMBER","PAP#2"."
NPW_NUMBER"))='TRUE') AND
("PAP#2"."EFFECTIVE_START_DATE"<=TRUNC(SYSDATE@!)) AND
("PAP#2"."EFFECTIVE_END_DATE">=TRUNC(SYSDATE@!)) AND
("PAP#2"."EMPLOYEE_NUMBER" IS NOT NULL) AND
("PAP#2"."BUSINESS_GROUP_ID"=DECODE("APPS"."HR_GENERAL"."GET_XBG_PROF
ILE"(),'Y',"PAP#2"."BUSINESS_GROUP_ID","APPS"."HR_GENERAL"."GET_BUSIN
ESS_GROUP_ID"())) AND
("SYS_ALIAS_1#1"."PERSON_ID"="PAP#2"."PERSON_ID") AND
("SYS_ALIAS_1#1"."PRIMARY_FLAG"='Y') AND
("SYS_ALIAS_1#1"."ASSIGNMENT_TYPE"='E') AND
("SYS_ALIAS_1#1"."PERIOD_OF_SERVICE_ID"="B#0"."PERIOD_OF_SERVICE_ID")
) innerQuery
Rationale
SQL statement with SQL_ID "arsw8990yh3wh" was executed 2 times and had
an average elapsed time of 85 seconds.




Rationale
SQL statement with SQL_ID "cks0np5q9dnvj" was executed 729 times and had
an average elapsed time of 0.21 seconds.
Rationale
Average CPU used per execution was 0.12 seconds.


Finding 4: PL/SQL Execution
Impact is .02 active sessions, 15.26% of total activity.
--------------------------------------------------------
PL/SQL execution consumed significant database time.

Recommendation 1: SQL Tuning
Estimated benefit is 0 active sessions, 4.44% of total activity.
----------------------------------------------------------------
Action
Tune the entry point PL/SQL "SYS.DBMS_SQLTUNE.EXECUTE_TUNING_TASK#1" of
type "PACKAGE" and ID 252290. Refer to the PL/SQL documentation for
addition information.
Rationale
46 seconds spent in executing PL/SQL ID 0.

Recommendation 2: SQL Tuning
Estimated benefit is 0 active sessions, 4.16% of total activity.
----------------------------------------------------------------
Action
Tune the entry point PL/SQL "SYS.DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC"
of type "PACKAGE" and ID 3217. Refer to the PL/SQL documentation for
addition information.
Rationale
130 seconds spent in executing PL/SQL
"SYS.DBMS_SPACE.OBJECT_SPACE_USAGE" of type "PACKAGE" and ID 3217.

Recommendation 3: SQL Tuning
Estimated benefit is 0 active sessions, 3.33% of total activity.
----------------------------------------------------------------
Action
Tune the entry point PL/SQL "APPS.HR_GENERAL.GET_BUSINESS_GROUP_ID" of
type "PACKAGE" and ID 77479. Refer to the PL/SQL documentation for
addition information.
Rationale
93 seconds spent in executing PL/SQL "APPS.FND_PROFILE.GET" of type
"PACKAGE" and ID 78735.

Recommendation 4: SQL Tuning
Estimated benefit is 0 active sessions, 3.33% of total activity.
----------------------------------------------------------------
Action
Tune the entry point PL/SQL "APPS.HR_GENERAL.GET_XBG_PROFILE" of type
"PACKAGE" and ID 77479. Refer to the PL/SQL documentation for addition
information.
Rationale
83 seconds spent in executing PL/SQL "APPS.FND_PROFILE.GET" of type
"PACKAGE" and ID 78735.


Finding 5: Commits and Rollbacks
Impact is .01 active sessions, 5.6% of total activity.
------------------------------------------------------
Waits on event "log file sync" while performing COMMIT and ROLLBACK operations
were consuming significant database time.

Recommendation 1: Application Analysis
Estimated benefit is .01 active sessions, 5.6% of total activity.
-----------------------------------------------------------------
Action
Investigate application logic for possible reduction in the number of
COMMIT operations by increasing the size of transactions.
Rationale
The application was performing 83 transactions per minute with an
average redo size of 4340 bytes per transaction.

Recommendation 2: Host Configuration
Estimated benefit is .01 active sessions, 5.6% of total activity.
-----------------------------------------------------------------
Action
Investigate the possibility of improving the performance of I/O to the
online redo log files.
Rationale
The average size of writes to the online redo log files was 6 K and the
average time per write was 11 milliseconds.

Symptoms That Led to the Finding:
---------------------------------
Wait class "Commit" was consuming significant database time.
Impact is .01 active sessions, 5.6% of total activity.


Finding 6: Top SQL By I/O
Impact is 0 active sessions, 3.86% of total activity.
-----------------------------------------------------
Individual SQL statements responsible for significant user I/O wait were
found.

Recommendation 1: SQL Tuning
Estimated benefit is 0 active sessions, 4.53% of total activity.
----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SQL statement with SQL_ID "8szmwam7fysa3".
Related Object
SQL statement with SQL_ID 8szmwam7fysa3.
insert into wri$_adv_objspace_trend_data select timepoint,
space_usage, space_alloc, quality from
table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,
NULL, 'FALSE', :5, 'FALSE'))
Rationale
SQL statement with SQL_ID "8szmwam7fysa3" was executed 4589 times and
had an average elapsed time of 0.033 seconds.
Rationale
Average time spent in User I/O wait events per execution was 0.028
seconds.

Symptoms That Led to the Finding:
---------------------------------
Wait class "User I/O" was consuming significant database time.
Impact is .01 active sessions, 5.94% of total activity.


Finding 7: Unusual "Network" Wait Event
Impact is 0 active sessions, 3.37% of total activity.
-----------------------------------------------------
Wait event "SQL*Net message from dblink" in wait class "Network" was consuming
significant database time.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 3.37% of total activity.
----------------------------------------------------------------
Action
Investigate the cause for high "SQL*Net message from dblink" waits.
Refer to Oracle's "Database Reference" for the description of this wait
event.

Recommendation 2: Application Analysis
Estimated benefit is 0 active sessions, 3.37% of total activity.
----------------------------------------------------------------
Action
Investigate the cause for high "SQL*Net message from dblink" waits with
P1 ("driver id") value "675562835" and P2 ("#bytes") value "1".

Recommendation 3: Application Analysis
Estimated benefit is 0 active sessions, 2.7% of total activity.
---------------------------------------------------------------
Action
Investigate the cause for high "SQL*Net message from dblink" waits in
Module "SFXAPSTGDMP".

Recommendation 4: Application Analysis
Estimated benefit is 0 active sessions, 2.7% of total activity.
---------------------------------------------------------------
Action
Investigate the cause for high "SQL*Net message from dblink" waits in
Service "finprod".

Symptoms That Led to the Finding:
---------------------------------
Wait class "Network" was consuming significant database time.
Impact is 0 active sessions, 3.43% of total activity.


Finding 8: Undersized SGA
Impact is 0 active sessions, 3.13% of total activity.
-----------------------------------------------------
The SGA was inadequately sized, causing additional I/O or hard parses.
The value of parameter "sga_target" was "1024 M" during the analysis period.

Recommendation 1: Database Configuration
Estimated benefit is 0 active sessions, 2.83% of total activity.
----------------------------------------------------------------
Action
Increase the size of the SGA by setting the parameter "sga_target" to
1536 M.

Symptoms That Led to the Finding:
---------------------------------
Hard parsing of SQL statements was consuming significant database time.
Impact is .04 active sessions, 35.43% of total activity.
Wait class "User I/O" was consuming significant database time.
Impact is .01 active sessions, 5.94% of total activity.


Finding 9: Hard Parse Due to Invalidations
Impact is 0 active sessions, 2.36% of total activity.
-----------------------------------------------------
Cursors were getting invalidated due to DDL operations. This resulted in
additional hard parses which were consuming significant database time.

Recommendation 1: Application Analysis
Estimated benefit is 0 active sessions, 2.36% of total activity.
----------------------------------------------------------------
Action
Investigate appropriateness of DDL operations.

Symptoms That Led to the Finding:
---------------------------------
Hard parsing of SQL statements was consuming significant database time.
Impact is .04 active sessions, 35.43% of total activity.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Additional Information
----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.

The database's maintenance windows were active during 11% of the analysis
period.