Re: Serious Issue in oracle

If you find this situation again you can use the following query to know what are the wait events that are active :

select event from v$session where event not like '%ipc%' and wait_time=0;

wait_time=0 means that it is still waiting.

With Regards,
Saurabh Sood

On Thu, Jan 28, 2010 at 12:30 AM, AMIT VERMA <verma.labs_at_gmail.com> wrote:

> All,
>
> As per further analysis we have found the following:
>
> "If there is truncate going on the table & at the same time insertion will
> happen. This will make table non-responsive due to locks. We have the seen
> the situation using v$session"
>
> As per my knowledge Oracle maintain these situation however team told
> that processing (truncate & insertion) happens so fast that makes these
> situation. Please let us know your view.
>
> Thanks,
> Amit V
>
> On Tue, Jan 26, 2010 at 6:50 PM, Andrew Kerber <andrew.kerber_at_gmail.com>wrote:
>
>> Uh, guys, he says he is doing a select, not a select for update. He cant
>> be getting a deadlock. He should try comparing the plans when the query
>> runs quickly and the plan when it does not. Are histograms enabled? What
>> is the cursor sharing setting?
>>
>>
>> On Tue, Jan 26, 2010 at 2:15 AM, Dunbar, Norman <
>> norman.dunbar_at_environment-agency.gov.uk> wrote:
>>
>>> Morning Thomas,
>>>
>>> >> Yep, query gv$session and filter based on username,
>>> >> schemaname, and sql_id if you know it. Pay close attention
>>> >> to the event and blocking session columns. That will tell
>>> >> you if there is some sort of deadlock occurring.
>>>
>>> I'm puzzled. I've never seen a deadlock show up in V$SESSION or
>>> V$SESSION_WAIT before. Any deadlock I've actually seen - I've set up
>>> myself for testing purposes - usually has one statement killed by Oracle
>>> and an ORA-60 reported within a second and well before I get a chance to
>>> check these views.
>>>
>>> I know a second is a long time CPU wise, but I'm not all that sure that
>>> a deadlock will show up in either of these views. I'm interested though
>>> in knowing whether it does or doesn't - in case anyone has seen them
>>> show up.
>>>
>>> I've got a blog entry on deadlocks at
>>> http://qdosmsq.dunbar-it.co.uk/blog/?p=192 and I'd like to keep it up to
>>> date.
>>>
>>>
>>> Cheers,
>>> Norman.
>>>
>>>
>>> Information in this message may be confidential and may be legally
>>> privileged. If you have received this message by mistake, please notify the
>>> sender immediately, delete it and do not copy it to anyone else. We have
>>> checked this email and its attachments for viruses. But you should still
>>> check any attachment before opening it. We may have to make this message and
>>> any reply to it public if asked to under the Freedom of Information Act,
>>> Data Protection Act or for litigation. Email messages and attachments sent
>>> to or from any Environment Agency address may also be accessed by someone
>>> other than the sender or recipient, for business purposes. If we have sent
>>> you information and you wish to use it please read our terms and conditions
>>> which you can get by calling us on 08708 506 506. Find out more about the
>>> Environment Agency at www.environment-agency.gov.uk
>>>
>>> Information in this message may be confidential and may be legally
>>> privileged. If you have received this message by mistake, please notify the
>>> sender immediately, delete it and do not copy it to anyone else.
>>>
>>> We have checked this email and its attachments for viruses. But you
>>> should still check any attachment before opening it.
>>> We may have to make this message and any reply to it public if asked to
>>> under the Freedom of Information Act, Data Protection Act or for litigation.
>>> Email messages and attachments sent to or from any Environment Agency
>>> address may also be accessed by someone other than the sender or recipient,
>>> for business purposes.
>>>
>>> If we have sent you information and you wish to use it please read our
>>> terms and conditions which you can get by calling us on 08708 506 506. Find
>>> out more about the Environment Agency at www.environment-agency.gov.uk
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>
>>
>> --
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>
>
>
> --
> Amit Verma
> v.amit84_at_skype.com
>
>
> "Winning takes talent but it takes character to keep winning"
>

-- 
SAURABH SOOD
ORA-DBA

--
http://www.freelists.org/webpage/oracle-l