| can any help me out [message #340698] |
Wed, 13 August 2008 17:47  |
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 #340870 is a reply to message #340826] |
Thu, 14 August 2008 13:08  |
 |
Barbara Boehmer
Messages: 9092
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
