Afternoon Ignacio,
Results:
There was some improvement on the response time. Now, it
takes 2+ minutes instead of 3+, but I still think it is
pretty slow compared to the rest of the databases.
I'm not running 11g yet however, can you create a (sys owned) after
logon trigger that looks for your logon and Toad, and if found runs an
"alter session" to set event 10046 with waits (and binds) turned on -
similar to the code below (apologies for formatting in advance) which
will create a trace file on the database server.
When you connect as your user and Toad, you will be able to see exactly
where your session is delayed or held up (and you can use Toad's Trace
File Analyser to investigate as well!)
CREATE OR REPLACE TRIGGER sys.norms_logon_trigger
AFTER LOGON
ON DATABASE
BEGIN
-- Look for your user logon here, plus any other checks you need to
do.
IF (USER IN ('IGNACIO')) THEN
execute immediate 'alter session set events ''10046 trace name
context forever, level 12'' ';
END IF;
EXCEPTION
-- I know, I know, but in this case it is valid. Norm.
WHEN others THEN NULL;
END;
/
Once you have a trace file, you can drop this trigger to prevent any
possibility of it getting copied to production etc.
Cheers,
Norm. [TeamT]
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
