distributed query join local table want to run local sub select first

I have a view (huge data set) on remote, and a local table. when join them restrict on local table down to one row, it does not run on local first, it always run the complete view on remote which takes forever. Tried couple different hint did nto help.

Original query:

select *

from twadmin.v_inv_charter@sboptwc i, priadmin.repair r

where i.repair_ord = r.order_nbr and

r.close_dt > to_date(’04/18/2017 00:00:05′,’MM/DD/YYYY HH24:MI:SS’) and

r.close_dt < to_date(’04/18/2017 00:00:10′,’MM/DD/YYYY HH24:MI:SS’)

——————————————————————

| Id | Operation | Name |

——————————————————————

| 0 | SELECT STATEMENT | |

| 1 | MERGE JOIN CARTESIAN | |

| 2 | REMOTE | V_INV_CHARTER |<—– Expensive

| 3 | BUFFER SORT | |

| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| REPAIR |

| 5 | INDEX RANGE SCAN | REPAIR_ORDER_NBR |

——————————————————————


This clause restrict table r to one row:

select order_nbr from priadmin.repair r

where r.close_dt > to_date(’04/18/2017 00:00:05′,’MM/DD/YYYY HH24:MI:SS’) and

r.close_dt < to_date(’04/18/2017 00:00:10′,’MM/DD/YYYY HH24:MI:SS’)

ORDER_NBR

——————–

TW80410857

1 row selected.

Try with hint:

select /*+ driving_site (i) */ *

from twadmin.v_inv_charter@sboptwc i, priadmin.repair r

where i.repair_ord = r.order_nbr and

r.close_dt > to_date(’04/18/2017 00:00:05′,’MM/DD/YYYY HH24:MI:SS’) and

r.close_dt < to_date(’04/18/2017 00:00:10′,’MM/DD/YYYY HH24:MI:SS’)

——————————————————————————–

| Id | Operation | Name |

——————————————————————————–

| 0 | SELECT STATEMENT REMOTE | |

| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SERIAL_HISTORY |

| 2 | INDEX RANGE SCAN | SERIAL_HISTORY_REPORD_IDX |

| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SERIAL_HISTORY |

| 4 | INDEX RANGE SCAN | SERIAL_HISTORY_REPORD_IDX |

| 5 | MERGE JOIN | |

| 6 | VIEW | |

| 7 | WINDOW SORT PUSHED RANK | |

| 8 | HASH JOIN RIGHT OUTER | |

| 9 | VIEW | |

| 10 | HASH JOIN | |

| 11 | TABLE ACCESS FULL | VENDOR_SHIP_HDR |

| 12 | TABLE ACCESS FULL | VENDOR_SHIP_DTL |

| 13 | PARTITION RANGE ALL | |

| 14 | TABLE ACCESS FULL | RO_HIST |

| 15 | SORT JOIN | |

| 16 | REMOTE | REPAIR |

——————————————————————————–

what I really want to have it execute on local repair table first which retuns limited number of rows and send it over to remote to restrict the order_nbr.

I have also tried:

select *

select /*+ no_unnest push_subq */

order_nbr

from priadmin.repair r

where

r.close_dt > to_date(’04/18/2017 00:00:05′,’MM/DD/YYYY HH24:MI:SS’) and

r.close_dt < to_date(’04/18/2017 00:00:10′,’MM/DD/YYYY HH24:MI:SS’))


(select order_nbr

from priadmin.repair r

where r.close_dt > to_date(’04/18/2017 00:00:05′,’MM/DD/YYYY HH24:MI:SS’) and

r.close_dt < to_date(’04/18/2017 00:00:10′,’MM/DD/YYYY HH24:MI:SS’))

select *

from twadmin.v_inv_charter@sboptwc i , myq r

where i.repair_ord = r.order_nbr

Both run on the remote against entire view w/o restriction on repair_nbr.

Any suggestion?

Related:

Event ID 3002 — User-mode Protected Media Path File Validation

Event ID 3002 — User-mode Protected Media Path File Validation

Updated: November 30, 2007

Applies To: Windows Server 2008

