TABLE ACCESS GOAL: ANALYZED (FULL) OF 'TABLE'

I WANT TO KNOW IF I HAVE RECORD IN MY TABLE BY DOING:

SELECT 'X'
FROM TABLE;

BUT THAT SELECT STATEMENT DOES A LOT OF PHISYCAL READ.

IS THERE A WAY TO FIND OUT IF THERES RECORD IN MY TABLE WITHOUT DOING A LOT OF PHYSICAL READ?

I ALSO DID :
SELECT COUNT(*)
FROM TABLE;

AND IT DOES LESS PHISICAL READ.

BUT I WANT BETTER PERFORMANCE.:)

SELECT 'X'
FROM TABLE
where rownum = 1;

IT DOES A FULL EVEN WITH ROWNUM = 1

No, it most certanly does not perform a full scan!

Here is a cut-paste from my SQL*Plus session:

********************************
SQL> select count(*) from ix_table1;

COUNT(*)
---------
22465

SQL> set autotrace traceonly
SQL> select * from ix_table1;

22465 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'IX_TABLE1'

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1564 consistent gets
62 physical reads
0 redo size
874504 bytes sent via SQL*Net to client
166826 bytes received via SQL*Net from client
3002 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
22465 rows processed

SQL> select * from ix_table1 where rownum=1;

1 row selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'IX_TABLE1

Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
0 physical reads
0 redo size
1194 bytes sent via SQL*Net to client
674 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
********************************

Pay attention to a second operation in explain plan with rownum=1: it is "COUNT (STOPKEY)".

This means the initiated full table scan will exit immediately when the predefined number of rows (ROWNUM=x) will be found. Also pay attention to the statistics of PHYSICAL READS, DB BLOCK GETS, CONSISTENT GETS and ROWS PROCESSED for both queries - it will convince you that full scan is not executed with ROWNUM=1.

HTH,

Hi!

Can you help me with the output of the analysis. Actually I also want to use the Explain Plan stuff. I also got the similar results but can you tell me where to look for the explanation of the result that is returned by the command?

Thanks!!

Hi

you can look at Oracle 8/8i Concept manual chapter 20

I am also interested in EXPLAIN PLAN.
I have created a table plan_table in my schema.
I run this query say:
EXPLAIN PLAN
for
select * from test
where rownum=1;

And then do the select * from plan_table..
But I do not get the details in the format that you got..
and I also do not see all the details that you got in there.
I have been trying to find the help on this for a long time.
I also tried online help for the concept manual. It doesn't tell this.
Can you help me please ?
Can you send me the script that you ran to get these results.

Thanks, my email id
[email protected]

Check out http://technet.oracle.com/docs/produ...2/ch14_str.htm

or
http://technet.oracle.com/docs/produ...1d.htm#2061798

[Edited by marist89 on 01-04-2001 at 12:08 PM]

(To ShrutiM and sonaliak)

Regarding the explain plans and statistics from my previous post -

If you just want to format the output of explain plan in a more readable format, there is a bunch of various scripts all over the internet, a couple of them even in oracle documentation. However I find it somewhat clumsy to mess with "EXPLAIN_PLAN_and_then_SELECT_FROM_PLAN_TABLE" for each and every sql I want to tune. Nowadays every decent DBA and developement tool or utility incorporates graphical explain plan facility, where you even don't have to actualy run the query.

Long time ago even SQL*Plus entered the arena of those "decent tools" ;-)) and offers an excelent explain plan and query statistics. The basic syntax is

SET AUTOTRACE ON|OFF|TRACEONLY [EXPLAIN] [STATISTICS]

When you set the autotrace on then the result of every query will be followed by an explain plan and/or statistics. There are various combinations possible. I find the following particularlly useful:
SET AUTOTRACE TRACEONLY EXPLAIN
because the query you isue will not actually be run, only explained, which means you can tune long running queries without actually waiting a long time for them to finish.

A word of caution on statistics this facility offer. If you look closely on the statistics in my previous post you'll find it reported a memory sort on both queries. But if you look at the queries it is more than obvious that queries required no sort (no ORDER BY or GROUP BY or merge joins, ....). The sorts were actually performed internaly by explain plan facility when outputing the explain plan! So the statistics it reports does (probably) actually belongs to both the analyzed query and the internal queries on plan table.

If on the other hand you are not familiar with how to read and interpret the explain plan and statistics, then you should find more information in manuals and books. I myself find the Tuning Guide from official Oracle documentation very usefull.

HTH,

Thanks a lot jmodic.
You answered all the questions I had.
Which DBA tool would you recommend ?
As you may have guessed I am new to this dba world.
Ours is a startup company and we do not have these tools as yet.

Thanks again
Sonali

Quote:

---

Originally posted by sonaliak
Thanks a lot jmodic.
You answered all the questions I had.
Which DBA tool would you recommend ?
As you may have guessed I am new to this dba world.
Ours is a startup company and we do not have these tools as yet.

Thanks again
Sonali

---

I don't want to favor any vendor or tool, however since I also have to write a lot of PL/SQL I use TOAD extensively. You can even get a fully functional version for free from the web. And yes, I still use good (or bad) old SQL*Plus a lot, it is an extremely poverfull and simple basic tool if you know how to use all of it's features.

Apart for that I've used some other tools from Quest, BMC, Platinum and of course Oracle Enterprise Manager - they all have many wonderfull capabilities and some less vonderfull "features" (usually price is one of those). Hovewer I think most important think for dba is to understand what is happening in the database dictionary behind those nice and shiny front end screens. For that, there is no better way as to write your own bag of management and reporting scripts (even though maybe latter you'll switch to those WYSYWIG tools for the same reports, because their resukts are represented so much nicer and more understandable).

My 0.02$,

Thanks a lot!

I'm highly greatful for providing the above information. it will definitely prove benefitial.

I had some problem about a query running at client site which used to go into a long slumber state but now I feel I'll be able to find something.

Thanks al lot.

Shruti