Oracle Database Discussions

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

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 ..

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.

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

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

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

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