[ACCEPTED]-Hidden features in Oracle-oracleinternals

Accepted answer
Score: 15

Since Apex is now part of every Oracle database, these 2 Apex utility functions are useful even if 1 you aren't 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.
Score: 12

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

An index-based access 25 method is less efficient at reading rows 24 than a full scan when you measure it in 23 terms of rows accessed per unit of work 22 (typically per logical read). However many 21 tools will interpret a full table scan as 20 a sign of inefficiency.

Take an example where 19 you are reading a few hundred invoices frmo 18 an invoice table and looking up a payment 17 method in a small lookup table. Using an 16 index to probe the lookup table for every 15 invoice probably means three or four logical 14 io's per invoice. However, a full scan of 13 the lookup table in preparation for a hash 12 join from the invoice data would probably 11 require only a couple of logical reads, and 10 the hash join itself would cmoplete in memory 9 at almost no cost at all.

However many tools 8 would look at this and see "full table scan", and 7 tell you to try to use an index. If you 6 do so then you may have just de-tuned your 5 code.

Incidentally over reliance on indexes, as 4 in the above example, causes the "Buffer 3 Cache Hit Ratio" to rise. This is why the 2 BCHR is mostly nonsense as a predictor of 1 system efficiency.

Score: 9

The cardinality hint 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 |
 --------------------------------------------------------------------------

0

Score: 8

The Buffer Cache Hit Ratio is virtually 1 meaningless as a predictor of system efficiency

Score: 8

You can view table data as of a previous 4 time using Flashback Query, with certain 3 limitations.

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

11g has a whole new feature 2 set around preserving historical changes 1 more robustly.

Score: 7

Frequent rebuilding of indexes is almost 1 always a waste of time.

Score: 7

wm_concat works like the the MySql group_concat but 1 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
Score: 6
Score: 5

I just found out about the pseudo-column 19 Ora_rowSCN. If you don't set your table 18 up for this, this pcolumn gives you the 17 block SCN. This could be really useful for 16 the emergency, "Oh crap I have no auditing 15 on this table and wonder if someone has 14 changed the data since yesterday."

But even 13 better is if you create the table with Rowdependecies 12 ON. That puts the SCN of the last change 11 on every row. This will help you avoid a 10 "Lost Edit" problem without having to include 9 every column in your query.

IOW, when you 8 app grabs a row for user modification, also 7 select the Ora_rowscn. Then when you post 6 the user's edits, include Ora_rowscn = v_rscn 5 in addition to the unique key in the where 4 clause. If someone has touched the row since 3 you grabbed it, aka lost edit, the update 2 will match zero rows since the ora_rowscn 1 will have changed.

So cool.

Score: 4

If you get the value of PASSWORD column on DBA_USERS you 2 can backup/restore passwords without knowing 1 them:

 ALTER USER xxx IDENTIFIED BY VALUES 'xxxx';
Score: 3

Bypass the buffer cache and read straight 3 from disk using direct path reads.

alter session set "_serial_direct_read"=true;

Causes 2 a tablespace (9i) or fast object (10g+) checkpoint, so 1 careful on busy OLTP systems.

Score: 3

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

Warning: Use 1 at your own risk.

Score: 3

I don't know if this counts as hidden, but 6 I was pretty happy when I saw this way of 5 quickly seeing what happened with a SQL 4 statement you are tuning.

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 is estimated rows.
  • A-Rows is actual rows.
  • A-Time is actual time.
  • Buffers is actual buffers.

Where the 3 estimated plan varies from the actual execution 2 by orders of magnitude, you know you have 1 problems.

Score: 3

Not a hidden feature, but Finegrained-access-control 14 (FGAC), also known as row-level security, is 13 something I have used in the past and was 12 impressed with the efficiency of its implementation. If 11 you are looking for something that guarantees 10 you can control the granularity of how rows 9 are exposed to users with differing permissions 8 - regardless of the application that is 7 used to view data (SQL*Plus as well as your 6 web app) - then this a gem.

The built-in 5 fulltext indexing is more widely documented, but 4 still stands out because of its stability 3 (just try running a full-reindexing of fulltext-indexed 2 columns on similar data samples on MS-SQL 1 and Oracle and you'll see the speed difference).

Score: 3
Score: 2

Snapshot tables. Also found in Oracle Lite, and 2 extremely useful for rolling your own replication 1 mechanism.

Score: 2

@Peter

You can actually bind a variable of type 3 "Cursor" in TOAD, then use it 2 in your statement and it will display the 1 results in the result grid.

exec open :cur for select * from dual;
Score: 1

Q: How to call a stored with a cursor from 2 TOAD?

A: Example, change to your cursor, packagename 1 and stored proc name

declare cursor PCK_UTILS.typ_cursor;  

begin   
    PCK_UTILS.spc_get_encodedstring(  
        'U',  
        10000002,  
        null,  
        'none',  
        cursor);  
end;
Score: 1

The Model Clause (available for Oracle 10g and up)

0

Score: 1

WM_CONCAT for string aggregation

0

Score: 1

Scalar subquery caching is one of the most surprising features 9 in Oracle

-- my_function is NOT deterministic but it is cached!
select t.x, t.y, (select my_function(t.x) from dual)
from t

-- logically equivalent to this, uncached
select t.x, t.y, my_function(t.x) from t

The "caching" subquery 8 above evaluates my_function(t.x) only once per unique value 7 of t.x. If you have large partitions of the 6 same t.x value, this will immensely speed up 5 your queries, even if my_function is not declared DETERMINISTIC. Even 4 if it was DETERMINISTIC, you can safe yourself a possibly 3 expensive SQL -> PL/SQL context switch.

Of 2 course, if my_function is not a deterministic function, then 1 this can lead to wrong results, so be careful!

More Related questions