Protected Processes are used to enhance the Digital Rights Management technology in Windows Vista and Windows Server 2008. Code Integrity validates user-mode files loaded into Protected Processes that are part of the Protected Media Path. The validation compares the page hashes stored in the system security catalog files to the page hashes of the user-mode files themselves. If the page hashes in the system security catalog files do not match the page hashes from the system file, the system file is not loaded by the operating system.

Additionally, Code Integrity validates cryptographic system files. The following cryptographic system files are validated by Code Integrity: bcrypt.dll, dssenh.dll, rsaenh.dll, win32_tpm.dll, and fveapi.all.

Note: If a kernel debugger is attached to the computer, Code Integrity still validates the page hashes on the user-mode files against the page hashes stored in the system security catalog files, but the operating system will load the files.

Event Details

Product: Windows Operating System
ID: 3002
Source: Microsoft-Windows-CodeIntegrity
Version: 6.0
Symbolic Name: CiImagePageHashNotFound
Message: Code Integrity is unable to verify the image integrity of the file %2 because the set of per-page image hashes could not be found on the system.

Resolve
Replace the system file by using Startup Repair

The page hash of the system file must match the hash stored in the system security catalog. If the hashes do not match, you should replace the system file with a version that has the correct hash. This can be done by using Startup Repair.

To use Startup Repair to replace a system file:

  1. Insert the Windows product disc.
  2. Restart the computer.
  3. When prompted, press any key to start the computer from the Windows product disc.
  4. Choose the appropriate language settings, and then click Next.
  5. Click Repair your computer.
  6. Select the operating system you want to repair, and then click Next.
  7. On the System Recovery Options menu, click Startup Repair.
  8. When Startup Repair has finished, restart the computer.

Verify

To verify that user-mode files were sucessfully validated and loaded, confirm that Event ID 3002 or 3003 are no longer being logged to the Microsoft-Windows-CodeIntegrity operational event log channel.

To perform this procedure, you must be a member of the local Administrators group, or you must have been delegated the appropriate authority.

To confirm that Event ID 3002 or 3003 are no longer being logged to the Code Integrity operational channel:

  1. Click Start, point to Administrative Tools, and then click Event Viewer.
  2. Expand Applications and Service Logs, expand Microsoft, expand Windows, expand CodeIntegrity, and then click Operational.
  3. Click to sort the events on the Date and Time column.
  4. Look for an instance of Event ID 3002 or 3003 that is after the date and time the issue was resolved.
  5. If no instances are found, user-mode files are being successfully validated and loaded.

Related Management Information

User-mode Protected Media Path File Validation

Core Security

Related:

Event ID 2196 — Message Queuing Operation

Event ID 2196 — Message Queuing Operation

Updated: January 31, 2008

Applies To: Windows Server 2008

Message Queuing operation provides message authentication, message encryption, dead-letter queues, security settings, and other basic features. If Message Queuing has problems with any of these features, proper Message Queuing operation may suffer.

Event Details

Product: Windows Operating System
ID: 2196
Source: MSMQ
Version: 6.0
Symbolic Name: EVENT_WARN_FAILED_VERIFY_MESSAGE_SIGNATURE
Message: Message Queuing failed to verify digital signature of a message sent to queue %1. The message was rejected. A negative arrival acknowledgement will be sent if requested by the sender.

This event is logged at most once per %2 seconds. To change this setting, set \HKLM\Software\Microsoft\MSMQ\Parameters\Event2196 registry value to desired time in seconds.

Resolve
Confirm that the Message Queuing application is using a strong hash function and that it has a valid user certificate

The message’s signature could not be verified. This may indicate the following issues:

  • Weak hash function issues
  • Bad user certificate
  • Corruption of the message in transit

Resolve weak hash function issues

