Custom3

	Sign In Sign-Up

Latest Theme

What is ERP? Enterprise resource planning software, or ERP, doesn't live up to its acronym. Forget about planningâ€”it doesn't do much of thatâ€”and forget about resource, a throwaway term. But remember the enterprise part. This is ERP's true ambition. It attempts to integrate all departments and functions across a company onto a single computer system that can serve all those different departments' particular needs. That is a tall order, building a single software program that serves the needs of people in finance as well as it does the people in human resources and in the warehouse. Each of those departments typically has its own computer system optimized for the particular ways that the department does its work. But ERP combines them all together into a single, integrated software program that runs off a single database so that the various departments can more easily share information and communicate with each other. That integrated approach can have a tremendous payback if companies install the software correctly. Take a customer order, for example. Typically, when a customer places an order, that order begins a mostly paper-based journey from in-basket to in-basket around the company, often being keyed and rekeyed into different departments' computer systems along the way. All that lounging around in in-baskets causes delays and lost orders, and all the keying into different computer systems invites errors. Meanwhile, no one in the company truly knows what the status of the order is at any given point because there is no way for the finance department, for example, to get into the warehouse's computer system to see whether the item has been shipped. "You'll have to call the warehouse" is the familiar refrain heard by frustrated customers. ERP vanquishes the old standalone computer systems in finance, HR, manufacturing and the warehouse, and replaces them with a single unified software program divided into software modules that roughly approximate the old standalone systems. Finance, manufacturing and the warehouse all still get their own software, except now the software is linked together so that someone in finance can look into the warehouse software to see if an order has been shipped. Most vendors' ERP software is flexible enough that you can install some modules without buying the whole package. Many companies, for example, will just install an ERP finance or HR module and leave the rest of the functions for another day. For an expanded overview of this topic, read the ERP Executive Summary. How can ERP improve a company's business performance? ERP's best hope for demonstrating value is as a sort of battering ram for improving the way your company takes a customer order and processes it into an invoice and revenueâ€”otherwise known as the order fulfillment process. That is why ERP is often referred to as back-office software. It doesn't handle the up-front selling process (although most ERP vendors have recently developed CRM software to do this); rather, ERP takes a customer order and provides a software road map for automating the different steps along the path to fulfilling it. When a customer service representative enters a customer order into an ERP system, he has all the information necessary to complete the order (the customer's credit rating and order history from the finance module, the company's inventory levels from the warehouse module and the shipping dock's trucking schedule from the logistics module, for example). People in these different departments all see the same information and can update it. When one department finishes with the order it is automatically routed via the ERP system to the next department. To find out where the order is at any point, you need only log in to the ERP system and track it down. With luck, the order process moves like a bolt of lightning through the organization, and customers get their orders faster and with fewer errors than before. ERP can apply that same magic to the other major business processes, such as employee benefits or financial reporting. That, at least, is the dream of ERP. The reality is much harsher. Let's go back to those inboxes for a minute. That process may not have been efficient, but it was simple. Finance did its job, the warehouse did its job, and if anything went wrong outside of the department's walls, it was somebody else's problem. Not anymore. With ERP, the customer service representatives are no longer just typists entering someone's name into a computer and hitting the return key. The ERP screen makes them businesspeople. It flickers with the customer's credit rating from the finance department and the product inventory levels from the warehouse. Will the customer pay on time? Will we be able to ship the order on time? These are decisions that customer service representatives have never had to make before, and the answers affect the customer and every other department in the company. But it's not just the customer service representatives who have to wake up. People in the warehouse who used to keep inventory in their heads or on scraps of paper now need to put that information online. If they don't, customer service reps will see low inventory levels on their screens and tell customers that their requested item is not in stock. Accountability, responsibility and communication have never been tested like this before. People don't like to change, and ERP asks them to change how they do their jobs. That is why the value of ERP is so hard to pin down. The software is less important than the changes companies make in the ways they do business. If you use ERP to improve the ways your people take orders, manufacture goods, ship them and bill for them, you will see value from the software. If you simply install the software without changing the ways people do their jobs, you may not see any value at allâ€”indeed, the new software could slow you down by simply replacing the old software that everyone knew with new software that no one does. How long will an ERP project take? Companies that install ERP do not have an easy time of it. Don't be fooled when ERP vendors tell you about a three or six month average implementation time. Those short (that's right, six months is short) implementations all have a catch of one kind or another: The company was small, or the implementation was limited to a small area of the company, or the company used only the financial pieces of the ERP system (in which case the ERP system is nothing more than a very expensive accounting system). To do ERP right, the ways you do business will need to change and the ways people do their jobs will need to change too. And that kind of change doesn't come without pain. Unless, of course, your ways of doing business are working extremely well (orders all shipped on time, productivity higher than all your competitors, customers completely satisfied), in which case there is no reason to even consider ERP. The important thing is not to focus on how long it will takeâ€”real transformational ERP efforts usually run between one and three years, on averageâ€”but rather to understand why you need it and how you will use it to improve your business. What will ERP fix in my business? There are five major reasons why companies undertake ERP. Integrate financial informationâ€”As the CEO tries to understand the company's overall performance, he may find many different versions of the truth. Finance has its own set of revenue numbers, sales has another version, and the different business units may each have their own version of how much they contributed to revenues. ERP creates a single version of the truth that cannot be questioned because everyone is using the same system. Integrate customer order informationâ€”ERP systems can become the place where the customer order lives from the time a customer service representative receives it until the loading dock ships the merchandise and finance sends an invoice. By having this information in one software system, rather than scattered among many different systems that can't communicate with one another, companies can keep track of orders more easily, and coordinate manufacturing, inventory and shipping among many different locations at the same time. Standardize and speed up manufacturing processesâ€”Manufacturing companiesâ€”especially those with an appetite for mergers and acquisitionsâ€”often find that multiple business units across the company make the same widget using different methods and computer systems. ERP systems come with standard methods for automating some of the steps of a manufacturing process. Standardizing those processes and using a single, integrated computer system can save time, increase productivity and reduce head count. Reduce inventoryâ€”ERP helps the manufacturing process flow more smoothly, and it improves visibility of the order fulfillment process inside the company. That can lead to reduced inventories of the stuff used to make products (work-in-progress inventory), and it can help users better plan deliveries to customers, reducing the finished good inventory at the warehouses and shipping docks. To really improve the flow of your supply chain, you need supply chain software, but ERP helps too. Standardize HR informationâ€”Especially in companies with multiple business units, HR may not have a unified, simple method for tracking employees' time and communicating with them about benefits and services. ERP can fix that. In the race to fix these problems, companies often lose sight of the fact that ERP packages are nothing more than generic representations of the ways a typical company does business. While most packages are exhaustively comprehensive, each industry has its quirks that make it unique. Most ERP systems were designed to be used by discrete manufacturing companies (that make physical things that can be counted), which immediately left all the process manufacturers (oil, chemical and utility companies that measure their products by flow rather than individual units) out in the cold. Each of these industries has struggled with the different ERP vendors to modify core ERP programs to their needs. Will ERP fit the ways I do business? It's critical for companies to figure out if their ways of doing business will fit within a standard ERP package before the checks are signed and the implementation begins. The most common reason that companies walk away from multimillion-dollar ERP projects is that they discover the software does not support one of their important business processes. At that point there are two things they can do: They can change the business process to accommodate the software, which will mean deep changes in long-established ways of doing business (that often provide competitive advantage) and shake up important people's roles and responsibilities (something that few companies have the stomach for). Or they can modify the software to fit the process, which will slow down the project, introduce dangerous bugs into the system and make upgrading the software to the ERP vendor's next release excruciatingly difficult because the customizations will need to be torn apart and rewritten to fit with the new version. Needless to say, the move to ERP is a project of breathtaking scope, and the price tags on the front end are enough to make the most placid CFO a little twitchy. In addition to budgeting for software costs, financial executives should plan to write checks to cover consulting, process rework, integration testing and a long laundry list of other expenses before the benefits of ERP start to manifest themselves. Underestimating the price of teaching users their new job processes can lead to a rude shock down the line, and so can failure to consider data warehouse integration requirements and the cost of extra software to duplicate the old report formats. A few oversights in the budgeting and planning stage can send ERP costs spiraling out of control faster than oversights in planning almost any other information system undertaking. What does ERP really cost? Meta Group recently did a study looking at the total cost of ownership (TCO) of ERP, including hardware, software, professional services and internal staff costs. The TCO numbers include getting the software installed and the two years afterward, which is when the real costs of maintaining, upgrading and optimizing the system for your business are felt. Among the 63 companies surveyedâ€”including small, medium and large companies in a range of industriesâ€”the average TCO was million (the highest was million and lowest was ,000). While it's hard to draw a solid number from that kind of range of companies and ERP efforts, Meta came up with one statistic that proves that ERP is expensive no matter what kind of company is using it. The TCO for a "heads-down" user over that period was a staggering ,320. When will I get payback from ERPâ€”and how much will it be? Don't expect to revolutionize your business with ERP. It is a navel-gazing exercise that focuses on optimizing the way things are done internally rather than with customers, suppliers or partners. Yet the navel gazing has a pretty good payback if you're willing to wait for itâ€”a Meta Group study of 63 companies found that it took eight months after the new system was in (31 months total) to see any benefits. But the median annual savings from the new ERP system were $1.6 million. What are the hidden costs of ERP? Although different companies will find different land mines in the budgeting process, those who have implemented ERP packages agree that certain costs are more commonly overlooked or underestimated than others. Armed with insights from across the business, ERP pros vote the following areas as most likely to result in budget overrun. Training Training is the near-unanimous choice of experienced ERP implementers as the most underestimated budget item. Training expenses are high because workers almost invariably have to learn a new set of processes, not just a new software interface. Worse, outside training companies may not be able to help you. They are focused on telling people how to use software, not on educating people about the particular ways you do business. Prepare to develop a curriculum yourself that identifies and explains the different business processes that will be affected by the ERP system. One enterprising CIO hired staff from a local business school to help him develop and teach the ERP business-training course to employees. Remember that with ERP, finance people will be using the same software as warehouse people and they will both be entering information that affects the other. To do this accurately, they have to have a much broader understanding of how others in the company do their jobs than they did before ERP came along. Ultimately, it will be up to your IT and businesspeople to provide that training. So take whatever you have budgeted for ERP training and double or triple it up front. It will be the best ERP investment you ever make. Integration and testing Testing the links between ERP packages and other corporate software links that have to be built on a case-by-case basis is another often-underestimated cost. A typical manufacturing company may have add-on applications from the majorâ€”e-commerce and supply chainâ€”to the minorâ€”sales tax computation and bar coding. All require integration links to ERP. If you can buy add-ons from the ERP vendor that are pre-integrated, you're better off. If you need to build the links yourself, expect things to get ugly. As with training, testing ERP integration has to be done from a process-oriented perspective. Veterans recommend that instead of plugging in dummy data and moving it from one application to the next, run a real purchase order through the system, from order entry through shipping and receipt of paymentâ€”the whole order-to-cash bananaâ€”preferably with the participation of the employees who will eventually do those jobs. Customization Add-ons are only the beginning of the integration costs of ERP. Much more costly, and something to be avoided if at all possible, is actual customization of the core ERP software itself. This happens when the ERP software can't handle one of your business processes and you decide to mess with the software to make it do what you want. You're playing with fire. The customizations can affect every module of the ERP system because they are all so tightly linked together. Upgrading the ERP packageâ€”no walk in the park under the best of circumstancesâ€”becomes a nightmare because you'll have to do the customization all over again in the new version. Maybe it will work, maybe it won't. No matter what, the vendor will not be there to support you. You will have to hire extra staffers to do the customization work, and keep them on for good to maintain it. Data conversion It costs money to move corporate information, such as customer and supplier records, product design data and the like, from old systems to new ERP homes. Although few CIOs will admit it, most data in most legacy systems is of little use. Companies often deny their data is dirty until they actually have to move it to the new client/server setups that popular ERP packages require. Consequently, those companies are more likely to underestimate the cost of the move. But even clean data may demand some overhaul to match process modifications necessitatedâ€”or inspiredâ€”by the ERP implementation. Data analysis Often, the data from the ERP system must be combined with data from external systems for analysis purposes. Users with heavy analysis needs should include the cost of a data warehouse in the ERP budgetâ€”and they should expect to do quite a bit of work to make it run smoothly. Users are in a pickle here: Refreshing all the ERP data every day in a big corporate data warehouse is difficult, and ERP systems do a poor job of indicating which information has changed from day to day, making selective warehouse updates tough. One expensive solution is custom programming. The upshot is that the wise will check all their data analysis needs before signing off on the budget. Consultants ad infinitum When users fail to plan for disengagement, consulting fees run wild. To avoid this, companies should identify objectives for which its consulting partners must aim when training internal staff. Include metrics in the consultants' contract; for example, a specific number of the user company's staff should be able to pass a project-management leadership testâ€”similar to what Big Five consultants have to pass to lead an ERP engagement. Replacing your best and brightest It is accepted wisdom that ERP success depends on staffing the project with the best and brightest from the business and IS divisions. The software is too complex and the business changes too dramatic to trust the project to just anyone. The bad news is a company must be prepared to replace many of those people when the project is over. Though the ERP market is not as hot as it once was, consultancies and other companies that have lost their best people will be hounding yours with higher salaries and bonus offers than you can affordâ€”or that your HR policies permit. Huddle with HR early on to develop a retention bonus program and create new salary strata for ERP veterans. If you let them go, you'll wind up hiring themâ€”or someone like themâ€”back as consultants for twice what you paid them in salaries. Implementation teams can never stop Most companies intend to treat their ERP implementation as they would any other software project. Once the software is installed, they figure the team will be scuttled and everyone will go back to his or her day job. But after ERP, you can't go home again. The implementers are too valuable. Because they have worked intimately with ERP, they know more about the sales process than the salespeople and more about the manufacturing process than the manufacturing people. Companies can't afford to send their project people back into the business because there's so much to do after the ERP software is installed. Just writing reports to pull information out of the new ERP system will keep the project team busy for a year at least. And it is in analysisâ€”and, one hopes, insightâ€”that companies make their money back on an ERP implementation. Unfortunately, few IS departments plan for the frenzy of post-ERP installation activity, and fewer still build it into their budgets when they start their ERP projects. Many are forced to beg for more money and staff immediately after the go-live date, long before the ERP project has demonstrated any benefit. Waiting for ROI One of the most misleading legacies of traditional software project management is that the company expects to gain value from the application as soon as it is installed, while the project team expects a break and maybe a pat on the back. Neither expectation applies to ERP. Most of the systems don't reveal their value until after companies have had them running for some time and can concentrate on making improvements in the business processes that are affected by the system. And the project team is not going to be rewarded until their efforts pay off. Post-ERP depression ERP systems often wreak cause havoc in the companies that install them. In a recent Deloitte Consulting survey of 64 Fortune 500 companies, one in four admitted that they suffered a drop in performance when their ERP system went live. The true percentage is undoubtedly much higher. The most common reason for the performance problems is that everything looks and works differently from the way it did before. When people can't do their jobs in the familiar way and haven't yet mastered the new way, they panic, and the business goes into spasms. Why do ERP projects fail so often? At its simplest level, ERP is a set of best practices for performing different duties in your company, including finance, manufacturing and the warehouse. To get the most from the software, you have to get people inside your company to adopt the work methods outlined in the software. If the people in the different departments that will use ERP don't agree that the work methods embedded in the software are better than the ones they currently use, they will resist using the software or will want IT to change the software to match the ways they currently do things. This is where ERP projects break down. Political fights break out over howâ€”or even whetherâ€”the software will be installed. IT gets bogged down in long, expensive customization efforts to modify the ERP software to fit with powerful business barons' wishes. Customizations make the software more unstable and harder to maintain when it finally does come to life. The horror stories you hear in the press about ERP can usually be traced to the changes the company made in the core ERP software to fit its own work methods. Because ERP covers so much of what a business does, a failure in the software can bring a company to a halt, literally. But IT can fix the bugs pretty quickly in most cases, and besides, few big companies can avoid customizing ERP in some fashionâ€”every business is different and is bound to have unique work methods that a vendor cannot account for when developing its software. The mistake companies make is assuming that changing people's habits will be easier than customizing the software. It's not. Getting people inside your company to use the software to improve the ways they do their jobs is by far the harder challenge. If your company is resistant to change, then your ERP project is more likely to fail. How do I configure ERP software? Even if a company installs ERP software for the so-called right reasons and everyone can agree on the optimal definition of a customer, the inherent difficulties of implementing something as complex as ERP is like, well, teaching an elephant to do the hootchy-kootchy. The packages are built from database tables, thousands of them, that IS programmers and end users must set to match their business processes; each table has a decision "switch" that leads the software down one decision path or another. By presenting only one way for the company to do each taskâ€”say, run the payroll or close the booksâ€”a company's individual operating units and far-flung divisions are integrated under one system. But figuring out precisely how to set all the switches in the tables requires a deep understanding of the existing processes being used to operate the business. As the table settings are decided, these business processes are reengineered, ERP's way. Most ERP systems are not shipped as a shell system in which customers must determine at the minutia level how all the functional procedures should be set, making thousands of decisions that affect how their system behaves in line with their own business activities. Most ERP systems are preconfigured, allowing just hundredsâ€”rather than thousandsâ€”of procedural settings to be made by the customer. How do companies organize their ERP projects? Based on our observations, there are three commonly used ways of installing ERP. The Big Bangâ€”In this, the most ambitious and difficult of approaches to ERP implementation, companies cast off all their legacy systems at once and install a single ERP system across the entire company. Though this method dominated early ERP implementations, few companies dare to attempt it anymore because it calls for the entire company to mobilize and change at once. Most of the ERP implementation horror stories from the late '90s warn us about companies that used this strategy.Getting everyone to cooperate and accept a new software system at the same time is a tremendous effort, largely because the new system will not have any advocates. No one within the company has any experience using it, so no one is sure whether it will work. Also, ERP inevitably involves compromises. Many departments have computer systems that have been honed to match the ways they work. In most cases, ERP offers neither the range of functionality nor the comfort of familiarity that a custom legacy system can offer. In many cases, the speed of the new system may suffer because it is serving the entire company rather than a single department. ERP implementation requires a direct mandate from the CEO. Franchising strategyâ€”This approach suits large or diverse companies that do not share many common processes across business units. Independent ERP systems are installed in each unit, while linking common processes, such as financial bookkeeping, across the enterprise. This has emerged as the most common way of implementing ERP. In most cases, the business units each have their own "instances" of ERPâ€”that is, a separate system and database. The systems link together only to share the information necessary for the corporation to get a performance big picture across all the business units (business unit revenues, for example), or for processes that don't vary much from business unit to business unit (perhaps HR benefits). Usually, these implementations begin with a demonstration or pilot installation in a particularly open-minded and patient business unit where the core business of the corporation will not be disrupted if something goes wrong. Once the project team gets the system up and running and works out all the bugs, the team begins selling other units on ERP, using the first implementation as a kind of in-house customer reference. Plan for this strategy to take a long time. Slam dunkâ€”ERP dictates the process design in this method, where the focus is on just a few key processes, such as those contained in an ERP system's financial module. The slam dunk is generally for smaller companies expecting to grow into ERP. The goal here is to get ERP up and running quickly and to ditch the fancy reengineering in favor of the ERP system's "canned" processes. Few companies that have approached ERP this way can claim much payback from the new system. Most use it as an infrastructure to support more diligent installation efforts down the road. Yet many discover that a slammed-in ERP system is little better than a legacy system because it doesn't force employees to change any of their old habits. In fact, doing the hard work of process reengineering after the system is in can be more challenging than if there had been no system at all because at that point few people in the company will have felt much benefit. How does ERP fit with e-commerce? ERP vendors were not prepared for the onslaught of e-commerce. ERP is complex and not intended for public consumption. It assumes that the only people handling order information will be your employees, who are highly trained and comfortable with the tech jargon embedded in the software. But now customers and suppliers are demanding access to the same information your employees get through the ERP systemâ€”things like order status, inventory levels and invoice reconciliationâ€”except they want to get all this information simply, without all the ERP software jargon, through your website. E-commerce means IT departments need to build two new channels of access in to ERP systemsâ€”one for customers (otherwise known as business-to-consumer) and one for suppliers and partners (business-to-business). These two audiences want two different types of information from your ERP system. Consumers want order status and billing information, and suppliers and partners want just about everything else. Traditional ERP vendors are having a hard time building the links between the Web and their software, though they certainly all realize that they must do it and have been hard at work at it for years. The bottom line, however, is that companies with e-commerce ambitions face a lot of hard integration work to make their ERP systems available over the Web. For those companies that were smartâ€”or luckyâ€”enough to have bought their ERP systems from a vendor experienced in developing e-commerce wares, adding easily integrated applications from that same vendor can be a money-saving option. For those companies whose ERP systems came from vendors that are less experienced with e-commerce development, the bestâ€”and possibly onlyâ€”option might be to have a combination of internal staff and consultants hack through a custom integration. But no matter what the details are, solving the difficult problem of integrating ERP and e-commerce requires careful planning, which is key to getting integration off on the right track. One of the most difficult aspects of ERP and e-commerce integration is that the Internet never stops. ERP applications are big and complex and require maintenance. The choice is stark if ERP is linked directly to the Webâ€”take down your ERP system for maintenance and you take down your website. Most e-commerce veterans will build flexibility into the ERP and e-commerce links so that they can keep the new e-commerce applications running on the Web while they shut down ERP for upgrades and fixes. The difficulty of getting ERP and e-commerce applications to work togetherâ€”not to mention the other applications that demand ERP information such as supply chain and CRM softwareâ€”has led companies to consider software known alternately as middleware and EAI software. These applications act as software translators that take information from ERP and convert it into a format that e-commerce and other applications can understand. Middleware has improved dramatically in recent years, and though it is difficult to sell and prove ROI on the software with business leadersâ€”it is invisible to computer usersâ€”it can help solve many of the biggest integration woes that plague IT these days. SAP - The Basics Series Article 1 Who and/or what is SAP? How popular is it? Wow! (Updated July 2002) SAP the company was founded in Germany in 1972 by five ex-IBM engineers. In case youâ€™re ever asked, SAP stands for Systeme, Andwendungen, Produkte in der Datenverarbeitung which - translated to English - means Systems, Applications, Products in Data Processing. So now you know! Being incorporated in Germany, the full name of the parent company is SAP AG. It is located in Walldorf, Germany which is close to the beautiful town of Heidelberg. SAP has subsidiaries in over 50 countries around the world from Argentina to Venezuela (and pretty much everything in between). SAP America (with responsibility for North America, South America and Australia - go figure!) is located just outside Philadelphia, PA. The original five founders have been so successful that they have multiplied many times over such that SAP AG is now the third largest software maker in the world, with over 17,500 customers (including more than half of the world's 500 top companies). SAP employs over 27,000 people worldwide today, and had revenues of .34 billion and Net Income of million in FY01. SAP is listed in Germany (where it is one of the 30 stocks which make up the DAX) and on the NYSE (ticker:SAP). There are now 44,500 installations of SAP, in 120 countries, with more then 10 million users! So what made this company so successful? Back in 1979 SAP released SAP R/2 (which runs on mainframes) into the German market. SAP R/2 was the first integrated, enterprise wide package and was an immediate success. For years SAP stayed within the German borders until it had penetrated practically every large German company. Looking for more growth, SAP expanded into the remainder of Europe during the 80's. Towards the end of the 80's, client-server architecture became popular and SAP responded with the release of SAP R/3 (in 1992). This turned out to be a killer app for SAP, especially in the North American region into which SAP expanded in 1988. The success of SAP R/3 in North America has been nothing short of stunning. Within a 5 year period, the North American market went from virtually zero to 44% of total SAP worldwide sales. SAP America alone employs more than 3,000 people and has added the names of many of the Fortune 500 to itâ€™s customer list (8 of the top 10 semiconductor companies, 7 of the top 10 pharmaceutical companies etc). SAP today is available in 46 country-specific versions, incorporating 28 languages including Kanji and other double-byte character languages. SAP also comes in 21 industry-specific versions. SAP R/3 is delivered to a customer with selected standard process turned on, and many many other optional processes and features turned off. At the heart of SAP R/3 are about 10,000 tables which control the way the processes are executed. Configuration is the process of adjusting the settings of these tables to get SAP to run the way you want it to. Think of a radio with 10,000 dials to tune and youâ€™ll get the picture. Functionality included is truly enterprise wide including: Financial Accounting (e.g. general ledger, accounts receivable etc), Management Accounting (e.g. cost centers, profitability analysis etc), Sales, Distribution, Manufacturing, Production Planning, Purchasing, Human Resources, Payroll etc etc etc. For a full description of the modules included in SAP, see the related articles. All of these modules are tightly integrated which â€“ as you will find out â€“ is a huge blessing ... but brings with it special challenges. SAP are maintaining and increasing their dominance over their competitors through a combination of - embracing the internet with mySAP.com (a confusing name we believe) to head off i2 etc - extending their solutions with CRM to head off Siebel - adding functionality to their industry solutions Article 2 Who is it made for? Why might I need it? We have all heard about the large (and very large) companies who have implemented (or are still busy implementing) SAP R/3. But SAP is gaining acceptance by smaller companies too. There are many reasons a company selects and implements SAP â€“ some are good and some are bad. The good ones include replacing an out-dated and inefficient IT Architecture (including the CIOâ€™s nemesis â€¦ the burning platform), enabling business process change, and to gain competitive advantage. The bad ones are too numerous to go into here but would include the "why are we the only semiconductor company without SAP" question. More on the good reasons follows: 1. Replacing an out-dated and inefficient IT Architecture: In the beginning, computer systems were developed by individual departments to satisfy the requirements of that particular department. When someone finally realized that benefits could be had by linking these systems together, interface heaven was born. There are some companies today with literally thousands of interfaces, each of which needs to be maintained (assuming of course that there is someone around who understands how they work!). Sweeping them away and replacing them with an integrated system such as SAP can save much money in support. Of course, if you have a burning platform as well the question becomes even easier. 2. Enabling business process change â€“ From the start, SAP was built on a foundation of process best practices. Although it sounds absurd, it is probably easier (and less expensive) to change your companies processes to adapt to SAP than the other way around. Many companies have reported good success from combining a SAP implementation with a BPR project. 3. Competitive advantage â€“ The CFO types around have heard this old saying from the CIO types for many years now. The question still has to be asked â€¦ can you gain competitive advantage from implementing SAP? The answer, of course, depends on the company. It seems to us, however, that: ï‚· being able to accurately provide delivery promise dates for manufactured products (and meet them) doesnâ€™t hurt ... and ï‚· being able to consolidate purchase decisions from around the globe and use that leverage when negotiating with vendors has gotta help â€¦ and ï‚· being able to place kiosks in stores where individual customers can enter their product specifications and then feed this data directly into itâ€™s production planning process is pretty neat ï‚· etc etc Article 3 How much does it cost? What will it take to implement it? Wow! There is a defining moment in the journey of all companies on the road to SAP nirvana. This moment comes just after the company has concluded that it wantâ€™s SAP, it needs SAP, itâ€™s gotta have SAP â€¦ then comes the question â€˜so what does it take to implement itâ€™? This is the question which separates those who are ready from the wannabees. Before being accused of being too negative, let me remind you that at the heart of every good business decision lies a cost benefit analysis. If this cannot be complete with a positive outcome, the initiative (whatever it is) should probably not be launched. Same goes for a SAP implementation. Implementing SAP is expensive. No doubt about it. But the potential rewards can dwarf the costs (and have for many existing customers already). One customer reportedly made enough savings on the procurement of a single raw material to pay for the entire enterprise-wide SAP implementation! Of course these are hard to substantiate, but visit SAPâ€™s website and take a look at the customer testimonials. SAP sells itâ€™s R/3 product on a â€˜price per user basisâ€™. The actual price is negotiated between SAP and the customer and therefore depends on numerous factors which include number of users and modules (and other factors which are present in any negotiation). You should check with SAP, but for a ballpark planning number you could do worse than starting with per user. There is also an annual support cost of about 10% which includes periodic upgrades. Again, check with SAP. Then there is the implementation cost. Yowser. It is about now that you need to get the business case out again and remind yourself why you need to do this. The major drivers of the total implementation cost are the Timeframe, Resource Requirements and Hardware. 1. Timeframe - The absolute quickest implementation we have ever heard of is 45 days â€¦ but this was for a tiny company with very few users and no changes to the delivered SAP processes. At the other end of the scale you get the multi-nationals who are implementing SAP over 5 to 10 years. These are not necessarily failures â€¦ many of them are planned as successive global deployments (which seem to roll around the globe forever). Of course the really expensive ones are those we donâ€™t hear about! For the most part, you should be able to get your (single instance) project completed in a 9 to 18 month period. 2. People â€“ The smallest of SAP implementations can get done on a part-time basis without outside help. The largest swallow up hundreds of people (sometimes over a thousand) and include whole armies of consultants. This adds up fast. Again, get that business case out. The types of people you will need run the range from heavy duty techies to project managers. 3. Hardware â€“ The smallest of SAP implementations probably use only three instances (boxes) â€¦ one for the production system, one for test, and one for development. The largest implementations have well over 100 instances, especially if they involve multiple parallel projects (otherwise known as a program). Adding all this up, your SAP project can run anywhere from ,000 to hundreds of millions of $$$â€™s. As you can see, SAP can be all things to all companies â€¦ so itâ€™s best to talk to them (or your consulting firm) about your specific requirements. Article 4 Is there any help out there? What should I do next? Help From SAP AG There is a ton of help available out there - depending on your companies budget and culture â€“ to help you along your journey beginning with your strategy and ending up when you reach that hallowed (and sometimes distant) ground of post-implementation. This article concentrates on the help available from SAP AG. Article 5 discusses other sources of help. SAP AG As you read in article 1 of this series, SAP AG employs around 22,000 people. Although they re-organize as often as most other companies, you can think of them as being organized into the following four areas: Pre-Sales, Consulting, Training and Developers. ï‚· Pre-Sales. These are people with heavy-duty functional knowledge of one or more SAP modules and one or more industries. They give really excellent system demonstrations on particular areas of the system which â€“ while thick with pre-sales features â€“ are an extremely valuable source of information about SAP. Iâ€™m sure they have many other responsibilities as well, but if you can, get a demo from them. For an even more useful demo, ask if you can provide them with business process scenarios that are pertinent to your business or industry prior to the demo. ï‚· Consulting. While also knowledgeable in SAP (of course), these are mostly consulting types like those that can be found in the major consulting firms. Often a team will consist of consultants from SAP and a partner consulting firm and you will not know the difference. Expect them to have business process and/or industry knowledge in addition to detailed SAP knowledge. They are not readily available to non-customers as they are usually assigned to one or more customers. A good list of consulting partners is available in the links section of this website. ï‚· Training. In 1999 SAP opened up their training programs to non-customers and non-partners. This opens up a whole world of top-rate training programs at SAPâ€™s facilities around the globe. These can be expensive, however, and up to three weeks are usually required to gain a sufficiently deep understanding of a particular module or subject. If you have lots of time and money, you could register for one of SAPâ€™s â€˜academiesâ€™ which are five-week crash courses (emphasis on crash â€¦ as in burn) in one of the following areas: FI/CO, MM/SD, and HR/ABAP. These end with an examination and 'certification' in your chosen area. More information on SAP training courses can be found on SAPâ€™s website. ï‚· Developers. These heavy-duty techies are off limits to non-customers. Customers can sometimes get a message to them via the OSS system â€“ which is an automated trouble ticket type system. If you ever actually see one, or have one on the phone, ask all the questions you can think of, as you may never have the chance again! Article 5 Is there any help out there? What should I do next? Help From Other Sources Article 4 in this series covered the help that is available from SAP AG. Here we will cover the help that is available from other sources, including: Consulting Companies, SAPPHIRE and other SAP Events, ASUG, and this website. 1. Consulting Companies One of SAPâ€™s key strategies has been to develop partnerships with the Consulting Companies. This has contributed enormously to the widespread adoption of SAP due to the fact that there are literally thousands of consultants (SAP estimate 55,000) ready to help with all aspects of your SAP implementation â€¦ from strategy to completion. There are two types of consulting partners: 1. Global consulting partners (13 of these at last count) are the largest of the consulting firms who are able to provide global assistance to global companies, and 2. National consulting partners who are accredited by country Your need of a consulting partner depends on your project scope and complexity, your project budget, company culture, and prior SAP implementation experience in your company. Suffice to say that without heavy prior SAP experience in your company, all but the simplest SAP implementations would benefit from the involvement of experienced individuals who have done it before. Rates depend on your negotiations with the consulting company, of course, but you could do worse than use an estimate of per person per hour. Consulting styles differ from firm to firm, so make sure your company culture is compatible with the typical approach of your chosen consulting partner. In addition, spend some time on their websites to get an idea of their approach, experience and capabilities. 2. SAPPHIRE and other SAP events SAPPHIRE is the name given to SAPâ€™s annual user conference. Multiple SAPPHIREâ€™s can be found around the globe each year, and are usually sold out in advance. North American SAPPHIRE's are typically held in hot cities (off season) and attract upwards of 14,000 prospects, customers and partners. Read up on SAPPHIREâ€™99 here. SAPPHIRE is a great place to go explore, but is quite expensive at around ,200 for three days (food, lodging, travel etc is at your own expense). Even so, it is well worth the time and expense. Note: SAP holds other events throughout the year (TechEd, for example, is aimed at the more technical users) See their website for additional details. 3. ASUG (Americaâ€™s SAP User Group) As the name suggests, ASUG is a forum for users of SAP. Non-users (prospects and consultants) and not usually found lurking here. ASUG actually comprises of multiple sub-ASUGâ€™s â€“ each focusing on a particular area of SAP, for example there is an ASUG for High Tech companies, and an ASUG for companies using ALE etc. Leadership of these sub-ASUGâ€™s (for lack of a better description) usually rotates between members of the user community. ASUG provides opportunities for networking, learning and influencing SAP (for example joining forces with other users to convince SAP to include a particular modification in their standard software). In addition to meetings within the sub-ASUGâ€™s, there is an annual conference (which attracted nearly 6,000 users and vendors in 1999). More details on ASUG can be found at www.asug.com. 4. This website thespot4sap.com was created with a single objective in mind â€¦ to provide an online community where people involved in SAP implementations can share SAP knowledge. This will, we hope, lead to an environment where we can all learn more about SAP, and hopefully teach others along the way. We are proud to be independent of any other company, and current. There are many avenues available for you to expand your knowledge here â€¦ you can sign up for our newsletter, read more articles, catch up on SAP-related news, lurk on the discussion boards, follow the links, post your resume, find a job, vote in our polls, buy a book, buy a CBT, buy a premium paper (etc etc). If you have a specific question in mind, try our search facility or post it to the messageboard. If you have read all five of the articles in this series, well done! If you have the time, we would really appreciate some feedback. SAP Modules and Solutions Overview (November 2002) In the past when people were discussing SAP, the conversation very quickly boiled down to modules, for example: ï‚· SAPâ€™s courses were structured along module lines so that you would attend MM 101, 102 and 103. While at the course you would learn many things about MM, but not much about the rest of the SAP system and how MM fits into it. ï‚· A conversation with a SAP recruiter might go something like this: Recruiter â€¦ â€œwhich modules do you work with?â€ Candidate â€¦ â€œwell, I have a lot of purchasing process experienceâ€ Recruiter â€¦ â€œyes, but which modules do you work with?â€ Candidate â€¦ â€œwell, itâ€™s purchasing functionality â€¦ so that would be, umm, MM, FI and CO mainlyâ€ Recruiter â€¦ â€œgreat I have just the job for youâ€ Candidate â€¦ â€œfantastic, is it purchasing?â€ Recruiter â€¦ â€œwell it says here that they want an MM, FI and CO person and thatâ€™s you, right?â€ Not necessarily! A MM, FI, CO role might include Inventory Management (MM), Accounts Receivable (FI) and Profitability Analysis (CO) â€“ none of which a purchasing person is guaranteed to have ï‚· Many programme teams were organised along module lines, so that you would have a FI/CO, an MM and a HR team, for example. Training courses were (therefore) often prepared and delivered along module lines too. The result of this was that solutions were frequently optimised along module lines, and less often well integratred, and as for users, well, they were pretty much trained up in a module and left to get on with it post go-live. Fortunately those days are mostly passed, and more and more programmes (from design to build to training) are being organised along process lines such as: Order to Cash (including parts of SD, FI-AR and probably TY as well) Purchase to Pay (including MM-Purchasing and FI-AP) Record to Report (FI-GL etc) SAP now are moving away from describing their system as a set of modules, and now are using the term â€˜solutionsâ€™, which is much better. If you visit SAPâ€™s website (as we urge you to do) you will find that they have structured their Solutions tab as follows: ï‚· Financials ï‚· Human Resources ï‚· Customer Relationship Management ï‚· Supplier Relationship Management ï‚· Product Lifecycle Management ï‚· Supply Chain Management ï‚· Business Intelligence We think that this is a quite intuitive way of breaking it down, and a big leap forward for SAP. Of course, most recently (this year) SAP have unveiled their latest initiative which is Xapps â€“ but that is the subject of a yet to be written article (as soon as we understand it ourselves, that is). If youâ€™re still looking for that list of modules, here they are: FI Financial Accounting â€“ essentially your regulatory â€˜books of recordâ€™, including ï‚· General ledger ï‚· Book close ï‚· Tax ï‚· Accounts receivable ï‚· Accounts payable ï‚· Consolidation ï‚· Special ledgers CO Controlling â€“ basically your internal cost/management accounting, including ï‚· Cost elements ï‚· Cost centres ï‚· Profit centres ï‚· Internal orders ï‚· Activity based costing ï‚· Product costing AM Asset Management â€“ track, value and depreciate your assets, including ï‚· Purchase ï‚· Sale ï‚· Depreciation ï‚· Tracking PS Project Systems â€“ manage your projects, large and small, including ï‚· Make to order ï‚· Plant shut downs (as a project) ï‚· Third party billing (on the back of a project) HR Human Resources â€“ ah yes, people, including ï‚· Employment history ï‚· Payroll ï‚· Training ï‚· Career management ï‚· Succession planning PM Plant Maintenance â€“ maintain your equipment (e.g. a machine, an oil rig, an aircraft etc), including ï‚· Labour ï‚· Material ï‚· Down time and outages MM Materials Management â€“ underpins the supply chain, including ï‚· Requisitions ï‚· Purchase orders ï‚· Goods receipts ï‚· Accounts payable ï‚· Inventory management ï‚· BOMâ€™s ï‚· Master raw materials, finished goods etc QM Quality Management â€“ improve the quality of your goods, including ï‚· Planning ï‚· Execution ï‚· Inspections ï‚· Certificates PP Production Planning â€“ manages your production process, including ï‚· Capacity planning ï‚· Master production scheduling ï‚· Material requirements planning ï‚· Shop floor SD Sales and Distribution â€“ from order to delivery, including ï‚· RFQ ï‚· Sales orders ï‚· Pricing ï‚· Picking (and other warehouse processes) ï‚· Packing ï‚· Shipping CA Cross Application â€“ these lie on top of the individual modules, and include ï‚· WF â€“ workflow ï‚· BW â€“ business information warehouse ï‚· Office â€“ for email ï‚· Workplace ï‚· Industry solutions ï‚· New Dimension products such as CRM, PLM, SRM, APO etc Introduction Defining a Relational Database DATABASE- A database is a collection of data that is organized so that its contents can easily be accessed, managed, and updated. The most prevalent type of database is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. RDBMS-Acronym for Relational Database Management System. A database that allows the definition of data structures, storage and retrieval operations, and integrity constraints. In such a database, data and relations between them are organized in tables. SQL- (Structured Query Language) is a standard interactive and programming language for getting information from and updating a database. Although SQL is both an ANSI and an ISO standard, many database products support SQL with proprietary extensions to the standard language. Queries take the form of a command language that lets you select, insert, update, find out the location of data, and so forth. There is also a programming interface. Discussing the Theoretical, Conceptual, and Physical Aspects of a Relational Database A relational database is a set of tables containing data fitted into predefined categories. Each table (which is sometimes called a relation) contains one or more data categories in columns. Each row contains a unique instance of data for the categories defined by the columns. For example, a typical business order entry database would include a table that described a customer with columns for name, address, phone number, and so forth. Another table would describe an order: product, customer, date, sales price, and so forth. A user of the database could obtain a view of the database that fitted the user's needs. For example, a branch office manager might like a view or report on all customers that had bought products after a certain date. A financial service manager in the same company could, from the same tables, obtain a report on accounts that needed to be paid. When creating a relational database, you can define the domain of possible values in a data column and further constraints that may apply to that data value. For example, a domain of possible customers could allow up to ten possible customer names but be constrained in one table to allowing only three of these customer names to be specifiable. Using the Data Dictionary DATA DICTIONARY -A set of tables and views owned by the database administrator. It is a central source of information for Oracle Server and other relational databases.Data dictionary can be used to retrieve useful information about database. i.e. to retrieve tablespace information we can take help from DBA_TABLESPACES data dictionary table : Structure of DBA_TABLESPACES is: Name Null? Type ------------------------------- -------- ---- TABLESPACE_NAME NOT NULL VARCHAR2(30) INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NOT NULL NUMBER MAX_EXTENTS NOT NULL NUMBER PCT_INCREASE NUMBER MIN_EXTLEN NUMBER STATUS VARCHAR2(9) CONTENTS VARCHAR2(9) LOGGING VARCHAR2(9) EXTENT_MANAGEMENT VARCHAR2(10) ALLOCATION_TYPE VARCHAR2(9) PLUGGED_IN VARCHAR2(3) 1 SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,PCT_INCREASE 2* FROM DBA_TABLESPACES TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE ------------------------------ -------------- ----------- ----------- ----------- ------------ SYSTEM 10240 10240 1 121 50 USERS 10240 10240 1 121 50 RBS 10240 10240 1 121 50 TEMP 10240 10240 1 121 50 OEM_REPOSITORY 10240 10240 1 121 50 INDX 10240 10240 1 121 50 BILLING 10240 10240 1 121 50 MYTABLE 10240 10240 1 121 50 SOHEL_TABLE_SPACE 10240 10240 1 121 50 USER_DATA 10240 10240 1 200 0 TEMPORARY_DATA 10240 10240 1 121 50 HBFC 10240 10240 1 121 50 HATIL_TS 10240 10240 1 121 50 Some Common Data Dictionary Tables/Views DBA_CATALOG-All database Tables, Views, Synonyms, Sequences DBA_COL_COMMENTS-Comments on columns of all tables and views DBA_CONSTRAINTS-Constraint definitions on all tables DBA_CONS_COLUMNS-Information about accessible columns in constraint definitions DBA_DATA_FILES-Information about database data files DBA_EXTENTS-Extents comprising all segments in the database DBA_FREE_SPACE-Free extents in all tablespaces DBA_INDEXES-Description for all indexes in the database DBA_IND_COLUMNS-COLUMNs comprising INDEXes on all TABLEs and CLUSTERs DBA_OBJECTS-All objects in the database DBA_ROLES-All Roles which exist in the database DBA_ROLE_PRIVS-Roles granted to users and roles DBA_ROLLBACK_SEGS-Description of rollback segments DBA_SEGMENTS-Storage allocated for all database segments DBA_SEQUENCES-Description of all SEQUENCEs in the database DBA_TABLESPACES-Description of all tablespaces DBA_TAB_COLUMNS-Columns of user's tables, views and clusters DBA_TAB_COMMENTS-Comments on all tables and views in the database DBA_TAB_PRIVS-All grants on objects in the database DBA_TEMP_FILES-Information about database temp files Managing Tablespaces and Datafiles Creating and managing tablespaces Tablespaces A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application's objects to simplify some administrative operations. CREATE TABLESPACE USER_DATA DATAFILE 'L:\USER_DATA01.DBF' SIZE 102400 K NOLOGGING DEFAULT STORAGE( INITIAL 10 K NEXT 10 K MINEXTENTS 1 MAXEXTENTS 200 PCTINCREASE 0 ) ONLINE PERMANENT; To increase the size of the tablespace we can add more datafile/s ALTER TABLESPACE USER_DATA ADD DATAFILE 'L:\USER_DATA02.DBF' SIZE 1024K; To Modify Default Storage Clause ALTER TABLESPACE USER_DATA DEFAULT STORAGE( NEXT 20K) Obtaining tablespace and datafile information To obtain tablespace and datafile information we can take help from the following data dictionary views/tables DBA_DATA_FILES Information about database data files DBA_TABLESPACES Description of all tablespaces DBA_DATA_FILES FILE_NAME Name of the database data file FILE_ID ID of the database data file TABLESPACE_NAME Name of the tablespace to which the file belongs BYTES Size of the file in bytes BLOCKS Size of the file in ORACLE blocks STATUS File status: "INVALID" or "AVAILABLE" RELATIVE_FNO Tablespace-relative file number AUTOEXTENSIBLE Autoextensible indicator: "YES" or "NO" MAXBYTES Maximum size of the file in bytes MAXBLOCKS Maximum size of the file in ORACLE blocks INCREMENT_BY Default increment for autoextension USER_BYTES Size of the useful portion of file in bytes USER_BLOCKS Size of the useful portion of file in ORACLE blocks DBA_TABLESPACES TABLESPACE_NAME Tablespace name INITIAL_EXTENT Default initial extent size NEXT_EXTENT Default incremental extent size MIN_EXTENTS Default minimum number of extents MAX_EXTENTS Default maximum number of extents PCT_INCREASE Default percent increase for extent size MIN_EXTLEN Minimum extent size for the tablespace STATUS Tablespace status: "ONLINE", "OFFLINE", or "READ ONLY" CONTENTS Tablespace contents: "PERMANENT", or "TEMPORARY" LOGGING Default logging attribute EXTENT_MANAGEMENT Extent management tracking: "DICTIONARY" or "LOCAL" ALLOCATION_TYPE Type of extent allocation in effect for this tablespace To obtain information about datafile/s for a specific tablespace SELECT TABLESPACE_NAME,FILE_NAME,BYTES,BLOCKS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='USER_DATA' TABLESPACE_NAME FILE_NAME BYTES BLOCKS ------------------------------ ---------------------------------------- --------- --------- USER_DATA L:\USER_DATA01.DBF 52428800 25600 L:\USER_DATA04.DBF 104857600 51200 L:\USER_DATA02.DBF 104857600 51200 To determine used space & free space for a specific tablespace we can take help from DBA_FREE_SPACE data dictionary view TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ -------------------------------------- DBA_FREE_SPACE TABLESPACE_NAME Name of the tablespace containing the extent FILE_ID ID number of the file containing the extent BLOCK_ID Starting block number of the extent BYTES Size of the extent in bytes BLOCKS Size of the extent in ORACLE blocks RELATIVE_FNO Relative number of the file containing the extent Find total free space for a specific tablespace : 1 SELECT FILE_ID , SUM(BYTES) 2 FROM DBA_FREE_SPACE 3 WHERE TABLESPACE_NAME='USER_DATA' 4* GROUP BY FILE_ID FILE_ID SUM(BYTES) --------- ---------- 14 42907648 26 102293504 27 102930432 Find total space allocated for a specific tablespace 1 SELECT FILE_ID,BYTES 2 FROM DBA_DATA_FILES 3 WHERE TABLESPACE_NAME='USER_DATA'; FILE_ID BYTES --------- --------- 14 52428800 26 104857600 27 104857600 Find total used space , free space , total space allocated for specific tablespace (By Single SELECT statement)? To obtain information about temporary tablespace we can take help from DBA_TEMP_FILES data dictionary view TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ -------------------------------------- DBA_TEMP_FILES FILE_NAME Name of the database temp file FILE_ID ID of the database temp file TABLESPACE_NAME Name of the tablespace to which the file belongs BYTES Size of the file in bytes BLOCKS Size of the file in ORACLE blocks STATUS File status: "AVAILABLE" RELATIVE_FNO Tablespace-relative file number AUTOEXTENSIBLE Autoextensible indicator: "YES" or "NO" MAXBYTES Maximum size of the file in bytes MAXBLOCKS Maximum size of the file in ORACLE blocks INCREMENT_BY Default increment for autoextension USER_BYTES Size of the useful portion of file in bytes USER_BLOCKS Size of the useful portion of file in ORACLE blocks To obtain information about rollback segments we can take help from DBA_ROLLBACK_SEGS data dictionary views TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ -------------------------------------- DBA_ROLLBACK_SEGS SEGMENT_NAME Name of the rollback segment OWNER Owner of the rollback segment TABLESPACE_NAME Name of the tablespace containing the rollback segent SEGMENT_ID ID number of the rollback segment FILE_ID ID number of the file containing the segment header BLOCK_ID ID number of the block containing the segment header INITIAL_EXTENT Initial extent size in bytes NEXT_EXTENT Secondary extent size in bytes MIN_EXTENTS Minimum number of extents MAX_EXTENTS Maximum number of extents PCT_INCREASE Percent increase for extent size STATUS Rollback segment status INSTANCE_NUM Rollback segment owning parallel server instance number RELATIVE_FNO Relative number of the file containing the segmentheader Storage Structures and Relationships Describing the logical structure of the database (segments, extents, blocks) Data Blocks, Extents, and Segments Oracle Data Blocks At the finest level of granularity, an Oracle database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. A data block size is specified for each Oracle database when the database is created. A database uses and allocates free database space in Oracle data blocks. Extents The next level of logical database space is called an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information. Segments The level of logical database storage above an extent is called a segment. A segment is a set of extents allocated for a certain logical structure The Relationships Among Data Blocks, Extents, and Segments Listing the segment types and their uses - Data Segment - Index Segment - Temporary Segment - Rollback Segment Managing Tables Creating and maintaining tables using appropriate storage settings Table: A named collection of related information, stored in a relational database or server, in a two-dimensional grid that is made up of rows and columns. CREATE TABLE EMP ( EMPNO NUMBER (4) NOT NULL, ENAME VARCHAR2 (10), JOB VARCHAR2 (9), MGR NUMBER (4), HIREDATE DATE, SAL NUMBER (7,2), COMM NUMBER (7,2), DEPTNO NUMBER (2), CONSTRAINT PK_EMP_TEST PRIMARY KEY ( EMPNO ) USING INDEX TABLESPACE INDX PCTFREE 10 STORAGE ( INITIAL 10240 NEXT 10240 PCTINCREASE 50 )) TABLESPACE USERS PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 10240 NEXT 10240 PCTINCREASE 50 MINEXTENTS 1 MAXEXTENTS 121) NOCACHE; Table structure/space clause can be modified later Some parameters can be modified by altering the table: ALTER TABLE EMP PCTFREE 20; Some storage Parameter can be modified: ALTER TABLE EMP STORAGE(NEXT 5120); But INITIAL , MINEXTENTS are not allowed to modify : ALTER TABLE EMP STORAGE(INITIAL 5120) ORA-02203: INITIAL storage options not allowed ALTER TABLE EMP STORAGE(MINEXTENTS 2) ORA-01570: MINEXTENTS must be no larger than the 1 extents currently allocated [ If you specify STORAGE parameters during TABLE creation the DEFAULT STORAGE parameters of the TABLESPACE will be ignored ] To add a column to an existing table: ALTER TABLE EMP ADD BONUS NUMBER(7,2) / To convert a null column to not null column : ALTER TABLE EMP MODIFY SAL NOT NULL / Obtaining table information: To obtain information about a table the following data dictionary views can be queried : DBA_TABLES Description of all relational tables in the database DBA_TAB_COLUMNS Columns of user's tables, views and clusters TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ -------------------------------------- DBA_TABLES SAMPLE_SIZE The sample size used in analyzing this table LAST_ANALYZED The date of the most recent time this table was an alyzed PARTITIONED Is this table partitioned? YES or NO IOT_TYPE If index-only table, then IOT_TYPE is IOT or IOT_O VERFLOW else NULL TEMPORARY Can the current session only see data that it place in this object itself? SECONDARY Is this table object created as part of icreate for domain indexes? NESTED Is the table a nested table? BUFFER_POOL The default buffer pool to be used for table blocks ROW_MOVEMENT Whether partitioned row movement is enabled or disabled GLOBAL_STATS Are the statistics calculated without merging underlying partitions? USER_STATS Were the statistics entered directly by the user? DURATION If temporary table, then duration is sys or sys else NULL SKIP_CORRUPT Whether skip corrupt blocks is enabled or disabled MONITORING Should we keep track of the amount of modification? OWNER Owner of the table TABLE_NAME Name of the table TABLESPACE_NAME Name of the tablespace containing the table CLUSTER_NAME Name of the cluster, if any, to which the table belongs IOT_NAME Name of the index-only table, if any, to which the overflow entry belongs PCT_FREE Minimum percentage of free space in a block PCT_USED Minimum percentage of used space in a block INI_TRANS Initial number of transactions MAX_TRANS Maximum number of transactions INITIAL_EXTENT Size of the initial extent in bytes NEXT_EXTENT Size of secondary extents in bytes MIN_EXTENTS Minimum number of extents allowed in the segment MAX_EXTENTS Maximum number of extents allowed in the segment PCT_INCREASE Percentage increase in extent size FREELISTS Number of process freelists allocated in this segment FREELIST_GROUPS Number of freelist groups allocated in this segment LOGGING Logging attribute BACKED_UP Has table been backed up since last modification? NUM_ROWS The number of rows in the table BLOCKS The number of used blocks in the table EMPTY_BLOCKS The number of empty (never used) blocks in the table AVG_SPACE The average available free space in the table CHAIN_CNT The number of chained rows in the table AVG_ROW_LEN The average row length, including row overhead AVG_SPACE_FREELIST_BLOCKS The average freespace of all blocks on a freelist NUM_FREELIST_BLOCKS The number of blocks on the freelist DEGREE The number of threads per instance for scanning the table INSTANCES The number of instances across which the table is to be scanned CACHE Whether the table is to be cached in the buffer cache TABLE_LOCK Whether table locking is enabled or disabled TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ -------------------------------------- DBA_TAB_COLUMNS OWNER TABLE_NAME Table, view or cluster name COLUMN_NAME Column name DATA_TYPE Datatype of the column DATA_TYPE_MOD Datatype modifier of the column DATA_TYPE_OWNER Owner of the datatype of the column DATA_LENGTH Length of the column in bytes DATA_PRECISION Length: decimal digits (NUMBER) or binary digits(FLOAT) DATA_SCALE Digits to right of decimal point in a number NULLABLE Does column allow NULL values? COLUMN_ID Sequence number of the column as created DEFAULT_LENGTH Length of default value for the column DATA_DEFAULT Default value for the column NUM_DISTINCT The number of distinct values in the column LOW_VALUE The low value in the column HIGH_VALUE The high value in the column DENSITY The density of the column NUM_NULLS The number of nulls in the column NUM_BUCKETS The number of buckets in histogram for the column LAST_ANALYZED The date of the most recent time this column was analyzed SAMPLE_SIZE The sample size used in analyzing this column CHARACTER_SET_NAME Character set name CHAR_COL_DECL_LENGTH Declaration length of character type column GLOBAL_STATS Are the statistics calculated without merging underlying partitions? USER_STATS Were the statistics entered directly by the user? AVG_COL_LEN The average length of the column in bytes To find information about total number of rows and chained of a specific table : SELECT TABLE_NAME,NUM_ROWS,CHAIN_CNT FROM DBA_TABLES WHERE OWNER='EBC' AND TABLE_NAME='BC_BILL_IMAGE' / (You MUST analyze the table with compute statistics before execute the above query) Identify the situations in which tables need to be rebuilt. 1. When fragmentation found in the tablespace for table segment 2. When chained rows found 3. When row migration occurs Identify the characteristics of table rebuilds. During rebuilding operation the following actions are performed : -- Lock original table before rename -- Make backup copy of original table -- Remove all other NAMED Table Constraints because they will cause errors when re-creating the table -- Recreate original table -- Copy the data from the renamed table -- Recreate indexes EXCLUDING those created via Unique Constraints -- Recreate the FKey Constraints from the NEW table -- Grant any privs associated with the old table -- Recreate the FKey Constraints that reference the NEW table -- Recompile any dependent objects -- Recompile triggers for the new table Managing Indexes Indexes are optional structures associated with tables, which can be created to increase the performance of data retrieval. Just as the index in this manual helps you locate specific information faster than if there were no index, an Oracle index provides a faster access path to table data. When processing a request, Oracle can use some or all of the available indexes to locate the requested rows efficiently. Indexes are useful when applications often query a table for a range of rows (for example, all employees with a salary greater than 1000 dollars) or a specific row. Listing the different types of indexes and their uses According to the number of index column/columns -Simple index (Single Column) -Complex index (Multiple Columns) According to index Values -Unique Index Specifies that the value of the column (or columns) upon which the index is based must be unique. -Bitmap Index Specifies that index is to be created as a bitmap, rather than as a B-tree. Bitmap indexes store the rowids associated with a key value as a bitmap. Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. Obtaining index Information By querying the data dictionary views below , we can collection various information about indexes: TABLE_NAME COMMENTS ------------------------------ -------------------------------------------------- DBA_INDEXES Description for all indexes in the database DBA_IND_COLUMNS COLUMNs comprising INDEXes on all TABLEs and CLUSTERs To display information about indexes , we can query from DBA_INDEXES,DBA_IND_COLUMNS SELECT INDEX_NAME,INDEX_TYPE,TABLESPACE_NAME,UNIQUENESS FROM DBA_INDEXES WHERE OWNER='SCOTT' / SELECT TABLE_NAME,COLUMN_NAME,COLUMN_POSITION,COLUMN_LENGTH FROM DBA_IND_COLUMNS WHERE TABLE_NAME='EMP' AND INDEX_OWNER_NAME=’SCOTT’ / Identify the situations in which indexes need to be rebuilt.  To change tablespace for the index  When fragmentation found in the tablespace Identify the reason of index rebuilds.  To change Tablespace for indexes  To reduce fragmentation  Reorganize index Values Maintaining Users Maintaining Passwords User password can be changed periodically to maintain security . ( DBA can change password by Altering User) ALTER USER SCOTT IDENTIFIED BY TIGER; OR (User can change password just typing PASSWORD at the prompt i.e.SQL > ) SQL>PASSWORD Locking Accounts If an user is no longer required to login or an user can be locked for a certain period to maintain security ALTER USER SCOTT ACCOUNT LOCK; ALTER USER SCOTT ACCOUNT UNLOCK; Changing User Quota Space quota on a tablespace can be changed by altering an user ALTER USER SCOTT QUOTA 10M ON USERS; Dropping a User User can be dropped if it is not required in future If the user has no object DROP USER user_name; If the user has objects DROP USER user_name CASCADE; a. Monitoring Users Displaying User Account Status SELECT USERNAME,USER_ID,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE, DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM USER_USERS / Managing Privileges Privileges: Types 1 System Privileges 2 Object Privileges a. Managing System Privileges Granting System Privileges To grant system privileges to users and roles GRANT CREATE TABLE TO SCOTT / Revoking System Privileges To revoke system privileges and roles from users and roles. REVOKE CREATE TABLE FROM EMP / b. Managing Object Privileges Granting Object Privileges To grant privileges for a particular object to users, roles, and PUBLIC GRANT INSERT ON EMP TO SCOTT / Revoking Object Privileges To revoke object privileges for a particular object from users and roles. REVOKE SELECT ON EMP FROM DEMO / c. Monitoring Privileges To display system/object privileges for a specific user or all user we can take help from the following data dictionary tables/views TABLE_NAME COMMENTS ------------------------------ -------------------------------------------------- DBA_SYS_PRIVS System privileges granted to users and roles DBA_TAB_PRIVS All grants on objects in the database USER_SYS_PRIVS System privileges granted to current user USER_TAB_PRIVS Grants on objects for which the user is the owner, grantor or grantee Displaying System Privileges TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ -------------------------------------- USER_SYS_PRIVS USERNAME User Name or PUBLIC PRIVILEGE System privilege ADMIN_OPTION Grant was with the ADMIN option Displaying Object Privileges TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ -------------------------------------- USER_TAB_PRIVS GRANTEE Name of the user to whom access was granted OWNER Owner of the object TABLE_NAME Name of the object GRANTOR Name of the user who performed the grant PRIVILEGE Table Privilege GRANTABLE Privilege is grantable Managing Roles Implementing Roles Creating a Role CREATE ROLE supervisor; GRANT SELECT, INSERT, DELETE, UPDATE ON EMP TO supervisor; Identifying Predefined Roles (Some common predefined roles) CONNECT RESOURCE DBA SELECT_CATALOG_ROLE EXECUTE_CATALOG_ROLE DELETE_CATALOG_ROLE IMP_FULL_DATABASE EXP_FULL_DATABASE Modifying Roles REVOKE SELECT ON EMP FROM SUPERVISOR; GRANT SELECT ON DEPT TO SUPERVISOR; Assigning Roles to Users GRANT supervisor TO SCOTT; Revoking Roles From Users REVOKE supervisor FROM EBC; Dropping Roles DROP ROLE supervisor; Roles: Guidelines and Information Reasons for Creating Roles - Maintain Business Security - Displaying Role Information TABLE_NAME COLUMN_NAME COMMENTS ------------------------------ ------------------------------ -------------------------------------- USER_ROLE_PRIVS USERNAME User Name or PUBLIC GRANTED_ROLE Granted role name ADMIN_OPTION Grant was with the ADMIN option DEFAULT_ROLE Role is designated as a DEFAULT ROLE for the user OS_GRANTED Role is granted via the operating system (using OS _ROLES = TRUE) TABLE_NAME COLUMN_NAME COMMENTS --------------- ------------------------------ -------------------------------------------------- DBA_SYS_PRIVS GRANTEE Grantee Name, User or Role receiving the grant PRIVILEGE System privilege ADMIN_OPTION Grant was with the ADMIN option TABLE_NAME COLUMN_NAME COMMENTS --------------- ------------------------------ -------------------------------------------------- DBA_TAB_PRIVS GRANTEE User to whom access was granted OWNER Owner of the object TABLE_NAME Name of the object GRANTOR Name of the user who performed the grant PRIVILEGE Table Privilege GRANTABLE Privilege is grantable Displaying Active Roles and Privileges SELECT * FROM ROLE_TAB_PRIVS; SELECT * FROM ROLE_SYS_PRIVS; Networking Overview Basic Oracle Net Architecture Overview ====== Net8 is the foundation of Oracle's family of networking products, allowing services and their applications to reside on different computers and communicate as peer applications. The main function of Net8 is to establish network sessions and transfer data between a client machine and a server or between two servers. Net8 is located on each machine in the network. Once a network session is established, Net8 acts as a data courier for the client and the server. Architectural Concepts:  Networking Environments  Net8 and the Transparent Network Substrate (TNS) Networking Environments Oracle networking environments are based on two concepts:  Distributed Processing  Distributed Databases Distributed Processing Oracle databases and client applications operate in what is known as a distributed processing environment. Distributed or cooperative processing involves interaction between two or more computers to complete a single data transaction. Applications such as an Oracle tool act as clients requesting data to accomplish a specific operation. Database servers store and provide the data. In a typical network configuration, clients and servers may exist as separate logical entities on separate physical machines. This configuration allows for a division of labor where resources are allocated efficiently between a client workstation and the server machine. Clients normally reside on desktop computers with just enough memory to execute user friendly applications, while a server has more memory, disk storage, and processing power to execute and administer the database. Distributed Databases This type of client-server architecture also enables you to distribute databases across a network. A distributed database is a network of databases stored on multiple computers that appears to the user as a single logical database. Distributed database servers are connected by a database link, or path from one database to another. One server uses a database link to query and modify information on a second server as needed, thereby acting as a client to the second server. Net8 and the Transparent Network Substrate (TNS) Net8 uses the Transparent Network Substrate (TNS) and industry-standard networking protocols to accomplish its basic functionality. TNS is a foundation technology that is built into Net8 providing a single, common interface to all industry-standard protocols. With TNS, peer-to-peer application connectivity is possible. In a peer-to-peer architecture, two or more computers (called nodes when they are employed in a networking environment) can communicate with each other directly, without the need for any intermediary devices. In a peer-to-peer system, a node can be both a client and a server. A review of how Oracle clients and servers operate and communicate with each other will help you to understand what TNS is and how it works with Net8 to establish network sessions. Explaining the key components of Oracle Net architecture PDBLIVE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = DB_SERVER)(PORT = 1521)) (CONNECT_DATA = (SID = PDB)) ) The SID of the database had to be specified in the CONNECT_DATA section of the TNSNAMES.ORA file, as shown below: service_name= (description= (address_list= (address=...) (address=...) ) (connect_data=(sid=sales)) DESCRIPTION_LISTs were used for mapping multiple connect descriptors to net service names and for client load balancing of multiple listeners. In order to client load balance, the user had to define separate connect descriptors for each listener. If there was only one list of addresses, it was required to use an ADDRESS_LIST, as shown below: service_name= (description= (address_list= (address=...) (address=...) ) (connect_data=(sid=sales)) Explaining Oracle Net Services role in client-server connections A client is configured with a net service name that contains the location of a listener and service name of the service to which to connect. When a client requests a connection from a listener, the service name is sent to the listener. The listener receives the network session request, determines if the information passed to it matches the information it has registered, and determines if the client request may be serviced. Backup and Recovery Overview Describing the Basics of Database Backup, Restore, and Recovery Simply speaking, a backup is a copy of data. This copy includes important parts of your database such as the control file and datafiles. A backup is a safeguard against unexpected data loss and application errors; should you lose your original data, you can use the backup to make it available again. Backups are divided into physical backups and logical backups. Physical backups are copies of physical database files. In contrast, logical backups contain data that you extract using the Oracle Export utility and store in a binary file. You can use logical backups to supplement physical backups. To restore(The replacement of a lost or damaged file with a backup. You can restore files either with O/S commands such as UNIX cp ) a physical backup is to reconstruct it and make it available to the Oracle server. To recover (The application of redo data or incremental backups to database files in order to reconstruct lost changes. e.g. instance recovery, crash recovery.Oracle performs two types of recovery automatically using online redo records) a restored datafile is to update it using redo records(A group of change vectors describing a single, atomic change to the database. Oracle constructs redo records for all data block changes and saves them on disk in the current online redo log. Redo records allow changes to database blocks to be reconstructed should data loss occur. ), i.e., records of changes made to the database after the backup was taken. Oracle performs crash recovery and instance recovery automatically after an instance failure. Instance recovery is an automatic procedure that involves two distinct operations: rolling forward the backup to a more current time by applying online redo records and rolling back all changes made in uncommitted transactions to their original state. Guidelines for Using Export and Import Export and Import are utilities that move Oracle data in and out of Oracle databases. Export writes data from an Oracle database to an operating system file in a special binary format. Import reads Export files and restores the corresponding information into an existing database. Although Export and Import are designed for moving Oracle data, you can also use them to supplement backups of data. This section describes the Import and Export utilities, and includes the following topics:  Using Export  Using Import Using Export The Export utility allows you to back up your database while it is open and available for use. It writes a read-consistent view of the database's objects to an operating system file. System audit options are not exported Examples: To export full database issue the command below $ exp user/paswsd@connect_string full=y file=c:\sl\aa.dmp log=c:\sl\aa.log OR You can use interactive window to perform desired mode of export To start interactive window: $ exp(Enter) Export Modes Mode Description User Exports all objects owned by a user. Table Exports all or specific tables owned by a user. Full Database Exports all objects of the database. Using Import The Import utility allows you to restore the database information held in previously created Export files. It is the complement utility to Export. To recover a database using Export files and the Import utility: 1. Re-create the database structure, including all tablespaces and users. Note: These re-created structures should not have objects in them. 2. Import the appropriate Export files to restore the database to the most current state possible. Depending on how your Export schedule is performed, imports of varying degrees will be necessary to restore a database. $ imp Creating Oracle Database Creating, managing, and using initialization files: PFILE => Example INIT.ORA file # # Copyright (c) 1991, 1998 by Oracle Corporation # ############################################################################## # Example INIT.ORA file # # This file is provided by Oracle Corporation to help you customize # your RDBMS installation for your site. Important system parameters # are discussed, and example settings given. # # Some parameter settings are generic to any size installation. # For parameters that require different values in different size # installations, three scenarios have been provided: SMALL, MEDIUM # and LARGE. Any parameter that needs to be tuned according to # installation size will have three settings, each one commented # according to installation size. # # Use the following table to approximate the SGA size needed for the # three scenarious provided in this file: # # -------Installation/Database Size------ # SMALL MEDIUM LARGE # Block 2K 4500K 6800K 17000K # Size 4K 5500K 8800K 21000K # ############################################################################### db_name = PDB instance_name = PDB service_names = PDB db_files = 1024 control_files = ("E:\Oracle\oradata\PDB\control01.ctl", "E:\Oracle\oradata\PDB\control02.ctl") db_block_buffers = 8192 shared_pool_size = 16777216 java_pool_size = 20971520 open_cursors= 100 processes = 100 log_buffer = 32768 db_block_size = 2048 open_links = 4 Creating Database using DBCA(Database Configuration Assistant) Creating Database Manually Consider the following issues before you create a database:  Plan your database tables and indexes, and estimate how much space they will require.  Plan how to protect your new database, including the configuration of its online and archived redo log (and how much space it will require), and a backup strategy.  Select the database character set. You must specify the database character set when you create the database. After the database is created, you cannot change the character set choices without re-creating the database. Hence, it is important that you carefully consider which character set(s) to use. All character data, including data in the data dictionary, is stored in the database character set. If users access the database using a different character set, the database character set should be the same as, or a superset of, all character sets they use. Also become familiar with the principles and options of starting up and shutting down an instance, mounting and opening a database, and using parameter files. To create a new database, you must have the following:  the operating system privileges associated with a fully operational database administrator  sufficient memory to start the Oracle instance  sufficient disk storage space for the planned database on the computer that executes Oracle To Create a New Database and Make It Available for System Use 1. Back up any existing databases. 2. Create parameter files. 3. Edit new parameter files. 4. Check the instance identifier for your system. 5. Start SQL*Plus and connect to Oracle as SYSDBA. 6. Start an instance. 7. Create the database. 8. Back up the database. To create the new database, use the SQL CREATE DATABASE statement, optionally setting parameters within the statement to name the database, establish maximum numbers of files, name the files and set their sizes, and so on. When you execute a CREATE DATABASE statement, Oracle performs the following operations:  creates the datafiles for the database  creates the control files for the database  creates the redo log files for the database  creates the SYSTEM tablespace and the SYSTEM rollback segment  creates the data dictionary  creates the users SYS and SYSTEM  specifies the character set that stores data in the database  mounts and opens the database for use Example: Open OS Terminal $ SQLPLUS /nolog connect username/password as sysdba You can start an instance without mounting a database; typically, you do so only during database creation. Use the STARTUP command with the NOMOUNT option: SQL>STARTUP NOMOUNT PFILE=C:\TESTDB\INIT.ORA; SQL>CREATE DATABASE TESTDB DATAFILE ‘C:\TESTDATA\SYSTEM01.DBF’ SIZE 5M LOGFILE GROUP 1 ‘C:\TESTDATA\TESTGRP0101.LOG’ SIZE 50K, GROUP 2 ‘C:\TESTDATA\TESTGRP0201.LOG’ SIZE 50K; (You MUST run catalog.sql and cataproc.sql after database creation) Identifying the various options available to shutdown an instance SHUTDOWN NORMAL NORMAL is the Default shutdown process . No new connection request is permitted after issuing this command . SHUTDOWN TRANSACTIONAL After issuing this command shutdown process will be waiting until any pending transaction to be completed. No new connection request is permitted after issuing this command . SHUTDOWN ABORT Force disconnection of all connection and shutdown the instance immediately. No new connection request is permitted after issuing this command . Overview of Performance Tuning Tuning phases The following steps provide a recommended method for tuning an Oracle database. These steps are prioritized in order of diminishing returns: steps with the greatest effect on performance appear first. For optimal results, therefore, resolve tuning issues in the order listed: from the design and development phases through instance tuning. Step 1: Tune the Business Rules Step 2: Tune the Data Design Step 3: Tune the Application Design Step 4: Tune the Logical Structure of the Database Step 5: Tune Database Operations Step 6: Tune the Access Paths Step 7: Tune Memory Allocation Step 8: Tune I/O and Physical Structure Step 9: Tune Resource Contention Step 10: Tune the Underlying Platform(s) Dynamic performance views · Instance-Level Views for Tuning · Session-Level or Transient Views for Tuning · Current Statistic Values and Rates of Change Dynamic performance views, or "V$" views, are useful for identifying instance-level performance problems. V$ view content is provided by underlying X$ tables. The X$ tables are internal data structures that can be modified by SQL statements. These tables are therefore only available when an instance is in a NOMOUNT or MOUNT state. This chapter describes the most useful V$ views for performance tuning. V$ views are also useful for ad hoc investigation, for example, when users report sudden response time deterioration. Although the V$ views belong to user SYS, users other than SYS have read-only access to V$ views. Oracle populates the V$ views and X$ tables at instance startup. Their contents are flushed when you shut down the instance. The X$ tables and their associated V$ views are dynamic, so their contents are constantly changing. X$ tables retain timing information providing you have set the init.ora parameter TIME_STATISTICS to TRUE, or if you execute the SQL command: ALTER SYSTEM SET TIME_STATISTICS=TRUE; Data Dictionary Cache (DDC) Tuning Determine whether misses on the data dictionary cache are affecting the performance of Oracle. You can examine cache activity by querying the V table as described in the following sections. Misses on the data dictionary cache are to be expected in some cases. Upon instance startup, the data dictionary cache contains no data, so any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses should decrease. Eventually the database should reach a "steady state" in which the most frequently used dictionary data is in the cache. At this point, very few cache misses should occur. To tune the cache, examine its activity only after your application has been running. Overview of tuning the buffer cache You can use or bypass the Oracle buffer cache for particular operations. Oracle bypasses the buffer cache for sorting and parallel reads. For operations that use the buffer cache, this section explains: · Evaluating Buffer Cache Activity by Means of the Cache Hit Ratio · Increasing the Cache Hit Ratio by Reducing Buffer Cache Misses · Removing Unnecessary Buffers when Cache Hit Ratio Is High After tuning private SQL and PL/SQL areas and the shared pool, you can devote the remaining available memory to the buffer cache. It may be necessary to repeat the steps of memory allocation after the initial pass through the process. Subsequent passes allow you to make adjustments in earlier steps based on changes in later steps. For example, if you increase the size of the buffer cache, you may need to allocate more memory to Oracle to avoid paging and swapping. Tuning checkpoints A checkpoint is an operation that Oracle performs automatically. This section explains: · How Checkpoints Affect Performance · Choosing Checkpoint Frequency · Fast-Start Checkpointing Optimize Sort Operations The default sort area size is adequate to hold all the data for most sorts. However, if your application often performs large sorts on data that does not fit into the sort area, you may want to increase the sort area size. Large sorts can be caused by any SQL statement that performs a sort on many rows. Sort Area Parameters SORT_AREA_SIZE is a dynamically modifiable initialization parameter that specifies the maximum amount of memory to use for each sort. If a significant number of sorts require disk I/O to temporary segments, your application's performance may benefit from increasing the size of the sort area. In this case, increase the value of SORT_AREA_SIZE. The maximum value of this parameter depends on your operating system. You need to determine how large a SORT_AREA_SIZE makes sense. If you set SORT_AREA_SIZE to an adequately large value, most sorts should not have to go to disk (unless, for example, you are sorting a 10-gigabyte table). SQL Statement Tuning This section describes five ways you can improve SQL statement efficiency: · Restructure the Indexes · Restructure the Statement · Modify or Disable Triggers · Restructure the Data · Keeping Statistics Current and Using Plan Stability to Preserve Execution Plans Hints As an application designer, you may know information about your data that the optimizer does not know. For example, you may know that a certain index is more selective for certain queries. Based on this information, you may be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan. Hints allow you to make decisions usually made by the optimizer. You can use hints to specify: · The optimization approach for a SQL statement · The goal of the cost-based approach for a SQL statement · The access path for a table accessed by the statement · The join order for a join statement · A join operation in a join statement

.