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

Thread: error message

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    I was trying to find list of tables doing full table scan:
    I got this script from some user forum, but when I run this as system/ manager I get error
    LINE/COL ERROR
    --------------------------------------------------------------------------------
    6/47 PLS-00201: identifier 'SYS.V_$SQLAREA' must be declared
    6/15 PL/SQL: SQL Statement ignored
    14/26 PLS-00364: loop index variable 'X1' use is invalid
    14/4 PL/SQL: Statement ignored
    27/12 PLS-00364: loop index variable 'X1' use is invalid
    26/3 PL/SQL: SQL Statement ignored
    -------------------------------
    Is there any other way to find this information ?
    Thanks
    --------------------------------
    create table full_sql (sql_text varchar2(1000), executions number);
    create or replace procedure p_findfullsql as
    v_csr number;
    v_rc number;
    v_string varchar2(2000);
    v_count number;
    cursor c1 is select sql_text,executions from v$sqlarea
    where lower(sql_text) like '%select%';
    begin
    for x1 in c1 loop
    delete from plan_table ;
    Begin
    v_Csr := DBMS_SQL.OPEN_CURSOR;
    v_string := 'explain plan for ' ;
    v_string := v_string||x1.sql_text ;
    DBMS_SQL.PARSE(v_csr, v_string, DBMS_SQL.V7);
    v_rc := DBMS_SQL.EXECUTE(v_csr);
    DBMS_SQL.CLOSE_CURSOR(v_csr);
    Exception
    when others then
    null;
    End ;
    select count(*) into v_count from plan_table
    where options like '%FULL%'
    and operation like '%TABLE%' ;
    if v_count > 0 then
    insert into full_sql(sql_text,executions)
    values (x1.sql_text, x1.executions) ;
    end if;
    end loop ;
    commit;
    end ;
    /
    execute p_findfullsql ;
    select * from full_sql;
    drop table full_sql;
    Sonali

  2. #2
    Does it works with SYS?
    If yes, why don't you grant privileges to system to select those sys tables?
    Ramon Caballero, DBA, rcaballe@yahoo.com

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    The problem is someone changed our sys password and we don't know what it is now ??? Looks bad I know...
    Sonali

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    Uh if someone you don't know has sys access to your database you've got bigger problems than full table scans :)

    Can't you connect as system and change the password for sys? alter user sys identified by newpassword. Worked fine for me. Might depend on your authentication scheme though.

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Uh, yeah :)

    As far as the script goes. I like it, and it's a real good starting point. Realize, however, what it is doing. It is generating plans for all the statements in the SGA. This is fine. however, most SGAs are not large enough to handle every single statement from your application. Statements get aged out. Plus, as you start explaining plans, you are adding to the SGA, thus causing other statements to fall out. Just want to make sure that you don't think this is the end-all, be-all of tricks. In order to minimize statement loss, you should probably attack your application in small pieces. Run part of the app, putting that SQL into the SGA, and then run this script. Then run another part of the app to put that piece in the SGA, and run this script again.

    A few more quick points.
    - The script only looks for SELECTs, so it could miss table scans on Updates, etc, which are still possible.
    - It still uses DBMS_SQL, which is slower. But if you are in 7.x world, you have no choice.
    - Minor optimization ppoint: Never do a full count when all you care about is existenace:
    select count(*) into v_count from plan_table
    where options like '%FULL%'
    and operation like '%TABLE%' ;
    *Should* be:
    select count(*) into v_count from plan_table
    where options like '%FULL%'
    and operation like '%TABLE%' and ROWNUM = 1;

    And lastly, realize that a tablescan is not necessarily a bad thing. It is a good indicator of a problem statement, but not the final word. Many smaller tables will be table-scanned and that is fine.

    If you are on a quest to minimize tablescans, you can set OPTIMIZER_INDEX_COST_ADJ to 1, which will make the optimizer almost *always* use an index (assuming one exists - even a bad one) before resorting to a tablescan.

    HTH,

    - Chris

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