Hidden Functions in Oracle

Question

I liked the answers and questions about hidden functions in sql server

What can you tell us about Oracle?
Hidden tables, internal works ..., secret stored procedures, a package that has good utilities ...

Answer 1

Because Apex is now part of every Oracle database, these Apex utility functions are useful even if you are not using Apex:

SQL> declare 2 v_array apex_application_global.vc_arr2; 3 v_string varchar2(2000); 4 begin 5 6 -- Convert delimited string to array 7 v_array := apex_util.string_to_table('alpha,beta,gamma,delta', ','); 8 for i in 1..v_array.count 9 loop 10 dbms_output.put_line(v_array(i)); 11 end loop; 12 13 -- Convert array to delimited string 14 v_string := apex_util.table_to_string(v_array,'|'); 15 dbms_output.put_line(v_string); 16 end; 17 / alpha beta gamma delta alpha|beta|gamma|delta PL/SQL procedure successfully completed. 

Answer 2

"Full table scans are not always bad. Indexes are not always good."

An index-based access method is less efficient at reading rows than a full scan when you measure it in terms of rows available per unit of work (usually for each logical read). However, many tools interpret a full table scan as a sign of inefficiency.

Let’s take an example when you read several hundred invoices in the invoice table and view the payment method in a small search table. Using an index to check the lookup table for each invoice probably means three or four logical io for the invoice. However, a full scan of the lookup table in preparation for a hash join from the invoice data will probably require only a few logical readings, and the hash join itself will be completely absent in memory with almost no cost.

However, many tools will look at this and see a “full table scan” and ask them to use the index. If you do, you can simply cancel your code.

By the way, the dependence on indexes, as in the above example, leads to the fact that the "Hit ratio of the buffer cache" increases. This is why BCHR is basically nonsense as a predictor of system performance.

Answer 3

The power indication is mostly undocumented.

  explain plan for select /*+ cardinality(@inner 5000) */ * from (select /*+ qb_name(inner) */ * from dual) / select * from table(dbms_xplan.display) / -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5000 | 10000 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- 

Answer 4

Buffer cache hit rate is virtually pointless as a predictor of system performance

Answer 5

You can view table data as of a previous time using Flashback Query with certain restrictions.

 Select * from my_table as of timestamp(timestamp '2008-12-01 15:21:13') 

11g has a completely new feature set that preserves more important historical changes.

Answer 6

Frequently rebuilding indexes is almost always a waste of time.

Answer 7

wm_concat works like MySql group_concat, but it is undocumented.

with data:

 -car- -maker- Corvette Chevy Taurus Ford Impala Chevy Aveo Chevy select wm_concat(car) Cars, maker from cars group by maker 

gives you:

 -Cars- -maker- Corvette, Impala, Aveo Chevy Taurus Ford 

Answer 8

The OVERLAPS predicate is not documented.

http://oraclesponge.wordpress.com/2008/06/12/the-overlaps-predicate/

Answer 9

I just found out about the pseudo-column Ora_rowSCN. If you do not configure a table for this, this pcolumn gives you an SCN block. It can be really useful for an emergency: "Oh shit, I don’t have an audit on this table, and I wonder if someone changed the data from yesterday."

But even better if you create a table with Rowdependecies ON. This puts the SCN of the last change on each line. This will help you avoid the Lost Board problem without having to include each column in your query.

IOW, when the application grabs the line to change the user, also select Ora_rowscn. Then, when you publish user editing, add Ora_rowscn = v_rscn in addition to the unique key in the where clause. If someone touched a line after you grabbed it, aka lost editing, the update will correspond to zero lines since ora_rowscn changes.

So great.

Answer 10

If you get the PASSWORD column value on DBA_USERS , you can back up / restore passwords without knowing them:

  ALTER USER xxx IDENTIFIED BY VALUES 'xxxx'; 

Answer 11

Bypass the buffer cache and read directly from disk by reading the direct path.

 alter session set "_serial_direct_read"=true; 

Invokes a checkpoint on a table space (9i) or fast object (10g +), so be careful on busy OLTP systems.

Answer 12

More undocumented material at http://awads.net/wp/tag/undocumented/

Warning: use at your own risk.

Answer 13

I don't know if this is considered hidden, but I was very pleased when I saw this way to quickly see what happened to the SQL statement that you are setting up.

 SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM DUAL; SELECT * FROM TABLE(dbms_xplan.display_cursor( NULL, NULL, 'RUNSTATS_LAST')) ; PLAN_TABLE_OUTPUT ----------------------------------------------------- SQL_ID 5z36y0tq909a8, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM DUAL Plan hash value: 272002086 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | --------------------------------------------------------------------------------------------- | 1 | TABLE ACCESS FULL| DUAL | 1 | 1 | 1 |00:00:00.02 | 3 | 2 | --------------------------------------------------------------------------------------------- 12 rows selected. 

Where:

• E-Rows are graded lines.
• A-Strings are actual strings.
• A-Time is the actual time.
• Buffers are actual buffers.

If the evaluation plan differs from the actual execution by an order of magnitude, you know that you have problems.

Answer 14

Not a hidden function, but the Finegrained-access-control (FGAC) function, also known as row-level security, is what I used in the past, and was impressed with the effectiveness of its implementation. If you are looking for something that ensures that you can control the granularity of how rows are exposed to users with different permissions - regardless of the application that is used to view the data (SQL * Plus, as well as your web application) - then this is precious a rock.

Built-in full-text indexing is more widely documented, but still stands out because of its stability (just try running a full reindexing of columns with full-text indexing on similar data samples in MS-SQL and Oracle, and you will see the speed difference).

Answer 15

WITH clause

Answer 16

Snapshot Tables . Also found in Oracle Lite, it is extremely useful for deploying its own replication engine.

Answer 17

@Peter

In fact, you can bind a Cursor type variable in TOAD, and then use it in your statement and display the results in the result grid.

 exec open :cur for select * from dual; 

Answer 18

Q: How to call a saved cursor from TOAD?

A: Example, change to your cursor, package_name and saved proc name

 declare cursor PCK_UTILS.typ_cursor; begin PCK_UTILS.spc_get_encodedstring( 'U', 10000002, null, 'none', cursor); end; 

Answer 19

Model Proposal (Available for Oracle 10g and Above)

Answer 20

WM_CONCAT for row aggregation

Answer 21

Scalar caching of subqueries is one of the most amazing features in Oracle.

 -- my_function is NOT deterministic but it is cached! select tx, ty, (select my_function(tx) from dual) from t -- logically equivalent to this, uncached select tx, ty, my_function(tx) from t 

The caching subquery above evaluates my_function(tx) only once for a unique tx value. If you have large partitions of the same tx value, this will greatly speed up your requests, even if my_function not declared DETERMINISTIC . Even if it was DETERMINISTIC , you can protect yourself with the possibly expensive SQL -> PL / SQL context switch.

Of course, if my_function not a deterministic function, this can lead to incorrect results, so be careful!