Home » RDBMS Server » Performance Tuning » Query With View Vs Select of the View behave differently (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0)
| Query With View Vs Select of the View behave differently [message #440208] |
Thu, 21 January 2010 10:31  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi,
I have a query that involved 4 views,
Original query with 4 views and some where claused runs for 2 mins and get 15 rows,
-- Original
SELECT commongridcolsrtgsandach.batchtnum, commoncombinedview.itype,
consolidatedcombinedview.cmb_bene_name,
commoncombinedview.actionlist,
consolidatedcombinedview.cmb_credit_amount,
consolidatedcombinedview.cmb_credit_currency,
consolidatedcombinedview.cmb_debit_account_number,
consolidatedcombinedview.cmb_value_date,
commoncombinedview.methoddesc,
consolidatedcombinedview.cmb_template_code,
commoncombinedview.status_description,
commoncombinedview.updatecount__, commoncombinedview.statushovertext,
commoncombinedview.FUNCTION,
commoncombinedview_cust.paymentmethod_ll,
commoncombinedview_cust.paymentdescription_ll,
commoncombinedview.accounthovertext,
consolidatedcombinedview.cmb_debit_account_name,
commoncombinedview.product, commoncombinedview.TYPE,
commoncombinedview.digest, commoncombinedview.digestauditid,
commoncombinedview_cust.templatechangedflag,
commoncombinedview.ismodified,
consolidatedcombinedview.cmb_type_description,
commoncombinedview_cust.template_auto_release,
commoncombinedview_cust.template_auto_approve,
commongridcolsrtgsandach.bankname,
commongridcolsrtgsandach.debit_bank_id,
commongridcolsrtgsandach.debit_branch_name,
commongridcolsrtgsandach.origcompname,
commongridcolsrtgsandach.origcompid, commoncombinedview.tnum,
commoncombinedview.cutoff_time,
commongridcolsrtgsandach.bene_account_type,
consolidatedcombinedview.cmb_bene_id,
commongridcolsrtgsandach.bene_address_1,
commongridcolsrtgsandach.bene_address_2,
commongridcolsrtgsandach.bene_address_3,
commongridcolsrtgsandach.bene_country,
commongridcolsrtgsandach.bene_bank_type,
consolidatedcombinedview.cmb_bene_bank_id,
consolidatedcombinedview.cmb_bene_bank_name,
consolidatedcombinedview.cmb_bene_account,
commongridcolsrtgsandach.bene_bank_address_1,
commongridcolsrtgsandach.bene_bank_address_2,
commongridcolsrtgsandach.bene_bank_address_3,
commongridcolsrtgsandach.bene_bank_country,
commongridcolsrtgsandach.correspondent_type,
commongridcolsrtgsandach.correspondent_id,
commongridcolsrtgsandach.correspondent_name,
commongridcolsrtgsandach.correspondent_address_1,
commongridcolsrtgsandach.correspondent_address_2,
commongridcolsrtgsandach.correspondent_address_3,
commongridcolsrtgsandach.correspondent_country,
consolidatedcombinedview.cmb_originator_id,
consolidatedcombinedview.cmb_originator_name,
commongridcolsrtgsandach.originator_address_1,
commongridcolsrtgsandach.originator_address_2,
commongridcolsrtgsandach.originator_address_3,
commongridcolsrtgsandach.originator_country,
commoncombinedview.entered_timestamp, commoncombinedview.entered_by,
commoncombinedview.approved_by_1,
consolidatedcombinedview.cmb_template_description
FROM websystemq4.commongridcolsrtgsandach,
websystemq4.commoncombinedview,
websystemq4.consolidatedcombinedview,
websystemq4.commoncombinedview_cust commoncombinedview_cust
WHERE ( ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND (websystemq4.actionmodeavailablecheck ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod,
commoncombinedview.accountfilter,
NULL,
'SELECT'
) != 0
)
AND ( ( commoncombinedview.TYPE = 'BOOK'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'BOOK'
AND commoncombinedview.entrymethod = 1
)
OR ( commoncombinedview.TYPE = 'INTL'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'INTL'
AND commoncombinedview.entrymethod = 1
)
OR ( commoncombinedview.TYPE = 'TRANSFER'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'TRANSFER'
AND commoncombinedview.entrymethod = 1
)
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'RTGS'
AND commoncombinedview.FUNCTION = 'INST'
)
OR ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND (websystemq4.actionmodeavailablecheck ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod,
commoncombinedview.accountfilter,
NULL,
'SELECT'
) != 0
)
AND ( ( commoncombinedview.TYPE = 'BOOK'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'BOOK'
AND commoncombinedview.entrymethod = 1
)
OR ( commoncombinedview.TYPE = 'INTL'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'INTL'
AND commoncombinedview.entrymethod = 1
)
OR ( commoncombinedview.TYPE = 'TRANSFER'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'TRANSFER'
AND commoncombinedview.entrymethod = 1
)
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'RTGS'
AND commoncombinedview.FUNCTION = 'TMPL'
)
OR ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND ( (commoncombinedview.TYPE = 'SACHCC')
OR (commoncombinedview.TYPE = 'SACHCC')
OR (commoncombinedview.TYPE = 'SACHCD')
OR (commoncombinedview.TYPE = 'SACHCD')
OR (commoncombinedview.TYPE = 'SACHCEC')
OR (commoncombinedview.TYPE = 'SACHCEC')
OR (commoncombinedview.TYPE = 'SACHCP')
OR (commoncombinedview.TYPE = 'SACHCP')
OR (commoncombinedview.TYPE = 'SACHCRC')
OR (commoncombinedview.TYPE = 'SACHCRC')
OR (commoncombinedview.TYPE = 'SACHCSP')
OR (commoncombinedview.TYPE = 'SACHCSP')
OR (commoncombinedview.TYPE = 'SACHCVP')
OR (commoncombinedview.TYPE = 'SACHCVP')
OR (commoncombinedview.TYPE = 'SACHP')
OR (commoncombinedview.TYPE = 'SACHP')
OR (commoncombinedview.TYPE = 'SACHTP')
OR (commoncombinedview.TYPE = 'SACHTP')
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'USACH'
AND commoncombinedview.FUNCTION = 'INST'
)
OR ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND ( (commoncombinedview.TYPE = 'SACHCC')
OR (commoncombinedview.TYPE = 'SACHCC')
OR (commoncombinedview.TYPE = 'SACHCD')
OR (commoncombinedview.TYPE = 'SACHCD')
OR (commoncombinedview.TYPE = 'SACHCEC')
OR (commoncombinedview.TYPE = 'SACHCEC')
OR (commoncombinedview.TYPE = 'SACHCP')
OR (commoncombinedview.TYPE = 'SACHCP')
OR (commoncombinedview.TYPE = 'SACHCRC')
OR (commoncombinedview.TYPE = 'SACHCRC')
OR (commoncombinedview.TYPE = 'SACHCSP')
OR (commoncombinedview.TYPE = 'SACHCSP')
OR (commoncombinedview.TYPE = 'SACHCVP')
OR (commoncombinedview.TYPE = 'SACHCVP')
OR (commoncombinedview.TYPE = 'SACHP')
OR (commoncombinedview.TYPE = 'SACHP')
OR (commoncombinedview.TYPE = 'SACHTP')
OR (commoncombinedview.TYPE = 'SACHTP')
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'USACH'
AND commoncombinedview.FUNCTION = 'TMPL'
)
OR ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND ( (commoncombinedview.TYPE = 'BDACHCC')
OR (commoncombinedview.TYPE = 'BDACHCC')
OR (commoncombinedview.TYPE = 'BDACHCD')
OR (commoncombinedview.TYPE = 'BDACHCD')
OR (commoncombinedview.TYPE = 'BDACHCEC')
OR (commoncombinedview.TYPE = 'BDACHCEC')
OR (commoncombinedview.TYPE = 'BDACHCP')
OR (commoncombinedview.TYPE = 'BDACHCP')
OR (commoncombinedview.TYPE = 'BDACHCRC')
OR (commoncombinedview.TYPE = 'BDACHCRC')
OR (commoncombinedview.TYPE = 'BDACHCSP')
OR (commoncombinedview.TYPE = 'BDACHCSP')
OR (commoncombinedview.TYPE = 'BDACHCVP')
OR (commoncombinedview.TYPE = 'BDACHCVP')
OR (commoncombinedview.TYPE = 'BDACHP')
OR (commoncombinedview.TYPE = 'BDACHP')
OR (commoncombinedview.TYPE = 'BDACHTP')
OR (commoncombinedview.TYPE = 'BDACHTP')
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'USACH'
AND commoncombinedview.FUNCTION = 'BATCH'
)
OR ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND ( (commoncombinedview.TYPE = 'BDACHCC')
OR (commoncombinedview.TYPE = 'BDACHCC')
OR (commoncombinedview.TYPE = 'BDACHCD')
OR (commoncombinedview.TYPE = 'BDACHCD')
OR (commoncombinedview.TYPE = 'BDACHCEC')
OR (commoncombinedview.TYPE = 'BDACHCEC')
OR (commoncombinedview.TYPE = 'BDACHCP')
OR (commoncombinedview.TYPE = 'BDACHCP')
OR (commoncombinedview.TYPE = 'BDACHCRC')
OR (commoncombinedview.TYPE = 'BDACHCRC')
OR (commoncombinedview.TYPE = 'BDACHCSP')
OR (commoncombinedview.TYPE = 'BDACHCSP')
OR (commoncombinedview.TYPE = 'BDACHCVP')
OR (commoncombinedview.TYPE = 'BDACHCVP')
OR (commoncombinedview.TYPE = 'BDACHP')
OR (commoncombinedview.TYPE = 'BDACHP')
OR (commoncombinedview.TYPE = 'BDACHTP')
OR (commoncombinedview.TYPE = 'BDACHTP')
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'USACH'
AND commoncombinedview.FUNCTION = 'BHTMPL'
)
)
AND (commoncombinedview.tnum = commongridcolsrtgsandach.tnum)
AND (commoncombinedview.tnum = consolidatedcombinedview.tnum)
AND (commoncombinedview.tnum = commoncombinedview_cust.tnum)
ORDER BY commongridcolsrtgsandach.batchtnum DESC
Modifed one --> i am taking out the select of the 1 view(commoncombinedview) instead of calling the view, and it gets the result in 2 secs,
It's not buffered or in memory, becuase the modified one i ran first still same.
-- Replaced view with the SQL from the view
SELECT commongridcolsrtgsandach.batchtnum, commoncombinedview.itype,
consolidatedcombinedview.cmb_bene_name,
commoncombinedview.actionlist,
consolidatedcombinedview.cmb_credit_amount,
consolidatedcombinedview.cmb_credit_currency,
consolidatedcombinedview.cmb_debit_account_number,
consolidatedcombinedview.cmb_value_date,
commoncombinedview.methoddesc,
consolidatedcombinedview.cmb_template_code,
commoncombinedview.status_description,
commoncombinedview.updatecount__, commoncombinedview.statushovertext,
commoncombinedview.FUNCTION,
commoncombinedview_cust.paymentmethod_ll,
commoncombinedview_cust.paymentdescription_ll,
commoncombinedview.accounthovertext,
consolidatedcombinedview.cmb_debit_account_name,
commoncombinedview.product, commoncombinedview.TYPE,
commoncombinedview.digest, commoncombinedview.digestauditid,
commoncombinedview_cust.templatechangedflag,
commoncombinedview.ismodified,
consolidatedcombinedview.cmb_type_description,
commoncombinedview_cust.template_auto_release,
commoncombinedview_cust.template_auto_approve,
commongridcolsrtgsandach.bankname,
commongridcolsrtgsandach.debit_bank_id,
commongridcolsrtgsandach.debit_branch_name,
commongridcolsrtgsandach.origcompname,
commongridcolsrtgsandach.origcompid, commoncombinedview.tnum,
commoncombinedview.cutoff_time,
commongridcolsrtgsandach.bene_account_type,
consolidatedcombinedview.cmb_bene_id,
commongridcolsrtgsandach.bene_address_1,
commongridcolsrtgsandach.bene_address_2,
commongridcolsrtgsandach.bene_address_3,
commongridcolsrtgsandach.bene_country,
commongridcolsrtgsandach.bene_bank_type,
consolidatedcombinedview.cmb_bene_bank_id,
consolidatedcombinedview.cmb_bene_bank_name,
consolidatedcombinedview.cmb_bene_account,
commongridcolsrtgsandach.bene_bank_address_1,
commongridcolsrtgsandach.bene_bank_address_2,
commongridcolsrtgsandach.bene_bank_address_3,
commongridcolsrtgsandach.bene_bank_country,
commongridcolsrtgsandach.correspondent_type,
commongridcolsrtgsandach.correspondent_id,
commongridcolsrtgsandach.correspondent_name,
commongridcolsrtgsandach.correspondent_address_1,
commongridcolsrtgsandach.correspondent_address_2,
commongridcolsrtgsandach.correspondent_address_3,
commongridcolsrtgsandach.correspondent_country,
consolidatedcombinedview.cmb_originator_id,
consolidatedcombinedview.cmb_originator_name,
commongridcolsrtgsandach.originator_address_1,
commongridcolsrtgsandach.originator_address_2,
commongridcolsrtgsandach.originator_address_3,
commongridcolsrtgsandach.originator_country,
commoncombinedview.entered_timestamp, commoncombinedview.entered_by,
commoncombinedview.approved_by_1,
consolidatedcombinedview.cmb_template_description
FROM websystemq4.commongridcolsrtgsandach,
(
-- BEGIN: websystemq4.commoncombinedview
SELECT tnum, accountfilter, approved_by_1, approved_by_2, approved_by_3, approved_by_4, approved_by_5, approved_by_6, approved_by_name_1, approved_by_name_2,
approved_by_name_3, approved_by_name_4, approved_by_name_5, approved_by_name_6, approved_by_panelcode_1, approved_by_panelcode_2,
approved_by_panelcode_3, approved_by_panelcode_4, approved_by_panelcode_5, approved_by_panelcode_6, approved_timestamp_1, approved_timestamp_2,
approved_timestamp_3, approved_timestamp_4, approved_timestamp_5, approved_timestamp_6, approved_usergroup_1, approved_usergroup_2,
approved_usergroup_3, approved_usergroup_4, approved_usergroup_5, approved_usergroup_6, approver_rejection_by, approver_rejection_name,
approver_rejection_reason, approver_rejection_timestamp, approver_rejection_usergroup, batchtypedesc, bene_accountentitlement, canceled_by,
canceled_by_name, canceled_timestamp, canceled_usergroup, clientcomp_groupnum, clientcomp_name, credit_currency, cutoff_time, customer_reference,
debit_currency, deleted_by, deleted_by_name, deleted_timestamp, deleted_usergroup, effectivedate, entered_by, entered_by_name, entered_timestamp,
entrymethod, extended_status, extract_timestamp, FUNCTION, importid, last_action_time, macfield, modified_by, modified_by_name, modified_timestamp,
modified_usergroup, number_of_signatures, paymentscommon.parentusergroup, product, productdesc, rejection_by, rejection_reason, rejection_timestamp,
rejection_usergroup, released_by, released_by_name, released_timestamp, released_usergroup, repaired_by, repaired_by_name, repaired_timestamp,
repaired_usergroup, paymentscommon.status, status_description, TO_NCHAR (status_description_ll), templatecodeentitlement, tran_date, TYPE,
TO_NCHAR (type_ll), unapproved_by, unapproved_by_name, unapproved_timestamp, unapproved_usergroup, paymentscommon.updatecount__,
paymentscommon.usergroup, unreleased_by, unreleased_by_name, unreleased_timestamp, unreleased_usergroup, credit_amount, createdfrom,
debit_account_number, debit_amount, entrytype, imported_by, imported_usergroup, totalamtcredit, totalamtdebit, entryclass, finalupdate, value_date,
NULL AS activated_by, NULL AS activated_by_name, TO_DATE (NULL) AS activated_timestamp, NULL AS activated_usergroup, NULL AS last_used,
NULL AS last_used_by, NULL AS last_used_by_name, NULL AS last_used_usergroup, NULL AS deactivated_by, NULL AS deactivated_by_name,
TO_DATE (NULL) AS deactivated_timestamp, NULL AS deactivated_usergroup, users.userid AS currentuserid, users.usergroup AS currentusergroup,
'P' AS itype,
websystemq4.getavailableactionlist (users.usergroup,
users.userid,
paymentscommon.product,
paymentscommon.FUNCTION,
paymentscommon.TYPE,
paymentscommon.entrymethod,
paymentscommon.accountfilter,
paymentscommon.clientcomp_groupnum,
paymentscommon.status,
paymentscommon.number_of_signatures,
paymentscommon.extractstatus,
paymentscommon.entered_by,
paymentscommon.modified_by,
'INBOX'
) AS actionlist,
'TNum='
|| paymentscommon.tnum
|| '!_product='
|| paymentscommon.product
|| '!_function='
|| paymentscommon.FUNCTION
|| '!_type='
|| paymentscommon.TYPE AS rowidentifier,
websystemq4.getauditforstatushover ((SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommon.usergroup AND userid = paymentscommon.entered_by),
(SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommon.modified_usergroup AND userid = paymentscommon.modified_by),
(SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommon.approved_usergroup_1 AND userid = paymentscommon.approved_by_1),
(SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommon.approver_rejection_usergroup AND userid = paymentscommon.approver_rejection_by),
(SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommon.released_usergroup AND userid = paymentscommon.released_by),
(SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommon.unreleased_usergroup AND userid = paymentscommon.unreleased_by),
NULL,
NULL
) AS statushovertext,
paymentscommon.extractstatus,
DECODE ((SELECT paymentdescription_ll
FROM websystemq4.paymentscommon_cust
WHERE paymentscommon_cust.tnum = paymentscommon.tnum),
NULL, (SELECT paymentmethod_ll
FROM websystemq4.paymentscommon_cust
WHERE paymentscommon_cust.tnum = paymentscommon.tnum),
(SELECT paymentmethod_ll || ' / ' || paymentdescription_ll
FROM websystemq4.paymentscommon_cust
WHERE paymentscommon_cust.tnum = paymentscommon.tnum)
) AS methoddesc,
websystemq4.getauditforaccounthover ((SELECT cmb_debit_account_number
FROM websystemq4.paymentsconsolidated
WHERE paymentsconsolidated.tnum = paymentscommon.tnum),
(SELECT cmb_debit_account_name
FROM websystemq4.paymentsconsolidated
WHERE paymentsconsolidated.tnum = paymentscommon.tnum)
) AS accounthovertext,
paymentscommon.digest, paymentscommon.digestauditid, websystemq4.get_istmpmodified (paymentscommon.tnum) AS ismodified
FROM websystemq4.paymentscommon paymentscommon, websystemq4.users
WHERE paymentscommon.parentusergroup = users.parentusergroup
UNION
SELECT tnum, accountfilter, approved_by_1, approved_by_2, approved_by_3, approved_by_4, approved_by_5, approved_by_6, approved_by_name_1, approved_by_name_2,
approved_by_name_3, approved_by_name_4, approved_by_name_5, approved_by_name_6, NULL AS approved_by_panelcode_1, NULL AS approved_by_panelcode_2,
NULL AS approved_by_panelcode_3, NULL AS approved_by_panelcode_4, NULL AS approved_by_panelcode_5, NULL AS approved_by_panelcode_6,
approved_timestamp_1, approved_timestamp_2, approved_timestamp_3, approved_timestamp_4, approved_timestamp_5, approved_timestamp_6,
approved_usergroup_1, approved_usergroup_2, approved_usergroup_3, approved_usergroup_4, approved_usergroup_5, approved_usergroup_6,
approver_rejection_by, approver_rejection_name, approver_rejection_reason, approver_rejection_timestamp, approver_rejection_usergroup,
NULL AS batchtypedesc, NULL AS bene_accountentitlement, NULL AS canceled_by, NULL AS canceled_by_name, NULL AS canceled_timestamp,
NULL AS canceled_usergroup, clientcomp_groupnum, NULL AS clientcomp_name, NULL AS credit_currency, NULL AS cutoff_time, customer_reference,
NULL AS debit_currency, deleted_by, deleted_by_name, deleted_timestamp, deleted_usergroup, NULL AS effectivedate, entered_by, entered_by_name,
entered_timestamp, entrymethod, NULL AS extended_status, TO_DATE (NULL) AS extract_timestamp, FUNCTION, importid, TO_DATE (NULL) AS last_action_time,
macfield, modified_by, modified_by_name, modified_timestamp, modified_usergroup, number_of_signatures, paymentscommontemplate.parentusergroup,
product, productdesc, rejection_by, rejection_reason, rejection_timestamp, rejection_usergroup, NULL AS released_by, NULL AS released_by_name,
TO_DATE (NULL) asreleased_timestamp, NULL AS released_usergroup, repaired_by, repaired_by_name, repaired_timestamp, repaired_usergroup,
paymentscommontemplate.status, status_description, TO_NCHAR (status_description_ll), templatecodeentitlement, TO_DATE (NULL) AS tran_date, TYPE,
TO_NCHAR (type_ll), unapproved_by, unapproved_by_name, unapproved_timestamp, unapproved_usergroup, paymentscommontemplate.updatecount__,
paymentscommontemplate.usergroup, NULL AS unreleased_by, NULL AS unreleased_by_name, TO_DATE (NULL) AS unreleased_timestamp,
NULL AS unreleased_usergroup, NULL AS credit_amount, NULL AS createdfrom, NULL AS debit_account_number, NULL AS debit_amount, NULL AS entrytype,
NULL AS imported_by, NULL AS imported_usergroup, NULL AS totalamtcredit, NULL AS totalamtdebit, NULL AS entryclass, finalupdate,
TO_DATE (NULL) AS value_date, activated_by, activated_by_name, activated_timestamp, activated_usergroup, last_used, last_used_by, last_used_by_name,
last_used_usergroup, deactivated_by, deactivated_by_name, deactivated_timestamp, deactivated_usergroup, users.userid AS currentuserid,
users.usergroup AS currentusergroup, 'T' AS itype,
websystemq4.getavailableactionlist (users.usergroup,
users.userid,
paymentscommontemplate.product,
paymentscommontemplate.FUNCTION,
paymentscommontemplate.TYPE,
paymentscommontemplate.entrymethod,
paymentscommontemplate.accountfilter,
paymentscommontemplate.clientcomp_groupnum,
paymentscommontemplate.status,
paymentscommontemplate.number_of_signatures,
paymentscommontemplate.extractstatus,
paymentscommontemplate.entered_by,
paymentscommontemplate.modified_by,
'INBOX'
) AS actionlist,
'TNum='
|| paymentscommontemplate.tnum
|| '!_product='
|| paymentscommontemplate.product
|| '!_function='
|| paymentscommontemplate.FUNCTION
|| '!_type='
|| paymentscommontemplate.TYPE AS rowidentifier,
websystemq4.getauditforstatushover ((SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommontemplate.usergroup AND userid = paymentscommontemplate.entered_by),
(SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommontemplate.modified_usergroup AND userid = paymentscommontemplate.modified_by),
(SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommontemplate.approved_usergroup_1 AND userid = paymentscommontemplate.approved_by_1),
(SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommontemplate.approver_rejection_usergroup
AND userid = paymentscommontemplate.approver_rejection_by),
NULL,
NULL,
(SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommontemplate.activated_usergroup AND userid = paymentscommontemplate.activated_by),
(SELECT jpmcuserid
FROM websystemq4.users_cust
WHERE usergroup = paymentscommontemplate.deactivated_usergroup AND userid = paymentscommontemplate.deactivated_by)
) AS statushovertext,
'',
DECODE ((SELECT paymentdescription_ll
FROM websystemq4.paymentscommontemplate_cust
WHERE paymentscommontemplate_cust.tnum = paymentscommontemplate.tnum),
NULL, (SELECT paymentmethod_ll
FROM websystemq4.paymentscommontemplate_cust
WHERE paymentscommontemplate_cust.tnum = paymentscommontemplate.tnum),
(SELECT paymentmethod_ll || ' / ' || paymentdescription_ll
FROM websystemq4.paymentscommontemplate_cust
WHERE paymentscommontemplate_cust.tnum = paymentscommontemplate.tnum)
) AS methoddesc,
websystemq4.getauditforaccounthover ((SELECT cmb_debit_account_number
FROM websystemq4.paymentsconsolidatedtemplate
WHERE paymentsconsolidatedtemplate.tnum = paymentscommontemplate.tnum),
(SELECT cmb_debit_account_name
FROM websystemq4.paymentsconsolidatedtemplate
WHERE paymentsconsolidatedtemplate.tnum = paymentscommontemplate.tnum)
) AS accounthovertext,
paymentscommontemplate.digest, paymentscommontemplate.digestauditid, 'N'
FROM websystemq4.paymentscommontemplate, websystemq4.users
WHERE paymentscommontemplate.parentusergroup = users.parentusergroup
-- END: websystemq4.commoncombinedview commoncombinedview
) commoncombinedview,
websystemq4.consolidatedcombinedview,
websystemq4.commoncombinedview_cust
WHERE ( ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND (websystemq4.actionmodeavailablecheck ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod,
commoncombinedview.accountfilter,
NULL,
'SELECT'
) != 0
)
AND ( ( commoncombinedview.TYPE = 'BOOK'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'BOOK'
AND commoncombinedview.entrymethod = 1
)
OR ( commoncombinedview.TYPE = 'INTL'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'INTL'
AND commoncombinedview.entrymethod = 1
)
OR ( commoncombinedview.TYPE = 'TRANSFER'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'TRANSFER'
AND commoncombinedview.entrymethod = 1
)
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'RTGS'
AND commoncombinedview.FUNCTION = 'INST'
)
OR ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND (websystemq4.actionmodeavailablecheck ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod,
commoncombinedview.accountfilter,
NULL,
'SELECT'
) != 0
)
AND ( ( commoncombinedview.TYPE = 'BOOK'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'BOOK'
AND commoncombinedview.entrymethod = 1
)
OR ( commoncombinedview.TYPE = 'INTL'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'INTL'
AND commoncombinedview.entrymethod = 1
)
OR ( commoncombinedview.TYPE = 'TRANSFER'
AND commoncombinedview.entrymethod = 0
)
OR ( commoncombinedview.TYPE = 'TRANSFER'
AND commoncombinedview.entrymethod = 1
)
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'RTGS'
AND commoncombinedview.FUNCTION = 'TMPL'
)
OR ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND ( (commoncombinedview.TYPE = 'SACHCC')
OR (commoncombinedview.TYPE = 'SACHCC')
OR (commoncombinedview.TYPE = 'SACHCD')
OR (commoncombinedview.TYPE = 'SACHCD')
OR (commoncombinedview.TYPE = 'SACHCEC')
OR (commoncombinedview.TYPE = 'SACHCEC')
OR (commoncombinedview.TYPE = 'SACHCP')
OR (commoncombinedview.TYPE = 'SACHCP')
OR (commoncombinedview.TYPE = 'SACHCRC')
OR (commoncombinedview.TYPE = 'SACHCRC')
OR (commoncombinedview.TYPE = 'SACHCSP')
OR (commoncombinedview.TYPE = 'SACHCSP')
OR (commoncombinedview.TYPE = 'SACHCVP')
OR (commoncombinedview.TYPE = 'SACHCVP')
OR (commoncombinedview.TYPE = 'SACHP')
OR (commoncombinedview.TYPE = 'SACHP')
OR (commoncombinedview.TYPE = 'SACHTP')
OR (commoncombinedview.TYPE = 'SACHTP')
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'USACH'
AND commoncombinedview.FUNCTION = 'INST'
)
OR ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND ( (commoncombinedview.TYPE = 'SACHCC')
OR (commoncombinedview.TYPE = 'SACHCC')
OR (commoncombinedview.TYPE = 'SACHCD')
OR (commoncombinedview.TYPE = 'SACHCD')
OR (commoncombinedview.TYPE = 'SACHCEC')
OR (commoncombinedview.TYPE = 'SACHCEC')
OR (commoncombinedview.TYPE = 'SACHCP')
OR (commoncombinedview.TYPE = 'SACHCP')
OR (commoncombinedview.TYPE = 'SACHCRC')
OR (commoncombinedview.TYPE = 'SACHCRC')
OR (commoncombinedview.TYPE = 'SACHCSP')
OR (commoncombinedview.TYPE = 'SACHCSP')
OR (commoncombinedview.TYPE = 'SACHCVP')
OR (commoncombinedview.TYPE = 'SACHCVP')
OR (commoncombinedview.TYPE = 'SACHP')
OR (commoncombinedview.TYPE = 'SACHP')
OR (commoncombinedview.TYPE = 'SACHTP')
OR (commoncombinedview.TYPE = 'SACHTP')
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'USACH'
AND commoncombinedview.FUNCTION = 'TMPL'
)
OR ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND ( (commoncombinedview.TYPE = 'BDACHCC')
OR (commoncombinedview.TYPE = 'BDACHCC')
OR (commoncombinedview.TYPE = 'BDACHCD')
OR (commoncombinedview.TYPE = 'BDACHCD')
OR (commoncombinedview.TYPE = 'BDACHCEC')
OR (commoncombinedview.TYPE = 'BDACHCEC')
OR (commoncombinedview.TYPE = 'BDACHCP')
OR (commoncombinedview.TYPE = 'BDACHCP')
OR (commoncombinedview.TYPE = 'BDACHCRC')
OR (commoncombinedview.TYPE = 'BDACHCRC')
OR (commoncombinedview.TYPE = 'BDACHCSP')
OR (commoncombinedview.TYPE = 'BDACHCSP')
OR (commoncombinedview.TYPE = 'BDACHCVP')
OR (commoncombinedview.TYPE = 'BDACHCVP')
OR (commoncombinedview.TYPE = 'BDACHP')
OR (commoncombinedview.TYPE = 'BDACHP')
OR (commoncombinedview.TYPE = 'BDACHTP')
OR (commoncombinedview.TYPE = 'BDACHTP')
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'USACH'
AND commoncombinedview.FUNCTION = 'BATCH'
)
OR ( ( ( (commoncombinedview.status = 'AP')
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by = '10001269'
AND websystemq4.hasapproveown ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
OR ( commoncombinedview.status = 'EN'
AND commoncombinedview.entered_by <> '10001269'
)
OR ( commoncombinedview.status = 'AR'
AND commoncombinedview.entered_by = '10001269'
AND commoncombinedview.approver_rejection_by <>
'10001269'
)
OR ( commoncombinedview.status = 'IC'
AND commoncombinedview.entered_by = '10001269'
)
)
AND ( commoncombinedview.currentuserid = '10001269'
AND commoncombinedview.currentusergroup = '10000182'
)
)
AND (websystemq4.hasotheractions ('10000182',
'10001269',
commoncombinedview.product,
commoncombinedview.FUNCTION,
commoncombinedview.TYPE,
commoncombinedview.entrymethod
) = 1
)
AND ( (commoncombinedview.TYPE = 'BDACHCC')
OR (commoncombinedview.TYPE = 'BDACHCC')
OR (commoncombinedview.TYPE = 'BDACHCD')
OR (commoncombinedview.TYPE = 'BDACHCD')
OR (commoncombinedview.TYPE = 'BDACHCEC')
OR (commoncombinedview.TYPE = 'BDACHCEC')
OR (commoncombinedview.TYPE = 'BDACHCP')
OR (commoncombinedview.TYPE = 'BDACHCP')
OR (commoncombinedview.TYPE = 'BDACHCRC')
OR (commoncombinedview.TYPE = 'BDACHCRC')
OR (commoncombinedview.TYPE = 'BDACHCSP')
OR (commoncombinedview.TYPE = 'BDACHCSP')
OR (commoncombinedview.TYPE = 'BDACHCVP')
OR (commoncombinedview.TYPE = 'BDACHCVP')
OR (commoncombinedview.TYPE = 'BDACHP')
OR (commoncombinedview.TYPE = 'BDACHP')
OR (commoncombinedview.TYPE = 'BDACHTP')
OR (commoncombinedview.TYPE = 'BDACHTP')
)
AND commoncombinedview.parentusergroup = '10000182'
AND commoncombinedview.product = 'USACH'
AND commoncombinedview.FUNCTION = 'BHTMPL'
)
)
AND (commoncombinedview.tnum = commongridcolsrtgsandach.tnum)
AND (commoncombinedview.tnum = consolidatedcombinedview.tnum)
AND (commoncombinedview.tnum = commoncombinedview_cust.tnum)
ORDER BY commongridcolsrtgsandach.batchtnum DESC
Those are all bind variables, I have just coded the values to get the output,
Is this some thing noticed behaviour in 11g or What ?
What else could be the reason.
If you experts want the explain plan i can paste that also.
Any suggestions Please guide .
Thanks
|
|
|
|
| Re: Query With View Vs Select of the View behave differently [message #440213 is a reply to message #440208] |
Thu, 21 January 2010 11:53  |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So you've replaced one of the views in the query with an inline view that does the same select as the view?
If you want us to stand any chance of working out what's going we're going to need the explain plans.
No-one is going to read a select that big and try and work out what it's doing.
We don't know what tables you have.
We don't know what the relationships between the tables are.
We don't know what the view definitions are.
And it looks like you're calling functions in there - we know nothing about them either.
Run a sql trace for the query, that'll help identify if the functions are part of the problem.
Posting the view definitions as well would probably be usefull.
All that said, I suspect you're coming across the general problem that happens when you combine multiple complex views - oracle can struggle to work out the best plan, possibly because it can't work out how to merge them properly.
Have you tried writing an equivalent query against the actual underlying tables and bypass all the views?
I'd also rewrite the blocks of code like this:
AND ( (commoncombinedview.TYPE = 'BDACHCC')
OR (commoncombinedview.TYPE = 'BDACHCC')
OR (commoncombinedview.TYPE = 'BDACHCD')
OR (commoncombinedview.TYPE = 'BDACHCD')
OR (commoncombinedview.TYPE = 'BDACHCEC')
OR (commoncombinedview.TYPE = 'BDACHCEC')
OR (commoncombinedview.TYPE = 'BDACHCP')
As in lists, for clarity if nothing else.
|
|
|
|
| Re: Query With View Vs Select of the View behave differently [message #440219 is a reply to message #440213] |
Thu, 21 January 2010 12:36 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks for Looking into it,
Yes there are function calls, and i have tested them , they run in less than milli seconds, so that's why i am not sending that code here.
Please find the sql Trace for both original one and the modified one, I have removed the SQL Query from trace file output pasted below just to reduce the contents..
Sorry if the format is not ok for these trace..
Original One with all Views
TKPROF: Release 11.1.0.7.0 - Production on Thu Jan 21 12:52:08 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: apyt01q2_ora_1601684.trc
Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.21 0.23 0 0 0 0
Fetch 2 254.67 251.53 26848 29390 17 15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 254.89 251.78 26848 29390 17 15
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 93 (DEVL_MONITOR)
Rows Row Source Operation
------- ---------------------------------------------------
15 SORT ORDER BY (cr=7840886 pr=26848 pw=26848 time=0 us cost=3223551074 size=11467008689968 card=1210110668)
15 HASH JOIN (cr=7840886 pr=26848 pw=26848 time=65581 us cost=187569 size=11467008689968 card=1210110668)
3554 VIEW COMMONCOMBINEDVIEW_CUST (cr=54 pr=0 pw=0 time=44 us cost=20 size=953157 card=3441)
3554 SORT UNIQUE (cr=54 pr=0 pw=0 time=36 us cost=20 size=234625 card=3441)
3554 UNION-ALL (cr=54 pr=0 pw=0 time=43 us)
2587 TABLE ACCESS FULL PAYMENTSCOMMON_CUST (cr=38 pr=0 pw=0 time=20 us cost=12 size=187725 card=2503)
967 TABLE ACCESS FULL PAYMENTSCOMMONTEMPLATE_CUST (cr=16 pr=0 pw=0 time=3 us cost=6 size=46900 card=938)
15 HASH JOIN (cr=7840832 pr=26848 pw=26848 time=65576 us cost=181584 size=323505032187 card=35167413)
3554 VIEW COMMONGRIDCOLSRTGSANDACH (cr=315 pr=0 pw=0 time=55 us cost=93 size=1469307 card=3441)
3554 SORT UNIQUE (cr=315 pr=0 pw=0 time=46 us cost=93 size=420179 card=3441)
3554 UNION-ALL (cr=315 pr=0 pw=0 time=173 us)
1711 HASH JOIN (cr=178 pr=0 pw=0 time=45 us cost=44 size=302484 card=1662)
1711 TABLE ACCESS FULL RTGS_CUST (cr=23 pr=0 pw=0 time=4 us cost=8 size=66480 card=1662)
1711 TABLE ACCESS FULL RTGS (cr=155 pr=0 pw=0 time=27 us cost=35 size=236004 card=1662)
876 TABLE ACCESS FULL ACHUSHEADER (cr=53 pr=0 pw=0 time=5 us cost=16 size=21025 card=841)
540 HASH JOIN (cr=53 pr=0 pw=0 time=11 us cost=19 size=86295 card=523)
540 TABLE ACCESS FULL RTGSTEMPLATE_CUST (cr=7 pr=0 pw=0 time=2 us cost=4 size=18305 card=523)
540 TABLE ACCESS FULL RTGSTEMPLATE (cr=46 pr=0 pw=0 time=6 us cost=14 size=67990 card=523)
427 TABLE ACCESS FULL ACHUSHEADERTEMPLATE (cr=31 pr=0 pw=0 time=8 us cost=10 size=10375 card=415)
15 NESTED LOOPS (cr=7840517 pr=26848 pw=26848 time=65572 us cost=181317 size=8965080492 card=1022011)
15 VIEW COMMONCOMBINEDVIEW (cr=7840457 pr=26848 pw=26848 time=65500 us cost=3065 size=251834779 card=29701)
221124 SORT UNIQUE (cr=7839347 pr=26848 pw=26848 time=30876 us cost=3065 size=13762859 card=29701)
221124 UNION-ALL (cr=7839347 pr=0 pw=0 time=2343505 us)
249 TABLE ACCESS FULL USERS_CUST (cr=4048 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
80 TABLE ACCESS FULL USERS_CUST (cr=1344 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
132 TABLE ACCESS FULL USERS_CUST (cr=2160 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
41 TABLE ACCESS FULL USERS_CUST (cr=688 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
79 TABLE ACCESS FULL USERS_CUST (cr=1328 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
1660 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=2235 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
1660 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=575 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
1126 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=1601 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
1126 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=475 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
534 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=833 pr=0 pw=0 time=0 us cost=2 size=33 card=1)
534 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=299 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
1660 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATED (cr=2237 pr=0 pw=0 time=0 us cost=2 size=20 card=1)
1660 INDEX UNIQUE SCAN PK_PAYMENTSCON (cr=577 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86789)
1660 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATED (cr=2239 pr=0 pw=0 time=0 us cost=2 size=14 card=1)
1660 INDEX UNIQUE SCAN PK_PAYMENTSCON (cr=579 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86789)
161516 HASH JOIN (cr=384 pr=0 pw=0 time=1288 us cost=112 size=10694475 card=21605)
662 TABLE ACCESS FULL USERS (cr=23 pr=0 pw=0 time=1 us cost=8 size=17712 card=656)
2587 TABLE ACCESS FULL PAYMENTSCOMMON (cr=361 pr=0 pw=0 time=72 us cost=103 size=1171404 card=2503)
95 TABLE ACCESS FULL USERS_CUST (cr=1536 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
55 TABLE ACCESS FULL USERS_CUST (cr=912 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
58 TABLE ACCESS FULL USERS_CUST (cr=3056 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
13 TABLE ACCESS FULL USERS_CUST (cr=224 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
44 TABLE ACCESS FULL USERS_CUST (cr=736 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
4 TABLE ACCESS FULL USERS_CUST (cr=96 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
611 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=789 pr=0 pw=0 time=0 us cost=2 size=18 card=1)
611 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=178 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
356 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=497 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
356 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=141 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
255 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=365 pr=0 pw=0 time=0 us cost=2 size=36 card=1)
255 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=110 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
611 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATEDTEMPLATE (cr=782 pr=0 pw=0 time=0 us cost=2 size=20 card=1)
611 INDEX UNIQUE SCAN PK_PAYMENTSCONTEMPLATE (cr=171 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86790)
611 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATEDTEMPLATE (cr=770 pr=0 pw=0 time=0 us cost=2 size=12 card=1)
611 INDEX UNIQUE SCAN PK_PAYMENTSCONTEMPLATE (cr=159 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86790)
59608 HASH JOIN (cr=85 pr=0 pw=0 time=496 us cost=27 size=3068384 card=8096)
662 TABLE ACCESS FULL USERS (cr=24 pr=0 pw=0 time=0 us cost=8 size=17712 card=656)
967 TABLE ACCESS FULL PAYMENTSCOMMONTEMPLATE (cr=61 pr=0 pw=0 time=40 us cost=18 size=330176 card=938)
15 VIEW CONSOLIDATEDCOMBINEDVIEW (cr=60 pr=0 pw=0 time=0 us cost=6 size=293 card=1)
15 SORT UNIQUE (cr=60 pr=0 pw=0 time=0 us cost=6 size=263 card=2)
15 UNION ALL PUSHED PREDICATE (cr=60 pr=0 pw=0 time=0 us)
13 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATED (cr=36 pr=0 pw=0 time=0 us cost=2 size=134 card=1)
13 INDEX UNIQUE SCAN PK_PAYMENTSCON (cr=23 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86789)
2 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATEDTEMPLATE (cr=24 pr=0 pw=0 time=0 us cost=2 size=129 card=1)
2 INDEX UNIQUE SCAN PK_PAYMENTSCONTEMPLATE (cr=22 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86790)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
15 SORT (ORDER BY)
15 HASH JOIN
3554 VIEW OF 'COMMONCOMBINEDVIEW_CUST' (VIEW)
3554 SORT (UNIQUE)
3554 UNION-ALL
2587 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCOMMON_CUST' (TABLE)
967 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCOMMONTEMPLATE_CUST' (TABLE)
15 HASH JOIN
3554 NESTED LOOPS
3554 VIEW OF 'COMMONCOMBINEDVIEW' (VIEW)
3554 SORT (UNIQUE)
1711 UNION-ALL
1711 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
1711 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
876 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
540 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
540 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
540 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
427 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMON_CUST' (TABLE)
15 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMMON_CUST' (INDEX (UNIQUE))
15 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMON_CUST' (TABLE)
221124 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMMON_CUST' (INDEX (UNIQUE))
221124 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMON_CUST' (TABLE)
249 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMMON_CUST' (INDEX (UNIQUE))
80 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCONSOLIDATED' (TABLE)
132 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCON' (INDEX (UNIQUE))
41 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCONSOLIDATED' (TABLE)
79 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCON' (INDEX (UNIQUE))
0 HASH JOIN
1660 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'USERS' (TABLE)
1660 BITMAP CONVERSION (TO ROWIDS)
1126 BITMAP OR
1126 BITMAP CONVERSION (FROM ROWIDS)
534 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
534 BITMAP CONVERSION (FROM ROWIDS)
1660 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1660 BITMAP CONVERSION (FROM ROWIDS)
1660 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1660 BITMAP CONVERSION (FROM ROWIDS)
161516 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
662 BITMAP CONVERSION (FROM ROWIDS)
2587 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
95 BITMAP CONVERSION (FROM ROWIDS)
55 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
58 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCOMMON' (TABLE)
13 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
44 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
4 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
611 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
611 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
356 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
356 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMONTEMPLATE_CUST' (TABLE)
255 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMTEMP_CUST' (INDEX (UNIQUE))
255 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMONTEMPLATE_CUST' (TABLE)
611 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMTEMP_CUST' (INDEX (UNIQUE))
611 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMONTEMPLATE_CUST' (TABLE)
611 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMTEMP_CUST' (INDEX (UNIQUE))
611 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCONSOLIDATEDTEMPLATE' (TABLE)
59608 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCONTEMPLATE' (INDEX (UNIQUE))
662 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCONSOLIDATEDTEMPLATE' (TABLE)
967 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCONTEMPLATE' (INDEX (UNIQUE))
15 HASH JOIN
15 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'USERS' (TABLE)
15 BITMAP CONVERSION (TO ROWIDS)
13 BITMAP OR
13 BITMAP CONVERSION (FROM ROWIDS)
2 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
2 BITMAP CONVERSION (FROM ROWIDS)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
0 BITMAP CONVERSION (FROM ROWIDS)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
0 BITMAP CONVERSION (FROM ROWIDS)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
0 BITMAP CONVERSION (FROM ROWIDS)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
0 BITMAP CONVERSION (FROM ROWIDS)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCOMMONTEMPLATE' (TABLE)
0 VIEW OF 'COMMONGRIDCOLSRTGSANDACH' (VIEW)
0 SORT (UNIQUE)
0 UNION ALL PUSHED PREDICATE
0 NESTED LOOPS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'RTGS' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_RTGS' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'RTGS_CUST' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_RTGS_CUST' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ACHUSHEADER' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_ACHUSHEADER' (INDEX (UNIQUE))
0 NESTED LOOPS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'RTGSTEMPLATE' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_RTGSTEMPLATE' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'RTGSTEMPLATE_CUST' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_RTGSTEMPLATE_CUST' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ACHUSHEADERTEMPLATE' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_ACHUSHEADTEMP' (INDEX (UNIQUE))
0 VIEW OF 'CONSOLIDATEDCOMBINEDVIEW' (VIEW)
0 SORT (UNIQUE)
0 UNION-ALL
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCONSOLIDATED' (TABLE)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCONSOLIDATEDTEMPLATE' (TABLE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net more data to client 4 0.00 0.00
gc cr block 2-way 4 0.00 0.00
direct path write temp 215 0.03 0.95
direct path read temp 1359 0.04 2.06
SQL*Net message from client 2 72.90 72.91
********************************************************************************
SQL ID: 89mmj5cv07zq2
Plan Hash: 2463789682
SELECT ACTIONMODE, '['||ACTIONMODE||',
{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING
FROM
ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND
PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD
= :B2 AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,
{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM
ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND
PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND
ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION
SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS
ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID =
:B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND
ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION
SELECT 'COPYNEW', '[COPYNEW,{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS
ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID =
:B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND
ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 ORDER BY
1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 143445 2.03 3.45 0 0 0 0
Fetch 561811 7.80 14.02 0 1963683 0 418366
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 705257 9.83 17.48 0 1963683 0 418366
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
6 SORT UNIQUE (cr=15 pr=0 pw=0 time=0 us cost=16 size=264 card=4)
6 UNION-ALL (cr=15 pr=0 pw=0 time=3 us)
5 INDEX RANGE SCAN ACCOUNTBYACTIONCROSSREFPK (cr=4 pr=0 pw=0 time=1 us cost=3 size=66 card=1)(object id 86754)
1 INDEX RANGE SCAN ACCOUNTBYACTIONCROSSREFPK (cr=3 pr=0 pw=0 time=0 us cost=3 size=66 card=1)(object id 86754)
0 INDEX RANGE SCAN ACCOUNTBYACTIONCROSSREFPK (cr=4 pr=0 pw=0 time=0 us cost=3 size=66 card=1)(object id 86754)
0 INDEX RANGE SCAN ACCOUNTBYACTIONCROSSREFPK (cr=4 pr=0 pw=0 time=0 us cost=3 size=66 card=1)(object id 86754)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
6 SORT (UNIQUE)
6 UNION-ALL
5 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACCOUNTBYACTIONCROSSREFPK' (INDEX (UNIQUE))
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACCOUNTBYACTIONCROSSREFPK' (INDEX (UNIQUE))
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACCOUNTBYACTIONCROSSREFPK' (INDEX (UNIQUE))
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACCOUNTBYACTIONCROSSREFPK' (INDEX (UNIQUE))
********************************************************************************
SQL ID: 17ummczq1acb5
Plan Hash: 453089871
SELECT 'Y'
FROM
PAYMENTSCOMMONTEMPLATE P,PAYMENTSCONSOLIDATEDTEMPLATE PCT WHERE P.TNUM=
PCT.TNUM AND PCT.CMB_TEMPLATE_CODE=:B3 AND P.USERGROUP=:B2 AND :B1 <
P.MODIFIED_TIMESTAMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 54630 0.32 0.71 0 0 0 0
Fetch 54630 7.23 11.60 0 1445757 0 4413
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 109261 7.55 12.32 0 1445757 0 4413
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS (cr=33 pr=0 pw=0 time=0 us)
4 NESTED LOOPS (cr=29 pr=0 pw=0 time=42 us cost=9 size=31 card=1)
4 TABLE ACCESS FULL PAYMENTSCONSOLIDATEDTEMPLATE (cr=23 pr=0 pw=0 time=31 us cost=8 size=15 card=1)
4 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP (cr=6 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 86787)
0 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE (cr=4 pr=0 pw=0 time=0 us cost=1 size=16 card=1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 NESTED LOOPS
4 NESTED LOOPS
4 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCONSOLIDATEDTEMPLATE' (TABLE)
4 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_PAYMENTSCOMTEMP'
(INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMONTEMPLATE' (TABLE)
********************************************************************************
SQL ID: 7b5zy50ydpj2t
Plan Hash: 115578582
SELECT ACTIONMODE, '['||ACTIONMODE||',
{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING
FROM
ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND
PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD
= :B2 AND COMPINFO_ID = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,
{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM
ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND
PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND
ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1 UNION
SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS
ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND
ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','BATCH') AND
TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND
COMPINFO_ID = :B1 UNION SELECT 'COPYNEW', '[COPYNEW,
{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS ACTIONSTRING FROM
ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND
PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','TMPL','BHTMPL') AND TYPECODE
= :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1
ORDER BY 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 77670 1.17 1.94 0 0 0 0
Fetch 239955 5.28 10.14 0 2214050 0 162285
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 317626 6.45 12.09 0 2214050 0 162285
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
7 SORT UNIQUE (cr=30 pr=0 pw=0 time=0 us cost=23 size=200 card=4)
7 UNION-ALL (cr=30 pr=0 pw=0 time=6 us)
5 INDEX RANGE SCAN ACHCOMPANYIDBYACTIONCROSSREFPK (cr=4 pr=0 pw=0 time=4 us cost=4 size=50 card=1)(object id 86755)
1 INDEX RANGE SCAN ACHCOMPANYIDBYACTIONCROSSREFPK (cr=5 pr=0 pw=0 time=0 us cost=4 size=50 card=1)(object id 86755)
0 INLIST ITERATOR (cr=8 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN ACHCOMPANYIDBYACTIONCROSSREFPK (cr=8 pr=0 pw=0 time=0 us cost=5 size=50 card=1)(object id 86755)
1 INLIST ITERATOR (cr=13 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN ACHCOMPANYIDBYACTIONCROSSREFPK (cr=13 pr=0 pw=0 time=0 us cost=6 size=50 card=1)(object id 86755)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
7 SORT (UNIQUE)
7 UNION-ALL
5 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACHCOMPANYIDBYACTIONCROSSREFPK' (INDEX (UNIQUE))
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACHCOMPANYIDBYACTIONCROSSREFPK' (INDEX (UNIQUE))
0 INLIST ITERATOR
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACHCOMPANYIDBYACTIONCROSSREFPK' (INDEX (UNIQUE))
1 INLIST ITERATOR
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACHCOMPANYIDBYACTIONCROSSREFPK' (INDEX (UNIQUE))
********************************************************************************
SQL ID: fwaxw4cgz3zpb
Plan Hash: 1479835720
SELECT VALUE1
FROM
RESTRICTIONS WHERE USERGROUP = :B6 AND ROLEID = :B5 AND PRODUCTCODE = :B4
AND FUNCTIONCODE = :B3 AND TYPECODE = :B2 AND ENTRYMETHOD = :B1 AND
RESTRICTIONTYPE = 'ApproveOwn'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 221370 2.15 3.82 0 0 0 0
Fetch 221370 2.03 3.56 0 734047 0 46579
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 442742 4.18 7.39 0 734047 0 46579
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID RESTRICTIONS (cr=4 pr=0 pw=0 time=0 us cost=4 size=59 card=1)
1 INDEX RANGE SCAN PK_RESTRICTIONS (cr=3 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 86522)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'RESTRICTIONS'
(TABLE)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_RESTRICTIONS'
(INDEX (UNIQUE))
********************************************************************************
SQL ID: 9mtwtwhc30pwt
Plan Hash: 4011638367
SELECT USERGROUP
FROM
PAYMENTSCOMMON WHERE TNUM = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 161514 1.16 2.42 0 0 0 0
Fetch 161514 1.38 1.91 0 484542 0 161514
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 323029 2.54 4.33 0 484542 0 161514
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
1 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86785)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMON' (TABLE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_PAYMENTSCOMMON'
(INDEX (UNIQUE))
********************************************************************************
SQL ID: 2v152f2rbsfsu
Plan Hash: 1110141075
SELECT SUBMIT_TIMESTAMP
FROM
PAYMENTSCOMMON_CUST WHERE TNUM = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 161514 1.31 2.32 0 0 0 0
Fetch 161514 1.31 1.68 0 484542 0 161514
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 323029 2.62 4.01 0 484542 0 161514
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=3 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
1 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMON_CUST' (TABLE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMMON_CUST' (INDEX (UNIQUE))
********************************************************************************
SQL ID: c52usnsa7u5rr
Plan Hash: 13739038
SELECT CMB_TEMPLATE_CODE
FROM
PAYMENTSCONSOLIDATED WHERE TNUM = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 161513 1.22 2.28 0 0 0 0
Fetch 161513 1.30 1.67 0 484539 0 161513
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 323027 2.52 3.96 0 484539 0 161513
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATED (cr=3 pr=0 pw=0 time=0 us cost=2 size=8 card=1)
1 INDEX UNIQUE SCAN PK_PAYMENTSCON (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86789)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCONSOLIDATED' (TABLE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_PAYMENTSCON'
(INDEX (UNIQUE))
********************************************************************************
*** 2010-01-21 12:48:56.998
SELECT ACTIONMODE, '['||ACTIONMODE||',{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD = :B2 AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION SELECT 'COPYNEW', '[COPYNEW,{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 16 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 16 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
SQL ID: 1bz3s34qngy4v
Plan Hash: 3014886912
SELECT COUNT(*)
FROM
ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B8 AND ROLEID = :B7 AND
PRODUCTCODE = :B6 AND FUNCTIONCODE = :B5 AND TYPECODE = :B4 AND ENTRYMETHOD
= :B3 AND ACTIONMODE = :B2 AND COMPINFO_ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 8 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 0 0 8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 17 0.00 0.00 0 0 0 8
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN ACHCOMPANYIDBYACTIONCROSSREFPK (cr=0 pr=0 pw=0 time=0 us cost=3 size=50 card=1)(object id 86755)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'ACHCOMPANYIDBYACTIONCROSSREFPK' (INDEX (UNIQUE))
********************************************************************************
SQL ID: dh3a7vvp4fksf
Plan Hash: 1896929326
SELECT COUNT(*)
FROM
ENTITLEMENTS WHERE USERGROUP = :B6 AND ROLEID = :B5 AND PRODUCTCODE = :B4
AND FUNCTIONCODE = :B3 AND TYPECODE = :B2 AND ENTRYMETHOD = :B1 AND
ACTIONMODE NOT IN('SELECT','INBOXP','INBOXT','INBOX')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 27 0.00 0.00 0 0 0 0
Fetch 27 0.00 0.00 0 89 0 27
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 55 0.00 0.00 0 89 0 27
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=4 pr=0 pw=0 time=0 us)
5 INDEX RANGE SCAN PK_ENTITLEMENTS (cr=4 pr=0 pw=0 time=2 us cost=3 size=46 card=1)(object id 86465)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
5 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_ENTITLEMENTS'
(INDEX (UNIQUE))
********************************************************************************
SQL ID: 27k76482xhb85
Plan Hash: 2397851474
SELECT COUNT(*)
FROM
ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B8 AND ROLEID = :B7 AND
PRODUCTCODE = :B6 AND FUNCTIONCODE = :B5 AND TYPECODE = :B4 AND ENTRYMETHOD
= :B3 AND ACTIONMODE = :B2 AND ACCOUNTFILTER = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 11 0.01 0.00 0 0 0 0
Fetch 11 0.00 0.00 0 33 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 0.01 0.00 0 33 0 11
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
1 INDEX UNIQUE SCAN ACCOUNTBYACTIONCROSSREFPK (cr=3 pr=0 pw=0 time=0 us cost=2 size=66 card=1)(object id 86754)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'ACCOUNTBYACTIONCROSSREFPK' (INDEX (UNIQUE))
********************************************************************************
*** 2010-01-21 12:45:34.653
SELECT ACTIONMODE, '['||ACTIONMODE||',{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD = :B2 AND COMPINFO_ID = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1 UNION SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','BATCH') AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1 UNION SELECT 'COPYNEW', '[COPYNEW,{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','TMPL','BHTMPL') AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 34 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 34 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:48:12.932
SELECT ACTIONMODE, '['||ACTIONMODE||',{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD = :B2 AND COMPINFO_ID = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1 UNION SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','BATCH') AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1 UNION SELECT 'COPYNEW', '[COPYNEW,{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','TMPL','BHTMPL') AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 34 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 34 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:45:53.689
SELECT ACTIONMODE, '['||ACTIONMODE||',{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD = :B2 AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION SELECT 'COPYNEW', '[COPYNEW,{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 10 0.00 0.00 0 15 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.00 0.00 0 15 0 9
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
SQL ID: g3176qdxahvv9
Plan Hash: 1546270724
select :"SYS_B_0"
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 93 (DEVL_MONITOR)
Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 FAST DUAL
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.02 0.04
********************************************************************************
*** 2010-01-21 12:47:06.803
SELECT ACTIONMODE, '['||ACTIONMODE||',{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD = :B2 AND COMPINFO_ID = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1 UNION SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','BATCH') AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1 UNION SELECT 'COPYNEW', '[COPYNEW,{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','TMPL','BHTMPL') AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 27 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 27 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:49:37.076
SELECT ACTIONMODE, '['||ACTIONMODE||',{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD = :B2 AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION SELECT 'COPYNEW', '[COPYNEW,{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 12 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.00 0 12 0 7
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:49:33.072
SELECT ACTIONMODE, '['||ACTIONMODE||',{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD = :B2 AND COMPINFO_ID = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1 UNION SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','BATCH') AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1 UNION SELECT 'COPYNEW', '[COPYNEW,{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','TMPL','BHTMPL') AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 27 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 27 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:45:56.695
SELECT ACTIONMODE, '['||ACTIONMODE||',{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD = :B2 AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION SELECT 'COPYNEW', '[COPYNEW,{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 12 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 12 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:47:20.852
SELECT ACTIONMODE, '['||ACTIONMODE||',{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD = :B2 AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION SELECT 'COPYNEW', '[COPYNEW,{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 12 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 12 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:48:11.932
SELECT CMB_TEMPLATE_CODE FROM PAYMENTSCONSOLIDATED W
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:46:49.771
SELECT VALUE1 FROM RESTRICTIONS WHERE USERGROUP = :B6 AND ROLEID = :B5 AND PRODUCTCODE = :B4 AND FUNCTIONCODE = :B3 AND TYPECODE = :B2 AND ENTRYMETHOD = :B1 AND RESTRICTIONTYP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 4 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:44:49.589
SELECT USERGROUP FROM PAYMENTSCOMMON W
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:47:53.904
SELECT SUBMIT_TIMESTAMP FROM PAYMENTSCOMMON_CUST W
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:45:06.603
SELECT CMB_TEMPLATE_CODE FROM PAYMENTSCONSOLIDATED W
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:45:45.683
SELECT SUBMIT_TIMESTAMP FROM PAYMENTSCOMMON_CUST W
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:46:02.703
SELECT USERGROUP FROM PAYMENTSCOMMON W
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
*** 2010-01-21 12:45:49.684
SELECT CMB_TEMPLATE_CODE FROM PAYMENTSCONSOLIDATED W
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-00905: missing keyword
parse error offset: 77
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.01 0 0 0 0
Execute 2 0.21 0.23 0 0 0 0
Fetch 4 254.67 251.53 26848 29390 17 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 254.89 251.78 26848 29390 17 16
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net more data to client 4 0.00 0.00
gc cr block 2-way 4 0.00 0.00
direct path write temp 215 0.03 0.95
direct path read temp 1359 0.04 2.06
SQL*Net message from client 6 72.90 100.16
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 15 0.00 0.00 0 0 0 0
Execute 981723 9.37 16.99 0 0 0 0
Fetch 1562390 26.33 44.61 0 7811505 0 1116258
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2544128 35.70 61.60 0 7811505 0 1116258
Misses in library cache during parse: 1
Misses in library cache during execute: 1
981721 user SQL statements in session.
4 internal SQL statements in session.
981725 SQL statements in session.
12 statements EXPLAINed in this session.
********************************************************************************
Trace file: apyt01q2_ora_1601684.trc
Trace file compatibility: 11.1.0.7
Sort options: prsela exeela fchela
1 session in tracefile.
981721 user SQL statements in trace file.
4 internal SQL statements in trace file.
981725 SQL statements in trace file.
30 unique SQL statements in trace file.
12 SQL statements EXPLAINed using schema:
WEBSYSTEMQ4.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
7455585 lines in trace file.
401 elapsed seconds in trace file.
Modified one with 1 of the Views select as inner
TKPROF: Release 11.1.0.7.0 - Production on Thu Jan 21 12:49:55 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: apyt01q2_ora_1216656.trc
Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 2 0.09 0.10 0 1100 0 15
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.10 0.11 0 1100 0 15
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 93 (DEVL_MONITOR)
Rows Row Source Operation
------- ---------------------------------------------------
15 SORT ORDER BY (cr=1926 pr=0 pw=0 time=0 us cost=160589 size=771020532 card=81486)
15 HASH JOIN (cr=1926 pr=0 pw=0 time=35 us cost=211 size=771020532 card=81486)
3554 VIEW COMMONCOMBINEDVIEW_CUST (cr=54 pr=0 pw=0 time=44 us cost=20 size=953157 card=3441)
3554 SORT UNIQUE (cr=54 pr=0 pw=0 time=39 us cost=20 size=234625 card=3441)
3554 UNION-ALL (cr=54 pr=0 pw=0 time=42 us)
2587 TABLE ACCESS FULL PAYMENTSCOMMON_CUST (cr=38 pr=0 pw=0 time=19 us cost=12 size=187725 card=2503)
967 TABLE ACCESS FULL PAYMENTSCOMMONTEMPLATE_CUST (cr=16 pr=0 pw=0 time=3 us cost=6 size=46900 card=938)
15 HASH JOIN (cr=1872 pr=0 pw=0 time=34 us cost=190 size=21750080 card=2368)
15 NESTED LOOPS (cr=1788 pr=0 pw=0 time=85 us cost=164 size=613203 card=69)
15 VIEW (cr=1676 pr=0 pw=0 time=6 us cost=140 size=16922 card=2)
15 SORT UNIQUE (cr=1676 pr=0 pw=0 time=5 us cost=140 size=874 card=2)
15 UNION-ALL (cr=1676 pr=0 pw=0 time=2662 us)
2 TABLE ACCESS FULL USERS_CUST (cr=32 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
1 TABLE ACCESS FULL USERS_CUST (cr=32 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
1 TABLE ACCESS FULL USERS_CUST (cr=32 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
13 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=26 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
13 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=13 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
11 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=22 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
11 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=11 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
2 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=6 pr=0 pw=0 time=0 us cost=2 size=33 card=1)
2 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=4 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
13 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATED (cr=26 pr=0 pw=0 time=0 us cost=2 size=20 card=1)
13 INDEX UNIQUE SCAN PK_PAYMENTSCON (cr=13 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86789)
13 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATED (cr=26 pr=0 pw=0 time=0 us cost=2 size=14 card=1)
13 INDEX UNIQUE SCAN PK_PAYMENTSCON (cr=13 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86789)
13 HASH JOIN (cr=494 pr=0 pw=0 time=454 us cost=111 size=495 card=1)
1 TABLE ACCESS BY INDEX ROWID USERS (cr=13 pr=0 pw=0 time=0 us cost=7 size=17712 card=656)
1 BITMAP CONVERSION TO ROWIDS (cr=12 pr=0 pw=0 time=0 us)
1 BITMAP OR (cr=12 pr=0 pw=0 time=0 us)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
2587 TABLE ACCESS FULL PAYMENTSCOMMON (cr=361 pr=0 pw=0 time=26 us cost=103 size=1171404 card=2503)
1 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
1 TABLE ACCESS FULL USERS_CUST (cr=32 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
1 TABLE ACCESS FULL USERS_CUST (cr=32 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
2 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=6 pr=0 pw=0 time=0 us cost=2 size=18 card=1)
2 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=4 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
0 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=0 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
0 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
2 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=6 pr=0 pw=0 time=0 us cost=2 size=36 card=1)
2 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=4 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
2 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATEDTEMPLATE (cr=6 pr=0 pw=0 time=0 us cost=2 size=20 card=1)
2 INDEX UNIQUE SCAN PK_PAYMENTSCONTEMPLATE (cr=4 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86790)
2 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATEDTEMPLATE (cr=6 pr=0 pw=0 time=0 us cost=2 size=12 card=1)
2 INDEX UNIQUE SCAN PK_PAYMENTSCONTEMPLATE (cr=4 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86790)
2 HASH JOIN (cr=104 pr=0 pw=0 time=2439 us cost=26 size=379 card=1)
1 TABLE ACCESS BY INDEX ROWID USERS (cr=13 pr=0 pw=0 time=0 us cost=7 size=17712 card=656)
1 BITMAP CONVERSION TO ROWIDS (cr=12 pr=0 pw=0 time=0 us)
1 BITMAP OR (cr=12 pr=0 pw=0 time=0 us)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
967 TABLE ACCESS FULL PAYMENTSCOMMONTEMPLATE (cr=61 pr=0 pw=0 time=14 us cost=18 size=330176 card=938)
15 VIEW COMMONGRIDCOLSRTGSANDACH (cr=112 pr=0 pw=0 time=0 us cost=12 size=426 card=1)
15 SORT UNIQUE (cr=112 pr=0 pw=0 time=0 us cost=12 size=397 card=4)
15 UNION ALL PUSHED PREDICATE (cr=112 pr=0 pw=0 time=0 us)
11 NESTED LOOPS (cr=58 pr=0 pw=0 time=0 us cost=3 size=182 card=1)
11 TABLE ACCESS BY INDEX ROWID RTGS (cr=31 pr=0 pw=0 time=0 us cost=2 size=142 card=1)
11 INDEX UNIQUE SCAN PK_RTGS (cr=20 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86791)
11 TABLE ACCESS BY INDEX ROWID RTGS_CUST (cr=27 pr=0 pw=0 time=0 us cost=1 size=40 card=1)
11 INDEX UNIQUE SCAN PK_RTGS_CUST (cr=16 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 86792)
2 TABLE ACCESS BY INDEX ROWID ACHUSHEADER (cr=22 pr=0 pw=0 time=0 us cost=2 size=25 card=1)
2 INDEX UNIQUE SCAN PK_ACHUSHEADER (cr=20 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86773)
0 NESTED LOOPS (cr=15 pr=0 pw=0 time=0 us cost=2 size=165 card=1)
0 TABLE ACCESS BY INDEX ROWID RTGSTEMPLATE (cr=15 pr=0 pw=0 time=0 us cost=1 size=130 card=1)
0 INDEX UNIQUE SCAN PK_RTGSTEMPLATE (cr=15 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 86793)
0 TABLE ACCESS BY INDEX ROWID RTGSTEMPLATE_CUST (cr=0 pr=0 pw=0 time=0 us cost=1 size=35 card=1)
0 INDEX UNIQUE SCAN PK_RTGSTEMPLATE_CUST (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 86794)
2 TABLE ACCESS BY INDEX ROWID ACHUSHEADERTEMPLATE (cr=17 pr=0 pw=0 time=0 us cost=1 size=25 card=1)
2 INDEX UNIQUE SCAN PK_ACHUSHEADTEMP (cr=15 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 86774)
3554 VIEW CONSOLIDATEDCOMBINEDVIEW (cr=84 pr=0 pw=0 time=53 us cost=26 size=1025418 card=3441)
3554 SORT UNIQUE (cr=84 pr=0 pw=0 time=39 us cost=26 size=456404 card=3441)
3554 UNION-ALL (cr=84 pr=0 pw=0 time=40 us)
2587 TABLE ACCESS FULL PAYMENTSCONSOLIDATED (cr=61 pr=0 pw=0 time=6 us cost=16 size=335402 card=2503)
967 TABLE ACCESS FULL PAYMENTSCONSOLIDATEDTEMPLATE (cr=23 pr=0 pw=0 time=2 us cost=8 size=121002 card=938)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
15 SORT (ORDER BY)
15 HASH JOIN
3554 VIEW OF 'COMMONCOMBINEDVIEW_CUST' (VIEW)
3554 SORT (UNIQUE)
3554 UNION-ALL
2587 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCOMMON_CUST' (TABLE)
967 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCOMMONTEMPLATE_CUST' (TABLE)
15 HASH JOIN
15 NESTED LOOPS
15 VIEW
15 SORT (UNIQUE)
15 UNION-ALL
2 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
1 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
1 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
13 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMON_CUST' (TABLE)
13 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMMON_CUST' (INDEX (UNIQUE))
11 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMON_CUST' (TABLE)
11 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMMON_CUST' (INDEX (UNIQUE))
2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMON_CUST' (TABLE)
2 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMMON_CUST' (INDEX (UNIQUE))
13 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCONSOLIDATED' (TABLE)
13 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCON' (INDEX (UNIQUE))
13 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCONSOLIDATED' (TABLE)
13 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCON' (INDEX (UNIQUE))
13 HASH JOIN
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'USERS' (TABLE)
1 BITMAP CONVERSION (TO ROWIDS)
1 BITMAP OR
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
2587 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCOMMON' (TABLE)
1 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
1 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
1 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'USERS_CUST' (TABLE)
2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMONTEMPLATE_CUST' (TABLE)
2 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMTEMP_CUST' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMONTEMPLATE_CUST' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMTEMP_CUST' (INDEX (UNIQUE))
2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMONTEMPLATE_CUST' (TABLE)
2 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMTEMP_CUST' (INDEX (UNIQUE))
2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCONSOLIDATEDTEMPLATE' (TABLE)
2 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCONTEMPLATE' (INDEX (UNIQUE))
2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCONSOLIDATEDTEMPLATE' (TABLE)
2 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCONTEMPLATE' (INDEX (UNIQUE))
2 HASH JOIN
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'USERS' (TABLE)
1 BITMAP CONVERSION (TO ROWIDS)
1 BITMAP OR
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
1 BITMAP CONVERSION (FROM ROWIDS)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'PK_USERS' (INDEX (UNIQUE))
967 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCOMMONTEMPLATE' (TABLE)
15 VIEW OF 'COMMONGRIDCOLSRTGSANDACH' (VIEW)
15 SORT (UNIQUE)
15 UNION ALL PUSHED PREDICATE
11 NESTED LOOPS
11 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'RTGS' (TABLE)
11 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_RTGS' (INDEX (UNIQUE))
11 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'RTGS_CUST' (TABLE)
11 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_RTGS_CUST' (INDEX (UNIQUE))
2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ACHUSHEADER' (TABLE)
2 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_ACHUSHEADER' (INDEX (UNIQUE))
0 NESTED LOOPS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'RTGSTEMPLATE' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_RTGSTEMPLATE' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'RTGSTEMPLATE_CUST' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_RTGSTEMPLATE_CUST' (INDEX (UNIQUE))
2 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'ACHUSHEADERTEMPLATE' (TABLE)
2 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_ACHUSHEADTEMP' (INDEX (UNIQUE))
3554 VIEW OF 'CONSOLIDATEDCOMBINEDVIEW' (VIEW)
3554 SORT (UNIQUE)
3554 UNION-ALL
2587 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCONSOLIDATED' (TABLE)
967 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCONSOLIDATEDTEMPLATE' (TABLE)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net more data to client 4 0.00 0.00
gc cr block 2-way 2 0.00 0.00
SQL*Net message from client 2 39.90 39.91
********************************************************************************
SQL ID: 89mmj5cv07zq2
Plan Hash: 2463789682
SELECT ACTIONMODE, '['||ACTIONMODE||',
{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING
FROM
ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND
PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD
= :B2 AND ACCOUNTFILTER = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,
{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM
ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND
PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND
ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION
SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS
ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID =
:B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND
ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 UNION
SELECT 'COPYNEW', '[COPYNEW,{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS
ACTIONSTRING FROM ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID =
:B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE = 'INST' AND TYPECODE = :B3 AND
ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND ACCOUNTFILTER = :B1 ORDER BY
1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 11 0.00 0.00 0 0 0 0
Fetch 121 0.00 0.00 0 290 0 110
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 133 0.00 0.00 0 290 0 110
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
10 SORT UNIQUE (cr=27 pr=0 pw=0 time=0 us cost=16 size=264 card=4)
10 UNION-ALL (cr=27 pr=0 pw=0 time=5 us)
7 INDEX RANGE SCAN ACCOUNTBYACTIONCROSSREFPK (cr=5 pr=0 pw=0 time=4 us cost=3 size=66 card=1)(object id 86754)
1 INDEX RANGE SCAN ACCOUNTBYACTIONCROSSREFPK (cr=8 pr=0 pw=0 time=0 us cost=3 size=66 card=1)(object id 86754)
1 INDEX RANGE SCAN ACCOUNTBYACTIONCROSSREFPK (cr=7 pr=0 pw=0 time=0 us cost=3 size=66 card=1)(object id 86754)
1 INDEX RANGE SCAN ACCOUNTBYACTIONCROSSREFPK (cr=7 pr=0 pw=0 time=0 us cost=3 size=66 card=1)(object id 86754)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
10 SORT (UNIQUE)
10 UNION-ALL
7 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACCOUNTBYACTIONCROSSREFPK' (INDEX (UNIQUE))
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACCOUNTBYACTIONCROSSREFPK' (INDEX (UNIQUE))
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACCOUNTBYACTIONCROSSREFPK' (INDEX (UNIQUE))
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACCOUNTBYACTIONCROSSREFPK' (INDEX (UNIQUE))
********************************************************************************
SQL ID: 7b5zy50ydpj2t
Plan Hash: 115578582
SELECT ACTIONMODE, '['||ACTIONMODE||',
{!-PAY.ACTIONDESCRIPTION.'||ACTIONMODE||'-!}]' AS ACTIONSTRING
FROM
ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND
PRODUCTCODE = :B5 AND FUNCTIONCODE = :B4 AND TYPECODE = :B3 AND ENTRYMETHOD
= :B2 AND COMPINFO_ID = :B1 UNION SELECT 'MAKETMPL', '[MAKETMPL,
{!-PAY.ACTIONDESCRIPTION.MAKETMPL-!}]' AS ACTIONSTRING FROM
ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND
PRODUCTCODE = :B5 AND FUNCTIONCODE = 'TMPL' AND TYPECODE = :B3 AND
ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1 UNION
SELECT 'MAKEINST', '[MAKEINST,{!-PAY.ACTIONDESCRIPTION.MAKEINST-!}]' AS
ACTIONSTRING FROM ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND
ROLEID = :B6 AND PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','BATCH') AND
TYPECODE = :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND
COMPINFO_ID = :B1 UNION SELECT 'COPYNEW', '[COPYNEW,
{!-PAY.ACTIONDESCRIPTION.COPYNEW-!}]' AS ACTIONSTRING FROM
ACHCOMPANYIDBYACTIONCROSSREF WHERE USERGROUP = :B7 AND ROLEID = :B6 AND
PRODUCTCODE = :B5 AND FUNCTIONCODE IN ('INST','TMPL','BHTMPL') AND TYPECODE
= :B3 AND ENTRYMETHOD = 0 AND ACTIONMODE = 'INSERT' AND COMPINFO_ID = :B1
ORDER BY 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 45 0.00 0.00 0 157 0 41
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 50 0.01 0.00 0 157 0 41
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
10 SORT UNIQUE (cr=41 pr=0 pw=0 time=0 us cost=23 size=200 card=4)
11 UNION-ALL (cr=41 pr=0 pw=0 time=9 us)
7 INDEX RANGE SCAN ACHCOMPANYIDBYACTIONCROSSREFPK (cr=5 pr=0 pw=0 time=7 us cost=4 size=50 card=1)(object id 86755)
1 INDEX RANGE SCAN ACHCOMPANYIDBYACTIONCROSSREFPK (cr=7 pr=0 pw=0 time=0 us cost=4 size=50 card=1)(object id 86755)
1 INLIST ITERATOR (cr=12 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN ACHCOMPANYIDBYACTIONCROSSREFPK (cr=12 pr=0 pw=0 time=0 us cost=5 size=50 card=1)(object id 86755)
2 INLIST ITERATOR (cr=17 pr=0 pw=0 time=102 us)
2 INDEX RANGE SCAN ACHCOMPANYIDBYACTIONCROSSREFPK (cr=17 pr=0 pw=0 time=0 us cost=6 size=50 card=1)(object id 86755)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
10 SORT (UNIQUE)
11 UNION-ALL
7 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACHCOMPANYIDBYACTIONCROSSREFPK' (INDEX (UNIQUE))
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACHCOMPANYIDBYACTIONCROSSREFPK' (INDEX (UNIQUE))
1 INLIST ITERATOR
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACHCOMPANYIDBYACTIONCROSSREFPK' (INDEX (UNIQUE))
2 INLIST ITERATOR
2 INDEX MODE: ANALYZED (RANGE SCAN) OF
'ACHCOMPANYIDBYACTIONCROSSREFPK' (INDEX (UNIQUE))
********************************************************************************
SQL ID: dh3a7vvp4fksf
Plan Hash: 1896929326
SELECT COUNT(*)
FROM
ENTITLEMENTS WHERE USERGROUP = :B6 AND ROLEID = :B5 AND PRODUCTCODE = :B4
AND FUNCTIONCODE = :B3 AND TYPECODE = :B2 AND ENTRYMETHOD = :B1 AND
ACTIONMODE NOT IN('SELECT','INBOXP','INBOXT','INBOX')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 27 0.00 0.00 0 0 0 0
Fetch 27 0.00 0.00 0 89 0 27
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 55 0.00 0.00 0 89 0 27
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
5 INDEX RANGE SCAN PK_ENTITLEMENTS (cr=3 pr=0 pw=0 time=4 us cost=3 size=46 card=1)(object id 86465)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
5 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_ENTITLEMENTS'
(INDEX (UNIQUE))
********************************************************************************
SQL ID: fwaxw4cgz3zpb
Plan Hash: 1479835720
SELECT VALUE1
FROM
RESTRICTIONS WHERE USERGROUP = :B6 AND ROLEID = :B5 AND PRODUCTCODE = :B4
AND FUNCTIONCODE = :B3 AND TYPECODE = :B2 AND ENTRYMETHOD = :B1 AND
RESTRICTIONTYPE = 'ApproveOwn'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 22 0.00 0.00 0 0 0 0
Fetch 22 0.00 0.00 0 88 0 22
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 46 0.00 0.00 0 88 0 22
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID RESTRICTIONS (cr=4 pr=0 pw=0 time=0 us cost=4 size=59 card=1)
1 INDEX RANGE SCAN PK_RESTRICTIONS (cr=3 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 86522)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'RESTRICTIONS'
(TABLE)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_RESTRICTIONS'
(INDEX (UNIQUE))
********************************************************************************
SQL ID: 17ummczq1acb5
Plan Hash: 453089871
SELECT 'Y'
FROM
PAYMENTSCOMMONTEMPLATE P,PAYMENTSCONSOLIDATEDTEMPLATE PCT WHERE P.TNUM=
PCT.TNUM AND PCT.CMB_TEMPLATE_CODE=:B3 AND P.USERGROUP=:B2 AND :B1 <
P.MODIFIED_TIMESTAMP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 52 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 52 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS (cr=26 pr=0 pw=0 time=0 us)
1 NESTED LOOPS (cr=25 pr=0 pw=0 time=0 us cost=9 size=31 card=1)
1 TABLE ACCESS FULL PAYMENTSCONSOLIDATEDTEMPLATE (cr=23 pr=0 pw=0 time=0 us cost=8 size=15 card=1)
1 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP (cr=2 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 86787)
0 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE (cr=1 pr=0 pw=0 time=0 us cost=1 size=16 card=1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS MODE: ANALYZED (FULL) OF
'PAYMENTSCONSOLIDATEDTEMPLATE' (TABLE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_PAYMENTSCOMTEMP'
(INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMONTEMPLATE' (TABLE)
********************************************************************************
SQL ID: g3176qdxahvv9
Plan Hash: 1546270724
select :"SYS_B_0"
from
dual
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 93 (DEVL_MONITOR)
Rows Row Source Operation
------- ---------------------------------------------------
1 FAST DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 FAST DUAL
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.02 0.04
********************************************************************************
SQL ID: 9mtwtwhc30pwt
Plan Hash: 4011638367
SELECT USERGROUP
FROM
PAYMENTSCOMMON WHERE TNUM = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 13 0.00 0.00 0 0 0 0
Fetch 13 0.00 0.00 0 39 0 13
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 27 0.00 0.00 0 39 0 13
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
1 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86785)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMON' (TABLE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_PAYMENTSCOMMON'
(INDEX (UNIQUE))
********************************************************************************
SQL ID: 27k76482xhb85
Plan Hash: 2397851474
SELECT COUNT(*)
FROM
ACCOUNTBYACTIONCROSSREF WHERE USERGROUP = :B8 AND ROLEID = :B7 AND
PRODUCTCODE = :B6 AND FUNCTIONCODE = :B5 AND TYPECODE = :B4 AND ENTRYMETHOD
= :B3 AND ACTIONMODE = :B2 AND ACCOUNTFILTER = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 11 0.00 0.00 0 0 0 0
Fetch 11 0.00 0.00 0 33 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23 0.00 0.00 0 33 0 11
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
1 INDEX UNIQUE SCAN ACCOUNTBYACTIONCROSSREFPK (cr=3 pr=0 pw=0 time=0 us cost=2 size=66 card=1)(object id 86754)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'ACCOUNTBYACTIONCROSSREFPK' (INDEX (UNIQUE))
********************************************************************************
SQL ID: c52usnsa7u5rr
Plan Hash: 13739038
SELECT CMB_TEMPLATE_CODE
FROM
PAYMENTSCONSOLIDATED WHERE TNUM = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 13 0.00 0.00 0 0 0 0
Fetch 13 0.00 0.00 0 39 0 13
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 27 0.00 0.00 0 39 0 13
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATED (cr=3 pr=0 pw=0 time=0 us cost=2 size=8 card=1)
1 INDEX UNIQUE SCAN PK_PAYMENTSCON (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86789)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCONSOLIDATED' (TABLE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_PAYMENTSCON'
(INDEX (UNIQUE))
********************************************************************************
SQL ID: 2v152f2rbsfsu
Plan Hash: 1110141075
SELECT SUBMIT_TIMESTAMP
FROM
PAYMENTSCOMMON_CUST WHERE TNUM = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 13 0.00 0.00 0 0 0 0
Fetch 13 0.00 0.00 0 39 0 13
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 27 0.00 0.00 0 39 0 13
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 (WEBSYSTEMQ4) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=3 pr=0 pw=0 time=0 us cost=2 size=11 card=1)
1 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'PAYMENTSCOMMON_CUST' (TABLE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'PK_PAYMENTSCOMMON_CUST' (INDEX (UNIQUE))
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 4 0.09 0.10 0 1100 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.10 0.11 0 1100 0 16
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net more data to client 4 0.00 0.00
gc cr block 2-way 2 0.00 0.00
SQL*Net message from client 6 39.90 94.38
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.01 0.00 0 0 0 0
Execute 116 0.00 0.00 0 0 0 0
Fetch 267 0.00 0.00 0 826 0 250
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 393 0.01 0.01 0 826 0 250
Misses in library cache during parse: 0
118 user SQL statements in session.
0 internal SQL statements in session.
118 SQL statements in session.
11 statements EXPLAINed in this session.
********************************************************************************
Trace file: apyt01q2_ora_1216656.trc
Trace file compatibility: 11.1.0.7
Sort options: prsela exeela fchela
1 session in tracefile.
118 user SQL statements in trace file.
0 internal SQL statements in trace file.
118 SQL statements in trace file.
11 unique SQL statements in trace file.
11 SQL statements EXPLAINed using schema:
WEBSYSTEMQ4.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
1784 lines in trace file.
67 elapsed seconds in trace file.
One major difference i noticed between these 2 trace output,
i found these 2 lines extra in query with all Views
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path write temp 215 0.03 0.95
direct path read temp 1359 0.04 2.06
Not sure is it sorting or what,
Any more info on this is appreciated.
Thanks
[Updated on: Thu, 21 January 2010 12:38] Report message to a moderator |
|
|
|
| Re: Query With View Vs Select of the View behave differently [message #440326 is a reply to message #440208] |
Fri, 22 January 2010 07:45 |
cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Next time you've got a trace that big attach it as a file.
I wouldn't worry about those waits you've spotted, they only account for 2 seconds, and yes they're probably sorts.
The totals are quite informative.
Old ones:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.01 0 0 0 0
Execute 2 0.21 0.23 0 0 0 0
Fetch 4 254.67 251.53 26848 29390 17 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 254.89 251.78 26848 29390 17 16
New ones:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 4 0.09 0.10 0 1100 0 16
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.10 0.11 0 1100 0 16
The interesting difference here is in the disk and query columns. Basically the slow one is hitting just under 30K blocks in memory and almost as many on disk to find the 16 rows you're interested in.
The fast one is hitting 1100 blocks in memory and doing no disk reads.
Then we can look at the recursive sql totals.
Old:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 15 0.00 0.00 0 0 0 0
Execute 981723 9.37 16.99 0 0 0 0
Fetch 1562390 26.33 44.61 0 7811505 0 1116258
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2544128 35.70 61.60 0 7811505 0 1116258
New:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.01 0.00 0 0 0 0
Execute 116 0.00 0.00 0 0 0 0
Fetch 267 0.00 0.00 0 826 0 250
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 393 0.01 0.01 0 826 0 250
The recursive sql in this case should be the function calls in your query. So the interesting thing here is the execute and fetch counts. The total for the slow one is 2544128 and 393 for the fast one. Which is a massive difference.
This ties back to what we've observed with the grand totals, because the greater the number of rows scanned in a table, greater the number of times a function in the where clause will be called.
To give a simple example. Say you have a table (table1) with 10,000 rows and function f1 that does a query.
SELECT *
FROM table1
WHERE column1 = :1
AND f1(column2) = :2;
If there are no indexes oracle will do a full table scan and call the function 10000 times.
If there is a unique index on column1 then oracle will use that to access the table and it'll call the function just once.
So basically the view is stopping oracle optimising the query properly and so it's not using indexes when it should, so it's scanning more data than it needs to and consequently is executing the functions far too many times.
It's that last bit that's really slowing you down.
Finally lets look at the row source operation for the main query for the bit relating to the view you've replaced:
old:
Rows Row Source Operation
------- ---------------------------------------------------
15 VIEW COMMONCOMBINEDVIEW (cr=7840457 pr=26848 pw=26848 time=65500 us cost=3065 size=251834779 card=29701)
221124 SORT UNIQUE (cr=7839347 pr=26848 pw=26848 time=30876 us cost=3065 size=13762859 card=29701)
221124 UNION-ALL (cr=7839347 pr=0 pw=0 time=2343505 us)
249 TABLE ACCESS FULL USERS_CUST (cr=4048 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
80 TABLE ACCESS FULL USERS_CUST (cr=1344 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
132 TABLE ACCESS FULL USERS_CUST (cr=2160 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
41 TABLE ACCESS FULL USERS_CUST (cr=688 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
79 TABLE ACCESS FULL USERS_CUST (cr=1328 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
1660 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=2235 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
1660 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=575 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
1126 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=1601 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
1126 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=475 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
534 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=833 pr=0 pw=0 time=0 us cost=2 size=33 card=1)
534 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=299 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
1660 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATED (cr=2237 pr=0 pw=0 time=0 us cost=2 size=20 card=1)
1660 INDEX UNIQUE SCAN PK_PAYMENTSCON (cr=577 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86789)
1660 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATED (cr=2239 pr=0 pw=0 time=0 us cost=2 size=14 card=1)
1660 INDEX UNIQUE SCAN PK_PAYMENTSCON (cr=579 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86789)
161516 HASH JOIN (cr=384 pr=0 pw=0 time=1288 us cost=112 size=10694475 card=21605)
662 TABLE ACCESS FULL USERS (cr=23 pr=0 pw=0 time=1 us cost=8 size=17712 card=656)
2587 TABLE ACCESS FULL PAYMENTSCOMMON (cr=361 pr=0 pw=0 time=72 us cost=103 size=1171404 card=2503)
95 TABLE ACCESS FULL USERS_CUST (cr=1536 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
55 TABLE ACCESS FULL USERS_CUST (cr=912 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
58 TABLE ACCESS FULL USERS_CUST (cr=3056 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
13 TABLE ACCESS FULL USERS_CUST (cr=224 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
44 TABLE ACCESS FULL USERS_CUST (cr=736 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
4 TABLE ACCESS FULL USERS_CUST (cr=96 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
611 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=789 pr=0 pw=0 time=0 us cost=2 size=18 card=1)
611 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=178 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
356 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=497 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
356 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=141 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
255 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=365 pr=0 pw=0 time=0 us cost=2 size=36 card=1)
255 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=110 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
611 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATEDTEMPLATE (cr=782 pr=0 pw=0 time=0 us cost=2 size=20 card=1)
611 INDEX UNIQUE SCAN PK_PAYMENTSCONTEMPLATE (cr=171 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86790)
611 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATEDTEMPLATE (cr=770 pr=0 pw=0 time=0 us cost=2 size=12 card=1)
611 INDEX UNIQUE SCAN PK_PAYMENTSCONTEMPLATE (cr=159 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86790)
59608 HASH JOIN (cr=85 pr=0 pw=0 time=496 us cost=27 size=3068384 card=8096)
662 TABLE ACCESS FULL USERS (cr=24 pr=0 pw=0 time=0 us cost=8 size=17712 card=656)
967 TABLE ACCESS FULL PAYMENTSCOMMONTEMPLATE (cr=61 pr=0 pw=0 time=40 us cost=18 size=330176 card=938)
new:
Rows Row Source Operation
------- ---------------------------------------------------
15 VIEW (cr=1676 pr=0 pw=0 time=6 us cost=140 size=16922 card=2)
15 SORT UNIQUE (cr=1676 pr=0 pw=0 time=5 us cost=140 size=874 card=2)
15 UNION-ALL (cr=1676 pr=0 pw=0 time=2662 us)
2 TABLE ACCESS FULL USERS_CUST (cr=32 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
1 TABLE ACCESS FULL USERS_CUST (cr=32 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
1 TABLE ACCESS FULL USERS_CUST (cr=32 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
13 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=26 pr=0 pw=0 time=0 us cost=2 size=15 card=1)
13 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=13 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
11 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=22 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
11 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=11 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
2 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMON_CUST (cr=6 pr=0 pw=0 time=0 us cost=2 size=33 card=1)
2 INDEX UNIQUE SCAN PK_PAYMENTSCOMMON_CUST (cr=4 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86786)
13 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATED (cr=26 pr=0 pw=0 time=0 us cost=2 size=20 card=1)
13 INDEX UNIQUE SCAN PK_PAYMENTSCON (cr=13 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86789)
13 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATED (cr=26 pr=0 pw=0 time=0 us cost=2 size=14 card=1)
13 INDEX UNIQUE SCAN PK_PAYMENTSCON (cr=13 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86789)
13 HASH JOIN (cr=494 pr=0 pw=0 time=454 us cost=111 size=495 card=1)
1 TABLE ACCESS BY INDEX ROWID USERS (cr=13 pr=0 pw=0 time=0 us cost=7 size=17712 card=656)
1 BITMAP CONVERSION TO ROWIDS (cr=12 pr=0 pw=0 time=0 us)
1 BITMAP OR (cr=12 pr=0 pw=0 time=0 us)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
2587 TABLE ACCESS FULL PAYMENTSCOMMON (cr=361 pr=0 pw=0 time=26 us cost=103 size=1171404 card=2503)
1 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
1 TABLE ACCESS FULL USERS_CUST (cr=32 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
1 TABLE ACCESS FULL USERS_CUST (cr=32 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
0 TABLE ACCESS FULL USERS_CUST (cr=16 pr=0 pw=0 time=0 us cost=6 size=30 card=1)
2 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=6 pr=0 pw=0 time=0 us cost=2 size=18 card=1)
2 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=4 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
0 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=0 pr=0 pw=0 time=0 us cost=2 size=22 card=1)
0 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
2 TABLE ACCESS BY INDEX ROWID PAYMENTSCOMMONTEMPLATE_CUST (cr=6 pr=0 pw=0 time=0 us cost=2 size=36 card=1)
2 INDEX UNIQUE SCAN PK_PAYMENTSCOMTEMP_CUST (cr=4 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86788)
2 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATEDTEMPLATE (cr=6 pr=0 pw=0 time=0 us cost=2 size=20 card=1)
2 INDEX UNIQUE SCAN PK_PAYMENTSCONTEMPLATE (cr=4 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86790)
2 TABLE ACCESS BY INDEX ROWID PAYMENTSCONSOLIDATEDTEMPLATE (cr=6 pr=0 pw=0 time=0 us cost=2 size=12 card=1)
2 INDEX UNIQUE SCAN PK_PAYMENTSCONTEMPLATE (cr=4 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 86790)
2 HASH JOIN (cr=104 pr=0 pw=0 time=2439 us cost=26 size=379 card=1)
1 TABLE ACCESS BY INDEX ROWID USERS (cr=13 pr=0 pw=0 time=0 us cost=7 size=17712 card=656)
1 BITMAP CONVERSION TO ROWIDS (cr=12 pr=0 pw=0 time=0 us)
1 BITMAP OR (cr=12 pr=0 pw=0 time=0 us)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
1 BITMAP CONVERSION FROM ROWIDS (cr=2 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN PK_USERS (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=0)(object id 86544)
967 TABLE ACCESS FULL PAYMENTSCOMMONTEMPLATE (cr=61 pr=0 pw=0 time=14 us cost=18 size=330176 card=938)
I don't have time analyse that in detail (and I'd struggle even if I did since I know nothing about your db structure) but you can tell at a glance that the fast one is accessing a lot less rows.
There may be some hints you could use to help but I strongly recommend rewriting the query to not use views.
I'd also strongly recommend seeing if can find a way to avoid calling functions in the where clause.
|
|
|
|
Goto Forum:
Current Time: Sun May 12 19:11:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
