update stats in database

Asbon — Tue, 01 Aug 2023 12:04:18 GMT

Hi i'm using below query to gatter and update stats if stale_stats is "YES" then to "NO" status

to gatter stats:-

proc sql;

create table stats as

select owner,

table_name,

partition_name,

subpartition_name,

stattype_locked,

stale_stats /* YES or NO */

from pcds.dba_tab_statistics

where table_name = 'ADDR_TLNBR_MAINT_EVENT_C';

quit;

to update if stats is "YES" to make it "NO":-

proc sql;
connect to oracle (user="&pcdsuser" orapw="&pcdspw" path="@pcds");
execute( BEGIN
DBMS_STATS.GATHER_TABLE_STATS
(OWNNAME => %TSLIT ('FMT_TRAN_DBA'),
TABNAME => %TSLIT ('ADDR_TLNBR_MAINT_EVENT_C'),
PARTNAME => %TSLIT ('&_PARTITION_NAME.'),
GRANULARITY => %TSLIT ('PARTITION'),
CASCADE => TRUE,
estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt=> %TSLIT ('FOR ALL COLUMNS SIZE AUTO'),
FORCE=>TRUE);
END;
) by oracle;
disconnect from oracle;
quit;

I'm getting following error:

ERROR: ORACLE execute error: ORA-20001: &_OWNER. is an invalid identifierORA-06512: at "SYS.DBMS_STATS", line 24281ORA-06512: at
"SYS.DBMS_STATS", line 24332ORA-06512: at line 1.

can you please help in this.

Thanks.

Re: update stats in database

SASKiwi — Tue, 01 Aug 2023 21:25:02 GMT

I don't see any reference to &_OWNER in your posted code. It would help if you posted your complete SAS log including that error so we can see where it occurs.

Re: update stats in database

Quentin — Tue, 01 Aug 2023 22:04:50 GMT

I thought the purpose of %TSLIST is to add single quotes around a value? I would expect to see something like:

OWNNAME => 'FMT_TRAN_DBA',
TABNAME => 'ADDR_TLNBR_MAINT_EVENT_C',
PARTNAME => %TSLIT(&_PARTITION_NAME),
GRANULARITY => 'PARTITION',

But I haven't used Oracle in years...

Re: update stats in database

Asbon — Wed, 02 Aug 2023 03:56:56 GMT

below is complete log, only remove header part (from 1 to 86) to keep it short.
87 %macro ststs;
88
89 proc sql;
90 create table stats as
91 select owner,
2 The SAS System 23:44 Tuesday, August 1, 2023

92 table_name,
93 partition_name,
94 subpartition_name,
95 stattype_locked,
96 stale_stats /* YES or NO */
97 from pcds.dba_tab_statistics
98 where table_name = 'ADDR_TLNBR_MAINT_EVENT_C';
99 quit;
100
101 data stats_upd_ned;
102 set stats;
103 where upcase(trim(stale_stats))="YES";
104 run;
105
106 proc sql noprint;
107 select count(*) into :cnt from stats_upd_ned;
108 quit;
109
110 %if &cnt le 0 %then
111 %do;
112 %put "No RECORDS AVAILABLE";
113 %end;
114 %else
115 %do;
116
117 proc sql noprint;
118 select trim(OWNER), trim(TABLE_NAME),trim(PARTITION_NAME)
119 into
120 :_OWNER SEPARATED BY " ",
121 :_TABLE_NAME SEPARATED BY " ",
122 :_PARTITION_NAME SEPARATED BY " "
123 from stats_upd_ned
124 where trim(OWNER)='FMT_TRAN_DBA' and trim(TABLE_NAME) = 'ADDR_TLNBR_MAINT_EVENT_C';
125 quit;
126
127 %put &_OWNER.;
128 %put &_TABLE_NAME.;
129 %put &_PARTITION_NAME.;
130 /* options symbolgen;*/
131
132 %DO I=1 %TO &cnt;
133
134 proc sql;
135 connect to oracle (user="&pcdsuser" orapw="&pcdspw" path="@pcds");
136 execute( BEGIN
137 DBMS_STATS.GATHER_TABLE_STATS
138 (OWNNAME =>('FMT_TRAN_DBA'),
139 TABNAME =>('ADDR_TLNBR_MAINT_EVENT_C'),
140 PARTNAME =>%TSLIT(&_PARTITION_NAME.),
141 GRANULARITY =>('PARTITION'),
142 CASCADE => TRUE,
143 estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,
144 method_opt=>('FOR ALL COLUMNS SIZE AUTO'),
145 FORCE=>TRUE);
146 END;
147 ) by oracle;
148 disconnect from oracle;
149 quit;
3 The SAS System 23:44 Tuesday, August 1, 2023

150
151 %end;
152 %end;
153 %mend ststs;
154
155 %ststs;

