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, 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_CRITERIA: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 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. 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 _serial_direct_read or only_sequential_access for bit 19 of x$bh.flasg. No more confusion please! 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. 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, although 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. See http://yong321.freeshell.org/oranotes/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. ____________ [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.