Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Smile Unanswered: Performance tuning for Query in Oracle

    Hello
    I need Help. I have a query in Oracle . i need to tune it. It is taking lot of time to query.I need some suggestions how to tune it.
    The query i have is

    SELECT A.job,
    NVL(A.failures, 0),
    B.failures,
    to_char( A.next_date, 'DD-MON-YY' ) || ' ' || substr( A.next_sec, 1, 5 ),
    NVL(A.interval, '1'),
    A.schema_user
    FROM dba_jobs A,
    dba_jobs_running B
    WHERE UPPER( what ) = UPPER( P_JobName )
    AND A.job = B.job (+)
    ORDER BY A.schema_user;




    dba_jobs, dba_jobs_running are views

    The source for these view is

    CREATE OR REPLACE VIEW DBA_JOBS ( JOB,
    LOG_USER, PRIV_USER, SCHEMA_USER, LAST_DATE,
    LAST_SEC, THIS_DATE, THIS_SEC, NEXT_DATE,
    NEXT_SEC, TOTAL_TIME, BROKEN, INTERVAL,
    FAILURES, WHAT, NLS_ENV, MISC_ENV,
    INSTANCE ) AS select JOB, lowner LOG_USER, powner PRIV_USER, cowner SCHEMA_USER,
    LAST_DATE, substr(to_char(last_date,'HH24:MIS'),1,8) LAST_SEC,
    THIS_DATE, substr(to_char(this_date,'HH24:MIS'),1,8) THIS_SEC,
    NEXT_DATE, substr(to_char(next_date,'HH24:MIS'),1,8) NEXT_SEC,
    (total+(sysdate-nvl(this_date,sysdate)))*86400 TOTAL_TIME,
    decode(mod(FLAG,2),1,'Y',0,'N','?') BROKEN,
    INTERVAL# interval, FAILURES, WHAT,
    nlsenv NLS_ENV, env MISC_ENV, j.field1 INSTANCE
    from sys.job$ j



    The source for the second one is

    CREATE OR REPLACE VIEW DBA_JOBS_RUNNING ( SID,
    JOB, FAILURES, LAST_DATE, LAST_SEC,
    THIS_DATE, THIS_SEC, INSTANCE ) AS select v.SID, v.id2 JOB, j.FAILURES,
    LAST_DATE, substr(to_char(last_date,'HH24:MIS'),1,8) LAST_SEC,
    THIS_DATE, substr(to_char(this_date,'HH24:MIS'),1,8) THIS_SEC,
    j.field1 INSTANCE
    from sys.job$ j, v$lock v
    where v.type = 'JQ' and j.job (+)= v.id2


    Thanks

  2. #2
    Join Date
    Aug 2004
    Posts
    9
    Was this quicker before? Is the host CPU ocerloaded? Try (as SYSTEM or SYS user):

    ALTER SYSTEM FLUSH SHARED_POOL;

    Check the query if it is still slow. This will flush your shared pool and help if you have frgmented shared pool or it is full. Since these are data dictionary view no further tuning is needed.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hi,

    Be wary that with your second view, you are querying a view over another view. That means data for the first view (v$lock) are first generated, then you query these results so as to generate data for DBA_JOBS_RUNNING, and you finally query DBA_JOBS_RUNNING to get your result. Don't forget that the view definition (except for materialized views) is always re-executed whenever you access the view (which means at least two queries, one over the result of the other, when you query a view), so be careful about that when using views. As far as I know, it is quite hard to tune queries using too many views, because you have to tune each query, including view definitions, so as to optimize your final query.

    Moreover, you should try not to use SQL functions (such as SUBSTR, TO_CHAR, UPPER) when it is not necessary, for they might slow down your query a little.

    I'm not sure this is gonna help, but I hope !

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    this line will cause full table scans:
    WHERE UPPER( what ) = UPPER( P_JobName )
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Suggestions:


    1. rewrite your view script to convert WHAT column into upper case
    beforehand insteaad of at query time in your where clause.

    2. convert p_jobname to uppercase before throwing it into the
    query/cursor.
    p_jobname := upper(p_jobname);

    3. Rewrite query to something like this possibly:
    PHP Code:
    SELECT   a.job
             
    nvl(a.failures0), 
             (
    select b.failures
                from dba_jobs_running b
               where b
    .job a.jobfailures,
             
    to_char(a.next_date'DD-MON-YY') ||' '|| substr(a.next_sec15),
             
    nvl(a.INTERVAL'1'), 
             
    a.schema_user
        FROM dba_jobs a
       WHERE what 
    p_jobname
    ORDER BY a
    .schema_user
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Aug 2004
    Posts
    2
    Hi guys,
    Thanks for ur reply.
    I found the solution. Making it use the rule worked like a charm.
    It brought down the processing time to 50ms from 5 minutes.

    It seems that there is an open bug in 9i and later with the dba_jobs_running view. It tries to use the CBO even when there are no statistics present in the data dictionary.
    select statement was modified as follows:
    select /*+ rule */
    a.job,
    nvl(a.failures, 0),
    b.failures,
    to_char(a.next_date, 'DD-MON-YY') || ' ' || substr(a.next_sec, 1, 5),
    nvl(a.interval, '1'),
    a.schema_user
    from dba_jobs a, dba_jobs_running b
    where upper(a.what) = upper(p_jobname)
    and a.job = b.job(+)
    order by a.schema_user;


    Thanks again for ur response.

    Regards,
    Sridhar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •