Submitted Questions

  • Show the output of a Query Plan to User

    There is a query which takes more time in production, the user has reported the issue, and we have fine tuned the same and it is working fine now in testing environment, we need to explain the user the root cause and also we need to show the user the query is running fine in production. How to show the query performance upgrade to the user in production environment? For the same issue, we have fine...

    Ranvir thakor

    • May 23rd, 2019

    it could be the case where that query use the table which may get affected by high volume of DML operations and table statistics are not updated one so when query is getting executed optimizer referr...

  • Passing a Function name in Parameter

    Can we pass a function name as a parameter value?? will it have any effect in performance degradation when used in a Large online transaction process?

    tinku981

    • Jun 14th, 2013

    About performance, it depends upon the computations you will be performing inside that function. Also, if we are using (user-defined) function in SQL (rather than creating PL/SQL), then it will give better performance.

    While using user-defined functions, we need to avoid Mutating functions error.

    murugesan

    • Jan 19th, 2013

    Yes we can pass but the function should return a value of type appropriate for the calling function

    Example

    round(trunc(100))

  • Performance Tuning

    My query was running fine till last month. the query will run for each month end. suddenly it is taking more than 20 minutes for running. the data is increased by 1 million. the index are all working fine . There is no cpu utilization time and also no memory blocks. What may be the reason?The increase in data has happened previous months also

    shams756

    • Nov 2nd, 2012

    1) Try collecting Statistics
    2) Spilt your query based on incoming unique values

    Avi

    • Sep 25th, 2012

    Check for the bottle necks starts from the source and continue till target

  • Rectify Deadlock

    If a deadlock has occured and if you have given with the dump, how will you identify that where the deadlock has occured, how the deadlock occured and how to rectify the same.

    kari

    • Oct 16th, 2011

    the steps necessary to identify and rectify code causing deadlocks are: * Locate the error messages in the alert log. * Locate the relevant trace file(s). * Identify the SQL statements i...