| Rowid Range Scan [message #536074] |
Mon, 19 December 2011 03:08  |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
Hi All,
I want to make full table scan of my query so that I can execute it using parallel option
& the query will get completed in few mins. Eventhough I used this hint "/*+ parallel(t2,4) full(t2) */,
the query is executing with 8 parallel processes but it is going for "Rowid Range Scan" and it is
taking more time to complete. Please let me know how to make this query to go for full table scan
instead of "Rowid Range Scan" so that this query can be tuned.
SELECT /*+ parallel(t2,4) full(t2)*/ID, COUNT (1) FROM acnt_transact t2 GROUP BY ID;
Many Thanks.
|
|
|
|
|
|
|
|
| Re: Rowid Range Scan [message #536083 is a reply to message #536077] |
Mon, 19 December 2011 03:30  |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
Hi,
Please find the execution plan.
(1) SELECT STATEMENT CHOOSE
Est. Rows: 353,751,365 Cost: 762,007
PX COORDINATOR
(7) PX SEND QC (RANDOM) SYS.:TQ10001
Est. Rows: 353,751,365 Cost: 762,007
(6) HASH GROUP BY
Est. Rows: 353,751,365 Cost: 762,007
(5) PX RECEIVE
Est. Rows: 353,751,365 Cost: 509,714
(4) PX SEND HASH SYS.:TQ10000
Est. Rows: 353,751,365 Cost: 509,714
(3) PX BLOCK ITERATOR
Est. Rows: 353,751,365 Cost: 509,714
(2) TABLE TABLE ACCESS FULL RPT.ACNT_TRANSACT [Analyzed]
Blocks: 4,747,911 Est. Rows: 353,751,365 of 353,751,365 Cost: 509,714
Many Thanks.
|
|
|
|
|
|
| Re: Rowid Range Scan [message #536094 is a reply to message #536084] |
Mon, 19 December 2011 03:44 |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
Hi,
Yes the plan is not showing "ROWID RANGE SCAN" but when the query is executing it is going for "ROWID RANGE SCAN".
Please let me know if there is any option to disable ROWID scan so that query will go for full table scan.
SQL> select count(1) from gv$session_longops where sql_hash_value=2393214378
and target='RPT.ACNT_TRANSACT' and OPNAME='Rowid Range Scan';
COUNT(1)
----------
214
SQL>
Many Thanks.
[Updated on: Mon, 19 December 2011 03:48] by Moderator Report message to a moderator |
|
|
|
| Re: Rowid Range Scan [message #536096 is a reply to message #536094] |
Mon, 19 December 2011 03:47 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Have you considered you're observing the parallel slaves scanning portions of the table? How else to do these but range scans?
Also, best way to get explain plan is:
sql@ORA11GMK> explain plan for
2 select * from dual
3 /
Explained.
Elapsed: 00:00:00.01
sql@ORA11GMK> select * from table(dbms_xplan.display())
2 /
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
Elapsed: 00:00:00.05
[Updated on: Mon, 19 December 2011 03:48] Report message to a moderator |
|
|
|
|
|
| Re: Rowid Range Scan [message #536100 is a reply to message #536096] |
Mon, 19 December 2011 03:53 |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
Hi,
Yes the query is executing using parallel slaves.
SQL> select inst_id,sid,serial#,program from gv$session where username='RPT' and osuser='rwd';
INST_ID SID SERIAL# PROGRAM
---------- ---------- ---------- ------------------------------------------------
3 1020 39579 oracle@phdb7p03 (P003)
3 1052 16776 oracle@phdb7p03 (P002)
3 1029 24484 oracle@phdb7p03 (P001)
3 1024 25790 oracle@phdb7p03 (P000)
2 1014 40921 sqlplus@thdb1d02 (TNS V1-V3)
SQL>
Thanks.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
