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 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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 Go to previous messageGo to next message
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 Go to previous message
cookiemonster
Messages: 12320
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.
Previous Topic: Query Fetching data within Two hrs
Next Topic: Finding INSERT statments between 2 AWR snapshots
Goto Forum:
  


Current Time: Tue Sep 27 17:56:03 CDT 2016

Total time taken to generate the page: 0.23819 seconds