Oracle hints issue?

[dsleo]

Hi,

I have a job design like this

ORA->LKP->XFR->ORA

The first ORA stage executes a source query running a join between a few huge tables. This query results in close to 30 million records. To tune this query to return records faster, we added some Oracle hints. The query with oracle hints starts pulling records (the first cursor set) within 3 min when run from TOAD, but when i run this from DS job, it wont do anything for hours.

The hints are only forcing use of certain index's.

Why would a query that returns records promptly in TOAD not do the same thing from DS job?

Thanks for your suggestions in advance

[kcbland]

Hints are suggestions, not requirements. Is the query the same as in the job? What about the parallelism in the ORA stage?

[dsleo]

Default execution mode is Sequential mode (option is grayed out) in Stage->Advanced->Excecution mode for the ORA stage

[chulett]

What about the explain plan? Is it the same when checked, Toad run versus DataStage run?

[dsleo]

Yes Craig, the explain plan is the same. I just restarted the DS job and checked to confirm that.

is it possible that it is going to wait until it reads the entire 30 mil before moving on to next stage?

Because when watching the session (from TOAD) when job runs from DS, i see the number of rows processed increasing, but DS monitor doesnt show that it processed/started reading any records..

[ray.wurlod]

Try putting the name of the table you are querying into the Partition Table field as well, to see whether parallel execution becomes available.

[John Smith]

Hi,

I have a job design like this

ORA->LKP->XFR->ORA

The first ORA stage executes a source query running a join between a few huge tables. This query results in close to 30 million records. To tune this query to return records faster, we added some Oracle hints. The query with oracle hints starts pulling records (the first cursor set) within 3 min when run from TOAD, but when i run this from DS job, it wont do anything for hours.

The hints are only forcing use of certain index's.

Why would a query that returns records promptly in TOAD not do the same thing from DS job?

Thanks for your suggestions in advance

DS is doing the exact same thing which means your Oracle query is run the same way as if you had run it in TOAD. Only difference is TOAD returns the first 100 records whereas in DS it will have to process all the records before any stats is shown.
Try changing the parameters in TOAD to return all records and you will enccounter similar symtoms. Both DS and TOAD are just firing off your SQL and it's up to the database engine to run them.

[rleishman]

... the explain plan is the same. I just restarted the DS job and checked to confirm that.

How did you confirm it? Did you perform a SQL Trace? Or check V$SQL_PLAN. If you used a GUI, then it is possible that it re-planned the SQL rather than showing the actual execution plan.

If the plan is the same, then it is VERY likely that DS will start processing rows pretty much immediately. If your array size is 1000 (for example) obviously it will need 1000 rows before it does anything.


You can use the following SQL to get the real plan on 10g if you know the session id:

Code: Select all

SELECT  p.plan_table_output
FROM    v$session s
,       table(dbms_xplan.display_cursor(s.sql_id, s.sql_child_number)) p
where   s.sid = &1
/

It's harder in 9i, but here is a SQL Plus script that I use:

Code: Select all

-- Extract the plan for the a SID (v9i)

CLEAR COLUMN
COLUMN  sql_hash_value  NEW_VALUE sql_hash_value
COLUMN  sql_address     NEW_VALUE sql_address


SELECT  s.sql_hash_value, s.sql_address
FROM    v$session s
WHERE   sid = &1
/


COLUMN "Rows" FORMAT a6
COLUMN "Plan" FORMAT a68 wrap
COLUMN id FORMAT a4 justify right

SET PAGESIZE 500

WITH pt AS (
        SELECT  *
        FROM    v$sql_plan p
        WHERE   p.hash_value = '&sql_hash_value'
        AND     p.address = '&sql_address'
)
SELECT  xid AS id
,       plan AS "Plan"
,       rws AS "Rows"
FROM (
        SELECT  decode(access_predicates || filter_predicates, NULL, ' ', '*') ||
                lpad(id, 3, ' ') AS xid
        ,       lpad(' ',depth-1)||operation||' '|| options||' '||object_name
                || decode(partition_start, NULL, NULL, ' ' || partition_start || ':' || partition_stop)
                AS plan
        ,       lpad(
                        CASE
                                WHEN cardinality > 1000000
                                THEN to_char(trunc(cardinality/1000000)) || 'M'
                                WHEN cardinality > 1000
                                THEN to_char(trunc(cardinality/1000)) || 'K'
                                ELSE cardinality || ' '
                        END
                ,       6
                ,       ' '
                ) AS rws
        ,       id
        FROM    pt
        ORDER BY id
)
UNION ALL
SELECT  NULL
,       chr(10) || 'Access Predicates' || chr(10) || '------------------------'
,       NULL
FROM    dual
UNION ALL
SELECT  to_char(id)
,       access_predicates
,       NULL
FROM    pt
WHERE   access_predicates IS NOT NULL
UNION ALL
SELECT  NULL
,       chr(10) || 'Filter Predicates' || chr(10) || '------------------------'
,       NULL
FROM    dual
UNION ALL
SELECT  to_char(id)
,       filter_predicates
,       NULL
FROM    pt
WHERE   filter_predicates IS NOT NULL
/

[dsleo]

Only difference is TOAD returns the first 100 records whereas in DS it will have to process all the records before any stats is shown

That was part of my question Yes, whether DS would wait to read those 30 mil before starting to move on to next stage which i did not expect. Also, when i run from TOAD, i see the first fetch in 2-3 min, but when i run from DS i dont see the first fetch returning around the same time or even after 30 min (while monitoring my job session from session browser in TOAD)

[dsleo]

Ray,

I already have the partition table option set to the biggest table from the set of 5 tables being joined in this query.