error message

[sonaliak]

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;

[rcaballe]

Does it works with SYS?
If yes, why don't you grant privileges to system to select those sys tables?

[sonaliak]

The problem is someone changed our sys password and we don't know what it is now ??? Looks bad I know...

[pwoneill]

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.

[chrisrlong]

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