Home » RDBMS Server » Server Utilities » can any help me out (Oracle 9.0.0.2)
can any help me out [message #340698] Wed, 13 August 2008 17:47 Go to next message
thalladas
Messages: 16
Registered: August 2008
Junior Member
Hi group,

I have a store procedure which is used to analyze tables in our datawarehouse.
CREATE OR REPLACE PROCEDURE DW.atab (
ptab IN VARCHAR2,
pstat IN VARCHAR2 DEFAULT 'compute'
)

AUTHID CURRENT_USER
IS
v_sql_code VARCHAR2 (4000); -- Holder for SQL code
v_tab VARCHAR2 (80);
v_error_code NUMBER; -- exception error number
v_error_message VARCHAR2 (200); -- exception error message

BEGIN
v_tab := UPPER (ptab);
v_sql_code := 'analyze table ' || v_tab || ' ' || pstat || ' statistics';
EXECUTE IMMEDIATE v_sql_code;

EXCEPTION
WHEN OTHERS
THEN
p.l ('DYN SQL: ' || v_sql_code);
v_error_code := SQLCODE;
v_error_message := SUBSTR (SQLERRM, 1, 200);
p.l ('Error: ' || v_error_message);
END atab;
/
I am trying to change this procedure to use dbms_stats.gather_table_stats() instead of analyze table compute statistics.
we are using this procedure at many places in warehouse and our jobs are running late because using analyze.
I tested most of the tables which are taking time for analyzing manually with dbms_stats.gather_table_stats with 50% estimate percent and the results are far btter than analyze with compute statistics.
So if someone try to help me change/modify this procedure to use dbms_stats.gather_table_stats(ownername=>' xyz',tabname=>'name',estimate_percent => 50).

Thanks,
Re: can any help me out [message #340699 is a reply to message #340698] Wed, 13 August 2008 18:29 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
Why use a user-defined procedure at all? Why not just use dbms_stats.gather_table_stats directly? It is a procedure.

Re: can any help me out [message #340826 is a reply to message #340699] Thu, 14 August 2008 09:11 Go to previous messageGo to next message
thalladas
Messages: 16
Registered: August 2008
Junior Member
Because of error handling..if that doesn't excute for some unknown reason .i want to write that to a log file..

thanks,
Re: can any help me out [message #340831 is a reply to message #340826] Thu, 14 August 2008 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So maybe the documentation on the package will help to change the statement in v_sqlcode?

By the way, you know dbms_stats call, what is the problem?

Regards
Michel

[Updated on: Thu, 14 August 2008 09:16]

Report message to a moderator

Re: can any help me out [message #340870 is a reply to message #340826] Thu, 14 August 2008 13:08 Go to previous message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
You can just call dbms_stats.gather_table_stats directly in your procedure. You don't need any dynamic sql. You can add whatever error logging you want to it.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE atab
  2    (ptab  IN VARCHAR2)
  3    AUTHID CURRENT_USER
  4  IS
  5  BEGIN
  6    DBMS_STATS.GATHER_TABLE_STATS
  7  	 (ownname => USER,
  8  	  tabname => ptab);
  9  END atab;
 10  /

Procedure created.

SCOTT@orcl_11g> EXEC atab ('dept')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Previous Topic: how to upload (export) the text file to database?
Next Topic: Loading data by sql loader after truncating Master table failed
Goto Forum:
  


Current Time: Tue Dec 24 11:18:47 CST 2024