SQL Hints: /*+ MONITOR */

https://oracle-base.com/articles/11g/report_sql_monitor_text.txt

SQL HINT

/*+ MONITOR */

/*+ MONITOR */

The MONITOR hint switches on SQL monitoring for statements that would not otherwise initiate it.

SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees
FROM   emp e
       JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
ORDER BY d.dname;


If you have long running statements you don't want to monitor, use the NO_MONITOR hint to prevent them being monitored.



-- 11gR2
SET LINESIZE 200
COLUMN sql_text FORMAT A80

SELECT sql_id, status, sql_text
FROM   v$sql_monitor
WHERE  username = 'SCOTT';

SQL_ID        STATUS              SQL_TEXT
------------- ------------------- --------------------------------------------------------------------------------
526mvccm5nfy4 DONE (ALL ROWS)     SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees
                                  FROM   emp e
                                         JOIN dept d ON e.deptno = d.deptno
                                  GROUP BY d.dname
                                  ORDER BY d.dname
                                 


Once the SQL_ID is identified, we can generate a report using the REPORT_SQL_MONITOR function.
*/

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL /host/report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '526mvccm5nfy4',
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF                                 



SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees FROM emp e JOIN dept d ON e.deptno = d.deptno GROUP BY d.dname ORDER BY d.dname

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SCOTT (53:1261)
 SQL ID              :  526mvccm5nfy4
 SQL Execution ID    :  16777217
 Execution Started   :  03/19/2011 12:55:13
 First Refresh Time  :  03/19/2011 12:55:13
 Last Refresh Time   :  03/19/2011 12:55:13
 Duration            :  .014344s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@oel5-11gR2-demo.localdomain (TNS V1-V3)
 Fetch Calls         :  4

Global Stats
======================================================================
| Elapsed |   Cpu   |    IO    | PL/SQL  |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Calls |  Gets  |
======================================================================
|    0.01 |    0.01 |     0.00 |    0.00 |     0.00 |     4 |     41 |
======================================================================

SQL Plan Monitoring Details (Plan Hash Value=2970111170)
================================================================================================================================================
| Id |            Operation            |  Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                                 |         | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
================================================================================================================================================
|  0 | SELECT STATEMENT                |         |         |      |         1 |     +0 |     1 |        3 |       |          |                 |
|  1 |   SORT GROUP BY                 |         |       4 |    7 |         1 |     +0 |     1 |        3 |  2048 |          |                 |
|  2 |    MERGE JOIN                   |         |      14 |    6 |         1 |     +0 |     1 |       14 |       |          |                 |
|  3 |     TABLE ACCESS BY INDEX ROWID | DEPT    |       4 |    2 |         1 |     +0 |     1 |        4 |       |          |                 |
|  4 |      INDEX FULL SCAN            | PK_DEPT |       4 |    1 |         1 |     +0 |     1 |        4 |       |          |                 |
|  5 |     SORT JOIN                   |         |      14 |    4 |         1 |     +0 |     4 |       14 |  2048 |          |                 |
|  6 |      TABLE ACCESS FULL          | EMP     |      14 |    3 |         1 |     +0 |     1 |       14 |       |          |                 |
================================================================================================================================================

Using Boolean in Functions

Using Boolean in Functions

CREATE or replace FUNCTION testfn (
    date1 IN DATE
) RETURN BOOLEAN
    AS
BEGIN
    IF
        date1 > SYSDATE
    THEN
        RETURN true;
    ELSE
        RETURN false;
    END IF;
END;

----
SET SERVEROUTPUT ON;
----
DECLARE
    v_2   VARCHAR2(10);
BEGIN
       
    v_2 := case when testfn('1-Jan-2019') then 'true' else 'false' end;
    DBMS_OUTPUT.PUT_LINE(sys.diutil.bool_to_int(testfn('1-Dec-2019')));

    dbms_output.put_line(v_2);
    IF
        testfn('1-Jan-2019')
    THEN
        dbms_output.put_line('TRUE');
    ELSE
        dbms_output.put_line('FALSE');
    END IF;

END;



--convert boolean to int
SYS.DIUTIL.BOOL_TO_INT() function:

DECLARE
status BOOLEAN:= false;
BEGIN
  DBMS_OUTPUT.PUT_LINE(sys.diutil.bool_to_int(status));
END;

This will return 1 for true and 0 for false (and null for null).

Designing XSL Sub Templates

Why?

