Forum Stats

  • 3,873,122 Users
  • 2,266,506 Discussions
  • 7,911,426 Comments

Discussions

Categories

Application is slow at times

Smohib
Smohib Member Posts: 221 Blue Ribbon
edited Feb 26, 2014 8:37AM in General Database Discussions

Hi all,

I am told to check the performance of a AIX BOX with 10.1.0.2.0.

The client tells they run a certain report around 10am & all the users are just unable to use their application as it becomes dead slow, also this process / job takes sometimes 2-3 hours which slows down their business process. The same report when generated at evening will take about 30 -45 mins only.

I had advised them to change the timing as max users will login at 10am & will be using hence the delay, so they generated the report at 6.30am i.e.; at the time when hardly any users would have come, this too took almost 1.45 mins.

I am confused as to where is the problem, I had generated AWR Report & came to know 2 jobs are utilising max time of DB to execute(SQL ORDERED BY ELAPSED TIME i.e.; 51.5% & 19.2% of database time) out of 1 is a job which is the report (many nested queries get executed & it gets executed ONLY ONCE in a day) & the other is also a job ( i got a table name from AWR report, which I verified with developers & got to know that particular table dose not have any index & gets executed about 12700 times in 1 day).

I am not able to get whole query of both the jobs from AWR report...any idea where I will be able to find whole query so that I can get execution plan for the same..

I have informed the client about the status, I want to know is there anything more I need to look at / am I missing anything important?

If I am not clear please let me know.

Thanks,

Mohib

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Smohib
    Smohib Member Posts: 221 Blue Ribbon
    Answer ✓

    GV,

    Yes we brought down the application server, still I could find ONLY SYS using the table, hence killed that process & created index.

    had tried creating online index too..but got error "resource busy" thats the reason opted above steps....

    yes had created index logging in as user, not as sys as it was created by developer will confirm again.

    Mohib

    · Share on TwitterShare on Facebook

Answers

  • onkar.nath
    onkar.nath Member Posts: 733 Silver Badge

    V$SQL should give you the query based on session id.

    If the table is used so much then you should consider having an index on the same. you should also consider tuning the query in question but first get the query. If you can enable trace on the session for the given period of time then try enable 10046 trace which should give you the exact query and its plan.

    Onkar

    · Share on TwitterShare on Facebook
  • observer_83
    observer_83 Member Posts: 39

    Hi Smohib,

    If you already located suspected query and want to extract the plan based on the partial query text, use below

    select sql_text from v$sqltext where hash_value='SQL_HASH_VALUE' order by piece;

    sql_hash_value, you can query from v$session as below

    select sql_hash_value,prev_hash_value /* prev_hash_value is historic value of the same sql */ from v$session where sid='&sid';

    then, extract the plan of the query and share ..

    · Share on TwitterShare on Facebook
  • Smohib
    Smohib Member Posts: 221 Blue Ribbon
    Thanks observer_83 & onkar.nath for the tips, will look into them once I get remote connection from client, mean while I just want to know as to what is the below statement about.
    DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TIME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2(30) := :job_name; job_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH TIME ZONE := :job_start; window_start TIMESTAMP WITH TIME ZONE := :window_start; window_end TIMESTAMP WITH TIME ZONE := :window_end; BEGIN begin PROCMRRSTOCK; end; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
    This is the query in AWR report which takes 51% of database time to get executed(ONCE), I will have to tune this process.
    · Share on TwitterShare on Facebook
  • Smohib
    Smohib Member Posts: 221 Blue Ribbon

    Hi,

    I found out that I cant generate explain plan for "declare" statement... it says "SELECT" keyword missing....

    the 2nd query which I told, has "UPDATE MOHIBSTOCK SET MRRNO=:B2 WHERE MRRID=:B1" in AWR report, I doubt I will get sql_fulltext for this too..

    also please let me know in AWR which sections I need to check which will lead to slow performance?

    Thanks,

    Mohib

    · Share on TwitterShare on Facebook
  • Harmandeep Singh
    Harmandeep Singh Member Posts: 287 Bronze Badge

    To get the full query

    select sql_text from dba_hist_sqltext where sql_id='<text>';

    1. Query which has very high executions , multiple times a day, create the necessary index so that you have less load on buffer cache , which in turns improves overall application performance.

    2. Generate the trace for the reports which are running slow to further analyze the issue.

    It seems like at the start of day at 6:30 AM, since there is no data in cache for report it is taking time.

    In evening , since most of report data is cached , it is running fast.

    At 10, it is slow, for the same reasons of caching as well as overall load on box.

    3. Check the buffer cache/ SGA advisories too , to see if increase in SGA is required.

    Thanks,

    Harman

    · Share on TwitterShare on Facebook
  • Smohib
    Smohib Member Posts: 221 Blue Ribbon

    Hi,

    @Harmandeep/ observer_83 / onkar.nath

    Thanks for the tip, but unfortunately I got the same output from dab_hist_sqltext i.e.; "UPDATE MOHIBSTOCK SET MRRNO=:B2 WHERE MRRID=:B1"

    Anyways I successfully created a index on the table "MOHIBSTOCK" & its better , the report is being generated in about 10-30 mins will have to collect more information from client after observation for a few more days.

    Yes will look into Buffer Cache & SGA too, but here there is issue about RAM...very less RAM is configured & client is not ready to upgrade RAM for time being...anyways I will look into it.

    I never wanted to bring Listener or anything related to DB down, hence brought down the application server down (restarted) so that users who have not ended their session are disconnected & hence the table is free & I will create index, but that dint happen checked from v$session & killed the process using "MOHIBSTOCK" table(used by SYS) & then successfully created index on the table.

    Although things are working fine, just want to know what I did was correct? killing a process run by SYS?

    any link where in I can find what happens if SYS process is killed? how it has impact on other processes of database?

    Please help me on this.

    Thanks all for ideas

    Mohib

    · Share on TwitterShare on Facebook
  • observer_83
    observer_83 Member Posts: 39

    Smohib,

    Normally index creation in production is done when application server is down or when there is no activity on that table, If you are sure that no user are logged in and changing that table properties then you may create index online too.

    Here creating index on "MRRID" will be helpful.Once the necessary RAM is added you can alter SGA,buffer cache and shared pool accordingly.

    Thanks

    GV

    · Share on TwitterShare on Facebook
  • observer_83
    observer_83 Member Posts: 39

    Also killing any user defined(Known) process on session level is safe. if it is an application user then you should create index with that user and not with "sys".

    Thanks

    GV

    · Share on TwitterShare on Facebook
  • Smohib
    Smohib Member Posts: 221 Blue Ribbon
    Answer ✓

    GV,

    Yes we brought down the application server, still I could find ONLY SYS using the table, hence killed that process & created index.

    had tried creating online index too..but got error "resource busy" thats the reason opted above steps....

    yes had created index logging in as user, not as sys as it was created by developer will confirm again.

    Mohib

    · Share on TwitterShare on Facebook
This discussion has been closed.