NOTE: Compressing data set WORK.STATS increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: Table WORK.STATS created, with 632 rows and 6 columns.

NOTE: PROCEDURE SQL used (Total process time):
real time 0.07 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 5299.87k
OS Memory 24228.00k
Timestamp 08/01/2023 11:51:40 PM
Step Count 16 Switch Count 3
Page Faults 0
Page Reclaims 503
Page Swaps 0
Voluntary Context Switches 50
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

NOTE: There were 1 observations read from the data set WORK.STATS.
WHERE UPCASE(TRIM(stale_stats))='YES';
NOTE: The data set WORK.STATS_UPD_NED has 1 observations and 6 variables.
NOTE: Compressing data set WORK.STATS_UPD_NED increased size by 100.00 percent.
Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 2567.87k
OS Memory 21928.00k
Timestamp 08/01/2023 11:51:40 PM
Step Count 17 Switch Count 2
Page Faults 0
Page Reclaims 512
Page Swaps 0
Voluntary Context Switches 24
Involuntary Context Switches 7
Block Input Operations 0
Block Output Operations 0

NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 6202.56k
OS Memory 26024.00k
4 The SAS System 23:44 Tuesday, August 1, 2023

Timestamp 08/01/2023 11:51:40 PM
Step Count 18 Switch Count 0
Page Faults 0
Page Reclaims 248
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

SYMBOLGEN: Macro variable CNT resolves to 1
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 6211.96k
OS Memory 26024.00k
Timestamp 08/01/2023 11:51:40 PM
Step Count 19 Switch Count 0
Page Faults 0
Page Reclaims 252
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

SYMBOLGEN: Macro variable _OWNER resolves to FMT_TRAN_DBA
FMT_TRAN_DBA
SYMBOLGEN: Macro variable _TABLE_NAME resolves to ADDR_TLNBR_MAINT_EVENT_C
ADDR_TLNBR_MAINT_EVENT_C
SYMBOLGEN: Macro variable _PARTITION_NAME resolves to PTN_W20230730
PTN_W20230730
SYMBOLGEN: Macro variable CNT resolves to 1
SYMBOLGEN: Macro variable PCDSUSER resolves to aaaaaaaa
SYMBOLGEN: Macro variable PCDSPW resolves to xxxxxxxx
SYMBOLGEN: Macro variable _PARTITION_NAME resolves to PTN_W20230730
SYMBOLGEN: Macro variable VALUE resolves to PTN_W20230730
SYMBOLGEN: Macro variable VALUE resolves to PTN_W20230730
SYMBOLGEN: Macro variable S1 resolves to '"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable S2 resolves to "'
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable V1 resolves to PTN_W20230730
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable V2 resolves to "PTN_W20230730"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable S2 resolves to "'
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable S1 resolves to '"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable V3 resolves to 'PTN_W20230730'
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
ERROR: ORACLE execute error: ORA-20000: Unable to analyze TABLE "FMT_TRAN_DBA"."ADDR_TLNBR_MAINT_EVENT_C" PTN_W20230730,
insufficient privileges or does not existORA-06512: at "SYS.DBMS_STATS", line 24270ORA-06512: at "SYS.DBMS_STATS", line
24332ORA-06512: at line 1.
5 The SAS System 23:44 Tuesday, August 1, 2023

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Statement not executed due to NOEXEC option.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 3.92 seconds
user cpu time 0.01 seconds
system cpu time 0.09 seconds
memory 249.18k
OS Memory 19872.00k
Timestamp 08/01/2023 11:51:43 PM
Step Count 20 Switch Count 0
Page Faults 0
Page Reclaims 43
Page Swaps 0
Voluntary Context Switches 45
Involuntary Context Switches 60
Block Input Operations 0
Block Output Operations 0

156
157 %LET _CLIENTTASKLABEL=;
158 %LET _CLIENTPROCESSFLOWNAME=;
159 %LET _CLIENTPROJECTPATH=;
160 %LET _CLIENTPROJECTPATHHOST=;
161 %LET _CLIENTPROJECTNAME=;
162 %LET _SASPROGRAMFILE=;
163 %LET _SASPROGRAMFILEHOST=;
164
165 ;*';*";*/;quit;run;
166 ODS _ALL_ CLOSE;
167
168
169 QUIT; RUN;
170

Re: update stats in database

SASKiwi — Wed, 02 Aug 2023 05:11:16 GMT

Looks like you have a permissions problem reading certain Oracle system tables. You will have to talk to your Oracle DBA to fix that.

Re: update stats in database

Patrick — Wed, 02 Aug 2023 05:31:40 GMT

I believe you need to remove the single quotes as this is what %tslit() does for you.

topic Re: update stats in database in Administration and Deployment

update stats in database

Re: update stats in database

Re: update stats in database

Re: update stats in database

Re: update stats in database

Re: update stats in database