вторник, 11 февраля 2014 г.

Мантра Тома Кайта

Мантра Тома Кайта #oracle http://citforum.ru/database/oracle/kyte/
При разработке приложений баз данных я использую очень простую мантру:

если можно, сделай это с помощью одного оператора SQL;
если это нельзя сделать с помощью одного оператора SQL, сделай это в PL/SQL;
если это нельзя сделать в PL/SQL, попытайся использовать хранимую процедуру на языке Java;
если это нельзя сделать в Java, сделай это в виде внешней процедуры на языке C;
если это нельзя реализовать в виде внешней процедуры на языке C, надо серьезно подумать, зачем это вообще делать...
...есть с чем поспорить, думаю хорошо об это сказано в

Performance Tuning Methods
Prioritized Tuning Steps
The following steps provide a recommended method for tuning an Oracle database.
These steps are prioritized in order of diminishing returns:
steps with the greatest effect on performance appear first.
For optimal results, therefore, resolve tuning issues in the order listed:
from the design and development phases through instance tuning.
Step 1: Tune the Business Rules
Step 2: Tune the Data Design
Step 3: Tune the Application Design
Step 4: Tune the Logical Structure of the Database
Step 5: Tune Database Operations
Step 6: Tune the Access Paths
Step 7: Tune Memory Allocation
Step 8: Tune I/O and Physical Structure
Step 9: Tune Resource Contention
Step 10: Tune the Underlying Platform(s)

http://docs.oracle.com/cd/F49540_01/DOC/server.815/a67775/ch2_meth.htm
Странно, но лучше всего это описано в документации к Oracle 8i !!
(странно, что они ухудшили документацию)

как пример из Oracle 11G
3 Performance Improvement Methods
http://docs.oracle.com/cd/B28359_01/server.111/b28274/technique.htm#PFGRF032
http://docs.oracle.com/cd/B28359_01/server.111/b28274/toc.htm
написано все гораздо хуже, эх зря Лари Элиссон отошел от практики!

SQL запросы и CBO
http://iusoltsev.wordpress.com/profile/individual-sql-and-cbo/

Четыре способа корректировки планов запросов по образцу без изменения кода.
http://www.fors.ru/upload/magazine/05/http_texts/russia_ruoug_deev_sql_plans.html

Turbocharge SQL with advanced Oracle indexing
http://www.dba-oracle.com/art_9i_indexing.htm

The WEIRD: Another way for ORACLE NOT to use your index.
http://intermediatesql.com/oracle/oracle-11g-sql-plan-management-the-dark-side-of-spm-part-4/#Scenario_2:_The_UGLY:_How_to_screw_up_your_neighbor
http://intermediatesql.com/category/oracle/
Oracle SQL tuning - Tune individual SQL statements
http://www.dba-oracle.com/art_sql_tune.htm
Steps for Oracle Performance Tuning
http://www.dba-oracle.com/t_steps_for_oracle_performance_tuning.htm
Top 10 tips for Oracle performance
http://www.slideshare.net/gharriso/top-10-tips-for-oracle-performance

Parallel processing: Using parallel SQL effectively
http://searchitchannel.techtarget.com/feature/Parallel-processing-Using-parallel-SQL-effectively

Недели разработки экономят часы планирования.

http://blogs.hexaware.com/informatica-way/informatica-pushdown-optimization/
Еще

понедельник, 3 февраля 2014 г.

oracle generate calendar

SELECT TO_NUMBER (TO_CHAR (daterange, 'yyyy')) AS year,
       TO_NUMBER (TO_CHAR (daterange, 'yyyymmdd')) AS date_id,
       TRIM (TO_CHAR (daterange, 'month')) AS month,
       TO_NUMBER (TO_CHAR (daterange, 'mm')) AS month_id,
       TO_NUMBER (TO_CHAR (daterange, 'dd')) AS day_id,
       TRIM (TO_CHAR (daterange, 'day')) AS day,
       daterange AS calendar_date
  FROM (  SELECT DateRange
            FROM (    SELECT TO_DATE ('01.01.2005', 'dd.MM.YYYY') - 1 + LEVEL
                                AS DateRange
                        FROM DUAL
                       WHERE (TO_DATE ('01.01.2005', 'dd.MM.YYYY') - 1 + LEVEL) <=
                                LAST_DAY (
                                   ADD_MONTHS (
                                      SYSDATE,
                                      12 - TO_NUMBER (TO_CHAR (SYSDATE, 'mm'))))
                  CONNECT BY LEVEL <= 99999)
        ORDER BY DateRange);

