1) if it doesn't exist yet, create a plan table in your schema. You can do this
by running the script
$ORACLE_HOME/rdbms/admin/utlxplan.sql
2) use the "EXPLAIN PLAN" command:
EXPLAIN PLAN
SET STATEMENT_ID = 'sampleplan'
FOR
select count(*) from blobtest;
Explained.
3) query the plan table to see what oracle did:
SELECT LPAD(' ', 2*(level-1)) ||
operation || ' ' || options
|| ' ' || object_name || ' ' ||
DECODE(id, 0, 'Cost= ' || position)
"Query Plan"
FROM plan_table
START WITH id = 0 and statement_id = 'sampleplan'
CONNECT BY PRIOR id = parent_id
AND statement_id = 'sampleplan';
should give you the plan:
Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT Cost=
SORT AGGREGATE
TABLE ACCESS FULL BLOBTEST
4) remove your rows
DELETE
FROM plan_table
WHERE statement_id = 'sampleplan';
5) go back to step 2
If you use some sort of IDE like tool like TOAD or SQLdetective, they often
have their own versions of this kind of stuff available
at the touch of a button... much easier than the old school approach. Also note
that EXPLAIN PLAN is only half the picture - it's
great if you already know which queries are causing problems, but it won't help
you find them if you don't. For that, you need to
set oracle to use tracing and then use a little tool called tkprof to analyse
the trace file.
/t
>-----Original Message-----
>From: Ian Skinner [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, February 16, 2005 6:43 PM
>To: CF-Talk
>Subject: OT: SQL/Query exestuation plans
>
>I remember a thread recently where DBMS execution plans where
>used to debug a problematic query. I now have a need to
>explore this on an Oracle 8/9 system for documentation and
>debugging. I'm off to Google to see what I can find out on
>how one does this with Oracle systems. But I though I would
>also ask here since this group can often answer questions
>faster and more usefully then what I can Google up easily.
>
>So how does one actually capture and view these execution
>plans with an Oracle DBMS?
>
>--------------
>Ian Skinner
>Web Programmer
>BloodSource
>www.BloodSource.org
>Sacramento, CA
>
>"C code. C code run. Run code run. Please!"
>- Cynthia Dunning
>
>Confidentiality Notice: This message including any
>attachments is for the sole use of the intended
>recipient(s) and may contain confidential and privileged
>information. Any unauthorized review, use, disclosure or
>distribution is prohibited. If you are not the
>intended recipient, please contact the sender and
>delete any copies of this message.
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:195039
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
