For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
I want to use fontawesome icons in a card list but I want to change the color of the icon depending on certain conditions. How can I do this within the Apex framework?
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 ..
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.
Harman
@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
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
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".
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.