1. To get data thats deleted or updated and commited... FLASHBACK TABLE table_name TO
TIMESTAMP systimestamp - interval '1' minute; SELECT * FROM table_name
AS OF TIMESTAMP systimestamp - interval '1' minute;
Select * from v$Parameter where name like 'undo%'; -- the value needs to be AUTO -- and undo retention value is in seconds, thats the time allowed to undo
2. temporal validity
CREATE TABLE table_name( emp_id integer not null, in_date date not null, out_date date not null,
PERIOD FOR emp (in_date, out_date) );
SELECT * FROM table_name AS OF PERIOD FOR emp sysdate;
exec DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ALL'); exec DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT');
3. Performance gain on tables which we access frequently and not changing frequently
ALTER TABLE table_name RESULT_CACHE (MODE FORCE); /*+ result_cache */
ALTER SESSION SET RESULT_CACHE_MODE = 'FORCE';
First execution takes normal time, Sus sequent execution will not take time.
4. Column Update Mystery - Update one column and automatically update another column
Use Virtual Column -
column_name varchar2(20) GENERATED ALWAYS AS ( CAST(get_char(col1) as varchar2(20) ) )
Declare function as DETERMINISTIC
Only on standard heap tables, not on Index organized, clustered tables...
5. ALTER SESSION SET plsql_warnings = 'enable:all'
To get the possible run time errors while compilation itself. Also suggests the possible changes that help in performance. Does unreachablility analysis as well.
---
1. Use the benefits of RESULT CACHE -- /*+ result_cache */
2. Enable RESULT CACHE whenever needed. -- ALTER SESSION SET RESULT_CACHE_MODE = 'FORCE';
Create the function with RESULT_CACHE whenever needed. RESULT_CACHE - Session, DETERMINISTIC - Whole SGA
3. Gather extended statistics if necessary -- SELECT
dbms_stats.create_extended_stats (numm,'TABLE','(Col1,Col2)') FROm dual;
exec dbms_stats.gather_table_stats (null,'TABLE',method_opt=>'for all columns size skewonly for columns(Col1,Col2)');
4. Take advantage of bulk processing fetures
SELECT col1,col2 BULK COLLECT INTO v_col1, v_col2 FROM Table; FORALL i IN v_col1.FIRST .. v_col1.LAST
UPDATE table SET col2 = col2 +1 WHERE col1 = v_col1(i); -- We cant use IF or CASE in between these
5. Certain syntax can cause the optimizer to not consider an index 1. Operators such as <>, !=
2. Where upper(col1) = 'SMITH' 3. Implicit conversions
4. LIKE '%A' -- wild card string in starting of the 5. Arithimetic operators or || operator
6. Pass parameters by reference
Use the NOCOPY option when passing the parameter 7. Perform joins with the correct join methord
Nested loop, Hash, Sort-Merge
Nested loop - better for joining small result sets whhere the join columns are indexed
Hash - better for joining larger result set where one or more indexes are missing on the join column
Sort-Merge - better than nested loop joins for joining larger result sets but typically not as good as hash
8. Compare performance between alternative syntax for your code.
9. Use correlation ids when performing joins, even on columns where they are not required.
10. Analyze joins one by one and check that their use makes sense in each circumstance
11. Eliminate rows as early as possible in the join order
If filters are in the query along with joins, where in the execution plan do the filters take place in regards to where the joins take place...
12. Understand potential bottlenecks within Oracle's architecture Memory Structures
Buffer cache - All flavors of data
Shared pool - Code and supporting information PGA - Temporary work area - sorts for example Redo log buffer - Data chnages
Data changes
13. Undesrtanding blocks and block size PCTFREE PCTUSED
Read the fewest no of blocks possible to get the desired result set 14. Be aware of session and system parameters and their settings.
OPTIMIZER_MODE
DB_FILE_MULTIBLOCK_READ_COUNT
OPTIMIZER_INDEX_CACHING -- Higher this no, its high possibility that this index is there in buffer
OPTIMIZER_COST_ADJ -- default is 100. Lower this no, Index costs less than FTS
15. When creating multi column index, choose an optimal order of the columns 16. Avoid unnecessary sorts
Its done in PGA
casued by -- ORDER BY, DISTINCT, GROUP BY, UNION, INTERSECT, MINUS, Hash-join, Sort-Merge Hash-join, Creating an index, generating statistics
17. Do not overuse SELECT ... FROM DUAL;
instead use like assignment operation. v_var := SYSDATE;
18. Follow SQL and PL/SQl standards. Have a document and code reviews 19. Take advantage of available tuning tools
SQL Tuning Advisor PL/SQL Profiler Explain Plan Autotrace
Trace events 10046 and 10053(Shows the plan which it choose and tells the list of plans it generated and rejected)
20. Use hints as a temporary solution
---
---Oracle PL/SQL Best Practices Part 1
https://www.youtube.com/watch?v=IvsVGaTuHhY
1. Avoid initilization in declaration step. Because its not handled in the exception... 2. Instead of CURSOR r1 is SELECT .... FOR UPDATE; BEGIN
FOR v_rec IN r1 LOOP IF ...
UPDATE ... END IF;
END LOOP; COMMIT;
END; Use this...
CURSOR r1 is SELECT ....
FOR UPDATE OF e.sal; <---BEGIN
FOR v_rec IN r1 LOOP IF ...
UPDATE emp SET sal ... END IF; END LOOP; COMMIT; END; 3. Instead of v_name VARCHAR2(200); USe this... v_name emp.name%TYPE; <---v_rec emp%ROWTYPE; <---4. Instead of
SELECT SYSDATE INTO v_date FROm dual; USe this...
v_date := SYSDATE;
Consider using SELECt ...BULK COLLECT and FORALL type statements to reduce context switches.
5. Instead of
While looping collections i := i +1;
USe this...
i := v_num.next(i); <---6. Instead of
BEGIN
INSERT INTO dept VALUES(88,'hr',44500); COMMIT;
EXCEPTION
WHEN others THEN
dbms_output.put_line(SQLCODE); END; Use this... DECLARE v_2291_error EXCEPTION; PRGMA EXCEPTION_INIT(v_2291_error,-2291); <---BEGIN
COMMIT; EXCEPTION
WHEN v_2291_error THEN
dbms_output.put_line(SQLCODE); END;
7. Instead of DECLARE
TYPE v_nums IS TABLE OF emp.sal%TYPE INDEX BY binary_integer;
v_new_sals v_nums; BEGIN
UPDATE emp SET sal = sal * 1.1;
SELECT sal BULK COLLECt INTO v_new_sals FROM emp; END;
Use this... DECLARE
TYPE v_nums IS TABLE OF emp.sal%TYPE INDEX BY binary_integer;
v_new_sals v_nums; BEGIN
UPDATE emp SET sal = sal * 1.1
RETURNING sal BULK COLLECT INTO v_new_sals; <---END;
---
---Five Oracle 12c SQL, PL/SQL New Features To Take Advantage Of https://www.youtube.com/watch?v=PeXMOX1kxSU
1. Top-n Queries
Row limiting clause
FETCH FIRST/NEXT and OFFSET cluses
Retrive frist n records from a result set
Retrive the first n records after skipping over a set of records ANSI standard
2. FETCH FIRST - When you need first 5 records SELECt * FROM
(SELECT * FROM daily
ORDER BY att) WHERE rownum <= 5; SELECT * FROM daily
ORDER BY att
FETCH FIRST 5 ROWS ONLY;
3. FETCH FIRST - When you need first 5 records SELECT * FROM daily
ORDER BY att
4. FETCH FIRST - When you need first records based on percentage SELECT * FROM daily
ORDER BY att
FETCH FIRST 5 PERCENT ROWS ONLY;
5. OFFSET, FETCH NEXT - When you need next 3 records afte 5 rows SELECT * FROM daily
ORDER BY att OFFSET 5 ROWS
FETCH NEXT 3 ROWS ONLY;
6. Function/ Procedure in WITH clause: 7. Row Pattern matching:
Search for pattern matches within a column but across more than one row Usage
1. Trend analysis
2. Identify data patterns () 3. Quality control processing 4. Market analysis
Clauses and Options 1. MATCH_RECOGNIZE 2. PARTITION BY 3. ORDER BY 4. MEASURES
5. ROW PATTERN ROWS PER MATCH 6. PATTERN
7. DEFINE
MEASURES and PATERN cluses MEASURES
Navigation functions PREV, NEXT, FIRST, LAST PATTERN
Iternation quantifiers
* zero or more, + one or more, ? zero or more {n,} n or more, {n,m} between n and m
{,m} between zero and m {n} exactly n
...
Ex 1: Find apatterns of declaining attedance of 3 straight days SELECT * FORM daily
MATCH_RECOGNIZE(
ORDER BY att_date MEASURES
A.att_date AS high_date
LAST (DOWN.att) low_attendance, LAST (DOWN.att_date) low+date ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW PATTERN (A DOWN {3})
DEFINE
DOWN AS DOWN.att < PREV (DOWN.att) )
arep;
Ex 2: Find patterns where the same credit card number was used physically in 2 different locations on the same day.
SELECT * FROM cc_trans MATCH_RECOGNIZE (
PARTITION BY cc_num
ORDER By tans_date, trans_st MEASURES
A.cc_num AS ccnum, A.tran_date as tdate ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW PATTERN (A B+)
DEFINE
B as TRUNC(trans_date) = PREV (TRUNC(trans_date)) AND trans_st <> PREV (trans_st)
) trans
ORDER BY tdate;
8. PLSQL Sub program White list
Define the PLSQL program units that have access to a specific PLSQL program unit
The ACCESSIBLE BY clause 9. Invisible Columns:
Statements that do not directly reference the invisible column will not see that column
Columns can be made invisible when using CREATE TABLE, CREATE VIEW, ALTER VIEW, ALTER TABLE
Benefits: The ability to introduce a change while minimizing any negative side effects of that change
ALTER TABLE emp
MODIFY sal INVISIBLE;
Cascading TRUNCATE
TRUNCATE TABLE tname CASCADE; Identity Columns
CREATE TABLE tname
(trans_id NUMBER GENERATED AS identity PRIMARY KEY, trans_date DATE);
DBMS_UTILITY
UTL_CALL_STACK program DBMS_SCHEDULER
2 new job types SQL_SCRIPT BACKUP_SCRIPT