DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Couple of Questions. Very urgent

  1. #1
    Join Date
    May 2001
    Location
    Boston
    Posts
    24

    Smile

    Can anyone please clarify the quesitons below

    Question 1:

    I have created an index on a couple of columns.I then issue a query.Now I want to check what all indexes has been used by the optimizer? Can anyone let me know.

    Question 2:

    To select one row from the table should I use

    a) A SELECT statement (or)
    b) Using a Cursor

    Which one will yield good performance and why?


  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    1. Use SET AUTOTRACE ON in SQL*PLUS
    2. Do you mean by (b) an explicit cursor and by (a) an implicit cursor? Even for 1 row, I use an expicit cursor. I wouldn't say *good* perforamnce but fetching data with explicit cursor has its other advantages.

  3. #3
    Join Date
    Apr 2001
    Posts
    47
    Hi,

    To see if Oracle optimizer uses an index you just created, you can use EXPLAIN PLAN command in SQL*PLus for you query - in my opinion it is more convenient.

    As for question 2:

    To select one row from the table should I use

    a) A SELECT statement (or)
    b) Using a Cursor

    Which one will yield good performance and why?


    If you work with Oracle 7.xx - using an explicit cursor would give you better performance, because it does exactly one fetch for one row. If you use implicit cursor (select statement), even if the table contained only one row for the query, Oracle would do the second fetch to make sure that it was the last row. So, implicit cursor would do 2 fetches if you try to get one row.
    If you work with Oracle 8.xx....9 - it does not make any difference which type of cursors you use - it was improved by Oracle.

    Regards,

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    If you work with Oracle 8.xx....9 - it does not make any difference which type of cursors you use - it was improved by Oracle.
    Thanks a lot for the info. I didn't know that Oracle has done something about that. I have addopted using explicit cursors in Oracle 7, and I do the same in Oracle 8/8i. Could you please provide an URL concerning your last claim: about Oracle's improvement, I would be curios on what they've done.

    Still, I think that people should use explicit cursors: more readable applications for all it counts.


  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    "Using an explicit cursor would give you better performance over implicit cursor..."

    Just another of those myths that have nothing to do with reality! The truth is the other way around: implicit cursors generally outperform explicit cursors all the time, even in 7.xx! See what a real expert has to say about that:

    http://asktom.oracle.com/pls/ask/f?p...:1544606261686
    and
    http://asktom.oracle.com/pls/ask/f?p...:1205168148688


    [Edited by jmodic on 09-02-2001 at 03:28 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    cut & paste from http://www.oracledba.co.uk/tips/explicit_implicit.htm


    Pick any tuning manual, and you'll often see a section about coding with explicit cursors as opposed to implicit ones. The argument seems sound, typically going along the lines of:

    "With an explicit cursor, you can open, fetch and close, whereas with an implicit cursor must issue open, fetch, second fetch, close - the second fetch being needed to check for a too many rows exception.

    However, since 7.3, those cunning people at Oracle have worked around this problem and the second fetch is no longer required. Because PL/SQL is interpreted (watch out for the new native compile option in 9i), implicit cursors will actually run FASTER than explicit since typically you are using less code to achieve the same task.

    You can try the following test for yourself:

    REM
    REM First a temporary table
    REM
    create table blah ( x number primary key ) ;

    insert into blah
    select rownum x
    from sys.source$
    where rownum < 1000;

    REM
    REM Make sure its got the full set of statistics
    REM
    analyze table blah compute statistics;

    REM
    REM A procedure to do approx 50,000 implicit reads
    REM
    create or replace procedure imp_test is
    y number;
    begin
    for i in 1 .. 50 loop
    for j in 1 .. 999 loop
    select x
    into y
    from blah
    where x = j;
    end loop;
    end loop;
    end;
    /

    REM
    REM A procedure to do approx 50,000 explicit reads
    REM
    create or replace procedure exp_test is
    cursor c(p number) is
    select x
    from blah
    where x = p;
    y number;
    begin
    for i in 1 .. 50 loop
    for j in 1 .. 999 loop
    open c(j);
    fetch c into y;
    close c;
    end loop;
    end loop;
    end;
    /

    REM
    REM We give each one a run to prime the buffers
    REM
    exec imp_test
    exec exp_test

    REM
    REM And now we see who is king of the roost...
    REM
    set timing on
    exec imp_test
    exec exp_test



    so implicit is faster

  7. #7
    Join Date
    Apr 2001
    Posts
    47
    Hi guys,

    I read the links and tried examples. You both are absolutely right - it seems that an implicit cursor rules forever. I was wrong. Thank you for the info.

    Sorry, Julian.

    Best regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width