воскресенье, 2 февраля 2014 г.

exec sql plus in useful manner

SET NEWPAGE NONE
SET PAGESIZE 0
SET SPACE 0
SET LINESIZE 16000
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET TERMOUT OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET COLSEP |

spool &1..txt

@@&1

spool off
exit

I then call SQL*Plus passing the actual SQL script I want to run as an argument:

sqlplus -S user/password@database @dump.sql my_real_query.sql

The result is written to a file

    my_real_query.sql.txt

set wrap off 
set linesize 3000   // or something very large

SET ECHO OFF
SET SERVEROUTPUT ON SIZE 1000000
SET PAGESIZE 999
set wrap off
set linesize 3000   // or something very large

четверг, 16 января 2014 г.

truncate table partition by period start_date - end_date

EXPLAIN PLAN
SET STATEMENT_ID = 'partition.testtab.1' FOR SELECT * FROM TRANSACTION
WHERE TRAN_DATE BETWEEN to_date('01.05.2013', 'dd/mm/yyyy')
AND to_date( '30.05.2013','dd/mm/yyyy');
WITH
  pre_conv AS
  (
    SELECT
      CAST ( to_clob(COLUMN_VALUE) AS VARCHAR2(3999)) AS xml_plan
    FROM
      TABLE( XmlSequence( EXTRACT( DBMS_XPLAN.Build_Plan_Xml( 'PLAN_TABLE',
      'partition.testtab.1' ), '/plan/operation/partition' ) ) ) t
    WHERE
      ROWNUM=1
  )
  ,
  part AS
  (
    SELECT
      regexp_replace( xml_plan, '(.*start=")(\d+)(".*)', '\2') start_pos,
      regexp_replace( xml_plan, '(.*stop=")(\d+)(".*)','\2') stop_pos
    FROM
      pre_conv
  )
SELECT
   'ALTER TABLE '
  || t.table_name
  ||' TRUNCATE PARTITION '
  || t.partition_name
  || ' UPDATE INDEXES;'
  /*3.0*/
FROM
  user_tab_partitions t,
  part t2
WHERE
  t.table_name = 'TRANSACTION'
AND partition_position BETWEEN t2.start_pos AND t2.stop_pos;

another solution
create or replace PROCEDURE                 TRUNCATE_PARTITION
(IN_TABLE_OWNER IN VARCHAR2
,  IN_TABLE_NAME IN VARCHAR2
,  IN_DATE IN DATE)
IS
v_partition_name varchar2(50);

BEGIN
    --    v_partition_name:='';
     BEGIN
                SELECT partition_name
                INTO v_partition_name
                FROM          
                    ( 
                          SELECT table_name
                                      , partition_name
                                      , to_date(trim('''' from
                                             regexp_substr(extractvalue(dbms_xmlgen.
                                             getxmltype('select high_value from all_tab_partitions where table_name=''' ||  table_name || ''' and table_owner = ''' || table_owner || ''' and partition_name = ''' || partition_name || ''''),
                                             '//text()'),
                                             '''.*?''')),
                                   'syyyy-mm-dd hh24:mi:ss') high_value_in_date_format
                    FROM all_tab_partitions
                    WHERE 1=1
                        AND table_name = IN_TABLE_NAME
                        AND table_owner = IN_TABLE_OWNER
                        ) SQ
                 WHERE high_value_in_date_format=LAST_DAY(IN_DATE)+1;
                 EXCEPTION WHEN NO_DATA_FOUND THEN  v_partition_name:='';
       END;      

         
         IF LENGTH(v_partition_name)>0 THEN
            EXECUTE IMMEDIATE 'ALTER TABLE '|| IN_TABLE_OWNER ||'.'|| IN_TABLE_NAME ||' TRUNCATE PARTITION ' || V_PARTITION_NAME || ' UPDATE INDEXES'; /*2.0*/
         END IF;
         
END TRUNCATE_PARTITION;

пятница, 10 января 2014 г.