Difference between revisions of "Oracle Dictionary"

From Robs_Wiki
Jump to: navigation, search
(S)
(S)
 
Line 59: Line 59:
 
== S ==
 
== S ==
 
* '''selectivity''': The number of rows returned / total number of rows
 
* '''selectivity''': The number of rows returned / total number of rows
* '''snapshot controlfile''': RMAN needs to get a consistent view of the control file when it sets out to make backup of every datafile. It only needs to know the most recent checkpoint information and file schematic information at the time backup begins. After the backup starts, RMAN needs this information to stay consistent for duration of the backup operation; in other words, it needs a read consistent view of the control file. With the constant updates from the database, this is nearly impossible - unless RMAN were to lock the control file for the duration of the backup. But that would mean the database could not advance checkpoint or switch logs or produce new archive logs. Impossible.
+
* '''snapshot controlfile''': RMAN needs to get a consistent view of the control file when it sets out to make backup of every datafile. It only needs to know the most recent checkpoint information and file schematic information at the time backup begins. After the backup starts, RMAN needs this information to stay consistent for duration of the backup operation; in other words, it needs a read consistent view of the control file. With the constant updates from the database, this is nearly impossible - unless RMAN were to lock the control file for the duration of the backup. But that would mean the database could not advance checkpoint or switch logs or produce new archive logs. Impossible. To get around this, RMAN uses the snapshot control file, an exact copy of your control file that is only used by RMAN during backup and resync operations. At the beginning of these operations, RMAN refreshes the snapshot control file from the actual control file, thus putting a momentary lock on the control file. Then, RMAN switches to the snapshot and uses it for the duration of the backup; in this way, it has read consistency without holding up database activity.
To get around this, RMAN uses the snapshot control file, an exact copy of your control file that is only used by RMAN during backup and resync operations. At the beginning of these operations, RMAN refreshes the snapshot control file from the actual control file, thus putting a momentary lock on the control file. Then, RMAN switches to the snapshot and uses it for the duration of the backup; in this way, it has read consistency without holding up database activity.
 
 
* '''SQL Injection''': SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database.
 
* '''SQL Injection''': SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database.
 
* '''SQL Profile''': A SQL profile is a set of auxiliary information specific to a SQL statement. Conceptually, a SQLprofile is to a SQL statement what statistics are to a table or index. The database can use the auxiliary information to improve execution plans. Once implemented, the database creates the profile and stores it persistently in the data dictionary. If a user issues a statement for which a profile has been built, then the query optimizer uses both the environment and the SQL profile to build a well-tuned plan.
 
* '''SQL Profile''': A SQL profile is a set of auxiliary information specific to a SQL statement. Conceptually, a SQLprofile is to a SQL statement what statistics are to a table or index. The database can use the auxiliary information to improve execution plans. Once implemented, the database creates the profile and stores it persistently in the data dictionary. If a user issues a statement for which a profile has been built, then the query optimizer uses both the environment and the SQL profile to build a well-tuned plan.

Latest revision as of 07:21, 8 October 2020

A

  • adaptive cursor sharing: Oracle 11g uses Adaptive Cursor Sharing to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. more on adaptive cursor sharing...
  • bitmap join index: In a bitmap join index, the ROWIDs from one table are stored along with the indexed column from the joined table. The bitmap join index in Oracle is a lot like building a single index across two tables. You must build a primary key or unique constraint on one of the tables. When you are looking for information from just the columns in the index or a count, then you will be able to access the single join index.
  • ASSM (Automatic Segment Space Management): ASSM is a method used by Oracle to manage space inside data blocks. It eliminates the need to specify parameters like PCTUSED, Freelists and Freelist groups for objects created in the tablespace.

B

  • bind variable peeking: Bind Variable Peeking is a feature introduced in 9i that was added to allow the optimizer to peek at the value of bind variables and then use a histogram to pick an appropriate plan.
  • buffer gets: A buffer get represents the number of times Oracle had to access a block. The reads could have been satisfied either from memory (the buffers) or have resulted in a physical IO.

