Oracle Misnomers 1. xxx_tab_privs, where xxx is dba, all, or user: This view may as well be called xxx_obj_privs because not everything in it is a table. 2. xxx_synonyms.table_name: Same here. Table_name is not always the name of a table. 3. Clustering_factor: The higher the number, the more UNlikely two adjacent keys in the index can locate their table rows in the same block. Would be nice if it were called its opposite, scattering_factor! 4. Height balanced histogram: Jonathan Lewis "Cost Based Oracle" p.157 says, according to Wolfgang Breitling, the technical term in computer science is never height balanced histogram, but equi-depth histogram, instead. But if you think about what the height represents, a name like height-as-count histogram may be OK. 5. Maxlogfiles, maxlogmembers, and maxloghistory in create controlfile statement (modified from http://groups.google.com/group/comp.databases.oracle.server/msg/acba1f5d68596f3e): In spite of the names, maxlogfiles is the limit of log file groups, not log files, and maxlogmembers is the limit of logfile members per group, not for the entire database. Maxloghistory is not a misnomer, but it's worth explaining. All versions of documentation up to 10gR2, SQL Reference about create controlfile, says that maxloghistory is for RAC or OPS only. That's a misleading statement. Whether it's in a RAC or single instance database, maxloghistory sets the limit for the total number of logfiles, i.e. logfile members, that will be kept in control files, visible in v$log_history; the count is on logfiles, not logfile groups as documentation says. It's odd that Metalink Note:1060139.6 says v$log_history is replaced by v$archived_log (or is it only for Oracle8?). Although v$archived_log is more detailed (has more columns) than v$log_history, v$archived_log most likely has much less entries. According to http://asktom.oracle.com/pls/ask/f?p=4950:8:4899746395882994016::NO::F4950_P8_DISPLAYID,F4950_P8_CRI TERIA:1560536553318 (see the part "3 & 4)"), the number of entries in v$archived_log is determined by control_file_record_keep_time and the number of entries in v$log_history is controled by the maxloghistory. But there could be more entries if you once had a lot of logs generated within that time period pushing the reusable section of the control file to be larger. We can say the lower limit of v$archived_log entries is control_file_record_keep_time (in terms of time span). But the upper limit, even though not discussed anywhere, may we say it's maxloghistory, if the section of the control file is ever pushed to be that large! (According to Note:217718.1, the unit of maxloghistory is Oracle blocks, not records. It says the block size is the same as db_block_size. But I wonder if it's controlfile block size as determined by `dbfsize controlfilepath` on UNIX or v$controlfile.block_size in 10gR2 and x$kcccf.cfbsz in 10gR1.) 6. Breakable parse lock: A lock implies that something cannot be used unless you have the key or otherwise have permission. Even with the word "breakable", a lock is a misnomer here because a parse lock does not stop a session from breaking it at all. A better name may be parse trigger, or to avoid the word trigger since it has another meaning, parse alert, parse alarm, parse hook, etc. See also http://groups.google.com/group/comp.databases.oracle.server/msg/8b0daf7817b417a7 7. Oracle homonyms: (1) snapshot "snapshot too old" (error message for ORA-1555); materialized view (snapshot) (2) partition partitions of a table or index; hash partitions; partitions in analytic functions (3) node nodes in RAC (OPS); B*-tree index nodes; hanganalyze output (4) SID instance ID; session ID (5) cluster as in RAC; as in "create cluster" SQL statement 8. Wait_time, seconds_in_wait of v$session_wait (and v$session in 10g): It's easy to get confused because they mean the same thing in plain English. Wait_time is better called last_wait_time or last_wait_duration because it's only meaningful when the session is using CPU (on CPU) and not waiting.[note1] Seconds_in_wait may be called seconds_since_start_of_latest_wait. If the session is waiting i.e. not on CPU, it may be called current_wait_time for short (but unit is seconds, not centiseconds as for wait_time). If the session is on CPU, you have to use that mouthful long name. ------- ---------- ---|||||||-----||||||||||------> time A B x C y D Suppose the process is on CPU during AB and CD, idle otherwise ("idle" in the sense of "waiting", "not on CPU"). If current time is x, seconds_in_wait is Bx. If current time is y, wait_time is BC and seconds_in_wait is By. 9. "db file scattered read" and "db file sequential read": This well-known confusion arises because "db file scattered read" is often associated with full table scan and index fast full scan and "db file sequential read" is with index read. Intuitively, a full table scan (or index ffs) reads table blocks sequentially on disk. Why is the event called scattered read? Oracle chose the term "scattered" with emphasis on the destination, rather than source, of the read operation, memory. On UNIX/Linux, the event usually corresponds to a readv(2) system call which scatters the read blocks in memory. Likewise, sequential read arranges the read blocks in contiguous momery locations. If you think Oralce named them wrong and should switch the two terms around, that would still cause confusion from the read destination perspective. So neither convention makes you happy! Incidentally, sometimes Oracle does use the word "serial" in the sense of *sequentially* reading a table from head to toe, as in initialization parameter _serial_direct_read, or bit 19 of x$bh.flag only_sequential_access. No more confusion please! In 12c, v$event_name gives more meaningful display names for the events, "db multiblock read" for "db file scattered read" and "db single block read" for "db file sequential read". But these new names are not carried to views such as v$system_event, v$session_event. 10. Is driving table outer or inner table? Oracle documentation says, "In a nested loop join, for every row in the outer row set, the inner row set is accessed to find all the matching rows to join. Therefore, in a nested loop join, the inner row set is accessed as many times as the number of rows in the outer row set." If you think the "inner row set" sounds like "outer row set" to you, consider it a mistake Oracle made a long time ago by putting the driving table on the most indented line in the displayed execution plan while the natural programming logic is for each row fetched from driving table fetch a row from driven table do something end driven table loop end driving table loop So, is the driving table or driven table[note2] the outer table? It depends on the context you're talking about it in. Apparently, the quoted text is referring to the actual program, not explain plan output. 11. Use_Concat and No_Expand are too opposite hints. (Confirm by 11g new view v$sql_hint name and inverse columns.) Why is the second, negative, one not called No_Concat, or the first one called Use_Expand? 12. Oracle is habitually bad at confusing disk reads / writes with disk blocks read / written. One place is v$sql(area).disk_reads. Another is the number in tkprof result under "disk". Both should be disk blocks read (see ../diskreadunit.txt). Jonathan Lewis also points out that 'physical reads direct' and 'physical writes direct' statistics count the blocks read and written, not read and write requests (see "Cost-Based Oracle Fundamentals", p.356). Similarly in spite of the names, 'physical reads' and 'physical writes' count blocks, which Reference documentation does correctly point this out. 13. v$latch_parent contains latches that don't have children (solitary latches). In line with the name v$latch_children, v$latch_parent really should only have the rows where latch#'s are in v$latch_children. You don't call a man or woman that doesn't have kids a parent, do you? Actually, v$latch_parent can still stay as is, but a better name may be v$latch_adult or v$latch_adults. 14. nfb and nfl in data block dump are not simply number of freelist blocks and number of freelists, respectively. (Also see ./FreelistBlocks.txt) 15. Parse count (total): This statistic includes hard and soft parses, *plus the number of times the cursor is found in session cursor cache*! When a cursor (more precisely, the child cursor address) is found in session cursor cache, even a soft parse is avoided. 16. Dbms_repair package would be better called dbms_fence because it does not repair, but mark and skip corrupted blocks instead. (Ref: http://www.freelists.org/post/oracle-l/Block-corruption-while-rebuilding-the-tablespace,2) 17. V$object_usage would be better called user_index_usage, "index" for index type objects only, "user" for the currently logged-on user only, and removing v$ for lack of gv$ view (no need anyway). In Oracle 12.2, new views dba_index_usage and v$index_usage_info are named more sensibly. (Ref: https://docs.oracle.com/database/122/REFRN/toc.htm) 18. "Execute to Parse %" in AWR is not the ratio of execute calls to parse calls (nor the opposite). According to https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1594740500346667363 the formular is round (100 * (1 - :prse / :exe), 2) pctval So it's the parse calles divided by execute calls, but then use the difference of 1 minus that ratio. Because of this 1 minus it, the number is between 0 and 1 or 100% (non-inclusive). When it's close to 0, it's bad because you have too many parses, almost the same as executes. When it's close to 1, it's good, because you have few parses relative to executes. 19. In RMAN, "target" refers to the database you do backup and recovery work on. The name is not intuitive, and in case of database duplication, is misleading, because it refers to the source database and the auxiliary is the actual target. 20. "Logical" read is and is not memory read A logical read literally is indeed a read from a buffer in the buffer cache: http://docs.oracle.com/database/122/CNCPT/glossary.htm#CNCPT94718 "logical read A read of a buffer in the database buffer cache." But in practice, as in calculating statistics, the count of logical reads includes the number of normal physical reads. (In most contexts, this physical read refers to the number of blocks, not the number of times of reading, from the datafile. The two values may differ because of multiblock reads.) This is simply because if Oracle doesn't find a block in the buffer cache, it copies the block from the datafile into buffer cache and then reads it. E.g. we can say, "Out of 1000 logical reads of this table, 800 are directly from buffer cache and 200 are from the datafile first, followed by reading (i.e. actually examining the contents of) these 200 memory buffers." Tom Kyte said https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6643159615303 "LIO = logical = buffer cache. LIO *may* have incurred a PIO in order to get into the cache in the first place." That's exactly what I meant. Later in the thread, we see him say --- begin quote --- If I ask a query like: select * from emp where empno = :x I would expect 3 logical IOs for the index and one for the table. I would expect 4 logical IO's --- end quote --- I don't think he was making a distinction between buffer cache read and physical read when he says 3 or 4 "logical IOs" here. Also, according to https://docs.oracle.com/cd/E24628_01/em.121/e25160/oracle_database.htm#EMDBM1534 "The data [for calculating Buffer Cache Hit (%)] is derived from the ((DeltaLogicalGets - (DeltaPhysicalReads - DeltaPhysicalReadsDirect)) / DeltaLogicalGets) * 100 formula" We see that the word "logical" includes "physical", at least in the phrase "logical get" as compared to "physical get". If "logical" and "physical" were mutually exclusive in this formula, logical gets minus physical gets would make no sense. ____________ [note1] v$session_wait.wait_time=0 generally means the session is waiting, and v$session_wait.state will be 'WAITING'. But in 9i, wait_time can also be zero because of rounding a very small wait time. In this case, only state 'WAITING' should be trusted. Ref: C. Millsap et al. "Optimizing Oracle Performance", p.194. [note2] Nowhere else will you see the phrase "driven table"! But you know what I mean.