1. Allow Reusable advance functionality for RTF templates.

1. Transform Data structure for section of report
2. create a stylesheet to manage a complex layout

What?

Its a xsl file, which consists of

• one or more <xsl:template> definitions, each containing
• block of formatting
• block of processing commands

How to Use?

Once its developed as subtemplate, its called from main rtf file for processing and formatting requirements.

High Level Development Process

1. xsl template contains serveral <xsl: template> definitions, these definitions contains rules to apply, when a specified node is matched.

2. main template import the xsl template, and also command to apply

3. upload the main template to report definition, and subtemplate to catalog.

SQL Optimization: Optimizer Process

Query Optimizer SQL Processing
--------------------------------

1. Parsing: 
    Optimizer:
        -> Syntax Check
        -> Objects exists in sql [semantic check]
        -> Search shared pool, if statement is ever executed [cache]
            -> if this is the first time optimizer received the sql, it will create a hash of the sql statememt. and then saved it. [sha256]
           
2. Optimzation Stage:
        -> generate possible execution plans
        -> save the selected execution plan along side with parsed sql hash in shared pool.

3. Row Source Generation:
        -> convert execution plan in iterative binary format.
        -> similar to compiling execution plan.
        -> expensive
       
4. Execution: 
        -> Excuting the Row Source Tree produced by row source generator.
       
------------------------------------------------------------------------

Hard Parse: 1->2->3->4 [first time always hardparse]
when the optimizer perform all above steps to execute SQL statement, it is called Hard Parse.

Soft Parse: 1, 4 Only: [no 2,3]
------------------------------------------------------------------------

Lowest Cost Plan is chosen: CBO

------------------------------------------------------------------------

Execution Plan overall cost =
        -> selectivity [stat based]
        -> Cardinality [stat based]
        -> Cost: CPU, I/O and Network
       
*selectivity and cardinality changes based on data changes in objects

-> after any signifacant data changes: we should update these stats [gather stats program in Oracle apps]

-------------------------------

1. selectivity[relative no]: (retrieved rows/total rows)
            -> always <= 1
    [so how many rows you are selecting the query]
 
2.  cardinality [Rows]: no of rows returned by each operation in execution plan [ex. select empname from emp, dept where e.deptid= d.deptid and deptid = 10]           
    ex. if 10 employees are returned by sql, then cardinality is 10


   
Why selectivity and cardinality are important
----------------------------------------------
1. 10/10000, selectivity = 0.0001, get the rowid of the rows and search in index.

2. 5000/10000: low selectivity ex: search in index for half rows, so its better to skip index, access table dierctly is better.

3. cardinality: similar to selectivity, cardinality concept, Optimizer choose, if Index to be used or not.

Since cardinality is stat based, and optimizer look at these stats for
cardinality and then choose the execution plan, it most of times issue with stats which causes sub-optimal plan.
------------------------------------------------

How to build a BI Publisher data model query with receivables transactions information and notes text.

How to build a BI Publisher data model query with receivables transactions information and notes text.
In other words, how to link the tables RA_CUSTOMER_TRX_ALL with ZMM_NOTES.
--------------------------

SOLUTION: Following sample query can be used to join RA_CUSTOMER_TRX_ALL with ZMM_NOTES.

SELECT *
   FROM ra_customer_trx_all trx,
             zmm_notes note
WHERE note.source_object_code = 'AR_TRANSACTION'
    AND trx.customer_trx_id          = note.source_object_uid

RTF Template Various Number Formats

Double click on element, and

BI Publisher Properties > Formatting [Number] > Format

1. You want to show Negative Number -200.34 as (200.34) then enter below into format.

#,##0.0000;(#,##0.0000)


2. If Number is just decimal place to 0000, then place #,##0.0000

3. In case there is number which length is long like 20 char long, then it will display as scientific , to avoid it use Force LTR check box, which will read element from Left to Right (like Arabic style), and it will be read as text instead of number, and you can also check the code generated after applying this check box.

Also any operations being done on xml element , first it should be converted to number type.

<?number(invoiceAmt) - number(amtPaid)?>

page breaks in rtf template

there are 3 ways to create page breaks


<?split-by-page-break:?>

#below code should be written inside the BIP Field element
<xsl:attribute name="break-before">page</xsl:attribute>

directly add page breaks in pages.

------------------------------------------------------

Also in case you want to display different header and footer on different pages then you may use section break, and remove link to previous in the rtf template.