By default, Message Queuing 4.0 does not support certain weaker security algorithms that were available in earlier versions of Message Queuing. Support for the weaker security algorithms can be enabled with a registry entry. For more information about the security algorithms that are supported by Message Queuing 4.0, see PROPID_M_HASH_ALG (http://go.microsoft.com/fwlink/?LinkId=91702).

Message Queuing has historically offered four hashing algorithms with which to sign a message: MD2, MD4, MD5, and SHA1. In previous versions of Message Queuing, MD5 was the default for most message and SHA1 was used for Hypertext Transfer Protocol (HTTP) and multicast messaging, which were introduced in Message Queuing 3.0. SHA1 is now the default for all types of messages, because MD2, MD4, and MD5 have been deprecated as weak. Also, by default, Message Queuing 4.0 will neither accept messages that are signed with these weak algorithms nor generate them.

You can enable weaker algorithms on Message Queuing 4.0 to support any Message Queuing applications that require them by adding the registry key (not value) HKLM\SOFTWARE\Microsoft\MSMQ\Parameters\Security\WeakHashAlgorithms. If this registry key is not present, as is the case by default, all weaker algorithms are disabled. If this registry key is present, all weaker algorithms are enabled. To enable only certain weak algorithms, you must add the registry key and then specify the values of those weaker algorithms that you want to continue to disable.

Caution: Incorrectly editing the registry might severely damage your system. Before making changes to the registry, you should back up any valued data.

To perform the following procedures, you must have membership in Administrators, or you must have been delegated the appropriate authority.

To continue to disable certain weaker authentication algorithms:

  1. Open Registry Editor. To open Registry Editor, click Start. In the search box, type regedit, and then press ENTER.
  2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSMQ\Parameters\Security.
  3. On the Edit menu, point to New, and then click Key.
  4. Type WeakHashAlgorithms as the name of the new registry key, and then press ENTER.
  5. Right-click WeakHashAlgorithms, point to New, click DWORD (32-bit) Value, and then type any name for the new value.
  6. Double-click the new DWORD (32-bit) value, click Decimal, and then in Value data type the appropriate value for the algorithm that you want to disable:
    • 32769 for MD2
    • 32770 for MD4
    • 32771 for MD5
    • 32773 for MAC
  7. Click OK to close the Edit DWORD (32-bit) Value dialog box.
  8. Create new DWORD (32-bit) values for additional algorithms that you also want to disable.
  9. On the File menu, click Exit to close Registry Editor.

If your Message Queuing application is running on Windows Vista and it chooses a weaker authentication algorithm, Message Queuing will override the choice and use SHA1 instead, by default. If you need to generate messages on Message Queuing 4.0 with one of the weak algorithms, there is a registry key that will turn off the upgrading and make Message Queuing 4.0 honor the algorithm requested by your code. Create a DWORD registry value named WeakHashAlgUpgrade under the key HKLM\SOFTWARE\Microsoft\MSMQ\Parameters\security and set it to 0, and then restart the MSMQ Service.

For more information, see the following resources:

Fix an issue with a bad user certificate

To fix an issue with a bad user certificate:

  1. On any computer in the domain, click Start. In the search box, type compmgmt.msc, and then press ENTER.
  2. Enter administrator credentials, if you are prompted, and continue through the User Access Control messages.
  3. Navigate to the Message Queuing console tree (Services and Applications\Message Queuing).
  4. Right-click Message Queuing, and then click Properties.
  5. Click the User Certificate tab.
  6. Click View.
  7. Check to see whether the computer sending the unauthenticated messages is in the Personal Certificates list.
  8. If the computer is not in the list, a certificate was not registered.
  9. You can fix this by performing steps 1 through 7 on the computers on which the certificate was not registered. Then, for step 6, click Register instead of View.

Fix a corrupted message

If you think the message was corrupted in transit, there is probably an issue with a level below Message Queuing.

If you continue to get this error, note any details in the event message, and then contact Microsoft Customer Service and Support (CSS). For information about how to contact CSS, see Enterprise Support (http://go.microsoft.com/fwlink/?LinkId=52267).

Verify

Verify that the MSMQ Service is installed and running.

To perform this procedure, you must have membership in Administrators, or you must have been delegated the appropriate authority.

To verify that the MSMQ Service is installed and running:

  1. Open the Services snap-in. To open Services, click Start. In the search box, type services.msc, and then press ENTER.
  2. Locate the Message Queuing service, and confirm that the value in the Status column is Started.

Related Management Information

Message Queuing Operation

Message Queuing

Related: