Oracle Performance Management on major Operating Systems: WULZ
- A walk in the park or a wulz in the dark?
Adam Grummitt
adam@https://www.doczj.com/doc/972635115.html,
Metron Technology Ltd
https://www.doczj.com/doc/972635115.html,
Windows, UNIX, Linux and z/OS (WULZ) are now established as the leading
computer operating systems (OS). Oracle is well established as a leading
relational database management system (RDBMS). What is less clear is how
best to ensure the optimum performance of Oracle in each environment and how
to exercise effective performance management.
Performance management addresses the provision of a consistent acceptable
service at a known and controlled cost, both now and in the foreseeable future.
This requires the measurement and monitoring of current behavior, the
management and storage of those measurements, the analysis of past and
present behavior, the trending of past behavior and the prediction of future
behavior in the light of potentially changing scenarios. The key results of these
activities need automatic presentation on the web with exception conditions
requiring action highlighted.
Oracle is very well established in the UNIX world, but less so in other
environments where the “native” databases still dominate. Thus db2 is dominant
in OS/390 or z/OS, SQL Server is dominant under Windows and Linux has many
options. The use of Oracle under Windows or Linux is still not very widespread
for production systems, but this may change as those environments grow in
scalability and reliability. OS/390 is well established as the traditional Data
Centre norm, but usually working with IBM tools.
This paper discusses each platform and outlines a performance management
and capacity planning approach used in a variety of case studies. Software
factors reviewed include the OS architecture, database administration tools
available and the product scalability on the platforms. Performance Management
functions reviewed include capture and interpretation of performance metrics,
performance tuning parameters, workload trend analysis and analytical modeling
of Service Levels in terms of throughput and response times.
1. Introduction to Oracle Architecture
The first thing to remember is that, as far as the end users are concerned for an Oracle system, it really doesn’t matter whether the operating system is UNIX, Linux, Windows, OpenVMS, MVS (or OS/390 or z/OS), etc. Also, surprisingly true but exceptionally welcome, the Oracle architecture is so well implemented across the platforms that the Oracle performance information in the V$ views of the X$ tables is genuinely uniform. These are the key data sources for Oracle performance monitoring and provide a wealth of data. The most significant tables from this point of view are:
? v$sysstat ? v$database ? v$datafile ? v$filestat
? v$process
? v$session
? v$sesstat
? v$statname
? v$sessionwait
These are all available in the same format under all the platforms discussed above. However, the OS performance data is totally domain specific and held and accessed in different ways on each. This paper starts with an outline of Oracle architecture on UNIX. The implementations on the mainframe, Windows and Linux are examined in subsequent sections.
1.1 System Global Area
In order to appreciate the importance of the memory structures contained within the SGA, the algorithms used to manage them, and their effect on physical resource consumption, we should first consider the sequence of events caused when Oracle updates a row of a table. This is the key event that impacts on Oracle performance.
1. The row to be updated is first copied to the buffer cache in the SGA and locked.
2. A ‘before image’ (a copy of the original row) is written to the buffer cache for rollback.
3. The row is modified in the buffer cache.
4. The changed row is copied to the redo log buffer.
5. The update is committed and flagged in the redo log buffer.
6. The log writer (LGWR) writes the committed row to the redo log.
7. The database writer (DBWR) writes the modified row to disk
Each of these areas is considered separately below.
The buffer cache is used to store database blocks that contain tables, indexes, clusters, rollback segments and sort data. The important metric here is the probability that Oracle finds the block that it needs already in the cache, rather than having to get the block from disk. This is referred to as the buffer cache hit-ratio. The usual recommendation is that the hit-ratio should be above 95% for OLTP and above 85% for batch applications. If you are only able to look at one aspect of Oracle performance, the database buffer cache hit-ratio should be your choice.
The rollback segments are used for read-consistency (enabling Oracle to avoid the need for read locks), rollback and recovery. The key metric here is the rollback segment get hit-ratio. Any figure less than 99% is indicative of rollback contention - possible causes are too few rollback segments or rollback segments that are too small in proportion to the volume of updates.
The Redo Log buffers are used to hold changed data prior to writing them to the on-line Redo Logs. An update-intensive application can cause contention for access to the redo log buffer and this can be diagnosed by using data from the V$LATCH view. V$LATCH gives the number of gets, misses, immediate gets and immediate misses. If the ratio of misses to gets, or of immediate misses to immediate gets is greater than 1%, then the Redo Log buffer size should be increased. One can also monitor time spent waiting for Redo Log space or any other internal Oracle resources. This provides direct input to response time analysis.
The Shared Buffer Pool contains two vitally important memory structures - the Library Cache and the Dictionary Cache. A single INIT.ORA parameter (SHARED_POOL_SIZE) controls the total size of the Shared Buffer Pool including both the Library Cache and the Dictionary Cache. The main job of the Library Cache is to hold shared SQL statements that have been parsed. Clearly a significant performance gain can be obtained if Oracle can re-use an SQL statement that has already been parsed. Both the Library Cache get hit-ratio and the Dictionary Cache hit-ratio (once the database has been loaded and used for a little while) should be better than 90%.
1.2 Oracle Disk Storage
The advice provided by Oracle on tablespace allocation is summarized here; each site should check that the Oracle recommendations for their platform have been followed carefully.
The tablespace configuration defines the largest logical unit of space in an Oracle database. All databases should have at least six tablespaces as follows, although many more may be required in order to separate data of different types and with different uses.
SYSTEM, RBS, TEMP, USERS, xxx_DATA, xxx_INDEX
The SYSTEM tablespace should contain only data dictionary objects. A separate tablespace (RBS) should be allocated for rollback segments. A temporary tablespace should be used for sorts etc. The USERS tablespace is for those (few) users who need to create objects.
The Database Files information is obtained from the V$FILESTAT view. The primary objective should be to spread the Oracle files across the available physical devices in order to distribute the I/O evenly, and to prevent any single device from becoming a performance bottleneck.
Database blocks are read in order to retrieve the required data and the number of blocks needs to be minimized as one of the goals of any tuning exercise. This can be achieved in a number of ways:
?using a larger block size
?packing rows as closely as possible
?preventing row migration
?tuning SQL statements
Data Fragmentation causes additional I/O in order to satisfy each logical request and occurs in a number of different ways which need to be minimized:
?internal fragmentation - caused by deletions or updates within a block
?external fragmentation - where a table is stored on multiple (non-contiguous) extents
?row migration - where an updated row will no longer fit in its original block, the entire row is ‘migrated’ to a new block and pointed to from the original block
?row chaining - where a row is too large to fit in a single block
1.3 SQL and Database Navigation
SQL Standards
Significant performance gains can be obtained by exploiting Oracle’s ability to re-use parsed SQL statements. For SQL to be re-used it must be identical. This is much more likely to be the case if all SQL code is written to an enforced set of standards, and bind variables are used instead of literals.
Tracing SQL
The Oracle SQL_TRACE function can be used to analyze the resources used when Oracle executes SQL statements. SQL_TRACE can be enabled for the entire instance (this is not practicable due to the performance hit and volume of data that would be produced) or more typically for specific sessions. The output is written to a trace file that must be processed using another Oracle utility - TKPROF. TKPROF produces resource consumption and performance details for each SQL statement and (optionally) an EXPLAIN PLAN which shows the way in which the optimizer will process the SQL and the optimizer mode used. The SQL optimizer examines each SQL statement and chooses an optimal execution plan.
Database Navigation
When reading rows from a short table, or when the majority of rows are required from a long table, a table scan is usually the most efficient method. When only a few rows are required from a long table, it is usually more efficient to use an indexed read. Clearly this is only possible when an index has been defined on the table using the required key. The output from TKPROF and EXPLAIN PLAN can be used to identify missing (or mis-used) table indexes by showing the number of rows scanned, number of rows retrieved, and the access method used.
Locks
Oracle from V7 onwards uses a form of automatic row level locking to maintain the consistency of the database when it is updated. Locks are only applied to database updates, and in the majority of on-line systems locking activity will probably affect only a small number of users or transactions. A process sets a lock when it wishes to change a row in the database.
The probability of lock contention affecting performance depends on the locality of updates, which is a function of the application design and the transaction mix at any time. Details on locking activity are provided by the V$LOCK view although any lock contention will only be reported in real time. The Oracle utllockt.sql script can be used to provide information on lock ‘waiters’ and the lock modes requested and held.
Latches
A latch is an internal mechanism used by Oracle to ensure the consistency of shared data structures. One or more latches protect each major data structure. Latches should only be held for brief (microsecond) periods. A list of latches is in V$LATCHNAME. V$LATCHHOLDER details all processes holding latches and V$LATCH provides overall latch summary statistics.
2. Introduction to Oracle Performance
The metrics readily available from the UNIX Operating System for Performance Management are derived from a range of utility commands originally defined for diagnostic purposes like sar, ps, acctcom, iostat, vmstat and mpstat. Alternatively more intimate capture is feasible from the kernel via appropriate interrupts or privileged commands. Either way, this data then needs to be reconciled with that available from the Oracle v$table views as indicated above.
Most of the above architectural considerations will manifest themselves as Wait Events if there are problems. There are structures within Oracle which enable the investigation of Wait events to a high degree. This is the focus of a growing amount of attention in the Oracle performance management world [Shallahamer,01]. However, when assessing the performance of an application it is essential to address the system as a whole, rather than just from an Oracle or an operating system viewpoint [Metron,99]. A performance study of a badly performing Oracle application under UNIX illustrates the dangers of a restricted view. The Oracle team had attempted (quite correctly) to maximize the database buffer cache hit-rate. By increasing the number of buffer blocks they were eventually able to achieve a hit-rate consistently in excess of 99%. Unfortunately this increased the size of the SGA to the point that UNIX started to page it to and from disk. This caused considerable amounts of I/O (thus adversely affecting performance) which was not apparent from any of the Oracle metrics.
When tuning any Oracle system, the areas to address (in this order) are:
1 the design (if it’s not too late!)
2 the application
3 memory
4 I/O
5 contention
The main goals in Oracle tuning (taken from Oracle’s own documentation) are to make sure that: ?SQL statements access the smallest possible number of Oracle blocks
?if a block is needed, it is cached in memory
?users share the same code
?when code is needed it is cached in memory
?where physical I/O to disk is unavoidable, it is performed as quickly as possible
?users never have to wait for resources used by others
This area is well documented with some excellent white papers [Shallahamer,00].
2.1 Oracle Performance Management & Capacity Planning
The objective is to provide a consistent acceptable service at a known and controlled cost, both now and in the foreseeable future. The processes required to achieve this are straightforward: ?observe key resource consumption and service level metrics (from both Oracle and the OS) ?store those observations in a performance database (PDB)
?analyze the data in the PDB to derive relationships and correlations which will help in understanding the behavior patterns of the system
?predict the effect of any changes and derive an action plan to protect end-user service levels The last is the most crucial and often most neglected, and is achieved by use of analytic models. Analytical modeling techniques based on multi-priority multi-class queuing network theories have been used for many years now to predict the effect on computer performance of changes in configuration and workload. These techniques have been extended to cover Oracle environments. Much of the dynamic behavior of an Oracle system is non-linear and cannot therefore be simply extrapolated. The Oracle environment itself is also subject to the constraints placed upon it by its operating system environment.
Analytical modeling, based on queuing network theory, has been used for some time now to model the performance of computer systems, including the non-linearities that contention for resources causes [Grummitt,94]. Such techniques have been extended to encompass the behavior of application environments built using RDBMSs such as Oracle [Metron,94].
The key contribution that using such a model makes is its ability to translate an end-user workload into the resources (both physical and logical) that the workloads will both use and contend for, and derive the response time that will be provided. Once built and validated from actual baseline observations, the model can be used to explore the consequences of changes in workload and/or hardware configuration [Grummitt,96].
All analytical models represent service centers (resources that provide a service to the workload and/or may contribute a delay) and workloads. Each workload is described by its requirement for service at each of the service centers. Established modeling methods are adequate to model contention for physical resources and the consequent effect on response times.
Using a combination of the data available from the OS, and the data available from Oracle (primarily from the V$ views) it is possible to construct quite detailed models of the behavior of Oracle applications [Grummitt,95]. One should always remember however, that the model should only be sufficiently detailed to answer the specific planning questions. A model is, by definition, a simplification of reality, built for a specific purpose.
2.2 Baseline Modeling
The first step in the modeling process is to build and calibrate a baseline model. This is based on actual system measurements and can be used to predict current performance. This prediction is then compared to the measurements and the model is fine-tuned accordingly – a process usually referred to as ‘calibration’.
A model consists of a configuration and workload pair. The configuration definition can be derived directly from the information provided by the OS. The workload is described as set of workload components – each of which represents an important classification of work. The degree of detail required here will define the data sources that are required. For more detailed studies, the V$ views must be used.
Having derived the resource consumption of each session that was active during the modeling ‘window’, the sessions can be aggregated to form workload components using (usually) the O/S Command Name or Process Name or Oracle User Name to group ‘like’ sessions. As always, the required groupings should be chosen in accordance with the questions that the modeling is to address.
The resulting model will therefore consist of workloads derived from two completely disparate data sources – Oracle for the Oracle workloads and the OS for the rest. Care must be taken not to ‘double count’ any of the workload resource consumption records. A model is usually built from measurements taken during a busy period, and several baseline models may be required – one for each relevant workload peak.
2.3 CPU Allocation
There will almost certainly be differences between the total CPU consumption of the session records for a given instance, and the CPU consumption reported by the OS. This mis-match is referred to by mainframers as the “capture ratio”. An ‘overhead’ workload could be created to account for the difference. In typical case study situations, the CPU discrepancy is calculated and then ‘distributed’ across the Oracle workloads in proportion to each workload’s total CPU consumption. The advantage of this approach is that the capacity planner does not need to separately modify the arrival rate of the appropriate ‘overhead’ component when changes are made to the main ‘user’ workloads.
2.4 I/O Allocation
The V$SESSION view provides a total physical I/O count for each session, but this is not broken down by device. Note also that, although the metric is entitled ‘physical I/O’, all that this means is that Oracle has sent an I/O request to the operating system. This may or may not result in a physical I/O to DASD depending upon the effective operating system cache hit rate.
The I/O scenario is further complicated by the fact that the OS typically does not give a complete I/O picture either. The first question that the capacity planner has to ask is “Do I need to bother with precise I/O allocation to workloads?” If the overall I/O loading is small, and queuing for I/O devices is not likely to contribute significantly to response times, then there is no point in detailed allocation.
3 UNIX Case Study
A particular site had problems with their production system based on Oracle Financials where the response times for the production systems were complained of as being erratic. The hardware supplier had already proposed a 50% upgrade to all resources.
One of the first metrics looked at was the buffer cache hit-ratio, which was over 97% throughout the sample peak period of 11:00 to 12:00. This is a very high hit-ratio, well in excess of the usual targets. The individual session buffer cache hit-ratios for the users that used the majority of the resources, also showed average cache hit-ratios in excess of 98%. The other hit-ratios referred to above were also checked and found to be within acceptable limits. Clearly from an Oracle point of view, contention for SGA resources was not the source of the problem.
The live Oracle instance was reputed to be the major user of CPU resources on the system. This was checked by correlating the CPU consumption reported by UNIX with the CPU consumption reported by Oracle. This is shown graphically in figure 1.
Figure 2. Free Memory vs. Pageout Rate
The x-axis on this graph covers the period 0800 - 1300. The CPU usage reported for the Oracle ‘Live’ instance is a significant proportion of the total CPU for the majority of this period. Note that peak (O/S) CPU is over 80% at the morning peak time of 1100. CPU utilization at this level will cause significant CPU queuing with a consequent effect on response times.
The next area looked at was the way in which UNIX memory resources were being used. The graph in figure 2 shows a correlation between the amount of free UNIX memory and one of the key paging metrics - the number of physical page-outs per second.
This dramatic picture shows the effect of increasing main memory pressure. As the amount of free memory falls, the pageout rate stays very low - until free memory becomes critical, at which point physical pageouts start to increase rapidly. This heavy I/O load, and the CPU required to service it, was damaging application response times during the critical peak period.
In this case it was possible to reduce the SGA size from over 600KB to less than 400KB, by reducing the number of database block buffers, without any significant effect on database buffer cache hit-ratios. This, in turn, reduced the pressure on UNIX memory resources and virtually eliminated the paging activity shown above.
The traffic by Oracle file during the peak period was analyzed and the I/O activity was concentrated on a small number of files. More than half the total number of reads was to a file ‘houtabLIVE1.dbf’. This is one of the master files in the key production service called Housing.
An analysis of the CPU consumption of Oracle sessions for the peak period showed the predominance of a particular username with significant numbers of long table scans. A simple addition of an index to the table used would reduce total I/O by more than 20%.
The modeling window chosen was the peak morning hour, 1100 - 1200. The primary question to address was “Can the system cope with the expected increase in the number of concurrent Oracle maintenance sessions and, if not, what actions are required to make sure that it can?” The workload was therefore broken down into components that represented:
?the Oracle housing sessions in the ‘LIVE’ instance
?the remainder of the work in the ‘LIVE’ instance
?the work from other Oracle instances
?the work generated by UNIX itself in order to manage the environment
The initial or ‘baseline’ model was calibrated against the observations of device utilization and queue lengths. The model will thus define relative changes in response times, rather than absolute values, for the workload components that were selected.
A projection (or ‘scenario’) model was then built to show the way in which Housing response times would change as the overall loading increased.
Figure 3. Response Time Scenario Report Figure 4. Impact of upgrade & re-index
The first vertical bar represents the current (baseline) response time, and each subsequent bar represents the addition of five more concurrent Housing sessions. A number of different scenarios were then considered. The need for more disks was easily disproved. Also, with the improvement in the allocation of memory, there was no need for extra RAM.
A scenario model was then built incorporating just two changes from the model shown above
1. The CPU was upgraded from an 8-way to a 12-way at projection point 5
2. An index was to be added (to the table referred to in section 5.4 above) at projection point 7 The effect on housing response times is shown in figure 4 above.
The advantage of this approach is that a large number of alternative workload and configuration combinations can be assessed, from the point of view of their impact on end-user response times.
4. Oracle on the mainframe
Despite the widespread and premature obituary notices, the mainframe is not only alive and kicking, but also enjoying growth in configurations and applications. This is also true for Oracle implementations and it’s not hard to see why. For many years now, IBM MVS - OS/390 – z/OS mainframes have had the throughput and storage capabilities to support the very large database sizes and user populations that are only now becoming possible in Oracle UNIX applications. The increasing numbers who are implementing Oracle applications on OS/390 mainframes, need to get the best possible performance from the powerful and complex environment that the mainframe provides.
4.1 Mainframe Performance Management
This section is based on an OS/390 system which was running in Compatibility mode [Metron,97]. Many of the comments made concerning Performance Groups are equally applicable to Service Classes and/or Report Classes under Goal Mode.
The primary mechanism provided by OS/390 (in Compatibility Mode) to control the allocation of resources to workloads is implemented through Control Performance Groups. Each type of work on the system is mapped to a Performance Group. A large system may have many hundreds of Performance Groups, each of which is referred to by its Performance Group Number (PGN). Oracle recommends that each ‘production’ instance should be mapped to its own Performance Group. The advantage of this is that it provides resource allocation control at the Oracle instance level from MVS. This is important if, as happens frequently, the characteristics of a given instance change – for example, moving from primarily testing to production use. A secondary benefit is that this approach will make it much easier to determine an instance’s resource consumption from RMF/SMF and other measurement systems which use the performance group as their main reporting classification.
One of the most important parameters, which must be defined for each group, is its dispatching priority (or velocity in Goal Mode). The active priority of each workload can be varied according to the resources that is has consumed. It is also possible to ‘timeslice’ a particular Performance Group so that, whilst it may have a fairly low initial priority, it will spend some percentage of its time at a much higher pre-defined priority. In practice, most Oracle workloads do not need to be run at a high priority. The priority level should be less than CICS, equivalent to TSO, and higher than ‘batch’ Performance Groups. The exception to this is the TNS listener, which must be mapped to one of the highest priority groups.
OS/390 provides a very comprehensive resource consumption and performance reporting mechanism called System Management Facility/Resource Management Facility (SMF/RMF). SMF/RMF formats the information into a number of different record types.
The default Oracle SMF record type is 199. The format of the 199 record (in common with the other SMF records) is complex and so Oracle has provided a program (ORAFMT) to ‘unscramble’ the variable length records into a fixed-block sequential dataset.
4.2 Oracle Performance Management on the mainframe
The standard Oracle V$ views (based on the X$ tables) are available for Oracle on OS/390. The V$ views provide a much more comprehensive picture of overall instance performance and resource consumption than that available from the Oracle SMF (199) records and so this is the preferred data source. Another reason for using the V$ views is that they are consistent across all of Oracle’s supported platforms. Existing analysis routines can be used provided that access
to the mainframe instances can be achieved through SQLnet. (This will require the SQLnet ‘tnsnames’ reference file to be set up to point to the required instance and location.)
Oracle implementations on OS/390 usually consist of several ‘production’ instances together with a number of more lightly used test and development instances. It is important to consider the relative workload patterns of the production instances individually, and the ways in which each instance contributes to the overall Oracle resource consumption.
4.3 Integrated Performance Management
Metrics
Beyond the fact that some of the field sizes and absolute values in mainframe V$ views can be very large, they are consistent with other Oracle implementations. OS/390 metrics are derived from SMF and a check should be made for consistency in reporting between these two separate data sources.
Redo log file distribution on DASD
One of the most important issues affecting Oracle performance is the way in which the redo logs are configured. The standard recommendation is to spread the redo logs across a number of fast devices to avoid redo log contention affecting response times. This is particularly important in mainframe Oracle systems where a high data update rate is common.
Database Buffer Cache
The DB_BLOCK_BUFFERS entry in the init
4.4 Mainframe Case Study
The mainframe case study is a site which had major increases in traffic expected and wanted to assess likely future service levels.
Figure 5 shows the distribution of CPU usage by Performance Group for the most active performance groups on the case study system.
Figure 5. CPU Usage by Performance Group
This shows a typical distribution, with the vast majority of the CPU resources being used by a small number (often less than ten) of Performance Groups.
Figure 6 below shows the relative CPU consumption (y-axis) of each instance on the case study system over a five-day period (x-axis).
Figure 6. CPU consumption – all instances.
This type of report can be used to quickly focus attention on those instances which are consuming the majority of any particular resource.
In the case study system, the main production instances were running at a very low priority and so some predictive analysis was done to determine the effect on increasing the Oracle priority. Figure 7 shows the predicted effect on relative response times (y-axis) of incremental increases in the priority of the Oracle workload (x-axis).
Figure 7. Response time vs. priority
The model showed that a relatively modest increase in priority would have a dramatic effect on response times – reducing them by around 60%. Increasing the priority still further would provide
only negligible improvements. This prediction proved accurate in practice, with measured improvements of between 50 and 60%.
The main Oracle workloads on the Case Study system were expected to increase by up to 150%, and so a baseline model was built for the mid-morning period (the period most likely to experience the increased user load). A scenario model was then built, using the baseline model as a starting point, and ‘ramping-up’ the workload contribution from the main Oracle instance in linear steps of 5%. Figure 8 shows the predicted impact on CPU utilization. Each vertical bar represents a further five percent linear workload increase (the first is the baseline position). The y-axis is CPU utilization.
Figure 8. CPU Utilization Scenario, 5% growth & Upgrade Utilization Scenario, 25% growth This shows the expected linear behavior and indicated that the system is capable of handling a 50% increase on the Oracle workload before saturating the CPU.
The organization did, however, have to prepare for a much higher growth rate and so a more extreme scenario model was built with the Oracle workload increasing in steps of 25%. This model saturated at step 3 (75% increase) due to CPU loading. The model was then modified to include a CPU upgrade (from a 5-way to a 10-way processor) timed to take effect just before the saturation point. The effect of the CPU upgrade was to greatly reduce the CPU utilization, and after the upgrade, the CPU utilization increases at a reduced rate due to the increased power of the new CPU.
The most important question was, however, “How will end-user Oracle response times be affected?” Figure 8 also shows the relative effect on Oracle response times (y-axis, normalized to 1 at the baseline) of the workload changes and CPU upgrade already described.
Note the non-linear behavior in response times, which is due to contention for resources. Due the relatively light I/O loading, it was the CPU that, once again, caused the response times to degrade at an increasing rate as the workload grew.
Using these techniques, quite complex patterns of workload and configuration change can be mapped onto a time-line, and the overall effect on delivered response times can be derived. Quite frequently, the coincidence of a number of disparate factors can combine to cause sudden and potentially very damaging effects on response times.
At the conclusion of this exercise the client organization had a clear picture of the likely effect on response times of changes in workload, and the expenditure that would be required in order to maintain the required service levels.
Subsequent measurements of the case study system confirmed both that the estimates of workload increase were accurate (this is often the area where the least accuracy is evident!) and that response time predictions were within 5% of the measured values.
5. ORACLE under Windows
This section describes Oracle under Windows [Metron,98]. Oracle is very well established as the leading database on UNIX systems. Oracle users and administrators are therefore familiar with the architecture of Oracle on such platforms, and also with the tools and techniques for deriving maximum benefit from their database. By contrast, Oracle on Windows (including Windows XP, Windows 2000 or Windows NT) is less widely used in major production systems and its performance management generally less well understood than on UNIX and mainframes. However, the list of product solutions from Oracle for Windows confirms just how important Oracle regards the Windows platform for the future of their database.
5.1 Oracle Architecture on Windows
Oracle on Windows runs as a single process, whether on NT, W2K or XP. Because it is running as a system service it is really quite similar to a Unix daemon. Within that single process there are multiple threads of execution. Thread 0 acts as a service dispatcher, thread 1 as a service handler and then threads 2, 3, 4, 5 and 6 are the background threads. What is known on UNIX as the background processes are the background threads on Windows NT. Thread 2 is PMON, thread 3 is DBWR and there are further threads for log writer, system monitor etc. There is only one DBWR.
Within the Oracle process a large amount of the memory is used for the SGA. The SGA actually lives within the Oracle 8 process. The SGA is completely distinct from the Windows file system cache, and this is a very important distinction to bear in mind. For an Oracle system it is important to set memory so that it favors what are called network processes. Do not allow the Windows file system cache to grow to the detriment of the working set of Oracle 8 itself.
The configuration parameters for Oracle are stored within the registry and that takes priority over anything stored in any ‘init.ora’ file. The Windows implementation of Oracle uses standard Microsoft rules - with the information stored in the registry taking precedence. The registry is a key area in terms of backing up your Oracle system to maintain that information and not just the database itself. Perfmon can be used to investigate the performance to some degree. Some of the following panels are incorporated under Perfmon in Windows 2K and XP but are explicit as below in NT.
The Process View utility, which comes with the Windows resource kit, shows the Oracle 8 process itself and how much processor time is being used by that process and its breakdown into privileged and user percentages. See Figure 9. Because all of Oracle is contained underneath that heading, it shows exactly how much CPU time the whole Oracle workload is using. It is more difficult on a UNIX box to arrive at that same figure quickly.
This utility also provides details of the resources consumed by each thread. Since the threads are predefined, it can show (for example) just how much CPU time the DBWR might be using. The Process View utility also shows memory details as in Figure 9. This includes the SGA as well, so it is useful to be able to have a look at this and see just how much memory Oracle is using. Control Panel can be used to see the oracle services on the system, typically ‘OracleService’ and then the name of the instance that you are running (the default instance name on an NT system for Oracle is ORCL) along with its supporting services. The way to start the Oracle Service up is to start the OracleStartORCL service.
Oracle is fully integrated with the Windows architecture. The Windows Event viewer (incorporated in PerfMon in W2K) reports on what is happening on the database. It lists all significant events including starting and stopping Oracle and any errors. Oracle on Windows is also fully integrated with the Windows Performance Monitor, and third parties offer complete performance databases.
Figure 9 Process Viewer under NT & Memory Details
The Windows security system is integrated with Oracle so the Windows login name (defined on the domain controller for example) can be used to provide authentication and to get into the Oracle database itself. An SNMP mib is available and Windows supports TCP/IP. The installation of Oracle on Windows has to be carried out under an account with ‘administrator’ privilege, in other words a sort of a ‘root’ user (in UNIX terms). This is completely the opposite of what happens on UNIX where you must not install Oracle when logged on as ‘root’.
The default Oracle database block size is 2K because that matches the Windows page size. It always makes sense to keep the DB block size in multiples of the O/S blocksize. The default installation locations are the C drive (first hard disk drive) and a directory straight off the root called ORANT. It also creates various sub directories, on Oracle 8 for example, these include a PLUS80 and an RDBMS80 directory. The default instance name is ORCL and there are a number of other important differences from UNIX. There is no ‘Oracle’ user id. as such on Windows, nor is there a ‘dba’group.
The limitations are different for each platform variant, but as an indication, here are some limits for Oracle 8 on Windows NT4.0 Enterprise Edition (which vary for other versions):
?Maximum block size: 16384
?Maximum blocks per file: 4 million blocks
?Maximum possible file size [with 16k blocks]: 64GB
?Maximum files per database (depends on block size)
o with 2K/ 4K/ 8Kor more blocks = 20,000/ 40,000/ 65536 files
?Maximum database size: 65536 * 64GB = c. 4 Petabytes
o(NT as on OS can support up to 17 million Terabytes of disk space)
5.2 Administration tools.
Like any Windows service there are lots of different tools to start and to stop the Oracle service. If you have the Windows resource kit, you can bring up a command prompt and you can stop any process in its tracks immediately by ‘Kill –f’ which is very similar to the UNIX command ‘kill –9’. Do not do that if you can help it because that will just kill the Oracle process and leave things in a very nasty state. To bring Oracle down gracefully, either go into Oracle Enterprise Manager (OEM) or use the server manager command from the command line.
To stop Oracle at the service level, go to the command prompt and type ‘net stop Oracle Service’. This will perform a graceful close down of the Oracle service, (because in fact it does invoke the server manager). The same thing can be achieved by stopping the service from the control panel. Beware of ending the process from the windows Task Manager, which can also give unpredictable results.
The Oracle for Windows program group is set up for you when you install Oracle. The panel shows the range of facilities provided, including the backup manager, recovery manager, SQLnet etc. as well as documentation and the replication manager.
Wizard Technology has also been built in by Oracle for tasks such as the Database Assistant wizard for creating a database. In terms of getting up a SQL prompt there are various alternatives. Typical panels show the Oracle SQLPlus application running and also show some of the properties that you can set in that environment. So, instead of having to remember all the syntax settings there is a simple dialog box here to help you. When you are not a constant user of SQLplus (like me!) this is very useful.
5.3 Scalability
There are various clustering environments available, perhaps the most well known is Microsoft Cluster Server or MSCS (during development this was known as ‘wolfpack’). This is a very simple clustering architecture that was introduced by Microsoft with Windows NT 4.
Basically it is a ‘shared nothing’ model. There are two nodes (by default) in an MSCS cluster. They do not share the disks, so there are separate processes running on the two nodes. Any disks (which are actually plumbed together between the two nodes) are not shared between them, one node owns them exclusively at any one time. The shared nothing model is really to provide failover, not to provide any kind of scalability. The MSCS supports standard Microsoft applications such as MS SQL Server and this is also the model on which the Oracle Failover server is based. IBM has a larger number of cluster nodes than the basic two-node solution, but this is again really just to provide failover.
The second main clustering technology that Microsoft provides is called the ‘Load Balancing Server’ - some technology they actually bought in from Valence Research and was previously known as Convoy Cluster Server. This is positioned by Microsoft for the middle tier of three-tier client server architectures and in particular for things like web servers.
There are other third party solutions, and the important one in this context is Oracle Parallel Server (OPS). It is based on the kind of ‘shared disk’ model that was used on cluster architectures like OpenVMS.
Oracle Fail Safe maps on to the Microsoft Cluster Server architecture. Oracle will be running on one node (which can be quite a large symmetric multi-processing box) but if Oracle fails on one node it can then failover onto the second node. It is still a shared nothing disk model. It is a single instance so there is no kind of distributed lock management, and there is no scalability beyond what can be done within the single node.
OPS is available for Windows Clusters. OPS uses the shared disk model, it supports clusters of up to four nodes (but that is now increasing) and it does have an integrated distributed lock manager, lock mastering and dead lock detection. OPS uses parallel cache management. Each cluster node has its own local cache. The distributed lock manager ensures that the cache remains coherent and that occurs if necessary by ‘pinging to disk’. Full row-level locking is supported in parallel cache management as well. Oracle has recently introduced the concept of “cache fusion” for cache coherency. This uses shared memory between nodes, thereby avoiding the overhead of ‘pinging to disk’.
6. Linux
This section outlines experiences with installing and running Oracle on Linux [Atkinson,00]. As with Windows, Oracle on Linux is less widely used in major production systems and its performance management is generally less well understood than for UNIX or mainframes. However, Linux is gradually becoming a target platform for performance management.
Linux is a UNIX-like OS born in 1991, which was originally targeted at just Intel 386 platforms. It is now on many others, including 64-bit and mainframe systems. It is unique in that it has Open Source and is issued under the GPL. The Kernel was inspired and is controlled by Linus Torvalds. Linux is strictly speaking the OS kernel of a UNIX-like system. Around the Linux kernel there is a collection of other software, typically the bulk of the software on the system and typically based on GNU (i.e. open source utilities such as bash). The “GNU’s Not UNIX” open philosophy was founded in the GNU Project in 1984. GNU is a recursive acronym for “GNU’s Not UNIX”. So perhaps Linux is more properly described as GNU/Linux.
Linux is available for free, even the core of the distributions from vendors. The commercial versions of Linux include extras such as technical support, printed manuals and possibly additional, non-open source software. It may be obtained via “distributions” or “distros”, e.g. Red Hat, Suse, Debian, Mandrake. The commercial packages include support options; it is possible to download the commercial “distros”, or buy cheap CDs from https://www.doczj.com/doc/972635115.html,. The generally available version of the Linux kernel is 2.2.n. The much improved 2.4.n kernel is incorporated into some well-known “distros”.
Linux has been targeted as a serious platform by many major database vendors:
? Oracle
?IBM (UDB/DB2 and Informix-SE)
?Sybase (Sybase ASE), Software AG (Adabas) and CA (Ingres)
?Red Hat Database (https://www.doczj.com/doc/972635115.html,) based around PostgreSQL (https://www.doczj.com/doc/972635115.html,) ? MySQL (https://www.doczj.com/doc/972635115.html,) Open Source under the GNU General Public Licence
A typical Linux Software Environment consists of:
?Kernel - 2.2.x or 2.4.x (Red Hat 7.1 and later)
? Development environment
o glibc
o C library for Linux and other operating systems
o Advance on libc (glibc has been called “libc 6”)
o Current release of glibc is 2.2.4
? Database software
?Apache web server or equivalent
?File sharing (e.g. Samba)
?Other utilities such as mail, firewall, DNS server etc.
The Linux 2.4 kernel took two years to appear. It now supports up to 64 GB of RAM. Oracle makes use of Linux Kernel Features for the SGA shared memory and SMP scalability. There is inter-process communication and concurrency control, with thread support (emulated). Oracle also uses the Linux File I/O and Filesystem. Installing Oracle on Linux is not always straightforward. Experiences vary with the software levels used. When you install a “distro”, the following performance tools are installed by default:
?ps which can produce the same format of output data as other UNIX’s
? Process accounting
? Vmstat
not first
# 2001/02/09 16:00:03
UID PID PPID C STIME TTY TIME CMD
root 1 0 0 15:40 ? 00:00:04 init
root 2 1 0 15:40 ? 00:00:00 [kflushd]
root 3 1 0 15:40 ? 00:00:00 [kupdate]
root 4 1 0 15:40 ? 00:00:00 [kpiod]
root 5 1 0 15:40 ? 00:00:00 [kswapd]
root 6 1 0 15:40 ? 00:00:00 [mdrecoveryd]
bin 320 1 0 15:40 ? 00:00:00 portmap
root 345 1 0 15:40 ? 00:00:00 rpc.statd
root 359 1 0 15:40 ? 00:00:00 /usr/sbin/apmd -p 10 -w 5 -W -s
root 414 1 0 15:40 ? 00:00:00 syslogd -m 0
nobody 437 1 0 15:40 ? 00:00:00 identd -e -o
nobody 440 437 0 15:40 ? 00:00:00 identd -e -o
nobody 442 440 0 15:40 ? 00:00:00 identd -e -o
daemon 455 1 0 15:40 ? 00:00:00 /usr/sbin/atd
Figure 10 – Output captured using “ps –ef”
A suite of other open-source performance utilities is available for installation on a Linux system. The suite is known as sysstat, and the latest version that works with 2.2.n or 2.4.n Linux kernels is version 3.3.5. Sysstat includes the following very useful commands:
? sar
? iostat
? mpstat
A drawback of the sysstat sar is that it does not give disk device activity information (i.e. it is missing the sar –d option). However such information is available from the iostat command. In addition there is no acctcom command available on Linux. The nearest equivalent is the sa command. However, the underlying structure of the pacct file on Linux is the same as on other variants.
Linux 2.4.0 (https://www.doczj.com/doc/972635115.html,) 02/13/01
02:57:10 PM proc/s
02:57:40 PM 0.00
02:57:10 PM cswch/s
02:57:40 PM 6.40
02:57:10 PM CPU %user %nice %system %idle
02:57:40 PM all 0.03 0.00 0.06 99.91
02:57:10 PM pgpgin/s pgpgout/s
02:57:40 PM 0.00 0.33
02:57:10 PM pswpin/s pswpout/s
02:57:40 PM 0.00 0.00
02:57:10 PM tps rtps wtps bread/s bwrtn/s
02:57:40 PM 0.30 0.00 0.30 0.00 2.66
02:57:10 PM frmpg/s shmpg/s bufpg/s campg/s
02:57:40 PM 0.00 0.00 0.00 0.00
02:57:10 PM TTY rcvin/s xmtin/s
02:57:40 PM 0 0.00 0.00
02:57:10 PM kbmemfree kbmemused %memused kbmemshrd kbbuffers kbcached kbswpfree kbswpused %swpused 02:57:40 PM 6408 87944 93.20 0 19588 12596 265032 0 0.00 02:57:10 PM CPU i000/s i001/s i002/s i004/s i011/s i014/s i015/s
02:57:40 PM 0 100.00 0.00 0.00 0.00 0.43 2.66 0.00
Figure 11 – Sample sar data captured using “sar –A 30 1” command:
Running Oracle on Linux is not so different. Once installed, you begin to feel more at home with the software. There is no difference in “look and feel” from other operating system implementations. The performance data comes from the following: ps, sar, iostat, mpstat, lxAPMacctcom and df. There is plenty of information available on the web about Oracle on Linux. Linux distros formally support Oracle and vice versa.
7. Summary
Conclusions
&
The main message of this paper is not to be put off by the apparent complexities of Oracle system behavior. Selecting and concentrating on a small number of key metrics can often gain significant performance improvements. The data required comes from both Oracle and the OS. The Oracle data is readily available from the V$ tables. The OS data is totally domain specific and hence is stored in different ways in different places with different access mechanisms.
The paper includes references to the important metrics which are accessible through standard SQL or OS commands. One of the main problems with the raw metrics is that they are often snapshots at a single instant - and the important thing for characterizing system behavior is not the absolute values, but the way in which those values change over time. Software tools are now available to assist in the process of capturing and storing this data. Such tools also speed up the process of identifying patterns, trends and interrelationships between the observations.
Only a few years ago the accepted wisdom was that the behavior of Oracle systems was so complex that the only possible course of action was to monitor their performance and react to crises as they occurred. This is no longer the case. Modeling techniques are now available which allow the prediction of the future behavior and performance of Oracle systems. This enables accurate forecasts to be made of the change to service levels and resource implications of changing workloads. Thus it is possible to isolate potential performance problems and prevent them or avoid them in advance. This is well established on UNIX and mainframes.
On Windows, once configured, Oracle appears to an end user to be no different to Oracle on any other platform. Oracle 8 is fully integrated with the Windows OS. There is a rich package of GUI tools for controlling the database. With Oracle Parallel Server, users can run an enterprise-ready RDBMS on Windows that scales well beyond the current Microsoft offering.
On Linux, again Oracle feels much the same (though once the installation is done, breathe a sigh of relief). Performance monitoring is achieved via the V$ tables same as with other OS variants The pressures of modern business, together with the importance of quality and consistent end-user service levels, mean that systems must be managed proactively. The techniques described in this paper can be used to ensure that Oracle implementations provide the best possible performance to the end-users – both now and in the future.
8. References
[Atkinson,00]Des Atkinson, Metron, Experiences with installing Oracle on Linux, UKCMG 2000 [Metron,94]Metron, Performance Analysis of an Oracle-based Interactive UNIX System – a Case Study, CMG Proceedings 1994
[Metron,97]Metron, Performance Management and capacity planning for Mainframe Oracle Systems, UKCMG Proceedings 1997
[Metron,98]Metron, Oracle on UNIX and Windows NT: Comparisons and contrasts, UKCMG Proceedings 1998
[Grummitt,94] Adam Grummitt, Metron, Performance Management of Client-server Systems, CMG Proceedings 1994
[Grummitt,95] Adam Grummitt, Metron, Performance analysis of an Oracle based interactive UNIX system, EOUG Proceedings1995
[Grummitt,96] Adam Grummitt, Metron, Client server Performance Management: Evolution or Revolution?, CMG Proceedings 1996
[Metron,99] Metron, The Performance Management of Oracle-Based Systems on Unix - Oracle User Group Journal Issue 35 (Spring 99)
[Shallahamer,00] Craig Shallahamer, OraPub, Inc., Oracle Performance Triage:Stop the Bleeding! [Shallahamer,01] Craig Shallahamer, OraPub, Inc., Response Time Analysis for Oracle systems
基于java实现访问oracle数据库的方法 【摘要】随着Java Web的迅速发展，数据库连接已成为应用程序开发中的主要问题之一，连接数据库的效率也成为直接影响应用程序执行效率的因素。本文以Oracle数据库系统为例，详细介绍了在Java程序中访问数据库的传统数据库的连接方法和数据库连接池技术，并对此进行了分析和比较。 【关键词】Java;数据库;JDBC;连接池 1.引言 随着Java Web技术和计算机网络的迅速发展，人们对网络资源的共享提出了更高的要求。在网络环境中，对于数据的访问往往都是非常频繁的。在Java Web 的应用程序开发中使用Java访问数据库，是整个应用程序开发过程中的主要问题之一。下面就将以Oracle数据库系统为例，阐述在Java程序中访问数据库的传统的数据库连接方法和数据库连接池技术。 2.传统的数据库连接方法 2.1 JDBC简介 JDBC是用java连接数据库最为传统的方法，下面详细阐述在java程序中它具体是怎么实现的。 2.1.1 JDBC概念 JDBC的全称是Java Database Connectivity，即Java数据库连接。JDBC是标准的Java访问数据库的API。JDBC定义了数据库的连接、SQL语句的执行以及查询结果集的遍历等。它由一组用Java编程语言编写的类和接口组成，位于包java.sql下面，如java.sql.Connection，java.sql.Statement，java.sql.ResultSet等。JDBC提供给Java程序使用的，它将各种数据库的差异对Java程序屏蔽了起来，Java程序可以使用同样的可移植的接口访问数据库，使Java的应用程序屏蔽了数据库领域，同时保持了Java语言的“一次编写，各处运行”的优点。 2.1.2 JDBC的结构 JDBC主要有两类接口：面向程序开发人员的JDBC API和面向JDBC驱动程序的JDBC DRIVER API。前者是开发人员用来编写前端应用程序的，后者是由数据库厂商开发的。 （1）JDBC API由两个部分组成，一个是核心的API，其类包路径为javax.sql，这是J2EE的一部分，它具有可滚动的结果集、批量更新的实现类。另一个是扩展的API，其类包的路径为javax.sql，这是J2EE的一部分，它具有访问JNDI
JDBC Java中JDBC定义了java与各种sql数据库之间的编程接口，JDBC API是一个统一的标准应用程序编程接口，这样可以屏蔽异种数据库之间的差异。 JDBC的工作原理 JDBC与ODBC（OpenData Base Connectivity，开放式数据库连接）的作用非常类似，它在应用程序和数据库之间起到了桥梁的作用。ODBC使用的特别广泛，在java中还提供了JDBC-ODBC桥，能够在JDBC与ODBC之间进行转换，这样可以通过ODBC屏蔽不同种数据库之间的差异。在JDK的包java.sql.*中定义了一系列的类、接口、异常以及这些类和接口中定义的属性和方法，java的开发人员通过一定的规则调用java.sql.*中的API就可以实现对数据库管理系统的访问。 JDBC的四种驱动 1.JDBC-ODBC Bridge JDBC-ODBC Bridge可以访问一个ODBC数据源，但在执行Java程序的机器上必须安装ODBC驱动，并作出配置，它的调用方式如下图： 因为中间有个JDBC-ODBC，所以影响了执行效率。 配置ODBC数据源的方法如下（以Mysql为例）： 第一步，打开控制面板—>管理工具--->数据源（ODBC） 选择系统DSN，这一项默认应该是空的，我这里是已经配置好了一个数据源。
第二步，点击添加 第三步，选择Mysql的ODBC驱动，点击完成。 注：MySQL的ODBC驱动在系统中一般是找不到的，因为它不是系统默认自带的驱动，所以，我们要自己安装我们所需要的MySQL的ODBC驱动。安装步骤如下： ①．下载安装包，https://www.doczj.com/doc/972635115.html,/downloads/connector/odbc/ ②．我们下载与自己系统相应的ODBC驱动 这里我们选择
下载的，写的非常好，给大家分享下。 什么时候使用分区： 1、大数据量的表，比如大于2GB。一方面2GB文件对于32位os是一个上限，另外备份时间长。 2、包括历史数据的表，比如最新的数据放入到最新的分区中。典型的例子：历史表，只有当前月份的数据可以被修改，而其他月份只能read-only ORACLE只支持以下分区：tables, indexes on tables, materialized views, and indexes on materialized views 分区对SQL和DML是透明的(应用程序不必知道已经作了分区)，但是DDL 可以对不同的分区进行管理。 不同的分区之间必须有相同的逻辑属性，比如共同的表名，列名，数据类型，约束; 但是可以有不同的物理属性，比如pctfree, pctused, and tablespaces. 分区独立性：即使某些分区不可用，其他分区仍然可用。 最多可以分成64000个分区，但是具有LONG or LONG RAW列的表不可以，但是有CLOB or BLOB列的表可以。 可以不用to_date函数，比如： alter session set nls_date_format='mm/dd/yyyy'; CREATE TABLE sales_range (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE) PARTITION BY RANGE(sales_date) ( PARTITION sales_jan2000 VALUES LESS THAN('02/01/2000'),
Oracle 分区的概念 分区是指将巨型的表或索引分割成相对较小的、可独立管理的部分，这些独立的部分称为原来表或索引的分区。分区后的表与未分区的表在执行查询语句或其他DML语句时没有任何区别，一旦进行分区之后，还可以使用DDL语句对每个单独的分区进行操作。因此，对巨型表或者索引进行分区后，能够简化对它们的管理和维护操作，而且分区对于最终用户和应用程序是完全透明的。 在对表进行分区后，每一个分区都具有相同的逻辑属性。例如，各个分区都具有相同的字段名、数据类型和约束等。但是各个分区的物理属性可以不同，例如，各个分区可以具有不同的存储参数，或者位于不同的表空间中。 如果对表进行了分区，表中的每一条记录都必须明确地属于某一个分区。记录应当属于哪一个分区是记录中分区字段的值决定的。分区字段可以是表中的一个字段或多个字段的组合，这时在在创建分区表时确定。在对分区表执行插入、删除或更新等操作时，Oracle会自动根据分区字段的值来选择所操用的分区。分区字段由1～16个字段以某种顺序组成，但不能包含ROWID等伪列，也不能包含全为NULL值的字段。 图10-1显示了一个典型的分区表。通常在对表进行分区时也会将地对应的索引进行分区，但是未分区的表可以具有分区的索引，而分区的表也可以具有未分区的索引。 索引索引 索引 索引 5月6月7月 未分区的表（分区的索引） 分区的表（分区的索引） 图10-1 分区表与分区索引 一个表可以被分割成任意数目的分区，但如果在表中包含有LONG或LONG RAW类型的字段，则不能对表分区。对于索引组织表而言，虽然也可以分区，但是有如下一些限制： ●索引组织表仅支持范围和散列分区，不能以列表或复合方式对索引组织表进行分 区。 ●分区字段必须是主键字段的一个子集。 ●如果在索引组织表中使用了OVERFLOW子句，溢出存储段将随表的分区进行相 同的分割。 下面给出了应当考虑对表进行分区的一些常见情况： ●如果一个表的大小超过了2GB，通常会对它进行分区。 ●如果要对一表进行并行DML操作，则必须对它进行分区。 ●如果为了平衡硬盘I/O操作，需要将一个表分散存储在不同的表空间中，这时就必 须对表进行分区。 ●如果需要将表的一部分置为只读，而另一部分为可更新的，则必须以它进行分区。
完整java开发中JDBC连接数据库代码和步骤 JDBC连接数据库 ?创建一个以JDBC连接数据库的程序，包含7个步骤： 1、加载JDBC驱动程序： 在连接数据库之前，首先要加载想要连接的数据库的驱动到JVM（Java虚拟机），这通过https://www.doczj.com/doc/972635115.html,ng.Class类的静态方法forName(String className)实现。 例如： try{ //加载MySql的驱动类 Class.forName("com.mysql.jdbc.Driver") ; }catch(ClassNotFoundException e){ System.out.println("找不到驱动程序类，加载驱动失败！"); e.printStackTrace() ; } 成功加载后，会将Driver类的实例注册到DriverManager类中。 2、提供JDBC连接的URL ?连接URL定义了连接数据库时的协议、子协议、数据源标识。 ?书写形式：协议：子协议：数据源标识 协议：在JDBC中总是以jdbc开始 子协议：是桥连接的驱动程序或是数据库管理系统名称。 数据源标识：标记找到数据库来源的地址与连接端口。 例如：（MySql的连接URL） jdbc:mysql: //localhost:3306/test?useUnicode=true&characterEncoding=gbk ; useUnicode=true：表示使用Unicode字符集。如果characterEncoding设置为 gb2312或GBK，本参数必须设置为true 。characterEncoding=gbk：字符编码方式。 3、创建数据库的连接 ?要连接数据库，需要向java.sql.DriverManager请求并获得Connection对象，该对象就代表一个数据库的连接。 ?使用DriverManager的getConnectin(String url , String username , String password )方法传入指定的欲连接的数据库的路径、数据库的用户名和密码来获得。 例如： //连接MySql数据库，用户名和密码都是root String url = "jdbc:mysql://localhost:3306/test" ; String username = "root" ; String password = "root" ; try{ Connection con = DriverManager.getConnection(url , username , password ) ; }catch(SQLException se){ System.out.println("数据库连接失败！"); se.printStackTrace() ; }
Oracle分区技术总结 电信事业部 张雷
一．分区概述： 为了简化数据库大数据量的管理，ORACLE推出了分区选项。分区将表或索引分离在若干不同的表空间上，用分而治之的方法来支撑无限膨胀的大表和索引，从而提高大表和索引在物理一级的可管理性。将它们分割成较小的分区可以改善表和分区的维护、备份、恢复、事务及查询性能。 二．分区的特点： ◆所有的分区的逻辑属性是相同的，但他们的物理属性可以不同。 ◆分区的剪枝（Partition Pruning） Oracle server 可以自动识别分区，根据select 语句所指定的选择条件，只查询 有用的分区。如果语句的条件中对分区字段使用了函数，优化器则不能进行分区剪 枝，但to_date函数除外。 ◆分区的优点 (1) 高可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍 然可以使用； (2) 减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需 要修复，故能比整个大表修复花的时间更少； (3) 维护轻松：对于大型的历史数据表，将其分区，分别管理和方便地添加和删除。； (4) 均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能； (5) 改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行 执行，可使运行速度更快； (6) 基于分区的 join 操作，会提高查询性能 (7) 分区对用户透明，最终用户感觉不到分区的存在。 三．分区的方法： ◆Range Partitioning (范围分区)
范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据城市分区，根据时间进行分区等。实现方法就是在CREATE TABLE命令中增加PARTITION BY RANGE子句。 例如：CREATE TABLE UNITELE.BB_ACCOUNT_INFO_T ( ACCOUNT_ID NUMBER(10), CITY_CODE V ARCHAR2(8 BYTE) NOT NULL, CUSTOMER_ID NUMBER(10) NOT NULL, MAIL_SERVICE NUMBER(4), UNIT_COUNT NUMBER(8) DEFAULT 0, REMARK V ARCHAR2(256 BYTE), IF_VALID NUMBER(2) DEFAULT 1, ACCOUNT_FA VOUR_ID NUMBER(8) DEFAULT 0 NOT NULL ) TABLESPACE TS_TAB_BASE PARTITION BY RANGE (CITY_CODE) ( PARTITION PART840 V ALUES LESS THAN ('841'), PARTITION PART_OTHER V ALUES LESS THAN (MAXV ALUE) ); 分区的字段可以是一个列，也可以是多个列。 ★范围分区的特点 a、Range分区特别适合于按时间周期进行数据的存储。日、周、月、年等。 b、数据管理能力强，可以进行数据迁移，数据备份以及数据交换的操作。 c、范围分区的数据可能分布不均匀。 d、范围分区与记录值相关，实施难度和可维护性相对较差。有可能出现一个表分 成上万个分区，还可能出现后期拆分分区，增加分区的操作。 Hash Partitioning（散列分区）;
Java Web应用与Oracle连接问题 1.概况 刚安装的Java Web应用连接不到数据库，初步判断是数据库连接字符串没有正确配置。通常在Tomcat中可以通过配置WEB-INF下的web.xml或者applicationContext.xml文件来修改数据库连接字符串。 后询问高手，在Tomcat自身路径下也有配置文件： %TOMCAT%\conf\Catalina\localhost\%APPNAME%.xml 修改数据库连接字符串中的IP和SID
今天总结了java平时最常用的7种数据库连接方式，现在分享给大家 MySQL： String Driver="com.mysql.jdbc.Driver"; //驱动程序 String URL="jdbc:mysql://localhost:3306/db_name"; //连接的URL,db_name 为数据库名 String Username="username"; //用户名 String Password="password"; //密码 Class.forName(Driver).new Instance(); Connection con=DriverManager.getConnection(URL,Username,Password); Microsoft SQL Server: 1) String Driver="com.microsoft.jdbc.sqlserver.SQLServerDriver"; //连接SQL数据库的方法 String URL="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=db_name"; //db_name为数据库名 String Username="username"; //用户名 String Password="password"; //密码 Class.forName(Driver).new Instance(); //加载数据可驱动 Connection con=DriverManager.getConnection(URL,UserName,Password); // 2) String Driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"; //连接SQL数据库的方法 String URL="jdbc:sqlserver://localhost:1433;DatabaseName=db_name"; //db_name为数据库名 String Username="username"; //用户名 String Password="password"; //密码 Class.forName(Driver).new Instance(); //加载数据可驱动 Connection con=DriverManager.getConnection(URL,UserName,Password); Sysbase: String Driver="com.sybase.jdbc.SybDriver"; //驱动程序 String URL="jdbc:Sysbase://localhost:5007/db_name"; //db_name为数据可名 String Username="username"; //用户名 String Password="password"; //密码 Class.forName(Driver).newInstance(); Connection con=DriverManager.getConnection(URL,Username,Password); Oracle(用thin模式): String Driver="oracle.jdbc.driver.OracleDriver"; //连接数据库的方法String URL="jdbc:oracle:thin:@loaclhost:1521:orcl"; //orcl为数据库的SID String Username="username"; //用户名 String Password="password"; //密码 Class.forName(Driver).newInstance(); //加载数据库驱动
oracle 表分区 partition技术（转） 2009年06月12日星期五 11:23 一下内容转至：https://www.doczj.com/doc/972635115.html,/blog/166078 一、Oracle分区简介 ORACLE的分区是一种处理超大型表、索引等的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。 二、Oracle分区优缺点 优点：λ 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能； 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。缺点：λ 分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。 三、Oracle分区方法 范围分区：λ 范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。 Hash分区（散列分区）：λ 散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O 设备上进行散列分区，使得这些分区大小一致。 λ List分区（列表分区）： 当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。 范围-散列分区（复合分区）：λ 有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法（注意：先一定要进行范围分区） 范围-列表分区（复合分区）：λ 范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。（注意：先一定要进行范围分区） 四、Oracle表分区表操作 --Partitioning 是否为true select * from v$option s order by s.PARAMETER desc
一、问题引入 在java程序中，需要访问数据库，做增删改查等相关操作。如何访问数据库，做数据库的相关操作呢？ 二、Java连接数据库方法概述 java.sql提供了一些接口和类，用于支持数据库增删改查等相关的操作。该jar包定义了java访问各种不同数据库（mysql，oracle，sqlserver。。。。。）的统一接口和标准。同时，各个数据库厂商都提供了该jar包中定义的各个接口的实现类，用于具体实现本厂数据库的增删改查操作，即称之为“数据库驱动jdbc driver”。例如mysql的数据库驱动为：com.mysql.jdbc.driver；oracle的数据库驱动为：oracle.jdbc.driver.oracledriver。在java程序中访问数据库，做数据库连接时，可以采用两种方式： 1、使用java.sql API 利用该包提供的各种接口和类直接访问数据库。 例子： 2、使用数据库连接池 目前存在多个开源的java数据库连接池，这些连接池都是在java.sql基础上编写而成。 该连接池的解决的问题是： 当使用java.sql中提供的api创建数据库连接时候，需要耗费很大的资源，要进 行用户名密码数据库连接验证等，即耗费资源也耗费时间。如果在程序中，每次需 要访问数据库时候，都进行数据库连接，那么势必会造成性能低下；同时，如果用 户失误忘记释放数据库连接，会导致资源的浪费等。而数据库连接池就是解决该问 题，通过管理连接池中的多个连接对象（connection），实现connection重复利用。 从而，大大提高了数据库连接方面的性能。 该连接池的功能是： 负责创建，管理，释放，分配数据库连接即（connection）。首先，负责创建相应 数目的数据库连接对象（connection）对象，并存放到数据库连接池（connect pool）中。当用户请求数据库连接时，该连接池负责分配某个处于空闲状态的数据库连接 对象；当用户发出释放该数据库连接时，该连接池负责将该连接对象重新设置为空
表分区有以下优点： 1、数据查询：数据被存储到多个文件上，减少了I/O负载，查询速度提高。 2、数据修剪：保存历史数据非常的理想。 3、备份：将大表的数据分成多个文件，方便备份和恢复。 4、并行性：可以同时向表中进行DML操作，并行性性能提高。 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。 Oracle中提供了以下几种表分区： 一、范围分区：这种类型的分区是使用列的一组值，通常将该列成为分区键。示例1：假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下： CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, FIRST_NAME VARCHAR2(30) NOT NULL, LAST_NAME VARCHAR2(30) NOT NULL, PHONE VARCHAR2(15) NOT NULL, EMAIL VARCHAR2(80), STATUS CHAR(1) ) PARTITION BY RANGE (CUSTOMER_ID) ( PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02 ) 在创建表进行分区时，表空间必须先存在，而且建议将不同的分区放入不同的表空间中。 示例2：假设有ORDER_ACTIVITIES表，每6个月对订单进行清理，我们可以按月份对表进行分区，分区代码如下： CREATE TABLE ORDER_ACTIVITIES ( ORDER_ID NUMBER(7) NOT NULL, ORDER_DATE DATE, TOTAL_AMOUNT NUMBER, CUSTOTMER_ID NUMBER(7), PAID CHAR(1) ) PARTITION BY RANGE (ORDER_DATE) ( PARTITION ORD_ACT_PART01 VALUES LESS THAN
随笔- 6 文章- 0 评论- 1 Java连接各种数据库写法 # 示例配置参考，涵盖几乎所有的主流数据库 ############# Oracle数据库######################## # 数据库驱动名 driver=oracle.jdbc.driver.OracleDriver # 数据库URL(包括端口） dburl=jdbc:oracle:thin:@127.0.0.1:1521:zvfdb # 数据库用户名 user=root # 用户密码
password=zvfims ############# DB2数据库######################## # 数据库驱动名 driver=com.ibm.db2.jcc.DB2Driver # 数据库URL(包括端口） dburl=jdbc:db2://127.0.0.1:50000/zvfdb # 数据库用户名 user=root # 用户密码 password=zvfims ############# MySQL数据库######################## # 数据库驱动名
driver=com.mysql.jdbc.Driver # 数据库URL(包括端口） dburl=jdbc:mysql://127.0.0.1:3306/zvfdb # 数据库用户名 user=root # 用户密码 password=zvfims ############# PostgreSQL数据库数据库######################## # 数据库驱动名 driver=org.postgresql.Driver # 数据库URL(包括端口） dburl=jdbcostgresql://127.0.0.1/zvfdb
Java连接数据库全过程 第一次用java连接数据库，开始竟然不知道从哪里下手，后看了很多书，请教了很多高手，忙了一天才连接上，所以我把这个详细的过程整理出来贡献给大家，不当的地方，希望大家告诉我，一定更正。 要准备的软件： 1.操作系统：windows xp或其他系统； 2.java运行环境：JDK 官方下载网址：https://www.doczj.com/doc/972635115.html,/javase/downloads/index.jsp 3.数据库：SQL server 2000； 下面是java连接数据库需要更新的东西，不然连不上的： 1安装SQL SP3补丁：“SQLSERVER2000SP3补丁.exe”，和普通的程序安装一样。 2.把3个jar包：“msbase.jar”，“mssqlserver.jar”，“msutil.jar”加到classpath中，具体的安装方法我已经详细整理：“Java连接数据库配置——加载jar包”。 Java连接数据库的核心程序文件："TestConnect.java"如下 //TestConnect.java import java.sql.*; public class TestConnect { private Connection con=null;//Connection:与特定数据库的连接（会话）。在连接上下文中执行 SQL 语句并返回结果。 String dbDriver="com.microsoft.jdbc.sqlserver.SQLServerDriver";//声明数据库驱动名(这个是微软的驱动名) //String dbDriver="sun.jdbc.odbc.JdbcOdbcDriver";//sun驱动程序名 //String dbUrl="jdbc:odbc:lhs"; String dbUrl="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=lhs";//数据库驱动程序URL,和相应的驱动配套。 String dbUser="sa";//定义数据库登陆用户名 String dbPasw="123";//定义相应用户的登陆密码 //创建数据库连接 public void connect()
深入学习Oracle分区表及分区索引 关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言，基本上可以分成几类： ?Range(范围)分区 ?Hash(哈希)分区 ?List(列表)分区 ?以及组合分区：Range-Hash,Range-List。 对于表而言(常规意义上的堆组织表)，上述分区形式都可以应用(甚至可以对某个分区指定compress属性)，只不过分区依赖列不能是lob,long之类数据类型，每个表的分区或子分区数的总数不能超过1023个。 对于索引组织表，只能够支持普通分区方式，不支持组合分区，常规表的限制对于索引组织表同样有效，除此之外呢，还有一些其实的限制，比如要求索引组织表的分区依赖列必须是主键才可以等。 注：本篇所有示例仅针对常规表，即堆组织表! 对于索引，需要区分创建的是全局索引，或本地索引： l 全局索引(global index)：即可以分区，也可以不分区。即可以建range分区，也可以建hash分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。 l 本地索引(local index)：其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就是说你add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区。 Oracle建议如果单个表超过2G就最好对其进行分区，对于大表创建分区的好处是显而易见的，这里不多论述why，而将重点放在when以及how。 ORACLE对于分区表方式其实就是将表分段存储，一般普通表格是一个段存储，而分区表会分成多个段，所以查找数据过程都是先定位根据查询条件定位分区范围，即数据在那个分区或那几个内部，然后在分区内部去查找数据，一个分区一般保证四十多万条数据就比较正常了，但是分区表并非乱建立，而其维护性也相对较为复杂一点，而索引的创建也是有点讲究的，这些以下尽量阐述详细即可。 range分区方式，也算是最常用的分区方式，其通过某字段或几个字段的组合的值，从小到大，按照指定的范围说明进行分区，我们在INSERT数据的时候就会存储到指定的分区中。 List分区方式，一般是在range基础上做的二级分区较多，是一种列举方式进行分区，一般讲某些地区、状态或指定规则的编码等进行划分。 Hash分区方式，它没有固定的规则，由ORACLE管理，只需要将值INSERT进去，ORACLE 会自动去根据一套HASH算法去划分分区，只需要告诉ORACLE要分几个区即可。 WHEN 一、When使用Range分区 Range分区呢是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中，比如按照时间划分，2008年1季度的数据
详解ORACLE簇表、堆表、IOT表、分区表 簇和簇表 簇其实就是一组表，是一组共享相同数据块的多个表组成。将经常一起使用的表组合在一起成簇可以提高处理效率。 在一个簇中的表就叫做簇表。建立顺序是：簇→簇表→数据→簇索引 1、创建簇的格式 CREATE CLUSTER cluster_name (column date_type [,column datatype]...) [PCTUSED 40 | integer] [PCTFREE 10 | integer] [SIZE integer] [INITRANS 1 | integer] [MAXTRANS 255 | integer] [TABLESPACE tablespace] [STORAGE storage] SIZE：指定估计平均簇键，以及与其相关的行所需的字节数。 2、创建簇 create cluster my_clu (deptno number) pctused60 pctfree10 size1024 tablespace users storage( initial128k next128k minextents2 maxextents20
); 3、创建簇表 create table t1_dept( deptno number, dname varchar2(20) ) cluster my_clu(deptno); create table t1_emp( empno number, ename varchar2(20), birth_date date, deptno number ) cluster my_clu(deptno); 4、为簇创建索引 create index clu_index on cluster my_clu; 注：若不创建索引，则在插入数据时报错：ORA-02032: clustered tables cannot be used before the cluster index is built 管理簇 使用ALTER修改簇属性(必须拥有ALTER ANY CLUSTER的权限) 1、修改簇属性 可以修改的簇属性包括： * PCTFREE、PCTUSED、INITRANS、MAXTRANS、STORAGE * 为了存储簇键值所有行所需空间的平均值SIZE
Oracle中的Java体系结构 目前，使用Java来扩展存储程序是一种很流行的方法。在使用Java类库处理数据的过程中，PL/SQL是必不可少的一环，这是因为PL/SQL 封装了Java类库的数据访问，即任何Java存储对象访问的数据都必须经过PL/SQL。 由于本章的所有内容只被最新的数据库版本所支持，因此它独立于本书的其他章节，以下是本章要介绍的内容： ●Oracle中的Java体系结构 ●Oracle JDBC的连接类型 ●客户端驱动器(Client-side driver)，即JDBC瘦驱动(thin driver) ●Oracle调用接口驱动器，即中间层胖驱动(middle-tier thick driver) ●Oracle 服务器端内部驱动器(Oracle Server-Side Internal Driver)，即 服务器级的胖驱动 ●在Oracle中创建Java类库 ●创建内部服务器的Java函数 ●创建内部服务器的Java过程 ●创建内部服务器的Java对象 ●创建、装载、删除、使用Java类库时的故障诊断 ●映射Oracle类型 本章将向您展示一张Oracle中巨大的Java组织结构图，在解释了Java的体系结构之后，您还会看到开发和扩展Java组件的方法。 使用Java扩展的原因： 我们将使用PL/SQL作为存储Java类库和其他PL/SQL存储程序或匿名块程序之间的接口。我们还会映射Oracle和Java之间的本地数据类型和用户自定义数据类型，以便能定义有效接口和支持JSP (Java Server Page，Java服务器页面)。
1 Oracle中的Java体系结构 Oracle 9i和10g版本的数据库为开发服务器端或内部Java程序组件提供了一个健壮的体系结构。Java组件采用OO (Object-Oriented，面向对象)的结构，这样的结构非常适合Oracle的对象-关系模型(Object-Relational model)。组件的体系结构实际上就是一个库栈，它包含： ●操作系统的平台依赖性，例如UNIX、LINUX、Microsoft Windows； ●依赖Oracle数据库的文件和库管理； ●独立于平台的JVM (Java Virtual Machine，Oracle Java虚拟机)； ●Java内核类库，兼容不同的平台； ●Oracle支持的Java API (Application Programming Interfaces，应用程序接口)，如SQLJ、JDBC和JNDI； ●Oracle的PL/SQL存储对象，为SQL和PL/SQL程序之间提供接口，就像服务器端J ava类库一样。 Oracle和Java库就和普通的文件系统一样来存储和管理应用程序，它们屏蔽了不同操作系统的结构差异和系统限制，从而建立起一个独立于平台的存储、检索和恢复文件的统一处理过程。同时，Java虚拟机为创建有大量文档支持的O O程序提供了一个标准环境。另外，Oracle PL/SQL也为其他PL/SQL存储对象以及SQL访问Java库提供了软件包。 下图5-1给出了Oracle JVM(Java虚拟机)的体系结构。 Oracle JVM使用两种格式的命名空间：长名称和短名称。长名称和Java中类的命名模式是一样的，我们可以用它本来的命名空间来调用存储Java程序。然而，本章中Java示例的名称都是短名称，并且程序也没有放进程序包中。当然，您完全可以将您的Java程序放进程序包中。Java存储代码的命名空间包括了程序包的整个层次。如果命名空间的长度超过30个字符，Oracle在数据字典视图中就使用哈希命名空间。使用DBMS_JAVA包和LONGNAME函数可以获得完整的命名空间，而如果要获取短名称可以使用DBMS_JAVA程序包和SHORTNAME函数。
Java连接oracle数据库 一、普通的连接数据库 1、注册驱动 Class.forName(“oracle.jdbc.driver.OracleDriver”); 2、获取连接 Connection conn=null; conn= DriverManager.getConnection(“jdbc:oracle:thin:@127.0.0.1:1521:XE”,user,pa ssword); 3、建立statement Statement stat=conn.createStatement(); 4、执行SQL语句 stat.execute(“SQL语句”); 5、处理结果集 ResultSet rs=null; rs=stat.executeQuery(“SQL语句”); While(rs.next()){ System.out.println(“id:”+rs.getInt(“id”)+”last_name”+getString(“l ast_name”)); } 6、关闭连接 Rs.close(); Stat.close(); Conn.close();
二、加载properties文件连接数据库并使用PreparedStatement --------------------首先准备xxx.properties文件---------------------user=xxxxx password=xxxxxx driver=oracle.jdbc.driver.DriverOracle url=jdbc:oracle:thin:@127.0.0.1:1521:XE -------------------------------------------------------------------------------- 1、创建properties实例对象 Properties prop=new Properties(); 2、加载xxx.properties文件 prop.load(new FileInputStream(“xxx.properties文件路径”)); 3、获取xxx.properties文件中的属性 Class.forName(prop.getString(“driver”)); conn=DriverManager.getConnetion(prop.getString(“url”,prop)); 4、创建PreparedStatement实例对象并执行语句 String sql=“select*from table_name where id=?And last_name=?”; PreparedStatement ps=conn.preparedStatement(sql); ps.setInt(1,4); ps.setString(2,”nihao”);