C

  • cardinality: Cardinality is the number of rows the optimizer guesses will be processed for a plan step. cardinality for a single column = total # rows / number of distinct values. For example, a table with 100 rows and 10 distinct value has a cardinality of 10. Because Oracle assumes an even distribution if no histograms are being used.
  • column density: Density is a measure of how often duplicate values occur in a column. It is as a measure of the uniqueness of the data in a column: high density --> less unique data. Density values range from 0 to 1.0. There are different (but equivalent) ways to think of density. Density = 1/[# of distinct values in a column] / Density = Avg. number of duplicates for a given value / Total row count
  • CIDR: Classless Inter-Domain Routing is a method for allocating IP addresses and IP routing. The Internet Engineering Task Force introduced CIDR in 1993 to replace the previous addressing architecture of classful network design in the Internet. Its goal was to slow the growth of routing tables on routers across the Internet, and to help slow the rapid exhaustion of IPv4 addresses.
  • copy on write versus redirect on write: Different snapshot technologies. Read more more_copy_on_write

D

  • deprecated: The database feature is no longer being enhanced but is still fully supported" A well known example is Oracle Streams which is fully supported in Oracle Database 12c - but not in Oracle Multitenant - and is deprecated and therefore not enhanced or developed any further. Query for deprecated parameters: SELECT NAME,VALUE FROM V$PARAMETER WHERE ISDEFAULT='FALSE' AND ISDEPRECATED='TRUE';
  • domain index: The domain index is another feature, first introduced in Oracle 8i as an extensible index. The domain index is used for indexing non-traditional data, such as LOB data, video data and other non-textual columns.
  • direct path read: A direct path read is where the data is read directly from the data files into the PGA rather than into the buffer cache in the SGA.
  • driving table: In Oracle, the driving table is the table that is joined to other tables in an SQL query. The driving table is read once and for each row in the driving table, the inner table is processed once. more on driving table...

H

  • Hyperthreading: For each processor core that is physically present, the operating system addresses two virtual (logical) cores and shares the workload between them when possible. The main function of hyper-threading is to increase the number of independent instructions in the pipeline; it takes advantage of superscalar architecture, in which multiple instructions operate on separate data in parallel. With HTT, one physical core appears as two processors to the operating system, allowing concurrent scheduling of two processes per core. In addition, two or more processes can use the same resources: If resources for one process are not available, then another process can continue if its resources are available

I

  • Index clustering factor: The clustering factor records the number of blocks that will be read when scanning the index. If the index being used has a large clustering factor, then more table data blocks have to be visited to get the rows in each index block (because adjacent rows are in different blocks). If the clustering factor is close to the number of blocks in the table, then the index is well ordered, but if the clustering factor is close to the number of rows in the table, then the index is not well ordered.
  • Index Skip Scan: Prior to Oracle 9i, it was not possible to perform an Index Scan (Range or Unique) on a concatenated index unless the WHERE clause included a predicate on the leading column(s) of the index. Index Skip Scan allows Oracle to use an index when the first column is not supplied in the WHERE clause.
  • Inline View: An inline view is a SELECT statement in the FROM-clause of another SELECT statement. See examples_inline_view
  • IOT: Index Organized Tables (IOT) have their primary key data and non-key column data stored within the same B*Tree structure. Effectively, the data is stored within the primary key index. There are several reasons to use this type of table.

J

  • JSON: JSON is short for JavaScript Object Notation, and is a way to store information in an organized, easy-to-access manner. In a nutshell, it gives us a human-readable collection of data that we can access in a really logical manner. More info here: JSON_INFO

L

  • loopback ip address: A loopback address is primarily used as a means to validate that the locally connected physical network card is working properly and the TCP/IP stack installed. Typically, a data packet sent on a loopback address, never leaves the host system and is sent back to the source application.

O

  • OCPU: OCPU per hour is the number of Oracle Compute Unit (OCPU) hours used as part of the OAC. The OCPU “provides the CPU capacity equivalent of one physical core of an Intel Xeon processor with hyper threading enabled,” according to Oracle.

P

  • PCTFREE: This parameter is part of a segment creation statement. The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block.
  • PCTUSED: This parameter is part of a segment creation statement. The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED.
  • proxy server: proxy server forward client requests or (reverse proxies) server responses to the server (forward proxy / users (reverse proxy). They can also provide: load balancing, ip masking, traffic scrubbing, firewall services and content caching.

R

  • real-time apply: Dataguard option that ships changes directly (rather than waiting for the archive to be filled first) from the Primary to the Standby database. Makes use of Standby Redo Log Files. Check the setting with this query: select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;
  • recursive sql: In order to execute a SQL statement issued by a user, Oracle must issue additional statements, for example to check if the queried table exist, if the user has permission on the table, etc. Such statements are called recursive statements or recursive sql.
  • redirect on write: Different snapshot technologies. Read more more_copy_on_write

S

  • selectivity: The number of rows returned / total number of rows
  • snapshot controlfile: RMAN needs to get a consistent view of the control file when it sets out to make backup of every datafile. It only needs to know the most recent checkpoint information and file schematic information at the time backup begins. After the backup starts, RMAN needs this information to stay consistent for duration of the backup operation; in other words, it needs a read consistent view of the control file. With the constant updates from the database, this is nearly impossible - unless RMAN were to lock the control file for the duration of the backup. But that would mean the database could not advance checkpoint or switch logs or produce new archive logs. Impossible. To get around this, RMAN uses the snapshot control file, an exact copy of your control file that is only used by RMAN during backup and resync operations. At the beginning of these operations, RMAN refreshes the snapshot control file from the actual control file, thus putting a momentary lock on the control file. Then, RMAN switches to the snapshot and uses it for the duration of the backup; in this way, it has read consistency without holding up database activity.
  • SQL Injection: SQL injection usually occurs when you ask a user for input, like their username/userid, and instead of a name/id, the user gives you an SQL statement that you will unknowingly run on your database.
  • SQL Profile: A SQL profile is a set of auxiliary information specific to a SQL statement. Conceptually, a SQLprofile is to a SQL statement what statistics are to a table or index. The database can use the auxiliary information to improve execution plans. Once implemented, the database creates the profile and stores it persistently in the data dictionary. If a user issues a statement for which a profile has been built, then the query optimizer uses both the environment and the SQL profile to build a well-tuned plan.

V

  • varchar2 byte versus varchar2 char: If you define the field as VARCHAR2(11 BYTE), Oracle can use up to 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store, e.g. non-English characters. By defining the field as VARCHAR2(11 CHAR) you tell Oracle it can use enough space to store 11 characters, no matter how many bytes it takes to store each one. A single character may require up to 4 bytes.