Update statistics tablename indexname. You must give the name of the table with which the index is associated. SAP ASE automatically runs update statistics when you create an index on existing data. You can set update statistics to run automatically during the time that best suits your site, and avoid running it.
Active5 years ago
In your experience, how often should Oracle database statistics be run? Our team of developers recently discovered that statistics hadn't been run our production box in over 2 1/2 months. That sounds like a long time to me, but I'm not a DBA.
user290
9 Answers
At my last job we ran statistics once a week. If I remember correctly, we scheduled them on a Thursday night, and on Friday the DBAs were very careful to monitor the longest running queries for anything unexpected. (Friday was picked because it was often just after a code release, and tended to be a fairly low traffic day.) When they saw a bad query they would find a better query plan and save that one so it wouldn't change again unexpectedly. (Oracle has tools to do this for you automatically, you tell it the query to optimize and it does.)
Many organizations avoid running statistics out of fear of bad query plans popping up unexpectedly. But this usually means that their query plans get worse and worse over time. And when they do run statistics then they encounter a number of problems. The resulting scramble to fix those issues confirms their fears about the dangers of running statistics. But if they ran statistics regularly, used the monitoring tools as they are supposed to, and fixed issues as they came up then they would have fewer headaches, and they wouldn't encounter them all at once.
user11318user11318
8,52922 gold badges2222 silver badges2525 bronze badges
Since Oracle 11g statistics are gathered automatically by default.
Two Scheduler windows are predefined upon installation of Oracle Database:
When statistics were last gathered?
Status of automated statistics gathering?
Windows Groups?
Window Schedules?
Manually gather Database Statistics in this Schema:
Manually gather Database Statistics in all Schemas!
grokstergrokster
4,32211 gold badge2929 silver badges1919 bronze badges
Whenever the data changes 'significantly'.
If a table goes from 1 row to 200 rows, that's a significant change. When a table goes from 100,000 rows to 150,000 rows, that's not a terribly significant change. When a table goes from 1000 rows all with identical values in commonly-queried column X to 1000 rows with nearly unique values in column X, that's a significant change.
Statistics store information about item counts and relative frequencies -- things that will let it 'guess' at how many rows will match a given criteria. When it guesses wrong, the optimizer can pick a very suboptimal query plan.
Jonathan RuppJonathan Rupp
13.9k22 gold badges3939 silver badges5555 bronze badges
What Oracle version are you using? Check this page which refers to Oracle 10:
It says:
The recommended approach to gathering statistics is to allow Oracle to automatically gather the statistics. Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job.
David MedinetsDavid Medinets
2,77022 gold badges1818 silver badges1818 bronze badges
When I was managing a large multi-user planning system backed by Oracle, our DBA had a weekly job that gathered statistics. Also, when we rolled out a significant change that could affect or be affected by statistics, we would force the job to run out of cycle to get things caught up.
Joe SkoraJoe Skora
11.9k44 gold badges3232 silver badges3737 bronze badges
With 10g and higher version of oracle, up to date statistics on tables and indexes are needed by the optimizer to make 'good' execution plan decision. How often you collect statistics is a tricky call. It depends on your application, schema, data rate and business practice. Some third party apps which are written to be backward compatible with older version of oracle do not perform well with the new optimizer. Those application require that tables have no stats so that the db resorts back to rule base execution plan. But on the average oracle recommends that stats be collected on tables with stale statistics. You can set tables to be monitor and check their state and have them analyze if/when stale. Often that is enough, sometime it is not. It really depend on your database. For my database we have a set of OLTP tables that need nightly stats collection to maintain performance. Other tables are analyze once a week. On our large dw database, we analyze as needed as the tables are too large for regular analysis without affecting overall db load and performance. So the correct answer is, it depends on the application, data change and business needs.
MichaelNMichaelN
Make sure to balance the risk that fresh statistics cause undesirable changes to query plans against the risk that stale statistics can themselves cause query plans to change.
Imagine you have a bug database with a table ISSUE and a column CREATE_DATE where the values in the column increase more or less monotonically. Now, assume that there is a histogram on this column that tells Oracle that the values for this column are uniformly distributed between January 1, 2008 and September 17, 2008. This makes it possible for the optimizer to reasonably estimate the number of rows that would be returned if you were looking for all issues created last week (i.e. September 7 - 13). If the application continues to be used and the statistics are never updated, though, this histogram will be less and less accurate. So the optimizer will expect queries for 'issues created last week' to be less and less accurate over time and may eventually cause Oracle to change the query plan negatively.
Justin CaveJustin Cave
195k1919 gold badges306306 silver badges332332 bronze badges
In the case of a data warehouse-type system you can consider collecting no statistics at all, and relying on dynamic sampling (setting optimizer_dynamic_sampling to level 2 or above).
David AldridgeDavid Aldridge
44.6k88 gold badges5252 silver badges8181 bronze badges
Generally it's not recommended to gather statistics so frequent on the whole database unless you have a strong justification for that, such as a bulk insert or big data change happen frequently on the database.gathering statistics on the database in this frequency MAY change the queries execution plan to a new poor execution plans, the thing may cost you much time trying to tune every query affected by the new poor plans, this is why you should test the impact of gathering new statistics on a test database, or in case you don't have the time or the man power for that, at least you should keep a fallback plan by backing up the original statics before you gather new ones, so in case you gather a new statistics and then the queries didn't perform as expected, you can easily restore back the original statistics.
There is a very useful script can help you backup original statistics and gather new ones and provide you with SQL command you can use to restore back the original statics in case the thing didn't go as expected after gathering new statistics. You can find the script in this link:http://dba-tips.blogspot.com/2014/09/script-to-ease-gathering-statistics-on.html
ChionChion
Active1 month ago
Does anyone know how to use VBA to pull data from SAP Netweaver?
I have a number of daily reports that require exporting data from SAP to Excel and formatting it into a report. I have already written working VBA macros that do the formatting. I have to manually extract the data and run each report macro individually. So much time could be saved if my macro could just go into SAP, grab the data for report #1, format it, grab the data for report #2, etc.
I work with SAP NetWeaver (Release 730, Version 7300.1.3.1079). The reports are just Excel pivot tables and charts.
Thanks!
Sandra Rossi
5,34811 gold badge88 silver badges2929 bronze badges
S M PerronS M Perron
18844 gold badges55 silver badges2121 bronze badges
closed as off-topic by Jean-François Corbett, Sam, gunr2171, user1803551, TylerHMay 21 '15 at 13:37![]()
This question appears to be off-topic. The users who voted to close gave this specific reason:
1 Answer
This all depends on what sort of access you have to your SAP system. An ABAP program that exports the data and/or an RFC that your macro can call to directly get the data or have SAP create the file is probably best.
However as a general rule people looking for this sort of answer are looking for an immediate solution that does not require their IT department to spend months customizing their SAP system.
In that case you probably want to use SAP GUI Scripting. SAP GUI scripting allows you to automate the Windows SAP GUI in much the same way as you automate Excel. In fact you can call the SAP GUI directly from an Excel macro. Read up more on it here. The SAP GUI has a macro recording tool much like Excel does. It records macros in VBScript which is nearly identical to Excel VBA and can usually be copied and pasted into an Excel macro directly.
Here is a simple example based on a SAP system I have access to.
To help find the names of elements such as
wnd[1]/tbar[0]/btn[0] you can use script recording.Click the customize local layout button, it probably looks a bit like this:Then find the Script Recording and Playback menu item. Within that the More button allows you to see/change the file that the VB Script is recorded to. The output format is a bit messy, it records things like selecting text, clicking inside a text field, etc.
The provided script should work if copied directly into a VBA macro. It uses late binding, the line
Set SapGuiAuto = GetObject('SAPGUI') defines the SapGuiAuto object.
![]()
If however you want to use early binding so that your VBA editor might show the properties and methods of the objects you are using, you need to add a reference to AndASMAndASM
sapfewse.ocx in the SAP GUI installation folder.
5,24511 gold badge1616 silver badges3131 bronze badges
Not the answer you're looking for? Browse other questions tagged excelvbaexcel-vbasapnetweaver or ask your own question.Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |