Frequently Asked Questions
Why doesn't explain plan work with INSERT statements?
Because Oracle's explain plan does not work properly for INSERT statements. It only seems to populate one row in the plan table for insert statements - Simply fills the OPERATION column with 'INSERT STATEMENT'. If you want to see the explain plan, try changing the 'INSERT into' to 'SELECT count(*) from', and leave the rest of the statement as normal.
Why doesn't explain plan work sometimes on select statements that look fine?
Make sure there are no function calls in the select statement.
Oracle does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables. Oracle also says with bind variables in general, the EXPLAIN PLAN output may not represent the real execution plan.
From the text of a SQL statement, TKPROF cannot determine the types of the bind variables. It assumes that the type is CHARACTER, and gives an error message if this is not the case. You can avoid this limitation by putting appropriate type conversions in the SQL statement. This has been addressed in Oracle 9i.
Oracle also does not support the use of the EXPLAIN PLAN statement to determine the execution plans of SQL statements that access data dictionary views or dynamic performance tables.
You must also have the privileges necessary to execute the SQL statement for which you are determining the execution plan. If the SQL statement accesses a view, you must have privileges to access any tables and views on which the view is based. If the view is based on another view that is based on a table, you must have privileges to access both the other view and its underlying table.
When hit 'USERS RUNNING SQL NOW' for a SQL caught in ACTIVE SQL, not getting a row back for each SQL. Shouldn't we get a row, as its active SQL and it should be running?
ACTIVE SQL is SQL that is either running now, or has just run. We are joining the V$SQLTEXT and V$SESSION views, and are not specifying each session's STATUS. That means we do not care if the session's SQL text is currently being run, or whether it was the last such SQL text to be run.
So, if you get no rows back for 'USERS RUNNING SQL NOW', it means there are no users running this particular statement right now.
If I pick from the list of Databases in the drop-down
box on the Database Logon screen, I get the following error:
This might mean that you need the '.WORLD' extension added to the database name. We are getting the Database names from the local TNSNAMES.ORA file on the machine.
The '.WORLD' extension is not making it from the TNSNAMES.ORA
file entries through to the drop down box.
If this does not work, it means that Net8 could not locate the net service name specified in the TNSNAMES.ORA configuration file.
i.e. the Database name that you entered does not match up with any in your TNSNAMES.ORA file, so then SQL*NET cannot connect to this Database because it needs port numbers.
I am getting this error when I try to log on. The username/password/database details are all correct. Whats wrong?
Errors Tech definition:
MGA EagleEye uses SQL*Net/Net8 to connect to Oracle. This means it uses the TNSNAMES.ORA file to find out how to connect to the Database. If your TNSNAMES.ORA file doesn't exist, or has errors in it, then MGA EagleEye will not work. Basically, if you cannot connect to a database using Oracle's SQL PLUS (which works the same way), then you are not going to be able to connect using MGA EagleEye.